Question

VEND CODE -> VEND NAME Perry Computer Inc. uses the following INVOICE table keep track of their data. Relational schema is listed below. INVOICE (INV NUM, PROD NUM, SALE_DATE, PROD_DESCRIPTION, VEND_CODE, VEND_NAME, NUM SOLD, PROD_PRICE) Where: INV NUM -> SALE DATE PROD NUM-PROD DESCRIPTION, VEND_CODE, VEND NAME, PROD PRICE QUESTION: C. Propose a better design (with no data redundancy) and indicate the normal form of your table(s). Please use relational schema format like the INVOICE table in Perry's original design. Properly name each table. Clearly indicate the primary keys with bold and/or underlines and any foreign keys with "(FK)" next to the attribute. If the foreign key is also a primary key, use underline and "(FK)"

Answer 1

Answer)

Converting to BCNF since it is the highest normal form:

Table 1: Invoice(Inv_Num, Sale_Date) | Primary key : Inv_Num
FD:
Inv_Num -> Sale_Date

Table 2: InvoiceProduct(Inv_Num, Prod_Num, Num_Sold) | Primary key: Composite key of Inv_Num, Prod_Num | Foreign key: Inv_Num will refer to the Invoice(Inv_Num) | Foreign key: Prod_Num will refer to the Product(Prod_Num)

Table 3: Vendor(Vend_Code, Vend_Name) | Primary key : Vend_Code
FD:
Vend_Code -> Vend_Name

Table 4: Product(Prod_Num, Vend_Code, Prod_Description, Prod_Price) | Primary key: Composite key of Prod_Num, Vend_Code | Foreign key here will be the Vend_Code will be referring to the Vendor(Vend_Code) | Foreign key: Prod_Num will refer to the Product(Prod_Num)
FD:
Prod_Num -> Vend_Code, Prod_Description, Prod_Price

Conversion complete for the tables into normalized forms.

**Please Hit Like if you appreciate my answer. For further doubts on the question or answer please drop a comment, I'll be happy to help. Thanks for posting.**