CP363 DATABASES I, WINTER 2007, WLU

Announcements

1. First assignment handed out in class. Wed. Jan. 17, 2007
2. Labs will be held on Friday Jan. 26 and Fri. Feb. 2, in N2085 from 12:30 - 1:30
3. Second assignment handed out in class. Mon. Feb. 5, 2007
4. Midterm Solutions, Fri. Feb. 16, 2007
5. First assignment marked/returned. Fri. Feb. 16, 2007
6. UniversitySchemaTuplesQueries Fri. Feb. 16, 2007
7. Third assignment handed out in class. Wed. Mar. 14, 2007
8. Review Session: BA112, Monday, April 9th, 10:00-12:00 a.m.
9. BANK Schema Wed. Mar. 28, 2007
10. A2A3

Course Description

The course provides an introduction to the basic features of Database Management Systems (DBMS), focusing on Relational Database Management Systems (RDBMSs). Students will obtain an understanding of how the different components of a DBMS system fit together. They will write programs to interact with a particular DBMS as a case study.

Prerequisites CP264 (Data Structures II) or permission of the Depatment

Course Staff

• Instructor: Dr. Ilias S. Kotsireas,
  Office: N2076A, Office Hours: 24/7 and by appointment, Phone: 884-0710 ext. 2218, E-Mail: ikotsireATwlu.ca
• Assistants:
  • Ms. Wegdan Abdelsalam,
    Office: N2091, Office Hours: by appointment, Phone: 519-884-1970 ext.3165, E-mail: wabdelsalamATwlu.ca

Course Textbook

Fundamentals of Database Systems, 5/E Ramez Elmasri, Shamkant B. Navathe Publisher: Addison-Wesley Copyright: 2007 ISBN-10: 0321369572 ISBN-13: 9780321369574

Notes & Overheads

1. Introduction
2. ER (Entity-Relationship Data Model) 
3. EER (Extended/Enhanced Entity-Relationship Data Model) 
4. Relational Data Model, Relational Algebra 
5. Relational Algebra
6. Relational Constraints
7. SQL (Structured Query Language)
8. JDBC
9. Tuple Relational Calculus 
10. Functional Dependencies + Normalization
11. Transactions
12. Serializability
13. Object Databases
14. Recovery

Textbook Support Web Site   A link to the textbook's support web page is available at: http://www.aw-bc.com/

Course Website   A link to the course web page is available at: http://sauron.wlu.ca/kotsireas/teaching.html

Lecture Topics (corresponding to the 12 weeks schedule)

1. Introduction, Database System Concepts and Architecture
   • Data Models
   • Schemas
   • Instances
   • Data Independence
   • Database Languages
   CH1, CH2
2. Entity/Relationship (ER) Model
   • Entity Types
   • Entity Sets
   • Attributes
   • Keys
   • Relationship Types
   • Relationship Sets
   • Roles
   • Structural Constraints
   • Weak Enity Types
   • ER Diagrams
   • Naming Conventions
   • Binary/Ternary Relationships
   CH3
3. Extended Entity/Relationship (EER) Model
   • Subclasses, Superclasses, Inheritance
   • Specialization, Generalization, Hierarchies, Lattices
   • Union Types, Categories
   CH4
4. Relational Data Model, Relational Databases, Examples of Relational Database Management Systems (RDBMS), Relational Algebra (RA)
   • Relational Constraints
   • Relational Database Schemas
   • Update Operations
   • Select/Project (Unary) RA operators
   • Set-theoretic RA operators
   • Join/Division (Binary) RA operators
   • Aggregate Functions and Grouping
   • Outer Join
   CH5, CH6
5. Structured Query Language I (SQL)
   • Data Definition, Data Types
   • Constraint Specification
   • Basic Queries, SELECT/FROM/WHERE, Ambiguities/Aliasing, Tuple Variables, Distinct, ORDER BY
   • Set-theoretic UNION/EXCEPT/INTERSECT
   • Substring Pattern Matching, Arithmetic Operators
   • Null Values, IS, IS NOT
   • Nested Queries, Correlated Nested Queries, EXISTS, NOT EXISTS
   • Explicit Sets of Values, Attribute Renaming
   • Joined Tables, Outer Joins
   • Aggregate Functions, MAX, MIN, AVG, SUM, COUNT
   • Grouping, GROUP BY, HAVING clauses
   • INSERT/DELETE/UPDATE
   • CREATE ASSERTION, Triggers
   • Views
   CH8
6. Structured Query Language II (SQL)
   Additional SQL examples on diferrent relational database schemas
7. ER-to-Relational Mapping, EER-to-Relational Mapping
   CH7
8. Functional Dependencies (FDs), Normal Forms (NFs), Normalization
   • Redundancy, Update Anomalies, Spurious Tuples
   • Definition of an FD, Inference Rules for FDs
   • Equivalent Sets of FDs, Minimal Sets of FDs
   • First NF, Second NF, Third NF, 2NF/3NF Normalization
   • BCNF, BCNF Normalization
   CH10
9. Algorithms for Relational Database Scheme Design, Multivalued and Join Dependencies
   • Relational Decompositions
   • Attribute Preservation, Dependency Preservation
   • Nonadditive Join (Lossless Join)
   • NULL Values, Dangling Tuples
   • Multivalued Dependencies, Fourth NF
   • Join Dependencies, Fifth NF
   CH11
10. Object Databases
    • Object Identity, Object Structure, Complex Objects, Type Constructors, ODL
    • Encapsulation of Operations/Methods, ADTs, Persistence
    • Type Hierarchies, Inheritance
    • Polymorphism (Operator Overloading), Multiple Inheritance, Selective Inheritance
    CH20, CH21
11. Transactions, Schedules
    • The Lost Update Problem, The Temporary Update Problem, The Incorrect Summary Problem
    • Transactions States (BEGIN/END/COMMIT/ROLLBACK/ABORT), System Log
    • ACID properties (Atomicity, Consistency preservation, Isolation, Durability/Permanency)
    • operations in conflict (in a schedule)
    • complete schedules, recoverable schedules, cascading rollback, cascadeless schedules
    • Schedule Serializability, serial schedules, nonserial schedules, conflict-serializable schedules
    CH17
12. Concurrency Control Techniques, Database Recovery Techniques
    • protocols, locking data items
    • two-phase locking, binary locks, R/W locks
    • serializability by two-phase locking
    • deferred update, NO-UNDO/REDO algorithm
    • immediate update, UNDO/REDO algorithm
    • checkpoints, fuzzy checkpointing
    • transaction rollback
    CH18, CH19

Class Schedule

• Mon 11:30 - 12:20 Room BA111
• Wed 11:30 - 12:20 Room BA111
• Fri 11:30 - 12:20 Room BA111

Winter Semester Timetable

              |     Mon     |     Wed     |    Fri      |
              | 11:30-12:20 | 11:30-12:20 | 11:30-12:20 |
              |    BA111    |    BA111    |   BA111     |
______________|_____________|_____________|_____________|_____
              |             |             |             |
Week 1:       |   ---       |    Jan 03   |   Jan 05    |
Week 2:       |  Jan 08     |    Jan 10   |   Jan 12    |
Week 3:       |  Jan 15     |    Jan 17   |   Jan 19    |
Week 4:       |  Jan 22     |    Jan 24   |   Jan 26    |
Week 5:       |  Jan 29     |    Jan 31   |   Feb 02    |
Week 6:       |  Feb 05     |    Feb 07   |   Feb 09    |
Week 7:       |  Feb 12     |    Feb 14   |   Feb 16    |
______________|_____________|_____________|_____________|______
              |
Reading Week  |   Feb 19 - Feb 23   N o   C o u r s e s
______________|_____________________________________________
              |             |             |             |
Week 8:       |  Feb 26     |   Feb 28    |   Mar 02    |
Week 9:       |  Mar 05     |   Mar 07    |   Mar 09    |
Week 10:      |  Mar 12     |   Mar 14    |   Mar 16    |
Week 11:      |  Mar 19     |   Mar 21    |   Mar 23    |
Week 12:      |  Mar 26     |   Mar 28    |   Mar 30    |
              |  Apr 02     |    ---      |    ---      |
______________|_____________|_____________|_____________|______
              |             |             |             |

Course Requirements/Student Evaluation

• Assignment 1: 7.5%, due date: Friday, January 26, in class or by e-mail.
• Assignment 2: 15%, due date: Friday, March 9, in class or by e-mail.
• Assignment 3: 7.5%, due date: Friday, March 23, in class or by e-mail.
• Midterm Examination: 30%, date: Wednesday, February 14, in class.
• Final Examination: 40%, date:

   363  0 ........... Apr 12 (Thur)  9:00 a.m. .. Room SBE1220 

• All assignment submissions must be typesetted.
• Late assignment submissions will not be accepted and will be marked with 0.