Task $1$ Identify Entities & Develop Business Rules

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 ENTITY$\_1$ May$/$Must Relationship$\_$Verb$\_$Phrase number ENTITY$\_2$

Task $2$ Construct a logical Entity Relationship Diagram $($ERD$)$

Based on the business rules developed in Task $1,$ construct a logical ERD for the case study using the Visual Paradigm tool.

a$)$ 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$).$

b$)$ Name all relationships $($i$.$e$.$ you must use verb phrases on both sides of each relationship$)$

c$)$ Identify the Cardinality and Participation for each relationship.

d$)$ Resolve all many$-$to$-$many $($M:N$)$ relationships.

e$)$ Describe any assumptions you have made in a text note on the diagram.

Please note: You don$$t need to identify data types in your ERD.

Case Study:

A major faculty consisting of many departments at the Whangarei Institute of Technology $($WIT$)$ has approached you for the design and implementation of a relational database system. The requirements phase of the database development cycle identified the following data requirements for the faculty$$s proposed database system.

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

Each department runs several programmes. Each programme 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 programme, and a programme may offer many courses. The information to be stored about a course is: course code, course name, and course credits.

Each programme 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 programme only at a time. Once enrolled a student is assigned a unique student identification number. To complete a programme, each student must undertake and pass all the required courses in the programme. This requires that the database store the performance $($pass or fail$)$ of each student in every programme.

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 programme 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, 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.