Creating an ERD (Entity Relationship Diagram) using the business
rules provided to you by the Business Analyst working with the group that requires a database
for tracking orders and components. The following information has been developed to provide
the necessary details to create the ERD, DDL (Data Definition Language) commands and sample
data to verify that the business rules are legitimate which will enable an application to be
created for this purpose.
For our replacement part database system, we have defined the following five entities:
 SalesReps
 Customers
 Orders
 Items
 Parts
The following relationships exist between these entities:
 A salesrep can assist many customers and each customer can have only one salesrep
assigned their account.
 A customer can have many orders, but every order must have only one customer to be
created and processed.
 A order can be comprised of many items, but for an order to generate, it must contain
at least order number.
 An item is comprised on many parts, but each item must have at least one part.
The following table structure has been provided by the business analysts as clarification on the
necessary attributes and datatypes required for each table.

 

 

 

SALESREPS
Attribute Datatype Nullable Referential Integrity
SID NUMBER(10,0) No PK
LASTNAME VARCHAR2 (100) No
FIRSTNAME No
COMMISSION Yes
CUSTOMERS
Attribute Datatype Nullable Referential Integrity
C_ID NUMBER(10,0) No PK
LASTNAME VARCHAR2 (100) No
FIRSTNAME VARCHAR2 (50) No
COMPANYNAME VARCHAR2 (100) No
STREET VARCHAR2 (100) Yes
CITY VARCHAR2 (100) Yes
STATE VARCHAR2 (50) Yes
ZIPCODE NUMBER(10,0) Yes
PHONE VARCHAR2 (30) No
EMAIL VARCHAR2 (100) No
SID NUMBER(10,0) No
ORDERS
Attribute Datatype Nullable Referential Integrity
O_ID NUMBER(10,0) No PK
C_ID NUMBER(10,0) No
ORDERDATE DATE No
SHIPDATE DATE Yes
PAIDDATE DATE Yes
STATUS CHAR(1) Yes
ITEMS
Attribute Datatype Nullable Referential Integrity
O_ID NUMBER(10,0) No
ITEM_ID NUMBER(10,0) No PK
PID NUMBER(10,0) No
QUANTITY NUMBER(10,0) No

 

 

 

PARTS
Attribute Datatype Nullable Referential Integrity
PID NUMBER(10,0) No PK
DESCRIPTION VARCHAR2 (250) Yes
UNITPRICE NUMBER(10,2) Yes
ONHAND NUMBER(10,0) Yes
REORDER NUMBER(10,0) Yes
Part #1 - write  ERD diagram using the information supplied above. Show the attributes,
relationships and cardinality for all tables. Make sure you identify and include the foreign key
relationships for each of the tables. (50 points)
Part #2 - Using the ERD diagram created in Part #1, list the create table commands using the
correct DDL statements for all (5) tables. Use the data in the charts to properly formulate the
create table statement. (25 points)
Part #3 - Once the tables have been created. List (5) rows of sample data that will be contained
within each of the tables. List the 5 rows of data using the table feature in MS Word to clearly
show the sample data for each table. (25 points)
Make sure to include all three sections in your final report