SQL Queries - Rent a Book (RB) Case Study - IT Assignment Help

Assignment Task

Activity 1: SQL Queries
Case Study:
Rent a Book (RB) is a start-up business which allows students and lecturers to rent books. They want to create a database to store the information of their books, members, renting details and rental fee charged. RB asked you to create a database for them which include the following tables

Tables Attributes
Members MemberID(PK), FName, LName, Street, Town, State, Postcode and Balance
Books ISBN(PK), Name, Author, Year, Cost, CharegeCode (FK)
Rent RentID(PK), RentDate, MemberID(FK)
Rent Details RentID (PK,FK), BookCopy (PK,FK), RentFee, DueDate, ReturnDate, LateFee
BookCopy BookCopy_Num (PK), BookCopy_ArrivalDate, ISBN (FK)
Charge ChargeCode (PK), Description, RentFee, DailyLateFee

Write the SQL queries to create the tables the above. Use appropriate data types for each attribute and choose appropriate primary and foreign keys.
Write the Insert query to enter the following sample data into the tables created in Part a. The sample data is provide in the following tables.

Table 1 Members
Member
ID FName LName Street Town State Postcode Balance
111 Rose Green 2 Alison St Randwick NSW 2030 100
112 Stacey Knell 50 Garden St Revesbay NSW 2140 50
113 India Elmore 44 Main St Botany NSW 2332 15
114 Cameron Rose 73 Everlast St Geelong VIC 3754 0
115 John Parker 43 Cook Ave. Parkwood NSW 2543 12
116 Robert Swarm 71 Vase St Revesbay NSW 2140 75
117 Louis Opral 34 East Drive Corio VIC 3125 33
118 Grace Ken 9 Max Avenue Highton VIC 3453 22
119 Wendy David 3 Elmore ST Botany NSW 2332 25
120 Kim Green 87 Kent St Randwick NSW 2030 34

Table 2 Rent
RentID RentDate MemberID
5001 01/05/2020 113
5002 02/07/2020 119
5003 01/02/2021 112
5004 01/02/2021 113
5005 01/02/2021 120

Table 3 RentDetails
RentID BookCopy RentFee DueDate ReturnDate LateFee
5001 4325 5 30/05/2020 29/05/2020
5001 5432 7 30/05/2020 30/05/2020
5002 4329 4.5 02/09/2020 28/08/2020
5003 4327 3.5 01/04/2021 29/03/2021
5003 6637 10 01/04/2021 29/03/2021
5003 6634 6 01/04/2021 01/04/2021
5004 4326 5 01/04/2021 01/04/2021
5004 4328 12 01/04/2021
5005 5433 3.5 07/05/2021
5004 6635 10 01/04/2021 01/04/2021

Table 4 Charge
ChargeCode Description RentFee LateFee
1 Standard 5 2
2 New 10 4
3 Discount 2 0.5


Table 5 BookCopy
BookCopy_Num BookCopy_ArrivalDate ISBN
4325 10/01/2019 1337627909
4326 10/01/2019 1337627909
4327 10/01/2019 1337627909
4328 12/02/2019 0241258766
4329 12/02/2019 0241258766
5432 16/04/2019 1292166622
5433 16/04/2019 1292166622
5434 16/04/2019 1292166622
5435 16/04/2019 1292166622
6634 28/06/2019 0137081073
6635 28/06/2019 0137081073
6636 28/06/2019 9780134085
6637 28/06/2019 9780134085

Table 6 Books
ISBN Name Author Year Cost (AUD) Charge Code
1337627909 Database Systems, Design,
Implementation and Management Carlos Coronel and Steven Morris 2018 116 1
0241258766 The Art of Statistics:
Learning from Data David Spiegelhalter 2020 17 1
1292166622 The Practice of Computing Using Python Global Edition William F Punch and Richard Enbody 2016 95 2
0137081073 The Clean Coder: A Code of Conduct for Professional Programmers C. Martin Robert 2011 49 3
9780134085 Security in Computing Charles P. Pfleeger, Lawrencer Pfleeger and Jonathan Margulies 2015 100 1

Questions.

Write the SQL queries for the tables created in Part a and Part b.
Write a query to list the member id, first name, last name and total number of books borrowed by the member.
Write a query to list each member first name, last name, the rented book name and due date.
Write a query to create a view “BorrowDetails” for the query used in previous question and also display the number of books each member has from the newly created view.

This IT Assignment has been solved by our IT experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.
Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.