Data Modeling and Entity Relationship Diagram (ERD)

Data Modeling

A data model is a communication tool. Think of all of the people involved in building a simple application: business professionals, business analysts, data modelers, data architects, database developers, database administrators, developers, managers, etc. People have different backgrounds and experiences and varying levels of business knowledge and technical expertise. The data model allows us to confirm our knowledge of the area and make sure people see the information landscape similarly or, at a minimum, have an understanding of the differences that exist.

Hoberman, S. (2014) Data Modeling for MongoDB. Technics Publications.

The highest level of abstraction for the data model is called the Entity Relationship Diagram (ERD). It is a graphical representation of data requirements for a database.

Entity Relationship Diagram

The main value of carefully constructing an ERD is that it can readily be converted into a database structure.

There are three components in ERD.

Entity

Entities are the basic objects of ERDs. These are the tables of your database. Entity are nouns and the types usually fall into five classes. Concepts, location, roles, events or things. For example: students, courses, books, campus, employees, payment, projects. Specific example of an entity is called an instance. Each isntance becomes a record or a row in a table. For example, the student John Smith is a record in a table called students.

Relationships

Relationships are the associations between the entities. Verbs often describe relationships between entities. We will use Crow's Foot Symbols to represent the relationships. Three types of relationships are discussed in this lab. If you read or hear cardinality ratios, it also refers to types of relationships.

relationship

One to One Relationship (1:1)

A single entity instance in one entity class is related to a single entity instance in another entity class.

For example:

One to Many Relationship (1:M)

A single entity instance in one entity class (parent) is related to multiple entity instances in another entity class (child)

For example:

Many to Many Relationship (M:M)

Each entity instance in one entity class is related to multiple entity instances in another entity class; and vice versa.

For example:

The detailed Crow's Foot Relationship symbols can be found here. Crow's Foot Relationship Symbols

Many to many relationships are difficult to represent. We need to decompose a many to many (M:M) relationship into two one-to-many (1:M) relationships.

Attributes

Attributes are facts or description of entities. They are also often nouns and become the columns of the table. For example, for entity student, the attributes can be first name, last name, email, address and phone numbers.

Primary Key

Primary Key or identifier is an attribute or a set of attributes that uniquely identifies an instance of the entity. For example, for a student entity, student identification number is the primary key since no two students have the same student number.

An example

The following document is a quick guide for the Crow's foot ERD symbol guide.

ERD Relationship Symbols Quick Reference http://www.vivekmchawla.com/erd-crows-foot-relationship-symbols-cheat-sheet

This diagram example was redrawn from http://www2.cs.uregina.ca/~bernatja/crowsfoot.html using ConceptDraw PRO software enhanced with ConceptDraw ERD solution.

crow's foot

http://www.conceptdraw.com/examples/erd-example-with-diagram

Entities: STUDENT, COURSE, INSTRUCTOR, SEAT, CLASS, SECTION AND PROFESSOR.

Attributes for Entity STUDENT are: Student_id (*Primary key), student_name and student_address

Define the three kinds of Relationships.

You can use this free software to draw your ERD online. Draw.io

Once Draw.io page is loaded, make sure to choose Entity Relation on the left hand side. When you finished the ERD, File->Export as PDF document.

In Class Exercise Lab Assignment

Description

A university consists of a number of faculties, for example, faculty of Scince, Engineering, Arts etc. Each faculty offers many degrees. A number of classes make up each degree. Students enroll in a particular degree need to take many classes towards the completion of that degree. Each class is taught by an instructor from the specific faculty, and each instructor teaches many students.

Identify entities

A university consists of a number of faculties. Each faculty offers many degrees. A number of classes make up each degree. Students enrol in a particular degree need to take many classes towards the completion of that degree. Each class is taught by an instructor from the specific faculty, and each instructor teaches many students.

Design the ERD

Draw your ERD online.Draw.io
Please export the file as a PDF document.

  1. Identify the entities, attributes, primiary keys and relationships from the description.
  2. Draw an entity-relationship diagram showing the items you identified.
  3. Upload your ERD on URcourses before due time and also link the ERD on your index.html

Be ready for next week, get your MySQL account setup.

Mark will be deducted if your MySQL account is not ready by next week's lab time.

Creating a MySQL Account

For storing records or data sets, the CS215 labs use Hercules to work with a MySQL database. The following provides instructions to create your first MySQL database account.

  1. Go to the secure CS Web site
  2. Click on the "MySQL" link

    secure CS Web site

  3. Login with your CS username/ password

    login

  4. Select your MySQL password (must be different from your CS password)
  5. Please make a note (on your phone or otherwise) of this password because you will be using it in a later lab
  6. If an account already exists, you can reset or drop your database

    reset or drop DB

  7. It takes 5 to 30 minutes to create a MySQL database.