CS 320: Database Management Systems 2018

Fall 2018

Welcome to Database Management Systems!

All the homework/labs/lectures will be distributed on Moodle.

We meet TR, 2:45-4 pm, AEC 513.

Course Description

Course Description

This course examines the organization, design and implementation of database management systems. Together, we will examine how important database systems are to our everyday activities, how they contribute to society, and how they enrich the emerging field of data science. You will learn to design and build database applications, and leave the class with the skill needed to improve your understanding and use of databases on a continuing basis.


Types of Databases We Will Study
Relational Databases and Online Transaction Processing



No SQL Databases




Instructor Information(back up↑)

  • Instructor: Dr. Joann J. Ordille
  • Email: ordillej at lafaytte.edu
  • Office: 522 AEC
  • Office Phone: (610) 330-5416
  • Office Hours: M 5-6 pm, T, Th 4-5:30. Please email me if you would like to meet at any other time. Also, if my door is open, feel free to drop in.


Useful Links(back up↑)

Gradiance is our online learning system. Gradiance provides exercises with guidance when you choose an incorrect answer. Create your account using the code I emailed you.

This w3cSchools.com course will guide you through SQL features and let you try them as you go. As with any language, computer or natural, practice is key to internalizing the language. This site also includes tutorials on HTML, CSS, JavaScript, Bootstrap and a wealth of web technologies.  lynda.com (see below) also provides SQL tutorials.

  • lynda.com

lynda.com is available from your my Lafayette page. It provides many helpful tutorials. I recommend the “Ruby on Rails 5 Essential Training” and the “D3.js Essential Training for Data Scientists.” You can use lynda to help fill in background you might need for your project. You may also find the courses there on Ruby and Bootstrap helpful.

HackerRank.com provides progressively more difficult SQL practice problems.  It provides an environment where you can simulate the experience you will have in the lab exam.  It’s also a great place to practice various programming skills in preparation for interviews.

  • Papers published in the computer science literature as announced in the syllabus or on Moodle.

We will read several papers together to broaden our perspective on databases. Reading computer science papers is a primary way to keep up with the state of the art once you graduate. You will learn approaches to reading a computer science paper that will increase your understanding of it.

  • Moodle

We will use Moodle extensively. In particular, detailed information for class activities and assignments will be available weekly. Please check Moodle frequently, and whenever you receive an email notification to do so.

Course Information

Course Information(back up↑)

  • Web Page:http://sites.lafayette.edu/ordillej/cs-320-database-management-systems-syllabus-fall-2018/
  • Required Textbooks:
    • Database Systems: the Complete Book, 2nd ed. by Hector Garcia-Molina, Jeffrey D. Ullman and Jennifer D. Widom. (ISBN: 978-0-13-187325-4), Pearson.
    • This book is on reserve in the library. Before purchasing the Kindle version of this book, please note that the Kindle license is limited to ONE device.
    • Seven Databases in Seven Weeks, 2nd ed. by Luc Perkins, Eric Redmond and Jim R. Wilson. (ISBN: 978-1680502534), Pragmatic Bookshelf.
    • This book is also available for free online if you are an ACM Member. ACM Membership costs students $19 per year and gives you access to many useful computer science books for free, including the O’Reilly line of books.  ACM is the professional association for computer science.  It publishes a monthly magazine about new things in the field and hot topics, and a monthly magazine specifically for students.   It also publishes research papers and sponsors conferences.  Recently, it developed quite a good program of free tutorials and books online.  You can learn more about the ACM on the ACM website and in the flyer that encourages students to join.  You can join as a student using this online application form. In addition, existing ACM members can win prizes if they recruit others to join.  See the ACM Embassador’s Page for more information.
  • Recommended Textbook:

Next Generation Databases: NoSQL, NewSQL, and Big Data, by Guy Harrison. (ISBN: 978-1484213308), Apress.

This book provides an historical perspective on the development of many new (post-relational) database technologies, a look into why they were created and are so useful.  Some material from my lectures will come from this book, but the lecture material will be self-contained, so the book is not required.  This book is also available for free online if you are an ACM Member.  See the description for the previous book for information on becoming an ACM member.

  • Prerequisites:

    Students are required to complete CS 205 (Software Engineering) successfully before registering for this course. Software engineering techniques will be used in your database design and development project. Students are also required to co-register in CS 202 (Analysis of Algorithms). Algorithms studied in CS 202 are used in database systems, and the efficiency of various database algorithms will be discussed in this course.

Course Goals and Outcomes

Course Goals and Outcomes(back up↑)
Course Goals:

The goals of this course are to convey:

  • Understanding of important database design principles with a focus on efficiency and extensibility.
  • Functional understanding of the Structured Query Language (SQL) for the creation and manipulation of databases.
  • Skill in developing a database management system starting from a set of requirements.
  • Experience in developing a substantial database design, constructed as a group working over the majority of the semester.
  • Effectiveness in reading and evaluating papers in computer science.

Course Outcomes: After successfully completing this course, students will be able to:

  • Apply knowledge of computing and mathematics appropriate to database systems and to the computer science discipline.
  • Design, implement, and evaluate a database system to meet desired needs.
  • Function effectively on a team to accomplish a common goal.
  • Engage in continuing professional development in database systems and the computer science discipline.

Assignment Types

(back up↑)Assignment Types

Class Attendance: Class attendance is critical to success. In addition to instruction, we will engage in a variety of software engineering practices, e.g. the scrum of scrums, to assist in the design and development of the group projects. Every effort should be made to arrive on time and remain attentive for the entire class. In addition to unexcused absence, a pattern of late arrival, early departure or inattentiveness will be considered a violation of the attendance requirement. For every unexcused absence, you will lose 0.5 points from your final grade up to a total of 3 points. If you have a dean’s excuse or a coach’s letter for a particular day, you will be excused from class.

Class Participation: Class participation includes asking or answering questions, expressing an opinion about a topic of discussion, meeting with me during office hours, and reporting on activities in a group project, a reading group, or other group activity. All sincere efforts to participate are admired, so don’t worry, just speak up. You are even welcome to express an opinion different than mine. All types of participation count except participation that shows you failed to prepare for class. For example, asking: “Who is Ada Lovelace?” when the assignment was to read the Countess of Lovelace’s biography would not count as class participation.  But, it’s always better to ask than to sit there in the dark.

Reading Groups: Before the second class meeting, we will form reading groups of four to five students. Several times during the semester, you will read a paper in the computer science literature, and meet with your reading group to discuss it. I will provide you with a strategy for reading the paper well, and some questions for discussion. Your group will be responsible for submitting a summary of your discussion in writing before the class meets to discuss the paper with me. You may also want to meet with your reading group to study for exams. Your software project group may be different from your reading group.

Quizzes: To help you stay current, there will be unannounced quizzes. The quiz will test your knowledge of the previous week. The intention here is to encourage you to review the material each week in preparation for the next week.  Quizzes will be counted as part of your homework grade.

Homework: Homework assignments will consist of problems and labs in the Gradiance System, and other problems/labs assigned in class. Assignments will be listed in Moodle to help all of us track them. Completing the homework will deepen your understanding, help you build skills necessary for completing the group project, and assist you in preparing for exams. Your homework should be your own work, and not copied or supplied by anyone else. Since we will often discuss homework in class after the due date, late homework will not be accepted. For each Gradiance homework, you can read the assistance and redo the exercise if you make a mistake.

Group Projects: As advanced students in computer science, you are now in a position to accept and evaluate software project requirements from a client. This semester you will have an opportunity to do a project with a client.  I will offer some workshops outside of class on programming skills you may find useful in completing your project.

Exams: We will have two midterms. One will be a written exam in class. The second will be a in class lab exam with the use of your computer to test your proficiency in SQL.  The written exams will test your ability to answer homework type problems and your conceptual understanding of database systems. We will discuss the types of possible exam questions before each exam. You will be allowed one sheet of paper with notes to assist you in taking the exams. All work on the exam must be your own. There will be no make-up or early exam sittings without a request from the Dean or a Coach on your behalf.  Before any exam begins, you are required to close your course materials, and put them and your phone in the front of the class to avoid the temptation look at them during the exam.

Academic Honesty

(back up↑)Academic Honesty

It is essential that you follow the Lafayette College Code of Conduct with respect to academic honesty and avoidance of plagiarism as described in the Student Handbook. The beginning of the semester is a good time to review the handbook in this regard.

“To maintain the scholarly standards of the College and, equally important, the personal ethical standards of our students, it is essential that written assignments be a student’s own work, just as is expected in examinations and class participation. A student who commits academic dishonesty is subject to a range of penalties, including suspension or expulsion. Finally, the underlying principle is one of intellectual honesty. If a person is to have self-respect and the respect of others, all work must be his/her own.”

The Handbook gives the following examples of intellectual dishonesty:

  1. Submitting someone else’s work as your own.
  2. Incorporating someone else’s ideas or work into your own without attribution.
  3. Paying or arranging for someone else to do your work.
  4. Re-using material from another course without permission of your instructor.
  5. Engaging in unauthorized collaboration including asking for homework or programming assignment answers from an online discussion group.
  6. Obtaining the Instructor’s Answer Guide to the exercises in the book and using it. (This would also constitute theft, since the guide is only licensed to instructors.)

When in doubt about whether an action is considered academic dishonesty, it is best to consult with me before you act. Cases of suspected intellectual dishonesty will be reported to the Dean, and the Dean will investigate and impose penalties.


(back up↑)Grading

Graded Material:
The course grade is based on the materials listed below graded on a 100 point scale, with each item contributing a specified percentage to the overall score. As specified in the student handbook, A will reflect excellent work, B will reflect good work, C will reflect acceptable work, and D will reflect passing work.

Class Attendance 3%
Class Participation 3%
Quizzes and Homework 10%
Group Projects 20%
Mid-Term 20%
Lab Exam 20%
Final Exam 24%

Grading Scale:
Typically, grades are assigned as follows from your final numerical grade:

A: 93-100 B+: 87-89 C+: 77-79 D+: 67-69 F: 0-59
A-: 90-92 B: 83-86 C: 73-76 D: 63-66
B-: 80-82 C-: 70-72 D-: 60-62

Tentative Schedule

(back up↑)Tentative Schedule*


Wk. Dy. Date Topic

Readings (T:Database Systems,
                           W: In Seven Weeks)
1 T 8/28
Introduction to Databases
T: 1
R 8/30
Relational Model, Relational Algebra
Introduction to Functional Dependencies
T: 2, 3.1, 3.2
2 T 9/4
Ted Codd’s Paper,
Reasoning with Functional Dependencies
T: 3.2
R 9/6
Boyce-Codd Normal Form
T: 3.3-3.4
3 T 9/11
More Normal Forms,
Group Project Preliminaries
T: 3.5, 9
R 9/13
Relational Algebra with Bags
T: 5.1, 5.2
4 T 9/18
T: 6, W: Introduction,
W: PostgreSQL through Day 1
R 9/20
T: 6
5 T 9/25
T: 6.6, 18.1-18.3
R 9/27
No Class
6 T 10/2
More Transactions, Review
R 10/4
Midterm Exam
7 T 10/9
Fall Break. No Class.
R 10/11
Database Design with E-R Diagrams
T: 4 – 4.6
8 T 10/16
Database Design with UML
T: 4.7
R 10/18
Constraints, Triggers
T: 7,
W: PostgreSQL Day 2
9 T 10/23
Indices, Materialized Views
Cubes, Extensions
T: 8
W:  PostgreSQL Day 3 and Wrapup
R 10/25
10 T 10/30
Lab Exam
R 11/1
SQLite, a shining light
in Software Engineering
Distributed Databases
Introduction to noSQL Databases
T: 20-20.4.3, 20.5.2
11 T 11/6
Cap Theorem
W: Apppendix
R 11/8
12 T 11/13
W: MongoDB through Day 2
R 11/15
W: MongoDB Day 3 Wrapup
13 T 11/20
R 11/22
Happy Thanksgiving! No class.
14 T 11/27
W: DynamoDB
R 11/29
15 T 12/4
R 12/6
Project Presentations

*Tentative schedule, subject to change. Check Moodle for the most up to date information.

**No travel arrangements should be made until the final exam schedule has been issued


Additional Information

(back up↑)Additional Information

SMS Advisor:   Sexual Misconduct Support Advisors, formerly SASH, is a campus organization that seeks to prevent sexual and gender based violence and harassment.  It also provides services to those who have suffered from such violence or harassment.  I am an SMS Advisor, and will be on call 24/7 for one week this semester.  During that week, in the unlikely event that I receive a call during class, you will continue with your project or homework while I assist the caller.  If you or a friend ever needs an advisor, call or text 484 548-0325.

Respect for classmates, colleagues and team members: All students are expected to show respect and courtesy to each other. Mutual respect is a high ideal in academic, business, and personal life. It is central to learning well together. Disagreements over ideas or constructive criticism of someone’s work is in keeping with this ideal. Attacking or disparaging someone is not, and will not be tolerated. In group projects, mutual respect also includes reliably contributing to the project and keeping your commitments to the group.

We follow the College Diversity Statement which says in part:

All members of the College community share a responsibility for creating, maintaining, and developing a learning environment in which difference is valued, equity is sought, and inclusiveness is practiced.

To learn more about how these principles are followed in the computing industry, view the Google video:

Diversity at Google (https://youtu.be/_3RoQRN65AI)

and the eBay video:

Diversity Workshop at eBay, Europe (https://player.vimeo.com/video/159767606)

Privacy: Moodle contains student information that is protected by the Family Educational Right to Privacy Act (FERPA). Disclosure to unauthorized parties violates federal privacy laws. Courses using Moodle will make student information visible to other students in this class. Please remember that this information is protected by these federal privacy laws and must not be shared with anyone outside the class. Questions can be referred to the Registrar’s Office.

Equal Access: In compliance with Lafayette College policy and equal access laws, I am available to discuss appropriate academic accommodations that you may require as a student with a disability. Requests for academic accommodations need to be made during the first two weeks of the semester, except for unusual circumstances, so arrangements can be made. Students must register with the Office of the Dean of the College for disability verification and for determination of reasonable academic accommodations.

Important Dates:

  • Normal Add/Drop deadline: September 7th
  • Last day to Withdraw (WD): November 14th
  • Classes end: December 7th
  • Final Exams: December 11th – 18th

Federal credit hour statement: The student work in this course is in full compliance with the federal definition of a four credit hour course. Please see the Registrars Office Website (http://registrar.lafayette.edu/additional-resources/cep-course-proposal/) for the full policy and practice statement.

Credits: Logos Copyright © PostgreSQL, Apache HBASE, mongoDB, Amazon Dynamo, and Amazon Web Services respectively. Photo of Database Systems: The Complete Book Textbook Copyright © Pearson.  Photo of Seven Databases in Seven Weeks Textbook Copyright © Pragmatic Bookshelf.  Photo of Next Generation Databases: NoSQL, NewSQL, and Big Data Copyright © Apress.