CPS 430/542: Database Management Systems/Fall 2007

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 W 12:00pm-1:00pm, and by appointment.

Teaching assistant: Philip Pfeiffer, AN 152, 229-3832, pfeiffpe at notes dot udayton dot edu, OHs: T Th 10:00am-12:00pm, 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, lecture notes, and reading assignments (to be completed prior to class):
  1. Introduction to database systems, course outline, and data models (Ch1): Aug 22

  2. Data modeling: entity-relationship (E/R) and object-oriented data models (Ch2, §§4.1-4.3):
    1. essential E/R elements (§2.1): Aug 27
    2. E/R modeling constraints (§2.3) and weak entity sets (§2.4): Aug 29
    3. E/R design principles (§2.2) and examples: Sep 5 10
    4. object-oriented data modeling and the Object Definition Language (ODL; §§4.1-4.3): Sep 10 12

  3. Relational and object-relational data models (Ch3, §§4.4-4.5, 10.1-10.3):
    1. essential relational elements (§3.1) and converting E/R diagrams to relational designs (§3.2): Sep 19
    2. converting subclass structures to relations (§3.3): Sep 24
    3. functional dependencies (FDs; §3.4) and rules of FD's (§3.5): Sep 24
    4. FD and attribute closure algorithms (§3.5): Sep 26
    5. canonical cover (§3.5): Oct 1
    6. databases and logic (first-order predicate logic: Horn clauses, resolution, and unification; §§10.1-10.3): Oct 3
    7. databases and logic (PROLOG: facts, rules, and goals; §§10.1-10.3): Oct 8
      [A Brief Introduction to PROLOG, installing & using SWI-PROLOG, logic programming resources, Prolog Programming A First Course]
    8. normalization: BCNF (§3.6): Oct 10
    9. normalization: 3NF, multivalued dependencies, and 4NF (§§3.6-3.7): Oct 15 17
    10. converting ODL designs to relational designs (§4.4) and the object-relational data model (§4.5): Oct 22
      11. Exam II: Oct 24 (closed book, closed notes; practice problems)

  4. Query languages: relational algebra, SQL, Datalog (Ch5,6,10):
    1. primitive query operators (§§5.2, 6.1, 10.1-10.2): Oct 29 31
    2. joins (§§5.2, 6.2, 10.2): Nov 5
    3. example queries: Nov 7
    4. defining and populating a relation schema in SQL (§§6.5-6.6): Nov 12
      [using Oracle 8 on the CPS Suns, notes on Oracle from the Stanford Univ. DB group, help on Oracle from UMBC]
    5. subqueries (§6.3): Nov 14
      6. Exam III: Nov 19 (closed book, closed notes; practice problems)
    6. extended operators (grouping and aggregation) (§§5.3-5.4, 6.4): Nov 26
    7. views (§6.7) [mySQL vs. standard SQL (coutesy Ullman)]: Nov 28
    8. recursion in Datalog and SQL (§§10.3-10.4): Dec 3
    9. indices (§6.6), and enforcing (key and foreign-key) constraints in relational algebra (§5.5) and SQL (§§7.1-7.2, 8.1): Dec 5

  5. Web modeling: semistructured data, XML, XSLT, and RDF (§§4.6-4.7), and graduate students present their semester-long project: Dec 5

    6. Final exam: Dec 10, 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/29, due 9/5.
  2. coverage: Ch2, §§4.1-4.3; assigned 9/5, due 9/12; addendum.
  3. coverage: Ch2, §§3.1-3.4; assigned 10/3, due 10/10; addendum.
  4. coverage: §§3.1-3.5; assigned 10/10, due 10/17; addendum.
  5. coverage: §§3.6-3.7; assigned 10/31, due 11/7; addendum.
  6. coverage: §§5.2, 6.1-6.2, 10.1-10.2; assigned 11/7, due 11/14; addendum.
  7. coverage: §§5.3, 6.1-6.4, 6.7, 10.4; assigned 11/28, due 12/5.
Projects:
  1. coverage: §§3.4-3.5; assigned: 10/3; due: 10/31; specification.
  2. coverage: Ch6; assigned: 11/14; due: 11/28, 12/5; consists of two parts (49+51=100 points); the first part is available in GOAL and consists of two subparts (28+21=49 points); also include in your submission the SQL statements to define each relation schema (there are a total of 6 of them) as well as statements to INSERT at least one tuple into each relation; therefore, your project submission for first part will consist of 6 relation schema definitions, at least 6 insert statements, and 7 queries; the second part is the addendum.
Grades: posted in WebCT

Software tools (for drawing E/R diagrams): Xfig (X11 application) | Dia (X11 application) | OmniGraffle (for Mac OS X) | SmartDraw | ConceptDraw | ER/Studio | Visible Analyst | Grandite's SILVERRUN ModelSphere

Accounts: CPS account access @ home | UNIX account access | Using Oracle 8 on the CPS Suns | Using PROLOG | Keeping your password safe | A beginner's guide to effective e-mail
If you are unable to log into your 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 at the back of AN 131 and his office hours are M-F 8:30am-4:30pm.

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.