CS 320: Database Management Systems

Fall 2020

Welcome to Database Management Systems!

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

We meet TR, 2:45-4 pm via the video conference link on Moodle.

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, Some Important Examples Below




Instructor Information(back up↑)

  • Instructor: Dr. Joann J. Ordille
  • Email: ordillej at lafaytte.edu
  • Office Phone: (610) 330-5416
  • Office Hours: TR 5:45-6:45 pm (except when a faculty meeting is called),  F 3-4 pm. Please email me if you would like to meet at any other time.


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 SQL 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 front-end web technologies. lynda.com (see below) also provides a variety of helpful tutorials.

This summary describes the variant of SQL used in the MySQL Server.  It will vary a bit from PostgreSQL which we use.  Some students have recommended it as being very helpful.

  • lynda.com

lynda.com is available from your my Lafayette page. It provides many helpful tutorials. You can use lynda to help fill in background you might need for your projects. Your projects will involve building a database application for a customer, and that typically requires a web front-end.

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. They have a new tutorial on JavaScript as well. HackerRank is 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.

You will receive a notification to join our lab’s Slack which we will use for online discussions and Q&A.

We will use Slido during our class meetings to facilitate class discussion. The code for joining Slido will be displayed and available through a QR-code at the beginning of class. It will also be available on Moodle.

  • 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-2020/
  • 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.
    • Before purchasing the Kindle version of this book, please investigate whether the Kindle license is limited to ONE device.  While I have no direct experience using RedShelf, it seems to offer a diverse set of services for the digital copy of this book.
    • ACM Logo
    • Selected Readings provided through the ACM.  See details emailed to you.
  • 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:

  • Analyze a complex computing problem and to apply principles of computing and other relevant disciplines to identify solutions (ABET Outcome 1).
  • Design, implement, and evaluate a computing-based solution to meet a given set of computing requirements in the context of the program’s discipline (ABET Outcome 2).
  • Function effectively as a member or leader of a team engaged in activities appropriate to the program’s discipline (ABET Outcome 5).
  • Apply computer science theory and software development fundamentals to produce computing-based solutions (ABET Outcome 6).
  • 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 and in-class exercises, 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. Class attendance is considered in assigning your class participation grade.

Class Participation: Class participation includes asking or answering questions, expressing an opinion about a topic of discussion, meeting with me during office hours, interacting with me on Slack, participating in your team, and reporting on activities in a group project, a reading assignment, 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.

Lightweight Team Participation: Research has shown that students learn better in a community with their peers. We hope to help you form that community by creating lightweight teams that will collaborate in various course activities. The teams are lightweight, because they are for learning collaboratively without a lot of grade stress. Your contribution to your team counts for 2% of your grade.  Your lightweight team will also function as your reading group.  Several times during the semester, you will read a paper in the computer science literature, and meet with your lightweight team 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 lightweight team to study for exams. Your software project group may be different from your lightweight team.

Quizzes: To encourage you to prepare for class, there will be unannounced quizzes. The quizzes will test whether you completed the assigned reading or other preparation for the class. Other quizzes 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’s discussions. Some quizzes may include both kinds of questions.

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.

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. There will also be a final exam. 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.

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.

Lightweight Team Participation 3%
Class Participation 3%
Quizzes and Homework 7%
Group Projects 15%
Mid-Term 24%
Lab Exam 24%
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)
1 T 8/18
Introduction to Databases
T: 1
R 8/20
Relational Model, Relational Algebra
T: 2
2 T 8/25
Semi-Structured Data and the Web, MongoDB
R 8/27
Document Object Models
Front End Architectures
3 T 9/1
Ted Codd’s Paper,
Reasoning with Functional Dependencies
T: 3.1-3.2
R 9/3
Boyce-Codd Normal Form
Group Project Discussion
T: 3.3-3.4
4 T 9/8
More Normal Forms
T: 3.5, 9
R 9/10
Relational Algebra with Bags
T: 5.1, 5.2
5 T 9/15
T: 6, W: Introduction,
W: PostgreSQL through Day 1
R 9/17
T: 6
6 T 9/22
T: 6.6, 18.1-18.3
R 9/24
More Transactions, Review
Midterm This Week
7 T 9/29
R 10/1
Database Design with E-R Diagrams
T: 4-4.6
8 T 10/6
R 10/8
Constraints, Triggers
T: 7
9 T 10/13
Indices, Materialized Views
T: 8
R 10/15
Project Iteration Presentation
10 T 10/20
Lab Exam This Week
R 10/22
11 T 10/27
SQLite, a shining light
in Software Engineering,
Distributed Databases,
noSQL Databases
R 10/29
SQLite, a shining light
in Software Engineering,
Distributed Databases,
noSQL Databases
T: 20-20.4.3, 20.5.2
12 T 11/3
Big Data, Google Search Engine
R 11/5
Google Big Table, HBase
CAP Reading TBD
13 T 11/10
Project Iteration Presentation
R 11/12
14 T 11/17
R 11/19
Final Project Presentations
Final Exam

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

Additional Information

(back up↑)Additional Information

SM&RT (SMART) Advisor: Advisors in the Sexual Misconduct & Resource Training Program, formerly SASH, seek to prevent sexual and gender based violence and harassment. They also provide services to those who have suffered from such violence or harassment. I am an SMART Advisor, and am available through appointment to assist you or your friends if you experience sexual misconduct. As a SMART Advisor, I can keep the names of those involved or those consulting me private.

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)

Proper Usage of Course Materials and Classroom Recordings:  At Lafayette College, all course materials are proprietary and for class purposes only. This includes posted recordings of lectures, worksheets, discussion prompts, and other course items. Reposting such materials or distributing them through any means is prohibited. Such materials should not be reposted or distributed through any means. You must request my permission prior to creating your own recordings of class materials, and any recordings are not to be shared or posted online even when permission is granted to record. If you have any questions about proper usage of course materials please ask me.  Please also be in contact with me if you have any concerns with being recorded during the course.

Privacy: Moodle contains student information that is protected by the Family Educational Right to Privacy Act (FERPA). Courses using Moodle will make student information visible to other students in this class. Online discussions in Moodle or Slack occurring during synchronous class sessions or asynchronous class activities should also remain private and not be shared outside of the course.  Disclosure to unauthorized parties violates federal privacy laws. 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.

Important Dates:

  • Normal Add/Drop deadline: August 28th
  • Last day to Withdraw (WD): November 2nd
  • Classes end: November 20th
  • Final Exams: November 30th – December 7th

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. ACM Logo is in the public domain.  Photo of Database Systems: The Complete Book Textbook Copyright © Pearson.