wrb9UdnE

1DROP SCHEMA IF exists library;
2create schema Library;
3use Library;
4
5
6create table PUBLISHER (
7	Name varchar(40) primary key not null,
8	Address varchar(255),
9    Phone   char(12));
10    
11create TABLE BOOK (
12	Book_id char(20) primary key not null ,
13	Title varchar(40) not null , 
14	Publisher_name varchar(40) ,
15    foreign key (Publisher_name) references PUBLISHER(Name)
16		ON UPDATE cascade);
17 
18create TABLE Borrower(
19		Card_no char(20) primary key not null,
20        Name varchar(40),
21        Address varchar(255),
22        Phone char(12)
23    );
24    
25CREATE TABLE Library_branch(
26	Branch_id char(8) primary key not null,
27    Branch_name varchar(60) not null,
28    Address varchar(255) not null
29);
30    
31create table BOOK_AUTHORS (
32	Book_id char(20)  not null  , 
33	Author_name varchar(40) primary key not null,
34    FOREIGN KEY (Book_id) references BOOK(Book_id) 
35		ON update CASCADE
36) ;
37    
38
39create table BOOK_COPIES( 
40	Book_id char(20) not null ,
41    Branch_id char(8) not null ,
42    No_of_Copies integer,
43    foreign key (Book_id) references BOOK(Book_id)  
44		ON update CASCADE 
45        ON delete CASCADE,
46	foreign key (Branch_id) references LIBRARY_BRANCH(Branch_id) 
47		on update CASCADE 
48        on delete cascade);
49
50create table BOOK_LOANS(
51		Book_id char(20) not null  ,
52		Branch_id char(8) not null ,
53        Card_no char(20) not null ,
54        Date_out date not null,
55        due_date date not null,
56        primary key (Book_id , Branch_id , Card_no),
57        foreign key (Book_id) REFERENCES BOOK(Book_id)
58			ON update CASCADE,
59		FOREIGN KEY (Branch_id) references LIBRARY_BRANCH(Branch_id) 
60			on update CASCADE,
61		FOREIGN KEY (Card_no) references BORROWER(Card_no)
62			on update CASCADE
63        );
64        
65