ITEC 340
Database I
Homework 3
The following table which is being maintained shows information on adjuncts and the classes they teach.
Only the first few rows of the table are shown.
ssn lname fname rank dept college cousePrefix courseNum section courseName Credits  stipend  budget1 perc1 budget2 perc2 budget3 perc3
111-11-1111 Jones David Instructor ACTG COBE ACTG 211 01 Principles of Financial Accounting 3  $    3,200 D456 100%        
111-11-1111 Jones David Instructor ACTG COBE ACTG 211 02 Principles of Financial Accounting 3  $    3,200 D456 100%        
111-11-1111 Jones David Instructor FINC COBE FINC 321 01 Introduction to Business Finance 3  $    3,200 D876 100%        
111-22-3333 Krasno Jeff Instructor ACTG COBE BLAW 203 01 Legal Environment of Business 3  $    5,000 D456 50% R875 50%    
222-22-2222 Smith Robert Assistant FINC COBE FINC 321 02 Introduction to Business Finance 3  $    4,200 D876 100%        
222-22-2222 Smith Robert Assistant FINC COBE FINC 511 03 Finance for MBAs 3  $    4,800 D876 50% G321 50%    
333-33-3333 Toms Becky Instructor ACTG COBE ACTG 201 01 Survey of Accounting 3  $    3,000 D456 100%        
333-33-3333 Toms Becky Instructor ACTG COBE ACTG 201 02 Survey of Accounting 3  $    3,000 R111 100%        
333-33-3333 Toms Becky Instructor ITEC CIST ITEC 100 01 Introduction to Information Technology 3  $    3,400 D777 100%        
333-33-3333 Toms Becky Instructor ITEC CIST ITEC 120 01 Principles of Computer Science I 4  $    4,400 D777 50% G321 25% R111 25%
In addition to the sematics implied by the sample data assume that it is possible for two instructors to have the same name (but obviously a different ssn)
Make reasonable assumptions for any other relationships not clearly articulated by the sample data.
Perform lossless decomposition on the table above such that the resulting relations are in at least BCNF.
Show the same sample data in your normalized tables.
Make sure you identify all Primary Keys in your solution.
Consider the following table:
Submarines
SubName Class TopSpeed Overhauled 2nd Overhaul 3rd Overhaul
Nevada Typhoon 50 knots Jan-95    
King Shark 35 knots Mar-81 Dec-87 May-95
Tiger Shark 35 knots Sep-85 Feb-94  
Hoover Typhoon 50 knots Mar-93 1-Jan  
Jackson Shark 35 knots Feb-80 Jan-88 Jul-98
Perform lossless decomposition on the table above such that the resulting relations are in at least BCNF.
Show the same sample data in your normalized tables.
Make sure you identify all Primary Keys in your solution.
R1
A B C D
20 red California 76854
20 blue Ohio 15767
30 red California 889934
40 blue Ohio 576342
40 green California 49734
50 purple Texas 49734
50 blue Ohio 87003
60 green California 29
Indicate what normal form is violated by the above relation
Perform lossless decomposition on the table above such that the resulting relations are in at least BCNF.
Show the same sample data in your normalized tables.
Make sure you identify all Primary Keys in your solution.
Create a folder named RU03 in your ITEC 340 student folder.
Submit a single excel spreadsheet with three tabs, one tab for each of the above problems.
Name the spreadsheet Normalization.xls
Have the spreadsheet in your RU03 folder by 5:00 p.m. on Thursday, Sept. 22nd.