ICT285 – Databases, Assignment 1 MU

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, with description as necessary to find the following information. Each question is worth 2 marks.

NOTE:

You can use the symbols  s, P, 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.
Provide a description to explain how the query will run.

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.
List the names of all Brazilian athletes who won a gold medal.
List full details of the events that were held in venues with a capacity of over 50,000.
List the name and location of all venues, and the events that were scheduled to be held in them (if any).
List the names of Australian athletes who won a gold medal in an event held in the Aquatics Stadium.
List the name of any athlete who was placed 1 in both the Men’s 100m and 200m.
List the names of the athletes who did NOT win a medal in the Women’s 1500m Freestyle.
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 

Provide SQL queries and the result tables for the following (20 marks):

Please ensure that you include the result table as well as your SQL; you can copy and paste this from either your ssh client or SQL Developer. Each query is worth 2 marks. These tables exist in Rhea and are owned by the user “tutorials”. You may, if you wish, create your own copies of the tables under your own account. If you do so, you should ensure that you copy the sample data from tutorials’ tables.

These queries are based on the View Ridge Gallery database you have been using in the Lab sessions. Please see Chapters 6 and 7 of Kroenke ford background to the case and table structures.

Marks are allocated not only for correct answers, but also for best practice in the creation of the queries. You should also include a description along with each query to explain how it will run.  

List the details of any work of art (including the name of the artist who created the work) that has ‘Signed’ in their description.
List all the nationalities with more than one artist represented in the database, and the number of artists of that nationality.
List the number of works in each medium, ordered from highest to lowest number.
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.
List the full name and email of any customers who have no address recorded.
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.
List the full name of any customers who haven’t bought any works of art.
Which artist (give his/her full name) has the most customers interested in him or her, and how many customers are interested in them?
List the total dollar amount of sales each artist (give his/her full name) has made on their works, in descending order of total.
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.

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.
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.
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.
Give the SQL to add an attribute Sport to EVENT. (Possible values include Athletics, Swimming, Tennis, etc).
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 a Car-Service relation which records the details of transactions occurring in an Automotive Mechanic and Repair business. You may assume the data are representative.

You have been asked to design a relational database for this system. You know that there are problems with the current design and that it will need to be modified in order to work effectively.

You need to write a 1–2-page report that addresses the following:

What are the specific problems associated with the current design and why do they arise?
How would you change the current design and how does your new design address the problems you have identified with the current design.

In order to receive high marks for this question, you will need to demonstrate an understanding of the theories discussed in Topics 1, 2 and 3, how they apply to this problem, and justify the changes you are making to the system. Simply providing the amended design (even if it is correct) will only attract a small percentage of the marks for this question.

Question 5: Conceptual Design

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 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 orders 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 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:

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

The post ICT285 – Databases, Assignment 1 MU appeared first on Singapore Assignment Help.