A major faculty consisting of many Departments in the Whangarei University of Technology (WUT) has approached you for the design and implementation of a relational database system. The requirement collection and analysis phase of the database design process provided the following data requirements for the faculty’s proposed database system.

WUT offers many programs (e.g., Bachelor of Science, Bachelor of Information Technology, Bachelor of Software Engineering, Bachelor of Computer and Information Sciences). The information to be stored for a program is: program code, level, points, duration (in years), campus, and semester.

Each department runs several programs. Each program uses several courses (e.g., Programming I, Database System Design), but not every course is offered every semester/year to students. A course can be offered in more than one program, and a program may offer many courses. The information to be stored about a course is: the course code, course name, and course credits.

Each program is managed (e.g., a Programme Director) by a member of the academic staff, and each course is coordinated by a member of the academic staff also.

A student can enrol in one program only at a time. Once enrolled a student is assigned a unique student identification number. To complete a program, each student must undertake and pass all the required courses in the program. This requires that the database store the performance (pass or fail) of each student in every program.

Additional data stored on each student includes student name (first and last), address (town, street, and postcode), date of birth, gender, and student financial loan (Yes or No). For emergency purposes, the database stores the last name, first name, address, phone, and relationship of each student’s next of kin. Assume that every next-of-kin is a next-of-kin of one student only.

Each department is led by an academic staff member (e.g., the Head of the Department). The database should record the date the staff member started the role as Head of the department. Each department has a name, code, phone number, and location (e.g., Z Building). Each department employs many members of academic staff. An academic staff member works for one department only.

An academic staff member can be the director of at most one program but can be the coordinator of more than one course. An academic staff member may not be assigned any of the above-mentioned roles (such as Course Coordinator, Programme Director, or Head of the Department). All members of the academic staff teach courses. Every member of the academic staff may teach zero, one or more courses, and a course may be taught by more than one academic staff member. The database should record the number of hours an academic staff member spends teaching each course per week. The information to be stored for an academic staff member is: staff number, name (first and last), employment start date, phone extension number, office number, gender, salary, position (e.g. Lecturer, Senior Lecturer, Professor), and highest qualification.

 

Task 1 Identify Entities & Develop Business Rules [10 marks] Identify all entities and relationships in the case study and develop a set of business rules. You should follow the syntax given below. Two rules must describe each relationship, one in each direction. Each/A/An ENTITY_1 May/Must Relationship_Verb_Phrase number ENTITY_2 Task 2 Construct a logical Entity Relationship Diagram (ERD) [25 marks] Based on the business rules developed in Task 1, construct a logical ERD for the case study using the Visual Paradigm tool. Identify all attributes in each entity, including all primary and foreign keys (transform any composite and multivalued attributes according to the rules of the relational model). Name all relationships (i.e. you must use verb phrases on both sides of each relationship) Identify the Cardinality and Participation for each relationship. Resolve all many to many (M:N) relationships. Describe any assumptions you have made in a text note on the diagram. Please note: 1) You don't need to identify data types in your ERD. 2) You don't need to answer task 2 questions separately. You identify them on the diagram.