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).
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 talk about 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 course;
4. (Relational Schema, 15pt). Create a set of relational schemas with domains (use integer, date, string as types), 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 (single) publisher). ISBN is the standard code to uniquely identify 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. We can use a simple
SELECT * FROM sys.all_constraints;
to get the contents of that table. To look for a specific table, say the ENROLLED table in our university database, we restrict the query by requiring table_name = 'ENROLLED'. In the SELECT query we do this 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) We want to check what constraints there are on the STUDENTGROUP table. Rewrite the above SELECT query, so it lists the constraints for STUDENTGROUP. Can you make sense of the records for the STUDENTGROUP table? There should be three constraints. For each constraint, explain what it is. 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.