Test

The local department store recently expanded by opening five stores within local region. As the database administrator, I need to propose a new plan to re-design the database to meet it business operation needs. The new design must consider the fact that sales could come from different stores. Thus, the database should have ability to store sale records from different store locations in order to help financial department to run better reports and help inventory department to keep track of what it has in hand. In addition, to increase sale and to keep in touch with customers, the department store would like to have customer information such as name, address and email to store in the database so that it could send out coupons, flyers and deals to make its customers come back and shop more.

Base on the potential business rules, I propose a database diagram below as the database re-design solution.

According to the diagram above, there is a Stores Table which stores multiple Store information such as Store Number, location and contract info. To create relationship between multiple Departments and Stores, a new table named StoreDepartment is created. The relationship between Stores and StoreDepartment Table; Departments and StoreDepartment Table are M: M. This type of relationship ensure the same department could be assigned under different stores. For example Department Electronic which has DepartmentID is 1 can be under store 1 which has StoreID is 1 and store 2 which has StoreID is 2 and so on. The database structure is below:

Stores Table

Department Table

StoreDepartment Table

Stores - Departments report result

SQL Query

Select s.StoreNumber,s.Address ,s.City ,s.State ,s.Zip ,s.Phone , d.DepartmentName from StoreDepartment sd inner join Stores s on sd.StoreID = s.StoreID inner join Departments d on sd.DepartmentID = d.DepartmentID order by sd.StoreID

Result

By using INNER JOIN on Stores and Departments Table from StoreDepartment Table, the...