# CS3811 - 2000

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

The Project assignment is at 3811pr00.html .

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 )
PRE_REQ ( C#, Pre_C# )
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

### Assignment 3

Due November 6
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
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.