Homework 1 (due 4/8)
CSC 355

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

Handing it in: Submit to the d2l dropbox. Use some standard format for your homework (Word or pdf, for example), and make sure to include your name and assignment number. Include screenshots for code and testruns. You do not need to include the code (or any executables). Please include all material in a single file (and don't use zip). See SampleHWSubmission.docx (or SampleHWSubmission.pdf) for a sample. Note how I cropped and resized the screenshots to make them more readable.

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.

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:

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.

Marcus Schaefer
Last updated: April, 4th, 2014.