ER diagrams

ER diagrams

Learning Outcomes:

• Be aware of the different types of relationships among entities in a relational database.
• Analyse simple scenarios in business, education or other fields and create simple ER diagrams involving binary relationship only in designing databases.
• The resolution of many-to-many relationship into multiple one-to-many relationships should also be introduced.
• Transform the ER diagrams to tables in relational databases.

Simple ER diagrams:

Cardinality constraint

• One-to-one relationship 1:1 : Each A has a B.
• One-to-many relationship 1:M : Each A can have more than 1 B.
• Many-to-many relationship M:N : Each A can have more than 1 B. Each B can have more than 1 A.

*Participation constraint

• Optional participation:
  Question to ask: All A must have a B? No = optional participation

• Mandatory participation:
  Question to ask: All A must have a B? Yes = mandatory participation

Tips: 

An entity present in your table could be due to their action. E.g. Customers(A) buy products(B).

Customers must have bought some products to be included in your table. Therefore, all customers must be related to certain products. i.e. All A must have at least 1 B. à mandatory

Does it mean all products must be related to customers? Not necessarily. Some products in the shop maybe so outdated that no one wants to buy. i.e. Not all B has an A. à optional

Relevant past paper:

DSE ICT Elect A(SP-2016): SP 2b,d. PP 2b. 2012 3d. 2013 4b. 2014 3a. 2015 2b. 2016 4d.

AS CA(2000-2013): 2007 6. 2008 8f. 2011 10a. 2012 2a.

Resolution of many-to-many relationship

By adding 1 more entity, a M:N relationship can be resolved into 2 1:M relationships.

Relevant past paper:

AS CA(2000-2013): 2009 4. 2012 2b. 2013 2.

Transform the ER diagrams to tables in relational databases

Relevant past paper:

DSE ICT Elect A(SP-2016): SP 2c. PP 2c.