Home‎ > ‎6 pm class pages‎ > ‎

Anh Trieu

Entity Relationship Diagram 1:N; N:M



 
Techonologies change our lives. When computers are an essential part of our activities such as work, study, research, and entertainment, the development of database has improved our way of doing. Nowadays, all companies, and corporation use a database system to organize and control information and data. However, database structure can be complex, so that data model is used to build "a logical representation of database." (Using MIS, page 150)
 
The entity relationship data model is a tool for constructing the relationship of database. "Developers use it to describe the content of a data model by defining the things (entities) that will be stored in the database and the relationships among those entities." (Using MIS, page 151) For example, an Order has a relationship to a Product and Customer entity; or a relationship between Students and Advisers. The database designers will use Entity Relationship Diagrams to show the database's relationship. Entities have three basic relationship: one-to-one relationship, one-to-many relationship, and many-to-many relationship. In this project, I just mention two formular of relationship.
 
1. One-to-many relationship(1:N): According to Database Primer,"in a one-to-many relationship, each row in the related to table can be related to many rows in the relating table." Mother-childern is 1:N relationship. A mother can have one, two, or many childern, while a child has only one mother.
Here are some other examples of 1:N relationship.
 
Company-Employee Relationship
Company-Employee Relationship
 
Each company employ one or more employees, while each employee is employed by one company
 
 
 
Master-Detail
Master-Detail Relationship of an Order (ERD Examples)
 
 Master  Detail

Order number

Customer ID

Order number

Item Code

Price

Date

 
 
2. Many-to-many relationships(N:M). The realtionship between Adviser and Student is many-to-many relationship. One adviser can have many students, and one student can have many different advisers. So, what is many-to-many relationships? According to Database Primer, "in a many-to-many relationship, one or more rows in a table can be related to 0,1 or many rows in another table."  
There are some other example of N:M relationship
 
Lecture-Course Relationships
 
For each lecturer, it can be teaches at many different courses. For each course, it includes many different lectures.

 
 
 "An Author can write several Books, and a Books can be written by several Authors"
                                            (Wiki)
 
 
 
3. Junction Table
" What will we do if the database management systems only support for one-to-many relationship?" In this situation, we can use Junction table. Junction table is likely a bridge to link two or more one-to-many relationship diagram together if they has the same primary keys. Assume that, A and B is two 1:N relationships and AB is junction table. We need to make the link between A to AB, and B to AB based on the primary keys that forms from two foreign keys.
 
 
This picture from Wiki will show an example of junction table.
 
 
 
 
 
 
 
This is an examle of many-to-many relationship using junction table. The relationship between Computers and Computers Techs is one-to-many relationship (Primary key: Computer ID). The relationship between Computers Techs and Techs is also one-to-many relationship (Primary key: Tech ID).
 
 
 
 
 
 
 
 
 
 
 
 
4. Creat a Relationship.
So how to creat a relationship? There are some basic step to creat a relationship from Officemicrosoft.com
  • Creat table, field and entities.
  • You creat relationship by using by using a field from one table as a fiels in a related table. So that, you need to set up primary key and foreign key (the new field in the second table).
  • Types or relationships: one-to-one relationship; one-to-many-relationships; many-to-many relationships.
  • Sides or relationship. For example, one supplier can provide many assets, therefore, Supplier Table is one side of the relationship. While the Asset Table stays in many side.
  • Creat a relationship: The video in this link show how to creat a relationship in database  http://office.microsoft.com/en-us/access-help/create-relationships-RZ101772998.aspx?section=5
  • Set referential integrity that keeps your primary and foreign key field when you want to change, add, or remove data.
  • Creat lookup field.

 

 
Comments