Exercise #2 in Database systems 2022

Exercise #2 in Database systems 2022

Section 1 - ERD:

I have created an ERD for a library management system. The entities in the system are "Library," "Book," "Member," and "Transaction." The "Transaction" entity is a weak entity, as it depends on the existence of a "Member" and "Book" entity. The relationships in the ERD are as follows:

1. 
   One-to-Many Relationship: A "Library" can have multiple "Books," but a "Book" belongs to only one "Library."
   
2. 
   Many-to-Many Relationship: A "Member" can borrow multiple "Books," and a "Book" can be borrowed by multiple "Members." This relationship is represented by a "Transaction" entity that connects "Member" and "Book."
   
3. 
   One-to-One Relationship: A "Member" can have only one active "Transaction" at a time, and a "Transaction" belongs to only one "Member."
   

Additionally, the "Transaction" entity has an extra attribute called "Return Date," which represents the date when a borrowed book is expected to be returned. The ERD also includes a specialization where the entity "Member" is further divided into two specialized entities: "Student" and "Faculty," each with their specific attributes.

Section 2 - FD:

Given: R = (A, B, C, D, E, G) F = {A->B, B->CD, A->E, E->G}

1. 
   BC+ (Closure of BC): BC+ = BC (initially) BC+ = BC ∪ CD (using B->CD) BC+ = BCDEG (using A->E and E->G)
   
2. 
   Candidate Keys for R: A is a candidate key for R since A->B and A->E (A determines all attributes). Therefore, A is a candidate key for R.
   
3. 
   Is R in BCNF? No, R is not in BCNF because A->B violates BCNF. A is a determinant, and B is not a superkey.
   
4. 
   Decomposition: To decompose R into a few relations in 3NF or BCNF, we can decompose it into two relations: R1(A, B, E, G) and R2(B, C, D). This decomposition is in 3NF. We didn't use BCNF because R1(A, B, E, G) is not in BCNF due to A->B.
   
5. 
   Canonical Coverage: No, this is not a canonical coverage. The canonical coverage for F = {A->B, B->CD, A->E, E->G} is: F = {A->B, A->E, B->CD, E->G}