Fortis Electronics are a big electronics retail chain Franchise in the US. However, they have an old legacy data
storage system that maintains all data in file format. Front end applications update excel spreadsheets and these are
then collected to assemble the entire business data. This causes a lot of problems for the Franchise. The Franchise
has hired your team to design and implement a database system that will let them migrate from Excel.
Data for the Stores is in the spreadsheet "Fortis Electronics Store Data Dump.XLSX".
Statement of Work between your group and the company: 

A. Design and create an EER Model and a 3NF Database in MySQL.
Fortis Electronics Business Rules:
1. Org Structure - Fortis Electronics sales are organized by Sales Areas. There are four Sales Areas. Each
Sales Area consists of two states. Each state has several stores.

2. For security purposes, the database for each Sales Area is allowed to have the sales and customer
data of that Sales Area ONLY.
3. Each Fortis Electronics store stocks and sells all products offered by the company.
4. Fortis Electronics attempts to keep its Products classified via Product Hierarchy into Product Groups
5. The company policy requires to have Create Date and Time stamp fields on all tables for audit purposes.
6. For performance, table fields should use optimum metadata types and lengths, and not use generic
character metadata type unless needed.
7. To reduce the risk of data corruption, database technology needs to be used where applicable instead of
Excel. For example, database foreign key referential integrity should be used instead of Excel VLOOKUP
where possible.

 

*Objective* Logical Design:
1. Define EER model in 3NF form using MySQL (determine metadata based on initial analysis -
can change later during Physical Design)
2. Determine load sequence (order in which tables will be loaded based on Foreign-Key
relationships)

 

The attributes that should be included in EER model are shown in following two images

  

A 1 Order ID Product No 2 141234 #### 3 141235 4 141236 ###### 5 141237 6 141238 7 141239 8 141240 9 141241 10 141242 11 141243 12 141244 13 141245 14 141246 15 141247 16 141248 17 141249 18 141250 19 141251 20 141252 ####### 141253 21 22 141254 ##### 23 141255 24 25 26 27 28 29 30 31 141256 ##### 141257 141258 ####### 141259 32 33 141260 ####### 141261 141262 ####### 141263 141264 ###### 141265 34 141266 ##### 35 141267 36 141268 37 141269 38 141270 39 141271 B C Product Description Bose SoundSport Headphones Google Phone Bose SoundSport Headphones AAA Batteries (4-pack) AA Batteries (4-pack) AAA Batteries (4-pack) Wired Headphones AA Batteries (4-pack) AAA Batteries (4-pack) Bose SoundSport Headphones 27in 4K Gaming Monitor 27 in FHD Monitor Macbook Pro Laptop Google Phone 34in Ultrawide Monitor 34in Ultrawide Monitor Lightning Charging Cable LG Washing Machine ThinkPad Laptop AAA Batteries (4-pack) Wired Headphones AA Batteries (4-pack) USB-C Charging Cable USB-C Charging Cable USB-C Charging Cable AAA Batteries (4-pack) USB-C Charging Cable 34in Ultrawide Monitor AAA Batteries (4-pack) Bose SoundSport Headphones USB-C Charging Cable Wired Headphones AA Batteries (4-pack) AAA Batteries (4-pack) USB-C Charging Cable Wired Headphones ThinkPad Laptop Wired Headphones D Order Qty E Price Each $ 99.99 $ 600.00 F Line Total $ $ 99.99 $ 2.99 $ 3.84 $ 2.99 $ 11.99 $ 3.84 $ 2.99 $ 99.99 $ $ 389.99 $ 1 1 1 $ 1 $ 1 $ 1 $ 1 $ 1 $ 1 $ 1 $ 1 1 $ 149.99 $ 1 ######### $ 1 $ 600.00 $ 1 $ 379.99 $ 1 $ 379.99 $ 1 $ 14.95 $ 1 $ 600.00 $ 1 $999.99 $ 1 $ 2.99 $ 1 $ 11.99 3.84 $ $ 11.95 $ 1 $ 1 $ 1 $ 1 $ 11.95 $ 11.95 $ 2.99 $ 2 $ 1 $ 11.95 $ 1 $ 379.99 $ 2 $ 2.99 $ 1 $ 99.99 $ 1 $ 11.95 $ 1 $ 11.99 $ 1 $ 3.84 $ 1 $ 2.99 $ 1 $ 11.95 $ 1 $ 11.99 $ 1 $999.99 $ 1 $ 11.99 $ 99.99 600.00 99.99 2.99 3.84 2.99 11.99 3.84 2.99 99.99 389.99 149.99 1,700.00 600.00 379.99 379.99 14.95 600.00 999.99 2.99 11.99 3.84 11.95 11.95 11.95 5.98 11.95 379.99 5.98 99.99 11.95 11.99 3.84 2.99 11.95 11.99 999.99 11.99 Order Date G 1/22/19 1/28/19 1/17/19 1/5/19 1/25/19 1/29/19 1/26/19 1/5/19 1/1/19 1/22/19 1/7/19 1/31/19 1/9/19 1/25/19 1/3/19 1/5/19 1/10/19 1/24/19 1/30/19 1/17/19 1/8/19 1/9/19 1/29/19 1/12/19 1/19/19 1/20/19 1/1/19 1/9/19 1/16/19 1/11/19 1/3/19 1/1/19 1/2/19 1/9/19 1/14/19 1/3/19 1/27/19 1/30/19 H Customer Phone (799) 709-4858 (899) 618-2555 (545) 038-2808 (915) 763-8448 (547) 163-0427 (789) 867-5562 (940) 530-5717 (731) 384-8170 (936) 127-1170 (821) 737-6381 (823) 266-1885 (733) 843-5782 (946) 895-6632 (789) 327-3231 (710) 704-8882 (726) 123-3621 (999) 370-6418 (926) 375-8860 (711) 397-5073 (976) 270-0481 (985) 783-9203 (635) 970-5620 (660) 642-7629 (922) 081-8011 (827) 830-2440 (992) 456-3640 (936) 389-0878 (872) 428-5439 (612) 320-7160 (655) 428-4370 (683) 770-6489 (951) 186-4542 (799) 404-5214 (617) 665-4155 (848) 358-5663 (545) 365-2925 (868) 516-9969 (699) 428-5777 I Customer Credit Card No 1234580036122530 1234589864367220 1235000442987200 1235285534370390 1235659409304520 1235970925223730 1236189472835080 1236233016237410 1236374425289960 1236905798642990 1237217323537330 1237556704933530 1237583617573490 1237597401618970 1238146191284570 1238413461623440 1238414122027410 1238826947255030 1239155705959930 1239356764984340 1239449462676370 1240140988555700 1240379044943540 1240422915986950 1240709260398140 1241250604523080 1241293686033860 1241498377644500 1241668832642780 1242112621456980 1242434119650500 1243394421188360 1243814998439820 1243968895039460 1244235799269890 1245767815291740 1245890818780690 1245953447945970 J Store No Store Address 74769 495 Forest St, Boston, MA 02215 81053 662 Cherry St, New York City, NY 10001 88227 853 Center St, Portland, ME 04101 91357 938 8th St, Portland, ME 04101 91147 932 Spruce St, Seattle, WA 98101 72460 431 Wilson St, Seattle, WA 98101 76854 55 Pine St, Atlanta, GA 30301 67473 3 Maple St, Los Angeles, CA 90001 86149 798 Main St, Boston, MA 02215 93311 990 Main St, Austin, TX 73301 73040 449 4th St, Portland, OR 97035 70386 378 Hickory St, Seattle, WA 98101 79246 613 9th St, Atlanta, GA 30301 77431 566 Lakeview St, Los Angeles, CA 90001 64102 209 Cherry St, Portland, ME 04101 90225 908 5th St, Los Angeles, CA 90001 87573 836 Main St, Portland, OR 97035 79422 618 Lincoln St, Portland, OR 97035 62454 164 Lake St, Dallas, TX 75001 92527 97 7th St, Atlanta, GA 30301 69960 366 Sunset St, Atlanta, GA 30301 88850 87 4th St, Atlanta, GA 30301 87105 822 Park St, New York City, NY 10001 89980 900 Meadow St, New York City, NY 10001 69572 355 Willow St, Seattle, WA 98101 68847 337 12th St, Seattle, WA 98101 62782 174 Maple St, Los Angeles, CA 90001 77359 564 1st St, Boston, MA 02215 64874 228 Meadow St, Portland, ME 04101 81481 674 Jackson St, Portland, ME 04101 85459 780 13th St, Seattle, WA 98101 86328 801 Wilson St, Portland, ME 04101 89664 893 Lincoln St, Boston, MA 02215 79033 608 Hickory St, Seattle, WA 98101 85962 792 River St, San Francisco, CA 94016 89488 888 Ridge St, Dallas, TX 75001 71136 398 Washington St, New York City, NY 10001 67466 3 Church St, Los Angeles, CA 90001 K L Store State Massachusetts New York Maine Maine Wyoming Wyoming Georgia California Massachusetts Texas Oregon Wyoming Georgia California Maine California Oregon Oregon Texas Georgia Georgia Georgia New York New York Wyoming Wyoming California Massachusetts Maine Maine Wyoming Maine Massachusetts Wyoming California Texas New York California M State VP of Sales Bessie Maldonado Eleni Valentine Astrid Copeland Astrid Copeland Leyla Wilkerson Leyla Wilkerson Zara Mckenzie Irene Pace Bessie Maldonado Ricardo Reeves Bailey Guerrero Leyla Wilkerson Zara Mckenzie Irene Pace Astrid Copeland Irene Pace Bailey Guerrero Bailey Guerrero Ricardo Reeves Zara Mckenzie Zara Mckenzie Zara Mckenzie Eleni Valentine Eleni Valentine Leyla Wilkerson Leyla Wilkerson Irene Pace Bessie Maldonado Astrid Copeland Astrid Copeland Leyla Wilkerson Astrid Copeland Bessie Maldonado Leyla Wilkerson Irene Pace Ricardo Reeves Eleni Valentine Irene Pace A 1 Order ID Product No 2 141234 #### 3 141235 4 141236 ###### 5 141237 6 141238 7 141239 8 141240 9 141241 10 141242 11 141243 12 141244 13 141245 14 141246 15 141247 16 141248 17 141249 18 141250 19 141251 20 141252 ####### 141253 21 22 141254 ##### 23 141255 24 25 26 27 28 29 30 31 141256 ##### 141257 141258 ####### 141259 141260 ####### 141261 141262 ####### 141263 141264 ###### 141265 34 141266 ##### 35 141267 36 141268 37 141269 38 141270 39 141271 32 33 B C Product Description Bose SoundSport Headphones Google Phone Bose SoundSport Headphones AAA Batteries (4-pack) AA Batteries (4-pack) AAA Batteries (4-pack) Wired Headphones AA Batteries (4-pack) AAA Batteries (4-pack) Bose SoundSport Headphones 27in 4K Gaming Monitor 27in FHD Monitor Macbook Pro Laptop Google Phone 34in Ultrawide Monitor 34in Ultrawide Monitor Lightning Charging Cable LG Washing Machine ThinkPad Laptop AAA Batteries (4-pack) Wired Headphones AA Batteries (4-pack) USB-C Charging Cable USB-C Charging Cable USB-C Charging Cable AAA Batteries (4-pack) USB-C Charging Cable 34in Ultrawide Monitor AAA Batteries (4-pack) Bose SoundSport Headphones USB-C Charging Cable Wired Headphones AA Batteries (4-pack) AAA Batteries (4-pack) USB-C Charging Cable Wired Headphones ThinkPad Laptop Wired Headphones D Order Qty E Price Each $ 99.99 $ 600.00 F Line Total $ $ 99.99 $ 2.99 $ 3.84 $ 2.99 $ 11.99 $ 3.84 $ 2.99 $ 99.99 $ $ 389.99 $ 1 1 1 $ 1 $ 1 $ 1 $ 1 $ 1 $ 1 $ 1 $ 1 1 $ 149.99 $ 1 ######### $ 1 $ 600.00 $ 1 $ 379.99 $ 1 $ 379.99 $ 1 $ 14.95 $ 1 $ 600.00 $ 1 $999.99 $ 1 $ 2.99 $ 1 $ 11.99 3.84 $ $ 11.95 $ 1 $ 1 $ 1 $ 1 $ 11.95 $ 11.95 $ 2.99 $ 2 $ 1 $ 11.95 $ 1 $ 379.99 $ 2 $ 2.99 $ 1 $ 99.99 $ 1 $ 11.95 $ 1 $ 11.99 $ 1 $ 3.84 $ 1 $ 2.99 $ 1 $ 11.95 $ 1 $ 11.99 $ 1 $999.99 $ 1 $ 11.99 $ 99.99 600.00 99.99 2.99 3.84 2.99 11.99 3.84 2.99 99.99 389.99 149.99 1,700.00 600.00 379.99 379.99 14.95 600.00 999.99 2.99 11.99 3.84 11.95 11.95 11.95 5.98 11.95 379.99 5.98 99.99 11.95 11.99 3.84 2.99 11.95 11.99 999.99 11.99 Order Date G 1/22/19 1/28/19 1/17/19 1/5/19 1/25/19 1/29/19 1/26/19 1/5/19 1/1/19 1/22/19 1/7/19 1/31/19 1/9/19 1/25/19 1/3/19 1/5/19 1/10/19 1/24/19 1/30/19 1/17/19 1/8/19 1/9/19 1/29/19 1/12/19 1/19/19 1/20/19 1/1/19 1/9/19 1/16/19 1/11/19 1/3/19 1/1/19 1/2/19 1/9/19 1/14/19 1/3/19 1/27/19 1/30/19 H Customer Phone (799) 709-4858 (899) 618-2555 (545) 038-2808 (915) 763-8448 (547) 163-0427 (789) 867-5562 (940) 530-5717 (731) 384-8170 (936) 127-1170 (821) 737-6381 (823) 266-1885 (733) 843-5782 (946) 895-6632 (789) 327-3231 (710) 704-8882 (726) 123-3621 (999) 370-6418 (926) 375-8860 (711) 397-5073 (976) 270-0481 (985) 783-9203 (635) 970-5620 (660) 642-7629 (922) 081-8011 (827) 830-2440 (992) 456-3640 (936) 389-0878 (872) 428-5439 (612) 320-7160 (655) 428-4370 (683) 770-6489 (951) 186-4542 (799) 404-5214 (617) 665-4155 (848) 358-5663 (545) 365-2925 (868) 516-9969 (699) 428-5777 I Customer Credit Card No 1234580036122530 1234589864367220 1235000442987200 1235285534370390 1235659409304520 1235970925223730 1236189472835080 1236233016237410 1236374425289960 1236905798642990 1237217323537330 1237556704933530 1237583617573490 1237597401618970 1238146191284570 1238413461623440 1238414122027410 1238826947255030 1239155705959930 1239356764984340 1239449462676370 1240140988555700 1240379044943540 1240422915986950 1240709260398140 1241250604523080 1241293686033860 1241498377644500 1241668832642780 1242112621456980 1242434119650500 1243394421188360 1243814998439820 1243968895039460 1244235799269890 1245767815291740 1245890818780690 1245953447945970 J Store No Store Address 74769 495 Forest St, Boston, MA 02215 81053 662 Cherry St, New York City, NY 10001 88227 853 Center St, Portland, ME 04101 91357 938 8th St, Portland, ME 04101 91147 932 Spruce St, Seattle, WA 98101 72460 431 Wilson St, Seattle, WA 98101 76854 55 Pine St, Atlanta, GA 30301 67473 3 Maple St, Los Angeles, CA 90001 86149 798 Main St, Boston, MA 02215 93311 990 Main St, Austin, TX 73301 73040 449 4th St, Portland, OR 97035 70386 378 Hickory St, Seattle, WA 98101 79246 613 9th St, Atlanta, GA 30301 77431 566 Lakeview St, Los Angeles, CA 90001 64102 209 Cherry St, Portland, ME 04101 90225 908 5th St, Los Angeles, CA 90001 87573 836 Main St, Portland, OR 97035 79422 618 Lincoln St, Portland, OR 97035 62454 164 Lake St, Dallas, TX 75001 92527 97 7th St, Atlanta, GA 30301 69960 366 Sunset St, Atlanta, GA 30301 88850 87 4th St, Atlanta, GA 30301 87105 822 Park St, New York City, NY 10001 89980 900 Meadow St, New York City, NY 10001 69572 355 Willow St, Seattle, WA 98101 68847 337 12th St, Seattle, WA 98101 62782 174 Maple St, Los Angeles, CA 90001 77359 564 1st St, Boston, MA 02215 64874 228 Meadow St, Portland, ME 04101 81481 674 Jackson St, Portland, ME 04101 85459 780 13th St, Seattle, WA 98101 86328 801 Wilson St, Portland, ME 04101 89664 893 Lincoln St, Boston, MA 02215 79033 608 Hickory St, Seattle, WA 98101 85962 792 River St, San Francisco, CA 94016 89488 888 Ridge St, Dallas, TX 75001 71136 398 Washington St, New York City, NY 10001 67466 3 Church St, Los Angeles, CA 90001 K L Store State Massachusetts New York Maine Maine Wyoming Wyoming Georgia California Massachusetts Texas Oregon Wyoming Georgia California Maine California Oregon Oregon Texas Georgia Georgia Georgia New York New York Wyoming Wyoming California Massachusetts Maine Maine Wyoming Maine Massachusetts Wyoming California Texas New York California M State VP of Sales Bessie Maldonado Eleni Valentine Astrid Copeland Astrid Copeland Leyla Wilkerson Leyla Wilkerson Zara Mckenzie Irene Pace Bessie Maldonado Ricardo Reeves Bailey Guerrero Leyla Wilkerson Zara Mckenzie Irene Pace Astrid Copeland Irene Pace Bailey Guerrero Bailey Guerrero Ricardo Reeves Zara Mckenzie Zara Mckenzie Zara Mckenzie Eleni Valentine Eleni Valentine Leyla Wilkerson Leyla Wilkerson Irene Pace Bessie Maldonado Astrid Copeland Astrid Copeland Leyla Wilkerson Astrid Copeland Bessie Maldonado Leyla Wilkerson Irene Pace Ricardo Reeves Eleni Valentine Irene Pace