7.1: Introduction and Background

Mapping an Entity Relationship (ER) model gives a good overview of the design of a  system with the goal of making the system easier to understand at a technical level. The ER diagrams can be mapped to a relation schema, which means we can clearly display the relationship between its members. The database schema is a structure that describes in a formal language the association of data as a blueprint of how the database can be constructed. 

To understand this process, we will review below what an ER model is. An ER model is used to illustrate the logical interpretation of the system and consists of three components: Entity, Entity Type and Entity Set. 

Entity can refer to a person, a concept, an object, a virtual file, or it can represent an idea that can be quantified, such as a company, a job, or a document. An entity consists of an Entity Type and everything that consists of an entity type is called an Entity Set. The example below will clarify the power of design a schema showcasing an entity type, “Car,” and an entity set consisting of all cars. 

Figure \(\PageIndex{1}\): An Entity Set of All Cars. ("Module_3: Mapping ER to Schema, Normalization" by Dr Sarah North, Affordable Learning Georgia is licensed under CC BY 4.0)

The next important step in understanding the mapping is an Attribute. Attributes are properties which define the entity type. For example, a person may have the attributes name, date of birth, and address. The attribute in an ER diagram is represented by an oval. 

Figure \(\PageIndex{1}\): Attribute in an ER Diagram. ("Module_3: Mapping ER to Schema, Normalization" by Dr Sarah North, Affordable Learning Georgia is licensed under CC BY 4.0)

The attribute that uniquely identifies each entity is called a Key Attribute and is represented by an oval with underlining. A key attribute can be “ID,” which identifies each entity by a unique number. 

Figure \(\PageIndex{1}\): Key Attribute in an ER Diagram. ("Module_3: Mapping ER to Schema, Normalization" by Dr Sarah North, Affordable Learning Georgia is licensed under CC BY 4.0)

For complex designs, there are multiple different types of attributes: Composite Attributes, consisting of many individual attributes, Multivalued Attributes, consisting more than one value, and Derived Attributes, which are derived from other attributes as the name suggests. 

Between entities, there are relationships. A relationship has a type and can be part of a sets. The latter consists of one, two or many relationship types: unary, binary or N-ary. 

Figure \(\PageIndex{1}\): Multiple Different Types of Attributes. ("Module_3: Mapping ER to Schema, Normalization" by Dr Sarah North, Affordable Learning Georgia is licensed under CC BY 4.0)

Relationship Type: 

Figure \(\PageIndex{1}\): Relationship Type. ("Module_3: Mapping ER to Schema, Normalization" by Dr Sarah North, Affordable Learning Georgia is licensed under CC BY 4.0)

Relationship Set: 

Figure \(\PageIndex{1}\): Relationship Set. ("Module_3: Mapping ER to Schema, Normalization" by Dr Sarah North, Affordable Learning Georgia is licensed under CC BY 4.0)

Unary, Binary Relationship: 

Figure \(\PageIndex{1}\): Unary, Binary Relationship ("Module_3: Mapping ER to Schema, Normalization" by Dr Sarah North, Affordable Learning Georgia is licensed under CC BY 4.0)

An ER diagram can also have a Weak Entity. A weak entity is an entity type for which a key attribute can’t be defined. A company can store information of dependents (Parents, Children, Spouse) of an Employee but the dependents don’t exist without the Employee; we call this entity a weak entity. 

Figure \(\PageIndex{1}\): Weak Entity. ("Module_3: Mapping ER to Schema, Normalization" by Dr Sarah North, Affordable Learning Georgia is licensed under CC BY 4.0)