create table flight (
flight_Ref_No VARCHAR(11) NOT NULL,
flightCrewList_ID NUMBER(10) NOT NULL,
aircraft_Model_No VARCHAR(6) NOT NULL,
flight_Date DATE NOT NULL,
flight_Time TIME NOT NULL,
Departure VARCHAR(50) NOT NULL,
Arrival VARCHAR(50) NOT NULL,
primary key(flight_Ref_No),
foreign key(flightCrewList_ID) references crew_list(C_List_ID),
foreign key(aircraft_Model_No) references aircraft(aircraft_Model_No),
constraint chk_flight_Ref_No check (REGEXP_LIKE(flight_Ref_No, '^[A-Z]dddd-dddd$')),
constraint chk_CrewList_No check (REGEXP_LIKE(flightCrewList_ID, '^dddddddddd$')),
constraint chk_aircraft_Model_No check (REGEXP_LIKE(aircraft_Model_No, '^[A-Z][A-Z]dddd$'))
);
create table aircraft (
aircraft_Model_No VARCHAR(6) NOT NULL,
aircraft_ID VARCHAR(10) NOT NULL,
flight_Hrs NUMBER(10,2) NOT NULL,
Registration_Num NUMBER(15) NOT NULL,
Manufacturer VARCHAR(20) NOT NULL,
Manufacture_Year NUMBER(4) NOT NULL,
Status CHAR(1) NOT NULL,
primary key(aircraft_Model_No, aircraft_ID),
constraint chk_aircraft_Model_No check (REGEXP_LIKE(aircraft_Model_No, '^[A-Z][A-Z]dddd$')),
constraint chk_aircraft_ID check (REGEXP_LIKE(aircraft_ID, '^[A-Z][A-Z][A-Z]ddddddd$')),
constraint chk_Status check (UPPER(Status) in ('A','M'))
);
create table maintenance (
maintenance_ID NUMBER(10) NOT NULL,
aircraft_ID VARCHAR(10) NOT NULL,
maintenance_Team VARCHAR(5) NOT NULL,
Damaged_Parts VARCHAR(50) NOT NULL,
Description VARCHAR(50) NOT NULL,
primary key(maintenance_ID),
foreign key(aircraft_ID) references aircraft(aircraft_ID),
constraint chk_maintenance_ID check (REGEXP_LIKE(maintenance_ID, '^dddddddddd$')),
constraint chk_aircraft_ID check (REGEXP_LIKE(aircraft_ID, '^[A-Z][A-Z][A-Z]ddddddd$'))
);
create table booking (
Acc_ID VARCHAR(10) NOT NULL,
booking_ID NUMBER(7) NOT NULL,
flight_Ref_No VARCHAR(11) NOT NULL,
flight_Class CHAR(1) NOT NULL,
Price NUMBER(10,2) NOT NULL,
primary key(Acc_ID, booking_ID),
foreign key(Acc_ID) references account(Acc_ID),
foreign key(flight_Ref_No) references flight(flight_Ref_No),
constraint chk_Acc_ID check (REGEXP_LIKE(Acc_ID, '^[A-Z][A-Z][A-Z][A-Z]--dddd$')),
constraint chk_flight_Ref_No check (REGEXP_LIKE(flight_Ref_No, '^[A-Z]dddd-dddd$')),
constraint chk_flight_Class check (UPPER(flight_Class) in ('E','P','B','F'))
);
create table passenger (
Passport VARCHAR(9) NOT NULL,
passenger_IC NUMBER(12) NOT NULL,
Seat_Num VARCHAR(3) NOT NULL,
First_Name VARCHAR(10) NOT NULL,
Last_Name VARCHAR(20) NOT NULL,
Gender CHAR(1) NOT NULL,
DOB DATE NOT NULL,
Nationality VARCHAR(3) NOT NULL,
Baggage_Weight NUMBER(4,2) NOT NULL,
primary key(Passport, passenger_IC),
constraint chk_Passport check (REGEXP_LIKE(Passport, '^[A-Z][0-9]+$')),
constraint chk_Seat_Num check (REGEXP_LIKE(Seat_Num, '^[A-Z]dd$')),
constraint chk_Gender check (UPPER(Gender) in ('M','F')),
constraint chk_Nationality check (REGEXP_LIKE(Nationality, '^[A-Za-z][A-Za-z][A-Za-z]$'))
);
create table discount (
discount_Rate NUMBER(1,2) NOT NULL,
Price NUMBER(10,2) NOT NULL,
Condition_discount VARCHAR(100),
primary key(discount_Rate),
foreign key(Price) references booking(Price)
);
create table account (
Acc_ID VARCHAR(10) NOT NULL,
Email VARCHAR(35) NOT NULL,
Password VARCHAR(20) NOT NULL,
Created_at DATE NOT NULL,
Last_login_at TIME NOT NULL,
Phone_Num NUMBER(11) NOT NULL,
Acc_Name VARCHAR(10) NOT NULL,
Age NUMBER(2) NOT NULL,
primary key(Acc_ID, booking_ID),
constraint chk_Acc_ID check (REGEXP_LIKE(Acc_ID, '^[A-Z][A-Z][A-Z][A-Z]--dddd$')),
constraint chk_Email check (REGEXP_LIKE(email,'^[a-zA-Z]w+@(S+)$'))
);
create table crew_list (
C_List_ID NUMBER(10) NOT NULL,
pilot_ID NUMBER(4) NOT NULL,
Co-pilot_ID NUMBER(4) NOT NULL,
Stewardess_1_ID NUMBER(4) NOT NULL,
Stewardess_2_ID NUMBER(4) NOT NULL,
Steward_1_ID NUMBER(4) NOT NULL,
Steward_2_ID NUMBER(4) NOT NULL,
primary key(C_List_ID),
foreign key(pilot_ID) references pilot(pilot_ID),
foreign key(Co-pilot_ID) references pilot(pilot_ID),
foreign key(Stewardess_1_ID) references crew_cabin(Crew_ID),
foreign key(Stewardess_2_ID) references crew_cabin(Crew_ID),
foreign key(Steward_1_ID) references crew_cabin(Crew_ID),
foreign key(Steward_2_ID) references crew_cabin(Crew_ID),
constraint chk_CrewList_No check (REGEXP_LIKE(C_List_ID, '^dddddddddd$'))
);
create table pilot (
pilot_ID NUMBER(4) NOT NULL,
pilot_Rank VARCHAR(10) NOT NULL,
Total_flight_Hrs NUMBER(10,2) NOT NULL,
pilot_Leave_Status CHAR(1) NOT NULL,
primary key(pilot_ID, pilot_Rank),
foreign key(pilot_ID) references worker(worker_ID),
constraint chk_pilot_Leave_Status check (UPPER(pilot_Leave_Status) in ('L','W'))
);
create table crew_cabin (
Crew_ID NUMBER(4) NOT NULL,
Crew_Rank VARCHAR(10) NOT NULL,
Working_Hrs NUMBER(10,2) NOT NULL,
Crew_Leave_Status CHAR(1) NOT NULL,
primary key(Crew_ID, Crew_Rank),
foreign key(Crew_ID) references worker(worker_ID),
constraint chk_Crew_Leave_Status check (UPPER(Crew_Leave_Status) in ('L','W'))
);
create table worker (
worker_ID NUMBER(4) NOT NULL,
section_ID NUMBER(2) NOT NULL,
W_First_Name VARCHAR(10) NOT NULL,
W_Last_Name VARCHAR(20) NOT NULL,
W_Gender CHAR(1) NOT NULL,
W_Age NUMBER(2) NOT NULL,
Date_of_Employment DATE NOT NULL,
W_Email VARCHAR(35) NOT NULL,
W_Phone_Num NUMBER(11) NOT NULL,
Salary NUMBER(6,2) NOT NULL,
primary key(worker_ID),
foreign key(section_ID) references section(section_ID),
constraint chk_worker_Gender check (UPPER(W_Gender) in ('M','F'),
constraint chk_worker_Email check (REGEXP_LIKE(email,'^[a-zA-Z]w+@(S+)$'))
);
create table section (
section_ID NUMBER(2) NOT NULL,
section_Name VARCHAR(20) NOT NULL,
Description VARCHAR(50) NOT NULL,
primary key(section_ID)
);
insert into section values(01, 'TESTING', 'TESTING');
commit;
The code above does not work at all, and i couldnt figure out why. As i was trying to run the script in Run SQL Command Line, oracle kept prompting the following messages
SP2-0734: unknown command beginning “primary ke…” – rest of line ignored.
SP2-0734: unknown command beginning “constraint…” – rest of line ignored.
SP2-0734: unknown command beginning “constraint…” – rest of line ignored.
SP2-0734: unknown command beginning “constraint…” – rest of line ignored.
SP2-0044: For a list of known commands enter HELP and to leave enter EXIT.
SP2-0042: unknown command “)” – rest of line ignored.
SP2-0734: unknown command beginning “primary ke…” – rest of line ignored.
SP2-0734: unknown command beginning “foreign ke…” – rest of line ignored.
SP2-0734: unknown command beginning “constraint…” – rest of line ignored.
SP2-0734: unknown command beginning “constraint…” – rest of line ignored.