week_2.sql

use inform;
create table user(
user_id int primary key AUTO_INCREMENT,
F_name varchar(20) not null,
L_name varchar(20) not null,
Per_address varchar(50) not null,
temp_address varchar(50) not null,
age int(2) not null,
DOB date not null
);
create table book(
book_id int primary key auto_increment,
book_name varchar(20) not null,
publisher_name varchar(30) not null,
date_of_publish date not null,
total_page_no int(6) not null
);
create table librarian(
librarian_id int primary key auto_increment,
librarian_name varchar(40) not null,
department_name varchar(50) not null
);

ALTER TABLE librarian
drop librarian_name; -- table has been already created and we want to delect librarian_name column

alter table librarian
add librarian_fname varchar(20) not null,
add librarian_lname varchar(20) not null;  -- table has been already created and we need to add two column name libraria_fname and librarian_lname

create table members(
member_id int primary key auto_increment,
member_fname varchar(20),
member_lname varchar(20),
member_temp_address varchar(20) not null,
member_perm_address varchar(20) not null);

create table user_book( -- there is a many to many relation between user and book so we create a separate table called user_book 
user_id int,   -- as a junction or bridge table
book_id int,
primary key(user_id, book_id),  -- create a new primary key by merging two primary key of user and book
foreign key(user_id) references user(user_id), -- using user_id column as a foreign key in user_book table
foreign key(book_id) references book(book_id)  -- using book_id column as a foreign key in user_book table
);

create table book_librarian(
book_id int,
librarian_id int,
primary key(book_id, librarian_id),
foreign key (book_id) references book(book_id),
foreign key (librarian_id) references librarian( librarian_id)
);

create table members_book(
member_id int, book_id int,
primary key(member_id, book_id),
foreign key(member_id) references members(member_id),
foreign key(book_id) references book(book_id)
);
insert into user(user_id, F_name, L_name, Per_address, temp_address, age, DOB) values (null, 'trishna', 'bhattarai', 'pokhara', 'kathmandu', '19', '2006-02-01');
insert into user(user_id, F_name, L_name, Per_address, temp_address, age, DOB) values (null, 'krishna', 'kumar', 'kamal pokhari', 'india', '20', '2008-12-02');
insert into book( book_id, book_name, publisher_name, date_of_publish, total_page_no) values (null, 'idea', 'ram', '2052-08-09', '50');
insert into librarian( librarian_id, librarian_name, department_name) values (null, 'ranju', 'BCS');
insert into members( member_id, member_fname, member_lname, member_temp_address, member_perm_address) values (null, 'sanju', 'chaudhary', 'gorakhpur', 'bhairahawa');

select * from user;