Code: Select all
/*================*
* glogicaldm.sql *
* (mySQL) *
*================*/
/*===========*
* RELATIONS *
*===========*/
-- =========
-- BRANCH
-- =========
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE Branch;
CREATE TABLE Branch
(branch_no CHARACTER(4) PRIMARY KEY,
street CHARACTER VARYING(25),
city CHARACTER VARYING(15),
postcode CHARACTER(10) UNIQUE, -- ALTERNATE KEY
mgr_staff_no CHARACTER(5)); -- FOREIGN KEY
-- ===========
-- TELEPHONE
-- ===========
DROP TABLE Telephone;
CREATE TABLE Telephone
(tel_no CHARACTER VARYING(13) PRIMARY KEY,
branch_no CHARACTER(4)); -- FOREIGN KEY
-- ===========
-- STAFF
-- ===========
DROP TABLE Staff;
CREATE TABLE Staff
(staff_no CHARACTER(5) PRIMARY KEY,
fname CHARACTER VARYING(20),
lname CHARACTER VARYING(20),
position_ CHARACTER VARYING(20),
sex CHARACTER,
dob DATE,
salary DECIMAL(9,2),
supervisor_staff_no CHARACTER(5), -- FOREIGN KEY
branch_no CHARACTER(4)); -- FOREIGN KEY
-- ===========
-- MANAGER
-- ===========
DROP TABLE Manager;
CREATE TABLE Manager
(staff_no CHARACTER(5) PRIMARY KEY, -- FOREIGN KEY
mgr_start_date DATE,
bonus INTEGER);
-- ===============
-- PRIVATE OWNER
-- ===============
DROP TABLE Private_Owner;
CREATE TABLE Private_Owner
(owner_no CHARACTER(5) PRIMARY KEY,
fname CHARACTER VARYING(20),
lname CHARACTER VARYING(20),
address CHARACTER VARYING(50),
tel_no CHARACTER VARYING(13));
-- ================
-- BUSINESS OWNER
-- ================
DROP TABLE Business_Owner;
CREATE TABLE Business_Owner
(owner_no CHARACTER(5) PRIMARY KEY,
bname CHARACTER VARYING(20) UNIQUE, -- ALTERNATE KEY
btype CHARACTER VARYING(30),
contact_name CHARACTER VARYING(30),
address CHARACTER VARYING(50),
tel_no CHARACTER VARYING(13) UNIQUE); -- ALTERNATE KEY
-- ====================
-- PROPERTY_FOR_RENT
-- ====================
DROP TABLE Property_For_Rent;
CREATE TABLE Property_For_Rent
(property_no CHARACTER(5) PRIMARY KEY,
street CHARACTER VARYING(25),
city CHARACTER VARYING(15),
postcode CHARACTER VARYING(9),
type CHARACTER VARYING(15),
rooms INTEGER,
rent NUMERIC(9,2),
owner_no CHARACTER(5), -- FOREIGN KEY
staff_no CHARACTER(5), -- FOREIGN KEY
branch_no CHARACTER(4)); -- FOREIGN KEY
-- ==========
-- VIEWING
-- ==========
DROP TABLE Viewing;
CREATE TABLE Viewing
(client_no CHARACTER(5) NOT NULL, -- FOREIGN KEY
property_no CHARACTER(5) NOT NULL, -- FOREIGN KEY
view_date DATE,
comment CHARACTER VARYING(50),
PRIMARY KEY (client_no, property_no));
-- =========
-- CLIENT
-- =========
DROP TABLE Client;
CREATE TABLE Client
(client_no CHARACTER(5) PRIMARY KEY,
fname CHARACTER VARYING(20),
lname CHARACTER VARYING(20),
tel_no CHARACTER VARYING(13),
pref_type CHARACTER VARYING(15),
max_rent NUMERIC(9,2));
-- ==============
-- REGISTRATION
-- ==============
DROP TABLE Registration;
CREATE TABLE Registration
(client_no CHARACTER(5) PRIMARY KEY, -- FOREIGN KEY
branch_no CHARACTER(5), -- FOREIGN KEY
staff_no CHARACTER(4), -- FOREIGN KEY
date_joined DATE);
-- ========
-- LEASE
-- ========
DROP TABLE Lease;
CREATE TABLE Lease
(lease_no CHARACTER(8) PRIMARY KEY,
payment_method CHARACTER VARYING(15),
deposit_paid BOOL,
rent_start DATE,
rent_finish DATE,
client_no CHARACTER(5), -- FOREIGN KEY
property_no CHARACTER(5), -- FOREIGN KEY
UNIQUE (property_no, rent_start), -- ALTERNATE KEY
UNIQUE (client_no, rent_start)); -- ALTERNATE KEY
-- =======================
-- LEASE TEMPORARY TABLE
-- =======================
CREATE TEMPORARY TABLE Lease_Temp
AS SELECT L.*, (P.rent * 2) AS deposit, (rent_finish - rent_start) AS duration
FROM Lease L, Property_For_Rent P
WHERE L.property_no = P.property_no;
-- ===========
-- NEWSPAPER
-- ===========
DROP TABLE Newspaper;
CREATE TABLE Newspaper
(newspaper_name CHARACTER VARYING(25) PRIMARY KEY,
address CHARACTER VARYING(50),
tel_no CHARACTER VARYING(13) UNIQUE, -- ALTERNATE KEY
contact_name CHARACTER VARYING(30));
-- =========
-- ADVERT
-- =========
DROP TABLE Advert;
CREATE TABLE Advert
(property_no CHARACTER(5) PRIMARY KEY,
-- FOREIGN KEY
newspaper_name CHARACTER VARYING(25), -- FOREIGN KEY
date_advert DATE,
cost NUMERIC(9,2));
/*=======================*
* INTEGRITY CONSTRAINTS *
*=======================*/
-- =========
-- BRANCH
-- =========
ALTER TABLE Branch
ADD FOREIGN KEY (mgr_staff_no)
REFERENCES Manager(staff_no);
-- ===========
-- TELEPHONE
-- ===========
ALTER TABLE Telephone
ADD FOREIGN KEY (branch_no)
REFERENCES Branch(branch_no);
-- ===========
-- STAFF
-- ===========
ALTER TABLE Staff
ADD FOREIGN KEY (supervisor_staff_no)
REFERENCES Staff(staff_no);
ALTER TABLE Staff
ADD FOREIGN KEY (branch_no)
REFERENCES Branch(branch_no);
-- ===========
-- MANAGER
-- ===========
ALTER TABLE Manager
ADD FOREIGN KEY (staff_no)
REFERENCES Staff(staff_no);
-- ===================
-- PROPERTY_FOR_RENT
-- ===================
ALTER TABLE Property_For_Rent
ADD FOREIGN KEY (staff_no)
REFERENCES Staff(staff_no) MATCH FULL;
ALTER TABLE Property_For_Rent
ADD FOREIGN KEY (branch_no)
REFERENCES Branch(branch_no);
-- ==========
-- VIEWING
-- ==========
ALTER TABLE Viewing
ADD FOREIGN KEY (client_no)
REFERENCES Client(client_no);
ALTER TABLE Viewing
ADD FOREIGN KEY (property_no)
REFERENCES Property_For_Rent(property_no);
-- ==============
-- REGISTRATION
-- ==============
ALTER TABLE Registration
ADD FOREIGN KEY (client_no)
REFERENCES Client(client_no);
ALTER TABLE Registration
ADD FOREIGN KEY (branch_no)
REFERENCES Branch(branch_no);
ALTER TABLE Registration
ADD FOREIGN KEY (staff_no)
REFERENCES Staff(staff_no);
-- ========
-- LEASE
-- ========
ALTER TABLE Lease
ADD FOREIGN KEY (client_no)
REFERENCES Client(client_no);
ALTER TABLE Lease
ADD FOREIGN KEY (property_no)
REFERENCES Property_For_Rent(property_no);
-- ========
-- ADVERT
-- ========
ALTER TABLE Advert
ADD FOREIGN KEY (property_no)
REFERENCES Property_For_Rent(property_no);
ALTER TABLE Advert
ADD FOREIGN KEY (newspaper_name)
REFERENCES Newspaper(newspaper_name);