Cis515
CIS 515-Strategic Planning Of Database Systems
Potential sales and department store transactions stored within the database.
Following the successful expansion of the retail environment to several new stores there is a clear need for an enterprise wide relational database especially with the envisaged increase in sales as a result of current marketing activity. There are several important steps to consider when designing a database, as a well-designed database should be deployed and not only support the accuracy and integrity of business information but also avoid redundant data and assist with has enterprise level reporting tasked. If we analyze the transactions carried out in each store we can produce a list of the principal entities which are involved with a store based transaction:
Customers – those who enter the store and buy the products.
Vendors – the companies who supply stock to the stores.
Products – the products themselves, which are available in store for purchase.
Store – Location of the actual premises where the transaction takes place.
Employees – staff that work in each store and deal with the customers.
Sales – physical process of selling a product or product(s).
These would make up the core entities of the database and each entity would have various attributes with further relevant information which can be displayed in a hierarchical nature.
Customers
Customer #
Name
Address
Phone Number
Vendors
Company ID
Product
Cost
Contact Name
Contact Address
Contact Number
Products
Price
Type
Manufacturer
Store
Name
Address
Employee
Employee ID #
Name
Address
Sales
Products
Date
Customer
Employee
Store
These can be seen as the key attributes for each entity and provides the information required from each transaction by capturing the essential essence of the business operations. At the simplest level the organization, a store maintains various products stocks, is staffed by various employees who service: customers – assisting them with enquiries but ultimately selling those products to these customers which is the main transaction we are concerned with when it comes to designing the database. Having worked out the required entities and attributes for the database the next step is to determine the business rules which will be used concerning the sales transactions and their storage in the database – these are similar to common business practices and how the organization wishes to manage and organize their data but need to be quantified so that the database can be designed accordingly.
Database solution and the potential business rules that could be used to house the sales transactions of the department store. Before business rules are defined the information recorded would simply be a list of facts and figures with no correlating relationships designed to assist in the reporting and visualization of the data to allow proper analysis going forward. In the proposed database design there should be several business rules:
Rule 1:
We have 6 stores that sell Products – this will translate into a field constraint in the database where a designated code is assigned to each store location. Only these assigned codes will be permitted as valid in this field.
Rule 2:
Each of our vendors must supply at least one product – even if there are discontinued or legacy products/vendors then this would still hold true and be represented by a relationship constraint within the database. This would be manifested whereby a single record in the Vendors table would need to be related to at least one record in the Products table.
Evaluate all relationships of each entity within your database solution using the Crow’s Foot notation. Include all data elements, entities, relationships (such as 1:M, 1:1, M:N), and cardinalities...
References: Carter, J. (n.d.). Database Design and Programming with Access, SQL, Visual Basic and ASP.
Churcher, C. (n.d.). Beginning Database Design .
Liu, L., & Ozsu, M. T. (n.d.). Encyclopedia of Database Systems. Springer.
O 'Brien, M. C., & Winter, J. (n.d.). Developing Stored Procedures for Microsoft SQL Server.
Pratt, P. J., & Adamski, J. J. (n.d.). Concepts of Database Management.
Rob, P., & Coronel, C. (n.d.). Database Systems: Design, Implementation, and Management.
Please join StudyMode to read the full document