Question

You have been approached by a University for the design and implementation of a relational database system that will provide information on the courses it offers, the academic departments that run the courses, the academic staff and the enrolled students. The system will be used mainly by the students and the academic staff.

The requirement collection and analysis phase of the database design process provided the following data requirements for the University Database system.

Using the following requirements answer this question:

Question:

1) Create an Entity–Relationship (ER) model of the data requirements for the University Database case study using the UML notation.

Note: if necessary, use the additional concepts of the Enhanced Entity–Relationship (EER) model. State any assumptions necessary to support your design.

Requirements:

Each department runs a number of courses. The university provides a set of modules used in different courses. Each course uses a number of modules but not every module is used. A course is assigned a unique course code and a module is identified by a unique module code. A module can be used in one course only, but can be studied by many students. In addition to the module code each module unique title, start date, end date, texts (books), and assessment scheme (i.e. coursework and exam marks percentages) are also stored.

Each course is managed by a member of academic staff, and each module is coordinated by a member of academic staff also. The database should also store each course unique title, and duration (in years).

A student can enroll in one course at a time. Once enrolled a student is assigned a unique matriculation number. To complete a course, each student must undertake and pass all the required modules in his/her course. This requires that the database store the performance (pass or fail) of each student in every module.

Additional data stored on each student includes student name (first and last), address (town, street, and post code), date-of-birth, sex, and financial loan. For emergency purposes the database stores the name (not composite), address (not composite), phone, and relationship of each student next-of-kin. None of the next-of-kin's attributes is unique. Assume that every next-of-kin is a next-of-kin of one student only.

Each department is managed by a member of academic staff. The database should record the date he/she started managing the department. Each department has a name, phone number, fax number, and location (e.g. E Block). Each department employs many members of academic staff.

A member of academic staff can be the leader (i.e. manager) of at most one course, but can be the coordinator of more than one module. A member of academic staff may not be assigned any of the above mentioned roles (coordinator, course leader, department manager). All members of academic staff teach modules. Every member of academic staff teaches one or more modules, and a module may be taught by more than one member of academic staff. The database should record the number of hours per week a member of academic staff spend teaching each module. Each member of academic staff is identified by a unique staff number. All members of staff and students have unique computer network user ID numbers. Additional data stored on each member of academic staff includes name (first and last), phone extension number, office number, sex, salary, post (lecturer, or senior lecturer, or Professor, etc.), qualifications, and address (not composite). A member of academic staff work for one department only.

Answer 1

for many to many relationships we have made a joining table

I Module Course course_code INT course tite VARCHAR(45) years INT ◆ Academ ic-staff-Staff num ber TNT courses uses modues module_code INT Start_date DATE End-date DATE Texts VAR AR(45) ◆ Couser-work VARCHAR (45) Indexes ◆Percentage DECIMAL 1< . Course, ourse_code INT Academic staff Staff number INT Academic staff_ module Academic staff Staff number INT Module_module_code INT taught by no of hours per week INT studied by_studen t Academic_staff Staff_number INT ◆ First-name VARCHAR(45) ◆ Last-name VARCHAR(45) coorniates Module_module_code INT Next to_kin First name OAR Last-name CHAR > Address VARCHAR(45) | 艹 Phone-extension-number VARCHAR(45) Office_num ber VARCHAR(45) Percentage-obtained IT Sex VARGAR(45) Salary VARCHAR(45) Post VARCHAR(45) teaches modules Relationship VARCHAR (45) Student Matriculaton number INT ts reads modules ◇ Role VARCHAR(45) ◆ qualification VAR AR(45) Student Matriculation number INT ◆ First-name CHAR Last Name CHAR town VARCHAR(45) ◆ Addres VARCHAR(45) t has next to kin employs_staffs street VARCHAR(45) enroll for the courses ? Name CHAR ◆Phone number INT Fax number BIGINT Block_number VARCHAR (45) Academic_staff_Stoff.num ber INT 、 postcode INT date ofbirth DATE ◇ loan NT ◆ Course-course-code INT

since the academic staff can be a manager, course coordinator we have used the attribute role and set as null.

you can use isa(extended entity relationship) here to show the role of the staff if you want