MISY 5300

Assignment 2

Database Design

USE: MS Access

H⁺ Sport sells comfortable clothes to customers. H⁺ Sport is profitable and growing. Increased competition requires H⁺ Sport constantly to consider better ways to meet the needs of its customers. Keeping track of customers, products, orders, and employees is a critical task. Increasingly, customers want information services as well as products from the store. The vision of H⁺ Sport is to provide the highest quality customer service with the broadest range of products and services. The purpose of the proposed project is to prove that customer information services will improve customer satisfaction and lead to increased revenue and potentially increased market share. Also, the company wants to better manage their order processing system.

Specifically, the proposed system will provide information services such as review customer feedback, review customer life time sales, review the inventory of items carried in the store; view orders by month and states; view profit summary; and view top 5 customers.

In this exercise, you will design a database for H+ Sport by:

1. Creating the tables
2. Describing the attributes
3. Entering Data in the tables
4. Identifying the Primary Keys (PK) in each table.
5. Joining the PKs to the Foreign Keys (FK) (Table 3 shows the relationships)
6. Make sure to enforce referential integrity.

The tables and the descriptions are given below:

• Table 1: Entities and Attributes in the H⁺Sport System

Entity	Attributes	Data Type
CUSTOMER	Customer ID Customer Name Customer Phone Customer Address Zip Code Customer Email State Abbr	Number Short Text Short Text Short Text Short Text Short Text Short Text
FEEDBACK	Feedback ID Rating Public Feedback Text Customer ID Product ID	Number Number Yes/No Short Text Number Number
ORDER	Order ID Quantity Order Date Order Status Customer ID Product ID Employee ID	Number Number Date/Time Short Text Number Number Number
STATE	State Abbr State Name Region Sub Region	Short Text Short Text Short Text Short Text
PRODUCT	Product ID Color Price Item Sizes	Number Short Text Currency Short Text Short Text
PRODUCT DESCRIPTION	Item Description Who Wholesale Cost	Short Text Short Text Short Text Currency
SIZE	Sizes Size Full Text	Short Text Short Text
EMPLOYEE	Employee ID Employee Name Employee Email Department Position Benefits Employee Status Hire Date Salary Job Rating Photo	Number Short Text Short Text Short Text Short Text Yes/No Short Text Date/Time Currency Number OLE Object

Note:

• For attributes like Rating, Status with fixed input options, Priority (found in More Fields under Add & Delete section) can be used to create a drop-down function.

Table 2: Descriptions of data elements (Data Dictionary)

• Table 3: H⁺Sport ERD relationship lines

Source Entity	Relationship	Target Entity
CUSTOMER	One to many	FEEDBACK
CUSTOMER	One to many	ORDER
SIZE	One to many	PRODUCT
PRODUCT	One to many	FEEDBACK
PRODUCT	One to many	ORDER
EMPLOYEE	One to many	ORDER
STATE	One to many	CUSTOMER
PRODUCT DESCRIPTION	One to many	PRODUCT

The data tables:

CUSTOMER

Customer ID	Customer Name	Customer Phone	Customer Address	State Abbr	Zip Code	Customer Email
2000	Amber Reyes	(264) 821-1341	8208 Gentle Deer Gardens, Salt Lake [emailprotected]
2001	Bo clay	(885) 162-2679	9676 Pleasant Mountain Promenade, [emailprotected]
2002	Larissa Winters	(217) 343-7259	2015 Fallen Private, [emailprotected]
2003	Ciaran Bullock	(988) 774-8365	9309 Lost Deer Key, New [emailprotected]
2004	Rachel Caldwell	(136) 861-1447	7262 Cotton Pointe, Salt Lake [emailprotected]
2005	Rebecca Rasmussen	(437) 204-6983	3862 Merry Abbey, [emailprotected]
2006	Benjamin Foster	(383) 060-0284	683 Cotton Inlet, [emailprotected]
2007	Jennifer Michael	(234) 762-0777	3291 Indian Apple Ridge, Little [emailprotected]
2008	Zephania Welch	(996) 379-4496	2532 Dewy Falls, [emailprotected]
2009	Joseph Weber	(806) 372-5803	8385 Shady Zephyr Wood, Saint [emailprotected]

EMPLOYEE

Employee ID	Employee Name	Employee Email	Department	Position	Employee Status	Hire Date	Benefits	Salary	Job Rating	Photo
1001	Shawn [emailprotected]	Manufacturing	Team Member	Full Time	1/5/2001	Yes	$78,570.00	1	1
1002	Benjamin [emailprotected]	Quality Assurance	Team Member	Contract	1/10/2001	Yes	$86,100.00	4	2
1003	Sandi [emailprotected]	Manufacturing	Team Member	Half-Time	1/11/2001	Yes	$48,835.00	5	3
1004	Madelyn [emailprotected]	Quality Control	Team Member	Contract	1/30/2001	No	$45,030.00	3	4
1005	Greg [emailprotected]	Account Management	Team Member	Full Time	5/2/2001	No	$73,740.00	4	5

To Upload photos individually in MS Access. Make sure you have OLE object as data type. Copy the jpeg image, select the destination cell and paste it. The image will be inserted as 'Package'.

FEEDBACK

Feedback ID	Product ID	Customer ID	Rating	Public	Feedback Text
1	106	2008	4	Yes	Great value. Love the color.
2	101	2009	2	Yes	Fabric snags easily.
3	108	2000	5	No	Very durable.
4	103	2003	3	No	great product.
5	104	2005	5	Yes	Fantastic.

Order ID	Customer ID	Product ID	Quantity	Employee ID	Order Date	Order Status
1	2001	105	2	1004	1/1/2019	returned
2	2000	108	1	1002	1/2/2019	complete
3	2001	101	1	1005	1/2/2019	complete
4	2004	102	2	1001	1/8/2019	complete
5	2003	103	3	1003	1/12/2019	complete
6	2004	104	4	1003	1/19/2019	complete
7	2006	105	5	1001	1/22/2019	complete
8	2007	107	5	1002	2/1/2019	complete
9	2008	106	1	1002	2/5/2019	complete
10	2009	101	3	1005	2/9/2019	returned

ORDER

PRODUCT DESCRIPTION

Item	Description	Who	Wholesale Cost
Bamboo thermal ski coat	You'll be the most environmentally conscious skier on the slopes - and the most stylish - wearing our fitted bamboo thermal ski coat, made from organic bamboo with recycled plastic down filling.	Women	$45.89
Cross-back training tank	Our cross-back training tank is made from organic cotton with 10% Lycra for form and support, and a flattering feminine cut.	Women	$9.52
Polo shirt	Our pre-shrunk organic cotton polo shirt is perfect for weekend activities, lounging around the house, and casual days at the office. With its triple-stitched sleeves and waistband, our polo has maximum durability.	Men	$8.45
Skater graphic T-shirt	Hip at the skate park or around down, our pre-shrunk organic cotton graphic T-shirt has you covered.	Men	$12.99
Stretchy dance pants	Whether dancing the samba, mastering a yoga pose, or scaling the climbing wall, our stretchy dance pants, made from 80% organic cotton and 20% Lycra, are the most versatile and comfortable workout pants you'll ever have the pleasure of wearing.	Women	$22.19
V-neck sweater	This medium-weight sweater, made from organic knitted cotton and bamboo, is the perfect solution to a chilly night at the campground or a misty walk on the beach.	Men	$22.70
Women's V-neck T-shirt	Our pre-shrunk organic cotton t-shirt, with its slightly fitted waist and elegant V-neck is designed to flatter. You'll want one in every color!	Women	$8.19

PRODUCT

Product ID	Item	Sizes	Color	Price
100	Cross-back training tank	L	rose	$35.00
101	Bamboo thermal ski coat	M	mint	$128.00
102	Stretchy dance pants	S	evergreen	$85.00
103	Women's V-neck T-shirt	M	mint	$26.00
104	Women's V-neck T-shirt	XL	pink	$26.00
105	V-neck sweater	S	black	$65.00
106	V-neck sweater	M	gray	$65.00
107	Polo shirt	S	black	$38.00
108	Skater graphic T-shirt	S	burgundy	$45.00
109	Skater graphic T-shirt	XL	burgundy	$45.00

SIZE

Sizes	Size Full Text
L	Large
M	Medium
S	Small
XL	Extra Large

STATE

State Abbr	State Name	Region	Sub Region
AR	Arkansas	South	West South Central
KY	Kentucky	South	East South Central
MN	Minnesota	Midwest	West North Central
MO	Missouri	Midwest	West North Central
MT	Montana	West	Mountain
NY	New York	Northeast	Mid-Atlantic
OH	Ohio	Midwest	East North Central
PA	Pennsylvania	Northeast	Mid-Atlantic
UT	Utah	West	Mountain
VA	Virginia	South	South Atlantic

1. Shawn Neal 2. Benjamin Casey 3.Sandi Landry

4. Madelyn Nichols 5. Blank

Note: Right Click and Save as Picture.