Database Implementation: SQL Queries and Table Creation

This document presents a comprehensive solution to a database implementation assignment. It begins with the creation of several tables: `customer`, `orderlines`, `orders`, `parts`, `salesregion`, and `salesrepresentatives`, using SQL `CREATE TABLE` statements. Primary and foreign keys are defined using `ALTER TABLE` statements to establish relationships between the tables, ensuring data integrity. The solution then populates these tables with sample data using `INSERT INTO` statements. Finally, the assignment demonstrates data retrieval using SQL `SELECT` statements, including a query to display customer names and the parts they ordered, utilizing `INNER JOIN` to combine data from multiple tables. The solution also includes a bibliography of related database and SQL resources.

Running head: DATABASE IMPLEMENTATION
Database Implementation
Name of the Student
Name of the University
Author’s note:

1DATABASE IMPLEMENTATION
Create Tables:
CREATE TABLE `customer` (
`customerID` int(11) NOT NULL,
`lastName` varchar(200) NOT NULL,
`firstName` varchar(200) NOT NULL,
`streetAddress` varchar(200) NOT NULL,
`city` varchar(200) NOT NULL,
`state` varchar(20) NOT NULL,
`zipCode` int(11) NOT NULL,
`currentBalance` decimal(12,2) NOT NULL,
`creditLimit` int(11) NOT NULL,
`salesRepID` int(11) NOT NULL
);
CREATE TABLE `orderlines` (
`orderID` int(11) NOT NULL,
`partID` char(10) NOT NULL,
`numberOrdered` int(11) NOT NULL,
`quotedPrice` decimal(12,2) NOT NULL

2DATABASE IMPLEMENTATION
);
CREATE TABLE `orders` (
`orderID` int(11) NOT NULL,
`orderDate` date NOT NULL,
`customerID` int(11) NOT NULL,
`shippingDate` date DEFAULT NULL
);
CREATE TABLE `parts` (
`partID` char(10) NOT NULL,
`partDescription` varchar(200) NOT NULL,
`unitsOnHand` int(11) NOT NULL,
`class` char(5) NOT NULL,
`warehouseNumber` int(11) NOT NULL,
`unitPrice` decimal(12,2) NOT NULL
);
CREATE TABLE `salesregion` (
`regionID` char(5) NOT NULL,
`regionDescription` varchar(200) NOT NULL,

3DATABASE IMPLEMENTATION
`salesRepID` int(11) NOT NULL
);
CREATE TABLE `salesrepresentatives` (
`salesRepID` int(11) NOT NULL,
`lastName` varchar(200) NOT NULL,
`firstName` varchar(200) NOT NULL,
`streetAddress` varchar(200) NOT NULL,
`city` varchar(200) NOT NULL,
`state` varchar(20) NOT NULL,
`zipCode` int(11) NOT NULL,
`totalCommission` decimal(12,2) NOT NULL,
`commissionRate` decimal(12,2) NOT NULL
);
ALTER TABLE `customer`
ADD PRIMARY KEY (`customerID`),
ADD KEY `salesRepID` (`salesRepID`);
ALTER TABLE `orderlines`
ADD PRIMARY KEY (`orderID`,`partID`),

4DATABASE IMPLEMENTATION
ADD KEY `partID` (`partID`);
ALTER TABLE `orders`
ADD PRIMARY KEY (`orderID`),
ADD KEY `customerID` (`customerID`);
ALTER TABLE `parts`
ADD PRIMARY KEY (`partID`);
ALTER TABLE `salesregion`
ADD PRIMARY KEY (`regionID`),
ADD KEY `salesRepID` (`salesRepID`);
ALTER TABLE `salesrepresentatives`
ADD PRIMARY KEY (`salesRepID`);
ALTER TABLE `customer`
ADD CONSTRAINT `customer_ibfk_1` FOREIGN KEY (`salesRepID`)
REFERENCES `salesrepresentatives` (`salesRepID`);
ALTER TABLE `orderlines`
ADD CONSTRAINT `orderlines_ibfk_1` FOREIGN KEY (`orderID`) REFERENCES
`orders` (`orderID`),
ADD CONSTRAINT `orderlines_ibfk_2` FOREIGN KEY (`partID`) REFERENCES
`parts` (`partID`);

5DATABASE IMPLEMENTATION
ALTER TABLE `orders`
ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customerID`) REFERENCES
`customer` (`customerID`);
ALTER TABLE `salesregion`
ADD CONSTRAINT `salesregion_ibfk_1` FOREIGN KEY (`salesRepID`)
REFERENCES `salesrepresentatives` (`salesRepID`);
Insert into Tables:
INSERT INTO `customer` (`customerID`, `lastName`, `firstName`, `streetAddress`,
`city`, `state`, `zipCode`, `currentBalance`, `creditLimit`, `salesRepID`) VALUES
(311, 'Charles', 'Don', '48 College', 'Ira', 'MI', 49034, '825.75', 1000, 12),
(315, 'Daniels', 'Tom', '914 Cherry', 'Kent', 'MI', 48391, '770.75', 750, 6),
(405, 'Williams', 'Al', '519 Watson', 'Grant', 'MI', 49219, '402.75', 1500, 12),
(412, 'Adams', 'Sally', '16 Elm', 'Lansing', 'MI', 49224, '1817.50', 2000, 3),
(522, 'Nelson', 'Mary', '108 Pine', 'Ada', 'MI', 49441, '98.75', 1500, 12),
(567, 'Dinh', 'Tran', '808 Ridge', 'Harper', 'MI', 48421, '402.40', 750, 6),
(587, 'Galvez', 'Mara', '512 Pine', 'Ada', 'MI', 49441, '114.60', 1000, 6),
(622, 'Martin', 'Dan', '419 Chip', 'Grant', 'MI', 49219, '1045.75', 1000, 3);
INSERT INTO `orderlines` (`orderID`, `partID`, `numberOrdered`, `quotedPrice`)
VALUES

6DATABASE IMPLEMENTATION
(12491, 'BT04', 1, '149.99'),
(12491, 'BZ66', 1, '399.99'),
(12494, 'CB03', 4, '279.99'),
(12498, 'AZ52', 2, '12.95'),
(12504, 'CZ81', 2, '325.99');
INSERT INTO `orders` (`orderID`, `orderDate`, `customerID`, `shippingDate`)
VALUES
(12491, '2011-07-02', 311, '2011-07-22'),
(12494, '2011-07-04', 315, '2011-07-22'),
(12498, '2011-07-05', 522, '0000-00-00'),
(12504, '2011-07-05', 522, '0000-00-00');
INSERT INTO `parts` (`partID`, `partDescription`, `unitsOnHand`, `class`,
`warehouseNumber`, `unitPrice`) VALUES
('AX12', 'Iron', 104, 'HW', 3, '244.95'),
('AZ52', 'Darthboard', 20, 'SW', 2, '12.95'),
('BH22', 'Corn Popper', 95, 'HW', 3, '24.95'),
('BT04', 'Gas Grill', 11, 'AP', 2, '149.99'),
('BZ66', 'Washer', 52, 'AP', 3, '39.99'),
('CA14', 'Griddle', 78, 'HW', 3, '39.99'),

7DATABASE IMPLEMENTATION
('CB03', 'Bike', 44, 'SG', 1, '299.99'),
('CX11', 'Blender', 112, 'HW', 3, '22.95'),
('CZ81', 'Treadmill', 68, 'SG', 2, '349.95');
INSERT INTO `salesregion` (`regionID`, `regionDescription`, `salesRepID`) VALUES
('C', 'Central', 3),
('E', 'Eastern', 3),
('SC', 'South Central', 12),
('UP', 'Upper Pennisula', 12),
('W', 'Western', 6);
INSERT INTO `salesrepresentatives` (`salesRepID`, `lastName`, `firstName`,
`streetAddress`, `city`, `state`, `zipCode`, `totalCommission`, `commissionRate`) VALUES
(3, 'Jones', 'Mary', '123 Main', 'Grant', 'MI', 49219, '2150.00', '0.50'),
(6, 'Smith', 'William', '102 Raymond', 'Ada', 'MI', 49441, '4912.50', '0.70'),
(12, 'Diaz', 'Miguel', '419 Harper', 'Lansing', 'MI', 49224, '2150.00', '0.50');
Table Contents:
select * from customer;

8DATABASE IMPLEMENTATION
select * from parts;
Select customer ordered parts
select concat(customer.lastName, ' ', customer.firstName) AS Customer_Name,
parts.partDescription from customer INNER JOIN orders on customer.customerID =
orders.customerID INNER JOIN orderlines on orders.orderID = orderlines.orderID INNER
JOIN parts on orderlines.partID = parts.partID;

9DATABASE IMPLEMENTATION
Bibliography:
Anderson, D., 2018. Modeling and Analysis of SQL Queries in PHP Systems.
Challawala, S., Lakhatariya, J., Mehta, C. and Patel, K., 2017. MySQL 8 for Big Data: Effective
Data Processing with MySQL 8, Hadoop, NoSQL APIs, and Other Big Data Tools. Packt
Publishing Ltd.