-- Title:    	Assignment Part A
-- Purpose:  	Creation of the database tables
--           	including integrity constraints
--           	`
-- Language: 	SQL (Oracle v9i) 
-- Author:         Alan Johnson
-- Date:     	25th August 2007
-- Version:  	1.1 - Modified After Feedback


DROP TABLE loan;
DROP TABLE book;
DROP TABLE reservation;
DROP TABLE title;
DROP TABLE member;


CREATE TABLE member
(
memberid			number(10)		PRIMARY KEY,
first_name			varchar2(100)		,
surname				varchar2(100)		,
join_date			date			,
leave_date			date			,
category			char(3),

CONSTRAINT member_category_ck   			CHECK (category IN ('SS','SFS','SM')),
CONSTRAINT member_category_nn   			CHECK (category IS NOT NULL),
CONSTRAINT member_leave_date_ck				CHECK (leave_date > join_date)
);




CREATE TABLE title
(
titleid				number(10)		PRIMARY KEY,
title				varchar2(100)		,
edition				varchar2(100)		,
publisher			varchar2(100)		,
publish_date			number(4)		,
author				varchar2(100)		,
fiction				char(1),	
description			varchar2(300),

CONSTRAINT title_fiction_ck 				CHECK (fiction IN ('Y','N')),
CONSTRAINT title_fiction_nn				CHECK (fiction IS NOT NULL)
);



CREATE TABLE reservation
(
memberid			number(10)		,
titleid				number(10)		,
reserve_date 			date			,
valid_until			date			,
held_date			date			,

PRIMARY KEY (memberid, titleid),
FOREIGN KEY (titleid) REFERENCES title (titleid),
FOREIGN KEY (memberid) REFERENCES member (memberid),

CONSTRAINT reservation_held_date_ck			CHECK (held_date > reserve_date),
CONSTRAINT reservation_valid_until_ck			CHECK (valid_until > reserve_date)
);





CREATE TABLE book
(
bookid				number(10)		PRIMARY KEY,
titleid				number(10)		REFERENCES title (titleid),
loan_period			number(5)		,
acquired			date			,
cost				number(10)		,
removed				date			,

CONSTRAINT book_removed_ck				CHECK (removed > acquired),
CONSTRAINT book_cost_ck					CHECK (cost < 999.99)

);





CREATE TABLE loan
(
memberid			number(10)		,
bookid				number(10)		,
loan_date			date			,
due_date			date			,
date_returned			date			,

PRIMARY KEY (memberid, bookid),
FOREIGN KEY (memberid) REFERENCES member (memberid),
FOREIGN KEY (bookid) REFERENCES book (bookid),


CONSTRAINT loan_due_date_ck				CHECK (due_date > loan_date),
CONSTRAINT loan_date_returned_ck			CHECK (date_returned > loan_date)










-- Title:    	Assignment Part B
-- Purpose:  	SQL Script to AMEND the given test data script 
--		named "Existing Test Data Script".
-- Language: 	SQL (Oracle v9i) 
-- Author:      Alan Johnson
-- Date:     	26th August 2007
-- Version:  	1.0 


DELETE FROM member
WHERE memberid = 1000000010;

INSERT INTO member VALUES (1000000009, 'Imran', 'Patel', '01-NOV-91', NULL, 'SM');

SELECT *
FROM member;



UPDATE title SET publisher = 'McGraw Hill'
WHERE titleid = 6;

UPDATE title SET publisher = 'McGraw Hill'
WHERE titleid = 7;

SELECT *
FROM title;



UPDATE book SET removed = '12-JUN-05'
WHERE bookid = 7;

UPDATE book SET removed = '15-JUN-05'
WHERE bookid = 8;

SELECT *
FROM book;



--Information provided is correct, requires to be listed correctly in order via the following:

SELECT *
FROM loan
ORDER BY bookid, loan_date;



UPDATE reservation SET memberid = 1000000009
WHERE titleid = 6;

UPDATE reservation SET titleid = 7
WHERE memberid= 1000000009;

UPDATE reservation SET valid_until = '15-DEC-05'
WHERE memberid= 1000000009;

SELECT *
FROM reservation;

commit;






- Title:    	Assignment Part C
-- Purpose:  	Summary of the number of times each title has been borrowed by each category of user in the previous year

-- Language: 	SQL (Oracle v9i) 
-- Author:      Alan Johnson
-- Date:     	31st August 2007
-- Version:  	1.0


-- Summary of the number of times each title has been borrowed by each category of user in the previous year




SELECT  loan.bookid "Book ID", book.titleid "Title ID" , title.title "Book Title", count(loan.bookid) "# Times Loaned",
	member.category "Members Category"




FROM title, book, loan, member



WHERE loan.bookid=book.bookid



AND book.titleid=title.titleid



GROUP BY  loan.bookid, book.titleid, title.title, member.category