advanced database systems

CS4123 – ADVANCED DATABASE SYSTEMS END OF SUMMER SEMESTER 2019 PROJECT

1. For the DreamHome case study documented in Appendix A, suggest attributes and methods that would be appropriate for Branch, Staff, and PropertyForRent classes.

2. Produce use case diagrams and a set of associated sequence diagrams for the DreamHome case study documented in Appendix A.

3. Produce use case diagrams and a set of associated sequence diagrams for the University Accommodation Office case study documented in Appendix B.1.

4. Analyze any three DBMSs and discover how object-oriented features are implemented. Prepare a critical report on the contrasting and common features provided by them.

5. You have been asked by the Managing Director of DreamHome to investigate and prepare a report on the applicability of an OODBMS for the organization. The report should compare the technology of the RDBMS with that of the OODBMS, and should address the advantages and disadvantages of implementing an OODBMS within the organization, and any perceived problem areas. Finally, the report should contain a fully justified set of conclusions on the applicability of the OODBMS for DreamHome.

6. Analyze the University Accommodation Office case study documented in appendix B.1 and advice whether it is justifiable to adopt an object-oriented approach.

7. Produce an object-oriented database design for the University Accommodation Office case study presented in Appendix B.1. State any assumptions necessary to support your design.

8. Using the rules for schema consistency given in Section 27.4.3 and the sample schema given in Figure 27.11, consider each of the following modifications and state what the effect of the change should be to the schema:

a) adding an attribute to a class; b) deleting an attribute from a class; c) renaming an attribute; d) making a class S a superclass of a class C; e) removing a class S from the list of superclasses of a class C; f) creating a new class C; g) deleting a class; h) modifying class names.

APPENDIX

A Users’ Requirements Specification for DreamHome Case Study

Objectives

In this appendix you will learn:

• The data and transaction requirements for the Branch and Staff user views of the DreamHome case study described in Section 11.4.

This appendix describes the users’ requirements specification for the Branch and Staff user views of the DreamHome database system. For each collection of user views, the “Data Requirements” section describes the data used and the “Data Transactions” section provides examples of how the data is used.

A.1 Branch User Views of DreamHome A.1.1 Data Requirements Branches

DreamHome has branch offices in cities throughout the United Kingdom. Each branch office is allocated members of staff, including a Manager, who manages the operations of the office. The data describing a branch office includes a unique branch number, address (street, city, and postcode), telephone numbers (up to a maximum of three), and the name of the member of staff who currently manges the office. Additional data is held on each Manager, which includes the date that the Manager assumed his or her position at the current branch office, and a monthly bonus pay- ment based upon his or her performance in the property for rent market.

Staff

Members of staff with the role of Supervisor are responsible for the day-to-day activities of an allocated group of staff called Assistants (up to a maximum of 10, at any one time). Not all members of staff are assigned to a Supervisor. The data stored regarding each member of staff includes staff number, name, address, posi- tion, salary, name of Supervisor (where applicable), and the details of the branch office at which a member of staff is currently working. The staff number is unique across all branches of DreamHome.

A-1

Z01_CONN3067_06_SE_App1.indd 1 04/06/14 9:57 AM

A-2 | Appendix A Users’ Requirements Specification for DreamHome Case Study Properties for rent

Each branch office offers a range of properties for rent. The data stored for each property includes property number, address (street, city, postcode), type, number of rooms, monthly rent, and the details of the property owner. The property num- ber is unique across all branch offices. The management of a property is assigned to a member of staff whenever it is rented out or requires to be rented out. A member of staff may manage a maximum of 100 properties for rent at any one time.

When a given property is available for rent, the property details will be displayed on the DreamHome Web site and, when necessary, as advertisements in local and national newspapers.

Property owners

The details of property owners are also stored. There are two main types of property owner: private owners and business owners. The data stored for private owners includes owner number, name, address, telephone number, email, and password. The data stored on business owners includes name of business, type of business, address, telephone number, email, password, and contact name. The password will allow owners access to parts of the DreamHome database using the Web.

Clients

DreamHome refers to members of the public interested in renting property as clients. To become a client, a person must first register at a branch office of DreamHome. The data stored on clients includes client number, name, telephone number, email, preferred type of accommodation, and the maximum rent that the client is prepared to pay. Also stored is the name of the member of staff who processed the registration, the date the client joined, and some details on the branch office at which the client registered. The client number is unique across all DreamHome branches.

Leases

When a property is rented out, a lease is drawn up between the client and the property. The data listed in detail on the lease includes lease number, client num- ber, name and address, property number and address, monthly rent, method of payment, an indication of whether the deposit has been paid deposit (calculated as twice the monthly rent), duration of lease, and the start and end dates of the lease period.

Newspapers

When required, the details of properties for rent are advertised in local and national newspapers. The data stored includes the property number, address, type, number of rooms, rent, the date advertised, the name of the newspaper, and the cost to advertise. The data stored on each newspaper includes the newspaper name, address, telephone number, and contact name.

Z01_CONN3067_06_SE_App1.indd 2 04/06/14 9:57 AM

A.1 Branch User Views of DreamHome | A-3 A.1.2 Transaction Requirements (Sample) Data entry

Enter the details of a new branch (such as branch B003 in Glasgow). Enter the details of a new member of staff at a branch (such as Ann Beech at branch B003). Enter the details of a lease between a client and property (such as client Mike Ritchie renting out property number PG4 from the 10-May-12 to 9-May-13). Enter the details of a property advertised in a newspaper (such as property number PG4 advertised in the Glasgow Daily newspaper on the 06-May-12).

Data update/deletion

Update/delete the details of a branch. Update/delete the details of a member of staff at a branch. Update/delete the details of a given lease at a given branch. Update/delete the details of a newspaper advertisement at a given branch.

Data queries

Examples of queries required by the Branch user views:

(a) List the details of branches in a given city. (b) Identify the total number of branches in each city. (c) List the name, position, and salary of staff at a given branch, ordered by staff

name. (d) Identify the total number of staff and the sum of their salaries. (e) Identify the total number of staff in each position at branches in Glasgow. (f) List the name of each Manager at each branch, ordered by branch address. (g) List the names of staff supervised by a named Supervisor. (h) List the property number, address, type, and rent of all properties in Glasgow,

ordered by rental amount. (i) List the details of properties for rent managed by a named member of staff. (j) Identify the total number of properties assigned to each member of staff at a

given branch. (k) List the details of properties provided by business owners at a given branch. (l) Identify the total number of properties of each type at all branches. (m) Identify the details of private property owners that provide more than one

property for rent. (n) Identify flats with at least three rooms and with a monthly rent no higher than

£500 in Aberdeen. (o) List the number, name, and telephone number of clients and their property

preferences at a given branch. (p) Identify the properties that have been advertised more than the average num-

ber of times. (q) List the details of leases due to expire next month at a given branch.

Z01_CONN3067_06_SE_App1.indd 3 04/06/14 9:57 AM

A-4 | Appendix A Users’ Requirements Specification for DreamHome Case Study (r) List the total number of leases with rental periods that are less than one year at

branches in London. (s) List the total possible daily rental for property at each branch, ordered by

branch number.

A.2 Staff User Views of DreamHome A.2.1 Data Requirements Staff

The data required on members of staff includes staff number, name (first and last name), position, gender, date of birth (DOB), and name of the Supervisor (where appropriate). Members of staff in the position of Supervisor supervise an allocated group of staff (up to a maximum of 10 at any one time).

Properties for rent

The data stored on property for rent includes property number, address (street, city, and postcode), type, number of rooms, monthly rent, and the details of the property owner. The monthly rent for a property is reviewed annually. Most of the properties rented out by DreamHome are apartments (or flats). The management of a property is assigned to a member of staff whenever it is rented out or ready to be rented out. A member of staff may manage a maximum of 100 properties for rent at any one time.

Property owners

There are two main types of property owner: private owners and business own- ers. The data stored on private owners includes owner number, name (first and last name), address, telephone number, email, and password. The data stored on business owners includes owner number, name of business, business type, address, telephone number, email, password, and contact name.

Clients

When a prospective client registers with DreamHome, the data stored includes the client number, name (first and last name), telephone number, email, and some data on the desired property, including the preferred type of accommodation and the maximum rent that the client is prepared to pay. Also stored is the name of the member of staff who registered the new client.

Property viewings

Clients may request to view property. The data stored includes client number, name and telephone number, property number and address, date the client viewed the property, and any comments made by the client regarding the suitability of the property. A client may view the same property only once on a given date.

Leases

Once a client finds a suitable property, a lease is drawn up. The information on the lease includes lease number, client number and name, property number,

Z01_CONN3067_06_SE_App1.indd 4 04/06/14 9:57 AM

A.2 Staff User Views of DreamHome | A-5 address, type and number of rooms, monthly rent, method of payment, deposit (calculated as twice the monthly rent), whether the deposit is paid, the start and end dates of the rental period, and the duration of the lease. The lease number is unique across all DreamHome branches. A client may hold a lease associated with a given property from a minimum of three months to a maximum of 1 year.

A.2.2 Transaction Requirements (Sample) Data entry

Enter the details for a new property and the owner (such as details of property number PG4 in Glasgow owned by Tina Murphy). Enter the details of a new client (such as details of Mike Ritchie). Enter the details of a client viewing a property (such as client Mike Ritchie viewing property number PG4 in Glasgow on the 06-May-12). Enter the details of a lease between a client and property (such as client Mike Ritchie renting out property number PG4 from the 10-May-12 to 9-May-13).

Data update/deletion

Update/delete the details of a property. Update/delete the details of a property owner. Update/delete the details of a client. Update/delete the details of a property viewing by a client. Update/delete the details of a lease.

Data queries

Examples of queries required by the Staff user views:

(a) List details of staff supervised by a named Supervisor at the branch. (b) List details of all Assistants alphabetically by name at the branch. (c) List the details of property (including the rental deposit) available for rent at

the branch, along with the owner’s details. (d) List the details of properties managed by a named member of staff at the

branch. (e) List the clients registering at the branch and the names of the members of staff

who registered the clients. (f) Identify properties located in Glasgow with rents no higher than £450. (g) Identify the name and telephone number of an owner of a given property. (h) List the details of comments made by clients viewing a given property. (i) Display the names and phone numbers of clients who have viewed a given

property but not supplied comments. (j) Display the details of a lease between a named client and a given property. (k) Identify the leases due to expire next month at the branch. (l) List the details of properties that have not been rented out for more than three

months.

(m) Produce a list of clients whose preferences match a particular property.

Z01_CONN3067_06_SE_App1.indd 5 04/06/14 9:57 AM

Z01_CONN3067_06_SE_App1.indd 6 04/06/14 9:57 AM

appendix

B Other Case Studies Objectives

In this appendix you will learn:

•. The University Accommodation Office case study, which describes the data and transaction requirements of a university accommodation office.

•. The EasyDrive School of Motoring case study, which describes the data and transaction requirements of a driving school.

•. The Wellmeadows Hospital case study, which describes the data and transaction requirements of a hospital.

This appendix describes the University Accommodation Office case study in Section B.1, The EasyDrive School of Motoring in Section B.2, and the Wellmeadows Hospital case study in Section B.3.

B.1 The University Accommodation Office Case Study The director of the University Accommodation Office requires you to design a data- base to assist with the administration of the office. The requirements collection and analysis phase of the database design process has provided the following data requirements specification for the University Accommodation Office database followed by examples of query transactions that should be supported by the database.

B.1.1 Data Requirements Students

The data stored for each full-time student includes: the banner number, name (first and last name), home address (street, city, postcode), mobile phone number, email, date of birth, gender, category of student (for example, first-year undergraduate, postgraduate), nationality, special needs, any additional comments, current status (placed/waiting), major, and minor.

B-1

Z02_CONN3067_06_SE_APP2.indd 1 11/07/14 4:43 PM

B-2 | Appendix B Other Case Studies 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 apartment.

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 stu- dent’s welfare and academic progression throughout his or her time at the univer- sity. The data held on a student’s Adviser includes full name, position, name of department, internal telephone number, email, and room number.

Halls of residence

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.

Student flats

The Residence Office also offers student apartments. 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 apartment. The flat number uniquely identifies each apartment.

Each bedroom in an apartment has a monthly rent rate, room number, and a place number. The place number uniquely identifies each room 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 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), student’s name and banner number, place number, room num- ber, 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).

Invoices

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 banner number, 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,

Z02_CONN3067_06_SE_APP2.indd 2 11/07/14 4:43 PM

B.1 The University Accommodation Office Case Study | B-3 the method of payment (check, cash, Visa, and so on), the date the first and second reminder was sent (if necessary).

Student apartment inspections

Student apartments are inspected by staff on a regular basis to ensure that the accommodation is well maintained. The information recorded for each inspec- tion 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.

Residence staff

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).

Courses

The Residence Office also stores a limited amount of information on the courses offered by the university, including the course number, course title (including year), course instructor, instructor’s on-campus telephone number, email, room number, and department name. Each student is also associated with a single programme of studies.

Next-of-kin

Whenever possible, information on a student’s next-of-kin is stored, which includes the name, relationship, address (street, city, postcode), and contact telephone number.

B.1.2 Query Transactions (Sample) Listed here are some examples of query transactions that should be supported by the University Accommodation Office database system:

(a) Present a report listing the Manager’s name and telephone number for each hall of residence.

(b) Present a report listing the names and banner numbers of students with the details of their lease agreements.

(c) Display the details of lease agreements that include the summer semester. (d) Display the details of the total rent paid by a given student. (e) Present a report on students who have not paid their invoices by a given date. (f) Display the details of apartment inspections where the property was found to

be in an unsatisfactory condition. (g) Present a report of the names and banner numbers of students with their room

number and place number in a particular hall of residence. (h) Present a report listing the details of all students currently on the waiting list

for accommodation; that is; who were not placed. (i) Display the total number of students in each student category.

Z02_CONN3067_06_SE_APP2.indd 3 11/07/14 4:43 PM

B-4 | Appendix B Other Case Studies (j) Present a report of the names and banner numbers for all students who have

not supplied details of their next-of-kin. (k) Display the name and internal telephone number of the Adviser for a particular

student. (l) Display the minimum, maximum, and average monthly rent for rooms in resi-

dence halls. (m) Display the total number of places in each residence hall. (n) Display the staff number, name, age, and current location of all members of the

residence staff who are over 60 years old today.

B.2 The EasyDrive School of Motoring Case Study The EasyDrive School of Motoring was established in Glasgow in 1992. Since then, the school has grown steadily and now has several offices in most of the main cities of Scotland. However, the school is now so large that more and more administrative staff are being employed to cope with the ever-increasing amount of paperwork. Furthermore, the communication and sharing of information between offices, even in the same city, is poor. The Director of the school, Dave MacLeod, feels that too many mistakes are being made and that the success of the school will be short-lived if he does not do something to remedy the situation. He knows that a database could help in part to solve the problem and has approached you and your team to help in creating a database system to support the running of the EasyDrive School of Motoring. The Director has provided the following brief description of how the EasyDrive School of Motoring operates.

B.2.1 Data Requirements Each office has a Manager (who tends to also be a Senior Instructor), several Senior Instructors, Instructors, and administrative staff. The Manager is responsi- ble for the day-to-day running of the office. Clients must first register at an office, which includes completion of an application form, which records their personal details. Before the first lesson, a client is requested to attend an interview with an Instructor to assess the needs of the client and to ensure that the client holds a valid provisional driving license. A client is free to ask for a particular Instructor or to request that an Instructor be changed at any stage throughout the process of learning to drive. After the interview, the first lesson is booked. A client may request individual lessons or book a block of lessons for a reduced fee. An indi- vidual lesson is for one hour, which begins and ends at the office. A lesson is with a particular Instructor in a particular car at a given time. Lessons can start as early as 8:00 a.m. and as late as 8:00 p.m. After each lesson, the Instructor records the progress made by the client and notes the mileage used during the lesson. The school has a pool of cars, which are adapted for the purposes of teaching. Each Instructor is allocated to a particular car. As well as teaching, the Instructors are free to use the cars for personal use. The cars are inspected at regular intervals for faults. Once ready, a client applies for a driving test date. To obtain a full driving license, the client must pass both the driving and written parts of the test. It is the

Z02_CONN3067_06_SE_APP2.indd 4 11/07/14 4:43 PM