Skip to document
Welcome to StudocuSign in to access the best study resources
Guest user
0followers
HomeMy LibraryAsk AI
  • You don't have any recent items yet.
My Library
  • You don't have any courses yet.
  • You don't have any books yet.
  • You don't have any Studylists yet.

Project 3

Project 3
Course

Introduction to Databases and Databases Management Systems:Part 1 (CSIQ2634)

37 Documents
Students shared 37 documents in this course
Academic year: 2023/2024
Uploaded by:
Anonymous Student
This document has been uploaded by a student, just like you, who decided to remain anonymous.
University of the Free State

Comments

Please sign in or register to post comments.

Students also viewed

Related documents

Preview text

1

UNIVERSITY OF THE FREE STATE

Department of Computer Science

and Informatics

CSIS/CSIQ 3714

Project 3

Submission Instructions

Release date: 19 April 2023: Submission date: 2nd May @ 5PM

Instructions

  1. Use the database you created in Assignment 2.
  2. Write a stored procedure for each of the questions that follow.
  3. Name the stored procedure using the question number, e. Question 1, Question
  4. Script your SQL stored procedures only (tables and data are not needed).
  5. Upload the script to Blackboard
  6. Do not zip or PDF the script, simply submit the script as is onto Blackboard

Question 1

When a passenger with a valid booking checks in the following must occur:

  • A boarding pass is issued for that booking
  • The bag must be logged into the database with status “Checked”. Assume each boarding pass has only one bag.
  • A seat is allocated to the booking.
  • All required values (other than dates) are sent as parameters to the stored procedure.
  • The database must be left in a consistent state at the end of this process.

Question 2

Assuming FlightId in Booking is a foreign key, write a stored procedure that lets ticket agents see which bookings on a selected flight have not checked in (been issued a boarding pass). Show passenger last name and first name. Sort the list alphabetically. In your stored procedure, write the query in two different ways, once using an outer join and once without an outer join.

2

Question 3

For a given flight, list the entire flight crew. The screenshot below is an example of how the report should look.

Last name First name Role Jackson Lilly Pilot Peters Khenyn Co-pilot Frasier Kerry Chief Steward Merriman Nigel Steward

Question 4

A scanning error has crept in on the check-in desk. Write a stored procedure to determine which boarding passes have scanned more than 2 bags. Also show any bag that weighs more than 20KG. Use a variable to store the maximum weight of 20KK. Consider using a subquery for this stored procedure, alternatively use a SET operation.

Question 5

You need a stored procedure to update the flight boards. The flight board must show the route description, flight description, starting airport name and destination airport name. [Hint: Use a table alias].

Was this document helpful?

Project 3

Course: Introduction to Databases and Databases Management Systems:Part 1 (CSIQ2634)

37 Documents
Students shared 37 documents in this course

University: University of the Free State

Was this document helpful?
1
UNIVERSITY OF THE FREE STATE
Department of Computer Science
and Informatics
CSIS/CSIQ 3714
Project 3
Submission Instructions
Release date: 19 April 2023: Submission date: 2nd May @ 5PM
Instructions
1. Use the database you created in Assignment 2.
2. Write a stored procedure for each of the questions that follow.
3. Name the stored procedure using the question number, e.g. Question 1, Question
2.
4. Script your SQL stored procedures only (tables and data are not needed).
5. Upload the script to Blackboard
6. Do not zip or PDF the script, simply submit the script as is onto Blackboard
Question 1
When a passenger with a valid booking checks in the following must occur:
• A boarding pass is issued for that booking
• The bag must be logged into the database with status “Checked”. Assume each
boarding pass has only one bag.
• A seat is allocated to the booking.
• All required values (other than dates) are sent as parameters to the stored
procedure.
• The database must be left in a consistent state at the end of this process.
Question 2
Assuming FlightId in Booking is a foreign key, write a stored procedure that lets ticket agents
see which bookings on a selected flight have not checked in (been issued a boarding pass).
Show passenger last name and first name. Sort the list alphabetically. In your stored
procedure, write the query in two different ways, once using an outer join and once without
an outer join.

Students also viewed

Related documents