-
Notifications
You must be signed in to change notification settings - Fork 0
/
Queries.txt
102 lines (89 loc) · 3.47 KB
/
Queries.txt
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
create database library;
CREATE TABLE IF NOT EXISTS Books (
BookID INT AUTO_INCREMENT PRIMARY KEY,
Title VARCHAR(255) NOT NULL,
Author VARCHAR(100) NOT NULL,
Quantity INT NOT NULL,
CONSTRAINT unique_title_author UNIQUE (Title, Author)
);
-- Create Borrowers table
CREATE TABLE IF NOT EXISTS Borrowers (
BorrowerID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
ContactDetails VARCHAR(255)
);
-- Create Transactions table
CREATE TABLE IF NOT EXISTS Transactions (
TransactionID INT AUTO_INCREMENT PRIMARY KEY,
BookID INT REFERENCES Books(BookID),
BorrowerID INT REFERENCES Borrowers(BorrowerID),
TransactionDate DATE,
Type ENUM('Borrow', 'Return') NOT NULL
);
-- Create Genres table
CREATE TABLE IF NOT EXISTS Genres (
GenreID INT AUTO_INCREMENT PRIMARY KEY,
GenreName VARCHAR(50) NOT NULL
);
-- Create Publishers table
CREATE TABLE IF NOT EXISTS Publishers (
PublisherID INT AUTO_INCREMENT PRIMARY KEY,
PublisherName VARCHAR(100) NOT NULL
);
-- Update Books table
ALTER TABLE Books
ADD COLUMN GenreID INT,
ADD COLUMN PublisherID INT,
ADD CONSTRAINT fk_genre FOREIGN KEY (GenreID) REFERENCES Genres(GenreID),
ADD CONSTRAINT fk_publisher FOREIGN KEY (PublisherID) REFERENCES Publishers(PublisherID);
-- Create BookCopies table
CREATE TABLE IF NOT EXISTS BookCopies (
CopyID INT AUTO_INCREMENT PRIMARY KEY,
BookID INT REFERENCES Books(BookID),
Available BOOLEAN DEFAULT TRUE,
UNIQUE(BookID, Available)
);
use library;
SELECT * FROM borrowers;
SELECT * FROM transactions;
-- Insert sample genres
INSERT INTO Genres (GenreName) VALUES ('Science Fiction');
INSERT INTO Genres (GenreName) VALUES ('Mystery');
INSERT INTO Genres (GenreName) VALUES ('Fantasy');
INSERT INTO Genres (GenreName) VALUES ('Novel');
INSERT INTO Genres (GenreName) VALUES ('History');
INSERT INTO Genres (GenreName) VALUES ('Personal Finance');
INSERT INTO Genres (GenreName) VALUES ('Psychology');
INSERT INTO Genres (GenreName) VALUES ('Thriller');
INSERT INTO Genres (GenreName) VALUES ('Romance');
INSERT INTO Genres (GenreName) VALUES ('Travel');
INSERT INTO Genres (GenreName) VALUES ('Biography');
INSERT INTO Genres (GenreName) VALUES ('Self Help');
INSERT INTO Genres (GenreName) VALUES ('Non Fiction');
-- Insert sample publishers
INSERT INTO Publishers (PublisherName) VALUES ('Random House');
INSERT INTO Publishers (PublisherName) VALUES ('Penguin Books');
INSERT INTO Publishers (PublisherName) VALUES ('HarperCollins');
INSERT INTO Publishers (PublisherName) VALUES ('Plata Publishing');
INSERT INTO Publishers (PublisherName) VALUES ('Viking Press');
--
CREATE TABLE Users (
UserID INT AUTO_INCREMENT PRIMARY KEY,
Username VARCHAR(255) UNIQUE NOT NULL,
Password VARCHAR(255) NOT NULL,
UserType ENUM('Admin', 'Librarian', 'Student') NOT NULL
);
-- Inserting sample data for admin
INSERT INTO Users (Username, Password, UserType) VALUES ('admin', 'admin', 'Admin');
-- Inserting sample data for librarian
INSERT INTO Users (Username, Password, UserType) VALUES ('librarian', 'librarian123', 'Librarian');
-- Inserting sample data for student
INSERT INTO Users (Username, Password) VALUES ('adeel', '123');
ALTER Table borrowers
ADD COLUMN BookID INT,
ADD COLUMN Type VARCHAR(15),
ADD CONSTRAINT fk_BookID FOREIGN KEY (BookID) REFERENCES books(BookID);
ALTER Table borrowers
ADD Column Noofbooks INT;
-- ADD Column Quantity,
-- ADD Column Type;