CPS 430/542: Database Management Systems/Fall 2006

CPS 430/542 (3 sem hrs) provides an introduction to database systems, with an emphasis on modeling and programming databases. Students can expect illustration of E/R modeling, theoretical coverage of the relational model, practical exposure to programming a commercial DBMS, and discussion of how databases interface with the web. Cursory coverage of object-oriented and object-relational databases, multimedia databases, data warehousing, and data mining; database system implementation is beyond the scope of this course. Queries will be presented in relational algebra, SQL, and Datalog; programming will be done in SQL under Oracle in the UNIX programming environment. This course assumes no prior knowledge of database systems.

Syllabus

Pre-requisite: CPS 350 (Data Structures and Algorithms) with a minimum grade of C for students enrolled in CPS 542.

Meeting times: M W 4:30pm-5:45pm, MH 205

Instructor: Dr. S. Perugini, AN 145, 229-4079, perugisa at udayton dot edu, OHs: M T W Th 5:45pm-6:45pm and by appointment.

Teaching assistant: Philip Pfeiffer, MH 21A, 229-2199, pfeiffpe at notes dot udayton dot edu, OHs: T W Th F 1:45pm-2:45pm and by appointment.

Required textbook: [FCDB] A First Course in Database Systems by J.D. Ullman and J. Widom. Prentice Hall, Second edition, 2002. ISBN: 0-13-035300-0 (and associated GOAL account).

Course outline and reading assignments (to be completed prior to class):
  1. Introduction to database systems, course outline, and data models (Ch1): Aug 21

  2. Entity-relationship and object-oriented data models (Ch2, §§4.1-4.3):
    1. essential E/R elements (§2.1): Aug 23
    2. E/R modeling constraints (§2.3) and weak entity sets (§2.4): Aug 28
    3. E/R design principles (§2.2) and examples: Aug 30
    4. Object-oriented data modeling and the Object Definition Language (ODL) (§§4.1-4.3): Sep 6

  3. Relational and object-relational data models (Ch3, §§4.4-4.5, 10.1-10.3):
    1. essential relational elements; converting E/R diagrams to relational designs (§§3.1-3.2): Sep 11
    2. converting subclass structures to relations (§3.3): Sep 13
    3. Exam 1: Sep 18 (closed book, closed notes; practice problems)
    4. functional dependencies (FDs) and rules of FDs (§§3.4-3.5): Sep 20
    5. attribute and FD closure algorithms; canonical cover (§3.5): Sep 25 27
    6. normalization, BCNF, and 3NF (§3.6): Oct 2, 4
    7. multivalued dependencies and 4NF (§3.7): Oct 11
    8. databases and logic (PROLOG) (§§10.1-10.3): Oct 16 18
    9. Exam 2: Oct 23 (closed book, closed notes; practice problems)
    10. converting ODL designs to relational designs (§4.4), object-relational model (§4.5): Oct 25 30

  4. Query languages: relational algebra, SQL, Datalog (Ch5, 6, 10):
    1. primitive operators and joins in relational algebra, SQL, and Datalog (§§5.1-5.2, 6.1-6.2, 10.1-10.2): Nov 1
    2. example queries in relational algebra, SQL, and Datalog: Nov 6 8
    3. subqueries and extended operators (grouping and aggregation) (§§6.3, 5.3-5.4, 6.4): Nov 13
    4. Exam 3: Nov 15 (closed book, closed notes)
    5. defining and populating a relation schema in SQL (§§6.5-6.6): Nov 20
    6. views and indices (§§6.6-6.9): Nov 27
    7. recursion in Datalog and SQL (§§10.3-10.6): Dec 4
    8. enforcing (key and foreign-key) constraints in relational algebra and SQL (§§5.5-5.7, 7.1-7.2, 8.1): Dec 6

  5. Web modeling: semistructured data and XML (§§4.6-4.8); graduate students present their semester-long project: Dec 6

  6. Final exam: Dec 11, 4:30pm-6:20pm, MH 205 (comprehensive, closed book, closed notes)
Homeworks (50 pts each; accessed and submitted through GOAL; see Gradiance student guide for more information):
  1. coverage: Ch2; assigned 8/23, due 8/30; addendum.
  2. coverage: Ch2, §§4.1-4.3; assigned 9/6, due 9/13; addendum.
  3. coverage: Ch2, §§3.1-3.4; assigned 9/20, due 9/27; addendum.
  4. coverage: §§3.1-3.5; assigned 9/27, due 10/4; addendum.
  5. coverage: §§3.6-3.7; assigned 10/13, due 11/1; addendum.
  6. coverage: §§5.2, 6.1-6.2, 10.1-10.2; assigned 11/6, due 11/13; addendum.
  7. coverage: §§5.3, 6.1-6.4, 6.7, 10.4; assigned 11/29, due 12/6; addendum.
Projects:
  1. coverage: §§3.4-3.5; assigned: 10/11; due: 10/30; specification.
  2. coverage: Ch6; assigned: 11/6; due: 11/29; available in GOAL; consists of two parts (50 pts each); also include in your submission the SQL statements to define each relation schema (there are a total of 6 of them) as well as a statement to insert at least one tuple into each relation; therefore, your project submission will consist of 6 relation schema definitions, at least 6 insert statements, and 11 queries.
Grades: WebCT

Software tools (for drawing E/R diagrams): Xfig | Dia | SmartDraw | ConceptDraw | ER/Studio | Visible Analyst | Grandite's SILVERRUN ModelSphere | Gershwin (developed by Monash University)

Accounts: CPS account access @ home | UNIX account access | Using Oracle 8 on the CPS Suns | Using PROLOG | CPS labs hours | Keeping your password safe | A beginner's guide to effective e-mail
If you are unable to log into you CPS (Windows or UNIX account) or if you forget your CPS (Windows or UNIX) account password, contact the CPS systems administrator, Mr. Tramontana, at tramonjr at notes dot udayton dot edu or 229-3835, and be as specific as possible. Mr. Tramontana's office is located in the back of AN 131 and his office hours are M T W Th F 8:00am-5:00pm.

Readings and resources: [FCDB] webpage (filled with several helpful resources, including solution sketches to starred exercises) | Gradiance student guide

Helpful links: UD academic calendar | UD student handbook | UDit policies

Feedback: Dr. Perugini welcomes any feedback you may have on the style of the lectures, the concepts presented in class, the course webpage, homeworks, projects, deadlines, course and grading policies, or your general experience in the course.