Database Reference
In-Depth Information
11.
Patient (PatientNum, HouseholdNum, PatientName)
Household (HouseholdNum, HouseholdName, Street, City, State, Zip, Balance)
Service (ServiceCode, Description, Fee)
Appointment (PatientNum, ServiceCode, Date)
13.
StudentNum
!
StudentName
ActivityNum
!
ActivityName
CourseNum
!
Description
StudentNum
!!
ActivityNum
StudentNum
!!
CourseNum
363
Student (StudentNum, StudentName)
Activity (ActivityNum, ActivityName)
Course (CourseNum, Description)
StudentActivity (StudentNum, ActivityNum)
StudentCourse (StudentNum, CourseNum)
CHAPTER 6
DATABASE DESIGN 2: DESIGN METHOD
—
1. A user view is the view of data that is necessary to support the operations of a particular user. By
considering individual user views instead of the complete design problem, the database design
process is greatly simplified.
3. If the design problem were extremely simple, the overall design might not have to be broken
down into a consideration of individual user views.
5. The primary key is the column or columns that uniquely identify a given row and that furnish the
main mechanism for directly accessing a row in the table. An alternate key is a column or combi-
nation of columns that could have functioned as the primary key but was not chosen to do so. A
secondary key is a column or combination of columns that is not any other type of key but is of
interest for purposes of retrieval. A foreign key is a column or combination of columns in one
table whose values are required to match the primary key in another table. Foreign keys furnish
the mechanism through which relationships are made explicit.
7.
Department (DepartmentNum, DepartmentName)
Advisor (AdvisorNum, LastName, FirstName, DepartmentNum)
FK DepartmentNum
!
Department
Course (CourseCode, Description)
Student (StudentNum, LastName, FirstName, AdvisorNum)
FK AdvisorNum
!
Advisor
StudentCourse (StudentNum, CourseCode, Grade)
FK StudentNum
!
Student
FK CourseCode
!
Course
9. a. No change is necessary.
b. Store both the AdvisorNum and DepartmentNum columns in the Student table.
11. The method presented in this text is bottom up; that is, an approach in which specific user
requirements are synthesized into a design. By initially reviewing the requirements and determin-
ing a possible list of entities prior to following the steps in this method, you can gain the advan-
tages to both top-down and bottom-up approaches.
13. Many answers are possible. Be sure the functional dependencies you represent are based on rea-
sonable assumptions and that the tables you create are in third normal form based on these
dependencies.
