database assignment

Question

You are working in a Library and the Supervisor has asked you to create a database to store the data in the tables below. 

use MS access any version

Users Table

userID	FullName	Enabled	lastLogin
U001	Makereta Suka	F	2020-06-26
U002	Alice Daveta	T	2020-05-14
U003	Sanjay Kumar	T	2020-07-07
U004	Michael Fong	F	2020-09-15

Books

bookNumber	Title	Author	publishedDate	Isbn
B01	My First SQL book	Mary Parker	2012-02-12	9814 8302 9127
B02	My Second SQL book	John Mayer	1972-07-19	8653 0092 3713
B03	My Third SQL book	Cary Flint	2015-10-18	5231 2096 7812

User Address Table

userID	Address	Town	City
U001	17 Tuvana Place	Samabula	Suva
U002	6 Ono Street	Samabula North	Suva
U003	10 Browning Street	Raiwaqa	Suva
U004	100 Ratu Mara Road	Nabua	Suva

Checkout Table

checkoutNumber	userID	book Number	checkoutDate	returnDate
1	U001	B01	2019-10-15
2	U001	B02	2019-10-05	2019-10-13
3	U002	B02	2019-10-15	2019-10-22
4	U004	B03	2019-10-15

Review Table

reviewCode	bookNumber	reviewerName	Content	Rating	publishedDate
01	B01	Michael Fong	My first review	4	2019-12-10
02	B02	Michael Fong	My second review	5	2019-10-13
03	B02	Alice Daveta	Another review	1	2019-10-22

Instructions:

Study the tables carefully.

1]           Create a database called LibraryXXXX

2]           The field names and table names are already done for you.

3]           Create a structure for each table - Determine data types and properties for each field; identify a primary key for each table.

4]           Create relationships for the table.

5]           Enter all records in each table.

6]           You are the 4^th reviewer. Your review code is 04, you have reviewed the book titled “My first sql book”, and this is your second review of the book. You have rated the book at 3.

7]           Create forms for each table.

8]           Create and save the following queries:

a.      List all the books that have been reviewed. Listing must include name of book, name of reviewer and rating. Name query Q1.

b.     List all users. Listing must have names and their addresses. Q2

c.      List all books that have been returned. List must include book name, author, borrower’s name and returned date. Save query as Q3

9]           Create a report to list all books that have not been returned. List the borrower’s name and address. Save as Unreturned Books.

Answer 1

primary keys:

Users.userID

Books.bookNumber

UserAddress.userID

CheckOut.checkoutNumber

Review.reviewCode

a. List all the books that have been reviewed.

Listing must include name of book, name of reviewer

and rating.

SELECT Title, reviewerName,Rating

FROM Review JOIN Books

ON Review.bookNumber = Books.bookNumber

b. List all users.

Listing must have names and their addresses.

Select FullName,Address,Town,City

From Users JOIN UserAddress

ON Users.userID = UserAdress.userID

/* If you want only ACTIVE users, then you add the clause

WHERE Enabled=True */

c. List all books that have been returned.

List must include book name, author, borrower’s name

and returned date.

SELECT Title, Author, userName, returnDate

From Checkout Join Books

On checkout.bookNumber = Books.bookNumber

Join Users

On users.userId = Checkout.userID

where len(returnDate)>0

9. Create a report to list all books

that have not been returned. List the

borrower’s name and address. Save as Unreturned Books.

SELECT Title, Author, userName, returnDate

From Checkout Join Books

On checkout.bookNumber = Books.bookNumber

Join Users

On users.userId = Checkout.userID

where len(returnDate)=0