ReadIT is a small library in Woodley which is ran by the local community to encourage reading. Due to its success, they are now requiring a database to store and track book loans. They are also planning

Assignment Brief

As part of the formal assessment for the programme you are required to submit a Introduction to Databases assignment. Please refer to your Student Handbook for full details of the programme assessment scheme and general information on preparing and submitting assignments.

Assignment Task

Scenario:

ReadIT is a small library in Woodley which is ran by the local community to encourage reading. Due to its success, they are now requiring a database to store and track book loans. They are also planning to start a coffee shop so that any profits can help cover the expenses of the library.

You have been assigned as a database developer to undertake the following:

Task 1: Propose the design of a relational database for the coffee shop
Task 2: Implement the database for the community library.
Task 3: Test the implementation of the community library database

Part 1: Database Design And Development

Task 1: Coffee Shop Relational Database Design

1. The coffee shop will make fresh coffee on the premises based on customer orders. Initially, they are planning to offer 5 different varieties of coffee which can be ordered in 3 different sizes. The coffee prices will vary based on the coffee variety and the size of the selected cup. In addition, they will also sell items such as biscuits and cakes. They will need to have online catalogue for this menu along with prices.
2. They will also need to record the orders and print invoices.
3. They hope to increase the coffee varieties and offer other foods in the future, so it is important that the database design is scalable.

Task 2: Create the Library Relational Database

The library lends books to the local community’s registered members. Until now all information related to book stock, members and library transactions has been stored manually using a ledger. However, there have been times when the data has been inconsistent or lost and there are concerns around this manual way of processing the data so it has recommended that a database should be developed to manage all data related to the library.

Part 2: Test The Library Relational Database

Task 3

Using the Library database from Task 2, provide solutions to the following four problem cases in the form of SQL code and a brief explanation of your reasoning. Include screenshots of SQL queries execution and its results along with your schema, evidencing this in each of the cases mentioned below. Ensure your schema or username is included in every screenshot to verify it is your code