1. (Reading Assignment) Read Chapter 1 (with emphasis on Section 1.2), and Chapter 2 (skipping Section 2.4) of the textbook (Garcia-Molina, Ullman, Widom, Database Systems. The Complete Book).

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.

For the following problems you need the university.sql script (on d2l), and access to our SQL database, instructions also on d2l.

3. (University Database, 15pt). For this problem you will modify the DDL code that creates the university database so it supports the following features:

1. 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').

2. 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.

3. Every course has a cap on the enrollment. By default this value should be 40. In general, the number is allowed to be at most 50 (for the CHECK constraint you can <, <=, etc.).

4. A student can select a grading 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, and the selected grade basis must be one of the three values PF, CR, or AU.

Modify university.sql so it can deal with this information. This will require updating the DDL (CREATE TABLE command). Don't worry about modifying the INSERT commands, but see what happens (in particular, check that the default values are set correctly).

For submission: Only submit the relevant parts (i.e. the new CREATE TABLE commands), and some data from the affected tables to show that the fields were created (and, were appropriate, the default values were set).

Hint:  Read through the current university.sql first to get familiar with the syntax. For c and d remember that in Oracle the default value must come before the 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.