Wednesday, August 10, 2022

Database Management Post 1: The First Step For Creating a Database Is To Design An Entity Relationship Diagram

Covid-19 has been brutal for many businesses and industries. However, there have been such industries such as Healthcare, and Tech, that have been flourishing. E-Commerce is another example of an industry that has seen some growth, as in-person shopping has been limited due to COVID protocol and safety measures.

 

One company in particular, JUNGLE, is a start-up E-commerce company that has hired you to design and create their database management system in efforts to rapidly scale their business. As an aspiring database systems manager, you know that the first step to this process is to create an Entity Relationship (ER) diagram


This is always mandatory in a database systems design project as it helps designers become more comfortable with the components of a business.

 

This a tedious part of the assignment, but you are eager to accept it, nonetheless.

 

So, you meet with the Founder of JUNGLE, Jeffrey Kisses, to get an idea of the components of your ER diagram. You listen enthusiastically and learn the following information:

 

1. You have collaboratively determined the three key entities of the ER and they are CUSTOMER, TRANSACTION, and PRODUCT.

 

2. Each CUSTOMER can “order” multiple products from the JUNGLE platform, and a PRODUCT can only be owned by one customer.

 

3. A CUSTOMER can “make” multiple transactions. Yet, a TRANSACTION can only belong to one customer.

 

4. A TRANSACTION can “have” multiple products, and PRODUCTS can belong to multiple transactions. This is to account for any products that a customer may or may not return.

 

5. The following attributes have been correlated with the following entities:

            

CUSTOMER: will have the attributes of customer_id, name, age, gender, and zip code. It is important to note that customer_id is a unique attribute.

 

TRANSACTION: will have the attributes of purchase_id, customer_id, product_id, if_returned, date_of_purchase. Under this entity, the purchase_id will be a unique attribute.


PRODUCT: will have the attributes product_name, price, product_id. In this case, product_id will be a unique attribute.

 

After careful consideration, you determine that the next step of designing the ER model. You will need to classify the entities and attributes and connect the entities via lines called relationships. It goes without saying that cardinality between these relationships needs to be assigned as well.

 

Process and Next Steps:


You should design the ER using the following guidelines:

 

1. Place components for entities in RECTANGLES and all text should be in CAPS.


2. Attributes will be listed in the OVALS and connected with lines to the entity component. Lines between attributes and entity will be of different color (Blue) to be differentiated between relationships (Red) between entities. “Unique” attributes will be underlined.


3. Relationships will connect the entities with red lines and will be notated as follows:



            CUSTOMER:PRODUCT = 1:N

            CUSTOMER: TRANSACTION = 1:N

            TRANSACTION: PRODUCT = M:N



Verbs will be in diamonds connecting the relationships.



Entity Relationship Diagram



THANK YOU! 


This post was a change of pace, slightly. Database Management resides under the data analytics umbrella, so it is important to be somewhat familiar with it.


I hope this post is useful to you. Thank you for visiting my blog. It is as much yours as it is mine!

Share:
Location: Chicago, IL, USA