COM4015 sample demonstrates pet shop relational database from logical modeling (Pet, Product, Customer entities) to physical SQL implementation, 3NF normalization, queries for inventory, sales tracking, and event management.
This section covers COM4015 database design for a pet shop, from logical modeling with Crow’s Foot notation and 3NF normalization (Pet, Product, Customer entities) to physical SQL schema creation with constraints, keys, and sample data insertion for inventory/sales management.
6 Years | PhD
(a) Logical Model
The pet shop is planning to grow the store by selling various pet essentials including foods, toys, accessories, and medications as well as bringing mini-events associated with pets care, protection, and insurance. To address these forms of operations in business, a structured relational database is the most appropriate method of data management. The conceptual model is planned and drawn based on Crow’s Foot Notation and normalizes data up to Third normal form to eliminate redundancy and for better result.
The entities proposed in the logical model are Pet, Product, Service, Customer, Sales, Service Booking, Event and Event Participation. The Pet entity holds details of animals for sale such as breeds, type of pet, age, quantity, and cost. The Product entity has food items, accessories, toys, and more that the Pedigree brand has in its store including their name, category, cost, and the number in stock. The Service entity records details of other services like grooming, check up on the animal, and training sessions etc. This form of entity stores all the details of a customer such as name, phone number and address among others. The Sales entity deals with both pet and products and records all activities that involve sales. The Event section entails information on mini-events of the shop detailing the event name, description, date and location. The Event_participation entity is the connection between Customers and Events that imply attending events.
Get assistance from our PROFESSIONAL ASSIGNMENT WRITERS to receive 100% assured AI-free and high-quality documents on time, ensuring an A+ grade in all subjects.
Normalization helps are to ensure that each of the attribute in every table is dependent on the primary key which helps to prohibit data anomaly. The normal for increment 1NF process makes sure all the column ideally consists of atomic goods. In the 2NF process, every attribute depends on the whole of the primary key and not on a part of it as found in 1NF. They are involved in achieving the 3NF stage by preventing the elements, which are involved in transitive dependencies, from depending on other elements in addition to the key table.
Samples and reference materials aid students in grasping assignment structures and enhancing their academic abilities. We provide Assignment Writing Help while ensuring originality. The COM4015 Introduction to Databases Assignment Sample showcases logical/physical models, SQL schema, normalisation, and pet shop queries. This sample serves solely educational purposes.
(b) Physical Model
The physical model maps directly from the logical model and defines the attributes names for a particular table, the data type for each attribute, the constraints on the table and the relationship of the table with other tables. It has been designed by using Crow’s Foot Notation and among its characteristics, it ensures the reliability of the relations between tables with the help of Primary Keys and Foreign Keys.
Attributes of the Pet table are pet_ID, pet_breed, pet_type, age, quantity, unit_price. The attributes in the Product table include product_ID, product_name, product_type, price, and stock_quantity. The Service table has the fields as service_ID, service_name, service_price, and duration. Customer table contains columns like customer_ID, customer_name, customer_contact and customer_address. The Sales table tracks the usage through sales_ID, customer_ID, product_ID, pet_ID, sale_date and total_price. The Event table contains the event_ID, the event_name, the event_description, the event_date, and the event_location. The Event_Participation table links customers to events through participation_ID, event_ID, and customer_ID.
A primary or foreign key constraint proves that data in the one table is used to make reference to the data in the other table. The choice of VARCHAR for textual attributes, INT for numerical attributes, and DECIMAL for price associated fields is appropriate to store and process data. These include NOT NULL constraints for the columns that require a value to be entered and CHECK constraints for the validation of the value entered.
The Pet Shop requires a relational database to store the pet inventory, customer information, and the actual sales transactions. The information is divided into many tables for pets, general maintenance, enclosures, customers, and finally the sales records. In the next sections, the creation of the database schema, the insertion of the data, and the retrieval of the data are explained.
(a) Create the Database Schema
SQL was used in order to create the PetShopDB schema that forms the structure of the database. The data schema includes several tables, namely, Pet_Maintenance, Enclosure, Pet, Pet_Lives_In, Customer and Sales. Each table included proper data type, primary key, foreign key, and constraints in order to minimize compromise on data quality.
The Pet table was created to capture data related to breed, type, age, quantity, and price of the pets as well as information regarding maintenance on the pets. The Pet_Maintenance table bears the foreign key relationship with the Pet table.
(b) Insert Data into Tables
After the database schema had been designed, data was loaded into the tables with inventory details of the pet shop and customers.
(c) Display Tables with Inserted Data
The next step after data insertion was to use queries to show the contents of each such created table.
After the formation of the database, several tests were run with basic SQL statements to check the working of the database. These were query options incorporated into the system that included pet care recall, searching for pets according to desired specifications, concern with inventory, changing of prices, and estimating consumer’s sales revenue.
Case 1: Pet Care
To improve the pet data, two new fields (recommended_medication and medication_duration) were introduced in the Pet table.
Case 2: Search Functionality
Case 3: Inventory Management
A query was also done to subtract 1 from the quantity of Labrador Retriever following a sale.
Case 4: Transactions and Reports
This operational query was to alter the price of the German Shepherd breed.
References
Introduction Get free samples written by our Top-Notch subject experts for taking Assignment Help...View and Download
Introduction: Health Inequalities in India Health inequalities are one of the major problems of every country. Every country has...View and Download
Johnson's Mazda Oxford: Innovation, Sustainability & Growth Johnsons Mazda Oxford is thus a symbol of the technological...View and Download
Introduction to Predicting Garment Worker Productivity with ANN Assignment The goal of this study is to create predictive...View and Download
Introduction - Ethics, Working Collaboratively And Values Assignment Sample At Rapid Assignment Help, we combine expertise and...View and Download
Introduction Get free samples written by our Top-Notch subject experts for taking online Assignment Help services. LO...View and Download
Copyright 2025 @ Rapid Assignment Help Services
