CS3811 - 2000

Dr. R. Rosebrugh

General Information

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

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

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

Grades will be assigned with approximately the following weights:

• Assignments: 30
• Test: 25
• Project: 45

Check this URL regularly for information about the course.

Assignments

Assignment 1

From the text: 1.5,1.10,2.4,2.5,2.8,3.2. Due Sept 29.

Please attempt the other text problems before consulting the solutions provided.

Assignment 2

Due October 13.

Consider the following `relation schemes' or relvars (typing ignored)
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 appropriate 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 i.e. he 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.