Homework 1 (due 4/7)
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).

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

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 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:

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.

Marcus Schaefer
Last updated: April 1st, 2015.