/* Comment: We use the name “User2” for the table “User”, because “User” is a preserved word in Oracle. */

/* Solution A: with a relation Loan which only stores historical loans */


drop table User2;
drop table Book;
drop table Author;
drop table Loan;


create table User2(
	ucode char(5) constraint cp_User primary key deferrable,
	name varchar2(60),
	address varchar2(50),
	tel varchar2(20),
	total_loans number(3) default 0);

create table Book(
	bcode char(5) constraint cp_Book primary key  deferrable,
	title varchar(100),
	topic varchar(15) constraint null_topic not null deferrable,
	ucode char(5) constraint ca_Book_User references User deferrable,
	loan_date date,	
	constraint c_topic 
		check (topic in ('physics','electricity','mechanics','optics')) deferrable,
	constraint c_loan check ((ucode is null and loan_date is null) or 
										(ucode is not null and loan_date is not null)) deferrable);

create table Author(
	bcode char(5) constraint ca_author_Book references Book(bcode)deferrable,
	author varchar2(40),
	constraint cp_Author primary key(bcode, author) deferrable);

create table Loan(
	ucode char(5) constraint ca_pre_User2 references User2(ucode) deferrable
	constraint null_ucode not null deferrable,
	bcode char(5) constraint ca_pre_Book references Book(bcode) deferrable,
	loan_date date constraint null_loan_date not null deferrable,
	return_date date constraint null_return_date not null deferrable,
	constraint cp_loan primary key(bcode,loan_date) deferrable,
	constraint dates check (return_date>loan_date) deferrable);





/* Solution B: with a relation Loan which stores current and historical loans */

drop table User2;
drop table Book;
drop table Author;
drop table Loan;

create table User2(
	ucode char(5) constraint cp_User primary key deferrable,
	name varchar2(60),
	address varchar2(50),
	tel varchar2(20),
	total_loans number(3) default 0 not null deferrable );

create table Book(
	bcode char(5) constraint cp_Book primary key  deferrable,
	title varchar(100),
	topic varchar(15) constraint null_topic not null deferrable,
	constraint c_topic
		check (topic in ('physics','electricity','mechanics','optics')) deferrable);

create table Author(
	bcode char(5) constraint ca_author_Book references Book(bcode)  deferrable,
	author varchar2(40),
	constraint cp_Author primary key(bcode, author) deferrable);

create table Loan(
	ucode char(5) constraint ca_pre_User2 references User2(ucode) deferrable  
               constraint null_ucode not null deferrable,
	bcode char(5) constraint ca_pre_Book references Book(bcode) deferrable,
	loan_date date constraint null_loan_date not null deferrable,
	return_date date,
	constraint cp_loan primary key(bcode,loan_date) deferrable,
	constraint dates check ((return_date is null) or (return_date>loan_date)) deferrable);