$**$PLEASE USE VISUAL PARADIGM OR SOMETHING SIMILAR FOR THE ERD$**$

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.

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

Case:

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.