Relational Algebra, SQL and database design | Get complete solution now

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 (rank) 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, City, Capacity)
FINAL (AthleteNo, EventName, Rank, Medal)

Provide relational algebra (NOT SQL) queries to find the following informaList the name and country of all athletes.
  1. List the event name and scheduled start time for all events held in the Aquatics Stadium.
  2. List the names of athletes who competed in an event in Rio de Janeiro, Brasilia, or both.
  3. List the names of all Brazilian athletes who won a gold medal.
  4. List full details of the events that were held in venues with a capacity of over 60,000.
  5. List the name and city of all venues and the events that were scheduled to be held in them (if any)
  6. List the names of Australian athletes who won a gold medal in an event held in the Velodrome.
  7. List the name of any athlete who was ranked 1 in both the Men’s 200m and 400m.
  8. List the names of the athletes who did NOT win a medal in the Women's 1500m Freestyle.
  9. List the name of any athletes who won a gold medal in all the events he or she competed in.

SQL – SELECT queries

This question is based on the View Ridge Gallery database you have been using in the labs. See the textbook for the 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.
  1. List the details of all works 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 the number 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 an interest in, in alphabetical order of artist last name within customer last name.
  5. List the full name and email of any customers who do not have a complete address recorded.
  6. 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.
  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, and how many customers are interested in them?
  9. List the total dollar amount of sales each artist (give his/her full name) has made on their works, in descending order of total.
  10. List the name of any customers who have an interest in all the artists from the United States. Your query should not need to include names of individual artists.

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, and paste the queries into your assignment document.

VENUE (VenueName, Location, Capacity)
EVENT (EventName, ScheduledStart, VenueName)

Based on the table specifications provided, answer the following questions.
  1. 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.
  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 in Avenida 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, and many others.)
  5. Give the SQL to record the fact that the Maracana Stadium now has capacity 90,000.

Normalization

The following question is based upon the PROJECTS relation below that lists details of team projects in a unit similar to ICT333 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.
  1. Explain the problems with the existing design, in terms of the potential modification anomalies 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 need to show the schema, not the data.
  4. Explain how your new design addresses the problems you identified in (a) and preserves all the information in the original design.

Conceptual Design

Use the case study description and list of requirements below to create an entity-relationship diagram showing the data requirements of the FastFoods4U 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 a business, which he is calling FastFoods4U. 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 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 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 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, vegan, 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 from order 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 dishes from 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.

Get Project Solution Now

Comments

Post a Comment