Should have has many

Should have

Makes has many

supervises

pays 2. Relational data structures that transfers your ER diagrams

LOGIN(Loginname,loginid,Password)
STAFF(Firstname,Lastname,Staffid,Street,city,state,emailid)
CUSTOMER(Firstname,lastname,customerid,gender,address,dob,phoneno,mobileno,workphnno)
FLIGHTDETAIL(flightno,no.of seats,city of departure,city of arrival,time of departure,time of arrival,price)
TICKET(ticketno,dateofissue,flightinformation,cost)
INSURANCE(companyname,policyno,nameofcustomer)
PAYMENT(etps,creditcard,cheque,paymentno,dateofpayment)

NOTE:Relational datastructures LOGIN is in 3NF,data structure STAFF satisfies 1NF and 2NF to satisfy 3normalisation form table should decompose into 2 ,2nd table containing code and phoneno,similarly in TICKET database structure cost is dependant on flightinformation so it should decomposed,remaining database structures CUSTOMER,INSURANCE,PAYMENT is in 3rd normalisation form(3NF).

ASSUMPTIONS
LOGIN:
Staff should have mandatory login id and password to enter into reservations site of AAA travels and to service customers.
Customer should have valid userid and password to make reservations and check details regarding flights.
STAFF:
Staff has optional for dealing with customers because some staff supervise other staff.
Staff can reserve ticket for many customers.
Customer should have mandatory staff to reserve there ticket.
CUSTOMER:
Customer can search for flight details to reserve a ticket it is mandatory.
Customer may reserve non or reserve many tickets it is an optional.
Ticket is mandatory to the customer to travel.
Customer may have insurance or can have many insurance it’s an optional to customer.
Customer has option to choose his/her age by child 1or child 2 yo get discount.
PAYMENT:
Customer can choose payment method from the given it is optional to choose, but payment is mandatory to customer to reserve ticket for travelling.
Customer can choose charges depending upon the age because different age group has different charges.

3. A relational database schema

Table Name Field Type Descrption
Login lastName varchar(30) Password varchar(30 ) Primarykey Loginid varchar(20) Primarykey

Staff Firstname varchar(30) Lastname varchar(30) Street varchar(30) city varchar(30) state varchar(30) postcode int(10) phoneno int(15) primarykey emailid varchar(30) staffid varchar(20) primarykey

customer firstname varchar(30) lastname varchar(30) gender char(5) address varchar(30) emailaddress varchar(20) dob varchar(10) mobileno int(15) primarykey customerid varchar(20) primarykey

Flightdetail flightno varchar(20) primarykey No.of seats int(10) City of departure varchar(10) cityofarrival varchar(10) timeofdeparture int(10) timeofarrival int(10) price int(10) Ticket Ticketno int(10) primarykey dateofissue int(10) flightinformation varchar(30) cost int(20) customerid int(20) foriegnkey

Insurance companyname varchar(20) policyno varchar(10) primarykey customername varchar(20)

payment ETPS varchar(10) creditcard cheque paymentno varchar(20) payment dateofpayment int(10) charge int(10) ticketno int(10) foriegnkey