Library-Management/table_query.sql at master · rohan171678/Library-Management · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
--Creation of schema and table

create schema Library;


create table BOOK
(Book_id char(10) primary key not null,
Title varchar(60));


create table BOOK_AUTHORS
(Book_id char(10) not null,
Author_name varchar(60) not null,
Fname varchar(60),
Minit varchar(60),
Lname varchar(60),
primary key(Book_id,Author_name),
foreign key (Book_id) references BOOK(Book_id));

create table LIBRARY_BRANCH
(Branch_id char(1) primary key not null,
Branch_name varchar(60),
Address varchar(100),
);

create table BOOK_COPIES
(Book_id char(10) not null,
Branch_id char(1) not null,
No_of_copies int,
primary key(Book_id,Branch_id),
foreign key (Book_id) references BOOK(Book_id),
foreign key (Branch_id) references LIBRARY_BRANCH(Branch_id)
);

create table BORROWER
(card_no CHAR(4) not null ,
Fname varchar(60),
Lname varchar(60),
Address varchar(200),
city varchar(10),
State char(2),
Phone char(14),
primary key(card_no)
);

create table BOOK_LOANS
(Loan_id int not null auto_increment,
Book_id char(10) not null,
Branch_id char(1) not null,
card_no CHAR(4) not null ,
Date_out DATE,
Due_date DATE,
Date_in DATE,
primary key(loan_id),
foreign key (Book_id) references BOOK(Book_id),
foreign key (Branch_id) references LIBRARY_BRANCH(Branch_id),
foreign key (card_no) references BORROWER(card_no)
);

create table fine
(Loan_id int not null primary key,
Fine_amt float(20,2),
paid boolean default false,
foreign key (loan_id) references BOOK_LOANS(loan_id));

create view BOOK_COUNT AS
select bc.book_id,bc.branch_id, bc.no_of_copies as Available_COUNT, count(bl.book_id) AS LOANED_COUNT, count(bl.book_id)+no_of_copies as TOTAL_COUNT
from book_copies bc left join book_loans bl on bc.book_id = bl.book_id and bc.branch_id=bl.branch_id
group by bc.book_id,bc.branch_id;

--Loading data into the tables

LOAD LOCAL DATA INFILE '/home/Anupama/Downloads/Anupama_project/book1.csv'
INTO TABLE BOOK
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

LOAD LOCAL DATA INFILE '/home/Anupama/Downloads/Anupama_project/book2.csv'
INTO TABLE BOOK_AUTHORS
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

LOAD LOCAL DATA INFILE '/home/Anupama/Downloads/Anupama_project/book3.csv'
INTO TABLE BOOK_COPIES
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

LOAD LOCAL DATA INFILE '/home/Anupama/Downloads/Anupama_project/book4.csv'
INTO TABLE BORROWER
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

LOAD LOCAL DATA INFILE '/home/Anupama/Downloads/Anupama_project/book5.csv'
INTO TABLE LIBRARY_BRANCH
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

--Commands for modifying the table

Alter table BOOK_AUTHORS drop column fname;
Alter table BOOK_AUTHORS drop column lname;
Alter table BOOK_AUTHORS drop column minit;
alter table book_authors add column Type int;