We talked about database management systems in general, establishing some basic terminology and background (Chapter 1). We continued by talking about the relational data model (Sections 2.1, 2.2, 2.5, and started on 2.3).
We also saw Oracle SQL Developer, our interface to an Oracle SQL Database Server.
On the dangers of entity integrity constraints, see the NO TAGS article.
Next week, we will continue with SQL DDL (Section 2.3 for SQL in general) and start Chapter 6 on SQL (the DML part).
1. (Reading Assignment) Read Chapter 1 and Sections 2.1, 2.2 and 2.5 of the textbook (Ullman, Widom, A First Course in Database Systems).
2. (Install SQL Developer). Install Oracle's SQL Developer on your system and set up a connection. I have already emailed out a handout with detailed instructions. it is also available on d2l. Email me early if you're having any problems with this.
3. (University Database, 5pt). Download the SQL for the university database: university.sql (also on d2l) and run it on SQL Developer (use the Run Script button). Run a simple SQL query to verify that you got the data right, e.g.
SELECT *
FROM studentgroup;
4. (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, ID, and Birthdate
publishers have Name, ID, address; for the case that a publisher is a subsidiary of another publisher, we include the ID of the parent publisher.
books have ISBN, Title, Publisher (each book has a unique publisher). ISBN is the standard code to identiy a book.
Authors write Books; since many authors can co-author a book, we need to know the rank of an author contributing to a book (i.e. a number 1, 2, 3; for single author books, this number is 1).
5. (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 which stores all constraints. Use a simple
SELECT * FROM sys.all_constraints;
to get the contents of that table. To look for a specific table, say ENROLLED, look for table_name = 'ENROLLED' (in the SELECT query to can restrict by saying
SELECT * FROM sys.all_constraints WHERE table_name = 'ENROLLED'
Note that capitalization in the table name matters, since 'ENROLLED' is a string).
a) Can you make sense of the records for the STUDENTGROUP table? There should be three constraints. What are they? Hint: to identify the meaning of the field constraint_type, check out Oracle's documenation on the sys.all_constraints table. Inlcude a screenshot of running SELECT * FROM sys.all_constraints WHERE table_name = 'STUDENTGROUP'.
b) Pick the name of the primary key constraint in studentgroup (for me that is 'SYS_C00513764', your number will be different). The all_constraints table we saw in a) does not contain the details of which attributes are part of the primary key, that's in a different table called sys.user_cons_columns; verify that Oracle has the right column saved as primary key for STUDENTGROUP by running SELECT * FROM sys.user_cons_columns WHERE constraint_name = ''SYS_C00513764', of course, replacing my constraint numbers with yours). Include a screenshot.