SQL_Library_Management_Advance_Project/schemas.sql at main · paras160500/SQL_Library_Management_Advance_Project · 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
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
-- LIBRARY MANAGEMENT SYSTEM

--Creating Tables

drop table if exists branch;
create table branch (
	branch_id varchar(10) primary key,
	manager_id varchar(10),
	branch_address varchar(50),
	contact_n0 varchar(10)
);

drop table if exists employees;
create table employees (
	emp_id varchar(10) primary key,
	emp_name varchar(25),
	position varchar(15),
	salary int ,
	branch_id varchar(10)   -- FK
);

drop table if exists books;
create table books (
	isbn varchar(20) primary key,
	book_title varchar(70),
	category varchar(30),
	rental_price float,
	status varchar(15),
	author varchar(40),
	publisher varchar(40)
);

drop table if exists members;
create table members (
	member_id varchar(20) primary key,
	member_name varchar(25),
	member_address varchar(75),
	reg_date date
);

drop table if exists issued_status;
create table issued_status (
	issued_id varchar(10) primary key,
	issued_member_id varchar(10),  -- FK
	issued_book_name varchar(75),
	issued_date date,
	issued_book_isbn varchar(25),  -- FK
	issued_emp_id varchar(10)      -- FK
);

drop table if exists returned_status;
create table returned_status (
	return_id varchar(10) primary key,
	issued_id varchar(10),
	return_book_name varchar(75),
	return_date date,
	return_book_isbn varchar(20)
);


-- Foreign Key Constraint

alter table issued_status
add constraint fk_members
foreign key (issued_member_id)
references members(member_id);

alter table issued_status
add constraint fk_books
foreign key (issued_book_isbn)
references books(isbn);

alter table issued_status
add constraint fk_employees
foreign key (issued_emp_id)
references employees(emp_id);

alter table employees
add constraint fk_branch
foreign key (branch_id)
references branch(branch_id);

alter table returned_status
add constraint fk_issued_status
foreign key (issued_id)
references issued_status(issued_id);