29.03.2011 - Exercise1 - Unit3

29.03.2011-Exercise1-Unit3.sql

--Практикум--

SET SCHEMA FN71202;

CREATE TABLE DEPARTMENT (

	NUMBER INT NOT NULL PRIMARY KEY,
	NAME VARCHAR(30)
	
);

CREATE TABLE EMPLOYEE (

	ID INT NOT NULL PRIMARY KEY,
	NAME VARCHAR(30),
	PHONE CHAR(10),
	DEPARTMENT_ID INTEGER,
	FOREIGN KEY (DEPARTMENT_ID)
	REFERENCES DEPARTMENT(NUMBER)

);

CREATE TABLE PROJECT (

	NUMBER INTEGER NOT NULL PRIMARY KEY,
	NAME VARCHAR(30)
	
);

CREATE TABLE EMPLOYEE_PROJECT (
	
	PROJECT_ID INTEGER NOT NULL,
	EMPLOYEE_ID INTEGER NOT NULL,
	FOREIGN KEY (PROJECT_ID)
	REFERENCES PROJECT(NUMBER),
	FOREIGN KEY (EMPLOYEE_ID)
	REFERENCES PROJECT(ID),
	PRIMARY KEY (PROJECT_ID, EMPLOYEE_ID)
	
);

DROP TABLE EMPLOYEE;

DROP TABLE PROJECT;

DROP TABLE DEPARTMENT;

DROP TABLE EMPLOYEE_PROJECT;

CREATE TABLE CUSTOMERS (

	SSN VARCHAR(50) NOT NULL PRIMARY KEY,
	NAME VARCHAR(50),
	PHONE CHAR(10),
	ADDRESS VARCHAR(50)

);

CREATE TABLE FLIGHTS (

	NUMBER INTEGER NOT NULL PRIMARY KEY,
	DAY DATE,
	AIRCRAFT VARCHAR(50),
	COMPANY VARCHAR(50)

);

CREATE TABLE FLIGHTS_CUSTOMERS (

	FLIGHT_NUMBER INTEGER NOT NULL,
	CUSTOMER_SSN VARCHAR(50) NOT NULL,
	PRIMARY KEY (CUSTOMER_SSN, FLIGHT_NUMBER),
	FOREIGN KEY (CUSTOMER_SSN)
	REFERENCES CUSTOMERS (SSN),
	FOREIGN KEY (FLIGHT_NUMBER)
	REFERENCES FLIGHTS (NUMBER)

);

DROP TABLE FLIGHTS;

DROP TABLE CUSTOMERS;

DROP TABLE FLIGHTS_CUSTOMERS;

CREATE TABLE BOOKS (

	ISBN CHAR(13) NOT NULL PRIMARY KEY,
	NAME VARCHAR(100),
	AUTHOR_NAME VARCHAR(100),
	YEAR INTEGER,
	FOREIGN KEY (AUTHOR_NAME)
	REFERENCES AUTHOR(NAME)

);

CREATE TABLE AUTHOR (

	NAME VARCHAR(100) NOT NULL PRIMARY KEY

);

CREATE TABLE STUDENTS (

	FN INTEGER NOT NULL PRIMARY KEY,
	NAME VARCHAR(100)

);

CREATE TABLE STUDENTS_BOOKS (

	ISBN CHAR(13) NOT NULL,
	FN INTEGER NOT NULL,
	PRIMARY KEY (ISBN, FN),
	FOREIGN KEY (ISBN)
	REFERENCES BOOKS(ISBN),
	FOREIGN KEY (FN)
	REFERENCES STUDENTS(FN)

);

DROP TABLE STUDENTS;

DROP TABLE BOOKS;

DROP TABLE STUDENTS_BOOKS;

DROP TABLE AUTHOR