CP5633 Database Modelling

Published : 17-Sep,2021 | Views : 10

Question:

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.

Answer:

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.

Assumptions

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.

Business rules

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.

Entity: AUTHOR

Each AUTHOR has written zero or many AUTHO_BOOK.

Entity: BOOK

Each BOOK has been written by one or many AUTHORs.

Each BOOK is published by one PUBLISHER only.

Entity: PUBLISHER

Each PUBLISHER publishes one or many BOOKs.

Entity: DVD

Each DVD has at least one ACTOR and one DIRECTOR.

Entity: DVD_ACTOR_DETAIL

It is a mapping entity between DVD and ACTOR_DIRECTOR

Entity: ITEM

Each ITEM belongs to zero or many ORDERS.

Entity: ORDER

Each ORDER has only one RECEIPT

Entity: RECEIPT

Each receipt belongs to only one ORDR

Entity: EMPLOYEE

Each employee processes zero or many ORDERs.

Each EMPLOYEE has one and only UNIFORM

Conclusion

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.

References

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.