In this assignment you are required to:
- Design; implement a solution to a business problem based on the case study given.
- Implement the solution in Oracle.
Case Study.
The head of SEGi University Accommodation Department (WAD) requires you to design and implement a database to assist with the administration of the department. The following describes information requirements for SUAD database.
The accommodation is only available to full-students only. The data stored on each full-time student includes: the registration number, name, contact number, home address (street, city, postcode, state and country), date of birth, gender, category of student (for example, first year undergraduate, postgraduate), nationality, special needs, current status (placed/waiting), and what program the student is registered, next-of-kin, relationship to next-of-kin and next-of-kin contact number, address (street, city, postcode, state, country).
The student information stored relates to those currently renting a room and those on the waiting list. Students may rent a room in a hall of residence or student flat. When a student joins the University, he or she is assigned to a member of staff who acts as his or her Advisor of Studies. The Advisor of Studies is responsible for monitoring the student's welfare and academic progression throughout his or her time at University. The data held on a student's Advisor includes full name, position, and name of department, internal telephone number, and room number.
The SUAD also stores a limited amount of information on the program am by the University including the program number, program leader, internal telephone number, room number, and department name. Each student is associated with a single program.
Each hall of residence has a name, address, telephone number, and a hall supervisor 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 SUAD and is used when renting a room to a student.
The SUAD also offers student flats. These flats are fully furnished and provide single-room accommodation for groups of three, four, or five students. The information held on student flats includes a flat number, address, and the number of single bedrooms available in each flat. The flat number uniquely identifies each flat. Each bedroom in a flat has a monthly rent rate, room number, and a place number. The place number uniquely identifies each room available in all student flats and is used when renting a room to a student.
A student may rent a room in a hall or student flat 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 , 2, and 3. Each individual lease agreement between a student and the SUAD is uniquely identified using a lease number. The data stored on each lease includes the lease number, duration of the lease (given as semesters), name and matriculation number of the student, place number, room number, address details of the hall or student flat, 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, student's full name and registration number, place number, room number, and the address of the hall or flat. Additional data is also held on the payment of the invoice and includes the date the invoice was paid, the method of payment (cheque, cash, credit card, etc.), and the date the first and second reminder is sent (if necessary).
Student flats 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 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 SUAD and includes the staff number, name, home address (street, city, postcode), date of birth, gender, position (for example, Hall Manager, Administrative Assistant, or Cleaner, and location (for example, SUAD or Hall).
Requirement:
1) Develop an E/R diagram for the data set described above. Make sure to indicate all cardinality constraints specified above. The E/R diagram should not contain redundant entity sets, relationships, or attributes. Also, use relationships whenever appropriate. You will be expected to use advanced modeling techniques (EER diagram)
Note: If you need to make any assumptions, include them in your answer.
2) Produce set of relational tables at least in 3NF derived from your ER model you produced in part 1) clearing showing the primary keys, foreign keys and attribute/column names assigned to each relations. You will be expected to justify any mapping issues.
3) Implement the relational tables for your answer in part 2) using Oracle (write SQL statements to create the relations). You will be expected to demonstrate appropriate constraints (entity, referential, column/domain constraints and tables, entity integrity, tables, referential integrity, appropriate test data (note: please design your test data based on the queries asked below.
Please insert at least 5 rows of relevant data into parent tables and at 10 rows of data into the child tables.
You are required to describe the structure and show the contents of all relational tables.
4) Create query to answer the following questions.
(a). Using substitution variable, produce a list of student names, registration number, program that they register (specify by user) and hall of residence they are staying (specify by the user),
Enter BIT for Program register,
(b). Display the details of flat inspections where the property was found to be in an unsatisfactory condition
(c). Produce a pair of staff names, staff type, staff location that work at the same location.
(d). Display the minimum, maximum, and average monthly rent for rooms in halls of residence
(e). Display the details of the total rent paid by a given student.