COM4015 Assignment: Introduction to Databases Question | UOM

COM4015 Assignment Task Scenario

You have been assigned as a database developer to create a database for a pet shop. The pet shop is a sole trader in the UK, selling pet animals at affordable prices. Until now, all data relating to the animals in stock has been stored and managed manually using a paper based ledger. However, due to this manual way of processing, there have been times when data has been lost or incorrect and there are also concerns around data redundancy and security. There is also a need to improve the inventory and sales data management.

To improve the management of stock, it has been recommended to develop a database to manage the data including sales of the Pet shop. In addition, they are thinking of upscaling their business by selling pet related items and provide some services for the customers like different varieties of food, toys, accessories, and medicines. Also, to attract new customers they are conducting mini events such as talks for pet management, protection, and insurance.

The Pet shop owner would like you to rationalise this business idea to a relational database design. You have been assigned as a database developer to undertake the following:

Task 1: Propose the design of a relational database for the pet shop’s new business plan for their other items and accessories’ sales as well as mini events.
Task 2: Implement the database for the pet sales shop.
Task 3: Test the implementation of the pet sales shop.

Please make sure to use your university-allocated database account to answer the questions of Task 2 and 3, otherwise, your assignment work will not receive any marks for the tasks. Provide execution screenshots in the report evidencing each step of your work, as well as text-based copies of any SQL.

Part 1: Database Design And Development

Task 1: New Business Idea Database Design

To improve their business, the Pet shop is planning to sell different varieties of food, toys, accessories, and medicines. Also, to attract new customers they are conducting mini events such as talks for pet management, protection, and insurance. The Pet shop owner would like you to rationalise this new business idea to a relational database design.

A. Logical Model

Based on the scenario and the data provided, create a logical model for the database using a crow’s foot style Entity-Relationship Diagram (e.g., Barker/UML, do NOT use the Chen diagramming style). Make sure to normalise the data to 3NF, adding keys and identifier columns as appropriate. Annotations for the normalisation process is required to prove that all the entities are in 3NF. Make sure to note any assumptions made along with their reasons.

B. Physical Model

Using your logical model as a basis, create a physical model Entity-Relationship diagram using crow’s foot notation for the relational database provided by the Arden University. Make sure to use appropriate naming conventions and to identify data types, keys, and constraints. Note any assumptions made or further changes to the structure beyond those in the logical model.

Task 1: (1200 Words Equivalent)
Task 2: Create The Pet Shop Inventory And Sales Relational Database (40 Marks) (LO: 2)

The pet shop owner identified the usage of database management systems and approved a database design for their pet shop’s inventory and sales data management, which is given as the physical model Entity Relationship diagram in the figure 1.

To improve the management of stock and Pet shop sales, the Pet shop owner would like you to develop this relational database and prove its functionalities.

1. Based on Figure 1. Pet Shop Inventory and Sales physical model entity-relationship diagram, create the database using appropriate SQL in the database account provided by the Arden University.
2. Insert 12 Pet records in the Pet table using the sample data in Table 1. The shop does have 6 enclosures, insert those enclosure records in the Enclosure table and 6 different maintenance records for the 6 different types of pets. Also, insert 10 sales transactions for 6 different customers. Please include meaningful records for other tables. The given number of records are the minimum requirements for the database, you are allowed to add more data as required.
3. Display all the tables with the meaningful inserted data.

Figure 1. Pet Shop Inventory And Sales Physical Model Entity-Relationship Diagram

Table 1: Available Pets Data In The Shop

Task 2: (900 words equivalent) (30 marks) (LO: 3)

Part 1 Total: (2100 words equivalent) (70 marks) (LOs 2 & 3)

Part 2: Test The Pet Shop Relational Database

Task 3 (30 Marks) (LO: 1)

Using the Pet shop 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.

Case 1: Pet Care

The shop owner would like the new database to also keep information on animal care. Modify the database tables in task 2 so that the pet care data can be included in it. Data around the recommended medication and their duration are given in Table 2. Update the values for the animals listed in the database.

Table 2. Pet Care

Case 2: Search
To demonstrate the efficiency of searching to the Pet shop owner, create searches based on the below mentioned criteria.

a. Find animal details based on pet type.

b. Find animal details based on price. For example, animals less than £50.

Case 3: Inventory
The animals that the shop stock has changed. Write the queries according to the below mentioned details.

a. A “Labrador Retriever” dog is now being sold, so update the database based on the new sales.

b. Ragdoll cats are no longer sold. Remove the pet breed record to reflect this.

Page 8 of 16
Case 4: Transactions
Write queries to perform the following operations.

a. The cost of a specific dog breed is changed to £150. Update the database accordingly.

b. Group the pet by their type and display all the available pets in the database sorted based on pet price.

c. Calculate the predicted sales amount for the pets based on the pet_unit_price and available quantity. Dogs and cats will be sold with 15% profit rate of the actual unit price; Other pets will be sold with 10% profit rate of the actual price.

(900 words equivalent) (30 marks) (LOs: 1)

Make sure to use your university-allocated database account to answer the questions of Task 2 and 3, otherwise, your submitted work will not receive any marks for the tasks. Provide execution screenshots along with their output in the report evidencing each step of your work, as well as text-based copies of any SQL.