Sonoma State University

Department of Computer Science

CS 355 - Database Management Systems Design, Fall 2023

Course Information

• Class Time: Friday’s 9 a.m. to 12:40 p.m.
• Classroom: Darwin Hall 35
• Format: In person

Instructor Contact Information

• Name: Tim Coulter
• Office Location: Darwin 116D (beware, I'm not there often)
• Email: coultert@sonoma.edu
• Office Hours: Monday's, 11 a.m. to 12 p.m., on Zoom, by appointment only. Email for appointment. (Times subject to change.)

Course Description

This course focuses on the theoretical as well as the practical aspects of modern database systems. Topics include the study of the entity-relationship (E/R) model, relational algebra, data normalization, XML as a semi-structured data model, data integrity, and database administration. Current tools and technologies are used to create and manipulate sample databases.

Content, Timeline, and Important Dates

We will have 15 three hour and forty minute classes across the entire semester. We'll generally follow the book closely, especially during the first half of the semester. We'll differ slightly following the midterm, focusing more on your programming project as we near observed holidays. Please refer to the following schedule.

Course Standards & Learning Outcomes

The following learning outcomes (often called "standards") direct what you'll learn in the course. Every assignment, quiz, test, and project, will be linked specifically to one or more learning outcome. In most cases, the learning outcome(s) assessed in each assignment, quiz, test, and project, will be clearly labeled. The following is an example free response test question linked directly to a learning outcome:

You will be assessed on the following learning outcomes:

A. Understand what a database system is

1. You understand what data is, and how it's created.
2. You understand what a DBMS is and what it provides.
3. You know when to use files and when to use a DBMS.

B. Understand relational database data structures, and use basic SQL

1. You understand the general data structures used in relational databases (set, tuple, table, row, column).
2. You can create tables via CREATE TABLE statements when given verbal descriptions of data or existing data like CSV files; you can choose the appropriate data types for all data.
3. You can write basic SELECT queries via SQL, applying projection, conditional WHERE clauses, and SELECT expressions.
4. You can insert, update, and delete data in an existing table via SQL.
5. You understand and can apply referential integrity constraints, including PRIMARY KEY, FOREIGN KEY, and REFERENCES.
6. You understand and can apply CHECK constraints.

C. Write complex queries with SQL & relational algebra

1. You can express queries using relational algebra.
2. You can write SQL queries that make use of simple functions, and explain their use.
3. You can write SQL aggregation queries involving GROUP BY and HAVING clauses, and explain their use.
4. You can write SQL queries with left, right, and inner joins, including self-joins and cross-joins, and explain their use.
5. You can write SQL queries that contain subqueries and correlated subqueries, and explain their use.
6. You can flatten subqueries and correlated subqueries, and communicate why this is beneficial.

D. Design databases and use ER diagrams

1. You can communicate database structure using Entity-Relationships diagrams, expressing appropriate cardinality
2. You can describe and implement the discovery process, and describe how database requirements are formed
3. You can decompose unnormalized tables into BCNF and/or 3NF compliant tables.
4. You can identify tables that are not normalized
5. You are able to translate ER diagrams into SQL table schema.

E. Understand how data is stored and indexed in a DBMS

1. You understand storage media and their basic properties
2. You understand how data is stored using storage media in a DBMS
3. You understand how different indexing techniques work, and why data is indexed
4. You can analyze the performance of different storage and indexing strategies

F. Understand basic transaction processing concepts

1. You understand transactions and their properties (ACID)
2. You understand the anomalies that occur without ACID
3. You understand the locking protocols used to ensure Isolation
4. You understand logging and recovery techniques used by the DBMS

G. Understand cloud and distributed databases, replication, and data warehousing

1. You understand the architectural layout, and pros and cons of, cloud and distributed databases.
2. You understand how the two phase commit protocol is used in cloud and distributed databases.
3. You understand replication techniques and when to use them.
4. You understand and can explain the CAP theorem.
5. You understand the general need for, and schema design of, data warehouses.

H. Understand and use spatial data

1. You understand how to represent points and shapes using Well-Known Text and built-in constructor functions.
2. You understand how to use built-in functions like ST_ConvexHull, and you can extract points from the database to perform these calculations.
3. You understand how to combine previous SQL skills with spatial data and functions (e.g., using aggregate functions to compute average distances).
4. You understand how to visualize spatial data.

I. Understand how to store & query semi-structured data

1. You understand what a semi-structured data model is and why it is useful
2. You understand big data and how it affects DBMS structure.
3. You understand when to use document, key-value, wide column, and graph databases.
4. You understand how to query semi-structured data using MongoDB.

J. Create a DBMS-backed Application

1. You are able to conceive of, and define, a good use case for a database.
2. You are able to design, develop and test a DBMS-backed application that addresses that use case.
3. You are able to identify appropriate libraries (e.g., ORMs) for your desired DBMS and programming language.
4. You can implement basic CRUD queries using those libraries to save, load, update, and delete data to and from your database.
5. You can implement aggregation queries using those libraries, requesting your database to perform calculations across a large set of data.
6. You are able to communicate your design decisions related to your application's use of a DBMS.
7. You can discuss ways in which your application -- and its use of a database -- can be improved.

Rubric and Grading System

TL;DR: To succeed in this course, you must:

• Show mastery at least once for most of the standards listed above (see "Computing Your Letter Grade" section below).
• Each sub-bullet is a standard. Therefore, A.1 is a separate standard from A.2.
• Mastery is designated by a score of 3. All other scores will not impact your letter grade.
• Your letter grade is determined linearly based on the percentage of standards in which you've shown mastery.

Longer version:

This course uses Standards Based Grading to evaluate your performance in the course as well as determine your letter grade.

After completing work in this course, you will receive a score between 1 and 3, where a 1 means little or no evidence of mastery was shown in relation to a specific standard, and a 3 means there was a sufficient evidence of mastery shown. Refer to table below.

Computing Your Letter Grade

Your letter grade will be determined linearly based on the number of standards in which you have shown mastery. Please note that each sub-bullet in the Learning Outcomes section above is a separate standard. So, learning outcome A.1 is a separate standard from A.2.

You need only show mastery in a standard once in order to receive credit for that standard. You will have many opportunities to show mastery of a standard, in the format that works best for you. See section "How to Show Mastery" section, below.

How to Show Mastery

You can show mastery in a standard in the following ways:

• Answering the majority of questions related to that standard correctly (usually this means 75%) on the daily quizzes
• Answering the majority of questions related to that standard correctly on either the midterm or the final
• Incorporating a standard into your class project, and explaining why you're showing mastery of that standard (you can incorporate as many standards as you wish)
• Making an appointment with me to "teach me" a specific standard.
• Creating an application or work product on your own and submitting it to me, explaining why your application shows mastery of the standard (you may incorporate as many standards as you wish)

Helpful "getting an A" decision tree:

Daily Class Structure

Our scheduled class time runs three hours and forty minutes, from 9 a.m. to 12:40 p.m. Since this is such a lengthy amount of time, we'll break it up into specific sections and tasks:

• Lecture, direct instruction, and any discussion needed from the previous week.
• Lab time, to discuss, work on, and research what was taught during direct instruction.
• One to two bathroom breaks (or as needed; please speak up!).
• We'll end each class with a quiz and/or a quiz retake.

Please note that in rare cases an assignment may be given in place of a quiz. For example, you'll be asked to create your own Entity-Relationship diagram in Week 4.

Quizzes & Assignments

All take-home quizzes and assignments are optional! You do not need to complete them to pass this course. Quizzes are structured so that they incentivize you to read the material before class and come prepared. It is possible to get an A in this course simply acing all the quizzes. It's also possible to pass the course by ignoring these entirely and relying soley on the midterm and final.

You do not need to answer every question on a quiz. You will not be penalized for blank answers. You'll receive mastery credit toward every standard you pass, no matter how well you did in other standards.

Midterm and Final

Both the midterm and the final are optional! You do not need to complete the midterm or the final to pass this course. As mentioned above, you can receive the same credit through quizzes and assignments if you score well on those. That said, the midterm and the final are useful opportunities to cover any standards in which you have not yet shown mastery.

You do not need to answer every question on the midterm or the final. You will not be penalized for blank answers. You'll receive mastery credit toward every standard you pass, no matter how well you did on the other standards.

Programming Project

You will be assigned a programming project toward the end of the semester. The project will require you to design a database and create a working application using your language of choice. The programming project is optional! You do not need to submit a programming project to pass this course; however, note that showing mastery of some programming related standards can only be shown through code that you have written. If you do not submit the programming project, you'll need to find ways to make up these standards in other ways.

Resources Necessary for Class

In this course we will be using the following resources:

• TEXTBOOK: zyBooks Database Systems with SQL and zyLabs. More info on purchasing this textbook below. Use code SONOMACS355CoulterFall2023
• CANVAS: https://canvas.sonoma.edu/courses/37509

Instructions for accessing our textbook:

1. Sign in or create an account at learn.zybooks.com
2. Enter zyBook code: SONOMACS355CoulterFall2023
3. Click Subscribe

A subscription will cost $89. The cutoff to subscribe is Nov 30, 2023. Subscriptions will last until Dec 29, 2023.

Dropping, Adding, and Waitlist Policy

Students are responsible for understanding the policies and procedures about add/drops, academic renewal, etc. How to Register https://registrar.sonoma.edu/how-register#howto ​​has step-by-step instructions and important deadlines and penalties for adding and dropping classes. Students on the waitlist need to talk directly with their academic advisor for help accessing the class.

Academic Integrity & Plagiarism Policy

Students should be familiar with the University’s Cheating and Plagiarism Policy. Your own commitment to learning, as evidenced by your enrollment at Sonoma State University and the University’s policy, require you to be honest in all your academic coursework. Instances of academic dishonesty will not be tolerated. Cheating on exams or plagiarism (presenting the work of another as your own, or the use of another person’s ideas without giving proper credit) will result in a failing grade and sanctions by the University. For this class, all assignments are to be completed by the individual student unless otherwise specified.

Artificial Intelligence Use Policy

All use of generative AI (artificial intelligence) tools, like ChatGPT, Bard, and DALL-E, must be properly cited. Including generative AI output without proper citation is very similar to copying directly from the internet and will be considered the same as plagiarism. AI is a tool that cannot replace your learning to think, write, research, and create for yourself. If you choose to use an AI tool, verify any information it provides and be sure to properly cite any content you integrate into your academic and personal projects. For guidance on how to cite or verify AI-generated content, visit, email, chat, or make an appointment with our Sonoma State University librarians.

Please note that if the entirety of your work was generated via AI, you will receive no credit for that work even if the AI output was properly cited.

Examples of citations I'll accept:

• Comments in code that clearly state which portions of the code are AI generated, and the AI tool used.
• A block quote in a written response that that clearly shows which portion of the response were AI generated, citing the AI tool used.

I will not accept any AI generated images, or any answers that were completely generated by AI, even if properly cited.

Phone and Technology Use Policy

I love technology! This is a computer science class after all. Bring whatever technology to class that you think will help you be successful. However, please be courteous to others and silence your cell phones and all other noise-making devices. If you need to answer an important call, by all means do, but do so outside the classroom.

Other Policies

• Department Policy on Incomplete Grades
• Full list of University Policies

Question about this syllabus, or any policy it contains?

Please reach out to me at coultert@sonoma.edu with any questions!