Sonoma State University
Department of Computer Science
CS 355 - Database Management Systems Design, Fall 2023
- 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: firstname.lastname@example.org
- Office Hours: Monday's, 11 a.m. to 12 p.m., on Zoom, by appointment only. Email for appointment. (Times subject to change.)
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.
What You'll Learn
This course will help you become proficient with general-purpose and specialized database systems. You’ll learn how to analyze data and organize it in a way that supports modern applications. We’ll cover the theory of data and why it matters in the business world; entity-relationship (E/R) diagrams, to better visualize database structure; the SQL query language; relational databases and their history; database optimization; spatial databases; NoSQL databases (graph, key-value, document, etc.); and BIG data. Though we’ll spend most of our time with relational databases, you’ll be given enough of a foundation to both implement and use application-specific databases in your future career.
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.
|Week #||Class Date||Topic||Standards Covered (see below)||Book Chapter||Assessment Type|
|1||Aug. 25th||Welcome, Syllabus, and Introduction to Databases||A.x||1||Quiz|
|2||Sep. 1st||Relational Databases||B.x||2||Quiz|
|3||Sep. 8th||Complex Queries||C.x||3||Code|
|4||Sep. 15th||Database Design||D.x||4||ER Diagram|
|5||Sep. 22nd||Data Storage & Transaction Management||E.x and F.x||5 & 6||Code|
|6||Sep. 29th||Cloud Database Architecture||G.x||7+||Quiz|
|7||Oct. 6th||Spatial Data / Midterm Review||A.x - H.x||8+||Midterm|
|8||Oct. 13th||MIDTERM||A.x - H.x||In-class Assessment / Long Answer|
|9||Oct. 20th||NoSQL Databases||I.x (TBD)||10+||Code|
|10||Oct. 27th||NoSQL Databases #2||I.x (TBD)||10+||Code|
|11||Nov. 3rd||Project Introduction, ORM examples, and Authentication||J.x||9+||Project Deliverable 1|
|12||Nov. 10th||NO CLASS, Veteran's Day Observance|
|13||Nov. 17th||Project, Example (by me)||Variable||Project Deliverable 2|
|14||Nov. 24th||NO CLASS, Thanksgiving Day Observance|
|15||Dec. 1st||Project, con't||N/A||Project Deliverable 3|
|16||Dec. 8th||Project Presentations||N/A||Presentation & Code|
|17||Dec. 15th||FINAL (different class time, 8 a.m. to 10 a.m.)||A.x - H.x||In-class Assessment / Long Answer|
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
- You understand what data is, and how it's created.
- You understand what a DBMS is and what it provides.
- You know when to use files and when to use a DBMS.
B. Understand relational database data structures, and use basic SQL
- You understand the general data structures used in relational databases (set, tuple, table, row, column).
- 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.
- You can write basic SELECT queries via SQL, applying projection, conditional WHERE clauses, and SELECT expressions.
- You can insert, update, and delete data in an existing table via SQL.
- You understand and can apply referential integrity constraints, including PRIMARY KEY, FOREIGN KEY, and REFERENCES.
- You understand and can apply CHECK constraints.
C. Write complex queries with SQL & relational algebra
- You can express queries using relational algebra.
- You can write SQL queries that make use of simple functions, and explain their use.
- You can write SQL aggregation queries involving GROUP BY and HAVING clauses, and explain their use.
- You can write SQL queries with left, right, and inner joins, including self-joins and cross-joins, and explain their use.
- You can write SQL queries that contain subqueries and correlated subqueries, and explain their use.
- You can flatten subqueries and correlated subqueries, and communicate why this is beneficial.
D. Design databases and use ER diagrams
- You can communicate database structure using Entity-Relationships diagrams, expressing appropriate cardinality
- You can describe and implement the discovery process, and describe how database requirements are formed
- You can decompose unnormalized tables into BCNF and/or 3NF compliant tables.
- You can identify tables that are not normalized
- You are able to translate ER diagrams into SQL table schema.
E. Understand how data is stored and indexed in a DBMS
- You understand storage media and their basic properties
- You understand how data is stored using storage media in a DBMS
- You understand how different indexing techniques work, and why data is indexed
- You can analyze the performance of different storage and indexing strategies
F. Understand basic transaction processing concepts
- You understand transactions and their properties (ACID)
- You understand the anomalies that occur without ACID
- You understand the locking protocols used to ensure Isolation
- You understand logging and recovery techniques used by the DBMS
G. Understand cloud and distributed databases, replication, and data warehousing
- You understand the architectural layout, and pros and cons of, cloud and distributed databases.
- You understand how the two phase commit protocol is used in cloud and distributed databases.
- You understand replication techniques and when to use them.
- You understand and can explain the CAP theorem.
- You understand the general need for, and schema design of, data warehouses.
H. Understand and use spatial data
- You understand spatial types, and how to use them.
- You understand how spatial indexing works.
- You understand the convex hull and nearest neighbors algorithms, and how to compute them.
- You understand how to visualize spatial data.
I. Understand how to store & query semi-structured data
- You understand what is a semi-structured data model and why it is useful
- You understand big data and how it affects DBMS structure.
- You understand when to use key-value, wide column, document, and graph databases.
- You understand how to query semi-structured data.
J. Create a DBMS-backed Application (details subject to change)
- You are able understand the technical requirements of a particular informational problem.
- You are able to design, develop and test a DBMS-backed application to address that problem.
- You are able to use appropriate ORM libraries for your desired DBMS and language.
- You understand stored functions, stored procedures, and triggers, and when to use them.
- You are able to communicate your design decisions related to the DBMS and execution layers.
- You know how to decide what indexes to create for a given workload.
Rubric and Grading System
Read this and the following sections carefully!
The grading structure in this class may be different than you've seen in other classes. Please read this and the following sections fully.
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.
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.
|1||Little to no evidence of mastery was shown.||Student left a short-answer question blank or did not answer a question; gave little or no justification for their answer when justification was requested; answered many multiple choice questions related to a standard incorrectly; OR provided an answer to a question that was unrelated to the question asked or the associated standard.||⛔|
|2||Some evidence of mastery was shown.||Student answered a short-answer question correctly but did not provide any justification (or provided incorrect justification) when justification was requested; answered more than one multiple choice question related to the standard incorrectly; OR provided an answer to the question in a way that exhibited logical inconsistencies relative to mastery of the standard.||⛔|
|3||Sufficient evidence of mastery was shown.||Student answered a short-answer question correctly and provided appropriate justification for their answer when justification was requested; answered most or all multiple choice questions related to the standard correctly; exhibited little or no logical inconsistencies when justifying their answer, and provided an answer that showed acceptable coverage of any associated complexity.||✅|
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.
|Mastery completion percent||Grade|
|90%+ of standards completed||A|
|80% to 90% of standards completed||B|
|70% to 80% of standards completed||C|
|60% to 70% of standards completed||D|
|Less than 60% of standards completed||F|
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:
|Decision (read from top to bottom)||No||Yes|
|Did you get a 3 on all assignments, quizzes, and projects?||Move to step below||Congrats, you've got an A!|
|Did you make up all non-3's on the Midterm/Final?||Move to step below||Congrats, you've got an A!|
|Did you prove to me by appointment that you know the standard?||Move to step below||Congrats, you've got an A!|
|Did you bring in your own work and convince me you know the standard?||Move to step below||Congrats, you've got an A!|
|Did you repeat any of the steps above until you received a 3 on all standards?||Move to step below||Congrats, you've got an A!|
|End of the line! Your grade is computed based on a linear percentage of standards completed.||--||--|
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.
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:
- Sign in or create an account at learn.zybooks.com
- Enter zyBook code: SONOMACS355CoulterFall2023
- 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.
Use AI at your own risk!I reserve the right to score any work product with a zero if I suspect AI was imporoperly used. If this occurs, you can talk to me to clear up any misunderstanding, or make up standards through the avenues above. If I find you have violated this policy more than once, I'll refer you to the administration on a plagiarism violation. Remember! Always cite your sources, especially when using AI.
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.
Question about this syllabus, or any policy it contains?
Please reach out to me at email@example.com with any questions!