ERM depicts the database's main components. Which includes;

Entities

• An object of interest to the end user
• ERM refers to a table row as an entity instance or entity occurrence
• In Chen, Crow's Foot, and UML notations, an entity is represented by a rectangle that contains the entity's name
• The entity name, is a noun, usually written in all capital letters

Attributes

• Characteristics of entities e.g. STUDENT entity includes the attributes STU_LNAME, STU_FNAME, & STU_INITIAL etc

Relationships

• Entities interactions

Student Entity & Attributes

Attributes

• Required- is an attribute that must have a value
• Optional- is an attribute that does not require a value

Identifiers (Primary Keys)

• In a relational model, entities are mapped to tables, & the entity identifier is mapped as the table'sprimary key
• e.g. CAR (CAR_VIN, MOD_CODE, CAR_YEAR, CAR_COLOR)

Composites

• Composite identifier- a key composed of more than one attribute
• Composite attribute- an attribute that can be further subdivided to yield additional attribute

Simple attribute - an attribute that cannot be subdivided into meaningful components

• Single-valued attribute- attribute with only one value
• Multi-valued attribute- attribute that can have many values for a single entity occurrence
• Derived attribute- an attribute that does not physically exist within the entity, but it's derived via an algorithm

A relationship is an association between entities

Participants- entities that participate in a relationship

The relationship name is an active or passive verb;

• a STUDENT takes a CLASS
• a PROFESSOR teaches a CLASS
• a DEPARTMENT employs a PROFESSOR
• an AIRCRAFT is flown by a CREW

Connectivity- classification of the relationship between entities. Which includes; 1:1, 1:M, & M:N

Cardinality- a property that assigns a specific value to connectivity and expresses the range of allowed entity occurrences associated with a single occurrence of the related entity

• is indicated by placing the appropriate numbers beside the entities, using the format (min,max)
• Connectivities and cardinalities are established by concise statements known as business rules
• implementation of the cardinalities -use of triggers

Entity is existence-dependent- if it can exist in the database only when it is associated with another related entity occurrence

• In implementation terms, an entity is existence-dependent if it has a mandatory foreign key
• In such an environment, the existence-independent table must be created and loaded first because the existence-dependent key cannot reference a table that does not yet exist

Relationship strength

• Weak (Non-Identifying) Relationship A relationship in which the primary key of the related entity does not contain a primary key component of the parent entity

Relationship strength

• This relationship exists whenever the primary key of the related entity contains the primary key of the parent entity

Optional participation

• It's a relationship in which one entity occurrence does not require a corresponding entity occurrence in a particular relationship

Mandatory participation

• It's a relationship in which one entity occurrence must have a corresponding occurrence in another entity
• For example, an EMPLOYEE works in a DIVISION. i.e. A person cannot be an employee without being assigned to a company's division

A relationship degree indicates the number of entities or participants associated with a relationship. The types are;

• unary relationship- an ER term used to describe an association within an entity, e.g., an EMPLOYEE might manage another EMPLOYEE
• binary relationship- an ER term for an association (relationship) between two entities, e.g., PROFESSOR teaches CLASS
• ternary relationship- an ER term used to describe an association (relationship) between three entities, e.g., a DOCTOR prescribes a DRUG for a PATIENT

The process of database design is iterative rather than a linear or sequential process

Steps;

• Create a detailed narrative of the organization's description of operations
• Identify the business rules based on the description of operations
• Identify the main entities & relationships from the business rules
• Develop the initial ERD
• Identify the attributes & primary keys that adequately describe the entities
• Revise and review the ERD

Try out

• Draw a table of the ERM components
• Entity | Relationship | Connectivity | Entity
• Develop a ER diagram for COETEC
• Hint: entities includes; Professor, School, Department, Course, Class, Semester, Student, Building, Room and Enroll (the associative entity between Student and Class)
• Develop an ER diagram for GEGIS Students' Projects

Queries about this Lesson, please send them to: jmwaura@jkuat.ac.ke

*References*

• Database Systems: Design, Implementation, and Management, 12th ed. Carlos Coronel & Steven Morris
• Database Modeling and Design; Logical Design, 5th ed. Taby Teorey et.al
• Fundamentals of database systems, 6th ed. Ramez Elmasri & Shamkant B. Navathe