Application
Exercise: E-R Model
One of the major functions of any academic department is student
advising. In order to assist a department in better serving its majors, several
reports are generated.
A college listing provides a list of
all majors for each department in each college. Entries are grouped under a
heading that includes the name of the school or college as well as its dean.
Each group consists of a department entry that lists the department name, chair
and number of majors.
example: College
Listing
College:
Business Dean: William Stratton
|
|
Department |
Chair |
Majors |
|
|
Computer
Information Systems |
Aytes,
K. |
112 |
|
|
Accounting |
Picard,
B. |
135 |
A faculty advisor listing includes the faculty
advisor name, office, and phone number for each college and department.
example: Faculty
Advisor Listing
College: Business
Department:
Computer Information Systems
|
|
Faculty
Advisor |
Office |
Phone |
|
|
Joe
Chao |
BA
524 |
282-3413 |
|
|
Kevin
Parker |
BA
528 |
282-4783 |
Department:
Accounting
|
|
Faculty
Advisor |
Office |
Phone |
|
|
Mark
Bezik |
BA
334 |
282-4769 |
A department and major report lists each major by
student name, student number, address, phone, major, and faculty advisor for
each college and department.
example:
Department and Major Report
College: Business
Department: Computer Information Systems
|
|
Student |
ID# |
Address |
Phone |
Major |
Advisor |
|
|
Mark
Wright |
21548 |
Box
112 |
234-1234 |
CIS |
Chao |
|
|
Bob
White |
33214 |
Box
223 |
232-3342 |
CIS |
Parker |
Overall each school is directed by a dean, and
encompasses several departments. Each department includes several faculty
members who act as student advisors. One of those faculty members also serves
as the department chair. Students are associated with a department by means of
their major, and with a faculty member by means of the advising process.
Begin the database design process by
listing the entities and the relationships, including the connectivity (1:1,
1:M, or M:N) for each relationship. Then draw the E-R diagram, listing the
attributes separately in the format
ENTITY_NAME (attribute1, attribute2, ... ,
attribute n)
Be sure to underline each primary key, and explain any assumptions or constraints that you find necessary.