1 DATA MODEL (Fig. 5.9 / p.165) Entity Relationship Diagram Data Schema (Relational Data Model) CUSTOMER (Cust_Code, Cust_Name, Cust_City, Credit_Limit, S a les_YTD, Sales_Person) ORDER (SO_Number, SO_Date, CustOrderNo, Cust_Code) SALES (Shipment_Number, InvoiceNo, SO_Number, Cust_Code) INVENTORY (Item_Number, Item_Name, Qty_On_Hand, Unit_Cost, Unit_Price) ORDER_INVENTORY (SO_Number, Item_Number, Qty_Or dered) SALES_INVENTORY (Shipment_Number, Item_Number, Qty_Shipped) INVENTORY ORDERCUSTOMERSALES2 Data Dictionary Table: CUSTOMER Attributes Date Type/Size Definition Authorization Cust_Code CHAR (15) Customer Code (Primary Key) Cust_Name CHAR (50) Customer Name Cust_City CHAR (15) Customer City Credit_Limit CURRENCY Crediet Limit Sales_YTD CURRENCY Sales Year To Date Sales_Person CHAR (25) Sales Rep Table: ORDER Attributes Date Type/Size Definition Authorization SO_Number CHAR(9) Order No (Primary Key) SO_Date DATE Date CustOrderNo CHAR(15) Reference from Customer Cust_Code CHAR (15) Foreign Key link to Table CUSTOMER Table: SALES Attributes Date Type/Size Definition Authorization Shipment_Number INTEGER (5) Shipment No (Primary Key) InvoiceNo INTEGER (5) Invoice Number SO_Number CHAR(9) Foreign Key link to Table ORDER Cust_Code CHAR (15) Foreign Key link to Table CUSTOMER 3 Table: INVENTORY Attributes Date Type/Size Definition Authorization Item_Number CHAR (9) Item No (Primary Key) Item_Name CHAR (20) Item Name Qty_On_Hand INTEGER (4) Quantity On Hand Unit_Cost CURRENCY Unit Cost Unit_Price CURRENCY Unit Price Table: ORDER_INVENTORY Attributes Date Type/Size Definition Authorization RecNo AUTONUMBER Surrogate Key (Primary Key) SO_Number CHAR(9) Part of Composite Key link to Table ORDER Item_Number CHAR (9) Part of Composite Key link to Table INVENTORY Qty_Ordered INTEGER (4) Quantity On Hand Table: SALES_INVENTORY Attributes Date Type/Size Definition Authorization RecNo AUTONUMBER Surrogate Key (Primary Key) Shipment_Number INTEGER (5) Part of Composite Key link to Table SALES Item_Number CHAR (9) Part of Composite Key link to Table INVENTORY Qty_Shipped INTEGER (4) Quantity Shipped
View Full Document