Add Passenger Count And Manage Database Consistency In Fligh
Add Passenger Count and Manage Database Consistency in Flight Database
The objective of this assignment is to enhance understanding of relational database design and implementation, specifically with PL/SQL in Oracle 11g. The tasks include extending existing database structures, developing procedures, functions, triggers, and thoroughly testing them for managing airline flight data effectively.
The core challenge centers around adding an attribute to track the number of passengers booked per flight and ensuring that this number does not exceed the capacity of the assigned airplane. Additionally, the assignment involves creating PL/SQL packages with procedures for opening flights, scheduling tests, retrieving available airplanes, and maintaining data integrity with triggers. The final step is rigorous testing of all developed code components with appropriate data scenarios.
Paper For Above instruction
Effective airline flight management necessitates robust database design that ensures data accuracy, consistency, and operational feasibility. In this context, adding a passenger capacity indicator to each flight while preventing overbooking is critical. The database schema, initially consisting of tables such as FLIGHT, AIRPLANE, SERVES, and others, must be modified to include the new attribute and associated consistency constraints.
To maintain database integrity, one must implement mechanisms that automatically prevent insertion or update operations that would violate capacity constraints. This can be achieved through the use of check constraints, triggers, or procedural validations embedded within stored procedures. The choice of method hinges on factors such as performance, flexibility, and maintainability.
Adding the NoofPassengers attribute involves extending the FLIGHT table. A simple approach is to define this attribute with a CHECK constraint to ensure it never exceeds the airplane's capacity. However, as airplane capacity resides in the AIRPLANE table, a dynamic validation during data insertion or update becomes necessary. For this, PL/SQL triggers or stored procedures can be employed to enforce this rule consistently.
One effective method is to create a BEFORE INSERT or BEFORE UPDATE trigger on the FLIGHT table, which, upon an attempt to set or modify the NoofPassengers value, compares it with the capacity of the airplane assigned via the SERVES relationship. If the number exceeds capacity, the trigger raises an exception, blocking the transaction and alerting the user or application. This design ensures real-time enforcement of business rules, maintaining data consistency seamlessly.
Furthermore, advanced relational integrity can be enforced at the application layer or through stored procedures that manage flight creation or modification. Adopting stored procedures for manipulating flight records allows central control over validation logic, reducing errors and inconsistencies. It also simplifies auditing and future enhancements.
Creating a PL/SQL package encapsulates all related procedures, including opening a new flight, scheduling tests, and fetching available airplanes. For example, the open_flight procedure should check for existing flights with the same route and date, ensure the airplane's availability, and then insert the new flight with the NoofPassengers attribute set appropriately. If any validation fails, the procedure raises an exception, preventing incorrect data entry.
The second task involves scheduling tests for airplanes, which also requires verifying an airplane’s availability on the specified date. This can be handled via procedures that query scheduled tests and flights to avoid conflicts.
The third task demands a trigger to monitor the safety of flights by evaluating test scores. When a test score is updated, the trigger checks if it meets the threshold (e.g., 85%). If the score falls below this threshold, it automatically cancels all future flights associated with that airplane, maintaining operational safety.
Testing these functionalities requires creating sample data scenarios where flight capacities are challenged, test scores fluctuate, and airplane scheduling conflicts arise. Observing the system’s response validates the correctness of the constraints and procedures.
In conclusion, designing a resilient airline database involves carefully embedding constraints, leveraging PL/SQL features, and ensuring active validation through triggers and procedures. This approach guarantees that operational data remains consistent, safe, and reflective of real-world constraints, ultimately supporting airline management's decision-making and operational safety.
References
- Date, C. J. (2012). Oracle PL/SQL Programming (6th ed.). O'Reilly Media.
- Oracle Database 12c PL/SQL Programming. McGraw-Hill Education.