CS4400 Project Simple Airline Management System (SAMS) Solution

CS 4400: Introduction to Database Systems
Project Purpose
Project Phases
Inputs (we provide to you…)
• Scenario Description
• Sample Data Elements • Enhanced ERD
• Initial Data Set (in a NonNormalized Format) • Physical Database Schema with Initial Data Set
• Stored Procedure Shells
Phase I Phase II Phase III
• Enhanced Entity Relationship Diagram (ERD)
• List of Assumptions (optional) • Relational Schema
• Physical Database Schema with Initial Data Set
• Unhandled Exceptions List • Implemented Procedures
• Any Supporting Views and Related Structures
Outputs (…you turn in to us)
Phase III Directions
In Phase III, your tasks are to:
• Implement nineteen (19) stored procedures which allow the system operators to query and modify the database state in accordance with the problem scenario
• Implement six (6) views which provide information to the system operators about the database state from various "points of view" (i.e., system operator roles)
Implementation Details
• The only thing you will turn in is your completed procedure shell. The remaining material that we've provided is to support your efforts to develop your views and stored procedures.
• Each individual test case for a view or stored procedure will be graded on an based in large part on the number of test cases that it completes successfully. We encourage you to develop a solid plan for implementing the required structures (in general) from the easiest to the most difficult. Submitting fewer procedures that have been thoroughly tested and work well is usually better than lots of procedures that work in an inconsistent/haphazard manner.
• Your views and stored procedures must be based on the underlying tables and foreign keys that we've provided. You are not permitted to modify the tables and foreign keys that we've provided in any way.
• You must maintain the state of the system using the tables that we've provided. There might be other systems that use those same tables to monitor the state of the system, and that shared data must be updated and maintained in a consistent manner. Also, our testing systems will monitor the provided tables to ensure that transactions have been updated correctly.
• You must use the stored procedure and view specifications that we've provided in the shell (i.e., view and stored procedure names, along with the stored procedure parameter names, types, and order). These are essential to the interface of your application: any changes might impact how users are able to access your application, and might also adversely impact our evaluation of your submission.
• Keep in mind that some of the input values for some of the stored procedure parameters might be empty. In those cases, the input value will be a NULL. Review the scenario description and the database structure for more information about the valid ranges for the different parameters.
• You are welcome (and encouraged) to develop other views, functions, and stored procedures as desired. Developing these "supporting database structures" can help make implementing the required views and stored procedures easier by dividing a potentially complex problem into simpler tasks.
Submission Checklist
Each team needs one of its members to upload the deliverables to Canvas. The other team members should log in and check to ensure that all files have been uploaded correctly. Please include your team numbers in the file names.

Your submission must include the following deliverables compiled into one document:
• A file named cs4400_phase3_stored_procedures_team#.sql containing your completed views and stored procedures, along with any other "supporting database structures"
• Your SQL file must run in MySQL Workbench without error for you to receive credit for these statements
Version History