ComputerScienceExpert
$18/per page/
PLEASE SEE THE ATTACHED FILES 1) ASSIGMENTS 2) APPLICATION EXAMPLE
Study the given conceptual ER model diagram in each problem and perform the following two tasks.
Map the conceptual model into relations (a logical model) following the mapping rules. You should show the mapping of each entity type and each relationship type and provide a brief explanation of the mapping. Each relation should include the relation name, attribute names, primary key (underlined), and foreign keys marked by FK subscript if any. After the completion of all mapping steps, list the final set of relations.
Perform normalization of each mapped relation. You should (a) identify all candidate keys, primary key and non-prime attributes, (b) show the functional dependencies between the attributes including full dependencies and transitive dependencies, (c) determine the normal form of the relation with a brief explanation, (d) normalize the relation into 3NF relations if it is not in 3NF.
It is possible in the task 2 (d) above that you decide not to normalize a mapped relation into 3NF relations. In this case, you are required to provide a clear explanation of and argument for your decision.
Note that you should provide sufficient explanations to your answers. Otherwise, the answers will be interpreted by the reviewers. When it happens, the interpretations are usually different from yours and may lead to a conclusion that your solutions contain errors.
The assignment report should include the given ER model of each problem, the model mapping and the normalization of all mapped relations.
CIS 8040 Fundamentals of Management Systems Assignment 2
Due date: 2/1/2017 at 11:50 PM
This assignment is an individual assignment, not a team assignment. You must complete the
assignment independently and submit your original work. “Plagiarism or duplicate lab
assignments will be given a grade of ‘zero’, a point deduction equivalent to one final grade level
(i.e. from a B- to a C-), and a charge of academic dishonesty. Both the person copying the
assignment and the person supplying the copy will be penalized equally.”
Study the given conceptual ER model diagram in each problem and perform the following two
tasks.
1) Map the conceptual model into relations (a logical model) following the mapping rules.
You should show the mapping of each entity type and each relationship type and
provide a brief explanation of the mapping. Each relation should include the relation
name, attribute names, primary key (underlined), and foreign keys marked by FK
subscript if any. After the completion of all mapping steps, list the final set of relations.
2) Perform normalization of each mapped relation. You should (a) identify all candidate
keys, primary key and non-prime attributes, (b) show the functional dependencies
between the attributes including full dependencies and transitive dependencies, (c)
determine the normal form of the relation with a brief explanation, (d) normalize the
relation into 3NF relations if it is not in 3NF.
It is possible in the task 2 (d) above that you decide not to normalize a mapped relation into
3NF relations. In this case, you are required to provide a clear explanation of and argument for
your decision.
Note that you should provide sufficient explanations to your answers. Otherwise, the answers
will be interpreted by the reviewers. When it happens, the interpretations are usually different
from yours and may lead to a conclusion that your solutions contain errors. 1 CIS 8040 Fundamentals of Management Systems Problem 1 (30%)
The following conceptual model represented a grade report user view in a school. It assumed
that each section is taught by only one instructor. During the scheduling of classes for a
semester, a course section has neither any student registered for it, nor any instructor assigned
to teach it. After the class schedule is published, the classes are assigned instructors and allowed
students to register for them.
Day
Phone GPA Grade Student (0, m) Zip
code Address Section Offer (1, 1) Office Teach City Course
number Title Course (0, m) (0, 1) Street
State # of
Credits Section
ID Room Take (0, m) Term Time Instructor (0, m) Person Name Email ID Problem 2 (35%)
The following conceptual model showed a solution to Problem 2 in Assignment 1. Dept
Name Phone Department Name Emp
Num
(0,m) Report DoB Title Employee (1, 1) Date of
Marriage
(1,1) Marry
(0,1) (1,m) Possess (0,m) (1,m) (0,m) (1, 1) Data of
Meeting Emp Skill supply
Skill Num city
Use (1,1) (1,m) Name (1, 1) Location Population Desc
(0,m) Vendor
ID Assign State (0,m) (1,m) Proj Num Vendor Address Cost Project (1,m) Locate 2 CIS 8040 Fundamentals of Management Systems Problem 3 (35%)
The following ER diagram showed a solution to Problem 3 in Assignment 1.
State City Zip
code Street
Address
Name
EmpID Degree
Received Consultant Tech Skill
City DoB Tech
Consultant Age Address Coverage Cost Clearance Service Bus. Type
# of Years Business
Consultant Perform (0,m) Date (0,m) Num of
Employees
Business
Type (1, 1) (1,m) Contact
Address Zip
code Street
City Location Company
Name Customer (0,m) Building
Size Amount CustID Date
Amount (1,m) (0,m) Estimate (0,m) Phone Location
ID Desc
Service Zip code Street (0,m) Phone State Locate Phone
Title Name State Assignment Deliverables and Assessment
The assignment report should include the given ER model of each problem, the model mapping
and the normalization of all mapped relations.
The assignment answers are assessed based on the following rubric.
Task
Logical design
(50%) Criteria
Relation mapping
Mapping explanation
Relations Normalization
(50%) Analysis of each
relation Explanation
Correctly mapped the model
Sufficient explanation of each step
Completeness of each relation &
the final list of relations
Correctly identified all candidate
keys, primary keys and non-prime
attributes Weight
70%
20%
10% 20% 3 CIS 8040 Fundamentals of Management Systems Functional
dependencies
Determination of
normal forms Normalization
explanation Correctly identified all full
dependencies and transitive
dependencies
Correctly determined the normal
forms of mapped relations,
normalize non-3NF relations to
3NF relations if any
Sufficient explanation of the
normalization process 40% 20% 20% The assignment report should be submitted into the corresponding assignment drop box in
iCollege. Email submissions are not accepted. Labe submission is accepted until 5:00 PM before
the next class with 10% grade reduction penalty. 4
University Application Example
Logical Design
Instructor’s Solution TABLE OF CONTENTS
Logical Design................................................................................................................................................ 2
ER Model Mapping .................................................................................................................................... 2
Approach 1: Removing supertype PERSON .......................................................................................... 2
Approach 2: Keep supertype PERSON .................................................................................................. 5
Relation Normalization ............................................................................................................................. 6
Replacement of Composite Keys ............................................................................................................ 12
Final Logical Model ................................................................................................................................. 14 1 Logical Design
The logical design follows the conceptual design of the University Application example. Please reference
to the document, University Application Design Example: Conceptual Design Instructor’s Solution for
detailed discussions of the conceptual design.
The logical design consists of three design steps: ER model mapping, relation normalization, and
replacement of composite keys. ER Model Mapping
There are two mapping approaches. One approach does not map the supertype PERSON, and another
approach does. We show the detailed mapping steps in the first approach, and then discuss the second
approach and comment on the main difference. We adopted the Java variable name convention for
attribute names and the Java class name convention for table names. Approach 1: Removing supertype PERSON
All attributes of PERSON are inherited by its subtypes, Advisor, Student, and Instructor as shown below.
Note that we renamed the ID fields for clarity. Each of those entity types has a multivalued attribute
Phone. LName Email MName
FName
EmpID Advisor Location
Phone
Area LName Email MName
FName
StuID Student Location
Phone
Credit
hours LName Email MName
FName Instructor EmpID Location
Phone
Rank They are mapped into the following entity tables as shown below:
Advisor (advID, firstName, midName, lastName, email, location, area) AdvisorPhone (advID(fk), phoneNum)
Student (stuID, firstName, midName, lastName, email, location, creditHours)
StudentPhone (stuID(fk), phoneNum )
Instructor (instrID, firstName, midName, lastName, email, rank)
InstructorPhone (instrID(fk), phoneNum )
The removal of the Person supertype introduced an anomaly. It resulted in three separated tables for
the multivalued attribute Phone, AdvisorPhone, StudentPhone, and InstructorPhone. Those tables
provide helpful information about who have those phone numbers. They can be directly used to answer
queries like: “list all advisor phone numbers.” But, the cost is extra two tables, i.e., a fragmented
database. As a database designer, you can weight on what is more important, the easy to query or
reduced fragmentation. If a fragmented database is a concern, those three tables could be merged into
one table, PersonPhone, as shown below:
2 PersonPhone (personID(fk), phoneNum)
Alternatively, you could choose to merge only the advisor and instructor as both are employees like
EmployeePhone (empID(fk), phoneNum)
Thus, there are multiple ways to map the phone attribute. As the database designer, you can decide
which approach is preferred. In this example, we choose to keep separate phone tables for clarify.
The remaining entity types are shown below and are mapped into entity type tables. The multivalued
attributes in those entity types are also mapped into a separate table.
Name Location Name Department DeptID Location College Phone CollegeID # of
Credits Title Course
Course
number Phone Desc Major degree Program ProgmID Req. Credit
hours Department (deptID, name, location)
DepartmentPhone (deptID(fk), phoneNum)
College (collegeID, name, location)
CollegePhone (collegeID(fk), phoneNum)
Course (courseNumber, title, numberOfCredits)
Program (programID, description, requiredCreditHours, major, degree)
The next step is to map relationships. We begin with all one-to-many relationships.
The following is the one-to-many relationship between Department and Instructor. The Instructor table
is selected to implement the relationship by inserting the primary key of Department into Instructor as a
foreign key.
DeptID Department (0, m) Work (1, 1) Instructor EmpID Instructor (instrID, firstName, midName, lastName, email, location, rank, deptID(fk))
The following is the one-to-many relationship between College and Advisor. The Advisor is selected to
implement the relationship based on the mapping rules.
CollegeID College (0, m) Has (1, 1) Advisor AdvID Advisor (advID, firstName, midName, lastName, email, location, area, collegeID(fk))
The following one-to-many relationship is between College and Department. The Department is selected
to implement the relationship based on the mapping rules. 3 CollegeID College Belong (0, m) Department (1, 1) DeptID Department (deptID, name, location, collegeID(fk))
The following the identifying relationship is between the weak entity type Program and the “owner”
entity type Department. Program is selected to implement the relationship according the mapping rules.
DeptID Department Offer (0, m) (1, 1) Program ProgmID Program (programID, Description, requiredCreditHours, major, degree, deptID(fk))
Next, we map many-to-many relationships. The following many-to-many relationship is between Advisor
and Student. It is mapped to a separate relationship table based on the mapping rules.
Advisor EmpID advise (0, m) Date StuID Student (1, m) Notes
Time AdviceMeeting (advID(fk), stuID(fk), date, time, notes)
The primary key is a composite key consists of advID, stuID, date, and time. It is because that a student
may meet the same advisor multiple times, even in a same day. Thus, we need all of them to uniquely
identify each advising meeting.
The following many-to-many relationship is between Program and Course. The relationship is mapped
into a separate relationship table according to the mapping rules. The foreign keys form the composite
key of the relationship table.
ProgmID Program (1, m) require (1, m) Course Course
number ProgramCourse (programID(fk), courseNumber(fk))
The following many-to-many relationship is between Program and Student. The relationship is mapped
into a separate relationship table according to the mapping rules. The foreign keys form the composite
key of the relationship table.
ProgmID StuID Program (0, m) Select (1, m) Student StudentProgram (stuID(fk), programID (fk))
The last relationship is the ternary relationship between Student, Instructor and Course. As all maximum
cardinalities are ‘m’ (many), the relationship is mapped into a separate relationship table Class. The
composite key consists of term, stuID, and courseNumber. 4 Time Grade
StuID Student Take (0, m) Instructor (0, m) Term InstrID Day
(0, m) Course
number Course Class (term, day, time, grade, stuID(fk), courseNumber(fk), instrID (fk))
At the end of above steps, the following tables are mapped from the ER model:
Student (stuID, firstName, midName, lastName, email, location, creditHours)
Instructor (instrID, firstName, midName, lastName, email, location, rank, deptID(fk))
Advisor (advrID, firstName, midName, lastName, email, location, area, collegeID(fk))
Course (courseNumber, Title, numberOfCredits)
Program (programID, description, requiredCreditHours, major, degree, deptID(fk))
Department (deptID, name, location, collegeID(fk))
College (collegeID, name, location)
StudentPhone (stuID(fk), phoneNum )
AdvisorPhone (advID(fk), phoneNum)
InstructorPhone (instrID(fk), phoneNum )
DepartmentPhone (deptID(fk), phoneNum)
CollegePhone (collegeID(fk), phoneNum)
AdviceMeeting (advrID(fk), stuID(fk), date, time, notes)
ProgramCourse (programID(fk), course Number(fk))
StudentProgram (stuID(fk), programID (fk))
Class (term, day, time, grade, stuID(fk), courseNumber(fk), InstrID (fk)) Approach 2: Keep supertype PERSON
The second mapping approach maps the supertype entity type into an entity table.
Phone PersonID MName Location
Email FName Person LName Person (PersonID, firstName, midName, lastName, email, location)
PersonPhone (programID(fk), phoneNumber)
The subtype entity types inherit only the ID from the supertype Person as shown in the diagram below. 5 Area PersonID Advisor Credit
Hours Earned PersonID Student PersonID Rank Instructor Those entity types are mapped into three entity tables:
Advisor (PersonID, area)
Student (PersonID, creditHours)
Instructor (PersonID, rank)
Compared with the first approach, the second approach increases the query complexity and decreases
the query performance. It is because all attributes of a student are split into two tables, Person and
Student. (As the phone table appears in both approaches, it is excluded from the discussion.) To answer
queries that list all information about students, we have to join the Person table with the Student table.
From our discussions about the join operator, joining two tables is much slower than no join at all. Thus,
such database queries in the second approach will be more complex and slower than that in the first
approach. Same is true for other two entity types, Instructor and Advisor. Thus, the second mapping
approach is not referred because of the potential query complexity and poor query performance.
The rest of mappings in this approach is identical to that of the first approach. Thus, the discussions of
the mappings are omitted. Relation Normalization
The functional dependency analysis and the relation normalization begin with the list of tables mapped
from the ER model using the first mapping approach. The normalization process is presented for each
table. The discussions in this section frequently reference user requirements analysis. To indicate which
part of the user requirements is referenced, we use the following abbreviations:
UR-E<n> Attr – user requirements analysis, entity type n attributes
UR-R<n> Attr – user requirements analysis, relationship n attributes
UR-R<n> BR<k> – user requirements analysis, relationship n business rule k Student (stuID, firstName, midName, lastName, email, location, creditHours)
It has two candidate keys, stuID and email (UR-E1 Attr). StuID was selected as the primary key of the
table. The non-prime attributes are: firstName, midName, lastName, location, and creditHours.
We identified the following functional dependencies based on UR-E1 Attr:
stuID email
email stuID
stuID firstName, midName, lastName, location, creditHours
Note that there are other functional dependencies as shown below:
email firstName, midName, lastName, location, creditHours
6 But, those dependencies are redundant. It is because that knowing
email stuID
stuID firstName, midName, lastName, location, creditHours
we can logically deduce, by transitivity
email firstName, midName, lastName, location, creditHours
That is, the above functional dependencies were already known and do not provide any new, unknown
dependency. Thus, we can safely ignore those redundant dependencies. In the remaining discussions,
we will ignore those redundant functional dependencies.
But, there dependencies are transitive dependencies as shown below:
email stuID firstName, midName, lastName, location, creditHours
stuID email firstName, midName, lastName, location, creditHours
Based on the normalization theory and FDs listed above, we conclude that: Student is in 2NF because all non-prime attributes are fully depended on the key. Student is in 3NF because there is no any non-prime attribute that is transitively depended on
the key through a non-prime attribute. Note that there are two sets of transitive dependencies as shown above. But, both “intermediate”
determinants are key attributes, i.e., prime attributes. The 3NF definition is applied to only non-prime,
intermediate attributes. Hence, those transitive dependencies do not influence the determination that
the table is in 3NF by definition. We will assume the similar discussions about such transitive
dependencies on prime attributes in the remainder of the section to avoid repetitions. StudentPhone (stuID(fk), phoneNum)
It has only one candidate key, (stuID, phoneNum), and is the primary key by default. There is no any
non-prime attribute because all attributes are part of the key, i.e., prime attributes.
There is no functional dependency between stuID and phoneNum because a student may have multiple
phone numbers, and a phone number may be shared by multiple students (UR-E1 Attr). That is, the
dependencies between them are multivalued dependencies as shown below:
stuID ->> phoneNum
phoneNum ->> studID
Thus, we conclude that the table is in both 2NF and 3NF because there is no non-prime attribute in the
relation by NF definitions. Advisor (advrID, firstName, midName, lastName, email, location, area, collegeID(fk)) 7 Advisor has two candidate keys, advID and email (UR-E2 Attr). AdvID is selected as the primary key. The
non-prime attributes are: firstName, midName, lastName, location, area, and collegeID.
We identified the following functional dependencies based on UR-E2 Attr, UR-R10 BR1, and UR-R10 BR2:
advID email, firstName, midName, lastName, location, area, collegeID
email advID
Based on the FDs listed above, we conclude that: Advisor is in 2NF because all non-prime attributes are fully depended on the key. Advisor is also in 3NF because there is no any non-prime attribute that is transitively depended
on the key through a non-prime attribute. AdvisorPhone (advID(fk), phoneNum)
It has only one candidate key, (advID, phoneNum), and is the primary key by default. There is no any
non-prime attribute because all attributes are part of the key, i.e., prime attributes.
There is no functional dependency between advID and phoneNum because phoneNum is a multivalued
attribute (UR-E2 Attr). That is, the dependencies between them are multivalued dependencies as shown
below:
advID ->> phoneNum
phoneNum ->> advID
Thus, we conclude that table is in both 2NF and 3NF because there is no non-prime attribute in the
relation by NF definitions. Instructor (instrID, firstName, midName, lastName, email, location, rank, deptID(fk))
It has two candidate keys, instrID and email (UR-E3 Attr). InstrID is selected as the primary key. The nonprime attributes are: firstName, midName, lastName, location, rank, and deptID.
We identified the functional dependencies based on UR-E3 Attr, UR-R12 BR1, and UR-R12 BR2:
instrID email, firstName, midName, lastName, location, rank, deptID
email instrID
Based on the FDs listed above, we conclude that: Instructor is in 2NF because all non-prime attributes are fully depended on the key. Instructor is in 3NF because there is any no non-prime attribute that is transitively depended on
the key through a non-prime attribute. InstructorPhone (instrID(fk), phoneNum) 8 It has only one candidate key, (instrID, phoneNum), and is the primary key by default. There is no any
non-prime attribute because all attributes are part of the key, i.e., prime attributes.
There is no functional dependency between instrID and phoneNum because phoneNum is a multivalued
attribute (UR-E3 Attr). That is, the dependencies between them are multivalued dependencies as shown
below:
instrID ->> phoneNum
phoneNum ->> instrID
Thus, we conclude that table is in both 2NF and 3NF because there is no non-prime attribute in the
relation by NF definitions. Course (courseNum, title, numOfCredits)
It has only one candidate key courseNum and is selected as the primary key by default. The non-prime
attributes are: title and numOfCredits.
We identified the functional dependencies based on UR-E4 Attr:
courseNum title, numOfCredits
Based on the above FDs, we conclude that: Course is in 2NF because all non-prime attributes are fully depended on the key. Course is in 3NF because there is no any non-prime attribute that is transitively depended on
the key through a non-prime attribute. Program (progmID, description, requiredCreditHours, major, degree, deptID(fk))
It has only one candidate key and is selected as the primary key by default. The non-prime attributes
are: description, requiredCreditHours, major, degree, deptID.
We identified the following FDs based on UR-E4 Attr, UR-R13 BR1, and UR-R13 BR2:
programID description, requiredHours, major, degree, deptID
Based on the above FDs, we conclude that: Program is in 2NF because all non-prime attributes are fully depended on the key. Program is in 3NF because there is no any non-prime attribute that is transitively depended on
the key through a non-prime attribute. Department (deptID, name, location, collegeID(fk))
It has only one candidate keys, deptID and is selected as the primary key by default. The non-prime
attributes are, name, location and collegeID.
9 We identified the functional dependencies based on UR-E6 Attr, UR-R11 BR1, and UR-R11 BR2:
deptID name, location, collegeID
Based on the above FDs, we conclude that: Department is in 2NF because all non-prime attributes are fully depended on the key. Department is in 3NF because there is no any non-prime attribute that is transitively depended
on the key through a non-prime attribute. DepartmentPhone (deptID(fk), phoneNum)
It has only one candidate key, (deptID, phoneNum), and is the primary key by default. There is no any
non-prime attribute because all attributes are part of the key, i.e., prime attributes.
There is no functional dependency between deptID and phoneNum because phoneNum is a multivalued
attribute (UR-E6 Attr). That is, the dependencies between them are multivalued dependencies as shown
below:
deptID ->> phoneNum
phoneNum ->> deptID
Thus, we conclude that DepartmentPhone is in both 2NF and 3NF because there is no non-prime
attribute in the relation by NF definitions. College (collegeID, name, location)
It has only one candidate key, collegeID and is selected as the primary key by default. The non-prime
attributes are, name and location.
We identified the functional dependencies based on UR-E7 Attr:
collegeID name, location
Based on the above FDs, we concluded that: College is in 2NF because all non-prime attributes are fully depended on the key. College is in 3NF because there is no any non-prime attribute that is transitively depended on
the key through a non-prime attribute. CollegePhone (collegeID(fk), phoneNum)
It has only one candidate key, (collegeID, phoneNum), and is the primary key by default. There is no any
non-prime attribute because all attributes are part of the key, i.e., prime attributes.
There is no functional dependency between collegeID and phoneNum because phoneNum is a
multivalued attribute (UR-E6 Attr). That is, the dependencies between them are multivalued
dependencies as shown below:
10 collegeID ->> phoneNum
phoneNum ->> collegeID
Thus, we conclude that CollegePhone is in both 2NF and 3NF because there is no non-prime attribute in
the relation by NF definitions. AdviceMeeting (advID(fk), stuID(fk), date, time, notes)
It has only one composite candidate key, (advID, studID, date, time) and is selected as the primary key
by default. The non-prime attributes are: date, time, and notes
We identified the functional dependencies based on UR-R8 Attr:
(advID, stuID, date, time) notes
We identified the following multivalued dependencies based on UR-R8 BR1 and UR-R8 BR2:
advID ->> stuID
stuID ->> advID
Based on the FDs analysis, we conclude that AdviceMeeting is in 2NF because all non-prime attributes are fully depended on the key. AdviceMeeting is in 3NF because there is no any non-prime attribute that is transitively
depended on the key through a non-prime attribute. StudentProgram (stuID (fk), programID (fk))
It has only one composite candidate key, (stuID, programID) and is selected as the primary key by
default. There is no any non-prime attribute.
We identified the following multivalued dependencies based on UR-R9 BR1 and UR-R9 BR2:
stuID ->> programID
programID ->> stuID
Based on the FDs analysis, we conclude that StudentProgram is in both 2NF and 3NF because there is no
non-prime attribute in the relation by NF definitions ProgramCourse (programID (fk), courseNumber (fk))
It has only one composite candidate key, (programID, courseNumber) and is selected as the primary key
by default. There is no any non-prime attribute.
We identified the following multivalued dependencies based on UR-R14 BR1 and UR-R14 BR2:
programID ->> courseNumber
courseNumber ->> programID
11 Based on the FDs analysis, we conclude that ProgramCourse is in both 2NF and 3NF because there is no
non-prime attribute in the relation by NF definitions Class (term, day, time, grade, stuID(fk), courseNumber(fk), instrID(fk))
It has two candidate keys, (stuID, term, courseNumber) and (instrID, term, courseNumber) based on URR13 BR1, UR-R13 BR2, and UR-R13 BR3. The non-prime attributes are: day, time, and grade.
We identified the functional dependencies based on UR-R13 BR1, UR-R13 BR2, and UR-R13 BR3:
(stuID, term, courseNumber) day, time, grade
(stuID, term, courseNumber) (instrID, term, courseNumber)
Based on the FDs analysis, we conclude that ProgramCourse is in both 2NF and 3NF because there is no
non-prime attribute in the relation by NF definitions.
In conclusion, the relation normalization process discussed above verified that all those tables mapped
from the ER model are already in both 2NF and 3NF. It did not introduce any new table. Thus, the final
set of tables are the same as that listed in the previous section. Replacement of Composite Keys
In the final review of the logical model, the following tables have composite keys:
StudentPhone (stuID(fk), phoneNum )
AdvisorPhone (advID(fk), phoneNum)
InstructorPhone (instrID(fk), phoneNum )
DepartmentPhone (deptID(fk), phoneNum)
CollegePhone (collegeID(fk), phoneNum)
AdviceMeeting (advrID(fk), stuID(fk), date, time, notes)
ProgramCourse (programID(fk), course Number(fk))
StudentProgram (stuID(fk), programID (fk))
Class (term, day, time, grade, stuID(fk), courseNumber(fk), InstrID (fk))
They are resulted from implementing the multivalued attributes, many-to-many relationships and the
ternary relationship as shown in the ER mapping.
In practice, composite keys are not preferred. It is because that key attributes are treated differently in
the database and cost more storage space and process time than that for non-key attributes. For the
database performance consideration, singular and numeric keys, called surrogate keys, are preferred. A
surrogate key is an artificial, single valued and numeric key. It has no any meaning in the application. It is
solely introduced as unique identifier for a table. A familiar example is the GSU’s CRNs for classes.
Following the best practice, we replace all those composite keys by surrogate keys in those tables:
StudentPhone (stuPhoneID, stuID(fk), phoneNum )
AdvisorPhone (advPHoneID, advID(fk), phoneNum)
InstructorPhone (instrPhoneID, instrID(fk), phoneNum )
12 DepartmentPhone (deptPhoneID, deptID(fk), phoneNum)
CollegePhone (colPhoneID, collegeID(fk), phoneNum)
AdviceMeeting (adviceID, advrID(fk), stuID(fk), date, time, notes)
ProgramCourse (progCourseID, programID(fk), course Number(fk))
StudentProgram (stuProgID, stuID(fk), programID (fk))
Class (classID, term, day, time, grade, stuID(fk), courseNumber(fk), InstrID (fk))
The replacement of those composite keys with surrogate keys does not change the normal forms of
those tables. It is because those surrogate keys do not change the functional dependencies. Those
composite keys are still candidate keys. The only change is that they are no longer selected as the
primary keys because of the surrogate keys. That is, the introduction of surrogate keys in those tables
can be safely done without causing any normalization problem. With the replacement of composite
keys, the logical design can now be official completed.
As a bonus, however, surrogate keys may open new opportunity to improve the design of some tables.
The Class table is a good example. If we create a surrogate key for each class like the GSU’s CRN field.
The Class table can be broken down into two tables:
Class (classID, term, day, time, courseNumber(fk), InstrID (fk))
StudentGrade (classID, stuID(fk), grade)
The new Class table reduced a lot of data redundancy. In the old Class table, the attributes, term, day,
time, courseNumber, and instrID, are repeated with each student registered for the class. As the new
Class table no longer include the stuID attribute, all those redundant data are eliminated from...