ICT285 : Database Queries - IT Assignment

Internal Code - MAS1458

IT Assignment

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 : 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. 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 god 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. Question 2: 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. a. List the details of any work of art (including the name of the artist who created the work) that are signed. b. List all the nationalities with more than one artist represented in the database, and the number of artists of that nationality. c. List the number of works in each medium, ordered from highest to lowest number. d. List the names of all the customers and the names of the artists each customer has an interest in, in alphabetical order of artist last name within customer last name. e. List the full name and email of any customers who have no address recorded. f. List the work ID, title and artist name of all the works of art that sold for more than the average sales price, and the price they sold for. g. List the full name of any customers who haven’t bought any works of art. h. Which artist (give his/her full name) has the most customers interested in him or her, and how many customers are interested in them? i. List the total dollar amount of sales each artist (give his/her full name) has made on their works, in descending order of total. j. List the name of any customers who have an interest in all the artists from the United States. Question 3 : 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. 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.