Write the relational schema, draw its dependency diagram, and identify all depen
ID: 3798287 • Letter: W
Question
Write the relational schema, draw its dependency diagram, and identify all dependencies, including all partial and transitive dependencies. You can assume that the table does not contain repeating groups and that an invoice number references more than one product. (This table uses a composite primary key.) Remove all partial dependencies, write the relational schema, and draw the new dependency diagrams. Identify the normal forms for each table structure you created. You can assume that any given product is supplied by a single vendor, but a vendor can supply many products. Therefore, it is proper to conclude that the following dependency exists: PROD_NUM PROD_LABEL, PROD_PRICE, VEND_CODE, VEND_NAME Remove all transitive dependencies, write the relational schema, and draw the new dependency diagrams. Also, identify the normal forms for each table structure you created. Draw the Crow's Foot ERD.Explanation / Answer
Ans a - Relational Schema- It is created by listing down all the possible classes or entities in the system and their respective attributes.
PRODUCT(PROD_NUM,PROD_LABEL,PROD_PRICE)
SALES(INV_NUM,PROD_NUM,SALE_DATE,VEND_CODE,QUANT_SOLD)
VENDOR(VEND_CODE,VEND_NAME)
Dependency Diagrams-
1. INV_NUM --> PROD_NUM,PROD_LABEL,PROD_PRICE,VEND_CODE,VEND_NAME,QUANT_SOLD, SALE_DATE
2. PROD_NUM--> PROD_LABEL,PROD_PRICE,VEND_CODE,VEND_NAME
3. VEND_CODE-->VEND_NAME
Partial Dependencies--
Consider INV_NUM ,VEND_CODE and PROD_NUM as the PK for this relational table. Hence, Functional depency will be as follows
INV_NUM,PROD_NUM,VEND_CODE-->PROD_LABEL,PROD_PRICE,VEND_NAME,QUANT_SOLD, SALE_DATE
and partial dependencies are as below-
INV_NUM--> PROD_PRICE, INV_NUM-->PROD_LABEL, INV_NUM-->PROD_PRICE,
INV_NUM-->QUANT_SOLD, INV_NUM-->SALE_DATE,
PROD_NUM-->PROD_LABEL, PROD_PRICE
VEND_CODE-->VEND_NAME
Transitive Dependencies-- It occurs when one non-key attribute depends on another non-key attribute-
1. INV_NUM-->PROD_LABEL
since ( INV_NUM--> PROD_CODE and PROD_CODE-->PROD_LABEL)
2 INV_NUM-->PROD_PRICE
since( INV_NUM--> PROD_CODE and PROD_CODE-->PROD_PRICE)
3. INV_NUM-->VEND_NAME
since(INV_NUM-->VEND_CODE and VEND_CODE-->VEND_NAME)
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.