Relational algebra and SQL Assignment
Q1. Given the following table schemata:
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.
(a) List the name and country of all athletes.
(b) List the event name and scheduled start time for all events held in the Velodrome.
(c) List the names of athletes who competed in an event in Rio de Janeiro, Sao Paolo, or both.
(d) List the names of all Brazilian athletes who won a gold medal.
(e) List full details of the events that were held in venues with a capacity of over 50,000.
(f) List the name and location of all venues, and the events that were scheduled to be held in them (if any).
(g) List the names of Australian athletes who won a gold medal in an event held in the Aquatics Stadium.
(h) List the name of any athlete who was placed 1 in both the Men's 100m and 200m.
(i) List the names of the athletes who did NOT win a medal in the Women's 1500m Freestyle.
(j) List the name of any athletes who won a gold medal in all the events he or she competed in.
2. Provide SQL queries for the View Ridge Gallery database, which includes the following tables:
3. Based on the table specifications provided, answer the following questions. Each question is worth 3 marks.
(a) Give the SQL to create the VENUE table. Choose appropriate data types. None of the attributes should be allowed to be null. Include the primary key constraint.
(b) 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.
(c) Give the SQL to add the Maracana Stadium to the VENUE table. The stadium is located in Avenida Maracana and has a capacity of 78,838.
(d) Give the SQL to add an attribute Sport to EVENT. (Possible values include Athletics, Swimming, Tennis, etc.).
(e) Give the SQL to record the fact that the Maracana Stadium now has capacity 80,000.
4. The following information is captured in the PROJECTS relation:
|
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.
|
(a) Explain the problems with the existing design, in terms of the potential modification anomalies that it might exhibit.
(b) What normal form is the relation currently in? Explain your reasoning.
(c) 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.
(d) Explain how your new design addresses the problems you identified in (a) and preserves all the information in the original design.
5. 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.
To make the diagram easier to comprehend, let us first reorder these table schematas in a logical fashion, so that-in any instance where a master-detail relationship need be constructed-the master table is always defined before the detail table. Also, we see that we are able to combine ORDERS and DELIV_STATS: the ACTUAL_DATE_TIME column is merely added to ORDERS as an additional residual attribute-initially left NULL-and updated upon completion of the delivery.
The entity-relationship (E-R) diagram follows. Note that, to improve readability, those residual attributes that are not foreign keys referencing other relations are rendered in lower case only. Although the crow's feet notation was requested, a slight modification is effected: specifically, rather than one table merely pointing to another table, the actual foreign key of one table is connected to the corresponding primary key(s) of its associated table(s). This is done for the sake of crystalline clarity, i.e., to make the relationships between individual pairs of related tables abundantly clear.