Database Reference
In-Depth Information
NOTE
When using a software tool to produce E-R diagrams, the software might reverse the order of the fields that make up the pri-
mary key. For example, the E-R diagram in Figure A-8 indicates that the primary key for the Course table is CourseNum and
then DepartmentCode, even though you intended it to be DepartmentCode and then CourseNum. This difference is not a prob-
lem. Indicating the fields that make up the primary key is significant, not the order in which they appear.
323
sID
number, name, address, office location, phone number, current rank (Instructor, Assistant Professor, Associ-
ate Professor, or Full Professor), and starting date of employment. In addition, list the number, name, and
local and permanent addresses of each faculty member
User View 2
Faculty information report: List all faculty by department and each faculty member
'
—
s advisees; the code number and description of the
major in which the faculty member is advising each advisee; and the code number and description of the
department to which this major is assigned. This user view involves three entities (departments, faculty, and
advisees), so you can create the following three tables:
'
Department (
Faculty (
Advisee (
The next step is to assign a primary key to each table. Before doing so, however, you should briefly
examine the tables in the cumulative design and use the same names for any existing tables or attributes. In
this case, you would use DepartmentCode as the primary key for the Department table and FacultyNum as
the primary key for the Faculty table. There is no Advisee table in the cumulative collection, but there is a
Student table. Because advisees and students are the same, rename the Advisee entity to Student and use the
StudentNum attribute as the primary key rather than AdvisorNum. Your efforts yield the following tables and
primary keys:
Department (DepartmentCode,
Faculty (FacultyNum,
Student (StudentNum,
Next, add the remaining attributes to the tables:
Department (DepartmentCode, DepartmentName)
Faculty (FacultyNum, LastName, FirstName, Street, City, State,
Zip, OfficeNum, Phone, CurrentRank, StartDate, DepartmentCode)
Student (StudentNum, LastName, FirstName, LocalStreet,
LocalCity, LocalState, LocalZip, PermStreet, PermCity,
PermState, PermZip, (MajorNum, Description,
DepartmentCode, FacultyNum, LastName, FirstName) )
The DepartmentCode attribute is included in the Faculty table because there is a one-to-many relation-
ship between departments and faculty members. Because a student can have more than one major, the infor-
mation about majors (number, description, department, and the number and name of the faculty member
who advises this student in this major) is a repeating group.
