Download 8 Problems on Database Systems - Homework 1 | CS 411 and more Assignments Deductive Database Systems in PDF only on Docsity!
CS411 Database Systems
Fall 2005
HW
Due: 11:30am CST, 9/28/
Note: Print your name and NetID in the upper right corner of every page of your submission. Handin your homework to Bethany Britton in 2232 SC.
Problem 1 ER diagram
Consider the following information about a university database:
- Professors have an SSN, a name and a rank
- Projects have a project number, a starting data and an ending date
- Each project is always managed by one professor
- Each project is worked on by one or more professors
- Professors can manage and/or work on multiple projects
- Departments have a department number, a department name, and a main office
- Departments have a professor (department chair) who runs the department (a department can temporarily have no chair)
- Professors work in one or more departments, and for each department that they work in a time percentage is associated with their job
Design and draw an ER diagram that captures the information about the university. Be sure to indicate any key and participation constraints.
Problem 2 ER diagram– cont’d
Modify your solution for Problem 1 above, by introducing graduate students into the system:
- Graduate students have an SSN, a name, and a degree program
- Graduate students have one major department in which they are working on their degrees
- A graduate student may have at most one senior graduate student who advises him or her in terms of academic issues
- Some of the graduate students are Research Assistants (RA). Research Assistants work on projects and each Research Assistant has an office.
Update your ER diagram for Problem 1 to reflect the information given above. Be sure to indicate any key and participation constraints.
Problem 4 Schema Design
Convert the E/R diagram of Problem 1 to a relational database schema. Don’t forget to indicate the keys for each relation.
Problem 5 Functional Dependency
Consider a relation with schema R(A,B,C,D) and FD’s AB → C, BC → D, CD → A and AD → B.
(a) Find the closures for subsets A, CD and ABD respectively. (b) List all nontrivial FD’s that follow from the given FD’s. (Note: List only the ones with single attributes on the right side, e.g., AB → C) (c) What are all the keys? Explain your answer.
Problem 7 Normal Forms
The relationship between 3NF, BCNF, 4NF could be illustrated by the following figure. That is all the relations that are in 4NF are also in BCNF. And all the relations that are in BCNF are also in 3NF. Use your own examples to show relations that are:
(a) in 3NF but not in BCNF (b) in BCNF but not in 4NF (c) in 4NF Justify your examples by explaining why they satisfy the corresponding normal form requirements.
3 N F
4 N F
B C N F
Figure 1: Normal Forms
Problem 8 Functional Dependency and Normal Forms
Given a relation R and a set of functional dependencies (all the other functional dependencies could be derived from this set), can we always decide whether relation R is in BCNF by just checking whether there are any BCNF violations in the given set of functional dependencies, without checking other functional dependencies that could be derived from the given set of functional dependencies? If your answer is yes, try to prove it. If your answer is no, try to come up with a counter example.
