Untitled Flashcards Set

Scenario for developing the database

 

 

GlobeTrotter Airlines is a global airline dedicated to offering exceptional travel experiences to passengers around the world with its head office in Nepal. Established in 2010, GlobeTrotter Airlines in Kathmandu, has quickly become a trusted name in the aviation industry, renowned for its commitment to safety, customer satisfaction, and innovative services.

 

They have both international and domestic flights. Internationally they fly to 7 countries: India, Sri Lanka, Maldives, United Arab Emirates, Malaysia, Vietnam, and Indonesia. They provide flight services to 14 cities in Nepal. Each flight has a unique FlightID, and flight number. They have specific and timely departure and arrival time with references to different airports.

 

Every day hundreds of passengers trust GlobeTrotter for seamless and hassle-free travel outside and inside the country. Every time a passenger purchases their ticket various information regarding the passenger such as PassengerID, Name, Passport or citizenship number is recorded. Each ticket has a unique TicketID, seat number,passengerID, and flight ID.

 

The airline has been keeping track of flights,passengers, tickets, and airport records in various registers.  In order to solve this traditional approach of record-keeping, you are required to develop a database for the airlines which will consist of at least five entities with a minimum of five records in each table. Likewise, you are also required to implement suitable constraints in each table.

 

Each record of the table is to be uniquely identified using a suitable attribute. Before developing the database itself, you are required to define appropriate business rules for the defined scenario and draw an ERD (Entity Relationship Diagram) and Relational Diagram of the database model.

  • Entities to Consider:

    • Flights: Includes attributes such as FlightID, DepartureTime, ArrivalTime, Origin, Destination.

    • Passengers: Contains PassengerID, Name, ContactInfo, TicketID, FrequentFlyerStatus.

    • Tickets: Comprises TicketID, Price, Class, DateIssued, FlightID.

    • Airports: Features AirportID, Name, Location, IATA_Code, Capacity.

    • Employees: Encompasses EmployeeID, Name, Position, HireDate, Salary.

  • Business Rules:

    • Each flight must have a unique FlightID.

    • Passengers must be assigned a unique PassengerID and can book multiple tickets.

    • Tickets must reference a valid FlightID and be unique by TicketID.

    • Airports must have a unique AirportID and IATA_Code.

    • Employees must have a unique EmployeeID with relevant position details.

  • ERD and Relational Diagram:

    • Create diagrams to visually represent the relationships between these entities, ensuring to illustrate cardinalities and constraints. Additionally, ensure that all entities are properly normalized to avoid redundancy and maintain data integrity throughout the database. The diagrams should clearly depict the connections between the Airport and Employee entities, highlighting primary and foreign keys, as well as any other relevant attributes that define their relationships.

    • The Airport entity will include attributes such as AirportID (Primary Key), IATA_Code, Name, Location, and Capacity.

    • The Employee entity will incorporate EmployeeID (Primary Key), Name, Position, and associated AirportID (Foreign Key) to establish the relationship.

solve this

  • Ensure that the diagrams include:

    • Relationships between Airport and Employee entities, indicating one-to-many relationships where one airport can have multiple employees.

    • Proper notation for primary keys (PK) and foreign keys (FK) to clarify the connections.

    • Additional attributes for the Airport entity, such as ContactNumber and OpeningHours, to provide a more comprehensive view of the airport's operational details.

    • Annotations for any business rules that govern the relationships, such as employee roles specific to certain airports.

    • Airport Entity:

      • Attributes:

        • AirportID (PK)

        • Name

        • Location

        • ContactNumber

        • OpeningHours

      • Relationships:

        • has many Employees (FK: AirportID in Employee entity)

    • Employee Entity:

      • Attributes:

        • EmployeeID (PK)

        • Name

        • Role

        • AirportID (FK)

      • Business Rules:

        • Employees may have roles specific to their assigned airports, ensuring that staffing aligns with operational needs.

what about ERD?

In the Entity-Relationship Diagram (ERD), this relationship can be represented by an association between the Employee entity and the Airport entity, indicating that each employee is linked to a specific airport through the AirportID foreign key. Additionally, it is important to define the cardinality of this relationship, specifying whether an employee can be assigned to multiple airports or just one, which will impact the overall database design. Further, the ERD should also capture the attributes of both entities, such as EmployeeID, Role, and AirportName, to provide a comprehensive view of the staffing structure. This detailed representation will facilitate better understanding and management of employee assignments, enabling efficient allocation of resources based on the operational requirements of each airport. Moreover, it can also illustrate any additional relationships, such as the connection between employees and their respective roles, which will aid in clarifying responsibilities and enhancing accountability within the organization.