Create a Normalized Entity-RelationshipDiagram (ERD) to 3NF for the ABC University Accommodation Office using Crows Feet notation and the information below. Please show all data modeling work which includes entities, entity attributes, cardinality of relationships, key constraints, and relevant assumptions made in modeling the data. Must use a data modeling tool such as MS Visio, Lucid Chart, etc.

Scenario - ABC University Accommodation Office (Student Housing)

The director of the ABC University Accommodation Office requires you to design a database to assist with the administration of the office and the renting of residences to students. The requirements collection and analysis phase of the database design process has provided the following data requirements specification for the ABC University Accommodation Office database.

Data Requirements

The housing data stored for each full-time student includes: the student ID number, name (first and last name), home address (street, city, zip code), mobile phone number, email, date of birth, gender, category of student (for example, first-year undergraduate, postgraduate), nationality, special needs, any additional comments, current status (placed/waiting), major, and minor. The student information stored relates to those currently renting a room and those on the waiting list. Students rent a room in a hall of residence. When a student joins ABC University, he or she is assigned to a member of staff who acts as his or her Advisor. The Advisor is responsible for monitoring the student's welfare and academic progression throughout his or her time at ABC University. The data held on a student's Advisor includes full name, position,department name, internal telephone number, email, and room number.

Halls of Residence

Each hall of residence has a name, address, telephone number, and a hall manager,

who supervises the operation of the hall. The halls provide only single rooms, which have a room number, place number, and monthly rent rate.

The place number uniquely identifies each room in all halls controlled by the Accommodation Office and is used when renting a room to a student.

Leases

A student may rent a room in a hall for various periods of time. New lease agreements are negotiated at the start of each academic year, with a minimum rental period of one semester and a maximum rental period of one year, which includes three semesters: one, two and the summer semester. Each individual lease agreement between a student and the Accommodation Office is uniquely identified using a lease number. The data stored on each lease includes the lease number, duration of the lease (given as semesters), student's name and ID number, place number, room number, address details of the hall of residence, and the date the student wishes to enter the room, and the date the student wishes to leave the room (if known).

Invoices

At the start of each semester, each student is sent an invoice for the following rental period. Each invoice has a unique invoice number.The data stored on each invoice includes the invoice number, lease number, semester, payment due, student's full name and ID number, place number, room number, and the address of the hall. Additional

data is also held regarding the payment of the invoice and includes the date the invoice was paid, the method of payment (check, cash, Visa, and so on), and the date the first and second reminder was sent (if necessary).

Halls of Residence inspections

Halls of residence rooms are inspected by staff on a regular basis to ensure that the accommodation is well maintained. The information recorded for each inspection is the name of the member of staff who carried out the inspection, the date of inspection, an indication of whether the room was found to be in a satisfactory condition (yes or no), and any additional comments.

Residence staff

Some information is also held on members of staff of the Accommodation Office and includes the staff number, name (first and last name), email, home address (street, city, postcode), date of birth, gender, position (for example, Hall Manager, Administrative Assistant, Cleaner) and location (for example, Accommodation Office or Hall).

Courses

The Accommodation Office also stores a limited amount of information on the courses offered by the university, including the course number, course title (including year), course instructor, instructor's on-campus telephone number, email, room number, and department name. Each student is also associated with a single program of study.

Next-of-kin

Whenever possible, information on a student's next-of-kin is stored, which includes the name, relationship, address (street, city, postcode), and contact telephone number.