23.03.2011 - Exercise1 - Unit4

23.03.2011-Exercise1-Unit4.sql

SET SCHEMA FN71202;

CREATE TABLE PRODUCT(
	NAME VARCHAR(15),
	CREATOR VARCHAR(15),
	QUANTITY INTEGER,
	PRODUCT_CODE VARCHAR(5) PRIMARY KEY NOT NULL
);

DROP TABLE PRINTER;

CREATE TABLE PRINTER(
	MODEL VARCHAR(15),
	COLOR VARCHAR(1) DEFAULT 'n',
	 CHECK (COLOR IN ('n','c')),
	PRODUCT_ID VARCHAR(5) REFERENCES 
	PRODUCT(PRODUCT_CODE)
	ON DELETE CASCADE
	ON UPDATE RESTRICT
);

ALTER TABLE PRINTER DROP COLOR;

--SECOND EXERCISE--

CREATE TABLE CLASSES (
	CLASS INTEGER NOT NULL PRIMARY KEY,
	TYPE VARCHAR(50),
	COUNTRY VARCHAR(50),
	NUMGUNS INTEGER,
	DISPLACEMENT DOUBLE
);

CREATE TABLE SHIPS (
	NAME VARCHAR(50) NOT NULL PRIMARY KEY,
	CLASS INTEGER,
	LAUNCHED DATE DEFAULT CURRENT_DATE,
	FOREIGN KEY (CLASS)
	REFERENCES CLASSES(CLASS)
	ON DELETE NO ACTION
);

CREATE TABLE BATTLES (
	NAME VARCHAR(50) NOT NULL PRIMARY KEY,
	DATE DATE DEFAULT CURRENT_DATE
);

CREATE TABLE OUTCOMES (
	SHIP VARCHAR(50) NOT NULL,
	BATTLE VARCHAR(50) NOT NULL,
	PRIMARY KEY (SHIP, BATTLE),
	FOREIGN KEY (SHIP)
	REFERENCES SHIPS(NAME)
	ON DELETE CASCADE,
	FOREIGN KEY (BATTLE)
	REFERENCES BATTLES(NAME)
	ON DELETE CASCADE
);

DROP TABLE SHIPS;

DROP TABLE OUTCOMES;

DROP TABLE BATTLES;

DROP TABLE CLASSES;

--Facebook--

CREATE TABLE USERS (
	USER_ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
	EMAIL VARCHAR(32),
	PASSWORD VARCHAR(32),
	REGISTRATION_DATE DATE DEFAULT CURRENT_DATE	
);

CREATE TABLE FRIENDS (
	FIRST_USER INTEGER NOT NULL,
	SECOND_USER INTEGER NOT NULL,
	PRIMARY KEY (FIRST_USER, SECOND_USER),
	FOREIGN KEY (FIRST_USER)
	REFERENCES USERS(USER_ID)
	ON DELETE CASCADE,
	FOREIGN KEY (FIRST_USER)
	REFERENCES USERS(USER_ID)
	ON DELETE CASCADE
);

CREATE TABLE WALL (
	BELONGS_TO INTEGER NOT NULL,
	POSTED_BY INTEGER NOT NULL,
	CONTENTS VARCHAR(1000),
	POST_DATE DATE DEFAULT CURRENT_DATE NOT NULL,
	PRIMARY KEY (BELONGS_TO, POSTED_BY, POST_DATE),
	FOREIGN KEY (BELONGS_TO)
	REFERENCES USERS(USER_ID)
	ON DELETE CASCADE,
	FOREIGN KEY (POSTED_BY)
	REFERENCES USERS(USER_ID)
	ON DELETE CASCADE
);

CREATE TABLE GROUPS (
	GROUP_ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
	NAME VARCHAR(30),
	CONTENTS VARCHAR(1000) DEFAULT ''
);

CREATE TABLE GROUPMEMBERS (
	GROUP_ID INTEGER NOT NULL,
	USER_ID INTEGER NOT NULL,
	PRIMARY KEY (GROUP_ID, USER_ID),
	FOREIGN KEY (GROUP_ID)
	REFERENCES GROUPS(GROUP_ID)
	ON DELETE CASCADE,
	FOREIGN KEY (USER_ID)
	REFERENCES USERS(USER_ID)
	ON DELETE CASCADE
);

ALTER TABLE FRIENDS ADD 
	CONFIRMED CHAR(1) CHECK (CONFIRMED IN ('t', 'f')) DEFAULT 'f';

DROP TABLE FRIENDS;

DROP TABLE GROUPMEMBERS;

DROP TABLE GROUPS;

DROP TABLE USERS;

DROP TABLE WALL