Scribd
Upload
19 views2 pages

SQL Table Creation and Constraints

The document outlines SQL commands to create tables for a project, including airports, customers, employees, facilities, flights, payments, planes, reservations, and tickets. It also specifies primary and foreign key constraints for data integrity and checks for specific status and method values. The structure ensures relationships between different entities in the database are maintained properly.

Uploaded by

Mohsen Ebrahim
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
19 views2 pages

SQL Table Creation and Constraints

The document outlines SQL commands to create tables for a project, including airports, customers, employees, facilities, flights, payments, planes, reservations, and tickets. It also specifies primary and foreign key constraints for data integrity and checks for specific status and method values. The structure ensures relationships between different entities in the database are maintained properly.

Uploaded by

Mohsen Ebrahim
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

CREATE TABLE PROJ_AIRPORTS AS SELECT * FROM project25.

airports;
CREATE TABLE PROJ_CUSTOMERS AS SELECT * FROM [Link];
CREATE TABLE PROJ_EMPLOYEES AS SELECT * FROM [Link];
CREATE TABLE PROJ_FACILITIES AS SELECT * FROM [Link];
CREATE TABLE PROJ_FLIGHTS AS SELECT * FROM [Link];
CREATE TABLE PROJ_PAYMENTS AS SELECT * FROM [Link];
CREATE TABLE PROJ_PLANES AS SELECT * FROM [Link];
CREATE TABLE PROJ_RESERVATIONS AS SELECT * FROM [Link];
CREATE TABLE PROJ_TICKETS AS SELECT * FROM [Link];

ALTER TABLE PROJ_CUSTOMERS


ADD CONSTRAINT CUST_CID_PK PRIMARY KEY (CustomerID);

ALTER TABLE PROJ_EMPLOYEES


ADD CONSTRAINT EMP_EID_PK PRIMARY KEY (EmployeeID);

ALTER TABLE PROJ_AIRPORTS


ADD CONSTRAINT ARP_AID_PK PRIMARY KEY (AirportID);

ALTER TABLE PROJ_PLANES


ADD CONSTRAINT PLN_PID_PK PRIMARY KEY (PlaneID);

ALTER TABLE PROJ_FLIGHTS


ADD (
CONSTRAINT FLT_FID_PK PRIMARY KEY (FlightID),
CONSTRAINT FLT_PID_FK FOREIGN KEY (PlaneID) REFERENCES PROJ_PLANES (PlaneID),
CONSTRAINT FLT_DEPID_FK FOREIGN KEY (DepartureAirportID) REFERENCES PROJ_AIRPORTS
(AirportID),
CONSTRAINT FLT_ARRID_FK FOREIGN KEY (ArrivalAirportID) REFERENCES PROJ_AIRPORTS
(AirportID),
CONSTRAINT FLT_STS_CHK CHECK (Status IN ('Scheduled', 'Cancelled', 'Completed'))
);

ALTER TABLE PROJ_RESERVATIONS


ADD (
CONSTRAINT RES_RID_PK PRIMARY KEY (ReservationID),
CONSTRAINT RES_CID_FK FOREIGN KEY (CustomerID) REFERENCES PROJ_CUSTOMERS
(CustomerID),
CONSTRAINT RES_FID_FK FOREIGN KEY (FlightID) REFERENCES PROJ_FLIGHTS (FlightID),
CONSTRAINT RES_EID_FK FOREIGN KEY (EMPLOYEEID) REFERENCES PROJ_EMPLOYEES
(EMPLOYEEID),
CONSTRAINT RES_STS_CHK CHECK (ReservationStatus IN ('Pending', 'Confirmed',
'Cancelled'))
);

ALTER TABLE PROJ_PAYMENTS


ADD (
CONSTRAINT PYMT_PID_PK PRIMARY KEY (PaymentID),
CONSTRAINT PYMT_RID_FK FOREIGN KEY (ReservationID) REFERENCES PROJ_RESERVATIONS
(ReservationID),
CONSTRAINT PYMT_STS_CHK CHECK (PaymentStatus IN ('Pending', 'Completed',
'Failed')),
CONSTRAINT PYMT_TYP_CHK CHECK (PaymentMethod IN ('Credit Card', 'Debit Card',
'PayPal', 'Cash'))
);

ALTER TABLE PROJ_FACILITIES


ADD (
CONSTRAINT FCLTY_FID_PK PRIMARY KEY (FacilityID),
CONSTRAINT FCLTY_ARPID_FK FOREIGN KEY (AirportID) REFERENCES PROJ_AIRPORTS
(AirportID)
);

ALTER TABLE PROJ_TICKETS


ADD (
CONSTRAINT TKT_TID_PK PRIMARY KEY (TicketID),
CONSTRAINT TKT_RESID_FK FOREIGN KEY (ReservationID) REFERENCES PROJ_RESERVATIONS
(ReservationID)
);

You might also like