6.1 Опис таблиць бази даних з обмеженнями цілісності

CREATE TABLE aircrafttype (

Name VARCHAR(50) CONSTRAINT c1 PRIMARY KEY,

PlaceCount INTEGER CONSTRAINT c2 NOT NULL,

CarCapacity INTEGER CONSTRAINT c3 NOT NULL

);

CREATE TABLE aricompany (

Name VARCHAR(100) CONSTRAINT c4 PRIMARY KEY

);

CREATE TABLE reg_organ (

Name VARCHAR(100) CONSTRAINT c5 PRIMARY KEY

);

CREATE TABLE registration (

ID INTEGER CONSTRAINT c6 PRIMARY KEY,

RegDate DATE CONSTRAINT c7 NOT NULL,

Place VARCHAR(50) CONSTRAINT c8 NOT NULL

Owner VARCHAR(70) CONSTRAINT c19 NOT NULL

Ag_sign VARCHAR(10) CONSTRAINT c9 CHECK (Ag_sign in (`Yes, `No)),

OrganName VARCHAR(100) CONSTRAINT c10 REFERENCES reg_organ(Name) ON DELETE CASCADE

);

CREATE TABLE aircraft (

Hull_No INTEGER CONSTRAINT c11 PRIMARY KEY

ac_type VARCHAR(50) CONSTRAINT c12 REFERENCES aircrafttype (Name) ON DELETE CASCADE

compnyname VARCHAR(100) CONSTRAINT c13 REFERENCES aircompany(Name) ON DELETE CASCADE

RegID INTEGER CONSTRAINT c14 REFERENCES registration (ID) ON DELETE CASCADE

);

CREATE TABLE service ( //служба

Name VARCHAR(50) CONSTRAINT c15 PRIMARY KEY

);

CREATE TABLE serv ( //обслуживание

srvDate DATE CONSTRAING c16 PRIMARY KEY

result VARCHAR(20) CONSTRAINT NOT NULL

servName VARCHAR(50) CONSTRAINT c17 REFERENCES service(Name) ON DELETE CASCADE

Hull_No INTEGER CONSTRAINT c18 REFERENCES aircraft(Hull_No) ON DELETE CASCADE

);