The problems below use the Customer, OrderTbl, and Employee tables of a simplified Order Entry database. The Customer table records clients who have placed orders. The OrderTbl contains the basic facts about customer orders. The Employee table contains facts about employees who take orders. The primary keys of the tables are CustNo for Customer, EmpNo for Employee, and OrdNo for OrderTbl.
The following is the TABLE definitions for the first three tables showing the PRIMARY KEY constraints.
|
TABLE Customer has the following attributes
CustNo CHAR(8)
CustFirstName VARCHAR2(20)
CustLastName VARCHAR2(30)
CustCity VARCHAR2(30)
CustState CHAR(2)
CustZip CHAR(10)
CustBal NUMBER(12,2)
CustNo is the PRIMARY KEY
|
|
TABLE Employee has the following attributes
EmpNo CHAR(8)
EmpFirstName VARCHAR2(20)
EmpLastName VARCHAR2(30)
EmpPhone CHAR(15)
EmpNo is the PRIMARY KEY
|
|
TABLE OrderTbl has the following attributes
OrdNo CHAR(8)
OrdDate DATE
CustNo CHAR(8)
EmpNo CHAR(8)
OrdNo is the PRIMARY KEY
|
Step 1 - Drawing the first ER Diagram
Identify the foreign key columns in the table structures above and draw a relationship diagram depicting the relationships between the tables. In the Order Table (OrderTbl in the diagram), the CustNo column references the Customer table and the EmpNo column references the Employee table. For each relationship, identify the table on the "1" side of the relationship and the table on the "Many" side.
Step 2 - Should a NULL be allowed?
From examination of the sample data and your current understanding of businesses in which orders could be placed either in person, over the phone, or over the internet determine if it would be permissible for null values to be allowed for the foreign key Empno in the OrderTbl table? This column would reference back to the empno column in the employee table. Why or why not?
|
OrdNo
|
OrdDate
|
CustNo
|
EmpNo
|
|
O1656777
|
02/11/2000
|
C8543321
|
|
|
O7959898
|
02/19/2000
|
C8543321
|
E8544399
|
Step 3 - Drawing the second ER Diagram
In a separate diagram, extend your relationship diagram from problem 1 by adding two tables (OrdLine and Product). Table definitions for the table structures and primary keys constraints are shown below. You will need to identify the FOREIGN KEY(s) to complete this problem. When finished you should have a total of 5 tables represented in your diagram for this problem.
|
TABLE Product has the following attributes
ProdNo CHAR(8)
ProdName VARCHAR2(20)
ProdNo is the PRIMARY KEY
|
|
TABLE OrdLine has the following attributes
OrdNo CHAR(8)
ProdNo CHAR(8)
Qty NUMBER(5)
The combination of OrdNo and Prodno is the PRIMARY KEY
|