Question

In: Computer Science

Consider the following schema and functional dependencies: SHIPPING (ShipName, ShipType, VoyageID, Cargo, Port, ArrivalDate) Key: ShipName,...

Consider the following schema and functional dependencies:

SHIPPING (ShipName, ShipType, VoyageID, Cargo, Port, ArrivalDate) Key: ShipName, ArrivalDate FD1: ShipName > ShipType FD2: VoyageID > ShipName, Cargo FD3: ShipName, ArrivalDate > VoyageId, Port

1.Please list the final set of 3NF schema including all its keys.

2.Do any of the finalized 3NF schema have determinates that are not candidate keys? If yes, explain - which schema(s)? Why?  

Solutions

Expert Solution

1.

SHIPPING (ShipName, ShipType, VoyageID, Cargo, Port, ArrivalDate) IS THE GIVEN RELATION.

Key: ShipName, ArrivalDate

FD1: ShipName > ShipType

FD2: VoyageID > ShipName, Cargo

FD3: ShipName, ArrivalDate > VoyageId, Port

ISSUES IN 3NF :::

STEP :01     1. IT MUST BE IN 1NF

       EXPLANATION : There doesn't exist any two identical rows in a given relation or table.

IN GENERAL EVERY RELATION IS IN 1NF. SO THE GIVEN RELATION IS ALSO IN 1NF.


       STEP :02 2. IT MUST BE IN 2NF

        EXPLANATION : THERE DOESN'T EXIST ANY PARTIAL DEPENDENCIES.

        PARTIAL DEPENDENCY : A NON-PRIME ATTRIBUTE MUST NOT BE DEPENDENT ON A NON-PRIME   

       ATTRIBUTE.

        i,e., THERE SHOULD NOT BE ANY FD IS OF THE FORM

        NON-PRIME -----> NON-PRIME

        PRIME ATTRIBUTE : A PRIME ATTRIBUTE IS THE PROPER SUB SET OF A KEY (CANDIDATE KEY).

        NON PRIME ATTRIBUTES : ATTRIBUTES WHICH ARE NOT PRIME ATTRIBUTES.

        CANDIDATE KEY : IT A ATTRIBUTE OR SET OF ATTRIBUTES WHICH HELPS TO UNIQUELY IDENTIFY EACH ROW IN A RELATION.

        PRIME ATTRIBUTES FOR THE GIVEN RELATION : SHIPNAME , ARRIVALDATE

        NON PRIME ATTRIBUTES FOR THE GIVEN RELATION : ShipType, VoyageID, Cargo, Port

        CANDIDATE KEY FOR THE GIVEN RELATION : (SHIPNAME , ARRIVALDATE)

       EXAMINING THE FUNCTIONAL DEPENDENCIES IN THE GIVEN RELATION :

        IN FD 1 ::::: FD1: ShipName > ShipType   [PRIME > NON-PRIME ] [ SO NO PARTIAL DEPENDENCY]

        IN FD 2 ::::: FD1: VoyageID > ShipName, Cargo

        i.e,. BY LAW OF DECOMPOSITION

             VoyageID > ShipName   [NON-PRIME > PRIME] [SO NO PARTIAL DEPENDENCY]

              VoyageID > Cargo         [NON-PRIME > NON-PRIME] [SO PARTIAL DEPENDENCY]

       SO HERE WE DECOMPOSE THE RELATIONS SUCH THAT THERE IS NO PARTIAL DEPENDENCIES IN ALL

        RELATIONS .

        THEREFORE SHIPPING (ShipName, ShipType, VoyageID, Cargo, Port, ArrivalDate)

       WITH FUNCTIONAL DEPENDENCIES :

        FD1: ShipName > ShipType

        FD2: VoyageID > ShipName, Cargo

        FD3: ShipName, ArrivalDate > VoyageId, Port

       WILL DECOMPOSE INTO R1(VoyageID , Cargo) AND R2(ShipName, ShipType, Port, ArrivalDate,VoyageId)

       NOW R1 HAS THE KEY VoyageID AND R2 HAS THE KEY (ShipName, ArrivalDate)

       NOW IN BOTH THE RELATIONS THERE ARE NO PARTIAL DEPENDENCIES.

       HERE WE ARCHIVED IT BY DECOMPOSING THE GIVEN RELATION INTO SEVERAL RELATIONS

       WHEN THERE EXIST A PARTIAL DEPENDENCY.

     NO THE GIVEN RELATION IS IN 2NF.

      

STEP :03 :::: 3. THERE DOESN'T EXIST ANY PARTIAL DEPENDENCIES :

       TRANSITIVE DEPENDENCY : A NON-PRIME ATTRIBUTE MUST NOT BE DEPENDENT ON A PRIME ATTRIBUTE.

        i,e., THERE SHOULD NOT BE ANY FD IS OF THE FORM

        PRIME -----> NON-PRIME

        EXAMINING THE FUNCTIONAL DEPENDENCIES IN R1: R1(VoyageID , Cargo)

        IN R1 THERE IS ONLY ONE FUNCTIONAL DEPENDENCY VoyageID > Cargo [KEY --- > NON-PRIME]

        [SO NO TRANSITIVE DEPENDENCY]

       EXAMINING THE FUNCTIONAL DEPENDENCIES IN R2:

        R2(ShipName, ShipType, Port, ArrivalDate ,VoyageId)

       FD1: ShipName > ShipType   [PRIME ---- > NON-PRIME] [TRANSITIVE DEPENDENCY]

       SO WE DECOMPOSE THE RELATION R2 SUCH THAT THERE IS NO TRANSITIVE DEPENDENCY.

       SO THE RELATION R2 BECOMES R2(ShipName, ShipType) AND

        R3(ShipName, Port, ArrivalDate ,VoyageId)

       NOW IN R2 ShipName is the key and in R3 (ShipName , ArrivalDate) is the key

     

       FD2: VoyageID > ShipName [NON-PRIME ---- > PRIME] [ NO TRANSITIVE DEPENDENCY]

      FD3: ShipName, ArrivalDate > VoyageId, Port [KEY ---- > NON-PRIME] [ NO TRANSITIVE DEPENDENCY]

     NO THE GIVEN RELATION IS IN 3NF .

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Final set of 3NF schema including all its keys

R1(VoyageID , Cargo) , R2(ShipName, ShipType) , R3(ShipName, Port, ArrivalDate ,VoyageId)

     IN R1 :::::

    FUNCTIONAL DEPENDENCIES ::: VoyageID > Cargo

    PRIMARY KEY :::: VoyageID

   FOREIGN KAY :::: VoyageID   FROM R3

    IN R2 ::::::

    FUNCTIONAL DEPENDENCIES ::: ShipName > ShipType

    PRIMARY KEY :::: ShipName

   FOREIGN KAY :::: ShipName FROM R3

    IN R3 :::::::::

    FUNCTIONAL DEPENDENCIES ::: VoyageID > ShipName AND ShipName, ArrivalDate > VoyageId, Port

    PRIMARY KEY ::::ShipName, ArrivalDate

   FOREIGN KAY :::: NO FOREIGN KEY

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2 . YES , any of the finalized 3NF schema have determinants that are not candidate keys because here in 3NF we are only restricting the functional dependencies of the form [prime --- > non-prime ] and

[non-prime ---- > non-prime] but not [prime ---- > prime ] or [non-prime ---- > prime].

So here for every finalized 3NF there is a chance of determinants that are not candidate keys but prime or non-prime attributes depending upon the given functional dependencies in the given relation.

HERE FOR THE GIVEN RELATION WHICH IS FINALLY IN 3NF HAS DETERMINANT VoyageID in the functional dependency VoyageID > ShipName.

PLEASE DO VOTE : )


Related Solutions

Consider the following relational schema and set of functional dependencies. S(A,B,C,D,E,F,G) D → E E →...
Consider the following relational schema and set of functional dependencies. S(A,B,C,D,E,F,G) D → E E → B C → FG BE → AC Is the decomposition of S into S1(E,G,F) and S2(A,B,C,D,G) a lossless join decomposition? Choose one of the following queries as your answer: SELECT ’lossy’; SELECT ’lossless’;
Consider a relation R (ABCDEFGH) with the following functional dependencies: ACD --> EF AG --> A...
Consider a relation R (ABCDEFGH) with the following functional dependencies: ACD --> EF AG --> A B --> CFH D --> C DF --> G F --> C F --> D Find minimal cover and identify all possible candidate keys. In order to receive full credit, please list each step taken and the rules that you applied.
Task 1. For each table on the list, identify the functional dependencies. List the functional dependencies....
Task 1. For each table on the list, identify the functional dependencies. List the functional dependencies. Normalize the relations to BCNF. Then decide whether the resulting tables should be implemented in that form. If not, explain why. For each table, write the table name and write out the names, data types, and sizes of all the data items, Identify any constraints, using the conventions of the DBMS you will use for implementation. Write and execute SQL statements to create all...
Consider the following functional dependencies: Z -> XYD, X -> Y. Find the minimal cover of...
Consider the following functional dependencies: Z -> XYD, X -> Y. Find the minimal cover of the above.
1. Consider the following functional dependencies: Z -> XYD, X -> Y Find the minimal cover...
1. Consider the following functional dependencies: Z -> XYD, X -> Y Find the minimal cover of the above. 2. Consider the following two sets of functional dependencies: F = {A -> C, AC -> D, E -> AD, E -> H} and G = {A -> CD, E -> AH}. Check whether they are equivalent.
Consider the following relation R(A, B, C, D, E, G) and the set of functional dependencies...
Consider the following relation R(A, B, C, D, E, G) and the set of functional dependencies F={ A → BCD, BC → DE, B→D, D → A} Note: Show the steps for each answer. (a) Compute B+ . (b) Prove (using Armstrong’s axioms) that AG is superkey. (c) Compute Fc. (d) Give a 3NF decomposition of the given schema based on a canonical cover. (e) Give a BCNF decomposition of the given schema based on F. Use the first functional...
Consider the relational schemas given below and the respective sets of functional dependencies valid in the...
Consider the relational schemas given below and the respective sets of functional dependencies valid in the schemas. For each one of the relational schemas identify its highest normal form. Remember that the identification of a normal form requires analysis of the valid functional dependencies and the minimal keys. Provide justification of each answer. A solution with no comprehensive justification scores no marks. (iv) When a staff is required to undertake an inspection of properties, the staff is allocated a company...
Determine the Functional Dependencies that exist in the following Orders table. This table lists customer and...
Determine the Functional Dependencies that exist in the following Orders table. This table lists customer and order data. Orders (SupplierNum, SupplierName, Supp_Phone, ProductNum, Description, Product_type, QuotedPrice) . Normalize the above relation to 3 rd normal form, ensuring that the resulting relations are dependency-preserving and specify the primary keys in the normalized relations by underlining them.
Using your knowledge of the college environment, determine the functional dependencies that exist in the following...
Using your knowledge of the college environment, determine the functional dependencies that exist in the following table. After determining the functional dependencies convert this table to an equivalent collection of tables that are in third normal form. Student (StudentNum, StudentName, NumCredits, AdvisorNum,AdvisorName, DeptNum, DeptName, AdvisorNum, AdvisorName, DeptNum, DeptName, (CourseNum, Description, Term, Grade))
Normalize the following data by: a) identifying the functional dependencies, b) stating your assumptions, \ c)...
Normalize the following data by: a) identifying the functional dependencies, b) stating your assumptions, \ c) creating relations, and d) identifying primary and foreign keys. Customer Number| First Name |Last Name |Phone | Invoice Number| Date |Item Type |Quantity Item Price
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT