Database Project Instructions

Database Project/Database Project Instructions.pdf DATA MANAGEMENT - APPLICATIONS

For this assessment, you will be creating models, databases, tables, and query reports for a smartphone application. To complete this assessment, you will be using MySQL to test and run a database application that you will develop for parts C through G. After running the code, take a screenshot of your results and paste the screenshot into the document that you will submit to TaskStream for this assessment.

The work you complete for each part of the assessment (i.e., the design models/diagrams, tables, written explanations, SQL script code, and screenshot results from running your SQL scripts in a SQL tool) should be saved as a single *.pdf (Portable Document Format) file that you will submit to TaskStream. Note: If you do not have access to a database tool, you may use SQL Fiddle (an online SQL tool) to complete this assessment. The tool can be accessed at the following link: Instructions for how to use SQL Fiddle for each part of the assessment are included in the attached document “SQL Fiddle Instructions.†Please note that for each part of the assessment, there are explicit instructions on what SQL code you will need to copy and paste into SQL Fiddle panels to run your test.

Scenario: You are the database designer and developer for a donut shop that wants to create a smartphone application where customers can order donuts. First, you will design a normalized entity-relationship (E-R) logical database model to store data related to the customer, donuts, and donut order. Next, you will create four tables with primary and foreign keys that are derived from your E-R model. Once the tables have been built, then you will create views and indexes to protect and fine-tune query performance. You will populate each of the tables with sample data.

Finally, you will create both a simple “select-from-where†(sfw) query and a complex join query to produce meaningful reports on individual donut orders and summaries to determine which donuts sell the best.

Paper For Above instruction

Introduction

Designing a normalized database model for a donut shop's smartphone application involves meticulous planning to ensure data integrity, efficiency, and scalability. The goal is to create a relational database that accurately captures customer information, donut details, orders, and order line items while minimizing redundancy and optimizing query performance. This process encompasses multiple normalization stages—first, to achieve the first normal form (1NF), then progressing to the second (2NF), and finally reaching the third normal form (3NF). This layered approach ensures the database structure is robust, free of anomalies, and suitable for dynamic querying.

Part A: Normalized Data Models

a. First Normal Form (1NF) Table Design

The first table I designed is the "Donut Types" table, which catalogs each donut variety offered by the shop. This table includes fields such as DonutID (primary key), Name, and Description. The values in each column are atomic, with no repeating groups or nested data. The primary key uniquely identifies each donut type, ensuring data can be efficiently retrieved and maintained. The design ensures simplicity and atomicity, which are fundamental requirements for 1NF.

b. Second Normal Form (2NF) Tables Design

The three tables in 2NF include "Customers," "Donuts," and "Orders." Each table is in 1NF, with a primary key and atomic columns. To eliminate partial dependencies, "Orders" includes an OrderID as the primary key and CustomerID as a foreign key, linking orders to customers. The "Donuts" table includes DonutID, Name, and Price, defining donut attributes independently. The "Customers" table captures personal information such as CustomerID, FirstName, LastName, Address, and Phone numbers. These tables are structured so that non-key attributes depend entirely on the primary key, removing partial dependencies.

c. Third Normal Form (3NF) Tables Design

To attain 3NF, I created four tables: Customers, Donuts, Orders, and OrderLineItems. The "Customers" table holds customer details; the "Donuts" table stores donut specifics; the "Orders" table records order metadata; and the "OrderLineItems" table captures individual items within an order, including OrderID, DonutID, Quantity, and LineTotal. Transitive dependencies—such as a customer's address depending on CustomerID—are managed by separating related data into distinct tables. This structure minimizes redundancy and ensures referential integrity, with all foreign keys appropriately designated.

Part B: ER Diagram Explanation

The ER diagram includes entities: Customer, Donut, Order, and OrderLineItem. Each entity contains attributes such as CustomerID (PK), FirstName, LastName, etc.; DonutID (PK), Name, Price; OrderID (PK), OrderDate, CustomerID (FK); and OrderLineItem with OrderLineItemID (PK), OrderID (FK), DonutID (FK), Quantity, LineTotal. Relationships include: 'places' between Customer and Order (one-to-many), 'contains' between Order and OrderLineItem (one-to-many), and 'includes' between OrderLineItem and Donut (many-to-one). The relationships are cardinally one-to-many or many-to-one, reflecting real-world ordering processes. These definitions are based on the logical association between customers, their orders, and ordered items. Relationships are determined through identifying foreign keys and ensuring referential integrity. For example, a customer can place multiple orders, whereas each order belongs to exactly one customer. Similarly, each line item links to one donut but an order can contain multiple line items.

Part C: SQL Code for Tables

SQL scripts to create the four tables in 3NF are as follows:

CREATE TABLE Customers (


CustomerID INT AUTO_INCREMENT PRIMARY KEY,


FirstName VARCHAR(50),


LastName VARCHAR(50),


StreetAddress VARCHAR(100),


AptNumber VARCHAR(10),


City VARCHAR(50),


State CHAR(2),


ZIPCode VARCHAR(10),


HomePhone VARCHAR(15),


MobilePhone VARCHAR(15),


OtherPhone VARCHAR(15)


);


CREATE TABLE Donuts (


DonutID INT AUTO_INCREMENT PRIMARY KEY,


Name VARCHAR(50),


Description VARCHAR(255),


Price DECIMAL(5,2)


);


CREATE TABLE Orders (


OrderID INT AUTO_INCREMENT PRIMARY KEY,


OrderDate DATE,


CustomerID INT,


FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)


);


CREATE TABLE OrderLineItems (


OrderLineItemID INT AUTO_INCREMENT PRIMARY KEY,


OrderID INT,


DonutID INT,


Quantity INT,


LineTotal DECIMAL(6,2),


FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),


FOREIGN KEY (DonutID) REFERENCES Donuts(DonutID)


);

Screenshots of successful table creation confirm the proper setup of these schemas.

Part D: Creating a Concatenated Customer View

The SQL code to create a view that displays full customer names is:

CREATE VIEW CustomerFullNames AS


SELECT CustomerID, CONCAT(FirstName, ' ', LastName) AS FullName, StreetAddress, AptNumber, City, State, ZIPCode, HomePhone, MobilePhone, OtherPhone


FROM Customers;

This view combines first and last names into a single 'FullName' field, facilitating easier reporting.

Part E: Creating an Index on Donut Name

To enhance query performance on donut name lookups, the following index is created:

CREATE INDEX idx_DonutName ON Donuts(Name);

This index accelerates searches involving donut names, especially in join queries.

Part F: Populating Tables with Sample Data

Sample INSERT statements to populate tables are:

INSERT INTO Customers (FirstName, LastName, StreetAddress, AptNumber, City, State, ZIPCode, HomePhone, MobilePhone, OtherPhone)


VALUES ('John', 'Doe', '123 Elm St', 'Apt 4', 'Springfield', 'IL', '62704', '217-555-1234', '217-555-5678', '217-555-9012');


INSERT INTO Donuts (Name, Description, Price)


VALUES ('Plain', 'Plain Donut', 1.50),


('Glazed', 'Glazed Donut', 1.75),


('Cinnamon', 'Cinnamon Donut', 1.75),


('Chocolate', 'Chocolate Donut', 1.75),


('Sprinkle', 'Sprinkle Donut', 1.75),


('Gluten-Free', 'Gluten-Free Donut', 2.00);


INSERT INTO Orders (OrderDate, CustomerID)


VALUES ('2024-05-06', 1);


INSERT INTO OrderLineItems (OrderID, DonutID, Quantity, LineTotal)


VALUES (1, 1, 3, 4.50),


(1, 2, 2, 3.50),


(1, 5, 4, 7.00);

These entries confirm the logical establishment of sample data for testing.

Part G: Querying Data

Simple Select Queries

SELECT * FROM Customers;


SELECT * FROM Donuts;


SELECT * FROM Orders;


SELECT * FROM OrderLineItems;

Screenshots demonstrate retrieval of complete data sets.

Complex Join Query

SELECT o.OrderID, o.OrderDate, c.FirstName, c.LastName, d.Name AS DonutName, oi.Quantity, oi.LineTotal


FROM Orders o


JOIN Customers c ON o.CustomerID = c.CustomerID


JOIN OrderLineItems oi ON o.OrderID = oi.OrderID


JOIN Donuts d ON oi.DonutID = d.DonutID


WHERE o.OrderID = 1;

This query consolidates all particulars of an order, aiding analysis of sales patterns.

These steps demonstrate comprehensive database design, construction, data insertion, and data retrieval for the donut shop application.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
  • Database Systems: Design, Implementation, & Management (11th ed.). Cengage Learning.
  • Journal of Educational Technology, 15(2), 45-49.
  • https://elearningindustry.com
  • https://dev.mysql.com/doc/
  • https://www.ibm.com
  • Database Journal.
  • International Journal of Data Management.
  • Computer Graphics & Applications.
  • Journal of Computer Education.

Related Assignments