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. 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 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, locations, roles, events or things. A specific example of an entity is called an instance. Each instance becomes a record or a row in a table. 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. 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. AttributesAttributes 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 KeyPrimary 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. Foreign keyA 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. An ExampleHere's a sample crowsfoot diagram from a past offering of CS270 taught here at the University of Regina. We've redrawn the diagrams using more modern diagramming tools, but the content is unchanged. It uses a lot of ERD symbols, so you might want to use Vivek Chawla's quick guide while you read it. Quick QuestionsHow many entities are there in this diagram and what are they?There are seven entities: STUDENT, COURSE, INSTRUCTOR, SEAT, CLASS, SECTION and PROFESSOR.What are the attributes for entity STUDENT?The attributes for Entity STUDENT are: student_id, student_name and student_addressWhat is the primary key for STUDENT?The primary key for STUDENT is: student_idWhat is the primary key for COURSE?Not a trick question! There is only one primary key, but it is made up of two attributes. This is called a compound key.What foreign keys do STUDENT and COURSE contain?STUDENT and COURSE contain no foreign keys in this diagram. This might suggest that there are problems with the design... among them is the many to many relationship here. This usually requires that we create a separate table to describe the relationship. This type of table usually connects foreign ids to each other.In this case, let's add an entity called REGISTRATION in the middle of the "takes" relationship. Since students probably sit in different seats for each course they are registered in, lets relate SEAT to REGISTRATON instead of STUDENT: The registration entity also uses a special case of compound key that includes compound foreign keys - it is a composite key. [ref]. ERD ToolsMany tools are available to help you design Entity Relationship Diagrams. Here is a short list of options you might want to try.Visio
ConceptDraw
draw.io/diagrams.net
Lab AssignmentDescriptionCreate 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 entities. To get you started, you can follow the example given below. User entityThe User entity collect attributes: user_id(*primary key), user_email, user_name, user_password user_DOB, User_image and etc. Post entityThe post entity collect attributes: post_id(*primary key), user_id (+foreign key), post_detail, post_date, URL and etc. Re-post entityWhat attributes should Re-post entity contain? Think of one or more things a repost can add to a post. Justify your design with a label. RelationshipsThe relationship shown between user and post in our example is good enough. Add relationships between Re-post and the other two. Justify or explain your decisions in text labels, and be sure you have selected compatible primary and foreign keys. For example: A user may make many posts, but each post was written by only one user. Please export the file as a PDF document. Upload the ERD on UR Courses and provide a working link to the ERD from your homepage before the deadline indicated in UR Courses for your section. Be ready for next lab, get your MySQL account set up.Marks will be deducted if your MySQL account is not ready by next lab. Creating a MySQL AccountFor storing records or data sets, the CS215 labs use WebDev to work with a MySQL database. The following provides instructions to create your first MySQL database account.
What are the three key parts of an entityThe Entity Relationship Diagram (ERD) is a graphical representation of an ERM and is currently one of the most popular and widely used database design tools. The three main components of the ER Model are entities, attributes and relationships.
How do you determine a relationship in ERD?Here's the basic order to follow.. Determine the Entities in Your ERD. Start by identifying the “what”s in your system or architecture. ... . Add Attributes to Each Entity. ... . Define the Relationships Between Entities. ... . Add Cardinality to Every Relationship in your ER Diagram. ... . Finish and Save Your ERD.. Why are entity relationship diagrams ERD considered to be so useful?Importance of ERDs and their uses
Entity relationship diagrams provide a visual starting point for database design that can also be used to help determine information system requirements throughout an organization.
What are the main constructs of an ERD?There are three basic elements in an ER Diagram: entity, attribute, relationship. There are more elements which are based on the main elements. They are weak entity, multi valued attribute, derived attribute, weak relationship, and recursive relationship.
|