I Tried creating SQL statements with corresponding relations, including primary,

Question

I Tried creating SQL statements with corresponding relations, including primary, unique and foreign keys using SQL workbench, in relation with the ER diagram. I'm asking to see if someone can shows me how to load each table and give an example of each table. Also want ot make sure my sql statements are correct and if you can improvise it.

DROP DATABASE IF EXISTS AirRes;
CREATE DATABASE AirRes;
USE AirRes;

CREATE TABLE FEEDBACK(
fid SMALLINT NOT NULL AUTO_INCREMENT,
name varchar(40),
comment VARCHAR(255) NOT NULL,
rating VARCHAR(30) NOT NULL,
CONSTRAINT PK_FEEDBACK_fid PRIMARY KEY (fid)
);

ALTER TABLE FEEDBACK AUTO_INCREMENT = 10000;

CREATE TABLE DATATYPE_NAME(
fname VARCHAR(20),
midinitial VARCHAR(1),
lname VARCHAR(20));

CREATE TABLE CUSTOMER (
cid INT NOT NULL AUTO_INCREMENT,
name varchar(40),
email VARCHAR(30) NOT NULL,
phone CHAR(10) NOT NULL,
passwords VARCHAR(30) NOT NULL,
CONSTRAINT PK_Customer_cid PRIMARY KEY (cid)
);

ALTER TABLE CUSTOMER AUTO_INCREMENT = 1000000000;

CREATE TABLE CUSTOMER_FEEDBACK (
cfid Char(15),
cid INT,
fid SMALLINT,
PRIMARY KEY(cfid),
FOREIGN KEY(cid) REFERENCES CUSTOMER(cid),
FOREIGN KEY(fid) REFERENCES FEEDBACK(fid)
);

CREATE TABLE AIRLINES (
alid char(10) NOT NULL,
name VARCHAR(50) NOT NULL,
phone CHAR(10) NOT NULL,
CONSTRAINT PK_AIRLINES_alid
PRIMARY KEY (alid),
UNIQUE KEY (name)
);

CREATE TABLE AIRPLANES (
resNo Char(5) NOT NULL,
type VARCHAR(50) NOT NULL,
CONSTRAINT PK_AIRPLANES_resNo PRIMARY KEY (resNo)
);

CREATE TABLE AIRLINES_AIRPLANES (
ownid char(15) NOT NULL,
alid char(10),
resNo char(5) ,
PRIMARY KEY(ownid),
FOREIGN KEY (alid) REFERENCES AIRLINES(alid),
FOREIGN KEY (resNo) REFERENCES AIRPLANES(resNo)
);

CREATE TABLE AIRPORT (
code char(10) NOT NULL,
city VARCHAR(30) NOT NULL,
state VARCHAR(30) NOT NULL,
name VARCHAR(50) NOT NULL,
CONSTRAINT PK_AIRPORT_code
PRIMARY KEY(code),
UNIQUE KEY(name)
);

CREATE TABLE AIRLINES_AIRPORT (
landid char(15) NOT NULL,
alid char(10),
code char(5),
status varchar(10),
lday varchar(10),
ltime TIMESTAMP(0),
PRIMARY KEY (landid),
FOREIGN KEY (alid) REFERENCES AIRLINES(alid),
FOREIGN KEY (code) REFERENCES AIRPORT(code)
);

CREATE TABLE ORDERS (
oid char(10) NOT NULL,
expDate date NOT NULL,
cvv char(5) NOT NULL,
nameOfCardHolder varchar(50) NOT NULL,
billingAddress varchar(255) NOT NULL,
creditCardNum char(20) NOT NULL,
creditCardType varchar(20) NOT NULL,
confirmNum char(10) NOT NULL,
CONSTRAINT PK_ORDER_oid PRIMARY KEY(oid)
);

CREATE TABLE ORDERS_CUSTOMER (
oid Char(10),
cid INT,
FOREIGN KEY (oid) REFERENCES ORDERS(oid),
FOREIGN KEY (cid) REFERENCES CUSTOMER(cid)
);

CREATE TABLE FLIGHT (
fNum Char(10) NOT NULL,
departureTime timestamp(0) NOT NULL,
departureDate date NOT NULL,
planeType varchar(15),
capacity Char(5) NOT NULL,
CONSTRAINT PK_FLIGHT_fNum PRIMARY KEY(fNum)
);

CREATE TABLE PASSENGER (
pid Char(10) NOT NULL,
dob Date NOT NULL,
name VARCHAR(30) NOT NULL,
address VARCHAR(50) NOT NULL,
pic BLOB,
CONSTRAINT PK_PASSENGER_pid PRIMARY KEY(pid)
);

CREATE TABLE RESERVATION (
oid Char(10),
fNum Char(10),
pid Char(10),
FOREIGN KEY (oid) REFERENCES ORDERS(oid),
FOREIGN KEY (fNum) REFERENCES FLIGHT(fNum),
FOREIGN KEY (pid) REFERENCES PASSENGER(pid)
);

CREATE TABLE BOARDING (
fNum Char(10),
pid Char(10),
status Char(1) DEFAULT 0 NOT NULL,
FOREIGN KEY (fNum) REFERENCES FLIGHT(fNum),
FOREIGN KEY (pid) REFERENCES PASSENGER(pid)
);

CREATE TABLE SEAT (
seatNo Char(10) NOT NULL,
section varchar(20) NOT NULL,
priorities varchar(20),
CONSTRAINT PK_SEAT_seatNo
PRIMARY KEY(seatNo)
);

CREATE TABLE ACCOMODATION (
fNum Char(10),
seatNo Char(10),
FOREIGN KEY (fNum) REFERENCES FLIGHT(fNum),
FOREIGN KEY (seatNo) REFERENCES SEAT(seatNo)
);

NameOfCar dholder billinglde LName expDate CreCarc DOE 1,placé order IC word addres: CreCar dType customerphre ConfirmNun emil passenger cIC provide board capaxit flight acc omockhte planeType seatic FNun feecback depar tur eDate sec ? depar tur eTi e FIC c ommEnt belongs tc r ating code air por t Lta me rsNo type Lday

Explanation / Answer

I will start with the mapping to Customer and Feedback entity of the ER diagram. Both these entities are showing 1:M relationship. Every feedback is associated with one customer and each customer can submit multiple feedback. So this way table feedback will have field cid which will references to the cid of customer table.

In table Customer name is shown as composite attribute. When converting composite to relational table name will be eliminated and only the attribute fname, midinitial and lname will remain in the table.

CREATE TABLE CUSTOMER (

cid   INT NOT NULL AUTO_INCREMENT,

fname VARCHAR(20),

midinitial VARCHAR(1),

lname VARCHAR(20)email     VARCHAR(30) NOT NULL,

phone CHAR(10) NOT NULL,

passwords VARCHAR(30) NOT NULL,

CONSTRAINT PK_Customer_cid PRIMARY KEY (cid)

);

ALTER TABLE CUSTOMER AUTO_INCREMENT = 1000000000;

CREATE TABLE FEEDBACK(

fid   SMALLINT NOT NULL AUTO_INCREMENT,

name varchar(40),

comment   VARCHAR(255) NOT NULL,

rating VARCHAR(30) NOT NULL,

cid INT,

CONSTRAINT PK_FEEDBACK_fid PRIMARY KEY (fid),

CONSTRAINT FK_cust FOREIGN KEY (cid) REFERENCES CUSTOMER (cid)

);

ALTER TABLE FEEDBACK AUTO_INCREMENT = 10000;

# For this part again Entity AIRLINES and AIRPORT are showing 1:M relationship. Every                 # AIRPLANES is associated with a AIRLINES.

CREATE TABLE AIRLINES (

alid char(10) NOT NULL,

name VARCHAR(50) NOT NULL,

phone CHAR(10) NOT NULL,

CONSTRAINT PK_AIRLINES_alid

PRIMARY KEY (alid),

UNIQUE KEY (name)

);

CREATE TABLE AIRPLANES (

resNo   Char(5) NOT NULL,

type VARCHAR(50) NOT NULL,

alid char(10),

CONSTRAINT PK_AIRPLANES_resNo PRIMARY KEY (resNo),

CONSTRAINT FK_Airline FOREIGN KEY (alid) REFERENCES AIRLINES (alid)

);

CREATE TABLE AIRPORT (

code   char(10) NOT NULL,

city VARCHAR(30) NOT NULL,

state VARCHAR(30) NOT NULL,

name VARCHAR(50) NOT NULL,

CONSTRAINT PK_AIRPORT_code PRIMARY KEY(code),

UNIQUE (name)

);

CREATE TABLE LAND (

   resNo char(10),

   code char(5),

   status varchar(10),

   lday varchar(10),

   ltime TIMESTAMP(0),

   PRIMARY KEY (resNo, code),

   FOREIGN KEY (resNo) REFERENCES AIRPLANES(resNo),

   FOREIGN KEY (code) REFERENCES AIRPORT(code)

);

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.