DATA4200 Data Acquisition and Management Report 1 Sample

Still, Looking for DATA4200 Data Acquisition and Management Report 1 Sample? You’ve reached The Right Place!

Order Now

DATA4200 Data Acquisition and Management Report 1 Sample

Assessment Description

You have been hired as a database designer by a small business that sells products online. The business wants to create a relational database to store information about their customers, products, orders, and payments. They want to be able to view sales data, customer information, and inventory levels.

Case Study:

KBS Pty Ltd is a small business that sells products online. The company is expanding its operations and is looking to improve their data management systems. They currently have a system that stores customer information, product information, and order information in separate files, which has become difficult to manage as the business grows. They want to create a relational database to store all their data in one place, making it easier to manage and report on.

Instructions

• You are required to complete the following tasks:

1. Design an entity-relationship model (ERM) for the database using Lucid Charts or any other ER Modeling tool, and include all required necessary tables, columns, and relationships.

2. Normalise the tables to 3rd normal form (3NF) using appropriate data normalisation techniques.

3. Create a report outlining your database design and data normalisation decisions,

• Your report should be submitted in Word Document and include the following sections:

1. Introduction: A brief overview of the business and the purpose of the database.

2. Entity-Relationship Model: A diagram and explanation of the ERM, including all tables, columns, and relationships using crow’s feet notation.

3. Normalisation: An explanation of the normalisation techniques used and how the tables were normalised to 3NF.

4. Conclusion: A summary of your design decisions and any limitations of your database design.

Solution

Introduction

KBS Pty. Ltd. is a small business that operates in the e-commerce field. They sell various products online ranging from electronics to accessories. They were previously managing their stock and sales via manual file storage, which they realized would get un-reasonable as the company grows. Hence, they are currently in need of a well-designed relational database that can provide them with a secured and robust data storage and access medium (Coronel and Morris, 2019). This report will showcase the design of an Entity Relationship Diagram, describe its details and also discuss the normalization procedure.

ER Diagram

Explanation

Data Description

Business Rules and Assumptions

1. Customer Information:

o Each customer has a unique customer_id.

o Customers can place none or more orders over time.

2. Product Information:

o Each product has a unique product_id.

o Products have a set price, which are not meant change over time (this means that the price history of the products are not tracked).

3. Orders:

o An order is linked to one customer but can contain multiple products.

o Orders are identified by order_id and have a delivery address and status.

o Each order must be assigned a delivery address.

4. Order Items:

o Each order can have multiple products, and the same product can appear in different orders.

o No order_item record can contain the same product twice for the same order_id. In order to record multiple purchase of the same product, quantities can be altered accordingly for bond university assignment help.

5. Payments:

o Payments are made per order

o Each order may or may not have one payment. This means that in case an order is cancelled, it may not require a payment method linked.

o Payment statuses such as "Paid," "Pending," or "Failed" are tracked.

Relationship Details with Cardinality

1. CUSTOMER to ORDER:

o Relationship: 1:N ("places")

o A customer can place multiple orders, but each order belongs to one customer.

2. ORDER to ORDER_ITEM:

o Relationship: 1:N ("contains")

o An order can contain multiple order items, but each order item belongs to one specific order.

3. PRODUCT to ORDER_ITEM:

o Relationship: 1:N ("is included in")

o A product can appear in multiple order items (across various orders), but each order item is linked to one product.

4. ORDER to PAYMENT:

o Relationship: 1:1 ("is paid by")

o Each order can have only one payment, and a payment is linked to one order.

Normalization

The above ERD is already in its 3RD Normal form. The process below highlights how the requirement from the case study was adapted into the design process, and then normalized from 1NF, into 2NF and finally, the 3rd Normal Form was prepared. In order to ensure that the database designed in the above ERD is completely normalized to its 3rd Normal Form. Normalization test and conversion will be performed on the tables one by one, explaining the criteria for each normal form.

1NF Validation:

• Each table shall have a primary key and all fields store atomic values (no multi-valued or repeating groups). Also, it is necessary that all rows are unique and have proper column names (Gokila and BalaSubramani, 2019).

CUSTOMER:

• Primary Key: customer_id.

• All columns contain atomic values (no lists, arrays, or repeating groups).

• CUSTOMER is in 1NF.

PRODUCT:

• Primary Key: product_id.

• All columns contain atomic values, with no repeating groups or multivalued attributes.

• PRODUCT is in 1NF.

ORDER:

• Primary Key: order_id.

• All columns store atomic values.

• ORDER is in 1NF.

ORDER_ITEM:

• Primary Key: Composite key (order_id, product_id).

• All attributes store atomic values.

• ORDER_ITEM is in 1NF.

PAYMENT:

• Primary Key: payment_id.

• All attributes are atomic.

• PAYMENT is in 1NF.

2NF Validation

2NF requires no partial dependency (non-prime attributes must depend on the whole primary key, not part of it).

CUSTOMER:

• Primary Key: customer_id.

• All non-prime attributes (first_name, last_name, email, phone, address) fully depend on customer_id.

• CUSTOMER is in 2NF.

PRODUCT:

• Primary Key: product_id.

• All non-prime attributes (product_name, description, price, stock_quantity) fully depend on product_id.

• PRODUCT is in 2NF.

ORDER:

• Primary Key: order_id.

• All non-prime attributes (customer_id, order_date, delivery_address, status) fully depend on order_id.

• ORDER is in 2NF.

ORDER_ITEM:

• Primary Key: Composite key (order_id, product_id).

• All non-prime attributes (quantity, item_total) depend on the full composite key (order_id, product_id) and not on a part of it.

• ORDER_ITEM is in 2NF.

PAYMENT:

• Primary Key: payment_id.

• All non-prime attributes (order_id, payment_date, amount, payment_method, status) fully depend on payment_id.

• PAYMENT is in 2NF.

3NF Validation

3NF requires no transitive dependency (non-prime attributes depend on other non-prime attributes).

CUSTOMER:

• Primary Key: customer_id.

• There are no transitive dependencies (no non-prime attribute depends on another non-prime attribute).

• CUSTOMER is in 3NF.

PRODUCT:

• Primary Key: product_id.

• There are no transitive dependencies.

• PRODUCT is in 3NF.

ORDER:

• Primary Key: order_id.

• customer_id is a foreign key but does not create any transitive dependency with other attributes (order_date, delivery_address, status).

• ORDER is in 3NF.

ORDER_ITEM:

• Primary Key: Composite key (order_id, product_id).

• There are no transitive dependencies among non-prime attributes (quantity, item_total).

• ORDER_ITEM is in 3NF.

PAYMENT:

• Primary Key: payment_id.

• There are no transitive dependencies between non-prime attributes (order_id, payment_date, amount, payment_method, status).

• PAYMENT is in 3NF.

Conclusion:

• All tables (CUSTOMER, PRODUCT, ORDER, ORDER_ITEM, PAYMENT) satisfy 1NF, 2NF, and 3NF.

• The given design is fully normalized and complies with 3NF.

Conclusion

It can hence be concluded that a relational database can be of great importance to any e-commerce business, in order to easily maintain their operations, compared to maintaining a file based data handling system. As for the database design, five unique tables were created that serve important purposes in the design, for example Customer table stores customer’s details, Products table stores product information and Orders store order details and so on. Relationships were setup between each table to ensure that they are meaningfully connected. The ER Diagram that was created, was already in its Third Normal Form as ensured by the normalization check above. This ensures that the database has been designed in an appropriate and robust manner, and can be used for practical data storage and access.

References

Coronel, C. and Morris, S., 2019. Database systems: design, implementation and management. Cengage learning.

Gokila, D. and BalaSubramani, S., 2019. Impact of Normalization in Future.

Fill the form to continue reading
Would you like to schedule a callback?
Send us a message and we will get back to you

Main Services

Highlights

Earn While You Learn With Us
Confidentiality Agreement
Money Back Guarantee
Live Expert Sessions
550+ Ph.D Experts
21 Step Quality Check
100% Quality
24*7 Live Help
On Time Delivery
Plagiarism-Free

Our Samples

Get Instant Help
University Assignment Help

Still Finding University Assignment Help? You’ve Come To The Right Place!


CAPTCHA
AU ADDRESS
81 Isla Avenue Glenroy, Mel, VIC, 3046 AU
CONTACT
+61 493416265
EMAIL
support@uniassignmenthelp.net