Untitled

USE master;

DROP DATABASE FinalProject;

CREATE DATABASE FinalProject;

USE FinalProject;

CREATE TABLE tblListOfBooks (
	BookTitle varchar(50) NOT NULL,					--BOOK TITLE
	Author varchar(50) NOT NULL,					--BOOK WRITER
	SerialNumber int NOT NULL PRIMARY KEY,			--BOOK CODE
	NumberOfBook int NOT NULL,						--BOOK AVAILABILITY
	);

CREATE TABLE tblUser (
	TransactionID int IDENTITY(1,1) PRIMARY KEY, 
	FirstName varchar(50) NOT NULL,
	LastName varchar(50) NOT NULL,
	SerialNumber int FOREIGN KEY REFERENCES tblListOfBooks(SerialNumber),
	Quantity int NOT NULL,
	);

CREATE TABLE tblTransaction (
	TransactionID int UNIQUE FOREIGN KEY REFERENCES tblUser(TransactionID) ON DELETE CASCADE NOT NULL,
	FirstName varchar(50) NOT NULL,
	LastName varchar(50) NOT NULL,
	SerialNumber int NOT NULL,
	Quantity int NOT NULL,
	BorrowDate DATE DEFAULT GETDATE(),
	ReturnDate DATE DEFAULT GETDATE() + 7,
	Penalty int DEFAULT 0,
	);

INSERT INTO tblListOfBooks(BookTitle, Author, SerialNumber, NumberOfBook) VALUES
	('firstTitle', 'firstAuthor', 1111111, 50)

INSERT INTO tblListOfBooks(BookTitle, Author, SerialNumber, NumberOfBook) VALUES
	('secondTitle', 'secondAuthor', 2223222, 50)

INSERT INTO tblListOfBooks(BookTitle, Author, SerialNumber, NumberOfBook) VALUES
	('thirdTitle', 'thirdAuthor', 333333, 50)

INSERT INTO tblListOfBooks(BookTitle, Author, SerialNumber, NumberOfBook) VALUES
	('fourthTitle', 'fourthAuthor', 4444444, 50)

--INSERT INTO tblUser(FirstName, LastName, SerialNumber, Quantity) VALUES
--	('Arnieno', 'Maraan', 4444444, 4)

INSERT INTO tblUser(FirstName, LastName, SerialNumber, Quantity) VALUES
 ('Arnieno', 'Maraan', 333333, 1)

INSERT INTO tblTransaction(TransactionID, FirstName, LastName, SerialNumber, Quantity) VALUES
	(1, 'Arnieno', 'Maraan', 333333, 4)



SELECT * 
FROM tblListOfBooks;
SELECT *
FROM tblUser;
SELECT *
FROM tblTransaction;

SELECT * FROM tblListOfBooks;