ICT285: Databases - Relational Algebra - SQL - IT Assessment Answer

IT Assignment

Assignment Task

Question 1: Relational algebra

A database records information about athletes competing at the Olympics. An athlete competes for a particular country in one or more events. Events take place at a scheduled day and time in a particular venue. The result is recorded for all athletes in the final of the event. The medal (gold, silver or bronze) is also recorded for the medal winners in the event.

Note that we are not considering team sports or heats in this example – only individuals competing in the finals.

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

ATHLETE (AthleteNo, AthleteName, CountryName) COUNTRY (CountryName, NumberOfCompetitors) EVENT (EventName, ScheduledStart, VenueName) VENUE (VenueName, Location, Capacity) FINAL (AthleteNo, EventName, Place, Medal)

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

1. List the name and country of all athletes.
2. List the event name and scheduled start time for all events held in the Velodrome.
3. List the names of athletes who competed in an event in Rio de Janeiro, Sao Paolo, or both.
4. List the names of all Brazilian athletes who won a gold medal.
5. List full details of the events that were held in venues with a capacity of over 50,000.
6. List the name and location of all venues, and the events that were scheduled to be held in them
7. List the names of Australian athletes who won a god medal in an event held in the AquaticsStadium.
8. List the name of any athlete who was placed 1 in both the Men’s 100m and 200m.
9. List the names of the athletes who did NOT win a medal in the Women's 1500m Freestyle.
10. List the name of any athletes who won a gold medal in all the events he or she competed in.

Question 2: SQL – SELECT queries

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 as in dtoohey’s 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. Each question is worth 2 marks.

1. List the details of any work of art (including the name of the artist who created the work)that are signed.
2. List all the nationalities with more than one artist represented in the database, and thenumber of artists of that nationality.
3. List the number of works in each medium, ordered from highest to lowest number.
4. List the names of all the customers and the names of the artists each customer has aninterest in, in alphabetical order of artist last name within customer last name.
5. List the full name and email of any customers who have no address recorded.
6. List the work ID, title and artist name of all the works of art that sold for more than theaverage sales price, and the price they sold for.
7. List the full name of any customers who haven’t bought any works of art.
8. Which artist (give his/her full name) has the most customers interested in him or her, andhow many customers are interested in them?
9. List the total dollar amount of sales each artist (give his/her full name) has made on theirworks, in descending order of total.
10. List the name of any customers who have an interest in all the artists from the United States.

Question 3: Further SQL

You have been given the following specifications of a simple database for keeping track of venues and events at the Olympics (note that primary keys are shown underlined, foreign keys in bold).

You should run your SQL to demonstrate that it works correctly.

VENUE (VenueName, Location, Capacity)

EVENT (EventName, ScheduledStart, VenueName)

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

1. Give the SQL to create the VENUE table. Choose appropriate data types. None of the attributesshould be allowed to be null. Include the primary key constraint.
2. Give the SQL to create the EVENT table. Use appropriate data types, and include the primary key and foreign key constraints. Referential integrity should be set such that a venue may not be deleted from the database if there is an event recorded in it.
3. Give the SQL to add the Maracana Stadium to the VENUE table. The stadium is located inAvenida Maracana and has a capacity of 78,838.
4. Give the SQL to add an attribute Sport to EVENT. (Possible values include Athletics, Swimming,Tennis, etc)
5. Give the SQL to record the fact that the Maracana Stadium now has capacity 80,000.

Question 4: Normalisation

The following question is based upon the PROJECTS relation below that lists details of team projects in a unit similar to ICT302 IT Project. You can assume that the data is representative. The unit runs once a year. Team names are always unique, but projects can be repeated (if the team one year didn’t do a very good job, or the client needs it extending).

Year: The year the project is run StudentNo: Unique student number StudentName: The full name of the student Team: A name the team chooses for themselves FirstMajor: The first (or only) major of the student Project: A descriptive name of the project BroadArea: The general IT area the project is in Client: Name of the person/organisation who is client for the project Supervisor: Name of the staff member who supervises the team Grade: The final grade awarded to the student. It is possible for members of a team to receive different grades.

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 modificationanomalies that it might exhibit.
2. 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 needto show the schema, not the data.
4. Explain how your new design addresses the problems you identified in (a) and preserves allthe information in the original design.

Question 5: Conceptual Design

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

Bill is a university student who has been picking up meals from certain restaurants for his family on the way home from uni for the last two years, and has now hit upon the idea of making it into of business, which he is calling FineFoods4U. He intends to make available the menus of all the local restaurants for delivery to the homes in his neighbourhood, and has recruited several members of his family and some of his closer friends to make the deliveries. If the trial run is successful, he plans to make it into an Uber-style business (similar to UberEATS and Deliveroo).

Bill has phoned around the higher rated ethnic restaurants in his suburb, and has made a list of 20 who are willing to co-operate with him. Each restaurant has selected a subset of the items on their menus that they think will last the journey from their kitchen to the customer. They have decided that no high-cuisine meal can last more than 10 minutes in a heated container, so that has limited Bill’s area of service – customers can only be from the same suburb as the restaurants.

Customers will order their food via a webpage, after registering on the site. They can register from any location, but won’t be able to use the site unless the delivery address is in the same suburb as the restaurants. They can select a restaurant and choose a number of dishes from it, referring to the information available on the web page. They can also select dishes directly, by searching on particular requirements such as ‘vegetarian’ or ‘pizza’. However, they can only order from one restaurant per delivery.

Once they have selected their dishes, the customer enters the delivery date, time and address required, and pays the cost of the meal plus delivery via PayPal. The information about the order is sent to the restaurant and also to Bill, who assigns a driver who is currently free to pick up and deliver the order. The driver collects the meal from the restaurant and delivers to the customer. The driver records the actual date and time delivered, as Bill needs to keep track of whether he can live up to his promise to deliver on time.

Bill has heard that you are studying Databases and has asked you to design a database to keep track of the information requirements of his business. He wants the database to record information about customers, restaurants, dishes, drivers, and of course order s and deliveries. At this stage he does not want you to model any of the financial side of the business.

He wants to record various items of information about each restaurant, including its ethnicity (Malay, Indian, Chinese, French, Italian, Australian...) and predominant style (BBQ, formal, pub grub, noodle house, open spit, dim sum, fast food ...). A brief description of each restaurant (‘About Us’) is to be included, as well as a general description of their food. He also wants to record any special certifications the restaurant as a whole has (e.g. vegan, locavore, organic, nut free, Jain, Halal, Kosher).

The dishes at each restaurant also need to have enough information stored about them so that the customers know what they are selecting. As well as name and brief description, customers are likely to want to know how in general terms how the dish was prepared (fried, steamed, raw etc), its main ingredient (fish, cheese...), what type of course it is (soup, starter, main, dessert, side dish) and of course its price. As customers are increasingly aware of health issues, Bill also wants to record the number of kilojoules in each dish, and also whether it is gluten free, dairy free, and/or vegetarian, and possibly other nutritional aspects of the dish in the future.

Customers also need some indication of how long the dish will take to arrive: Bill guarantees 10 minutes delivery from when the dish is picked up, but obviously some dishes take longer than others to prepare. He has a rough categorisation of ‘fast’ (under 15 minutes, including delivery), ‘regular’ (15 minutes to half an hour), and ‘worth the wait’ (over half an hour) total time to door for each dish.

Although all the actual ordering will be done through the website, Bill wants to print a booklet for each restaurant, so they can have it available to their in-house customers for advertising.

Below are several queries and reports that Bill has requested the database must be able to support. There may well be many others as Bill analyses his business and plans for the future; therefore, you should design for flexibility.

The database will have to support at least the following querying and reporting requirements:

1. All the details of an order for a particular customer. The driver needs this to pick up the dishesfrom the restaurant, and to confirm with the customer on delivery.
2. All the vegetarian dishes that can be delivered to the customer in less than half an hour.
3. The details of the orders for a particular restaurant on a particular date.
4. A list of all the vegan restaurants and the names, description and prices of the dishes they offer.
5. List of all drivers, and the customers (if any) they delivered to on a particular date.
6. List of drivers who are currently free (i.e. not out on a delivery).
7. The total number of orders for each restaurant so far.
8. The booklet which lists the dishes available from a particular restaurant, with their names,descriptions, course type, prices and delivery time.

What you have to do:

• Use the case study description and querying requirements to create an entity-relationship diagram (ERD) for the FineFoods4U 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 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 at this point. However, Assignment 2 will involve creating the database from your design, so you should be satisfied that it will work.
• 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.
• Part of understanding a system at sufficient enough detail to model well involves asking questions. If you are not sure about some detail of the case study, you should ask on the Discussion Forum in LMS. You can subscribe to the discussion forums so that you don’t miss any messages.

Queston 1:

1. ?AtheleteName, CountryName(Athlete)
2. ?EventName, ScheduledStart(?VenueName=’Velodrone’(Event))
3. ?AthleteName((?AthleteNo(?VenueName=’Rio de Janerio’ V VenueName=’Sao Paolo’ (Event?Final)))?(Athlete))
4. ?AthleteName(?CountryName=’Brazil’ ? Medal=’Gold’ (Athlete?Final))
5. ?EventName,ScheduledStart,VenueName,Location(?Capacity>50000(Event?Venue))
6. ?VenueName,EventName,Location(Venue?Event)
7. ?AthleteName(?CountryName=’Australia’ ? Medal=’Gold’ ?VenueName=’Aquatcs’ (Athlete?(Event?Final)))
8. t1 ??AthleteNo(?EventName=’Men 100m’ ? Place=1(Final))

t2 ? ?AthleteNo(?EventName=’Men 200m’ ? Place=1(Final))

?AthleteName(Athlete?(t1?t2))

?AthleteName(?EventName=’Women 1500m Freestyle’ ? Medal=NULL(Athlete? Final))

?AthleteName,AthleteNo,EventName(?Medal=’Gold’(Athlete? Final)) ÷

?AthleteNo,EventName(Final))

Question 2:

select firstname,lastname,title,medium from artist a, work w where a.artistid=w.artistid and copy='signed';

FIRSTNAME LASTNAME TITLE MEDIUM

---------- ---------- ------------------------------ --------

Thomas Hardy Crowd English

select Nationality, count(ArtistID) NoOfArtist from artist group by Nationality having count(ArtistID)>1;

NATIONALITY NOOFARTIST

-------------------------------------------------- ----------

United Kingdom 2

select Medium, count(WorkID) NoOfWork from work group by Medium order by NoOfWork;

MEDIUM NOOFWORK

-------- --------

select C.FirstName CustomerFirstName, C.LastName CustomerLastName, A.FirstName ArtistFirstName, A.LastName ArtistLastName from Customer_artist_int CA, Customer C, Artist A where CA.CustomerID=C.CustomerID and CA.ArtistID=A.ArtistID order by 2,4;

CUSTO CUSTOMER ARTISTFIRS ARTISTLASTNAME

----- -------- ---------- ---------------

sam junor Patricia Aakhus

sam junor Thomas Hardy

jia samuels Patricia Aakhus

jia samuels charles Darwin

jia samuels Alexander Solzhenitsyn

select FirstName||' '||LastName FullName, Email from Customer where state=NULL;

FULLNAME EMAIL

------------------------------ ------------------------

jia samuels jia@gmail.com

select T.WorkID, W.Title, A.FirstName, A.LastName from Work W, Artist A, Trans T where T.WorkID=W.WorkID and W.ArtistID=A.ArtistID and SalesPrice > ( select Avg(SalesPrice) from trans);

WORKID TITLE FIRSTNAME LASTNAME

---------- ---------------------------------------- ---------- ---------------

2 The Life Alexander Solzhenitsyn

4 The Voyage Patricia Aakhus

select FirstName||' '||LastName FullName from customer where customerID not in (select customerID from Trans);

FULLNAME

------------------------------

Julie Strom

select FirstName||' '||LastName FullName,B.NoOfCustomer from Artist A, (select ArtistID, count(*) NoOfCustomer from Customer_artist_int group by ArtistID having count(*)=(select max(No) from (select count(*) No from Customer_artist_int group by ArtistID))) B where A.ArtistID=B.ArtistID;

FULLNAME NOOFCUSTOMER

---------------------------------------- ------------

Patricia Aakhus 2

1. select FirstName||' '||LastName FullName, Sales from Artist A, (select ArtistID, sum(SalesPrice) Sales from Trans T, Work W where T.WorkID=W.WorkID group by ArtistID) B where A.ArtistID=B.ArtistID order by Sales;

FULLNAME SALES

---------------------------------------- ----------

Alexander Solzhenitsyn 29

Patricia Aakhus 30

Thomas Hardy 46

select FirstName||''||LastName FullName from customer

where CustomerId not exist(select CustomerId

from Customer_artist_int a

where artistid in (select artistid

from artist where nationality='United States')

and not exist(select *from customer_artist_int c

where a.artistid=c.artistid));

FULLNAME

Alex Aakhus

Question 3:

create table VENUE (VenueName varchar2(50) primay key,

Location varchar2(50) not null,

Capacity number(10) not null

create table EVENT (EventName varchar2(50) primary key,

ScheduleStartdatetime,

VenueName varchar2(50) foreign key references VENUE(VenueName)

1. insert into VENUE values ('Maracana','Avenida Maracana',78838);
2. alter table EVENT add (Sport varchar2(50));
3. update VENUE set Capacity=80000 where VenueName='Maracana';

Question 4:

It exhibits below anomalies:

Update anomaly: for example, the supervisor name is repetedeverytime the team name is there. Now if the supervisor against a particular team changes then it must be updated in every row where that team appears. If even a single row is missed then the data will become incosistent.

Delete anomaly: if for some reason a project is terminated in the middle of the year then on deleting the rows containing that project, details of the students related to it will also be deleted.

The table is in first normal form as each attribute of the table have atomic (single) values. However, it is not in 2NF because non prime attributes (Year, StudentName, FirstMajor, BroadArea, Supervisor, Grade) are dependent only on the part of candidate key (Team, StudentNo, Project). This violates the rule for 2NF as the rule says “no non-prime attribute is dependent on the proper subset of any candidate key of the table”.

3NF:

Student (StudentNo(PK), StudentName, Major)

Project (Project(PK), BroadArea, Client)

Team (Team(PK), Year, Supervisor)

StudentTeam (StudentNo(PK,FK), Team(PK,FK), Grade)

TeamProject (Team (PK,FK), Project(FK))

In the new design all the below anomalies are overcome:

update anomaly: for example now if a supervisor for a particular project changes then it needs to be changed at only one place in only one table.

Delete anomaly: if a project is terminated then when it is removed from Project table then it will be automatically removed from TeamProject table also if 'Cascade on delete' is defined in the foreign key but the student information will not be deleted.

It also preserves all the information of student, project and team. Also the consolidated detail of student, team and project can be found by joining one or more tables.

Question 5:

ER DIAGRAM: Please find the below ER diagram which fullfill all the requirment