Using the relational model, define relations, keys, etc. to model flights, airlines and airports. Explain all...

Using the relational model, define relations, keys, etc. to model flights, airlines and airports. Explain all of the choices you make.

Solutions

Expert Solution

Using the relational model, define relations, keys, etc. to model flights, airlines and airports. Explain all of the choices you make.

Each airport is represented in the database, which retains its specific airport code, the name of the airpot and the city and state in which the airport is located.
There is a specific number for any airline flight, the airline for the flight, and the weekdays on which the flight is scheduled.
A flight includes one or two legs of a flight. Each leg of the flight has a leg number, airport of departure and scheduled time of departure, airport of arrival and scheduled time of arrival.
A leg example is an example of a flight leg at a given date. Also preserved are the number of available seats and the aircraft used in the example of the leg.
For each reservation, the customer reservations contain the client name, phone, and seat numbers on each leg case.
Data on aircraft and models of aircraft is also preserved. The model name, production company, and maximum number of seats are maintained for each model of airplane. The airports where such Can Land aircraft are kept in the database. Airplane I d, Total number of seats, and Type are maintained for each airplane.

Entities

Airport
Airplane Type
Airplane
Flight leg
Flight
Leg Instance

Relationships

It is a 1-N relationship between the airport and the flight leg based on the departure airport.
It is a 1-N relationship between Airport and Flight Leg based on arrival airport.
It is a M-N relationship between Airport and Airplane Type based on landing.
It is a 1-N relationship between Airplane Type and Airplane based on the type.
It is a 1-N relationship between Airplane and Leg instance based on the assignment.
It is a 1-N relationship between Leg instance and Seat based on reservation.
It is an N-1 relationship between Flight Leg and Flight based on the legs.
It is a 1-N relationship between Flight and Fare based on the fares.
It is a 1-N relation between Airport and Leg instance based on where it departs.
It is a 1-N relationship between Airport and Leg instance based on the arrival.
It is a 1-N relationship between Flight Leg and Leg instance based on instance.

Key attributes

Airport- Airport code
Airplane Type- TypeName
Airplane- AirplaneId
Leg Instance- Date
Seat- SeatNo
Fare- Code
Flight Leg- LegNo
Flight- Number

Other relevant attributes

Airport- Name, City, State
Airplane Type- Max-seats, Company
Airplane- Total-no-of-seats
Leg Instance- No-of-avail seats
Fare- Amount, Restrictions
Flight- Weekdays, Airline

ER Diagram

venereology answered 6 days ago

Next > < Previous

Related Solutions

1. What Would You Do? Case Assignment Delta Airlines Headquarters Atlanta, Georgia All airlines and airports...

1. What Would You Do? Case Assignment Delta Airlines Headquarters Atlanta, Georgia All airlines and airports lose bags. After all, they must handle thousands of bags per day, sort through the bags on each plane like a 500-piece puzzle dumped on the table from a just-opened box, and then rush them to the right connecting planes or baggage carousels. The challenging logistics, however, don’t make up for the impact of delays on passengers. There’s the Rabbi flying to Israel, whose...

1. Consider the following relations. The primary keys are underlined. All attributes are of type string...

1. Consider the following relations. The primary keys are underlined. All attributes are of type string if not indicated otherwise.  Student(s_ID, s_name, s_degree: integer, advisorID, d_ID)  Lecture(l_ID, l_name,l_degree: integer, p_ID, d_ID)  Register(s_ID,l_ID,score: integer, Semester)  Professor(p_ID,p_name, d_ID)  Department(d_ID, d_name, address) a. Find the names of professors who have taught in every semester. b. List the names of lectures that the CISE department offers but that are taught by a professor whose department is not CISE. c....

Define public relations and explain the different public relations activities.

Explain the differences among hierarchical, network, and relational data models. What makes the relational data model...

Explain the differences among hierarchical, network, and relational data models. What makes the relational data model the most popular data model in use today? (DQ 4-1)

Three airlines serve a small town in Ohio. Airline A has 48% of all scheduled flights,...

Three airlines serve a small town in Ohio. Airline A has 48% of all scheduled flights, airline B has 33% and airline C has the remaining 19%. Their on-time rates are 81%, 69%, and 35%, respectively. A flight just left on-time. What is the probability that it was a flight of airline A?

Airlines compute the weight of outbound flights using either standard average weights provided by the Federal...

Airlines compute the weight of outbound flights using either standard average weights provided by the Federal Aviation Administration (FAA) or weights obtained from their own sample surveys. The FAA standard average weight for a passenger’s carry-on items (personal items plus carry-on bags) is 16 pounds. Many airline companies have begun implementing fees for checked bags. Economic theory predicts that passengers will respond to the increase in the price of a checked bag by substituting carry-on bags for checked bags. As...

Evaluate your model using all factors outlined in the text. Assumptions, conditions, fit, outliers, etc. Then...

Evaluate your model using all factors outlined in the text. Assumptions, conditions, fit, outliers, etc. Then explain your answer in words. Create a linear regression model that has 2 or 3 independent variables. Your choice of models…your choice of variables. A1 Tim is a wedding planner. Looking at the data he has accessed, he is trying to simplify a model for how much a wedding will cost. Use the data to create a model with 2 or 3 Independent Variables,...

Normalize the following relations. Show possible candidate/primary keys and the functional dependencies. Explain the normal form...

Normalize the following relations. Show possible candidate/primary keys and the functional dependencies. Explain the normal form in which the relation is currently in (based on the sample data) and how do you break the relations to get 3rd Normal Form. Show the new relations obtained after normalization and underline the candidate/primary key in each new relation and italic the foreign key. Relation 1: COLLEGE PARKING TICKET (STID, LName, FName, PhoneNo, StateLic, LicNo, Ticket#, Date, Code, Fine) (Illustrated with sample data)...

Define and explain the tenets of the perfectly competitive economic model. Explain, and show using diagrams...

Define and explain the tenets of the perfectly competitive economic model. Explain, and show using diagrams and labels, why economists argue that competition between producers in markets to supply goods and services, and competition between consumers in markets to buy goods and services, with some conditions applying, will allocate scarce resources efficiently in the economy. Under what conditions, and why, do markets fail. Give examples of market failure, and why these occur, where markets do not provide the amount of...

Consider a relational dataset and specify your input and output variables, then: Train the model using...

Consider a relational dataset and specify your input and output variables, then: Train the model using 80% of this dataset and suggest an appropriate GLM model output to input variables. Specify the significant variables on the output variable at the level of ?=0.05 and explore the related hypotheses test. Estimate the parameters of your model. Predict the output of the test dataset using the trained model. Provide the functional form of the optimal predictive model. Provide the confusion matrix and...

Subjects