Tigerone
Is Newly Formed Flight Operations Company That Is Targeting D
Tigerone is newly formed Flight Operations Company that is targeting domestic travelers. The company needs a database to track flights, customers, fares, airplane performance, and personnel assignment. Since the company is promoting itself as providing "fast way out of town flights," individual seats are not assigned. More specific notes about different parts of the database are listed below: ï‚·Information about a flight includes its unique flight number, its origin, its destination, and estimated departure and arrival times. To reduce costs, the company only has nonstop flights with a single origin and destination. ï‚· Flights are scheduled for one or more dates with an airplane and a crew assigned to each scheduled flight, and the remaining capacity (seats remaining) noted. In a crew assignment, the employee number and job title (e.g., pilot, flight attendant) are noted. ï‚· Airplanes have a unique serial number, a model, a capacity, and a next scheduled maintenance date. ï‚· Crew members have a unique employee number, a name, a phone, and a job title. ï‚· Customers have a unique customer number, a phone number, and a name. ï‚· Records are maintained for reservations of scheduled flights including a unique reservation number, the flight information, flight date, a customer number, and the fare. A sample of the data items mentioned above is provided in the attached three reports; Report1.pdf, Report2.pdf, and Report3.pdf. Using the information provided to you, perform the following tasks: 1- Draw the Entity Relationship Diagram (ERD) for database. Use the Craw’s Foot notation to draw the ERD. 2- Identify ALL the Functional Dependencies (FDs) and construct normalized tables in the 4NF. 3- If needed, update your ERD to reflect any changes resulting from the normalization process and make.
Paper For Above instruction
The airline industry, particularly focusing on a newly established flight operations company like Tigerone, necessitates meticulous data management to ensure effective operations, compliance, and customer satisfaction. This paper delineates the development of a comprehensive database model using Entity-Relationship Diagram (ERD), normalization principles, and subsequent updates to the ERD to align with normalization outcomes. The goal is to establish a robust, efficient, and scalable database system capable of handling various operational facets such as flight scheduling, personnel management, customer reservations, and aircraft maintenance.
**Entity-Relationship Diagram (ERD) Construction**
The foundational step involves identifying key entities derived from the provided data constraints:
- **Flight**: Attributes include flight number (PK), origin, destination, estimated departure, and arrival times.
- **Airplane**: Attributes include serial number (PK), model, capacity, next scheduled maintenance date.
- **Crew Member**: Attributes include employee number (PK), name, phone, and job title.
- **Customer**: Attributes include customer number (PK), name, and phone.
- **Scheduled Flight**: This entity links a particular flight with specific scheduling details such as flight date, assigned airplane, crew, and remaining capacity.
- **Reservation**: Attributes include reservation number (PK), flight info, flight date, customer number (FK), and fare.
In the ERD, relationships include:
- **Flights** scheduled on multiple dates, linking to **Scheduled Flight**.
- **Scheduled Flights** assigned to exactly one **Airplane** and multiple **Crew Members** (through a crew assignment relationship).
- **Customers** making multiple **Reservations** for flights.
- **Flights** being associated with **Reservations**.
Using Craw’s Foot notation:
- The relationship between **Flight** and **Scheduled Flight** is one-to-many, indicating each flight can have multiple scheduled instances.
- The **Scheduled Flight** associates with exactly one **Airplane** (many-to-one).
- The **Crew Assignment** links **Scheduled Flights** to **Crew Members** in a many-to-many relationship, which will be resolved by establishing a junction entity, e.g., **CrewAssignment**.
- The **Reservation** is linked to **Customer** in a many-to-one relationship.
**Identification of Functional Dependencies (FDs) and Normalization to 4NF**
From the above entities, functional dependencies include:
- `FlightNumber → Origin, Destination, Estimated times`
- `SerialNumber → Model, Capacity, Next Maintenance Date`
- `EmployeeNumber → Name, Phone, Job Title`
- `CustomerNumber → Name, Phone`
- `ReservationNumber → Flight, FlightDate, CustomerNumber, Fare`
- `ScheduledFlightID → FlightNumber, Date, AirplaneSerial, RemainingCapacity`
- `CrewAssignmentID → ScheduledFlightID, EmployeeNumber, JobTitle`
These dependencies facilitate normalization:
- **First Normal Form (1NF)**: Ensured by atomic attributes.
- **Second Normal Form (2NF)**: Achieved by removing partial dependencies; e.g., moving flight details to a separate Flight table.
- **Third Normal Form (3NF)**: Achieved by removing transitive dependencies; e.g., ensuring that non-key attributes depend only on the primary key.
- **Boyce-Codd Normal Form (BCNF)** and **Fourth Normal Form (4NF)**: Further normalization addresses multi-valued dependencies, notably in crew assignments where a scheduled flight can have multiple crew members with different roles.
Based on the normalization process:
- The **Flight** table remains, with flight number as PK.
- The **Airplane** table remains, with serial number as PK.
- The **CrewMember** table remains, with employee number as PK.
- The **Customer** table remains, with customer number as PK.
- The **ScheduledFlight** table includes a surrogate key, with references to Flight and Airplane.
- The **Reservation** table links customers to specific scheduled flights.
- The **CrewAssignment** table resolves many-to-many crew-scheduled flight relationships, with composite PK (ScheduledFlightID, EmployeeNumber).
**Updating the ERD Post Normalization**
Normalization clarifies the relationships:
- The ERD now features **CrewAssignment** as a junction entity between **ScheduledFlight** and **CrewMember**, capturing crew roles.
- The **Reservation** links explicitly to **ScheduledFlight** via its ID, ensuring data integrity.
- All entities are in 4NF, with multivalued dependencies addressed through junction tables.
**Conclusion**
This structured approach, combining ER modeling and rigorous normalization, provides a reliable database foundation for Tigerone’s operations. By reflecting functional dependencies through the normalized schema, the company’s data management will support operational efficiency, scalability, and data integrity. Continuous review and updates of the ERD ensure it remains aligned with evolving business requirements and normalization standards, ultimately supporting Tigerone’s mission to offer fast, efficient flights to domestic travelers.
References
Codd, E. F. (1970). "A Relational Model of Data for Large Shared Data Banks," *Communications of the ACM*, 13(6), 377–387.