Dipping A Toe In SQL ✓ Solved
Dipping A Toe In SQL
Chapter 2 Lab – Dipping a toe in SQL Deadline: Jan. 31st at 11:59pm Purpose The purpose of this lab is to get comfortable with SQL. Do the two debug assignments on Blackboard FIRST Overall Description You will create a small database in 2NF in APEX that captures the following information: 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 you may want to capture) and three other pieces of information. 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 keep in mind it is up to you design how this information is stored and in which tables. What is listed above is NOT going to be your table schema…it’s just pieces of information you need to capture. For example, you may need to add junction tables if the connections are many to many. The specs above give you the information that needs to be available. How you decide to store these in tables is completely up to you. What to do Use the slides and other information provided to you to create a small database in APEX by using SQL commands Deliverable · The script used to create your database in a .txt file uploaded to Blackboard · A one-page report explaining your database design (including why it is in 2NF) single spaces calibiri font 11 or equivalent.
Sample Paper For Above instruction
Introduction
This paper presents the design and creation of a small relational database in second normal form (2NF) using Oracle APEX for a retail scenario involving customers and products. The goal is to develop a normalized database that efficiently captures customer details, product information, and their purchase relationships, while adhering to the principles of 2NF to eliminate partial dependencies. The design process involves identifying entities, defining appropriate tables, establishing relationships, and ensuring data integrity, all supported by SQL scripts.
Database Design Overview
The database comprises three primary tables: Customers, Products, and Purchases. Each table is designed to store distinct pieces of information, minimizing redundancy and maintaining normalization rules up to 2NF. The Customers table captures customer-specific data, including customer ID, first name, last name, address, phone number, and three additional pieces of customer-related information (e.g., email, date of birth, loyalty status). The Products table contains product ID, name, department, costs, and selling prices, along with three extra attributes such as supplier, warranty period, and stock quantity. The Purchases table acts as a junction table that links customers and products, capturing many-to-many relationships, along with purchase date and quantity.
Normalization to 2NF
The schema adheres to 2NF by ensuring that all non-key attributes depend solely on the primary key of their respective tables. In the Customers table, the primary key is customer ID, and all other fields depend entirely on it, eliminating partial dependencies. Similarly, in the Products table, product ID serves as the primary key, with all additional attributes depending solely on it. The Purchases table uses a composite key of Customer ID and Product ID, with other attributes like purchase date and quantity depending entirely on this combination. This structure ensures that there are no partial dependencies, satisfying the criteria for 2NF.
SQL Script Creation
The SQL script begins by creating the three tables with appropriate data types and primary keys. Foreign key constraints establish relationships between the Purchases table and the other two tables. Sample insert statements populate the tables with dummy data, demonstrating the database's functionality. The script is saved in a .txt file and uploaded as required.
Design Rationale
The database design emphasizes normalization to prevent data anomalies and redundancies. Dividing information across dedicated tables and establishing junctions for many-to-many relationships ensures data integrity, efficiency in updates, and easier maintenance. The inclusion of three additional pieces of information per table allows for flexibility and better data granularity, tailored to hypothetical real-world requirements.
References
- Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
- Database Concepts. Pearson.