Database Assignment - SQL Queries, Database Design, and Normalization

This document presents a comprehensive solution to a database assignment. It includes SQL queries to retrieve and manipulate data, focusing on inventory management, sales analysis, and customer data. The assignment covers various aspects of database design, including table structures, primary and foreign key relationships, and data normalization principles. Additionally, the solution provides examples of how to implement these concepts in a practical setting, along with references to relevant resources on database normalization and MySQL. The document showcases a practical application of database concepts, offering insights into query optimization and data modeling. The assignment addresses a range of database tasks, from simple data retrieval to more complex analysis of sales data. The solution also references external resources, demonstrating a thorough understanding of database principles and practical application.

Database Assignment
May 21
2019
Student ID:
Student Name:

Report
Question 4
Query 4 a)
SELECT Inventory.ItemID, Inventory.Description FROM Inventory;
Query 4 b)
SELECT count(InvoiceID) AS 'Num_Of_Sales in October' FROM Sales GROUP BY month(InvoiceDate)
HAVING month(InvoiceDate)=10;

Query 4 c)
SELECT sum(SalesInventory.Quantity * Inventory.UnitPrice) as Total_Sale_In_October FROM Inventory
INNER JOIN (Sales INNER JOIN SalesInventory ON Sales.InvoiceID = SalesInventory.SaleInvoiceID) ON
Inventory.ItemID = SalesInventory.ItemID group by month(Sales.InvoiceDate) having
month(Sales.InvoiceDate)=10;
Question 7
Inventory (Item_Number, Description, Quantity_On_Hand, List_Price)
Primary Key Item_Number
Customer (Customer_Number, Name, Street, City, State, ZipCode, CreditLimit, AccountBalance
)
Primary Key Customer_Number
Sales (Invoice_Number, Date, SalesPerson, Customer_Number, Amount )
Primary Key Invoice_Number
Foreign Key Customer_Number references Customer (Customer_Number)
Sales_Inventory (Invoice_Number, Item_Number, Quantity, Actual_Unit_Price )
Primary Key Invoice_Number, Item_Number
Foreign Key Invoice_Number references Sales (Invoice_Number)
Foreign Key Item_Number references Inventory (Item_Number)
Payment (Receipt_Number, Invoice_Number, Customer_Number, AmountPaid, Receipt_Date,
PaymentType, Total_Amount_Received)
Primary Key Receipt_Number
Foreign Key Invoice_Number references Sales (Invoice_Number)

Foreign Key Customer_Number references Customer (Customer_Number)
Question 8
Customer (CustomerID , CustomerName)
Primary Key CustomerID
Item (ItemID ,Description)
Primary Key ItemID
Invoice (InvoiceID , InvoiceDate, OrderDate, CustomerID, ItemID, Quantity)
Primary Key InvoiceID, ItemID
Foreign Key ItemID references Item (ItemID)
Foreign Key CustomerID references Customer (CustomerID)
(TechTarget 2019)
Question 9
Vendor (VendorID , VendorName, VendorAddress)
Primary Key VendorID
Part (PartID ,Description, UnitPrice)
Primary Key PartID
PurchaseOrder (PurchaseOrderID, PurchaseDate, PartID, Quantity, VendorID)
Primary Key PurchaseOrderID, PurchaseDate, PartID
Foreign Key PartID references Part (PartID)
Foreign Key VendorID references Vendor (VendorID)
(Ambysoft Inc. 2019)

References
Ambysoft Inc. (2019). Introduction to Data Normalization: A Database "Best" Practice. [Online].
Available: http://agiledata.org/essays/dataNormalization.html. [Accessed: 21-May-2019]
TechTarget (2019). Database normalization in MySQL: Four quick and easy steps. [Online].
Available: https://www.computerweekly.com/tutorial/Database-normalization-in-MySQL-Four-
quick-and-easy-steps. [Accessed: 21-May-2019]