Data modeling is a technique to document a software system using diagrams and symbols.
It is used to represent communication of data.
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.
- Entities: Number of tables you need for your database.
- Attributes: Information such as property, facts you need to describe each table.
- Relationships: How tables are linked together.
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 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.
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.
- Each student fills one seat and one seat is assigned to only one student.
- Each professor has one office space.
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)
- One instructor can teach many courses, but one course can only be taught by one instructor.
- One instructor may teach many students in one class, but all the students have one instructor for that class.
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.
- Each student can take many classes, and each class can be taken by many students.
- Each consumer can buy many products, and each product can be bought by many consumers.
The detailed Crow's Foot Relationship symbols can be found here. Crow's Foot
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 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* 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
number is the primary key since no two students have the same student number.
We can have only one primary key in a table. It identify uniquely
every row and it cannot be null.
A foreign key+ (sometimes called a referencing key) is a key used to link two tables
together. Typically you take the primary key field from one table and insert it into the
other table where it becomes a foreign key (it remains a primary key in the original table).
We can have more than one foreign key in a table.
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.
Entities: STUDENT, COURSE, INSTRUCTOR, SEAT, CLASS, SECTION AND PROFESSOR.
Attributes for Entity STUDENT are: Student_id (*Primary key), student_name and student_address
You can use this free software to draw ERD diagram online.
Once Draw.io page is loaded, make sure to choose Entity Relation
on the left hand side.
When you finished the ERD, File->Export asPDF document.
Create an appropriate Entity Relationship Diagram (ERD) for the
data associated with an online application. This ERD should include the user accounts,
posts and re-posts tables. To get you started,
you can follow the example given below.
The User table collect attributes: user_id(*primary key), user_email, user_name, user_password
user_DOB, User_image and etc.
The post table collect attributes: post_id(*primary key), user_id (+foreign key), post_detail,
post_date, URL and etc.
What attributes should Re-post table collect?
Please export the file as a PDF document.
Upload the ERD on URcourses and link the ERD on the index.html
Be ready for next week, get your MySQL account setup.
Marks will be deducted if your MySQL account is not ready by next week's lab.
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.
- Go to the secure CS Web site
- Click on the "MySQL" link
- Login with your CS username/ password
- Select your MySQL password (must be different from your CS password)
- Please make a note (on your phone or otherwise) of this password because you will be using it in a later lab
- If an account already exists, you can reset or drop your database
- It takes 5 to 30 minutes to create a MySQL database.