Technology storeData Base Design
Description (1)• A database for a large-scale
technology Store.
• The store has employees - either assistant or in the maintenance team
• Two separate entities with many to many relationship, a manager is not an employee. Every employee is required to be managed by a manager.
• Each order contains items
Description (2)• Items are distributed into
subclasses such as TV, AUDIO, ACCESSORIES, IT, KITCHEN, COMMUNICATIONS with different types of technology
• Items are delivered to the store by different suppliers
• Each item belongs to a brand
• The characteristics of the customers are also recorded
• Attributes of the warehouses are listed in the data base
ER-diagram
Relational
Model
Employee
e_id e_name e_phone e_address
1919 John Smith 7206161812 21 Park Lane, Denver, CO 80506
2121 Ashley Stewart 7201888787 19 Cleveland Place, Boston, MA 10506
1313 Drake McQuivey 3031883456 17 Lincoln Str, Chicago, IL 12345
1415 Sasha Fierce 7201234567 34 Washington Str, New York NY 23233
2123 Lucy McCartney 3033554545 21 Santa Monica, Paris CO 12323
3454 Brandon Kyler 9010200324 32 Eagle Drive, Broadway 211213
1232 Kris Kennedy 1000000000 345 Mantagnismesto, California 21213
6666 Drew Donaldson 2000000000 345 Mantagnismesto, California 21213
7777 Mason Lockwood 3000000000 123 Ocean City, MD 2342
8888 Jean Grey 4000000000 344 Estes Park, CO 23231
9999 Scott Summers 5000000000 989 Michigan Str, Mystic Falls, VA 2324
1111 Kanye Roberts 6000000000 717 Mintwood, Dark Streets, MA 23234
maintenance
e_id num_of_repairs
1313 40
2123 60
6666 88
assistant
e_id a_type
8888 A
9999 B
1111 A
Manager
MG_ID MG_name MG_phone salary
1919 John Smith 7206161812 hourly
7777 Mason Lockwood 3000000000 monthly
3454 Brandon Kyler 9010200324 monthly
Manages
MG_ID e_id
1919 1232
7777 8888
3454 1111
Example data (1)
Example data (2)
Customer
c_id c_name c_email
9000 Harry Jones [email protected]
8000 East West [email protected]
7000 Jason Peterson [email protected]
Itemi_id price qty_left
1 $50 822 $19 323 $20 45
ITi_id i_type
3 computer2 laptop1 ipad
TVi_id tv_size
1 122 153 17
Kitcheni_id k_category
3 robot3 stove2 fridge
Communicationi_id com_type
2 smartphone3 skypephone3 norphone
Accessoriesi_id a_type
2 headphones2 cases1 chargers
Audioi_id au_type
3 cd2 dvd1 blue ray
Ordero_num o_date payment_method e_id c_id
303 15-10-15 cash 8888 9000404 16-10-15 check 9999 8000505 14-10-15 online 1111 7000
Containso_num i_id
303 3404 2505 1
Suppliers_id s_name size s_location55 John Johnson 2324325656 342 Churchill Str, Mount Black, LA66 Kate Kerry 5656474323 234 Green Arrow, Saint Louis, MI 4567777 Fred Mattew 3542342245 121 Red Dots, Salisbury, MD 23234
Providess_id i_id quantity date shipment_size66 3 199 23-10-15 big55 1 345 10-10-15 average77 2 133 21-10-15 normal
Brandb_name popularity market_share country_of_origin i_idApple 7207171234 USA 2
Samsung 3232321321 Korea 3Sony 3121321445 Japan 1
Warehousew_num w_name w_size w_location
123 Old medium USA124 New maximum Belgium125 Archaic minimum Hong Kong
Example data (3)
Stored inw_num i_id
123 3124 1125 2
Normalization All the tables provided are
normalized to be in 3nf
• Babev, Nikolay, Leader• Amgalantuul, Ulsbold, Designer• Pavlova, Ivelina,Designer• Robo, Xhoni, Developer• Zahariev, Nikolay,Developer
Team members
Thank you !!!