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;