Answers on Relational Database Principles and SQL

Question 1: Relational algebra (20 marks)

This assignment requires you to answer a number of questions on relational database principles and SQL, and to design a database based on a case study.

�Learning Outcomes�

The assignment addresses the following learning outcomes for the unit:

LO 2. Demonstrate an understanding of relational database principles and theory

LO 3. Demonstrate practical skills in using SQL

LO 4. Demonstrate practical skills in data modelling using entity-relationship modelling

LO 5. Demonstrate practical skills in normalisation and convert a conceptual database design to a logical design in 3NF (partly)

Marks are distributed as follows

�

Question 1: Relational algebra	20
Question 2: SQL Select queries	20
Question 3: Further SQL	15
Question 4: Normalisation	20
Question 5: Conceptual design	25
Total	100

�

Question 1: Relational algebra (20 marks)

You have been asked to work with a database that records information about various lectures that are available for students at a conference. Each lecture addresses a topic, is presented by a lecturer. There are a limited number of seats in each of the venues, and this is reflected in the maximum number of students who can attend a lecture (capacity).

The schema for this database is as follows: (note that primary keys are shown underlined, foreign keys in bold).

Student (StudentNo, StudentName, UniversityName)

University (UniversityName, Country)

Lecturer (LecturerName, Biography, UniversityName)

Lecturer (LectureNo, LectureName, Topic, Description, Capacity, DateAndTime, LecturerName)

Participant (StudentNo, LectureNo)

Provide relational algebra (NOT SQL) queries to find the following information. Each question is worth 2 marks.

NOTE:

• You can use the symbols s, P, etc or the words �PROJECT�, �RESTRICT� etc as you prefer.�
• You do not need to try to make efficient queries � just correct ones.
• Where you use a join, always show the join condition.

1. List the name and university of all lecturers.
2. List the lecture name and for all lectures on the topic of �Biological Diversity�. �
3. List the names of students who attended a lecture on the topic of �Biological Diversity�, �Bugs and Bubs�, or both.
4. List the names of all students who attended a lecture by a lecturer from a university in Malaysia.
5. List the names of students from Murdoch University who went to a lecture on the theme of �Biological Diversity� run by a lecturer from a university in Italy.
6. List the lecture number, name and topic of all lectures, and the names of the students that attended, if any.
7. List the name of any students who went to both the lecture named �Introduction to Biology� and the lecture �Darwin 101�.
8. List the name, biography and university of all lecturers from a university in Greece.
9. List the names of the students who did NOT attend the lecture named �Grassland Cultivation Masterclass�.
10. List the names of any students who went to all lectures.�

Question 2: SQL � SELECT queries (20 marks)

This question is based on the View Ridge Gallery database you have been using in the labs. See the textbook for background to the case and the table structures.

The tables are:

ARTIST

CUSTOMER

WORK

TRANS

CUSTOMER_ARTIST_INT

You can use the dtoohey tables that we have been using. If you prefer, you can create your own copies of these tables under your own account to work with. If you do so, you should ensure you copy the same sample data and constraints as in the dtoohey tables.

Provide SQL AND result tables for the following queries. Paste the queries and the result tables from either your SSH client or SQL Developer into your assignment document. You can use a screen capture for the result tables, but NOT for the SQL.

Each question is worth 2 marks.

1. List the full details of any work of art, including the name of the artist who created the work, that have been described as Surrealist.
2. List the details of any work of art (including the name of the artist who created the work and the acquisition and asking price details) currently held in the gallery (i.e. works of art that have not been sold) with an asking price of > $400.
3. List the title of any work of art that has two (and only two) copies recorded in the database.
4. List the names of all deceased artists and the age they were when they died.
5. List the name of each artist represented in the database and the number of works of art by that artist, ordered from lowest to highest number.
6. List the work ID, title and artist name of all the works of art that sold for more than the average price of all sales, and the price they sold for.
7. List the total amount of sales of works of art by the gallery for each year, and the number of works sold that year.
8. Which artist has had the most works of art sold, and how many of the artist�s works have been sold?
9. Calculate the total profit made on all the works of art that have been sold (the profit/loss on a work of art is the difference between the acquisition price and the sales price)
10. List the name of any customers who have an interest in all artists.

Question 3: Further SQL (15 marks)

You have been given the following specifications of a simple database for keeping track of lectures and those who presented the lecture at a national Biological Sciences conference (note that primary keys are shown underlined, foreign keys in bold).

Question 2: SQL � SELECT queries (20 marks)

You should run your SQL to demonstrate that it works correctly, and paste in the statements used plus the output from Oracle. �

PRESENTER (PresenterNo, PresenterName, Biography, InstitutionName)

LECTURE (LectureNo, LectureName, Description, Theme, Capacity, DateAndTime, PresenterNo)

Based on the table specifications provided, answer the following questions. Each question is worth 3 marks.

1. Give the SQL to create the PRESENTER table. Choose appropriate data types. None of the attributes should be allowed to be null. Include the primary key constraint.
2. Give the SQL to create the LECTURE table. Use appropriate data types, and include the primary key and foreign key constraints. Referential integrity should be set such that if a presenter is deleted from the database, any lectures that he or she is running will also be deleted.
3. Give the SQL to add your own record to the PRESENTER table. Include your name, �Murdoch University� as your institution, and make up a short (10-20 words) biography.
4. Give the SQL to add the attribute VenueName to LECTURE. There are three possible venues: Building A, Building B and Building C, which should be enforced by the SQL definition.
5. Give the SQL to record the fact that all lectures have had their capacity increased by 10.

Question 4: Normalisation (20 marks)

The following question is based upon the APPOINTMENT relation below that lists details of appointments in a Podiatry Clinic.

StaffNo	Podiatrist	PatientName	PatNum	PatTelNo	ApptDateTime	RoomNo	Room Ext
S1011	Scott Bilstein	Jill White	P400	0159-454-369	10-JAN-20 0900	R1	7711
S1011	Scott Bilstein	Ian Bell	P895	0148-427-839	10-JAN-20 0930	R2	7712
S1024	Harry Lopez	John Jamieson	P398	0001-568-032	10-JAN-20 1000	R1	7711
S1024	Harry Lopez	Gayle Gordon	P668	0148-427-954	10-JAN-20 1030	R1	7711
S1011	Scott Bilstein	Ian Bell	P895	0148-427-839	11-JAN-20 0845	R1	7711
S1032	Boris Wilson	Gayle Gordon	P668	0148-427-954	11-JAN-20 0845	R2	7712

StaffNo: Unique number used to identify each Podiatrist

Podiatrist: The name of the Podiatrist

PatientName: Name of the patient for whom the appointment has been booked

PatNum: Unique number allocated to individual patients

PatTelNo: Telephone number of the patient

ApptDateTime: The time and date when the appointment has been booked

RoomNo: The room in which the appointment will take place

RoomExt: The telephone extension number of the treatment room.

You have been asked to design a relational database based on this design. You know that there are problems with the current design and that it will need to be modified in order to work effectively.

You need to write a report that addresses the following:

1. What are the specific problems associated with the current design and why do they arise (about � page)?
2. How would you change the current design and how does your new design address the problems you have identified with the current design.

In order to receive high marks for this question, you will need to demonstrate an understanding of the theories discussed in Topics 1, 2 and 3 and how they apply to this problem. Simply providing the amended design (even if it is correct) will only attract a small percentage of the marks for this question.

�Question 5: Conceptual Design (25 marks)

The Felicity Stillwell Hospital (FSH) is a small hospital located in the southern suburbs of Perth, specialising in the health care of elderly patients. As the first phase of a complete information systems redevelopment, you have been contracted to design the database that will support its data and processing requirements. Depending on how well you perform, you may be asked to implement the design at some later time.

Detailed information on the data recorded, maintained and accessed by hospital staff to support the management and operations of the hospital is described below (see FSH Detailed Requirements, FSH Processing Requirements and FSH Sample Forms and Reports below), including some of the paper forms and reports currently in use, and the main transaction requirements are summarised there. This description of data and processing should be the basis for your design.

What you have to do:

1. Create an entity-relationship diagram showing the data requirements of the system. Your ERD should be able to be implemented in a relational DBMS. You should use the ERD notation we have been using in the lectures, and should include a legend to explain the notation. You should include attributes in the ERD. The use of a drawing tool such as Visio will make this task easier. However, whichever tool you use, you must copy and paste the ERD into a word-processed document. This is because your tutor might not have access to the tools you have used. Please note that hand-drawn ERDs are not acceptable.
2. List and explain any assumptions you have made in creating the data model.

Some important things to note:

1. Part of understanding a system at sufficient enough detail to model well, involves asking appropriate questions. If you are not sure about some detail of the case study, you should ask on the Discussion Forum in LMS.
2. The University email server strips out any Visio (.vsd) files that are sent; even if they are included in a zip archive. So, if you want to send a draft of your design to your lecturer by email, you will need to change the extension to something other than .vsd (.blah works well) or paste the diagram into a word document.

Question 3: Further SQL (15 marks)

FSH Detailed Requirements

�

Wards

FSH has 17 wards with a total of 240 beds available for short and long-stay patients, and an outpatient clinic. Each ward is uniquely identified by a number (for example Ward 11) and also has a ward name (for example, Orthopaedic), location (E-Block), total number of beds, and telephone extension number (for example, Extn 7711).

Staff

FSH has a Medical Director, who has overall responsibility for the management of the hospital. The Medical Director maintains control over the use of the hospital resources (including staff, beds and supplies) in the provision of cost-effective treatment for all patients.

FSH has a Personnel Officer who is responsible for ensuring that the appropriate number and type of staff are allocated to each ward and the outpatient clinic.

The information stored on each member of staff includes a staff number, name (first and last), full address, telephone number, date of birth, gender, tax file number (TFN), position held, current salary, and salary scale. It also includes each staff member�s qualifications (which includes date of qualification, type, name of institution), and work experience details (which includes the name of the organisation, position and start and finish dates).

The type of employment contract for each member of staff is also recorded including the number of hours worked per week, whether the member of staff is on a permanent or temporary contract, and the type of salary payment (weekly/monthly).

An example of an FSH form used to record the details of a member of staff called Moira Samuel working in Ward 11 is shown in Figure 1 below.

Each ward and the outpatient clinic has a member of staff with the position of Charge Nurse. The Charge Nurse is responsible for overseeing the day-to-day operation of the ward/clinic. The Charge Nurse is allocated a budget to run the ward and must ensure that all resources (staff, beds, and supplies) are used effectively in the care of patients. The Medical Director works closely with the Charge Nurse to ensure the efficient running of the hospital.

A Charge Nurse is responsible for setting up a weekly staff roster, and must ensure that the ward/clinic has the correct number and type of staff on duty at any time during the day or night. In a given week, each member of staff is assigned to work an early, late or night shift.

As well as the Charge Nurse, each ward is allocated senior and junior nurses, doctors and auxiliaries. Specialist staff (for example, consultants, podiatrists) are allocated to several wards or the clinic.

Question 4: Normalisation (20 marks)

An example of an FSH report listing the details of the staff allocated to ward 11 is shown in Figure 2 below.

Patients

When a patient is first referred to the hospital he or she is allocated a unique patient number. At this time, additional details of the patient are also recorded including the name (first and last name), address, telephone number, date of birth, gender, marital status, date registered with the hospital, and the details of the patient�s next-of-kin.

Patient�s Next-of-Kin

The details of the patient�s next-of-kin are recorded, which includes the next-of-kin�s full name, relationship to the patient, address, and telephone number.

Local Doctors

Patients are normally referred to the hospital for treatment by their local doctor. The details of local doctors are held including their full name, provider number, address, and telephone number. The provider number is unique. An example of an FSH patient registration form used to record details of a patient called Anne Phelps is shown in Figure 3 below.

Patient Appointments

When a patient is referred by his/her doctor to attend FSH, the patient is given an appointment for an examination by a hospital consultant.

Each appointment is given a unique appointment number. The details of each patient�s appointment are recorded, and include the name and staff number of the consultant undertaking the examination, the date and time of the appointment and the examination room (for example, Room E252).

As a result of the examination, the patient is either recommended to attend the outpatient clinic or is placed on a waiting list until a bed can be found in a ward.

Outpatients

The details of outpatients are stored and include the patient number, name (first and last name), address, telephone number, date of birth, gender, and the date and time of the appointment at the Outpatient Clinic.

In-patients

The Charge Nurse and other senior medical staff are responsible for the allocation of beds to patients on the waiting list. The details of patients currently placed in a ward and those on the waiting list for a place on a ward are recorded. This includes the patient number, name (first and last name), address, telephone number, date of birth, gender, marital status, the details of the patient�s next-of-kin, the date placed on the waiting list, the ward required, expected duration of stay (in days), date placed in the ward, date expected to leave the ward, and the actual date the patient left the ward, when known.

When a patient enters the ward he or she is allocated a bed with a unique bed number. An example of an FSH report listing the details of patients allocated to Ward 11 is shown in Figure 4 below.

Surgical and Non-Surgical Supplies

FSH maintains a central stock of surgical (for example, syringes, sterile dressings) and non-surgical (for example, plastic bags, aprons) supplies. The details of surgical and non-surgical supplies include the item number and name, item description, quantity in stock, reorder level, and cost per unit. The item number uniquely identifies each type of surgical or non-surgical supply. The supplies used by each ward are monitored.

Ward Requisitions

When required, the Charge Nurse may obtain surgical, non-surgical, and pharmaceutical supplies from the central stock of supplies held by the hospital. This is achieved by ordering supplies for the ward using a requisition form. The information detailed on a requisition form includes a unique registration number, the name of the member of staff placing the requisition and the number and name of the ward. Also included is the item or drug number, name, description, dosage and method of administration (for drugs only), cost per unit, quantity required, and date ordered. When the requisitioned supplies are delivered to the ward, the form must be signed and dated by the Charge Nurse who initiated the order. An example of an FSH requisition form used to order supplies of morphine for ward 11 is shown in Figure 5 below.

Suppliers

The details of the suppliers of the surgical, non-surgical, and the pharmaceutical items are stored. This information includes the supplier�s name and number, address, telephone, and fax number. The supplier number is unique for each supplier.

FSH Processing Requirements

The following high-level transactions are undertaken to ensure that the appropriate information is available to enable the staff to manage and oversee the day-to-day running of FSH. Each transaction is associated with a specific function within the hospital. These functions are the responsibility of members of staff with particular job titles (positions). The main user or group of users of each transaction is given in brackets at the end of the description of each transaction. Your design should be able to support all of these transactions.

1. Create and maintain records recording the details of members of staff (Personnel Officer)
2. Search for staff who have particular qualifications or previous work experience (Personnel Officer)
3. Produce a report listing the details of staff allocated to each ward (Personnel Officer)
4. Create and maintain records recording the details of patients referred to the hospital (all staff)
5. Create and maintain records recording the details of patients referred to the out-patient clinic (Charge Nurse)
6. Produce a report listing the details of patients referred to the out-patient clinic (Charge Nurse and Medical Director)
7. Create and maintain records recording the details of patients referred to a particular ward (Charge Nurse)
8. Produce a report listing the details of patients currently located in a particular ward (Charge Nurse and Medical Director)
9. Produce a report listing the details of patients currently on the waiting list for a particular ward (Charge Nurse and Medical Director)
10. Create and maintain records recording the details of suppliers for the Hospital (Medical Director)
11. Create and maintain records detailing requisitions for supplies for particular wards (Charge Nurse)
12. Produce a report listing the details of supplies provided to specific wards (Charge Nurse and Medical Director)

Entity-Relationship (ER) modeling: This is a graphical representation of the data entities, their relationships, and attributes. ER models provide a clear and concise view of the data, enabling developers to design and create tables that accurately represent the data.

Normalization: This is the process of organizing the data in a database to reduce data redundancy and improve data integrity. Normalization involves dividing larger tables into smaller, more manageable tables that contain specific pieces of information. Normalization ensures that each piece of data is stored only once, thus reducing the risk of data inconsistencies and inaccuracies.

Primary keys: A primary key is a unique identifier for each row in a table. It ensures that each row can be uniquely identified and allows for easy data retrieval and manipulation. Foreign keys: A foreign key is a field in one table that refers to the primary key of another table. This relationship between tables allows for the creation of complex queries that retrieve data from multiple tables.

Indexing: Indexing is the process of creating a data structure that improves the speed and efficiency of data retrieval. It involves creating an index of the data based on specific fields, which allows the database to quickly search for and retrieve data.