代写relational database、代做SQL、代做Airline Staff、代写SQL语言程序

日期：2018-10-30 09:58

Objective:

The objective of this course project is to provide a realistic experience in the design

process of a relational database and corresponding applications. We will focus on

conceptual design, logical design, implementation, operation, maintenance of a

relational database. We will also implement an associated web based application to

communicate with the database (retrieve information, store information etc).

Project Overview:

The course project for this semester is online Air Ticket Reservation System. Using this

system, customers can search for flights, purchase flights ticket, view their upcoming

flight status or see their past flights etc. There will be three types of users of this system

- Customers, Booking Agents and Airline Staff (Administrator). Booking Agents will book

flights for other Customers, can get a fixed commission. They can view their monthly

reports and get total commission. Airline Staff will add new airplanes, create new flights,

and update flight status. In general this will be simple air ticket reservation system.

3 Parts of the Project:

1. WORK INDIVIDUALLY Create an ER diagram based on the description below.

(Already completed)

2. WORK INDIVIDUALLY Create a relational database design (relational Schema,

write table definitions in SQL, write some queries etc) based on ER diagram.

Deadline: 10/31/2018 11:55 pm.

3. Develop a web application for the system. You may work with a partner or

individually for this part.

Project Description

There are several airports (Airport), each consisting of a unique name and a city.

There are several airlines (Airline), each with a unique name. Each airline owns several

airplanes. An airplane (Airplane) consists of the airline that owns it, a unique

identification number within that airline, and the amount of seats on the airplane.

Each airline operates flights (Flight), which consist of the airline operating the flight,

unique a flight number within that airline, departure airport, departure time, arrival

airport, arrival time, a price, and the identification number of the airplane for the flight.

A ticket (Ticket) can be purchased for a flight either by a Customer or Booking Agent

(more on these below), and will consist of the customer’s email address, the airline

name, the flight number, and a booking_agent_ID. If a Booking Agent purchases the

ticket then their booking_agent_ID will be used, and if a Customer purchases the ticket

then the booking_agent_ID should be null. Each ticket will have a ticket ID number

which is unique in this System.

Anyone (including users not signed in) can see upcoming flights based on the source

airport, destination airport, source city, or destination city. Additionally, anyone can see

the status (delayed/on time etc) of in progress flights based on an airline and flight

number combination.

There are three types of users for this system: Customer, Booking Agent, and Airline

Staff.

Customer:

Each Customer has a name, email, password, address (composite attribute consisting of

building_number, street, city, state), phone_number, passport_number,

passport_expiration, passport_country, and date_of_birth. Each Customer’s email is

unique, and they will sign into the system using their email address and password.

Customers must be logged in to purchase a flight ticket.

Customers can purchase a ticket for a flight as long as there is still room on the plane.

This is based on the amount of tickets already booked for the flight and the seating

capacity of the airplane assigned to the flight and customer needs to pay the associated

price for that flight (to make it simple, we will consider that all tickets’ price of a flight is

fixed). No credit number or other related information is necessary.

Customer will be able to see their upcoming flights or previous flights taken for the

airline they logged in.

Booking Agent:

The role of a Booking Agent is similar to that of a Customer. A Booking Agent’s purpose

is to purchase a ticket on behalf of a Customer (with the same restrictions of seat

availability as above), but that Booking Agent will receive a 10% commission from the

ticket price.

A Booking Agent consists of a unique email, a password, and a booking_agent_ID. In

order for a Booking Agent to sign into the system, they must enter all three of these

items.

Once logged in, a Booking Agent will be able to see the amount of commission they

received in the past 30 days, the average commission they received per ticket booked,

and the total number of tickets they booked.

Airline Staff:

Each Airline Staff has a unique username, a password, a first name, a last name, a date

of birth, and the airline name that they work for. Airline Staff work for one particular

airline.

Airline Staff will be able to add new airplanes into the system for the airline they work

for.

Airline Staff will set in progress flight statuses in the system.

Each Airline Staff can create new flights only for the particular airline that they work for

and set the ticket price for flight. They will also be able to see all in-progress, upcoming,

and previous flights for the airline that they work for, as well as a list of passengers for

the flights.

In addition, Airline Staff will be able to see a list of all flights a particular Customer has

taken only on that particular airline.

Airline Staff will also be able to see the most frequent customer within the last year, see

the amount of tickets sold each month, and see the top 5 Booking Agents for the past

month and past year based on sales.

Airline Staff can query for how many flights get delayed/on-time etc.

What You Should Do for Part 1 (already completed):

Design an ER diagram for online Air Ticket Reservation System described above. Draw

the ER diagram neatly. You may draw it by hand or using a design tool.

Deadline: 10/16/2018 11:55 pm. (Already passed)

When you do this, think about: which information should be represented as attributes,

which as entity sets or relationship sets? Are any of the entity sets weak entity sets? If

so, what is the identifying strong entity set? What is the primary keys (or discriminant)

of each entity set? What are the cardinality constraints on the relationship sets?

The total project grade will be 30% of your course grade. Part 1 counts for about 20% of

the project grade. There may also be a quiz or exam question(s) based on the project.

What You Should Do for Part 2

You must use the posted Solution to Part 1 for part 2. First, compare your

solution to the posted solution and think about what you did correctly, what you did

incorrectly, and what aspects could have been modeled correctly either with your ER or

with the one we've posted.

1. Following the techniques we studied, derive a relational schema diagram from the

the posted Solution to Part 1 ER diagram. Remember to underline primary keys and

use arrows from the referencing schema to the referenced schema to indicate foreign

key constraints.

2. Write and execute SQL CREATE TABLE statements to create the tables. Choose

reasonable types for the attributes.

3. Write and execute INSERT statements to insert data representing one airline's air

ticket reservation system. As for example, you can insert data in the appropriate tables

as follows or you can insert data for other airline or your own make up airline:

a. One Airline name "China Eastern".

b. At least Two airports named "JFK" in NYC and "PVG" in Shanghai.

c. Insert at least two customers with appropriate names and other attributes.

Insert one booking agent with appropriate name and other attributes.

d. Insert at least two airplanes.

e. Insert At least One airline Staff working for China Eastern.

f. Insert several flights with upcoming, in-progress, delayed statuses.

g. Insert some tickets for corresponding flights. One customer buy ticket directly

and one customer buy ticket using a booking agent.

4. Write SQL queries for executing following queries and show the results in your file

(SQL query and corresponding answers):

a. Show all the upcoming flights in the system.

b. Show all of the delayed flights in the system.

c. Show the customer names who used booking agent to buy the tickets.

d. Show all of the airplanes owned by the airline (such as "Emirates")

Part 2 needs to be completed individually. Submit a PDF file for Relational Schema

diagram and one .SQL file for 2 (create table statements), one .SQL file for 3 (inserting

data in the database), one .SQL file for 4 (SQL queries and corresponding results) via

NYU Classes.

Deadline: 10/31/2018 11:55 pm.

The total project grade will be 30% of your course grade. Part 2 counts for about 20% of

the project grade. There may also be a quiz or exam question(s) based on the project.