DATABASE MANAGEMENT SYSTEMS ASSIGNMENT
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.
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.
Identify any assumptions you made that will assist in my understanding of your solution. (Must be included).
2. The Greene Valley Softball Conference is an amateur softball league which is made up of numerous teams. Each team has multiple coaches and multiple players. During the season, each team plays two games (home and away) against each of the other teams in the league. The only required statistic that must be captured is the total number of runs scored by each team in each game. Draw an E-R Diagram and document the tables and fields needed for your design using DBDL (in 3NF!).
Identify any assumptions you made that will assist in my understanding of your solution. (Must be included)
3. You are familiar with the TAL Distributors database 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.
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)
5. The table below lists sample Advisor/Student data, where Adv# and Stu# form a composite primary key.
|
Adv #
|
Stu #
|
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).
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)