1. Exercises: 1.7, 1.9, 2.10.
2. ER Model Question
The Director of Housing at the University of Souther Pacifiic requires you to design and implement a database systems to assist with the administration of the office. The requirements collection and analysis phase of the database design process based on his view has provided the following requirements specification for the Housing Office database system:
(1) Each residence hall has a name, number, address, telephone number, and a hall manager who supervises the operation of the residence hall. The halls provide only single rooms which have a room number, site number, and a weekly rental rate. The total number of rooms provided by the housing office is also available.
(2) The site number uniquely identifies each room in all of the halls controlled by the Housing office, and is used when renting a room to a student.
(3) Students may rent rooms throughout the academic year for various periods of time. Each individual rent agreement between a student and the Housing Office is uniquely identified by a lease number.
The data stored on rooms currently rented out includes the lease number, date the student started the rent period and date the student wishes to terminate the lease (if known).
(4) The data stored on each student includes: the ID number, name (first and last), mailing address (street, city, state, zip), date of birth, status (freshman, sophomore, junior, senior, graduate).
The student information stored relates to those currently renting a room and those on the waiting list.
The total number of student records stored by the Housing Office should also be available.
(5) The Housing Office also stores a limited amount of information on courses run by the university including: course number, course title, instructor, and dept. that offers the course. Each student is associated with one course.
(6) Whenever possible, information on a student's next-of-kin is stored, which includes the name, relationship, address, and contact telephone number.
Create a conceptual schema for the Housing Office using the concepts of the Entity-Relationship (ER) model.
State any assumptions you made when creating the ER model.