SQLite
SQLite Tutorial for Beginners: Learn in 3 Days
What is SQLite? SQLite is an open-source, embedded, relational database management system,...
Entity Relationship Model (ER Modeling) is a graphical approach to database design. It is a high-level data model that defines data elements and their relationship for a specified software system. An ER model is used to represent real-world objects.
An Entity is a thing or object in real world that is distinguishable from surrounding environment. For example, each employee of an organization is a separate entity. Following are some of major characteristics of entities.
In this tutorial, you will learn-
Let's consider our first example again. An employee of an organization is an entity. If "Peter" is a programmer (an employee) at Microsoft, he can have attributes (properties) like name, age, weight, height, etc. It is obvious that those do hold values relevant to him.
Each attribute can have Values. In most cases single attribute have one value. But it is possible for attributes have multiple values also. For example Peter's age has a single value. But his "phone numbers" property can have multiple values.
Entities can have relationships with each other. Let's consider the simplest example. Assume that each Microsoft Programmer is given a Computer. It is clear that that Peter's Computer is also an entity. Peter is using that computer, and the same computer is used by Peter. In other words, there is a mutual relationship between Peter and his computer.
In Entity Relationship Modeling, we model entities, their attributes and relationships among entities.
Enhanced Entity Relationship (EER) Model is a high-level data model which provides extensions to original Entity Relationship (ER) model. EER Models supports more details design. EER Modeling emerged as a solution for modeling highly complex databases.
EER uses UML notation. UML is the acronym for Unified Modeling Language; it is a general-purpose modeling language used when designing object-oriented systems. Entities are represented as class diagrams. Relationships are represented as associations between entities. The diagram shown below illustrates an ER diagram using the UML notation.
Now you may think why use ER modeling when we can simply create the database and all of its objects without ER modeling? One of the challenges faced when designing a database is the fact that designers, developers, and end-users tend to view data and its usage differently. If this situation is left unchecked, we can end up producing a database system that does not meet the requirements of the users.
Communication tools understood by all stakeholders(technical as well as non-technical users) are critical in producing database systems that meet the requirements of the users. ER models are examples of such tools.
ER diagrams also increase user productivity as they can be easily translated into relational tables.
Case Study: ER diagram for "MyFlix" Video Library
Let's now work with the MyFlix Video Library database system to help understand the concept of ER diagrams. We will use this database for all hand-on in the remainder of this tutorial
MyFlix is a business entity that rents out movies to its members. MyFlix has been storing its records manually. The management now wants to move to a DBMS
Let's look at the steps to develop EER diagram for this database-
The entities to be included in our ER diagram are;
Members and movies
The following holds true regarding the interactions between the two entities.
From the above scenario, we can see that the nature of the relationship is many-to-many. Relational databases do not support many-to-many relationships. We need to introduce a junction entity. This is the role that the MovieRentals entity plays. It has a one-to-many relationship with the members table and another one-to-many relationship with movies table.
Movies and categories entities
The following holds true about movies and categories.
We can deduce from this that the nature of the relation between categories and movies table is one-to-many.
Members and payments entities
The following holds true about members and payments
We can deduce from this that the nature of the relationship between members and payments entities is one-to-many.
Now lets create EER model using MySQL Workbench
In the MySQL workbench , Click - "+" Button
Double click on Add Diagram button to open the workspace for ER diagrams.
Following window appears
Let's look at the two objects that we will work with.
The members' entity will have the following attributes
Let's now create the members table
1.Drag the table object from the tools panel
2.Drop it in the workspace area. An entity named table 1 appears
3.Double click on it. The properties window shown below appears
Next ,
Your properties window should now look like this.
Repeat the above steps for all the identified entities.
Your diagram workspace should now look like the one shown below.
Lets create relationship between Members and Movie Rentals
Repeat above steps for other relationships. Your ER diagram should now look like this -
The entire ER Model is attached below. You can simply import it in MySQL Workbench
Click Here To Download ER Model
What is SQLite? SQLite is an open-source, embedded, relational database management system,...
What are Loops? Loops allows a certain part of the code in a program to get executed for the...
PL/SQL Tutorial Oracle PL/SQL is an extension of SQL language, designed for seamless processing of SQL...
What is Package in Oracle? PL/SQL package is a logical grouping of a related subprogram...
SQLite databases are very lightweight. Unlike other database systems, there is no configuration,...
What is INSERT INTO? INSERT INTO is used to store data in the tables. The INSERT command creates a new...