CS 430/630 Database Management Systems

 Spring 2017

Class meets MW 7:00-8:15 in M-1-409

  1. Syllabus
  2. Schedule
  3. Instructor & Contact
  4. Resources & Links

Course Objectives

The course will introduce fundamental concepts in relational databases, such as: the relational model and relational algebra; the Standard Query Language (SQL); database design theory; conceptual design; database application development; database security.

Prerequisites

Students registering for CS430/630 must have previously completed courses in data structures and discrete mathematics. Also, they are required to have working knowledge of Java and C programming languages, as well as familiarity in using the UNIX operating system.

Topics Covered

Textbooks

R. Ramakrishnan, J. Gehrke (R&G), "Database Management Systems", 3rd edition. Available in .pdf form online.
J. Murach (M), "Oracle SQL and PL/SQL for Developers", 2nd edition. Please purchase a print copy (under $50).
Note: No electronic devices are allowed in open-book exams, so be prepared to print out parts of online books

Attendance Policy

Class attendance is strongly encouraged, and students are expected to participate actively in class by asking and answering questions. Occasional classes will have "labs", i.e., practice work in class, with credit for completed papers. In case of missed class, students are responsible to get up-to-date with course materials and announcements available at the class web page www.cs.umb.edu/cs630.

ACCOMMODATIONS: The University of Massachusetts Boston is committed to providing reasonable academic accommodations for all students with disabilities.  This syllabus is available in alternate format upon request. Students with disabilities who need accommodations in this course must contact the instructor to discuss needed accommodations. Accommodations will be provided after the student has met with the instructor to request accommodations. Students must be registered with the Ross Center for Disability Services, UL 211, www.ross.center@umb.edu , 617.287.7430 before requesting accommodations from the instructor.

ACADEMIC CONDUCT: It is the expressed policy of the University that every aspect of academic life—not only formal coursework situations, but all relationships and interactions connected to the educational process—shall be conducted in an absolutely and uncompromisingly honest manner. The University presupposes that any submission of work for academic credit indicates that the work is the student’s own and is in compliance with University policies. In cases where academic dishonesty is discovered after completion of a course or degree program, sanctions may be imposed retroactively, up to and including revocation of the degree. Students are required to adhere to the Code of Student Conduct, including requirements for academic honesty, delineated in the University of Massachusetts Boston Bulletin, found at:  http://www.umb.edu/life_on_campus/policies/community/code.

Topics in More Detail

NOTE: Get a Linux account for cs430/630 by running apply for cs630, even if you already have a UNIX/Linux account here. This should be done by Friday Jan. 27 to list your username for an Oracle account. See the class web page at http://www.cs.umb.edu/cs630. Also read Access to cs.umb.edu systems from offsite.

Here "R&G" means Ramakrishnan and Gehrke, M means Murach. "6pp" means formatted as 6 slides/page
Password for slides is available in class, or by email to eoneil.
Topic Reading
Course Overview and Introduction to Relational Model
Mon., Jan. 23 Lecture 1 (6pp)
R&G: Chapter 1 - all, Chapter 2 - 2.1, 2.2, 2.3, Chapter 3 to top of pg. 62, plus subsection 3.2.1 (keys)
M: Chapter 1 to page15
Relational Algebra
Wed., Jan. 25 Lecture 2 (6pp)

Fri., Jan. 27: first round of Oracle accounts have been made based on existent class accounts. Login on topcat or pe07 to try yours out. See Development Setup below for more information. Homework 1 is available: see link below.
Mon., Jan. 30 Lecture 3 (6pp) Lab sheet (Solution
Wed., Feb. 1, noon: more Oracle accounts made
R&G: Chapter 4 - 4.1, 4.2
Introduction to SQL.
Wed., Feb. 1: Lecture 4 (6pp)
Sat., Feb. 4: system pe07 is down, so just use topcat for now.
Sat, 11:45: pe07 is up and mysql accounts have been created: try
pe07$ mysql -u joe -D joedb -p
R&G: Chapter 5 - 5.1-5.3
M: Chapter 1, Chapter 3, 4
Intro to SQL - More on Constraints, Queries
Mon., Feb.6 Lecture 5 (6pp)  
M: Chapter 10
SQL Nested Queries
Wed., Feb. 8 Lecture 6 (6pp)
Mon., Feb. 13 Snow day
R&G: Chapter 5 - 5.4 to (Q8)
M: Chapter 6
SQL Aggregate Queries
Wed., Feb. 15 Lecture 7 (6pp)

Wed. Feb. 22 SQL Lab in class. You can bring your laptop if you want help with your setup, but it's not needed for the SQL exercises.
Lab sheet (Solution)

Mon., Feb. 27 Lecture 8 (6pp) More Aggregate Examples, Nulls

R&G: Chapter 5 - 5.5
M: Chapter 5
Division in Relational Algebra and SQL
Wed., Mar. 1 Lecture 9(6pp) Division
R&G: Chapter 4 - 4.2.5, Chapter 5 - 5.4 Q9 on page 150
Outer Joins, Create Table, insert, update, delete, SQL Constraints
Mon., Mar. 6 Lecture10(6pp) Outer Joins, DML, Constraints
R&G: Chapter 5 - 5.6
Chapter 3 - 3.1-3.3
M: Chapter 7. 8, 10
The Entity-Relationship (ER) Model
Mon., Mar. 6 Lecture10a(6pp) Murach Chap. 9 on Designing DBs
Wed., Mar. 8 Lecture11(6pp) ER Model
Mon., Mar. 20 Lecture12(6pp) ER Model, continued.
R&G: Chapter 2 - all except 2.4.5, 2.5.3, 2.5.4, 2.6-2.8; Chapter 3 - 3.5 (up to 3.5.5)
M: Chapter 9
Wed., Mar 22 Midterm Review (6pp) Practice Midterm Exam (Solution)
Midterm Exam Monday, Mar. 27 
Makeup Midterm Exam Wed., Apr. 5 5:20-6:50 W-2-200 exam solution

Views
Wed., Mar. 29 Lecture13(6pp) Views
R&G: Chapter 3: 3.6 and 3.7
M: Chapter 11
Database Application Development - Intro, JDBC, Transactions
Mon., Apr. 3 Intro, JDBC Lecture14(6pp)
Wed., Apr. 5 JDBC Lecture15(6pp)
Mon., Apr. 10 JDBC Lecture16(6pp)
Wed., Apr. 12 Transactions in JDBC Lecture17(6pp)

R&G:
Chapter 6.1
R&G: Chapter 6.2, 6.3
R&G: Chapter 16 to pg. 529 (Intro Transactions)
Database Application Development - Oracle PL/SQL intro
Wed., Apr. 19 Lecture 18(6pp) Lecture18a(6pp)
PL/SQL transactions and stored procedures
Mon., Apr. 24 Lecture 19(6pp)
M: Chapter 13, 15
M: Chapter 14 (transactions in PL/SQL)
Schema Refinement and Normal Forms
Wed., Apr. 26 Lecture 20(6pp)
M: Chapter 9 from pg. 298
R&G:
Chapter 19 - 19.1-19.3 
BCNF and 3NF. Decompositions
Mon., May 1 Lecture 21(6pp)
Wed., May 3: Lab on FDs and Decompositions, in class (3 pts) Lab Sheet Solution
R&G: Chapter 19 - 19.1-19.6 Solved Exercises to try: 19.3, 19.5, 19.7
Security and Authorization
Lecture 22(6pp) Lecture 22a(6pp)
R&G: Chapter 21
M: Chapter 12
Course Evaluation. Final Exam Review.
Final Review(6pp)

Practice Final Exam (Solution)

Final Exam: Wed., May 17  6:30-9:30 in W-1-409

Instructor: Prof. Betty O'Neil

TA: Mohammad Hadianpour