I need someone to help put it together to complete it and thank you... (Part 1)
ID: 3862305 • Letter: I
Question
I need someone to help put it together to complete it and thank you...
(Part 1) How would l Do a COMPLETE/CORRECT ER Model (SLQ) Mapping to relational Database on this? SQL statements for Entities and Relationships Creation 1. Make sure the data type and data size are appropriate for each attribute. For example, data type TINYINT is better than INT for the attribute age. Do not forget to include a suitable Primary Key and/or Foreign Key in your definition of each table. SLQ for loading data for each relation 2. Make sure to include one sample record from each table here and you need SLQ statements for 10 business functions. Each flight has a limited number of available seats Online Airline Reservation System The main objective of the online system is to create a convenient and easy-to-use online system for passengers trying to buy airline tickets. The system is based on a relational database with its flight management and reservation functions. There are number of flights that go from/to different cities at different dates and times. Users of the system should be able to retrieve flight information between two given cities with the given dates times of travel from the database. A route from city A to city B is sequence of connecting flights from A to B such that: a) there are at most two connecting stops, excluding the starting city and destination city of the trip, b) the connecting time is between one to two hours. Each flight has a limited number of available seats. The system will support two types of user privileges, Customer and Employees. Customer will have access to customer functions making a new reservation (one-way, round-trip multi-city, flexible data time, and confirmation), canceling an existing reservation, and viewing his/her itinerary. The employees will have access to both customers and flight management functions. The flight customers who have seats reserved on a given fight, 2) all flights for a given airport, 3) flight roster and schedule, 4) get all flights whose arrival and departure times are on time delayed, and 5) total sales for a given flight, and administrating flight information by 1) dding a flight, deleting a flight adding a new airport, updating fare for flights, adding a new flight leg ating departure arrival tintes for flight leg instances instance, andExplanation / Answer
1. Create table flights(destination varchar2(20),stops number(2), ftime time, seats number(3),connecting_time time, constraint pk_flights primary key(destination, time, connecting_time) ;
2. Create table management(destination varchar2(20),customer varchar2(10) primary key ,roaster varchar2(7),schedule timestamp, delayed_flight date, sales number(10), foreign key(destination) references flights (destination)) ;
3.crete table reservation (customer varchar2(10), one_way char check (one_way in ('y' or 'n')), roundtrip char check (roundtrip in (' y' or 'n')), city varchar2(10),rtime time, rdate date, confirmation varchar2(5), foreign key(customer) references management (customer)) ;