Program(s): Database - Introductory
Program Coordinator(s): Multiple Coordinators
Course Leader or Contact: N/A
Version: 2.0
Status: Approved (APPR)

Section I Notes: N/A

Detailed Description
In this course, the students design, document, and implement relational database models for common software systems. Through various hands-on activities, students practice essential database modeling and implementation skills such as: Examining business problem descriptions; Designing and documenting conceptual, logical, and physical database models; Normalizing logical database designs; Implementing data models on a relational database management system (RDBMS); and Using constraints to ensure data integrity and quality. The students also practice writing database queries in the Structured Query Language (SQL) to enter, retrieve and maintain the data, as well as building reports in an industry-standard reporting tool to present the data in informative and intuitive ways.

Program Context

 Critical Performance
By the end of this course, students will have demonstrated the ability 
to:
- Design, document, and implement optimal (i.e. 
normalized) database models for common business/software problems
- Create, query, and maintain the data using the Structured Query 
Language
- Present data in informative and intuitive ways using 
an industry-standard reporting tool

Learning Outcomes
To achieve the critical performance, students will have demonstrated 
the ability to: 

- Articulate the roles of relational databases in common
software systems
- Analyze business problem descriptions to identify all relevant data 
entities and the relationships between these entities 
- Document the relevant data entities and their relationships in a 
conceptual model using Entity-Relationship Diagrams(ERDs)
- Design logical database models from an ERD to effectively    
represent one-to-one, one-to-many, many-to-many, and inheritance (i.e. 
"is-a") relationships
- Validate a logical data model against first, second, and third 
normal form
- Design appropriate physical data models to enable efficient    
implementation on a RDBMS 
- Implement a physical model in an industry-standard RDBMS
- Use appropriate database constraints to enforce data integrity and 
quality
- Formulate effective SQL statements to enter, query, and maintain the 
data
- Present data in informative and intuitive ways using industry-
standard reporting tools

In-class Exercises (10)   20% 
Assignment (1)            10%
Case Study (1)            20%    
Mid-term exam             25% 
Final exam                25%
Total                    100%

Faculty members teaching this course agree to the following 
practices:
Sheridan wishes to encourage behaviours that will help students be 
successful in the workplace, and to ensure that students receive 
credit for their individual work. 
1.	For submission of assignments and projects, the faculty
        member will specify, on the class plan:  
	- Due dates and special instructions for submissions 
	- Deductions for overdue submissions 
2.	Where a range of evaluations is stated on this outline, the
        number and type of evaluations will be the same across all
        sections of this course for a given semester.
3.	Exams must be written as scheduled by the faculty member. A
        make up exam is at the faculty members discretion provided
        that the student has an acceptable reason for their absence
        and may be asked for documented evidence, such as a medical
        certificate, explaining their absence. These special
        situations must be discussed with the faculty member
        immediately once the situation becomes known. Semester time
        constraints may limit rewrite options.
 
To pass the course, students must achieve at least 50% overall in the 
course, as well as a 50% weighted average across the tests and the 
exams combined. Student will receive at least 40% of the evaluation 
by the end of the week 8.

Hoffer J., Ramesh V., and Topi H. "Modern Database 
Management", 12th edition, ISBN 9780133544619,
Prentice Hall. 

Supplementary Reference:
Books 24-7, which can be accessed through AccessSheridan, Sheridan 
Library.

Module 1:  Introduction
           - Meet and greet
           - Course overview
           - The use of DBs in software applications
           - Software installation and configuration
           - Architectural overview
           - SQL Basics (Arithmetic expressions, null values, operator
             precedence, aliases, concatenation operator, etc.)
           - Reinforcing Activities: 
               -- In-Class Exercise(s) 
Module 2:  Data Retrieval
           - Filtering: WHERE, LIKE, and logical keywords
           - Sorting: ORDERED BY
           - Duplicate removal: DISTINCT
           - Aggregate functions
           - Sub-queries and IN keyword
           - Reinforcing Activities:
              -- In-Class Exercise(s)
              -- Assignment Posted. [Sample Assignment: Translate
                 questions written in English into SQL select
                 statements and build reports to present the data
                 returned by the queries in informative ways.]
Module 3:  Data Reporting and Presentation:
           - Data vs Information
           - Building simple reports
           - Report deployment and execution
           - Parametrized reports
           - Graphs and charts
           - Reinforcing Activities:
              -- In-Class Exercise(s)
Module 4:  Data Retrieval Part 2:
           - CROSS JOIN and INNER JOIN
           - LEFT OUTER JOIN and RIGHT OUTER JOIN
           - Multi-table joins
           - Reinforcing Activities:
              -- In-Class Exercise(s)
Module 5:  Data Retrieval Part 3:
           - GROUP BY and HAVING
           - UNION, INTERSECT, SUBTRACT
           - Reinforcing Activities:
              -- In-Class Exercise(s)
Module 6:  Data Retrieval--Comprehensive Practice
           Data Manipulation
           - INSERT, DELETE, UPDATE
           - Reinforcing Activities:
              -- In-Class Exercise(s)
              -- Assignment Due
Module 7:  Midterm Exam
           Midterm take-up and debrief
Module 8:  Intuition Development: A closer look at the sample database
           - Entities, Attributes, and Relationships
           - Table as "Class", Row as "Object", Column as "Property"
           - Types of relationships, and how they were represented
           - E-R Diagrams
           - Identifying relevant entities, attributes and
             relationships from business problem descriptions:
             - "Has-A" vs "Is-A"
             - Single-value vs multi-value attributes
           - Reinforcing  Activities:
              -- In-Class Exercise(s)
              -- Case Study Posted. [Sample Case Study: Design &
                 implement the data model, write the necessary
                 queries, and create necessary reports for a more
                 substantial software system (e.g A Registrar's
                 Office Record Management System that
                 involves Programs, Students, Courses, Sections,
                 Transcripts, Grades, Semesters, etc.)]
Module 9:  Modeling
           - Choosing primary keys
           - Representing 1-to-many relationships with Foreign Keys
           - Resolving multi-value attributes into 1-to-many 
             relationships using composite entities
           - Using other constraints
           - Reinforcing Activities:
              --In-Class Exercise(s)
                (Suggestion: Design the data model for a simple
                application such as an Appointment Booking system
                for a Doctor's Office (Multiple doctors, patients,
                appointments, visits, etc.)
Module 10:  Implementation
            - Creating tables
            - Creating primary and foreign keys
            - Creating other constraints
            - Inserting reference data
            - Reinforcing Activities:
               --In-Class Exercise(s)
                 (Suggetion: Implement the data model designed in
                  the previous week.)
Module 11:  Data Modeling Revisited: A theoretical perspective
            - Relational model and Relational algebra
            - Normalization
            - Normal Forms checking
            - Reinforcing Activities:
              --In-Class Exercise(s)
Module 12:  Advanced topics:
            - Indexing
            - Views
            - Modeling inheritance (is-a) 
              relationships
            - Reinforcing Activities:
               --In-Class Exercise(s)
                 Case Study
Module 13:   Comprehensive practice
             - Reinforcing Activities:
                --In-Class Exercise(s)
                  Case Study Due
Module 14:   Final Exam