November 11, 2024

Topics of the day

• Relational models
• SQL

Suggested readings

• Sections 5.1, 8.1, 8.4 from Ramez Elmasri and Shamkant Navathe, Fundamentals of database systems, 4e
• Sections 3.1, 4.1, 4.3 from Ramez Elmasri and Shamkant Navathe, Fundamentals of database systems, 6e
• Section 3.1 from Mark Allen Weiss, Data structures and algorithm analysis in C++, 3e
• Sections 6.1, 6.5 from Jeffrey Ullman and Jennifer Widom, A first course in database systems, 2e

Online references

• Relational database (en.wikipedia.org)
• Relation (en.wikipedia.org)
• Examples of CREATE/INSERT (alvinalexander.com)
• SQL (en.wikipedia.org)
• SQL syntax (SQLite dialect) (sqlite.org)
• SQL query semantics (jvns.ca)

Questions and exercises

1. What part of a SELECT statement controls the columns that will be produced in the result? What part controls which rows will be filtered into the result?
2. Give an example SQL statement that shows how GROUP BY is used to aggregate information (with summary statistics).
3. What is the difference between WHERE clauses and HAVING clauses in an SQL SELECT statement?
4. What does it mean to "join" two tables? What makes something a "natural" join (as opposed to some other kind of join)?

Assignments

Today

• Homework 6 due

Upcoming

• Project 4: Database prep due (13 Nov)
• Project 4: Database design due (18 Nov)
• Homework 7 due (18 Nov)
• Homework 6 revision (18 Nov)
• Homework 5 revision (18 Nov)