More Constraints. October 8. Today. Status of the class Review of MVDs Piazza Refresher Activity. Logistics. Midterm Exam Logistics. The midterm exam moved to Wednesday night, 7pm, October 22nd . 1.5 hours. No room big enough for us. Firas has the req in week 1!
For each fixed course (CS145),
every staff member in that course and
every student in that course
occur in a tuple in that table.
Write: Course ↠ Staff or Course ↠ Student
Course ↠ Staff
If A B does A↠ B ?
Hint: It’s sorta like multiplying
MVDs have “rules” too!
4th Normal Form is
Ignore: MVD is conditional independence in graphical models (databases, first!)
Constraints in SQL
for your reference*
*eligible for catch up lecture
Again: If two tuples agree on the value of attributes in the key, then they must be the same tuple.
1. Which would you select as a key?
2. Is a key always guaranteed to exist?
3. Can we have more than one key?
“only bona fide students may enroll in courses”,
i.e., students must appear in the course table.
Studid is not a key. Why? What is?
We say that Studid is a foreign key that refers to Students
CREATE TABLE Enrolled (
PRIMARY KEY (studid, cid),
FOREIGN KEY (studid) REFERENCES Students
1. What if we insert a tuple into Enrolled, but no corresponding student?
2. What if we delete a student?
DBA chooses (syntax in the book)
Say, Jim just enrolled in his first class.
In SQL, we may constrain a column to be NOT NULL, e.g., name
NB: Whenever we do something ugly, it’s for the sake of performance