Answer Happy

A. Convert this data model to a database design. Specify tables, primary keys, and foreign keys. Using Figure 5-26 and Figure 5-28 as guides, specify column properties.
FIGURE 5-26 Continued Column Name InvoiceNumber LineNumber Quantity UnitPrice Total Column Name ProductNumber Product Type Product Description UnitPrice QuantityOnHand Data Type (Length) Integer Integer Integer No Numeric (9,2) No Numeric (9,2) No Data Type (Length) Key Primary Key, Foreign Key Primary Key Char (35) Char (24) Key Primary Key No VarChar (100) No Numeric (9, 2) No Integer No Required Yes Yes No No No (e) LINE_ITEM Required Yes Yes Yes Yes Yes (f) PRODUCT Default Value None None None None None Default Value None None None None 0 Remarks REF: INVOICE This is not quite a Surrogate Key-for each InvoiceNumber: Increment=1 Application logic will be needed to supply the correct value Remarks
FIGURE 5-28 Modified Column Specifications for HSD Tables with Added Foreign Keys Data Type (Length) Integer Column Name SeminarID CustomerID Column Name CustomerID Contact Number Contact Date Contact Type SeminarID Column Name InvoiceDate CustomerID Payment Type Subtotal Shipping Integer Tax Total Data Type (Length) Integer Integer InvoiceNumber Integer Date Char (15) Integer Data Type (Length) Date Integer Char (25) Key Primary Key, Foreign Key Primary Key, Foreign Key Key Primary Key, Yes Foreign Key Primary Key Yes No No Foreign Key (a) SEMINAR_CUSTOMER No Numeric (9,2) No Numeric (9,2) No Numeric (9,2) No Numeric (9,2) No Required Yes Required Default Value None Key Primary Key Yes Yes No Yes Foreign Key Yes Yes No No No No Yes Yes No (b) CONTACT None None None None (c) INVOICE Default Value None None None Cash None None None None None Required Default Value DBMS supplied Remarks REF: CUSTOMER Remarks This is not quite a Surrogate Key-for each Contact Number: Start=1 Increment=1 Application logic will be needed to supply the correct value Format: yyyy-mm-dd REF: SEMINAR Remarks Surrogate Key: Initial value=35000 Increment=1 Format: yyyy-mm-dd REF: CUSTOMER