-- PostgreSQL commands to build the kplug-library database
-- vim:ts=4:syn=sql
-- john h. robinson, iv
-- $Id: psql_tables.txt,v 1.5 2002/01/22 06:38:10 jaqque Exp $

drop table Addresses;
drop sequence addresses_id_seq;
create table Addresses (
	id SERIAL,
	street VARCHAR (50),
	city VARCHAR (50),
	country VARCHAR (50),
	zip VARCHAR (50),
	PRIMARY KEY (id)
);
grant insert on Addresses to kplib;
grant update on Addresses_id_seq to kplib;
grant select on Addresses_id_seq to kplib;

drop table AddressTypes;
drop sequence addresstypes_id_seq;
create table AddressTypes (
	id SERIAL,
	type VARCHAR (50),
	PRIMARY KEY (id)
);
grant select on AddressTypes to kplib;

drop table Emails;
drop sequence emails_id_seq;
create table Emails (
	id SERIAL,
	name VARCHAR (100),
	domain VARCHAR (100),
	PRIMARY KEY (id)
);
grant insert on Emails to kplib;
grant update on Emails_id_seq to kplib;
grant select on Emails_id_seq to kplib;

drop table EmailTypes;
drop sequence emailtypes_id_seq;
create table EmailTypes (
	id SERIAL,
	type VARCHAR (50),
	PRIMARY KEY (id)
);
grant select on EmailTypes to kplib;

drop table Offices;
drop sequence offices_id_seq;
create table Offices (
	id SERIAL,
	title VARCHAR (100),
	PRIMARY KEY (id)
);

drop table Phones;
drop sequence phones_id_seq;
create table Phones (
	id SERIAL,
	country_code VARCHAR (10),
	city_code VARCHAR (10),
	line VARCHAR (10),
	PRIMARY KEY (id)
);

drop table PhoneTypes;
drop sequence phonetypes_id_seq;
create table PhoneTypes (
	id SERIAL,
	type VARCHAR (50),
	PRIMARY KEY (id)
);

drop table Prefixes;
drop sequence prefixes_id_seq;
create table Prefixes (
	id SERIAL,
	prefix VARCHAR (100),
	PRIMARY KEY (id)
);
grant select on prefixes to kplib;

drop table Publishers;
drop sequence publishers_id_seq;
create table Publishers (
	id SERIAL,
	name VARCHAR (100),
	PRIMARY KEY (id)
);

drop table Suffixes;
drop sequence suffixes_id_seq;
create table Suffixes (
	id SERIAL,
	suffix VARCHAR (100),
	PRIMARY KEY (id)
);
grant select on Suffixes to kplib;

drop table TokenTypes;
drop sequence tokentypes_id_seq;
create table TokenTypes (
	id SERIAL,
	type VARCHAR (50),
	PRIMARY KEY (id)
);
grant select on TokenTypes to kplib;

drop table People;
drop sequence people_id_seq;
create table People (
	id SERIAL,
	prefix INTEGER,
	f_name VARCHAR (100),
	m_name VARCHAR (100),
	l_name VARCHAR (100),
	suffix INTEGER,
	PRIMARY KEY (id),
	FOREIGN KEY (prefix) REFERENCES Prefixes,
	FOREIGN KEY (suffix) REFERENCES Suffixes
);
grant insert on people to kplib;
grant update on people_id_seq to kplib;
grant select on people_id_seq to kplib;

drop table Books;
drop sequence books_id_seq;
create table Books (
	id SERIAL,
	title VARCHAR (100),
	author VARCHAR (100),
	publisher VARCHAR (100),
	published DATE,
	ISBN varchar (20),
	value DECIMAL(9,2),
	PRIMARY KEY (id),
	FOREIGN KEY (publisher) REFERENCES Publishers
);

drop table BookReviews;
drop sequence bookreviews_id_seq;
create table BookReviews (
	id SERIAL,
	book INTEGER,
	reviewer INTEGER,
	review TEXT,
	PRIMARY KEY (id),
	FOREIGN KEY (book) REFERENCES Books,
	FOREIGN KEY (reviewer) REFERENCES People
);

drop table Officers;
drop sequence officers_id_seq;
create table Officers (
	id SERIAL,
	office INTEGER,
	person INTEGER,
	date_in DATETIME,
	date_out DATETIME,
	PRIMARY KEY (id),
	FOREIGN KEY (office) REFERENCES Officers,
	FOREIGN KEY (person) REFERENCES People
);

drop table People_Addresses;
create table People_Addresses (
	person INTEGER,
	address INTEGER,
	type INTEGER,
	PRIMARY KEY (person, address),
	FOREIGN KEY (person) REFERENCES People,
	FOREIGN KEY (address) REFERENCES Addresses,
	FOREIGN KEY (type) REFERENCES AddressTypes
);
grant insert on People_Addresses to kplib;

drop table People_Emails;
create table People_Emails (
	person INTEGER,
	email INTEGER,
	type INTEGER,
	PRIMARY KEY (person, email),
	FOREIGN KEY (person) REFERENCES People,
	FOREIGN KEY (email) REFERENCES Emails,
	FOREIGN KEY (type) REFERENCES EmailTypes
);
grant insert on People_Emails to kplib;

drop table People_Phones;
create table People_Phones (
	person INTEGER,
	phone INTEGER,
	PRIMARY KEY (person, phone),
	FOREIGN KEY (person) REFERENCES People,
	FOREIGN KEY (phone) REFERENCES Phones
);

drop table Publisher_Addresses;
create table Publisher_Addresses (
	publisher INTEGER,
	address INTEGER,
	PRIMARY KEY (publisher, address),
	FOREIGN KEY (publisher) REFERENCES Publishers,
	FOREIGN KEY (address) REFERENCES Addresses
);

drop table Publishers_Phones;
create table Publishers_Phones (
	publisher INTEGER,
	phone INTEGER,
	PRIMARY KEY (publisher, phone),
	FOREIGN KEY (publisher) REFERENCES Publishers,
	FOREIGN KEY (phone) REFERENCES Phones
);

drop table Tokens;
drop sequence tokens_id_seq;
create table Tokens (
	id SERIAL,
	person INTEGER,
	type INTEGER,
	value VARCHAR(100),
	PRIMARY KEY (id),
	FOREIGN KEY (person) REFERENCES People,
	FOREIGN KEY (type) REFERENCES TokenTypes
);
grant insert on Tokens to kplib;
grant update on Tokens_id_seq to kplib;

drop table Transactions;
drop sequence transactions_id_seq;
create table Transactions (
	id SERIAL,
	book INTEGER,
	person INTEGER,
	date_out DATETIME,
	date_due DATETIME,
	date_in DATETIME,
	fee DECIMAL (9,2),
	PRIMARY KEY (id),
	FOREIGN KEY (book) REFERENCES Books,
	FOREIGN KEY (person) REFERENCES People
);

\dt
\ds
\di