Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Design and build an optimized University Accommodation database system for Access University College. Project description Access University College has seen an incredible growth in its

Design and build an optimized University Accommodation database system for Access University College.
Project description
Access University College has seen an incredible growth in its student population. Since its establishment, the university accommodation database system has experienced tremendous growth, resulting in a number of performance and scalability difficulties. It is time to improve and optimize the database in order to satisfy the university's expanding needs.
As a reference point, find below the requirements of the University during the design of the existing Database System.
When a student joins the university, he or she is assigned to a member of staff who acts as his or her Adviser. The Adviser is responsible for monitoring the students welfare and academic advancement throughout his or her time at the university. The data held on a students Adviser includes full name, position, name of department, mobile phone number, email, and residential address.
The data stored for each student includes the name (first and last name), home address (street, city, postcode), mobile phone number, email, date of birth, gender, category of student (for example, undergraduate, postgraduate), nationality, special needs, any additional comments, current status (placed/waiting), and programme. The student information stored relates to those currently renting a room and those on the waiting list.
Whenever possible, information on a students next-of-kin is stored, which includes the name, relationship to student, residential address (street, city, postcode), and contact phone number.
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 Residence Office and is used when renting a room to a student.
A student may rent a room 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 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), students name and place number, address details of the hall and the date the student wishes to enter the room, and the date the student wishes to leave the room (if known).
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, students full name and place 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 (cheque, cash, card, and so on)
The 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, place number, an indication of whether the property was found to be in a satisfactory condition (yes or no), and any additional comments.
Some information is also held on members of staff of the Residence 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, Residence Office or Hall).
The breakdown of the project task is given below;
Phase One
Presentation :
a) With the aid of a database modelling software, create the following Entity Relationship Diagram (ERD);
i. Conceptual ERD
ii. Logical ERD
iii. Physical ERD
For each ERD , i want you to draw and design the
b) Derive relational schema from the above case study that represents the entities and relationships. Identify primary, alternate and foreign keys.
Note: use the following notation to describe your relational schema, as shown in the example of a Staff relation given below.
staffNo fName lName address NIN sex DOB deptNo
c) Using the technique of normalization, normalise your schemas above to improve efficiency and reduce redundancy. This may involve refining table structures, indexes, and relationships.
write and normalize the schemas withe explanation.
Phase Two
Presentation:
a) Using mysql server, implement the normalized schemas. Ensure that referential integrity is established between related tables.
b) Store data into each relation. The minimum tuples should be 10 for each relation.
c) Generate the SQL script for the database created.
Phase Three
Presentation:
a) Create SQL queries that are optimized for the following statements.
i. List of details of all students currently on the waiting list for accommodation.
ii. List of students detail with the details of their lease agreements.
iii. List of students who have not paid their invoices by a given date.
iv. List the minimum, maximum, and average monthly rent for rooms in residence halls.
v. Display the details of apartment inspections where the property was found to be in an unsatisfactory condition.
b) Create three (3) separate database views for i, ii & iii above.
c) Discuss the concurrency control mechanisms used in the DBMS selected.
d) Develop a backup strategy for the database, including regular backups and recovery procedures in case of data loss.
e) Implement user authentication and authorization mechanisms to control access to the database.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Spatio Temporal Database Management International Workshop Stdbm 99 Edinburgh Scotland September 10 11 1999 Proceedings Lncs 1678

Authors: Michael H. Bohlen ,Christian S. Jensen ,Michel O. Scholl

1999th Edition

3540664017, 978-3540664017

More Books

Students also viewed these Databases questions

Question

LO15.6 Prepare to present effectively in teams.

Answered: 1 week ago

Question

Why We Form Relationships Managing Relationship Dynamics?

Answered: 1 week ago