Reference no: EM131251341
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.
|
Where are there still some areas in which there more to do
: Think about the many ways that the United States has made tremendous progress in realizing equality for all Americans. Where are there still some areas in which there is more to do? What conclusions have you reached while researching your Final Pr..
|
|
Denote the granular noise and slope overload regions
: The step size of the DM is 1 V, and the sampler operates at 10 samples/s. Sketch the input waveform, the delta modulator output, and the integrator output over a time interval of 0 to 2 s. Denote the granular noise and slope overload regions.
|
|
Discuss the performance of the system under these conditions
: Select the appropriate step size and sampling rate for a sine-wave test signal, and discuss the performance of the system, using the parameter values you have selected.
|
|
Calculate the additional tax and initial tax
: Salmon, Inc., a private foundation, has existed for 8 years. During this period, Salmon has been unable to satisfy the requirements for classification as a private operating foundation. At the end of 2013, it has undistributed income of $350,000. Of ..
|
|
List the name and country of all athletes
: List the name and country of all athletes. List the event name and scheduled start time for all events held in the Velodrome. List the names of athletes who competed in an event in Rio de Janeiro, Sao Paolo, or both.
|
|
Determine the minimum sampling frequency
: Determine the minimum sampling frequency for each signal, and design a TDM commutator and decommutator to accommodate the signals.
|
|
Discuss careers and personalities of malcolm bricklin
: Briefly discuss the careers and personalities of Malcolm Bricklin and John DeLorean. What was vision of each of these men, and how realistic was it? Specifically how and why did government (In the case of Bricklin, New Brunswick, Canada, and for D..
|
|
Determine the minimum sampling frequency for each signal
: Determine the minimum sampling frequency for each signal, and design a TDM commutator and decommutator to accommodate these signals.
|
|
Compute the mean and standard deviation and variance
: Create a practice data file that contains the following variables and values: Compute the mean and standard deviation and variance and print out on a single page
|