Purchase Solution

Simple Relationship Diagrams: ER diagrams

Not what you're looking for?

Ask Custom Question

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

Attachments
Purchase this Solution

Solution Summary

The expert examines simple relationship diagrams for ER diagrams.

Purchase this Solution


Free BrainMass Quizzes
Basic Computer Terms

We use many basic terms like bit, pixel in our usual conversations about computers. Are we aware of what these mean? This little quiz is an attempt towards discovering that.

Basic UNIX commands

Use this quiz to check your knowledge of a few common UNIX commands. The quiz covers some of the most essential UNIX commands and their basic usage. If you can pass this quiz then you are clearly on your way to becoming an effective UNIX command line user.

Basic Networking Questions

This quiz consists of some basic networking questions.

Word 2010: Tables

Have you never worked with Tables in Word 2010? Maybe it has been a while since you have used a Table in Word and you need to brush up on your skills. Several keywords and popular options are discussed as you go through this quiz.

Excel Introductory Quiz

This quiz tests your knowledge of basics of MS-Excel.