You are to write a brief discussion of your solution, i.e. how you approached the modeling problem and any issues you may have encountered (maximum of ½ page) You are to write all applicable business rules necessary to establish entities,relationships, optionalities, connectivities, cardinalities and constraints. If a many–tomany relationship is involved, state the business rules regarding the bridging entities after breaking down the many-to-many relationship. An example business rules format can be found in Appendix A of this document.
Based on these business rules*, draw a fully labeled and implementable EntityRelationship Diagram (ERD). Include all entities, relationships, optionalities,connectivities, cardinalities and constraints. You must use Crow’s foot notation and MS Visio (or other software) to create the ERD. A Hand-drawn ERD will NOT be accepted. A sample ERD can be found in Appendix A of this document. (Note: The ERD created using Visio will need to be saved as an image file and then be included in your document file to be submitted).
A summary to describe the major justifications, assumptions and limitations related to your database design. For example: Assumption/justifications for optionalities, connectivities, constraints data type and data domain; and special cases or data integrity issues that cannot be handled.
Database constitutes one of the most significant elements for the information management and processing that is carried out by the business organizations. The amount and type of information that is associated with the business units is required to be carefully handled. The case study is based upon the business that is carried out by Francine. She inherited a bookstore from one of her relatives and has now termed the business as Francine’s Op Shop (FOPS). She decided to store her information in the database so that the information is correctly organized.
The computerized databased that will be designed for the business will store the information on the books and DVDs present in the store along with the inventory information. The details associated with the sales receipts and the items in the old stock will also be managed by the database. The information associated with the members of the staff along with their uniforms will be kept in the database.
One of the other purpose for which the database will be designed will be the reports that will be created and managed by the information present in the database. There will be automated reports that will be generated on the basis of year, month, week, hour and likewise and will be allowed to be extracted as well. The report covers the ER Diagram for the business along with the set of assumptions and business rules that apply.
In the solution, I majorly faced issues in mapping the RECEIPT and ITEM entities. A receipt can have more than one item and one item can belong to more than one receipt. This is not a many-to-many relationship. Hence, I had to introduce two new entities named as ORDER and ORDER_DETAILS. An item can belong to one to many ORDERs and a single ORDER can have many items. Hence this relation was mapped using an associative ORDER_DETAIL. A single ORDE can have only one RECEIPT which has been mapped as one-to-one mapping and fulfills the scenario.
Entity Relationship Diagram is also referred as ER Diagram or ERD and it is a structural diagram that is used for the design of the database. The diagram is used for modeling the various entities that are present in the database along with the relationships present among these entities.
In case of FOPS, the entities that are present include ITEM, RECEIPT, EMPLOYEE, ORDER, ORDER_DETAIL, BOOK, AUTHOR, AUTHOR_BOOK, DVD, DVD_ACTOR_DETAIL, ACTOR_DIRECTOR, VOLUNTEER, PUBLISHER and HIRED. All of these entities have a relationship between each other and there are specific attributes that are also associated with each (Iitj, 2017). The attributes of these entities along with the relationship that is present is as depicted in the diagram below.
There are a few assumptions that have been used in the ER Diagram and the database modeling that has been done for the business of Francine. Also, there are specific assumptions that are associated with the business rules that apply to the business. The assumptions are as listed below.
A uniform used once, shall not be used by another employee.
An author can write zero books and a single book can be written by one to many authors.
A DVD has at least one actor and one director.
The business rules that apply to the business are as listed below. The author may or may not have written a book and the value that will be assigned to the entity will determine the same. There is a one-to-many relationship present between the publisher and the book, that is, a book will be published by a single publisher while one publisher may have published multiple books. There is also a minimum of one actor and one director associated with the DVD. An item may also be associated with multiple orders. There is a one-to-one relationship that is present between the receipt and the order. The employees may process multiple orders and there is a single uniform that is assigned to an employee. These business rules are listed below on the basis of every entity involved.
Each AUTHOR has written zero or many AUTHO_BOOK.
Each BOOK has been written by one or many AUTHORs.
Each BOOK is published by one PUBLISHER only.
Each PUBLISHER publishes one or many BOOKs.
Each DVD has at least one ACTOR and one DIRECTOR.
It is a mapping entity between DVD and ACTOR_DIRECTOR
Each ITEM belongs to zero or many ORDERS.
Each ORDER has only one RECEIPT
Each receipt belongs to only one ORDR
Each employee processes zero or many ORDERs.
Each EMPLOYEE has one and only UNIFORM
There are various database design approaches and techniques that may be used. The one that has been used in this case is the designing of the Entity Relationship Diagram (ERD). There are different entities that are involved in the business that is carried out by Francine. These entities and the relationship that is present between these entities can be easily explained by the ER model. It provides a visual representation of every detail of the database which makes it easier to implement the business specifications. The design and development team can also acquire the required information so that the same may be implemented correctly (Kumar, 2015). There are also other database design techniques that may be used, such as conceptual modeling, normalization and likewise. Each of these techniques has their own pros and cons.
The database that is being designed for Francine’s business will offer various advantages as well. The information sets will be stored in a manner such that there is no duplication or replication of data. Also, the data will be adequately organized and the security of the data sets will also be enhanced. There may be certain security risks and attacks that may take place in spite of the security controls and measures. The database will have the process of automated backups as well. In such measures, a copy of the data sets will always be stored at another location which will ensure that there is no data loss. The information will be easily shared among the employees as well and that will make it easier to take the business decision accurately and quickly.
Iitj. (2017). Data Modeling Using the Entity-Relationship (ER) Model. Home.iitj.ac.in. Retrieved 19 December 2017, from http://home.iitj.ac.in/~ramana/ch3-ER-diagrams.pdf
Kumar, P. (2015). Entity-Relationship (E/R) Model. Nptel.ac.in. Retrieved 19 December 2017, from http://nptel.ac.in/courses/106106095/pdf/2_Entity_Relationship_Model.pdf.
Our Amazing Features
No missing deadline risk
No matter how close the deadline is, you will find quick solutions for your urgent assignments.
100% Plagiarism-free content
All assessments are written by experts based on research and credible sources. It also quality-approved by editors and proofreaders.
500+ subject matter experts
Our team consists of writers and PhD scholars with profound knowledge in their subject of study and deliver A+ quality solution.
Covers all subjects
We offer academic help services for a wide array of subjects.
We care about our students and guarantee the best price in the market to help them avail top academic services that fit any budget.
Getting started with MyEssayAssignmentHelp is FREE