Posted: December 17th, 2014

RELATIONAL ALGEBRA AND SQL DML STATEMENTS

RELATIONAL ALGEBRA AND SQL DML STATEMENTS

Project description
Developing a Database System: Query the Tables

Database Queries and Reports

For the same PC Store database, generate reports for the output specified in SLP 2:

Sales transactions list
Inventory status list
Monthly revenue and profit report
Monthly report of PCs not selling
Monthly report of top selling PCs
Inventory status query by product
Comparison report for the sales of desktops vs. laptops
You may add any other output that you think is appropriate.

Write a 2- to 5-page paper that includes your SQL statements and screenshots of the result tables in DBMS.

SLP Assignment Expectations
Use SQL properly for data manipulation, mainly querying the tables to obtain required information.
Communicate effectively with your audience.
———-
Added on 16.12.2014 22:13
please see related docs

The database will be made up of many different tables. Considering the store is mainly an inventory of computers, the first table to be added to the database is

product. Each computer comes from different manufacturers such as Apple, Toshiba, HP and Dell. This implies that there needs to be a manufacturer table that can store

each of the manufacturers along with the contact information for that manufacturer. The computer product table will store how many of each type of computer is on hand.
To track the computers sold, there should obviously be an orders table. This table will store the item that was purchased (product_num) and also reference the invoice

number (invoice_num). There is also an invoice table that stores the invoice number, invoice customer number and invoice employee number. Taking in mind the number of

store keepers, there needs to be a table to store employee details. The columns include employee name, city, state, employee status, salary and hire date. Finally, we

need a table to track employee job status. This has the employee status and employee number fields. There needs to be an event that triggers the restocking of items in

the computer product table. When this event occurs, it should be recorded in the database along with the date and status. For instance, if the store is running low on

stock of Dell’s latitude laptop, something should be triggered that stores a row in a restock table with the item’s inventory number and restock status. The restock

status is a simple flag to show if the item has been reordered. The columns could include:  not reordered, reordered, backordered, and no longer available. Of course,

just like the sales table, this table will contain dates for all of the important events (i.e., when the restock is triggered and when the order is placed).
An order may contain more than one order item. In this case, a join table will need to be created. A join table is used when there is a need to join the primary keys

of two tables to facilitate a potential many-to-many relationship. In this particular instance, it could be argued that an item in the computer product table may exist

in many orders. Likewise, an order may contain many items from the computer product table.
There are various foreign keys throughout the tables. The Employees’ emp_status  is a foreign key to the emp_job_status table. The customer number is a foreign key to

the invoices table. The restock table obviously has a foreign key to the computer products table. These foreign keys tie the tables together.
It is also important that various indexes are added to the tables to support the more comment reports. For instances, an index could be added to the computer product

number within the products table. Of course, it must be taken into consideration that indexes can decrease the efficiency of inserts and updates. Therefore, they

should be chosen and implemented wisely.
The database will have three packages:  The Sales Package, The Inventory Package, and The Reports Package. These packages will hold various procedures that perform the

operations that are needed for the system. For instance, the sales package will have procedures to perform a sale. It may also consider procedures to reverse a sale,

refund a sale, or update a sale. The reports package will obviously hold all of the procedure necessary to return reports that are important to management.
As previously mentioned, there needs to be something to trigger the restocking of items. The sales package procedures will have an impact on the quantity on hand value

within the computer product table. The quantity will decrease as sales are processed. The inventory table could hold a restock level for each item. Also, a trigger is

added to the database that runs after update on the computer product table. The trigger will review the current quantity versus the restock level. If the current

quantity is now equal to the restock level, a row will be inserted into the restock table. In this manner, most of the functionality of the database is implemented and

complete at the DBMS level.
Another trigger executed at each invoice table insertion or update, checks stock against the desired order quantity and decides whether the transaction can proceed.

Finally, the ordered quantity is subtracted from the inventory product table.
To generate the “Sales Transaction List” a user-input date range will be required from the sales table; the software will display all rows (or owner-specified subset)

for primary keys within the given date range.
The “Inventory Status List” the user will enter a given date in the products table, and the software will display the current inventory at hand. The table Products

will be joined with the orders table to display inventory found in the Products table but not in the Orders table.
To show a “Monthly Report of PCs not selling” the user will query the products found in the products table but featured rarely or not found at all in the orders table.

The products table will be joined to the orders table using the product_num column to display this report.
To display “Inventory Status Query by Product” the user will enter a product name e.g a computer name like Dell in the products table. The products table will be

joined with the orders table using the product_num column to display this report.
Another report could be of the products most ordered. The user will enter the products number in the orders table and the system will display the item with most

product_num entries. This could be done using a count function.

— phpMyAdmin SQL Dump
— version 4.1.12
— http://www.phpmyadmin.net
—
— Host: 127.0.0.1
— Generation Time: Dec 04, 2014 at 02:35 PM
— Server version: 5.6.16
— PHP Version: 5.5.11

SET SQL_MODE = “NO_AUTO_VALUE_ON_ZERO”;
SET time_zone = “+00:00″;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

—
— Database: `new_database`
—

— ——————————————————–

—
— Table structure for table `customer`
—

CREATE TABLE IF NOT EXISTS `customer` (
`customer_num` int(11) NOT NULL,
`customer_name` varchar(45) DEFAULT NULL,
`customer_address` varchar(45) DEFAULT NULL,
PRIMARY KEY (`customer_num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

— ——————————————————–

—
— Table structure for table `employees`
—

CREATE TABLE IF NOT EXISTS `employees` (
`emp_num` int(11) NOT NULL,
`emp_name` varchar(45) DEFAULT NULL,
`city` varchar(45) DEFAULT NULL,
`state` varchar(45) DEFAULT NULL,
`emp_status` varchar(45) DEFAULT NULL,
`emp_salary` varchar(45) DEFAULT NULL,
`emp_hire_date` varchar(45) DEFAULT NULL,
PRIMARY KEY (`emp_num`),
KEY `emp_status` (`emp_status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

— ——————————————————–

—
— Table structure for table `emp_job_status`
—

CREATE TABLE IF NOT EXISTS `emp_job_status` (
`emp_status` varchar(45) NOT NULL DEFAULT ”,
`emp_num` int(11) DEFAULT NULL,
PRIMARY KEY (`emp_status`),
KEY `FK_emp_job_status` (`emp_num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

— ——————————————————–

—
— Table structure for table `invoice`
—

CREATE TABLE IF NOT EXISTS `invoice` (
`invoice_num` int(11) NOT NULL,
`inv_customer_num` int(11) DEFAULT NULL,
`inv_emp_num` int(11) DEFAULT NULL,
PRIMARY KEY (`invoice_num`),
KEY `FK_invoice` (`inv_emp_num`),
KEY `FK_invoice1` (`inv_customer_num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

— ——————————————————–

—
— Table structure for table `orders`
—

CREATE TABLE IF NOT EXISTS `orders` (
`invoice_num` int(11) DEFAULT NULL,
`product_num` int(11) DEFAULT NULL,
KEY `product_num` (`product_num`),
KEY `FK_orders` (`invoice_num`),
KEY `product_num_2` (`product_num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

— ——————————————————–

—
— Table structure for table `products`
—

CREATE TABLE IF NOT EXISTS `products` (
`product_num` int(11) NOT NULL,
`product_name` varchar(45) DEFAULT NULL,
`product_price` varchar(45) DEFAULT NULL,
PRIMARY KEY (`product_num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

—
— Constraints for dumped tables
—

—
— Constraints for table `employees`
—
ALTER TABLE `employees`
ADD CONSTRAINT `FK_employees` FOREIGN KEY (`emp_status`) REFERENCES `emp_job_status` (`emp_status`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `emp_job_status`
—
ALTER TABLE `emp_job_status`
ADD CONSTRAINT `FK_emp_job_status` FOREIGN KEY (`emp_num`) REFERENCES `employees` (`emp_num`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `invoice`
—
ALTER TABLE `invoice`
ADD CONSTRAINT `FK_invoice1` FOREIGN KEY (`inv_customer_num`) REFERENCES `customer` (`customer_num`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `FK_invoice` FOREIGN KEY (`inv_emp_num`) REFERENCES `employees` (`emp_num`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `orders`
—
ALTER TABLE `orders`
ADD CONSTRAINT `FK_order1` FOREIGN KEY (`product_num`) REFERENCES `products` (`product_num`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `FK_order` FOREIGN KEY (`product_num`) REFERENCES `products` (`product_num`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `FK_orders` FOREIGN KEY (`invoice_num`) REFERENCES `invoice` (`invoice_num`) ON DELETE CASCADE ON UPDATE CASCADE;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Database from Module 3
The database before normalization is as follows.

Figure 1 Database before Normalization
Tables
The six tables forming the database have the following structure:
•    Customer (num, name, address)
•    Employee (num, name, city, state, emp_status*, emp_salary, emp_hiredate)
•    Product (num, name, price)
•    Invoice (num, customer_num*, emp_num*)
•    Order (invoice_num*, product_num*)
•    Emp_Job_Status (emp_status, emp_num*)

1NF
Some of the database tables (Customer, Product, Invoice) are in their 1NF as the data in each field is atomic and has unique name, there is no data redundancy and each

table has a unique primary key.
Following changes are required to bring the remaining database tables to their 1NF.
•    Order – the table has no primary key defined in the database. By making the invoice_num*, product_num* a composite primary key, the table would confirm 1NF

i.e. Order (invoice_num*, product_num*)

The required SQL statements would be as follows:

DROP TABLE orders;

CREATE TABLE IF NOT EXISTS `orders` (
`invoice_num` int( 11 ) DEFAULT NULL ,
`product_num` int( 11 ) DEFAULT NULL ,
PRIMARY KEY (`invoice_num`, `product_num`),
FOREIGN KEY (`product_num`) REFERENCES `products` (`product_num`) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (`invoice_num`) REFERENCES `invoice` (`invoice_num`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = latin1;

•    Emp_Job_Status – the table has redundant information. The same content is already contained in the Employee table. By removing the Emp_Job_Status table and

making the emp_status a simple attribute of the Employee table, 1NF of the database can be ensured i.e.  Employee (num, name, city, state, emp_status, emp_salary,

emp_hiredate).

The required SQL to make these changes would be:

ALTER TABLE employees DROP FOREIGN KEY FK_employees;

DROP TABLE emp_job_status;
After 1NF, the database table structure is as follows:
•    Customer (num, name, address)
•    Employee (num, name, city, state, emp_status, emp_salary, emp_hiredate)
•    Product (num, name, price)
•    Invoice (num, customer_num*, emp_num*)
•    Order (invoice_num*, product_num*)

2NF
There is only one composite primary key in the database i.e. in the Order table. And as there are no non-key attributes in the Order table, the table is in 2NF.
Thus, since in all the database tables, the non-key attributes are dependent on the complete primary key, the database is in its 2NF.
3NF
In the database tables, Customer, Product, Invoice and Order, there are no transitive dependency between the non-key attributes, so these four tables are in their 3NF.
The Employee table does not exist in its 3NF as the state attribute is dependent on the city attribute. To bring the table in its 3NF, the following changes need to be

made:
•    Create a new table States, with city as the primary key i.e. States (city, state).
•    Remove the state attribute from the Employee table, and set city as the foreign key i.e.  Employee (num, name, city, state, emp_status, emp_salary,

emp_hiredate)

The SQL required to make the changes would be:

CREATE TABLE IF NOT EXISTS `states` (
`city` varchar( 45 ) NOT NULL ,
`state` varchar( 45 ) NOT NULL ,
PRIMARY KEY ( `city` )
) ENGINE = InnoDB DEFAULT CHARSET = latin1;
ALTER TABLE employees DROP state;
ALTER TABLE employees ADD CONSTRAINT fk_city FOREIGN KEY ( city ) REFERENCES states( city ) ;
After 3NF, the database table structure is as follows:
•    Customer (num, name, address)
•    States (city, state)
•    Employee (num, name, city, state, emp_status, emp_salary, emp_hiredate)
•    Product (num, name, price)
•    Invoice (num, customer_num*, emp_num*)
•    Order (invoice_num*, product_num*)

Figure 2 Database after Normalization

PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT 🙂