Homework Solution: Employee Branch Publisher Holds Work-shift Author Book Figure 1: Sample incomplete ER Diagram for SBH…

    Employee Branch Publisher Holds Work-shift Author Book Figure 1: Sample incomplete ER Diagram for SBH QUESTION: Identify all functional dependencies for each relation of the SBH database. Then state which normal form (1NF, 2NF, 3NF or BCNF) each of the existing relation is in, and why?
    Employee Branch Publisher Holds Work-shift Author Book Figure 1: Sample incomplete ER Diagram for SBH

    Expert Answer

     
    a) The functional dependencies for the given SBH database for each of the relation are determined as ,

    Employee Branch Publisher Holds Work-shift Author Book Figure 1: Sample defective ER Diagram restraint SBH

    QUESTION:
    Identify total administrative dependencies restraint each affinity of the SBH database. Then recite which regular create (1NF, 2NF, 3NF or BCNF) each of the solid affinity is in, and why?

    Employee Branch Publisher Holds Work-shift Author Book Figure 1: Sample defective ER Diagram restraint SBH

    Expert Repartee

     

    a) The administrative dependencies restraint the dedicated SBH database restraint each of the affinity are fixed as ,

    Branch->Book ; Administrative dependency is necessary

    Author->Publisher ; Administrative dependency is necessary

    Author->Book; Administrative dependency is necessary

    Employee->Employee;

    The dedicated Funtional dependencies are necessary  if the strange seclusion does referable inclose the right-agency policy mark of the removed administrative dependency.

    The minimal shield firm of administrative dependencies restraint dedicated affinity :

    F_min { Author->Book, Publisher ; Branch->Book }

    The liberal firm of nonappreciationappreciation inconsiderable administrative dependencies is :

    Author,Branch,Publisher → Book

    b) The input affinity Branch->Book , Author->Publisher, Author->Book Employee->Employee; are introduce in First regular create owing each mark is antecedent to inclose barely undivided treasure per tier.

    They are referable in second regular create and third regular create owing the dedicated stipulations are referable content ,  a) The right-agency policy is a subfirm of the left agency policy, (2) the left-agency policy is a superclew of the affinity, or (3) the right-agency policy is some poverty clew of the affinity. The administrative dependencies that failed are: Author → Book; Author → Publisher; Branch → Book.

    The affinitys are referable in BCNF create owing they are referable in 3N create.