- PMP(Project Management Plan) Assignment: Managing Fitzwilliam Park Project with Scope, Cost & Schedule Insights
- GA31 Research Methodology and Quantitative Methods Assignment: A Research Plan on Corporate Social Responsibility
- Understanding Dementia Care Assignment Questions: Causes, Diagnosis, Treatment & Family Support
- D/618/7406 Unit 5: Challenges of IT Security in Modern Organizations – Risks, Solutions & Best Practices
- HSC Level 2/3 Unit 012 Assignment: Care Worker Responsibilities and Ways of Working
- CIPD Level 5HR03 Assignment: Understanding Reward Approaches and Their Impact on Performance and Contribution
- CIPD level 5HR02 Assignment: Talent Management and Workforce Planning Unit Guide
- Level 3 D/615/3823 Assignment: Regulation, Protection, and Collaborative Practice in Health and Social Care
- PGM216D Assignment: Bicycle Store Sales Management Application
- MATH6033 Assignment: Epidemiological Investigation of Cardiovascular Health and Tea Consumption Risks
- EH6147 Assingment: Stakeholder Analysis for Quality Improvement in Hand Hygiene Compliance
- Assignment: Investigation of Solution Concentration Through Standard Preparation, Titration, and Colorimetry Techniques
- MATH6033 Assignment: Cardiovascular Risk and Tea Drinking: Epidemiological Analyses
- CIPD level 3 3CO03 Assignment: Core behaviours for people professionals
- DAC4B1: Personal development in adult care settings
- Unit 19 Research Project Assignment 1: Impact of Corporate Social Responsibility on Business Success & Community Wellness
- EG5022 Assignment: Georeferencing and Accuracy Assessment of a Quarry 3D Model Using Photogrammetric GCPs
- Assignment: Financial Performance and Strategic Analysis of a UK Listed Company: A CORE Evaluation and Reflective Review
- 5CNMN002W Assignment: Advanced measurement- Major measurement taking off
- K/650/2298 Level 3 Understanding Roles, Responsibilities, and Effective Partnerships in Health and Social Care
COM4015 Pet Shop Relational Database Design & Implementation Assignment Report – Inventory, Sales & Events System
| University | University of Manchester(UoM) |
| Subject | COM4015 Introduction to Databases |
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.
Do You Need Assignment of This Question
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.
a. 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.
b. 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.
c. 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)
Buy Answer of This Assessment & Raise Your Grades
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.
Are You Looking for Answer of This Assignment or Essay
Is your COM4015 – Pet Shop Relational Database Design & Implementation assignment report not going to be done due to lack of time? No worries! You are at the right place. Our platform provides the best AI-free Student Assignment Help. We have knowledgeable and skilled writers who can give you high-quality, plagiarism-free solutions and assignments with original content. Just write do my assignment. And we help you to stand out from the rest, along with getting high grades. We also provide free sample assignments to help you. Contact us right away.
Answer
