We talked about the relational data model (Sections 2.1, 2.2, 2.5). Next, we wll talk about SQL DDL (Section 2.3) and start on Chapter 6 on SQL (DML).
On the dangers of entity integrity constraints, see the NO TAGS article.
1. (Reading Assignment) Read Chapter 2 (skipping Section 2.4) of the textbook (Ullman, Widom, A First Course in Database Systems).
2. (Relational Schema, 15pt). Create a set of relational schemas with domains, underlined primary keys and arrows pointing from foreign keys to the primary key they refer to for a database containing the following information:
authors have Last Name, Firstname, and a unique ID.
ebooks have ISBN (International Standard Book Number), Title, Price and a (unique) Author; if the book has been superceded by a newer edition (with a different ISBN), we also want to include that information.
customers have Name, unique ID, and Credit Card Number.
an ereader has unique ID, a type (e.g. Kindle), and an owner (a unique Customer).
a customer can buy a particular ebook for a customer (themselves, or as a gift for another customer); we need to store the date when the ebook was bought.
3. (University Database, 15pt). You want to modify the university database so it supports the following features:
Space on transcripts is limited, so there are only 20 characters for each course name. Add a field short_title that allows you to store an alternative short name for each course. (E.g. 'Theory of Comp' for 'Theory of Computation').
Our university sometimes cross-lists courses (e.g. the graduate CSC 489 may be cross-listed with the undergraduate CSC 389). We allow at most one cross-list per course.
Every course has a cap on the enrollment. By default this value should be 40. In general, the number is allowed to range from 5 to 60.
A student can select a grade basis when enrolling in a class; there are three choices: PF(pass/fail), CR (student receives a letter grade), and AU (audit, no grade assigned). Add a field to the database which supports storing this information. Default is CR. This field may not be null, that is, a grade basis must be selected.
Modify university.sql so it can deal with this information. This will require updating the DDL (CREATE TABLE command) as well as the INSERT commands (e.g. to INSERT cross-listed courses).
Two options for submission: 1) Only submit the relevant parts (i.e. the new CREATE TABLE commands and a single INSERT for each of the affected tables), or, 2) submit your updated university.sql with the changes high-lighted (excluding the INSERTs).
Hint: Part a requires adding a line to the create table command, part b requires adding a line to a create table command and the addition of a foreign key constraint, parts c and d require the addition of a new field with a default value and a check constraint.
4. (Constraints, 10pt) According to Codd's Twelve Rules everything in a datbase is stored in tables. Including constraints. Let's check out the system table sys.all_constraints. Use a simple
SELECT * FROM sys.all_constraints;
to get the contents of that table. To look for a specific table, say STUDENT, look for table_name = 'STUDENT' (in the SELECT query to can restrict by saying SELECT * FROM sys.all_constraints WHERE table_name = 'STUDENT').
a) Can you make sense of the records for the STUDENT table?
b) Can you identify the records resulting from the work you did for 3c and 3d above (not in the student table)?
Include a screenshot of relevant records with a short explanation.