Please See Attachment:1. Create an Entity Relationship (UML Notations) 2. Logical Model (Relational Schema) including Primary and Foreign Keys3. Ensure RM is in 3NFCan you also build a DBMS based on the Logical Schema?Ensure it includes the following: Make sure that your primary keys are assigned in each table. Verify that all of your fields are assigned an appropriate data type. Verify that all of your fields are assigned an appropriate field length. Use default values where appropriate (i.e. default for “OrderDate” as today’s date). Insert 2-3 rows of data in each table. You make up the dataThe University Accommodation Office Case Study
The Director of the University Accommodation Office requires you to design a database
to assist with the administration of the office. The requirements collection and analysis
phase of the database design process based on the Director’s view has provided the
following requirements specification for the Accommodation Office database.
The data stored on each full-time student includes the matriculation number, name (first
and last name), home address (street, city/town, postcode), date of birth, sex, category of
student (for example, first year undergraduate, postgraduate, etc), nationality, smoker
(yes
or no), special needs, any additional comments, current status (placed/waiting), and what
course the student is studying on.The student information stored relates to those currently
renting a room and those on the waiting list. Students may rent a room in a university
owned 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 progress. The data held on a student’s Advisor includes
their full name, position, name of department, internal telephone number, and room
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 the halls controlled by the Accommodation Office and is used when
renting a room to a student.
The Accommodation Office 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 (15 weeks) and a maximum rental period of one year, which
includes Semesters 1, 2, 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), name and matriculation number of the student, place number, room number,
address details of the hall or student flat, 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 matriculation 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 (check, cash,
Visa, etc), 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 Accommodation Office and
includes the staff number, name (first and last name), home address (street, city/town,
postcode), date of birth, sex, position (for example, Hall Manager, Administrative
Assistant, Cleaner), and location (for example, Accommodation Office or Hall).
The Accommodation Office also stores a limited amount of information on the courses
run by the University including the course number, course title (including year), course
leader’s name, internal telephone number, and room number, and department name. Each
student is associated with a single course.
Next-of-kin
Whenever possible, information on a student’s next-of-kin is stored which includes the
name, relationship, address (street, city/town, postcode), and contact telephone number.
Purchase answer to see full
attachment
Why Choose Us
- 100% non-plagiarized Papers
- 24/7 /365 Service Available
- Affordable Prices
- Any Paper, Urgency, and Subject
- Will complete your papers in 6 hours
- On-time Delivery
- Money-back and Privacy guarantees
- Unlimited Amendments upon request
- Satisfaction guarantee
How it Works
- Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
- Fill in your paper’s requirements in the "PAPER DETAILS" section.
- Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
- Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
- From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.