IT Assessment Answer
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 to find the following information. Each question is worth 2 marks.
Question 2: SQL – SELECT queries
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. Each question is worth 2 marks.
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.
Question 4: Normalisation
The following question is based upon the PROJECTS relation below that lists details of team projects in a unit similar to ICT302 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. Each question is worth 5 marks
Question 5: Conceptual Design
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.
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 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 order s 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 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:
What you have to do:
Some important things to note:
t2 ← πAthleteNo(σEventName=’Men 200m’ Ʌ Place=1(Final))
πAthleteName(σEventName=’Women 1500m Freestyle’ Ʌ Medal=NULL(Athlete⋈ Final))
πAthleteName,AthleteNo,EventName(σMedal=’Gold’(Athlete⋈ Final)) ÷
select firstname,lastname,title,medium from artist a, work w where a.artistid=w.artistid and copy=’signed’;
FIRSTNAME LASTNAME TITLE MEDIUM
———- ———- —————————— ——–
Thomas Hardy Crowd English
select Nationality, count(ArtistID) NoOfArtist from artist group by Nationality having count(ArtistID)>1;
United Kingdom 2
select Medium, count(WorkID) NoOfWork from work group by Medium order by NoOfWork;
select C.FirstName CustomerFirstName, C.LastName CustomerLastName, A.FirstName ArtistFirstName, A.LastName ArtistLastName from Customer_artist_int CA, Customer C, Artist A where CA.CustomerID=C.CustomerID and CA.ArtistID=A.ArtistID order by 2,4;
CUSTO CUSTOMER ARTISTFIRS ARTISTLASTNAME
—– ——– ———- —————
sam junor Patricia Aakhus
sam junor Thomas Hardy
jia samuels Patricia Aakhus
jia samuels charles Darwin
jia samuels Alexander Solzhenitsyn
select FirstName||’ ‘||LastName FullName, Email from Customer where state=NULL;
jia samuels email@example.com
select T.WorkID, W.Title, A.FirstName, A.LastName from Work W, Artist A, Trans T where T.WorkID=W.WorkID and W.ArtistID=A.ArtistID and SalesPrice > ( select Avg(SalesPrice) from trans);
WORKID TITLE FIRSTNAME LASTNAME
———- —————————————- ———- —————
2 The Life Alexander Solzhenitsyn
4 The Voyage Patricia Aakhus
select FirstName||’ ‘||LastName FullName from customer where customerID not in (select customerID from Trans);
select FirstName||’ ‘||LastName FullName,B.NoOfCustomer from Artist A, (select ArtistID, count(*) NoOfCustomer from Customer_artist_int group by ArtistID having count(*)=(select max(No) from (select count(*) No from Customer_artist_int group by ArtistID))) B where A.ArtistID=B.ArtistID;
Patricia Aakhus 2
Alexander Solzhenitsyn 29
Patricia Aakhus 30
Thomas Hardy 46
select FirstName||”||LastName FullName from customer
where CustomerId not exist(select CustomerId
from Customer_artist_int a
where artistid in (select artistid
from artist where nationality=’United States’)
and not exist(select *from customer_artist_int c
create table VENUE (VenueName varchar2(50) primay key,
Location varchar2(50) not null,
Capacity number(10) not null
create table EVENT (EventName varchar2(50) primary key,
VenueName varchar2(50) foreign key references VENUE(VenueName)
It exhibits below anomalies:
Update anomaly: for example, the supervisor name is repetedeverytime the team name is there. Now if the supervisor against a particular team changes then it must be updated in every row where that team appears. If even a single row is missed then the data will become incosistent.
Delete anomaly: if for some reason a project is terminated in the middle of the year then on deleting the rows containing that project, details of the students related to it will also be deleted.
The table is in first normal form as each attribute of the table have atomic (single) values. However, it is not in 2NF because non prime attributes (Year, StudentName, FirstMajor, BroadArea, Supervisor, Grade) are dependent only on the part of candidate key (Team, StudentNo, Project). This violates the rule for 2NF as the rule says “no non-prime attribute is dependent on the proper subset of any candidate key of the table”.
Student (StudentNo(PK), StudentName, Major)
Project (Project(PK), BroadArea, Client)
Team (Team(PK), Year, Supervisor)
StudentTeam (StudentNo(PK,FK), Team(PK,FK), Grade)
TeamProject (Team (PK,FK), Project(FK))
In the new design all the below anomalies are overcome:
update anomaly: for example now if a supervisor for a particular project changes then it needs to be changed at only one place in only one table.
Delete anomaly: if a project is terminated then when it is removed from Project table then it will be automatically removed from TeamProject table also if ‘Cascade on delete’ is defined in the foreign key but the student information will not be deleted.
It also preserves all the information of student, project and team. Also the consolidated detail of student, team and project can be found by joining one or more tables.
ER DIAGRAM: Please find the below ER diagram which fullfill all the requirment
This IT assignment sample was powered by the assignment writing experts of My Assignment Services AU. You can free download this IT assessment answer for reference.