EECS-3421B

Introduction to Database Systems

York University

Fall 2019
 

Welcome to Introduction to Database Systems, EECS-3421 Section B, for winter term 2019. Materials, instructions, and notices for the class will accumulate here over the semester.

 

Instructor: Nasim Razavi
Office: 2053 Lassonde Bldg.
Office Hours: Tue 1-3pm
Office Hours: Tue, Dec 10: 9:30am- 11:30am
e-mail: nasim@eecs.yorku.ca

 

T.A.: Wenxiao Fu
Office: 2053 Lassonde Bldg.
Office Hours:

Thursday (Dec 12) 6:00pm-7:00pm (Project 3)

Project: 1,2,3
e-mail: wxfu@eecs.yorku.ca

 

T.A.: Hoorieh Marefat
Office: TBD
Office Hours:

Wednesday (Nov 27) 11:00am-12:30pm (Cancelled)

Friday (Nov 29) 1:30pm-3:00pm Room:LAS-2031A

Project: 4
e-mail: marefat@cse.yorku.ca

 

Announcements
  • 2019-11-25 11:15pm - Project 4 due:

    Online Submission Due: by 11:59pm Monday 2 December 2019

    In-class Submission Due: by 10:00am Tuesday 3 December 2019

     

  • 2019-11-19 3:00pm - Project 3 is now available.

    Online Submission Due: by 11:59pm Thursday 21 November 2019

     

  • 2019-09-24 12:30pm - You can post your questions about project 1 on Piazza/project 1. You can also meet Wenxiao (T.A.) in her office hours (Thursday (Sep 26) 13:30-14:30, Wednesday (Oct 2) 12:00-13:0) at Lassonde #2053.

  • 2019-09-19 10:21am - E/R LiberOffice Template You can simply select and copy elements from the template and paste them into your E/R diagram in LiberOffice.

 

Course Description

Concepts, approaches and techniques in database management systems (DBMS). Logical model of relational databases. An introduction to relational database design. Other topics such as query languages, crash recovery and concurrency control.

Prerequisites:

  • general prerequisite &
  • LE/EECS 2030 3.00 or LE/EECS 1030 3.00;

Course credit exclusions:

  • LE/CSE 3421 3.00,
  • AK/AS/SC/CSE 3421 3.00,
  • AP/ITEC 3220 3.00.

Note: The general prerequisite is a cumulative GPA of 4.50 or better over all major EECS courses. (EECS courses with the second digit “5” are not major courses.)
 

Course Objectives

Students will

  • become proficient at modelling databases at a conceptual and physical level of design.
  • be able to develop database schemas that enforce data integrity.
  • also become knowledgeable in the creation, altering, and manipulation of tables, indexes, and views using relational algebra and SQL.

Specific topics to be covered include the following.

  • the relational model
  • relational algebra & calculus
  • the SQL query language
  • conceptual design and the E-R Model
  • transaction management, concurrency control, & recovery
 

Learning Outcomes

After successful completion of the course, students are expected to be able to do the following.

  • Model databases proficiently at conceptual and logical levels of design. Use the entity-relationship model (E-R) and E-R diagrams with extensions.
  • Develop relational database schemas which respect and enforce data integrity represented in E-R diagrams.
  • Implement a relational database schema using Structured Query Language (SQL), to create and manipulate tables, indexes, and views.
  • Create and use complex queries in SQL.
  • Write database application programs with an understanding of transaction management, concurrency control, and crash recovery.

 Text Book

book title : Database Systems: The Complete Book
authors : H. Garcia-Molina, J.D. Ullman, & J. Widom
date : 2009 (2nd edition)
publisher : Pearson / Prentice Hall
ISBN : 0-13-187325-3
 

 Course Requirements

Four Projects 25%
Two Tests each 20%
Exam 35%
 
 

Projects

Design Project 1 5%
Schema Project 2 5%
Queries Project 3 10%
Application Project 4 5%
 
 

Tests & Final Exam

There will be minimum one test during the term.

There will be a final exam scheduled during the term's final-exam period that will be comprehensive for all the material covered during the course.

 

Lecture Notes

  1. The Game
    1. introduction[pdf]
    2. database models[pdf]
  2. Design
    1. the E/R model[pdf]
      1. modelling with E/R
      2. translating E/R to relational
    2. design theory[pdf]
      1. functional dependencies
      2. decompositions
      3. the normal forms
  3. Queries
    1. conceptual query languages (relational algebra)[pdf]
    2. SQL
      1. SQL: queries, etc.[pdf]
        1. the basics
          1. select-from-where
          2. multi-relation queries
          3. Sub-queries
        2. Advanced
          1. aggregation
          2. insert / delete / update
        3. example SQL queries
      2. constraints & triggers [pdf]
      3. authetication [pdf]
  4. Applications
    1. the system
      transaction management, concurrency control, crash recovery, etc.
      1. database system overview [pdf]
      2. transaction management (& concurrency control)
      3. crash recovery [pdf]
    2. application programming [pdf]
      1. embedded SQL for C & C++ (local / DB2)
      2. JDBC (CLI)
      3. SQL/PL: a small example [pdf]

Read the textbook chapters listed in the day-by-day in the schedule. (Ideally, do the reading before that day.)

 
 

Schedule

 
Week Day Topic Read Due
 0 Fr, Sep 6 Introduction  Ch1  
 1
Tu, Sep 10
Fr, Sep 13
Database Models
ER-Model: Basics
Ch2 [1-3]
Ch4 [1-4]
 
 2
Tu, Sep 17
Fr, Sep 20
ER-Model: Advanced
...
   
 3
Tu, Sep 24
Fr, Sep 27
Translating to Relational
Design Theory: Functional Dependencies
Ch4 [5,6] & Ch7 [1.1-2]
Ch3 [1-5]
 
 4
Tu, Oct 01
Fr, Oct 04
Decomposition
The Normal Forms
P#1
 5
Tu, Oct 08
Fr, Oct 11
...
Test#1
 
    Fall Reading Week    
 6
Tu, Oct 22
Fr, Oct 25
Relational Algebra
...
Ch5
 
 7
Tu, Oct 29
Fr, Nov 01
SQL: Basics   
SQL: Advanced
Ch6 [1-3]
 
 8
Tu, Nov 05
Fr, Nov 08
...
SQL Constraints & Triggers
Ch6 [4,5]
Ch7
 
 9
Tu, Nov 12
Fr, Nov 15
SQL: OLAP
Test #2
Ch22 [1,2]
 
 10
Tu, Nov 19
Fr, Nov 22
Transactions
Views, Indexes
Ch6 [6]
Ch8
 
 11
Tu, Nov 26
Fr, Nov 29
Database Connections
Cursors, etc.
Ch9 [3,5-7]
 
 12 Tu, Dec 03 NoSQL, Semi-Structured Ch11 [1,2] & Ch12 [1,2]  
  Tu, Dec 04 Fall Study Day    
  Dec 05-20 Winter Examinations    
 
 

Tests, Exams, & Attendance Policies

The tests must be taken when scheduled, unless the student has extenuating circumstances (e.g., medical). The student must obtain approval from the instructor. (The student need not obtain a medical report for medical situations; but a reasonable case must be made.) The weight of a test that must be missed will be transferred onto the final exam.

Class attendance is important as the student will have an opportunity to ask for clarification of course and text material. There will be problem solving sessions during class period so that students gain experience applying the theory in practice. However, attendance itself is not part of the grade or otherwise enforced.
 

E-Mail Policy

Please use a York University account when e-mailing me (with regards to the course), so that I can know who it is. Please include [3421B].