Question

Part I.

Create a library check-out database using Microsoft SQL Server that stores data about books, patrons, and the check-out process.

Books (BookID, BookName, Author, YearPublished)

Patrons (PatronsID, PatronsName, PatronsAddress, PatronsBirthday)

CheckInOut (TransactionID, PatronID, BookID, CheckOutDate, NumDay, ReturnDate, Late, Fees, Paid) - the NumDay field contains the number of days patrons can keep the book, if the return date is over the number of day, then the Late field will have a Y value and a fee of $1.00 per day should be in the Fees field for that book. If the ReturnDate of the book is before or the same as the number of day allowed, then Late will have N and no fees in the Fees field; set the Paid fee to Y/N if the patron paid the fees or not)

Apply the concepts of database systems that you have learned so far. Be sure to include tables, fields, keys, relationships, and test data in your database.

Part II. Submit a paper with headings and screenshots in this order:

The design and diagram of your tables.

1. SQL queries and the results selecting all data from all tables.

2. SQL query and results using a WHERE clause with an AND operator - Select and display all patrons who are reading a book and it is not past due (you will need to add the number of days the book can stay to the checkout date and compare to today's date, that way you will know if the book is or not past due)

3. SQL query and results using a WHERE clause with an OR operator. - Select and display all books that are out or books that have a unpaid fee.

4. Create a function and a trigger for when the book is past due, a fee of $1.00 per day is added to the Fees field for the corresponding TransactionID. Create test data for this scenario and show the before and after images for this situation.

Below is the DB I created for this scenario. I'm looking for help with the SQL queries and verifying my DB is correct.

CREATE TABLE [dbo].[Books] (
   [BookID] INT IDENTITY(1,1) PRIMARY KEY,
   [BookName] NVARCHAR(50) not null,
   [Author] NVARCHAR(50) not null ,
   [YearPublished] INT not null ,
);

CREATE TABLE [dbo].[Patrons] (
   [PatronID] INT IDENTITY(100,1) PRIMARY KEY,
   [PatronName] NVARCHAR(25) not null ,
   [PatronAddress] TEXT not null,
   [PatronBirthday] DATE ,
);

CREATE TABLE [dbo].[CheckInOut] (
   [TransactionID] INT IDENTITY PRIMARY KEY ,
   [PatronID] INT FOREIGN KEY REFERENCES dbo.Patrons(PatronID),
   [BookID] INT FOREIGN KEY REFERENCES dbo.Books(BookID),
   [CheckOutDate] DATE not null ,
   [NumDay] INT ,
   [ReturnDate] DATE ,
   [Late] TEXT ,
   [Fees] DECIMAL ,
   [Paid] TEXT ,
   );

  
SET IDENTITY_INSERT [dbo].[Books] ON;
INSERT INTO [dbo].[Books] (BookID, BookName, Author, YearPublished)
VALUES
('111', 'Book One', 'Authoer One', '2011'),
('222', 'Book Two', 'Author Two', '2012'),
('333', 'Book Three', 'Author Three', '2013');
GO
SET IDENTITY_INSERT [dbo].[Books] OFF;

SET IDENTITY_INSERT [dbo].[Patrons] ON;
INSERT INTO [dbo].[Patrons] (PatronID, PatronName, PatronAddress, PatronBirthday)
VALUES
('1', 'Patron One', '111 First Ave Flint, MI', '01/01/1981'),
('2', 'Patron Two', '222 2nd Ave SLC, UT', '02/02/1982'),
('3', 'Patron Three', '333 3rd Ave SD, CA', '03/03/1983');
GO
SET IDENTITY_INSERT [dbo].[Patrons] OFF;

SET IDENTITY_INSERT [dbo].[CheckInOut] ON;
INSERT INTO [dbo].[CheckInOut] (TransactionID, PatronID, BookID, CheckOutDate, NumDay, ReturnDate, Late, Fees, Paid)
VALUES
('1001', '3', '333', '04/01/2018', '7', '04/08/2018', 'N', '0', 'Y'),
('2002', '2', '222', '04/02/2018', '14', '04/16/2018', 'N', '0', 'Y'),
('3003', '1', '111', '04/03/2018', '21', '04/25/2018', 'Y', '2', 'n');

GO
SET IDENTITY_INSERT [dbo].[CheckInOut] OFF;

Answer 1

In MS SQL Server 2014 created following tables

1.books 2.patrons 3.checkOutProcess

create database library

use library

create table books(
booksId int primary key Identity(1,1),
booksName varchar(50) not null ,
bookAuthor varchar(50) not null,
Edition int not null
)

create table patrons(
patronsId int primary key Identity(100,1),
name varchar(20) not null,
address text not null,
)

create table checkOutProcess(
checkOut int primary key Identity,
checkOutDate date not null,
checkoutType int not null,
booksId int not null references books,
patronsId int not null references patrons
)

insert into books values('Angular5','Sachin Tendulkar',1)
insert into books values('MongoDB','Virat Kohli',4)
insert into books values('Getting started with NodeJS','Rahul D',2)
insert into books values('ReactJS','KL Pandey',3)

insert into patrons values('Steve Smith','sydeny')
insert into patrons values('Shaun Marsh','perth')
insert into patrons values('Aron Finch','sydeny')
insert into patrons values('P Ricky','UK')

insert into checkOutProcess values('2018-10-09',1,2,101)
insert into checkOutProcess values('2018-10-03',2,3,101)
insert into checkOutProcess values('2018-12-03',1,1,101)
insert into checkOutProcess values('2018-10-09',2,4,101)

SQL queries selecting all data from all tables.

select * from books
select * from patrons
select * from checkOutProcess

Results from the queries showing the data from each table.

SQL query using a WHERE clause with an AND operator.

select booksName,bookAuthor,checkoutId,name from books ,checkOutProcess ,patrons
where books.booksId =checkOutProcess.booksId AND patrons.patronsId=101

Results from the query using a WHERE clause with an AND operator.

SQL query using a WHERE clause with an OR operator.

select checkOutId,checkOutProcess.patronsID ,checkOutDate,checkOutType,name,address from checkOutProcess ,patrons where
checkOutProcess.patronsId=patrons.patronsId OR patrons.name='Steve Smith'

Results from the query using a WHERE clause with an OR operator.