Question 1. The Perron Electronics Assembly Company (PEAC) needs a database to track its training program. PEAC has several trainers and they offer various courses for their employees. Courses are offered numerous times throughout the year. Each course offering is taught by just one trainer and every trainer is capable of teaching all of the courses offered through PEAC. Design a set of tables and relationships for PEAC in 3NF. Represent your answer in DBDL (i.e. give table name & fields. Underline primary key.) Draw an Entity-Relationship Diagram using Visio or Lucid Charts saved in document or as a PDF.
Note: for all ER-Diagrams on this test, it is not necessary to list the attributes, just use rectangles with appropriate connecting lines to document Entities and Relationships but showing attributes can help you.
Identify any assumptions you made that will assist in my understanding of your solution. (Must be included)
Question 2. Design a database for customer tracking service for Customer Resource Application Processing (CRAP) that tracks Customers, Salesreps, and openOrders. A SalesRep can have multiple customers. For the SalesRep, Track the firstname, lastname, street, city, postal, basepay and commissionrate and hiredate. There can be multiple orders for a customer. For the Customer, firstname, lastname, street, city, postal, customertype, balance, amountpaid. For the Orders, track the Orderamount, customer, and orderdate
Create a set of tables for the company CRAP in 3NF. Represent your answer in DBDL. (i.e. give table name and fields. Use good naming conventions. Underline the primary key.) Draw an Entity-Relationship Diagram using Visio or Lucid Charts saved in document or as a PDF.
Identify any assumptions you made that will assist in my understanding of your solution. (Must be included)
Question 3. You are familiar with the TAL Distributors database (chapter 1 in Concepts) and its set of 3NF relations.
A fictitious company, named ABC, uses a similar database design. As compared to TAL, the ABC database uses the exact same structure for the Orders, OrderLine, and Item tables. However, in place of the Rep and Customer tables (of the TAL database), the ABC Company uses the table structures below:
ABCRep (RepNum, RepLName, RepFName, RepStreet, RepCity, RepState, RepZip, RepSalary)
ABCCustomer (CustNum, CustName, CustStreet, CustCity, CustState, CustZip, CustBalance, CustCreditlimit)
ABCRepCust (RepNum, CustNum, CommissionRate)
For Firm ABC, each of the above relations is in 3NF. What 2 differences must exist in the company policies of Firm ABC as compared to TAL Distributors?
Explain your answer based on the assumptions of the relationship differences.
Question 4. The Brown Accounting and Recount Firm (BARF) has contacted you to create a personnel database to keep track of their employees. The following constraints are available:
• The company has just one single location
• The company is organized into departments
• Each department has multiple employees
• Each employee is assigned to one department at a time
• Each employee has a personnel history (promotions, raises, transfers, etc.)
• Each employee is assigned a phone number that does not change
• Each employee is assigned to an office which could change (when transferred, promoted, etc.)
Draw an ER-Diagram of your database design for BARF and document a reasonable number of fields for your tables using DBDL (your db design must be in 3NF!).
Identify any assumptions you made that will assist in my understanding of your solution. (Must be included)
Question 5. The table below lists sample Advisor/Student data, where AdvNum and StuNum form a composite primary key like the homework from chapter 5 and 6.
|
AdvNum
|
StuNum
|
AdvName
|
AdvOffice
|
StuName
|
StuMajor
|
|
S101
|
P100
|
T. Jones
|
10-110A
|
G. White
|
CIS
|
|
S101
|
P105
|
T. Jones
|
10-110A
|
J. Bell
|
MATH
|
|
A264
|
P108
|
M. Thomas
|
10-110B
|
I. McKay
|
ENG
|
|
A264
|
P110
|
M. Thomas
|
10-110B
|
P. Wax
|
CIS
|
|
V555
|
P105
|
R. Brown
|
10-111A
|
J. Bell
|
MATH
|
|
V555
|
P110
|
R. Brown
|
10-111A
|
P. Wax
|
CIS
|
Give examples of insertion, deletion and update anomalies with the above table. (No SQL statements, just an example/description of each anomaly based on the data).
Question 6. Convert the table from task number 5 above into a set of tables in 3NF. Draw an ER- Diagram of your database design for the Advising data and document the fields for your tables using DBDL.
Identify any assumptions you made that will assist in my understanding of your solution. (Must be included)
Bonus:
Question 7. You are in charge of keeping track of the players, coaches and referees in an amateur football league. The following design has been given to you, and you need to correct it!
Referee (lastname, firstname, street, city, state, phone, division) Coach (Division, phone, lastname, firstname)
Player (lastname, firstname, division, coach) Division (name, referee, coach, area)
Correct and create a set of tables for the league in 3NF. Represent your answer in DBDL. (i.e. give table name and fields. Underline the primary key.) Draw an entity- relationship diagram.
Identify any assumptions you made that will assist in my understanding of your solution. (Must be included)