CIS431-DL  Dr. Julian M. Scher        Home Mini Exam #2        (Extended) Entity Relationship Data Modeling

DESIGNING AN (EXTENDED) ENTITY-RELATIONSHIP DATA MODEL FOR TIJN UNIVERSITY

DUE: Wednesday, February 4, 4:00PM

READ/VIEW: Chapter 2, pp. 31-46 & PPT Slides & VT2

     You have been hired as a Database Consultant by TIJN University, to design an (Extended) Entity Relationship Data model for a database they wish to establish for the University. The President and Provost of TIJN have provided you with the following details regarding TIJN:

    "TIJN is composed of several COLLEGEs: a College of Engineering, a College of Science and Liberal Arts, a College of Management, a College of Architecture, and a College of Computing Sciences. Also, officially Architecture and Management are called 'Schools' but for the purpose of this data model are to be known as 'Colleges.' Each of our colleges is administered by its one (and only)  DEAN.

    A COLLEGE is composed of one or more academic DEPARTMENTs. For example, the College of Science/Liberal Arts is composed of the Departments of Humanities, Mathematics, Physics, etc., and the College of Computing Sciences is composed of two Departments: Computer Science and Information Systems. The College of Architecture, on the other hand, has only one department, named the Architecture Department. A particular DEPARTMENT can only be a member of one COLLEGE.

    Each DEPARTMENT will offer a (non-empty!) set of COURSEs. For instance, the Department of Mathematical Sciences will offer courses in Differential Equations, Vector Analysis, Statistics, etc. But please note that if the Department of Mathematical Sciences offers a course named "Statistics," then no other academic Department at TIJN may also offer a course named 'Statistics.'

    A COURSE will have several SECTIONs (at least one SECTION, but our more popular and required COURSES will have many SECTIONs, sometimes 10 or 20 SECTIONs). Each SECTION of a COURSE will be instructed by a single PROFessor (i.e., we do not allow 'team teaching' of a given SECTION). The identifier of a SECTION is the concatenation of the COURSE number and SECTION number, so, for example, we have CIS431101, where CIS431 is the CourseNumber and 101 is the SectionNumber.

    Each academic DEPARTMENT employs a set of PROFessors. All of our academic DEPARTMENTs will have at least one PROFessor, and there will never be in our database a Department without any professors, even though we might be planning to create a Department in some future semester.  Our PROFessors are generally associated with a single academic DEPARTMENT, but we do have a few situations where we allow PROFessors to have joint appointments in two academic DEPARTMENTs, such as one of our PROFessors who has a joint appointment in the Mathematics Department and the Physics Department. {We do not allow for  any 'university' professors, i.e., each PROFessor must have some 'home' academic DEPARTMENT.}

    Each of our academic DEPARTMENTs is chaired by a single Department Chair, who, by necessity, must be a PROFessor, and who receives extra compensation. (Due to the heavy workload involved, we will not allow a given PROFessor to chair more than one DEPARTMENT.)

    Under our AAUP (this is the American Association of University Professors, a professional society for PROFessors) contract, a PROFessor will instruct at most 4 SECTIONs of various COURSEs. Most of our PROFessors are at or near this maximum, though in a few cases we have some  PROFessors on research contracts who spend all  of their time on research, and do not teach any sections of any courses..

    What we are most proud of at TIJN are our STUDENTs. Our students may enroll for up to 7 SECTIONs of COURSEs (most enroll for less than this maximum), but, naturally, we do not allow a STUDENT to enroll in more than one SECTION of a given COURSE. While most of our STUDENTs do enroll in several SECTIONs each semester, under unusual circumstances we do allow a student to 'maintain matriculation' and not enroll for any classes, and in this case, the non-matriculated STUDENT will still reside in our database. While we generally try to keep our SECTION sizes limited to, say, 25 or 30 STUDENTs, we do have on campus several large and recently built lecture halls which can accommodate a section as large as 180 STUDENTs.

    At TIJN, we forcefully require each and every  STUDENT to declare a major in an academic DEPARTMENT. Students are not allowed to have double majors, i.e., a student must major in one and only one Department.

    Lastly, TIJN believes very strongly in proper student advisement. Every STUDENT must have a PROFessor from their DEPARTMENT to serve as their advisor. Please note that ALL PROFessors (even those on research contracts who do not teach) are required to serve as advisors  to students. PROFessors are thus required to advise students, and many PROFessors will generally advise several STUDENTs."

    Given the details above from the TIJN administration, your assignment is to construct an extended Entity Relationship Data Model of TIJN. We have capitalized the ENTITY SETS you will need. Below we have provided you with the structure for your extended ER diagram (in the real world, you will not be so lucky!) Your assignment is to complete this diagram: Include relationships and all cardinalities, and use precisely the notation given in the Powerpoint slides and pp. 31- 46 of the text. For each entity class in your extended E-R model, you are to create/design (exactly) three meaningful attributes, including the primary key which you must identify. Do NOT use ellipses to represent attributes as in Figure 2-10 on  page 40 in the text; rather, enumerate separately from this diagram the entity sets, and for each entity set, specify the three (or more) attributes, being careful to underline the primary key

    You can print the ER Diagram below, and fill it in with the requested information, and submit to Dr. Scher in either 'drop off' or 'snail-mail' modes.. {You may. if you wish, (re)construct the diagram below using Microsoft Powerpoint or some other application such as Visio, if you happen to have this. However, we will not accept any hand-written ER diagrams (i.e., we do want to see a professional looking appearance of most of your extended ER diagram). If you do have MS Powerpoint and wish to use it to construct the ER model below, click on the fish      for some insights on how to do this (the insights are with reference to Powerpoint 2000, but you can readily adapt these to more recent (or older) versions of Powerpoint.