1. Suppose the following are the assumptions about a university database: a. Eac
ID: 3630999 • Letter: 1
Question
1. Suppose the following are the assumptions about a university database:
a. Each student has a student id, name, address, and phone number. Different students must have different student numbers. Each student has a unique address and phone number.
b. A student may take multiple courses, and a course may be taken by more than one student.
c. Each course has a course id, course name, offering department and offering style (e.g. in-class, reading, on-line, etc). A course id uniquely identifies a course.
d. A course can be taught by only one instructor, and offered by exactly one department, but may be offered in different styles.
e. Each department has a department name, a general office, and a unique department head. Different departments have different names.
f. A person cannot be the head for more than one department.
g. Different department heads have different names.
The following relational schema, UNIV, is designed to keep track of the above information.
UNIV sid sname address phone cid cname instructor style dept dhead
The meanings of some selected attributes are listed below:
cid: course id
cname: course name
dept: department name
style: the style a course is offered
dhead: the head’s name of a department
The meaning of the remaining attributes should be clear.
In the UNIV relation, each tuple records that a student takes a course, which is taught by an instructor and offered by a department in a particular style. It also records the information about the student, course, and the department.
Answer the following questions:
c. Describe why UNIV is not in BCNF.
d. create a relation instance for UNIV that contain redundancies that can cause all three kinds of anomalies.
Explanation / Answer
(c)
If a dependency X -->Y exists in a relation, with X not a super key and Y a prime attribute, then the relation is not in BCNF.
From the not trivial functional dependencies and the candidate keys
non trivial functional dependencies:
{sid, cid} --> instructor
{sid, cid} –> dept
{cid, style} –> dept
dhead –> dept
Candidate keys:
{address, phone}
{cid, dept}
{cid, instructor}
{dept, style}
We can say that UNIV is not in BCNF since neither of the left hand sided of the non trivial functional dependencies are super keys and the right-handed sided are prime attributes. It violates BCNF.
(d)
The relation instance that contain redundancies that can cause all three kinds of anomalies is:
Student (address, sname, sid, phone, course_taken)
The course_taken table is Course_taken (cid, cname, dept, style)
The student table contains information about student and the course taken. The course taken table gives the information about the course.
Insertion anomaly: whenever a new course is inserted it means that a new student is to be added to the student table.
Update anomaly: whenever a course information is updated in the course_taken table, the student table values are also to be updated as it is related to the course_taken table values
Deletion anomaly: whenever a row in the course_taken table is deleted, its values in the student table will also be deleted.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.