DBS301 - Database Design II and SQL Using Oracle

Outline info
Semester
School
Last revision date 2017-05-29 00:32:45.965
Last review date 2017-07-17 00:15:35.793


Subject Title
Database Design II and SQL Using Oracle

Subject Description
This subject continues the study of database design and SQL begun in DBS201. Students will learn the entire set of SQL statements using Oracle's DBMS. Students will also learn Oracle's SQL *Plus commands. Students will work in teams to prepare and implement the logical and physical database schema for a business application requiring 12-18 tables including all necessary contraints to protect the integrity of the data.

Credit Status
1 credit (3 units)
Required for CPA - Computer Programming and Analysis (Ontario College Advanced Diploma)
Required for CPD - Computer Programmer (Ontario College Diploma)

Learning Outcomes
Upon successful completion of this subject the student will be able to:

1. Identify all constraints required to maintain data integrity for a business application

2. Work effectively as a member of a team to analyse data requirements, prepare an Entity Relationship Diagram, normalize application user views and prepare a logical database schema for a business application requiring more than 12 tables

3. Identify use of necessary indexes, views and sequences for an application

4. Implement, maintain and retrieve data using an Oracle database for a complex business application

5. Use SQL's Data Definition Language statements of CREATE, ALTER and DROP to create, modify and drop database objects including tables, views, sequences and indexes with all constraints required to maintain data and referential integrity

6. Use SQL*Plus commands to efficiently create, edit and run SQL statements and to format SQL output

7. Use SQL's Data Manipulation Language statements of INSERT, UPDATE and DELETE to add, change and delete application data from tables

8. Use SQL's Transaction Control Language statements of COMMIT to make database changes permanent and ROLLBACK to undo database changes

9. Use SQL's Data Control Language statements of GRANT and REVOKE to permit and remove access to database objects

10. Use commands from SQL*Plus, Oracle's extension to the SQL language, to customize a session environment

11. Use iSQL*Plus to create and run SQL statements

12. Use SQL's SELECT statement to retrieve data required by an application, including use of single row and group functions, nested queries and correlated sub-queries

Essential Employability Skills
Execute mathematical operations accurately.

Apply a systematic approach to solve problems.

Use a variety of thinking skills to anticipate and solve problems.

Locate, select, organize, and document information using appropriate technology and information systems.

Analyze, evaluate, and apply relevant information from a variety of sources.

Manage the use of time and other resources to complete projects.

Take responsibility for one's own actions, decisions, and consequences.

Cheating and Plagiarism
Each student should be aware of the College's policy regarding Cheating and Plagiarism. Seneca's Academic Policy will be strictly enforced.

To support academic honesty at Seneca College, all work submitted by students may be reviewed for authenticity and originality, utilizing software tools and third party services. Please visit the Academic Honesty site on http://library.senecacollege.ca for further information regarding cheating and plagiarism policies and procedures.

Discrimination/Harassment
All students and employees have the right to study and work in an environment that is free from discrimination and/or harassment. Language or activities that defeat this objective violate the College Policy on Discrimination/Harassment and shall not be tolerated. Information and assistance are available from the Student Conduct Office at student.conduct@senecacollege.ca.

Accommodation for Students with Disabilities
The College will provide reasonable accommodation to students with disabilities in order to promote academic success. If you require accommodation, contact the Counselling and Disabilities Services Office at ext. 22900 to initiate the process for documenting, assessing and implementing your individual accommodation needs.

Prerequisite(s)
DBS201 or familiarity with databases. 

Topic Outline

Review of Logical and Physical Database Design 15%

  • Review identification of entities, attributes, candidate and primary keys and normalization of application user views to 3NF
  • Identify historical data requirements
  • Merge 3NF relations obtained for each user view into a common set of relations for the entire application
  • Identify required tables, sequences and indexes for an application
  • Identify all primary keys, foreign keys, validations, mandatory and uniqueness constraints required to protect data integrity
SQL's Data Definition Language Statements 20%
  • Create, modify and drop tables, views, sequences and indexes
  • Define primary key, foreign key, not null, unique and check constraints required to maintain integrity of application data
Oracle's Data Dictionary 5%
  • Query data dictionary using USER_OBJECTS, USER_TABLES, USER_TAB_COLUMNS, USER_INDEXES, USER_SEQUENCES, USER_VIEWS
  • Discuss why a commit is automatically performed after any change to data dictionary information
SQL's SELECT Statement 20%
  • Create, test and walkthrough queries using ordering, single and group row functions, joins (equijoin, non-equijoin, outer, self), subqueries and correlated subqueries
  • Implement relational operators (selection, projection, Cartesian Product, join, union, intersection and difference) in SQL
  • Create appropriate test data
SQL's Data Manipulation Language Statements 20%
  • Create, test and walkthrough insert, update and delete statements, both simple and those including use of subqueries
SQL's Data Control Language Statements 5%
  • Apply and test grant and revoke statements
SQL's Transaction Control Statements 10%
  • Review temporary versus permanent changes to data
  • Apply and test commit and rollback commands
  • Discuss need for transaction processing in a business application
  • Identify and implement transactions required by a business application
Oracle SQL *Plus Commands 5%
  • Customize a session environment using SET command
  • Format ouput using: COLUMN, TTITLE, BTITLE, BREAK, COMPUTE, CLEAR commands
  • Use the DESCRIBE, SPOOL, START, @ commands

Mode of Instruction
Class lectures and discussions are supported by laboratory exercises, directed reading and practical assignments. The sequence of topics is inter-leaved to that each session includes a variety of topics and activities to ensure that the work assigned between lectures is productive.

It is the student's responsibility to save documents, articles and notes that the instructor has provided on BlackBoard or in class. Students will not be able to access BlackBoard as of the last day of the student’s class.

Prescribed Texts
Oracle 11G Notes - for DBS301
Based on copyright agreement the Oracle package is for a student's personal educational use only.

Promotion Policy

Grading Policy
A+ 90%  to  100%
A 80%  to  89%
B+ 75%  to  79%
B 70%  to  74%
C+ 65%  to  69%
C 60%  to  64%
D+ 55%  to  59%
D 50%  to  54%
F 0%    to  49% (Not a Pass)
OR
EXC Excellent
SAT Satisfactory
UNSAT Unsatisfactory

For further information, see a copy of the Academic Policy, available online (http://www.senecacollege.ca/academic-policy) or at Seneca's Registrar's Offices.


Modes of Evaluation
Since this is a professional credit subject, marking standards reinforce professional practice by demanding legible, tidy work. Written materials should be well organized and grammatically correct, with proper spelling and punctuation.

Assignments

  • Students must retain a duplicate of all assignments.
  • Computer assignments should be documented to the instructor's standards.
  • Assignments must be handed in on the scheduled due date. Late assignments are penalized.
  • For particulars, please obtain standards, dates, etc. from your instructor.

Absenteeism and Tests
  • Students should be aware that absenteeism will impact on their ability to achieve satisfactory grades.
  • If you miss a test, you must provide the reason in writing to the instructor prior to the next scheduled class. If your reason is accepted, you will be permitted to write a make-up test. Otherwise, you will be given a zero for the test. You must submit an original doctor’s certificate identifying the date, length of time of expected absence and the specific reason for your absence, or other appropriate documentation.

Term Work and Final Exam
  • Students must attain a combined grade of at least 50% on term work and the final exam. Students must pass the final exam in order to pass the subject. 
  • For further information on evaluation and academic standing, see a copy of the Academic Policy available at Seneca registration offices.

Grading is based on the following marking scheme:


Assignment 1 20%
Assignment 2 20%
Midterm Test 30%
Final Exam 30%





 

Approved by: Denis Gravelle