Database Design & SQL for Data Analysis DBA7001

Assessment Information/Brief 2024-25

To be used for all types of assessment and provided to students at the start of the module. Information provided should be compatible with the detail contained in the approved module specification although may contain more information for clarity.

Assessment task details and instructions

Task 1 (65 marks)

Imagine you are employed as a database developer consultant for issuing the e-boarding number for the pre-booked flights at the airport. They are currently in the process of developing a new database system which they require for storing information on the flight details, including passengers, reservation, employees, tickets, and baggage information. In your initial consultation with the airport, you have gathered the information below. Please read the below carefully and continue to the task description.

Client Requirements

An employee begins their shift by logging into the airport's secure ticketing system using their unique username and password (role can be classified as Ticketing Staff or Ticketing Supervisor with Employee ID, email and name). Once authenticated, they access the ticket reservation module to manage passenger bookings. If not authenticated, then Ticketing Supervisor will intervene by granting permission. A passenger who previously reserved a flight now requires ticket issuance. The employee retrieves the passenger's reservation by searching for the Passenger Name Record (PNR) containing the travellers itinerary, booking details and status (confirmed, pending, cancelled). After confirming the reservation details such as flight number, departure and arrival times, and seat assignment (if preferred seat not available, will be set out to NULL), the employee proceeds to issue the ticket. This process involves generating an e-boarding number, which is then associated with the passenger's PNR in the system, the Employee ID of the employee issuing the boarding number will be stored. Additionally, the ticketing system calculates the additional fare, including any taxes or fees, is they have taken any additional services like extra baggage (100 GBP per kg), upgraded meal (20 GBP per person) or preferred seat ( 30 GBP per person). Each flight, identified by a unique FlightID, flight number, departure time, arrival time, origin, destination, is associated with multiple tickets (Ticket ID, reservation ID, issuedate, issuetime, fare, seatnumber, class (which can be business, firstclass or economy), linking passengers to specific flights and seat assignments. Passengers, identified by PNR including PassengerID, email, meal (vegetarian or non-vegetarian) DoB, first name, and last name (where passenger emergency contact number is optional), can hold multiple tickets, reflecting their travel itineraries. The system tracks baggage information by associating each flight with its corresponding ticket, weight, status (checkedin or loaded), and additional services like baggage fee. This process ensures that passengers with prior reservations are efficiently issued tickets, maintaining the airport's commitment to operational excellence and customer satisfaction.

Task Details

As the database consultant, you are required to design the database system based on the information provided above, along with a number of associated database objects, such as stored procedures, user-defined functions, views and triggers. Your submission will take the form of working T-SQL statements required for the steps outlined below, a backup of the database created, and a report explaining and justifying your design decisions, and the process you followed to complete the tasks. You should include screenshots and the T-SQL statements within the report itself.

1. You should design and normalise your proposed database into 3NF, fully explaining and justifying your database design decisions and documenting the process you have gone through to implement this design using T-SQL statements in Microsoft SQL Server Management Studio, using screenshots to support your explanation. All tables and views must be created using T-SQL statements, which should be included in your Clearly highlight which column(s) are primary keys or foreign keys. You should also explain the data type used for each column and justify the reason for choosing this. You should also consider using constraints when creating your database to help ensure data integrity.

You must include a database diagram as part of your submission. If you have made any additional assumptions aside from the information above when designing your database, you should clearly state these. Create tables according to the scenario explained above which should include details on flights, reservations, employees, baggage, passengers, and tickets. Populate (Insert) the tables with the appropriate number of records (at least 7). You should also ensure the data you input allows you to adequately test that all the following queries.

2. Add the constraint to check that the reservation date is not in the

2. Identify Passengers with Pending Reservations and Passengers with age more than 40

2. The ticketing system also requires stored procedures or user-defined functions to do the following things:
   
   
   1. Search the database of the ticketing system for matching character strings by last name of passenger. Results should be sorted with most recent issued ticket first.
   
   
   1. Return a full list of passengers and his/her specific meal requirement in business class who has a reservation today (i.e., the system date when the query is run)
   
   
   1. Insert a new employee into the
   
   
   1. Update the details for a passenger that has booked a flight
   
   
   

5. The ticketing system wants to be able to view all e-boarding numbers Issued by a Specific Employee showing the overall revenue generated by that employee on a particular flight. It should include details of the fare, additional baggage fees, upgraded meal or preferred seat. You should create a view containing all the required information.

6. Create a trigger so that the current seat allotment of a passenger automatically updates to reserved when the ticket is issued.

7. You should provide a function or view which allows the ticketing system to identify the total number of baggages (which are checkedin) made on a specified date for a specifi

8. If there are any other database objects such as views, stored procedures, user-defined functions, or triggers which you think would be relevant to the airport scenario given the brief above, you will obtain higher marks for providing these along with an explanation of their functionality. (Give 2 additional queries).

Within your report, you will also need to provide your client with advice and guidance on:

• Data integrity and concurrency

• Database security

• Database backup and recovery

Generic information on these topics, which is not applied to the given scenario, is likely to score poorly.

To get more than a satisfactory mark, you must use all of the below at least once in your database:

• Views

• Stored procedures

• System functions and user defined functions

• Triggers

• SELECT queries which make use of joins and sub-queries

NB: All functions and stored procedures in task1, should be called as well (You should define, call them and show the results) Your report for Task 1 should be no more than 4,000 words (code snippets are not included in the wordcount). In the first part you should provide a description of the database design and normalization, then you should answer each question, explain and show the results and then you should explain what you have considered in your database for Data integrity and concurrency, Database security and database backup and recovery. Your report should also be structured and numbered so that we can easily identify which part of the report relates to each of the above numbered steps above. You should include the screenshots of all the codes and their outputs/results with explanation in your report You should include one SQL file for Task 1 in the zip file provided in the code area. Name the SQL file "studentid_task1.sql" and make sure to separate the answers to different questions by commenting very clearly.

Task 2 (35 marks)

For the second task, you should use the five csv files provided for you on Blackboard. These files are an excerpt from a larger file which is a real-world dataset from an online shopping site. The file provides the information on customers on products and their orders, order items with payments. The data includes five related tables, which are provided in five csv files:

• The csv file has 120 records and provides the customer_id, name, email, phone and country. Customer_id is a unique key identifier.

• The csv file provides details of the 5 different products that can be purchased. This includes the product_id, product_name, category and price. The product_id column provides a unique identifier for each product.

• The csv file provides a total of 120 records with order_id, customer_id and order_date. The order_id column provides a unique identifier for each order.

• csv lists the total 120 rows including order_item_id, order_id, product_id, quantity, price_each, Total_price and Total_amount. The order_item_id column provides a unique identifier for each order-item. Total_amount is calculated based on the multiple order_id placed for different products.

• csv includes 120 rows including payment_id, order_id, payment_date, payment_method and Amount_paid. The payment_id column provides a unique identifier for this table.

1. For this task, imagine you work as a database consultant for an Online shopping site. The first stage of your task is to create a database and import the five tables from the csv file. You should also add the necessary primary and foreign key constraints to the tables and provide a database diagram in your report which shows the five tables and their relationships. You should create the database with the name OnlineShoppingDB and the tables with the following names:
   
   
   1. Customers
   
   
   1. Products
   
   
   1. Orders
   
   
   1. Orders_items
   
   
   1. Payments
   
   
   

You should also leave the column names as they appear in the csv file. This is so we can re-run your code.

2. Write a query that returns the names and countries of customers who made orders with a total amount between 500 and 1000.

3. Get the total amount paid by customers belonging to UK who bought at least more than three products in an order.

4. Write a query that returns the highest and second highest amount_paid from UK or Australia this is calculated after applying VAT as 2% multiplied by the amount_paid. Some of the results are not integer values and your client has asked you to round the result to the nearest integer value

5. Write a query that returns a list of the distinct product_name and the total quantity purchased for each product called as total_quantity. Sort by total_quantity.

6. Write a stored procedure for the query given as: Update the amount_paid of customers who purchased either laptop or smartphone as products and amount_paid>=17000 of all orders to the discount of 5%.

7. You should also write at least five queries of your own and provide a brief explanation of the results which each query returns. You should make use of all of the following at least once:

• Nested query including use of EXISTS or IN

• Joins

• System functions

• Use of GROUP BY, HAVING and ORDER BY clauses

Your report for Task 2 should be no more than 2,000 words and should provide a brief description of the data import steps you followed (with screenshots as needed), along with your T-SQL statements, a brief explanation of each one and the full result set (where it is feasible to include this). As with Task 1, code snippets are not included in the wordcount.

NB: You should include the screenshots of all the codes and their outputs/results with explanation in your report. You should include one SQL file for Task 2 in the zip file provided in the code area.

Name the SQL file "studentid_task2.sql" and make sure to separate the answers to different questions by commenting very clearly.