CS3811

Dr. R. Rosebrugh

Current Assignment

General Information

The course meeting time is 9:30MWF in HH101. For official detail see the Department Handbook.

The textbook is Introduction to Database Systems by Date. We will cover all of chapters 1--12, and most of chapters 13-25.

There will be written assignments, a test and a major project. The test will be held in class on March 21.

Grades will be assigned with approximately the following weights:

• Assignments: 25
• Test: 35
• Project: 40

Check this URL regularly for information about the course.

Assignments

Assignment 1

1. From the text: 1.8
2. 2.6
3. Write Data Definition statements for a relational database derived from your answer to 1.8. Show the resulting catalog (Tables and columns only...)
4. Examine Paradox 5.0 for Win. How does it reflect the ANSI/Sparc architecture? What host languages are supported? Is there a dictionary? Is there support for domains? keys?
Also do 2.2 and 3.1 (but don't hand in your answers!!!), Due Jan. 24.

Assignment 2

Due Feb 3

Consider the following `relation schemes'
ENROLL ( S#, C#, Section ) : S# is a student, C# a course
TEACH ( Prof, C#, Section )
ADVISE ( Prof, S# ) : faculty adviser
PRE_REQ ( C#, Pre_C# )
GRADES ( S#, C# , Grade, Year )
STUDENT ( S#, Sname )

1. Draw an ER diagram that might have led to the relations defined.

2. Draw appropriate referential diagrams. (You will need to list approriate primary and foreign keys). Compare your answer to 1.

3. Write relational algebra queries with the following results:
- all students taking courses with Smith or Jones
- all students taking a course their adviser teaches
- professors who teach more than 1 section of a course
- courses that student `Jon Doe' can enroll in ie has passed prereqs!

4. Write relational algebra expressions with the following results:
- courses and their enrollments by section
- profs, their courses and enrollment totals by section
- student numbers and their GPA

5. Write relational algebra expressions with the following results:
- students in section A of CS1711 all get a B
- pre-1990 grades are eliminated

Assignment 3

1. For the relation scheme R(ABC) let S = { A --> B, B --> C}. Find S+ (you may ignore trivial dependencies).

2. If S = {A --> B, B --> C, C --> A} and T = {A --> BC, B --> A, C --> A} show that S and T are equivalent. Find an irreducible cover for T.

3. For R(ABCD) with dependencies S = {AB --> C, A --> D, BD --> C}

a) find an irreducible cover for S
b) give a 3NF dependency-preserving decomposition of R with only 2 schemes
c) does your answer to a) have a lossless join? (if not, modify your answer to assure this)
d) give 3 examples of update anomalies that can arise if R is not decomposed.

4. For the relation scheme R(SDIM) with dependencies {SI --> D, SD --> M}

a) find all candidate keys of R
b) show that R is 2NF, but not 3NF
c) give an example of an update anomaly that can arise for R

For 4. it might be handy to interpret the attributes as Store, Department, Item, Manager.