Questions on relational database principles and SQL

Assignment 1

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.

Question 1: Relational algebra

A national ICT conference has a database that records information about the various workshops that are available for attendees at the conference. Each workshop is on one of several possible themes, is run by a presenter and has a limited number of places available for participants. Information on all conference attendees and presenters is also recorded.

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

ATTENDEE (AttendeeNo, AttendeeName, InstitutionName)

INSTITUTION (InstitutionName, State)

PRESENTER (PresenterName, Biography, InstitutionName)

WORKSHOP (WorkshopNo,WorkshopName, Theme, Description, Capacity, DateAndTime,

PresenterName)

PARTICIPANT (AttendeeNo, WorkshopNo)

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

NOTE:

• You can use the symbols π ,α , 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 institution of all presenters.
2. List the workshop name and capacity for all workshops on the theme of ‘Virtual Reality’.
3. List the names of attendees who attended a workshop on the theme of ‘Virtual Reality’, ‘Big Data’, or both.
4. List the names of all attendees who attended a workshop run by a presenter from an institution in Queensland.
5. List the names of attendees from Murdoch University who went to a workshop on the theme of ‘Green IT’ run by a presenter from an institution in Tasmania.
6. List the workshop number, name and theme of all workshops, and the names of their attendees, if any.
7. List the name of any attendees who went to both the workshop named ‘Introduction to Oracle’ and the workshop ‘Normalisation 101’.
8. List the name, biography and institution of all presenters from an institution in South Australia.
9. List the names of the conference attendees who did NOT participate in the workshop named ‘Relational Algebra Masterclass’.
10. List the names of attendees who went to all workshops.

Question 2: Further SQL

You have been given the following specifications of a simple database for keeping track of workshops and their presenters at a national ICT conference (note that primary keys are shown underlined, foreign keys in bold).

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)

WORKSHOP (WorkshopNo, WorkshopName, 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 WORKSHOP 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 workshops 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 WORKSHOP. 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 workshops have had their capacity increased by 10.

Question 3: Normalisation

The following question is based upon the MAJORS relation below that lists details of the four ‘Majors’ golf tournaments and their winners over the last few years. You can assume that the data is representative.

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.

Answer the following questions. Each question is worth 5 marks

1. Explain the problems with the existing design, in terms of the potential modification anomalies that it might exhibit.
2. What is the candidate key(s) of the relation? What normal form is the relation currently in? Explain your reasoning.
3. Convert the relation to a set of relations in at least Third Normal Form (3NF). You only need to show the schema, not the data. Do not create any new attributes. Give each of your new relations an appropriate name. Show all primary keys and foreign keys.
4. Explain how your new design addresses the problems you identified in (a) and preserves all the information in the original design.

MAJORS

Tournament	Established	Year	Date	Course	Location*	Prize money	Winner	Winner country
The US Masters	1934	2016	April 6-10	Augusta National Golf Club	Augusta, Georgia	$1,800,000	Danny Willett	England
The US Masters	1934	2017	April 6-9	Augusta National Golf Club	Augusta, Georgia	$1,980,000	Sergio Garcia	Spain
The US Masters	1934	2018	April 5-8	Augusta National Golf Club	Augusta, Georgia	$2,050,000	null	null
The US Open	1895	2016	June 16-19	Oakmont Country Club	Oakmont, Pennsylvania	$1,800,000	Dustin Johnson	USA
The US Open	1895	2017	June 15-18	Erin Hills Golf Club	Erin, Wisconsin	$2,160,000	Brooks Koepka	USA
The US Open	1895	2018	June 14-17	Shinnecock Hills Golf Club	Southampton, New York	$2,200,000	null	null
The Open	1860	2016	July 14-17	Royal Troon Golf Club	Troon, Scotland	$1,549,590	Henrik Stenson	Sweden
The Open	1860	2017	July 20-23	Royal Birkdale Golf Club	Southport, England	$1,845,000	Jordan Spieth	USA
The Open	1860	2018	July 20-23	Carnoustie Golf Links	Carnoustie, Scotland	$1,980,000	null	null
The PGA Championship	1916	2016	July 28-31	Baltusrol Golf Club	Springfield, New York	$1,800,000	Dustin Johnson	USA
The PGA Championship	1916	2017	August 10-13	Quail Hollow Club	Charlotte, North Carolina	$1,890,000	Justin Thomas	USA
The PGA Championship	1916	2018	August 9-12	Bellerive Country Club	St. Louis, Missouri	$1,890,000	null	null

* Treat Location as a single string – do not try to split it.

Question 4: Conceptual Design

Use the case study description and list of requirements below to create an entity-relationship diagram showing the data requirements of the Pet Net database. Your ERD should be able to be implemented in a relational DBMS.

Pet Net is a new business venture by animal lovers Elizabeth and Jeremy Trellis, providing in-home pet boarding for pet owners while they are on holiday. Its motto is “Your Friend Our Friend”. Pet Net’s business model is about bringing together pet owners looking for a suitable sitter, with sitters who offer a particular service depending on their circumstances. The Pet Net database provides the opportunity to match the needs of the particular pet with an appropriate pet sitter. For example, a large dog that needs lots of exercise would be paired with a sitter who can take it for long walks, while a pet rat in a cage could be cared for in a small apartment.

Pet Net aims to provide care in either the pet’s own home, or hosted in the pet sitter’s home, depending on what the pet owner is looking for.

Potential pet sitters register on the Pet Net database by providing their name, suburb, email address and phone number. They write a short profile describing themselves and the services they provide that can be used for display on the website. They also answer a number of standard questions including whether they provide pet sitting at the pet’s home or hosting in their own home (or both), and which animals they are prepared to look after: dogs (small, medium, large), cats, small animals (such as rabbits, rats, mice, etc), fish, or birds. Pet sitters are in competition with each other for opportunities and can set their own prices (for example, $25 per night for pet hosting; $30 per night for in-home sitting). Sitters must provide various identification documents to Pet Net and once these are verified they are assigned a Sitter ID and can commence operating.

Pet owners register on the Pet Net database by providing their name, suburb, email address and phone number, and are assigned an Owner ID. They provide details of the animal (or animals) they are seeking accommodation for, and whether they are looking for an in-home sitter, or hosting at the sitter’s home. They also provide the dates they need the service for, and any special requirements to be discussed with potential sitters.

Once a pet owner has located a suitable sitter through the Pet Net database, they contact the sitter and arrange to meet. If this is successful, a booking is then arranged between sitter and owner. The booking information also includes the dates of the stay, the pet (or list of pets), details of the service, and agreed price. Once the booking is paid for (NOT handled by the Pet Net database) the booking is confirmed in the database.

After the pet stay is completed, the owner may choose to provide a star rating and comments on their experience. This feedback is used in subsequent Pet Net searches: for example, an owner may search for all the five star rated sitters in their suburb. Pet Net may also remove a sitter from the database if they have verified poor reviews.

Obviously, a pet owner is likely to need Pet Net’s boarding services on more than one occasion, and may need it for different pets each time. Similarly, a sitter’s availability and services may change and they need to keep this updated in the database.

Below are several queries and reports that the Pet Net database must be able to support. There may well be many others as Elizabeth and Jeremy analyse their business and plans for the future; therefore, you should design for flexibility as well as ensuring your ERD can answer these questions.

1. All the pet sitters who can look after large dogs in the dog’s own home.
2. All the pet sitters who provide hosting services in a particular suburb.
3. Pet sitters who are available for cats during March 2018.
4. All sitters rated as four stars or over.
5. All comments on bookings for a particular sitter.
6. Sitters who can look after both dogs and cats.
7. Any sitter registered at an address of a previously disqualified sitter.
8. The number of pet sitters available for each type of animal.
9. The average price charged by pet sitters.
10. All of the bookings for a particular owner.

What you have to do:

• Use the case study description and querying requirements to create an entity-relationship diagram (ERD) for the Pet Net database. Your ERD should be able to be implemented in a relational DBMS.
• List and explain any assumptions you have made in creating the data model.
• You should use the crow’s feet 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, and indicate primary and foreign keys. The use of a drawing tool such as Visio will make this task easier.
• 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 make sure the labels and symbols in your ERD are readable. Enlarge them from the default if necessary.
• Please note that hand-drawn ERDs are not acceptable.

Some important things to note:

• You don’t have to create the database or any of the reports/queries at this point. However, Assignment 2 will involve creating the database from your design, so you should be satisfied that it will work with at least the queries shown.
• You should make any assumptions that are required, but must state them clearly. Obviously, your assumptions should not contradict any of the information already provided.