Chapter 2 Lab – Dipping A Toe In SQL
Chapter 2 Lab – Dipping a toe in SQL
The purpose of this lab is to get comfortable with SQL. Do the two debug assignments on Blackboard FIRST
You will create a small database in 2NF in APEX that captures customer information including which products the customer has bought, their identifying information (ID, First Name, Last Name, Address, Phone Number, and three other pieces of information), and product information including which customers have bought the product, which department the product is from, how much it costs to buy, how much we sell it for, the productID. In addition to these fields, three extra pieces of information. Please decide how this information is stored and in which tables.
Use the slides and other provided resources to create a small database in APEX using SQL commands. The deliverables include the script used to create your database in a .txt file uploaded to Blackboard and a one-page report explaining your database design, including why it is in 2NF. The report should be single-spaced, using Calibri font size 11 or equivalent.
Paper For Above instruction
Developing a functional and properly normalized database is vital for efficient data management and retrieval. In this assignment, I designed a database to capture customer and product information with adherence to the Second Normal Form (2NF), ensuring that data redundancy is minimized and dependencies are well-structured. This paper discusses the decisions made during the database design process, including table structures, relationships, and normalization justifications.
Database Design Overview
The database comprises primarily three tables: Customers, Products, and Customer_Products (a junction table). This design efficiently captures the many-to-many relationship between customers and products—each customer can buy multiple products, and each product can be purchased by multiple customers. Additional details are stored within these tables, alongside extra fields to meet assignment requirements.
Customer Table
The Customer table contains core customer information: CustomerID (primary key), FirstName, LastName, Address, PhoneNumber, and three additional pieces of data, such as Email, Date_of_Birth, and Membership_Status. The primary key, CustomerID, uniquely identifies each customer. This table is in 2NF because all non-key attributes depend solely on the primary key, with no partial dependencies. Customer details are atomic, and there are no repeating groups, satisfying the criteria for 2NF.
Product Table
The Product table includes ProductID (primary key), ProductName, Department, CostPrice, SalePrice, and three extra information fields like Supplier, Stock_Level, and Warranty_Period. These attributes depend entirely on the primary key ProductID. The table is normalized to 2NF because all non-key attributes are fully dependent on ProductID, and transitive dependencies are avoided since related data like department or supplier are stored separately.
Customer_Products (Junction Table)
This table links customers and products, enabling many-to-many relationships. It contains CustomerID, ProductID, and additional data such as PurchaseDate, Quantity, and PaymentMethod. The composite primary key is (CustomerID, ProductID), with other fields fully dependent on this combination. This junction table ensures that the database conforms to 2NF by eliminating partial dependencies and redundancy in customer and product details.
Normalization to 2NF
The design adheres to 2NF because it eliminates partial dependencies by ensuring that non-key attributes depend on the entire primary key. For example, in the Customer_Products table, PurchaseDate, Quantity, and PaymentMethod depend on the combination of CustomerID and ProductID. No attribute depends solely on part of a composite key, thereby satisfying 2NF's core requirement.
Furthermore, by separating customer and product details into distinct tables, we avoid repeating data and promote data integrity. Additional attributes like department, supplier, or warranty are stored in their respective tables or fields, preventing transitive dependencies that could violate higher normalization forms.
Additional Considerations
Three additional pieces of information are incorporated into each table to meet assignment specifications. In the Customer table, these could be Email, Date_of_Birth, and Membership_Status. In the Product table, Supplier, Stock_Level, and Warranty_Period. For the junction table, PurchaseDate, Quantity, and PaymentMethod are selected to add meaningful context to transactions.
This flexible design allows for easy expansion and maintenance. For instance, new product features or customer attributes can be integrated without disrupting the existing schema, thanks to normalization principles.
Conclusion
In conclusion, this database design successfully captures customer and product data while maintaining 2NF standards. The separation of concerns through distinct tables and a junction table prevents redundancy and ensures that each piece of information is stored efficiently. The normalization process enhances data consistency, reduces anomalies, and facilitates scalable data management.