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

  

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 K 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 L Store State Massachusetts New York Maine M State VP of Sales Bessie Maldonado Eleni Valentine Astrid Copeland Maine Astrid Copeland Wyoming Leyla Wilkerson Leyla Wilkerson Zara Mckenzie Irene Pace Wyoming Georgia California Massachusetts Texas Oregon Wyoming Georgia Bessie Maldonado Ricardo Reeves Bailey Guerrero Leyla Wilkerson Zara Mckenzie California Irene Pace Maine California Astrid Copeland Irene Pace Oregon Bailey Guerrero Oregon Bailey Guerrero Texas Ricardo Reeves Georgia Zara Mckenzie Georgia Zara Mckenzie Georgia Zara Mckenzie New York Eleni Valentine New York Eleni Valentine Wyoming Leyla Wilkerson Wyoming California Leyla Wilkerson Irene Pace Massachusetts Bessie Maldonado Astrid Copeland Astrid Copeland Leyla Wilkerson Maine Maine Wyoming Maine Massachusetts Wyoming California Texas New York California Astrid Copeland Bessie Maldonado Leyla Wilkerson Irene Pace Ricardo Reeves Eleni Valentine Irene Pace 1234567822NNAN2222222222222223333 mmmmm 3 A Order ID 141234 #### 141235 141236 141237 AAA Batteries (4-pack) 141238 AA Batteries (4-pack) 141239 141240 9 141241 10 141242 11 141243 141244 141245 141246 141247 141248 B Product No C Product Description Bose SoundSport Headphones Google Phone Bose SoundSport Headphones D E F G H Order Qty Price Each Line Total Order Date Customer Phone Customer Credit Card No 1 $ 99.99 $ 99.99 1/22/19 1 $ 600.00 $ 600.00 1/28/19 (799) 709-4858 (899) 618-2555 1 $ 99.99 $ 99.99 1/17/19 1 $ 2.99 $ 2.99 1/5/19 1 $ 3.84 $ 3.84 1/25/19 19 20 141252 141249 141250 141251 AAA Batteries (4-pack) Wired Headphones AA Batteries (4-pack) AAA Batteries (4-pack) 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 1 $ 2.99 $ 2.99 1/29/19 1 $ 11.99 $ 11.99 1/26/19 1 $ 3.84 $ 3.84 1/5/19 1 $ 2.99 $ 2.99 1/1/19 Bose SoundSport Headphones 1 $ 99.99 $ 99.99 1 $ 389.99 $ 389.99 1/22/19 1/7/19 1 $ 149.99 $ 149.99 1/31/19 1 ######### $ 1,700.00 1/9/19 1 $ 600.00 $ 600.00 1/25/19 1 $ 379.99 $ 379.99 1/3/19 1 $ 379.99 $ 379.99 1/5/19 1 $ 14.95 $ 14.95 1/10/19 1 $ 600.00 $ 600.00 1/24/19 1 $ 999.99 $ 999.99 1/30/19 141253 AAA Batteries (4-pack) 1 $ 2.99 $ 2.99 1/17/19 141254 Wired Headphones 1 $ 11.99 $ 11.99 1/8/19 141255 AA Batteries (4-pack) 1 $ 3.84 $ 3.84 1/9/19 141256 USB-C Charging Cable 1 $ 11.95 $ 11.95 1/29/19 141257 USB-C Charging Cable 1 $ 11.95 $ 11.95 1/12/19 141258 USB-C Charging Cable 1 $ 11.95 $ 11.95 1/19/19 141259 AAA Batteries (4-pack) 2 $ 2.99 $ 5.98 1/20/19 141260 29 141261 USB-C Charging Cable 34in Ultrawide Monitor 1 $ 11.95 $ 11.95 1/1/19 1 $ 379.99 $ 379.99 1/9/19 30 141262 AAA Batteries (4-pack) 2 $ 2.99 $ 5.98 1/16/19 141263 Bose SoundSport Headphones 1 $ 99.99 $ 99.99 1/11/19 141264 USB-C Charging Cable 1 $ 11.95 $ 11.95 1/3/19 141265 Wired Headphones 1 $ 11.99 $ 11.99 1/1/19 34 141266 AA Batteries (4-pack) 35 141267 AAA Batteries (4-pack) 1 $ 1 $ 3.84 $ 3.84 1/2/19 2.99 $ 2.99 1/9/19 36 141268 37 141269 38 39 141270 141271 Wired Headphones ThinkPad Laptop Wired Headphones USB-C Charging Cable 1 $ 11.95 $ 11.95 1/14/19 1 $ 11.99 $ 11.99 1/3/19 1 $ 999.99 $ 999.99 1/27/19 1 $ 11.99 $ 11.99 1/30/19 (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