COS5020-B : Database Systems Practical Usage of SQL - IT Assignment Help

Assignment Task:

Task:

Portfolio of exercises, testing the ability to manipulate the formalisms introduced in the lectures and practical usage of SQL.
Weighting: 40% of the assessment for Database systems
Date Issued: 11 November, 2020
Date Due: 4 December, 2020, 17:00
This CW1 addresses the following Learning Outcomes:
1b) Apply the theory underlying relational database systems.
2b) Apply SQL to query and modify data in a RDBMS.

Part A. Relational Algebra and Relational Calculus [40 Points]
The following tables form part of a database held in an RDBMS.
Airport (airportId, name, city, country)
Flight (flightId, airline, depAirportId, arrAirportId, depTime, arrTime, duration)
Ticket (ticketId, flightId, flightDepDate, seatNo, class, price, passengerId)
Passenger (passengerId, fName, lName, nationality, email, phoneNo)

Formulate the following queries in relational algebra and tuple relational calculus.
A1. Retrieve all information about the airports in the UK (airport Id, airport name, city). [6 Points]
A2. Retrieve a list with all the flights between UK and France and some of their details (flight Id, airline, departure airport, arrival airport, departure time, duration).

A3. Provide a list with all the tickets in business class, showing: the passenger first name, last name, ticket Id, flight departure date, seat number, class and price. [6 Points]

A4. List the name of all the British passengers (first name, last name) which bought tickets more expensive than £500 and the ticket value. [6 Points]

A5. Find out which are the flights with the longest duration and provide the following details: flight id, duration, departing country, arriving country. [6 Points]

A6. In relational algebra an operator is said to be monotone if whenever we add a tuple to one of its arguments, the result contains all the tuples that it contained before adding the tuple, plus perhaps more tuples. Which of the relational algebra operators you are familiar with are monotone? For each operator, give a brief explanation why you believe it is monotone, or an example showing that it is not. [10 Points]

Part B. Practical usage of SQL [60 Points]

B1. Write CREATE TABLE statements for each relation described in Part A. Choose for each attribute an appropriate data type, define the primary keys (in bold and underlined) and foreign keys (underlined). [10 Points]
B2. Populate these tables with some sample data, such that all the SQL queries that you will write return non-empty result sets. Provide in the report a few examples of INSERT statements for each table and upload with the coursework the SQL dump of the database. [10 Points]
B3-B7. Write SQL queries for each query from Part A, that means 5 SQL queries corresponding to exercises A1-A5. [ 5 X 3 =15 Points] Write SQL queries for the following exercises:
B8. Calculate the average ticket price for each airline company and order them descending, showing: airline, average price. [3 Points]
B9. Produce a report with the total ticket sales for ‘Lufthansa’ airline company in the month of October 2020, showing how many flights they had this month, how many tickets sold and their total price. [3 Points]
B10. List all the flights with departure times between 08:00 and 11:59, on the route London – Paris (any airports in these cities), ordered by the departure time. [3 Points]

B11. Provide a list with the 5 most expensive tickets sold, ordered descending by the price, showing the ticket id, price, flight id, departure and arrival airports. [3 Points]
B12. Display all the airports for which there are at least 4 flights in your database. [3 Points]
B13. Find all the passengers that had flights from all the airports in the UK. Provide at least 2 different solutions and explain them.

The above  COS5020-B IT Assignment has been solved by our  IT Assignment  Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.