CS 430/630 Database Management Systems

Fall, 2018

MW 7:00-8:15 in W-1-031


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.


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


  1. R. Ramakrishnan, J. Gehrke (R&G), Database Management Systems, 3rd edition, 2002. Available in .pdf form online.
  2. J. Murach (M), Oracle SQL and PL/SQL for Developers, 2nd edition, 2014. 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

Recommended book: just being published (available 9/30/18 on Amazon), covers nearly all of R&G topics, plus newer topics: W. Lemahieu, S. vanden Broucke, B. Baesens, Principles of Database Management, Cambridge U. Press, 2018. Videos


Simple point system. Midterm exam: 100 points, Final exam: 150 points, Assignments: various, about 100 points total. The exams are open-print-books, posted solutions, your own assignment papers, closed electronic devices.

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. See detailed directions at  https://www.cs.umb.edu/sp/resources/other/faqs/#FAQ02, reachable from www.cs.umb.edu by clicking RESOURCES, looking for the Common section, and clicking Answers to Frequently Asked Questions, then selecting FAQ02. You can get help in S/3/158. This should be done by the end of the first week 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
Wed., Sept. 5 Lecture01(6pp)  Also see above NOTE about getting accounts.
See DatabaseSetup for more info. 
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
Mon., Sept. 10 Lecture02(6pp) Relational Algebra
noon: Oracle accounts made for 26 students with class accounts
Tues 5pm: 12 more Oracle accounts made.

Wed., Sept. 12 Lecture03 (6pp) RA Queries, Relational Algebra lab, in class
RA Lab sheet (Solution)
Fri 11am: 5 more, 6 to go
Sun 2pm: 1 more, 5 to go
R&G: Chapter 4 - 4.1, 4.2
Intro to SQL.
Mon., Sept. 17 Lecture04 (6pp)
Tues.: mysql accounts made for all but 5 students
Wed., Sept. 19 Lecture05 (6pp)
Thurs. 11am: Oracle and mysql accounts are made for all but 1 student
R&G: Chapter 5 - 5.1-5.3
M: Chapter 1, Chapter 3, 4
M: Chapter 10
SQL Nested Queries
Mon., Sept. 24 Lecture06 (6pp)

R&G: Chapter 5 - 5.4 to (Q8)
M: Chapter 6
SQL Aggregate Queries
Wed., Sept. 26 Lecture07 (6pp) Murach Chap. 5 (6pp)

Mon., Oct. 1 SQL Lab, in class (5 points) hw2 info (6pp)
SQL Lab Sheet (Solution)

More on Aggregates, Nulls
Wed., Oct. 3 Lecture08 (6pp) (with after-class added notes on yelp_db's business.stars values, new slide 23)
Fri., Oct 5: piazza invitations should be in your preferred email inbox (.forward address or for 6 students without .forward files, info sheet entry)
Let me know by email if you haven't received an invitation.

Mon., Oct. 8 Holiday
Wed., Oct. 10 Lecture9 (6pp) Division in RA and SQL, Using the JOIN keyword in SQL
R&G: Chapter 5 - 5.5
M: Chapter 5
Outer Joins, Create Table, insert, update, delete, SQL Constraints
Mon., Oct. 15 Lecture10 (6pp)
R&G: Chapter 5 - 5.6
Chapter 3 - 3.1-3.3
M: Chapter 7. 8, 10
The Entity-Relationship (ER) Model
Mon., Oct. 15 Lecture10a (6pp) Intro
Wed., Oct. 17 Lecture11 (6pp)
Mon., Oct. 22 Lecture12 (6pp)
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., Oct. 24 Lecture13 (6pp)

R&G: Chapter 3: 3.6 and 3.7
M: Chapter 11
Mon., Nov. 5 Midterm Review (6pp)
Midterm Exam Wed., Nov. 7 covering above material (including Views)
Practice Midterm Exam (solution)

Database Application Development - Intro, JDBC, Transactions
Mon., Oct. 29 Lecture14 (6pp)
Wed., Oct. 31 DB Access using .NET (6pp) Intro JDBC Lecture15 (6pp)
Wed, Nov. 14 Lecture16 (6pp) (with hw4 notes) Java Exceptions (6pp)
Mon., Nov. 19 JDBC, Transactions Lecture 17 (6pp)
Wed., Nov. 21 no class
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
Mon. Nov. 26 Lecture18 (6pp), with transactions: Lecture18b (6pp)
M: Chapter 13, 15
M: Chapter 14 (transactions in PL/SQL)
Schema Refinement and Normal Forms
Wed., Nov. 28 Lecture19 (6pp)
M: Chapter 9 from pg. 298
Chapter 19 - 19.1-19.3 
BCNF and 3NF. Decompositions
Mon., Dec. 3 Lecture20 (6pp
Wed., Dec. 5 Normalization Lab, in class (4 points) Lab (Solution) NormalizationSummary (6pp)
R&G: Chapter 19 - 19.1-19.6 Solved Exercises to try: 19.3, 19.5, 19.7
Security and Authorization
Mon., Dec. 10 Lecture21 (6pp) Lecture21a (6pp)
R&G: Chapter 21
M: Chapter 12
Intro to NoSQL Databases (if time) Sorry, no time. When you have time, read good recent intro. Also note that important NoSQL databases (MongoDB, DynamoDB, and others) now have ACID transactions. Lemahieu et al (recommended reference above) has Videos with one for Chap. 11 on NoSQL
Wed., Dec. 12 Final Review (6pp), teacher evaluations
Final Exam Wed., Dec. 19, 6:30-9:30 in room W-2-158 (note change)
Practice Final Exam (Solution)

Instructor: Prof. Betty O'Neil

TA: Mohammad Hadianpour

Piazza Site: post your questions here

Local Resources
Student Information Form (in case you missed the first class) 
Web Resources