SQL
surbhi1693
incompleteanswer.docxCIS 3650 Group Project
List all functional dependencies and types (partial/transitive) are listed below as applicable:
SECTION I
A. Functional Dependencies:
1. ProjectNumber→ProjectName (partial dependency)
a. Each ProjectNumber is assigned a unique name
b. ProjectName does not identify ProjectNumber because another ProjectNumber could be added that has the same name
2. EmployeeNumber→EmployeeName, JobClass, ChargePerHour (partial dependency)
a. Each EmployeeNumber is assigned to only one employee
b. Each Employee Number is assigned to a JobClass which has the same ChargePerHour
c. EmployeeName does not determine EmployeeNumber because two (or more) employees could have the same name
d. JobClass and ChargePerHour are the same; table does not indicate employees in same JobClass have differential rates based on seniority
3. JobClass→ChargePerHour (transitive dependency)
a. Each JobClass charges a unique amount
b. ChargePerHour does not determine JobClass because if a new JobClass is created it could charge the same amount per hour as another JobClass. Although in the data given, this is not the case, it is a possibility.
4. ChargePerHour, HoursBilled→TotalCharge
a. The TotalCharge is equal to ChargePerHour X HoursBilled
b. TotalCharge does not determine ChargePerHour X HoursBilled because it is possible that another row could have a different combination that would result in the same TotalCharge amount
5. ProjectNumber, EmployeeNumber→ProjectName, EmployeeName, JobClass, ChargePerHour, HoursBilled, TotalCharge
a. Since ProjectNumber determines, ProjectName, EmployeeNumber, HoursBilled, TotalCharge and Employee Number determines EmployeeName, JobClass, ChargePerHour, the two determinates together will identify all columns listed
B. Multivalued Dependencies:
There are seven multivalued dependencies in the table, as listed below:
1. ProjectNumber and ProjectName would show multiple values in the table if entered per row.
2. EmployeeNumber, EmployeeName, JobClass, ChargePerHour, ChargePerHour columns all have values that repeat in the rows.
C. Candidate Keys:
The candidate keys are EmployeeNumber, ProjectNumber, JobID.
D. To 1NF: ProjectNumber is the Primary Key and the table meets the eight characteristics of a relation (Rows are unique and contain data about a single entity instance, order of columns/rows is unimportant, cells hold a single value, column entries hold data about the entity’s attributes, are of the same type, and have a unique name.)
|
PROJECT NUMBER |
PROJECT NAME |
EMPLOYEE NUMBER |
EMPLOYEE NAME |
JOB CLASS |
CHARGE PER HOUR |
HOURS BILLED |
TOTAL CHARGE |
|
15
15 |
Evergreen |
103 |
June E. Arbough |
Elec. Engineer |
$84.50 |
23.8 |
$2,011.10 |
|
15 |
Evergreen |
101 |
John G. News |
Database Designer |
$105.00 |
19.4 |
$2,037.00 |
|
15 |
Evergreen |
105 |
Alice K. Johnson * |
Database Designer |
$105.00 |
35.7 |
$3,748.50 |
|
15 |
Evergreen |
106 |
William Smithfield |
Programmer |
$35.75 |
12.6 |
$450.45 |
|
15 |
Evergreen |
102 |
David H. Senior |
Systems Analyst |
$96.75 |
23.8 |
$2,302.65 |
|
18 |
Amber Wave |
114 |
Annelise Jones |
Applications Designer |
$48.00 |
24.6 |
$1,183.26 |
|
18 |
Amber Wave |
118 |
James J. Frommer |
General Support |
$18.36 |
45.3 |
$831.71 |
|
18 |
Amber Wave |
104 |
Anne K. Ramoras * |
Systems Analyst |
$96.75 |
32.4 |
$3,134.70 |
|
18 |
Amber Wave |
112 |
Darlene M. Smithson |
DSS Analyst |
$45.95 |
44 |
$2,021.80 |
|
22 |
Rolling Tide |
105 |
Alice K. Johnson |
Database Designer |
$105.00 |
64.7 |
$6,793.50 |
|
22 |
Rolling Tide |
104 |
Anne K. Ramoras |
Systems Analyst |
$96.75 |
48.4 |
$4,672.80 |
|
22 |
Rolling Tide |
113 |
Delbert K. Joenbrood * |
Applications Designer |
$48.00 |
23.6 |
$1,135.16 |
|
22 |
Rolling Tide |
111 |
Geoff B. Wabash |
Clerical Support |
$26.87 |
22 |
$591.14 |
|
22 |
Rolling Tide |
106 |
William Smithfield |
Programmer |
$35.75 |
12.8 |
$457.60 |
|
25 |
Starflight |
107 |
Maria D. Alonzo |
Programmer |
$35.75 |
24.6 |
$879.45 |
|
25 |
Starflight |
115 |
Travis B. Bawangi |
Systems Analyst |
$96.75 |
45.8 |
$4,431.15 |
|
25 |
Starflight |
101 |
John G. News * |
Database Designer |
$105.00 |
56.3 |
$5,911.50 |
|
25 |
Starflight |
114 |
Annelise Jones |
Applications Designer |
$48.00 |
33.1 |
$1,592.11 |
|
25 |
Starflight |
108 |
Ralph B. Washington |
Systems Analyst |
$96.75 |
23.6 |
$2,283.30 |
|
25 |
Starflight |
118 |
James J. Frommer |
General Support |
$18.36 |
30.5 |
$559.98 |
|
25 |
Starflight |
112 |
Darlene M. Smithson |
DSS Analyst |
$45.95 |
41.4 |
$1,902.33 |
E. To 2NF: The table must be broken into smaller tables to eliminate partial dependencies. The tables below now have a single primary key, so they are in 2NF.
JOB CLASS
JobID (PK)
Job
ChargePerHour
1
Elec. Engineer
$84.50
2
Database Designer
$105.00
3
Programmer
$35.75
4
Systems Analyst
$96.75
5
Applications Designer
$48.00
6
General Support
$18.36
7
DSS Analyst
$45.95
8
Clerical Support
$26.87
PROJECT
ProjectNumber (PK)
ProjectName
15
Evergreen
18
Amber Wave
22
Rolling Tide
25
Starflight
|
EMPLOYEE |
|||
|
EMPLOYEE NUMBER (PK) |
EMPLOYEE NAME |
JOB CLASS |
CHARGE PER HOUR |
|
103 |
June E. Arbough |
Elec. Engineer |
$84.50 |
|
101 |
John G. News |
Database Designer |
$105.00 |
|
105 |
Alice K. Johnson * |
Database Designer |
$105.00 |
|
106 |
William Smithfield |
Programmer |
$35.75 |
|
102 |
David H. Senior |
Systems Analyst |
$96.75 |
|
114 |
Annelise Jones |
Applications Designer |
$48.00 |
|
118 |
James J. Frommer |
General Support |
$18.36 |
|
104 |
Anne K. Ramoras * |
Systems Analyst |
$96.75 |
|
112 |
Darlene M. Smithson |
DSS Analyst |
$45.95 |
|
105 |
Alice K. Johnson |
Database Designer |
$105.00 |
|
104 |
Anne K. Ramoras |
Systems Analyst |
$96.75 |
|
113 |
Delbert K. Joenbrood * |
Applications Designer |
$48.00 |
|
111 |
Geoff B. Wabash |
Clerical Support |
$26.87 |
|
106 |
William Smithfield |
Programmer |
$35.75 |
|
107 |
Maria D. Alonzo |
Programmer |
$35.75 |
|
115 |
Travis B. Bawangi |
Systems Analyst |
$96.75 |
|
101 |
John G. News * |
Database Designer |
$105.00 |
|
114 |
Annelise Jones |
Applications Designer |
$48.00 |
|
108 |
Ralph B. Washington |
Systems Analyst |
$96.75 |
|
118 |
James J. Frommer |
General Support |
$18.36 |
|
112 |
Darlene M. Smithson |
DSS Analyst |
$45.95 |
|
PROJECT DETAILS |
|||||
|
PROJECT NUMBER (PK) |
PROJECT NAME |
EMPLOYEE NUMBER |
CHARGE PER HOUR |
HOURS BILLED |
TOTAL CHARGE |
|
15
15 |
Evergreen |
103 |
$84.50 |
23.8 |
$2,011.10 |
|
15 |
Evergreen |
101 |
$105.00 |
19.4 |
$2,037.00 |
|
15 |
Evergreen |
105 |
$105.00 |
35.7 |
$3,748.50 |
|
15 |
Evergreen |
106 |
$35.75 |
12.6 |
$450.45 |
|
15 |
Evergreen |
102 |
$96.75 |
23.8 |
$2,302.65 |
|
18 |
Amber Wave |
114 |
$48.00 |
24.6 |
$1,183.26 |
|
18 |
Amber Wave |
118 |
$18.36 |
45.3 |
$831.71 |
|
18 |
Amber Wave |
104 |
$96.75 |
32.4 |
$3,134.70 |
|
18 |
Amber Wave |
112 |
$45.95 |
44 |
$2,021.80 |
|
22 |
Rolling Tide |
105 |
$105.00 |
64.7 |
$6,793.50 |
|
22 |
Rolling Tide |
104 |
$96.75 |
48.4 |
$4,672.80 |
|
22 |
Rolling Tide |
113 |
$48.00 |
23.6 |
$1,135.16 |
|
22 |
Rolling Tide |
111 |
$26.87 |
22 |
$591.14 |
|
22 |
Rolling Tide |
106 |
$35.75 |
12.8 |
$457.60 |
|
25 |
Starflight |
107 |
$35.75 |
24.6 |
$879.45 |
|
25 |
Starflight |
115 |
$96.75 |
45.8 |
$4,431.15 |
|
25 |
Starflight |
101 |
$105.00 |
56.3 |
$5,911.50 |
|
25 |
Starflight |
114 |
$48.00 |
33.1 |
$1,592.11 |
|
25 |
Starflight |
108 |
$96.75 |
23.6 |
$2,283.30 |
|
25 |
Starflight |
118 |
$18.36 |
30.5 |
$559.98 |
|
25 |
Starflight |
112 |
$45.95 |
41.4 |
$1,902.33 |
F. To 3NF: PROJECT and JOB CLASS are in 3NF because they have no transitive dependencies. EMPLOYEE and PROJECT DETAILS have columns that depend on other columns that are not designated as the PK. These two tables must be split out as follows:
|
PROJECT DETAILS |
|||
|
EmployeeNumber (FK) |
ProjectNumber (FK) |
HoursBilled |
TotalCharge |
|
103 |
15 |
23.8 |
$2,011.10 |
|
101 |
15 |
19.4 |
$2,037.00 |
|
105 |
15 |
35.7 |
$3,748.50 |
|
106 |
15 |
12.6 |
$450.45 |
|
102 |
15 |
23.8 |
$2,302.65 |
|
114 |
18 |
24.6 |
$1,183.26 |
|
118 |
18 |
45.3 |
$831.71 |
|
104 |
18 |
32.4 |
$3,134.70 |
|
112 |
18 |
44 |
$2,021.80 |
|
105 |
22 |
64.7 |
$6,793.50 |
|
104 |
22 |
48.4 |
$4,672.80 |
|
113 |
22 |
23.6
|
$1,135.16 |
|
111 |
22 |
22 |
$ 591.14 |
|
106 |
22 |
12.8 |
$ 457.60 |
|
107 |
25 |
24.6 |
$ 879.45 |
|
115 |
25 |
45.8 |
$4,431.15 |
|
101 |
25 |
56.3 |
$5,911.50 |
|
114 |
25 |
33.1 |
$1,592.11 |
|
108 |
25 |
23.6 |
$2,283.30 |
|
118 |
25 |
30.5 |
$ 559.98 |
|
112 |
25 |
41.4 |
$1,902.33 |
EMPLOYEE
EmployeeNumber (PK)
EmployeeName
JobID (FK)
101
John G. News
2
102
David H. Senior
4
103
June E. Arbough
1
104
Anne K. Ramoras
4
105
Alice K. Johnson
2
106
William Smithfield
3
107
Maria D. Alonzo
3
108
Ralph B. Washington
4
111
Geoff B. Wabash
8
112
Darlene M. Smithson
7
113
Delbert K. Joenbrood
5
114
Annelise Jones
5
115
Travis B. Bawangi
4
118
James J. Frommer
6
G. The PROJECT and JOB CLASS tables cannot go higher than 3NF because there is only a single PK and no candidate keys exist. The EMPLOYEE and PROJECT DETAILS tables have candidate keys that can identify the other columns in the relation so they are both in BCNF.
H. The EMPLOYEE and PROJECT DETAILS are in BCNF, PROJECT and JOB CLASS can only go to 3NF.
I. The Primary and Foreign Keys assigned per table are:
a. EMPLOYEE PK is EmployeeNumber, JobID is FK
b. PROJECT PK is ProjectNumber
c. JOB CLASS PK is JobID
d. PROJECT DETAILS PK is EmployeeNumber, ProjectNumber
Final Normalized tables:
EMPLOYEE
EmployeeNumber (PK)
EmployeeName
JobID (FK)
101
John G. News
2
102
David H. Senior
4
103
June E. Arbough
1
104
Anne K. Ramoras
4
105
Alice K. Johnson
2
106
William Smithfield
3
107
Maria D. Alonzo
3
108
Ralph B. Washington
4
111
Geoff B. Wabash
8
112
Darlene M. Smithson
7
113
Delbert K. Joenbrood
5
114
Annelise Jones
5
115
Travis B. Bawangi
4
118
James J. Frommer
6
JOB CLASS
JobID (PK)
Job
ChargePerHour
1
Elec. Engineer
$84.50
2
Database Designer
$105.00
3
Programmer
$35.75
4
Systems Analyst
$96.75
5
Applications Designer
$48.00
6
General Support
$18.36
7
DSS Analyst
$45.95
8
Clerical Support
$26.87
PROJECT
ProjectNumber (PK)
ProjectName
15
Evergreen
18
Amber Wave
22
Rolling Tide
25
Starflight
|
PROJECT DETAILS |
|||
|
EmployeeNumber (FK) |
ProjectNumber (FK) |
HoursBilled |
TotalCharge |
|
103 |
15 |
23.8 |
$2,011.10 |
|
101 |
15 |
19.4 |
$2,037.00 |
|
105 |
15 |
35.7 |
$3,748.50 |
|
106 |
15 |
12.6 |
$450.45 |
|
102 |
15 |
23.8 |
$2,302.65 |
|
114 |
18 |
24.6 |
$1,183.26 |
|
118 |
18 |
45.3 |
$831.71 |
|
104 |
18 |
32.4 |
$3,134.70 |
|
112 |
18 |
44 |
$2,021.80 |
|
105 |
22 |
64.7 |
$6,793.50 |
|
104 |
22 |
48.4 |
$4,672.80 |
|
113 |
22 |
23.6
|
$1,135.16 |
|
111 |
22 |
22 |
$ 591.14 |
|
106 |
22 |
12.8 |
$ 457.60 |
|
107 |
25 |
24.6 |
$ 879.45 |
|
115 |
25 |
45.8 |
$4,431.15 |
|
101 |
25 |
56.3 |
$5,911.50 |
|
114 |
25 |
33.1 |
$1,592.11 |
|
108 |
25 |
23.6 |
$2,283.30 |
|
118 |
25 |
30.5 |
$ 559.98 |
|
112 |
25 |
41.4 |
$1,902.33 |
SECTION II.
![]()
Database Integrity:
The design exhibits database integrity because there is a mandatory to mandatory minimum cardinality between JOB_CLASS and EMPLOYEE as well as between PROJECT and PROJECT_DETAILS. The minimum cardinality insists that one entity instance from EMPLOYEE must include an instance from JOB_CLASS and vice versa. In the PROJECT_DETAILS table an EmployeeNumber cannot be added unless it already exists in the EMPLOYEE table. Similarly, a new ProjectNumber can only be added to the PROJECT_DETAILS table if it exists in the PROJECT table.
