Introduction
A model is any representation of something else. It may be physical e.g. a toy car representing a real car or it can be conceptual, only represented on paper to bring a good description of the operation of the real object. “A data model is a way finding tool for both business and IT professionals, which uses a set of symbols and text to precisely explain a subset of real information to improve communication within the organization and thereby lead to a more flexible and stable application environment.” (Singh 2009).In software engineering a data model is an abstract representation of facts and how those facts can be accessed.
By examining a data model a certain structure comes out, depending on the type of data being represented and retrieved; all this leads to structured data. Data modeling structure is used in design of database models and information systems. We have different types of data model structures in relation to their contribution in data base development; these are relational model, hierarchical model and the network model (Inmon 206).
Approaches to Database Design
There are no specific universally agreed approaches to database design. Database designs being a modeling technique, different designers have taken their on approach to develop functioning databases. Some designers employ the integrated approach. This has a series of phases. The input and output of this phase are explained and any problems that may arise being pointed out. These phases are: requirement analysis, View modeling, and View integration, View restructuring and finally schema analysis and mapping Schema any analysis and mapping (Connolly 7).
Another approach is staged database design. This being a top down approach it begins by outlining the general requirement of an organization. As this task continues the organization problems are checked in detail. Steps involved in this approach are: analysis of current user environment being keen on its output. This is flowed by logical data model being developed. At this stage the designer looks into issues like data volumes, transactions volumes, etc. the next stage is choosing a DBMS. It is during this stage that the designer makes a choice of database management system. The next stage is the mapping of the logical model to the DBMS.
After this step a physical model is developed, then follows the evaluating of the physical model followed by performance tuning and then finally implementing the physical model. The third approach is semantic modeling. We also have the top down approach and the design approach. In the design approach implementing the distances involves the 2nd normal form and then 3rd normal form (Nagabhushana 396).
The Underground Pizza Data Model
This data model consists of 9 entities Customer, Store, Order, Address, StaffRotation, Product, Role, OrderLine and salesPerson. Underground Pizza Company will implement a relational database developed by Oracle 10g. The data model meets user requirements by ensuring all constraints are taken care of. This is achieved by use of well identified attributes and relationships.
Entities and Attributes for Underground Pizza Company
The following table shows a list of entities and attributes and their relationships.
Constraints are imposed on Underground Pizza Company
Each store should have a maximum of 7 staff. The staff roles to be included in the system are managers, pizza makers, support staff. Each store should have two pizza makers. The purpose of support staff is strictly to take orders. Delivery staff per store should be 3 and the method should be either pick up or delivered to customer. All Staff are allowed to serve in a store for duration of three months with exception of the manager. The menu items only consist of pizzas, drinks and side orders (garlic bread, combo meals). For any customer to get a discount of 20% on any subsequent purchase after first purchase the customer’s details must exist in the database and the subsequent purchase should have been made in less than 15 days after first purchase.
Assumptions
The system will be able to calculate the duration a staff is assigned to a particular store and notify the system administrator when staff rotation is due. This is achieved by recording the start date into a field, end date into a field and introducing another field used to determine whether the rotation is due. Another assumption is that the system will be programmed to determine whether a staff role is a manager and hence exclude the manager from the rotation policy. The system will also be able to ensure that each store has only two pizza makers, three delivery staff and the rest be support staff and a manager.
Another assumption is that the system will determine an order period starting from the first time a customer makes an order, and if by any chance the customer makes another order before the expiry of 15 days an automatic discount of 20% will be applied on the order after which the period will be considered over. This will come into effect for any customer who exists in the database.
Many-to-Many
In the UPC data model we have Customer, Order, OrderLine and Product entities which have direct relationships. For a many-to-many relationship to exist there is need to have a third entity to associate the two entities. A one-to-many relationship should exist between the two entities (order entity and the product entity) and the association table. An order contains 1 or more products. The minimum product to exist on an order is one because an order cannot exist with zero products. Any identified product can be included in zero or many orders. This is due to the fact that we can have an order without a particular product. Bearing this in mind we notice a many-to-many relationship between orders and products. The following diagram brings out the many-to-many relationship clearly.
Each and every organization relies on useful, relevant, adaptable, reliable and economic information to make sound judgment that can keep it afloat. Due to the fact that relevant information can only be obtained from data that is coherent and has integrity, we cannot wish away the importance of implementing relevant systems that can capture this data and represent it in the most appropriate way. Coming up with a data model for UPC requires a lot of experience and knowledge in the field of data modeling. This will help gather the business needs making it possible to come up with a clear list of entities, attributes and their relationships.
Data modeling will help UPC be able to capture all the requirements of the organization aiding to promote seamless operations of all the entities. If data modeling is done well it will ease automation purposes as well as provide a flexible structural base to accommodate any dynamic changes in the organization data requirements and interactions (Singh 139).
Works Cited
Connolly, Harry. “Database Systems: A practical Approach to Design Implementation.” American Libraries 31.2 (2005): 6-8. Print.
Inmon, Wendy. Building the Data Warehouse. 3rd ed. Ranken Publishers, 2006. Print.
Nagabhushana, Lynn. “Relational Approach to Database Design.” Research and College Libraries News 63.5 (2003): 394-397. Print.
Singh, Luis. “Data Warehousing” Public Libraries 42.2 (2004): 138-141. Print.