QUESTION:

Question 1.1: Define the entities, attributes and draw ERD.

Question 1.2: List the Functional and Transitive dependencies in each relation

Question 1.3 Normalize each relation to 3NF.

Question 1.4: Create the database using SQL queries

SCENARIO:

Nikaed Student Accommodation Office currently using a Database to maintain their record, however, in the last meeting, the Dean of Student Accommodation decided to replace the presently running outdated database with a new database. The Dean of Accommodation wants to have a new database designed and developed to assist with the administration of the multi- campus office. The initial requirement collection and analysis phase of the database design process has provided the following data requirements specification for the University Accommodation. The office database, followed by examples of query transactions that should be supported by the database.

Data Requirements:

Students: Only full-time students can avail the university housing. The data stored for each student include student ID (badge no), student name (first and last name), home address (street, city, postcode), Nationality, age, email, mobile phone number, date of birth, gender, Study level of student (undergraduate, postgraduate), special needs, any additional comments, current status (placed/waiting), school (i.e. SIT).

The student information stored relates to those currently renting a room as well as those on the waiting list. Students may rent a room in a hall of residence or student apartment. When a student joins the university housing, he/she is assigned a staff member who acts as his/her Adviser. The Adviser is responsible for monitoring the student's welfare and academic progression throughout his or her time at the university. The data held on a student's Adviser includes full name, staff id (badge no), position, name of the department, internal telephone number, email, and office number. In addition to this, a new student is also assigned a mentor as a guide. The student-mentor is also a student.

Airotciv Hall: 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 luxury 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 Residence Office and is used when renting a room to a student.

Nikaed Unit: The University residence also offers single room for student. These are fully furnished and provide single-room accommodation for groups of three, four, or five students. The information held on student apartments includes an apartment number, address, and the number of single bedrooms available in each unit. The unit number uniquely identifies each unit. Each bedroom in unit has a monthly rent rate, room number, and a place number. The place number uniquely identifies each room available in all student Units and is used when renting a room to a student.

Dormitory: The Residence Office also offers dormitory. These are fully furnished and provide dormitory accommodation for groups of max three undergraduate students. The information held on student dormitory includes an dormitory number, dormitory bed number (1,2,3), address, and the number of dormitories available in each apartment. The flat number uniquely identifies each apartment. Each bed in Dormitory has a monthly rent rate, Dormitory and bed number, and a place number. The place number uniquely identifies each room and bed number available in all student apartments and is used when renting a room to a student.

Leases: A student may rent a room in a hall or student apartment or dormitory for various periods. 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 semesters 1 and 2 and the summer semester. Each individual lease agreement between a student and the Residence 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 Bdg number, place number, room number, address details of the hall or student apartment, and the date the student wishes to enter the room, and the date the student wishes to leave the room (if known).

Payment and Inspection: At the start of each semester, each student is sent a payment invoice for the rental period. Each payment invoice has a unique number. The data stored on each invoice consist of invoice number, lease number, term (T1,T2, T3), due payment, student's full name and badge number (studentID), place number, room number, and the address of the hall or apartment. 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), the date the first and second reminder was sent (if necessary). Staff inspect the accommodation on a regular basis to ensure that the accommodation is well maintained inspect student apartment inspections Student apartments. 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 property was found to be in a satisfactory condition (yes or no), and any additional comments.

Accommodation 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, Hostel Manager, Administrative Assistant, Cleaner) and location (for example, Residence Office or Hall).

Program: The Residence Office also stores a limited amount of information on the Program offered by the university, including the course number, Program title (including year), Program director, campus telephone number, director email, room number, and department name. Each student is also associated with a single program of studies.

Guardian: Whenever possible, information on a student's guardian is stored, which includes the name, relationship, address (street, city, postcode), and contact telephone number.

Student and staff can login into the system and system can keep record of login history.