MKz2M8KM

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