ER and relational model

Introductory concepts

Where data comes from:

• Real world: This is the part of reality that interests us. It consists of concrete objects.
• Logical or conceptual world (knowledge or information): An abstract model of the real world.
• Representative world (data): The transition from the abstract model of information to a computer representation.

The real world

From the real world, we must extract abstractions of what interests us.

The conceptual world: entities, attributes, and values

All information is characterized by three elements: entity, attribute, and value.

Entity: A distinguishable object from others, whose properties interest us. It is the object of information.

Attribute: The properties that describe the object.

Value: The data itself that characterizes the attribute.

Domain: The set of all valid values an attribute can have.

Null: Determines an unknown value.

Key: An attribute or set of attributes that allows individual entities to be identified.

The world of representations

The computer representation of an elementary piece of information is called data.

To represent data, a tabular representation is usually used, where each row represents an individual entity and each column identifies an attribute of the entity.

Register/Record: The representation of an entity, equivalent to a row in the table.

Field: The representation of an attribute of an entity.

The set of fields forms the record, and the set of records forms the file or table.

Conceptual design: The entity-relationship (ER) model

The entity-relationship data model is used to model data for use in a database. ER models are directly translatable to the relational model. These models are represented using ER diagrams.

ER modeling is based on three concepts:

• Entities: Real-world objects of which we want to model some properties, and which are distinguishable from other objects.
• Relationships: Associations and interactions between entities.
• Attributes: Properties of interest of entities and some relationships.

Entities and attributes

In an ER diagram, entity types are represented, defining a collection of similar entities. An entity type is represented with its name in capital letters inside a box.

Keys

Candidate key

A set of attributes that uniquely identifies an entity.

Simple and composite keys

A simple key consists of a single attribute. A composite key consists of two or more attributes.

Primary key

The candidate key chosen by the database designer to uniquely identify entities.

Alternative key

Candidate keys that were not chosen as the primary key.

Synthetic keys

An artificial key created when no natural candidate key exists or when they are too complex.

Relationships

Degree of relationships

Refers to the number of entities participating in a relationship.

Attributes in relationships

Relationships can also have attributes that describe the association.

Connectivity of binaryrelationships

1 to 1 relationships (1:1)

Each instance of entity A is related to at most one instance of entity B, and vice versa.

1 to N relationships (1:N)

One instance of entity A can be related to many instances of entity B, but B is related to only one of A.

N to M relationships (N:M)

Instances of entity A can be related to many instances of entity B, and vice versa.

Minimum connectivities

Specifies the minimum number of times an entity instance can participate in a relationship (usually 0 or 1).

Recursive relationships

When an entity is related to itself.

Ternary relationships

Relationships involving three different entities.

Connectivity of ternary relationships

Defines the mapping constraints (1:1:1, 1:1:N, 1:N:M, N:M:P) within a three-way association.

Types of entities

Strong and weak entities

Strong entities exist independently. Weak entities depend on a "parent" entity for their existence and identification.

Generalization/specialization

A hierarchy where a "supertype" entity shares common attributes with "subtype" entities.

Associative entities

A relationship that is treated as an entity so it can participate in other relationships.

The relational model

Fundamental concepts in the relational model

Domain

The set of valid values for an attribute.

Registries

The set of rows in a table.

Degree

The degree is the number of attributes in a table.

Properties of a table

• It has a unique name within the database.
• No duplicate rows.
• Column entries are atomic.
• Column entries are from the same domain.
• Operations combining different data types are not allowed.
• Each attribute has a unique name.
• The order of columns is insignificant.
• The order of rows is insignificant.

Terminology

Terminology table (Term | Alt 1 | Alt 2):

• Relation | Table | File
• Tuple | Row | Record
• Attribute | Column | Field

Integrity rules and constraints

Constraints allow a designer to specify the semantics of data. They are rules that force the DBMS to check that data satisfies the semantics.

Domain integrity

The domain restricts the values of attributes in the relation.

Entity integrity

Requires that every table has a primary key. Neither the PK nor any of its parts can contain null values.

Referential integrity

Requires that a foreign key must either have a corresponding primary key or be null. It prevents "orphan records" (FK pointing to a non-existent PK).

Note: When configuring referential integrity, the PK and FK must have the same data type and come from the same domain.

Rules for foreign keys

Additional rules for when a parent record is modified or deleted:

• Cascade Update: Propagates changes to child records.
• Cascade Delete: Deletes related child records.

Business rules

Additional rules specified by users or administrators.

Cardinality and connectivity

Constraints based on the number of related entities.

Types of relationships

Specific business logic defining how entities interact.

Optional and mandatory relationships

Determines if an entity must or may participate in a relationship.

Translating the ER model to the relational model

1 to 1 relationships

The primary key of one side is placed as a foreign key (FK) in the other.

1 to N relationships

The primary key from the "1" side is passed to the "many" (N) side as a foreign key.

N to M relationships

A new table is created containing the primary keys of both entities as a composite primary key.

Weak entities

The primary key of the weak entity is composed of the primary key of the strong entity plus a discriminator.

Recursive relationships

The primary key is mapped as a foreign key within the same table, using a different name.

Ternary relationships

Example of 1-1-1 Ternary Relationship

Handled by merging keys into one of the entities or creating a junction table depending on specific constraints.

Example of N-M-1 Ternary Relationship

The PKs of the "N" and "M" sides are usually moved to a new table along with the "1" side's PK.

Example of N-M-P Ternary Relationship

A new table is created with the PKs of all three entities.

Example of Transformation of a Hierarchical Relationship

Conversion of sub-types into either separate tables or a single table with a type indicator.

Issues in ER to Relational Transformation

The Deadlock (Circular Reference)

Occurs when Table A references Table B, and Table B references Table A.

Solution 1: Create Table A first without the FK. Create Table B with the FK to A. Then add the FK constraint to Table A. Example: DEPARTMENTS(Id, ..., idEmployee) EMPLOYEES(IdEmployee, ..., idDepartment) where idDepartment references DEPARTMENTS.

Solution 2: Create a third table (Table C) to manage the relationship so that the initial tables do not reference each other directly. Example: DEPARTMENTS(Id, ...) EMPLOYEES(IdEmployee, ..., idDepartment) MANAGED_BY(idDepartment, idEmployee)

The second solution is generally better for avoiding insertion problems.

Summary of ER to Relational Transformations

Transformation mappings (ER Model -> Relational Model):

• Entity -> Table
• Attributes -> Columns
• Weak Entity -> New table with owner's PK
• Attributes -> Same fields
• Primary Key -> Primary Key
• 1:1 Relationship -> Pass the key to one side as FK
• 1:N Relationship -> Pass the key of the "1" side to the "many" side as FK
• N:N or Non-binary -> New table
• N-ary Relationship -> New table

ER Modeling

Relational Design and Redundancy

Redundancy is normally undesirable. Poor designs lead to:

• Information repetition (Redundancy).
• Inability to represent information.
• Insertion Anomaly: Inconsistent information inserted.
• Update Anomaly: Incorrect existing information changes.
• Deletion Anomaly: Loss of unintended data when a record is deleted.

A good design must:

• Store necessary info with minimum redundancy.
• Maintain minimum links between relations for ease of use.
• Improve query performance.
• Minimize update problems.

This is achieved through Normalization and understanding Functional Dependencies (FD).Normalization

The process of determining how much redundancy exists in a table.

Normal Forms

The primary key of a relation always functionally determines the rest of the attributes.

Intention: What we want to store (attributes). Extension: The set of values (records).

Functional Dependency (FD): A constraint {X} → {Y} where the value of X uniquely determines the value of Y. Full Functional Dependency: When no subset of {X} can determine {Y}.

Design Anomalies

Modification Anomalies: Forcing changes in all tuples containing a fact. Deletion Anomalies: Losing elementary facts unintentionally. Insertion Anomalies: Being unable to record a fact because other data is missing.

Theory of Normalization

• First Normal Form (1NF): All attributes are atomic (no repeating groups).
• Second Normal Form (2NF): Is in 1NF and every non-prime attribute is fully functionally dependent on the primary key.
• Third Normal Form (3NF): Is in 2NF and no non-prime attribute is transitively dependent on the primary key.
• Boyce-Codd Normal Form (BCNF): A stronger version of 3NF where every determinant is a candidate key.