The relevance of creating a data model for the XYZ Mobile Business inventory database can be attributed to the fact that it is often the first step in database design so that it can help create a conceptual model of how the data models will relate to each other. Thus, the modeling will enable progression from conceptual to logical model to a physical schema. This response, therefore, focuses on data modeling layout including an appropriate E/R diagram. The type of data modeling in the response involves entity relationship (ER) data modeling. The ER modeling is built on two concepts in which entities are identified as tables which hold specific information and relationships identified as associations between the entities (Watt, n.d).
The data modeling stage, in this case, is the conceptual model in which we get to understand the relationship between entities in the inventory database. Creating the model requires the identification of the entity and relationship concepts relative to the business data and flow of transactions. Thus, the main entities in the database inventory would be customer, company, and item. The customer entity represents the buyers of the mobile phones, the company entity refers to the seller/management of the business while the item entity refers to the mobile phones being sold. This stage of the database development only focuses on the abstract view of the inventory database.
A query requests data results so that one may act on the data or just view the results. Since databases are used to store large amounts of information, retrieving specific data sets without queries would be a predicament (Juame, 2014). For instance, if the manager wishes to know the number and brands of phones sold on a specific day as well as their prices, they would just run a query to filter out unnecessary information. The queries to be run in inventory database include select query, totals query, make table query, update query, and delete query. The queries will help find data quickly by filtering through specific conditions, summarize or calculate data, and automate data management tasks (Microsoft, 2017). The select query, for instance, will enable the user to view the names and prices of the mobile phones sold by just running the query on the table with the products.
Figure 1: ERD – Over-the-Counter Model
Below is an illustration of the data modeling layout with the various specifications. The model imitated for the XYZ Mobile Phones Business inventory database is an ‘Over the Counter Model’ with five main entities. The entities are an abstraction of the database tables and are customer, credit card, item, order, and company entities. The relationship between the customer and item entities is many-to-many, meaning one customer can buy many items just as many items can belong to one or more customer. Also, the ERD reveals that the company (XYZ) can sell many items. And finally, the relationship between the item to order entities is many-to-one, meaning an order can be of many items. Each of the five entities has an attribute(s).
- Juame, J. (April 26, 2014). The Importance of Queries: The Key to Useful SM Data. Retrieved from: https://www.brandwatch.com/blog/importance-queries/
- Microsoft (2017). Introduction to queries. Retrieved from: https://support.office.com/en-us/article/Introduction-to-queries-a9739a09-d3ff-4f36-8ac3-5760249fb65c
- Watt, A. (n.d). Chapter 8: The Entity Relationship Data Model. Retrieved from: https://opentextbc.ca/dbdesign01/chapter/chapter-8-entity-relationship-model/