CS 430/630 Database Management Systems

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

• The Relational Data Model and Relational Algebra
• Standard Query Language (SQL)
• The Entity-Relationship Data Model
• Database Design Theory
• Database Application Development
• Database Security

Textbooks

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 published (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

Grading

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 here.

Instructor: Prof. Betty O'Neil

• Email: eoneil at cs dot umb dot edu (preferred contact)
• Office: Science Building, 3rd Floor, Room 169 (S-3-169)
• Office Hours: MW 3:30-3:45, 5:30-6:45pm or by appointment

TA: Brian Le, Brian.Le002 at umb dot edu

Textbook Resources:

• For R. Ramakrishnan, J. Gehrke, "Database Management Systems", 3rd edition.
• Textbook website (includes solved exercises)
• For Murach, "Oracle SQL and PL/SQL for Developers", 2nd edition:
• Textbook website (includes download with solved exercises, etc.)

Homework Assignments: 

Homework 1 Oracle, keys, relational algebra. Due Mon., Sept. 23 in class, on paper. Solution
Note there was a mistake in the answer key given to the grader that resulted in some papers losing credit for good answers to problem 8, parts 1-3 (cs630 only). I'll fix your homework grade in office hours or after class Monday.
Homework 2 Mysql, foreign keys, SQL with subqueries, group by, having. Using the Yelp DB. Due Tues., Oct. 8 by midnight in your cs630/hw2 directory. Solution
Homework 3 RA/SQL Division, Outer Join, E-R, due Wed., Oct. 30 in class and in your cs630/hw3 directory.
   Oct. 14: updated to include the text of the problems from Murach, for problem 2. Solution
Homework 4 Intro to JDBC, due Tuesday midnight Nov. 19 in directory cs630/hw4. Solution (zip)
Homework 5 JDBC, PL/SQL, due Tuesday midnight Dec. 3 in directory cs630/hw5. Solution (zip)
Homework 6 FDs, Normalization, Views, Access Control, due Wednesday, Dec. 11 (last class) in class, on paper. Solution

Local Resources

• Access to cs.umb.edu systems from offsite Logging in and transferring files.

• Student Information Form (in case you missed the first class) 

• Development Setup Installing Java on your home system.
• Database Setup Setting up access for Oracle and Mysql, setting up the needed tunnels to our database servers. (Putty tunnels info revised Nov. 24)
• Murach tables and queries:  invoices, vendors, invoice_line_items, general_ledger_accounts, etc.

• Previous cs430/cs630 home page

Web Resources

• Java We are using version 7 or 8. JDK API
  
• Relational Algebra examples (solved problems) at Stanford
• SQLPlus quick guide
• Intro to Embedded SQL (from Stanford Infolab)
• PL/SQL User's Guide and Reference (from Oracle)
• Intro to PL/SQL (from Stanford Infolab)
• A PL/SQL Tutorial
• Error Handling in PL/SQL
• Oracle Error Codes Lookup 

• Oracle: We are using version 12c. Sign up (free) for Oracle Technical Network. Oracle 12c documentation (SQL) Oracle error messages info.

• MySQL MySql Setup We are using version 5.6 on topcat.cs.umb.edu. docs (SQL)