Question

For this option, you will use the Sales and Purchased Items tables below. You are to develop CREATE TABLE statements for both tables and analyze the two tables for redundancies in the dataset. Finally, you should explain why these two tables are not candidates for a relational model.

Sales Table

LastName	FirstName	Phone	InvoiceDate	InvoiceItem	Price	Tax	Total
Shire	Robert	206-524-2422	12/14/2017	Antique Desk	3000.00	249.00	3249.00
Shire	Robert	206-524-2422	12/14/2017	Antique Desk Chair	500.00	41.50	541.50
Goodyear	Katherine	206-524-3544	12/15/2017	Dining Table Linens	1000.00	83.00	1083.00
Bancroft	Chris	426-635-9788	12/15/2017	Candles	50.00	4.16	54.16
Griffith	John	206-524-4656	12/23/2017	Candles	45.00	3.74	48.74
Shire	Robert	206-524-2422	1/5/2018	Desk Lamp	250.00	20.75	270.75
Tierney	Doris	425-635-8677	1/10/2018	Dining Table Linens	750.00	62.25	812.25
Anderson	Donna	360-538-3544	1/12/2018	Book Shelf	250.00	20.75	270.75
Goodyear	Katherine	206-524-3544	1/15/2018	Antique Chair	1250.00	103.75	1353.75
Goodyear	Katherine	206-524-3544	1/15/2018	Antique Chair	1750.00	145.25	1895.25
Tierney	Doris	425-635-8677	1/25/2018	Antique Candle Holders	350.00	29.05	379.05

Purchased Items Table

PurchaseItem	PurchasePrice	Purchase Date	Vendor	Phone
Antique Desk	1800.00	11/7/2017	European Specialties	206-325-7868
Antique Desk	1750.00	11/7/2017	European Specialties	206-325-7868
Antique Candle Holders	210.00	11/7/2017	European Specialties	206-325-7868
Antique Candle Holders	200.00	11/7/2017	European Specialties	206-325-7868
Dining Table Linens	600.00	11/14/2017	Linen and Things	206-325-6755
Candles	30.00	11/14/2017	Linen and Things	206-325-6755
Desk Lamp	150.00	11/14/2017	Lamps and Lighting	206-325-8977
Floor Lamp	300.00	11/14/2017	Lamps and Lighting	206-325-8977
Dining Table Linens	450.00	11/21/2017	Linen and Things	206-325-6755
Candles	27.00	11/21/2017	Linen and Things	206-325-6755
Book Shelf	150.00	11/21/2017	Harrison, Denise	425-746-4332
Antique Desk	1000.00	11/28/2017	Lee, Andrew	425-746-5433
Antique Desk Chair	300.00	11/28/2017	Lee, Andrew	425-746-5433
Antique Chair	750.00	11/28/2017	New York Brokerage	206-325-9088
Antique Chair	1050.00	11/28/2017	New York Brokerage	206-325-9088

Your deliverables for this assignment are:

1. CREATE TABLE statements for the Sales table.
2. CREATE TABLE statements for the Purchased Items table.
3. Identify the redundancies in each table
4. Explanation of why theses to data sets are not candidates for a relation data model
5. A brief discussion on the primary challenges, if any, you experienced in completing this assignment

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

This demonstration is using SQL Server 2014.Tables are created using SQL Server Management Studio (SSMS).

Database :

/*create database SalesPurchaseDB*/
create database SalesPurchaseDB;

/*use database to create table*/
use SalesPurchaseDB;

Tables :

1.Table Name :Sales

/*1.Table Name :Sales*/
create table Sales(
LastName varchar(20) not null,
FirstName varchar(20) not null,
Phone varchar(10) not null,
InvoiceDate date not null,
InvoiceItem varchar(20) not null,
Price decimal(8,2) not null,
Tax decimal(6,2) not null,
Total decimal(6,2) not null);

2.Table Name :Purchased

/*2.Table Name :Purchased */
create table Purchased (
PurchaseItem varchar(20) not null,
PurchasePrice decimal(6,2) not null,
PurchaseDate date not null,
Vendor varchar(20) not null,
Phone varchar(10) not null);

**********************************

Redundancies in Sales Table :

• Here in Sales table for each invoice item redundant data for firstname , last name , invoice date , phone number needs to be inserted.
• Also for invoice item Antique Chair last name , first name , phone , invoice date all is same but only price for this item is different.That needs to identify.

********************************

Redundancies in Purchased Table :

• For Purchased table for some of the purchased item like Antique Desk have same purchase date , vendor and phone but have two different prices.This leads to redundancy of data.
• Also for each purchase item vendor phone and vendor details is redundant this can be removed by normalizing table.

**********************************

Explanation of why theses to data sets are not candidates for a relation data model :

• In case of both these table there is no any particular column exists so make it as a primary key.
• Because both table contains redundant data.
• Also there is no any common column exists in the table to have a relation between these two table.

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.