5. (15 points) Suppose we are keeping track of professors and which departments

Question

5. (15 points) Suppose we are keeping track of professors and which departments they belong to. The relation is R(profssn, profname, deptname, deptaddress) with primary key profssn. Apart from the functional dependency implied by the primary key, an additional functional dependency is deptname ? deptaddress (a) Explain why the relation is not in the Boyce-Codd normal form (b) Indicate what changes you would make to put the relation in the Boyce-Codd normal form i.e. show the new relational schema. Clearly indicate primary keys and foreign keys. (c) Give an example to demonstrate how the new relational schema is better than the original relational schema by showing an insertion anomaly i.e. some information which can't be inserted with the original relational but can be inserted with the new relational shema. You have to do this as follows: i. build a small instance (the same information represented with both the original and new relational schema) ii. explain what is the infor iii. Briefly explain why the insert will not work with the original relational schema. iv. Show how the information will be correctly inserted with the new relational schema mation you want to insert

Explanation / Answer

a)

the pre condition for BCNF is that the database should be in 3NF ie no transitive key dependency but here:

profssn -> deptname

deptname -> deptaddress

therefore because of the existence of this transitive key dependency , the given relation is not in BCNF.

b)

prof(profssn(PK),profname,deptname(FK))

dept(deptname(PK),deptaddress)

this relation is definitely in 3NF because the transitive dependency discussed aboove has been resolved.

a relation in 3NF is also in BCNF if there are no overlapping candidate keys, which is definitely the case here because both relations have one key each.

c) in the old schema if we were to introduce a new dept , we could not do so until there was atleast one professor working in that dept. But this can be done with the new schema.

Inserting In our old schema, the details about a new department called "Iot" with address "Noida"

we do not have any professor to assign to the new dept , therefore we cannot add information about the new dept.

In our new schema ,

we can simply insert a new record into the dept relation and this is not concerned with the fact that there is no professor currently in that dept.

insert into dept values("IoT","Noida");

Now while adding professor to the dept.

insert into prof values(101,"Mayank Bhardwaj","IoT");

Related Questions

Navigate

• Browse (All)
• Browse 5
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.