TigerOne Flight Operations Database: Newly Formed

Tigerone Flight Operations Databasetigerone Is Newly Formed Flight O

Develop a comprehensive database design for TigerOne, a newly established domestic flight company. The database must track flights, customers, fares, airplane performance, and personnel assignments. The company operates exclusively non-stop flights with no assigned seats, emphasizing speed and simplicity. The key data elements include flight details (flight number, origin, destination, estimated departure and arrival times), scheduled flights (dates, assigned airplane and crew, remaining capacity), airplane specifics (serial number, model, capacity, next maintenance date), crew members (employee number, name, phone, job title), customers (customer number, name, phone), and reservation records (reservation number, flight info, date, customer, fare). Utilize the provided reports for data examples to create the following:

  • Draw the Entity Relationship Diagram (ERD) using Crow’s Foot notation.
  • Identify all functional dependencies (FDs) and construct normalized tables in 4NF.
  • Update your ERD to reflect any normalization-based changes.
  • Create the corresponding tables in Microsoft SQL Server.

Paper For Above instruction

The design of an effective database for TigerOne, a start-up domestic airline company focusing on speed and simplicity, necessitates careful analysis and precise structuring to meet operational needs. The key aspects involve capturing flight operations, customer data, personnel, aircraft, and reservations, while ensuring data integrity, minimal redundancy, and ease of maintenance. This paper discusses the process of designing the Entity Relationship Diagram (ERD), identifying functional dependencies, normalization to Boyce-Codd Normal Form (BCNF) or Fourth Normal Form (4NF), updating ERD accordingly, and implementing the design in SQL Server.

Entity Relationship Diagram (ERD)

The ERD forms the backbone of the database, illustrating entities such as Flight, Schedule, Aircraft, Crew Member, Customer, and Reservation. The entities are linked through relationships that reflect real-world associations—flights are scheduled on multiple dates, aircraft are assigned to scheduled flights, crew members are assigned to flights, and customers make reservations for specific flights on specific dates.

For example, the Flight entity holds flight number, origin, destination, estimated times, emphasizing the non-stop nature of the flights. The Schedule entity connects flights on specific dates with an assigned aircraft and crew, also tracking remaining capacity. Crew Members include pilots, flight attendants, and co-pilots, each identified by unique employee numbers and assigned to various flights.

The Customer entity maintains details for airline clientele, while Reservations link customers to specific scheduled flights, recording their fare. The ERD illustrates the cardinalities of relationships: one flight can have many scheduled instances, each scheduled flight involves specific aircraft and crew, and each reservation ties one customer to one scheduled flight.

Functional Dependencies and Normalization

Analyzing the sample data and requirements reveals key functional dependencies:

  • Flight Number → Origin, Destination, Estimated Times: Each flight number uniquely determines its origin, destination, and times.
  • Serial Number → Model, Capacity, Next Maintenance Date: Each aircraft's serial number uniquely determines its specifications.
  • Employee Number → Name, Phone, Job Title: Each crew member's employee number identifies their personal data.
  • Reservation Number → Flight Number, Flight Date, Customer ID, Fare: Each reservation uniquely determines the associated flight, date, customer, and fare.
  • Flight Number & Flight Date → Origin, Destination, Depart Time, Arrive Time: The schedule depends on flight number and date.

Applying normalization principles, the initial unnormalized data is decomposed into tables that satisfy 4NF, primarily by removing multi-valued dependencies and ensuring that every determinant is a candidate key. This process results in tables such as Flights, Aircrafts, CrewMembers, Customers, ScheduledFlights, CrewAssignments, Reservations, and FlightDetails, each with primary keys and appropriate foreign keys to enforce referential integrity.

Updated ERD Reflecting Normalization

Post-normalization, the ERD is refined to clearly delineate entities and relationships, eliminating redundancies and multi-valued dependencies. For instance, CrewAssignments links crew members to scheduled flights, and FlightDetails separates flight-specific information. The entities are connected via one-to-many or many-to-many relationships, appropriately resolved into associative tables where necessary.

Implementation in SQL Server

Based on the normalized schema, the SQL scripts include CREATE TABLE statements with primary keys, foreign keys, and constraints to enforce data integrity. Sample code snippets outline creating tables such as Aircrafts, Employees, Crews, Flights, ScheduledFlights, CrewAssignments, Customers, Reservations, and FlightDetails. Data from the provided reports are inserted respecting data types and constraints. Proper indexing ensures query efficiency, and relationships are enforced through foreign keys. The complete database schema ensures consistency, reduces redundancy, and supports efficient operations for TigerOne.

Conclusion

Designing the TigerOne database involved translating operational requirements into an ERD, analyzing functional dependencies, enforcing normalization to eliminate redundancy, and implementing a relational schema within SQL Server. This comprehensive approach ensures that the database will support the company's goals of speed, simplicity, and reliability, providing a solid foundation for flight operations, reservations, customer management, and maintenance tracking.

References

  • Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387.

Related Assignments