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.