Chapter 3 - The conceptual data model

The concepts used in an Entity-Relationship model are:

Entity set Attribute Relationship set Constraint Domain Extension Intension

An entity can be:

concrete (TEACHER or STUDENT) insubstantial (GRADE) an occurrence (EXAM)

A primary key shall be at least:

- Stable. The value of a primary key must not change or become null throughout the life of the entity. For example, consider a student record; using the age field as the primary key would not be appropriate because the value of the primary key must not change over time. - Minimal. The primary key should be composed of the minimum number of fields to ensure the occurrences are unique.

To do that, it is important to follow some steps:

1. Draw an Entity-Relationship Diagram. First create entity sets to identify attributes and to establish the proper relationship sets. 2. Define integrity constraints. Identify and document integrity constraints such as required data, referential integrity, attribute domain constraints, enterprise constraints, and entity integrity. 3. Review the final model. This requires you remove M:N relationships, remove recursive relationships, remove super types, remove relationships with attributes, and re-examine 1:1 relationships, which are normally not necessary.

Candidate key

A candidate key is an attribute or set of attributes that uniquely identifies a record in a relation

Derived attribute

A derived attribute has its value computed from another attribute or attributes. For example, the sum of all students in a faculty. A derived attribute is not a part of a table from a database, but is shown for clarity or included for design purposes even though it adds no semantic information; it also provides clues for application programmers.

Hierarchy

A hierarchy represents an ordered set of items.

Multi-valued attribute

A multi-valued attribute has many values for one entity. For example, a person has many phone numbers. Each attribute can have only one value for each instance of the entity set. When you encounter a multi-valued attribute you need to transfer it to another entity set.

Primary key

A primary key is one of the candidate keys from a relation. Every relation must have a primary key.

Data Integrity component

A set of integrity rules which guarantees the correctness of data.

Surrogate key

A surrogate key acts as a primary key but does not exist in the real world as a real attribute of an entity set.

Alternate key

An alternate key is any candidate key that is not chosen to be the primary key. It may become the primary key if the selected primary key is not appropriate.

Manipulation component

Defines the operations applied on the database (for searching or updating data in a database, or for modifying the database structure).

Partial or optional:

Each entity set may participate in a relationship; the participation is non-compulsory.

Total or mandatory:

Each entity set must participate in a relationship and it cannot exist without that participation; the participation is compulsory.

The types of database models are:

External data model Conceptual data model Internal data model

The different cardinality constraints are listed below.

Given a binary relation R between E (left entity in the figures below) and F (right entity in the figures below), R is said to be:

Unary relationship set

In this type of constraint, the same entity set participates many times in the same relationship sets. It is also known as the recursive relationship set.

A relationship set can be more formally defined as a mathematical relation on entity sets as follows.

Let the following concepts be represented by the following variables: Entity set: E Entity: e Relationship set: R Relationship: r Given a set of entities E1, E2... Ek a relation R defines a rule of correspondence between these entity sets. An instance R(E1, E2,..., Ek) of the R relation means entities E1, E2... Ek are in a relation R at this instance.

Mandatory attributes

Mandatory attributes must have a value.

Optional attributes

Optional attributes may have a value or be left null

The components of a database model are:

The components of a database model are:

The types of attributes

The types of attributes

The types of constraints

The types of constraints

Structural component

This means a set of common rules for developing a database.

Conceptual data model.

This model is used for a general view of the data and is independent of a specific DBMS. The Object-based Logical Model represents this model.

Internal data model.

This model is used for a translation of the conceptual model to a specific DBMS. The Physical Data Model represents this model.

External data model.

This model is used for viewing a representation of every user and is also called Universe of Discourse. The main model is the Records-based Logical Model.

Unique identifier

This type of attribute distinguishes one entity from another. For example, in a classroom, you can distinguish between one student and another using a student ID.

Simple (atomic) attribute

This type of attribute has a single component. For example, the Gender attribute has a single component with two values.

Single valued attribute

This type of attribute has one value for one entity. For example, the Title attribute has a single value for each teacher.

Unstable attributes

This type of attribute have values that always change. For example, the study year of a student.

Participation cardinalities (optionality).

This type of constraint specifies whether the existence of an entity set depends on being related to another entity set via the relationship set.

Subsets and supersets

When a group of instances has special properties such as attributes or relationship sets that exist only for that group, it makes sense to subdivide an entity set into subsets.

A relationship set

always exists between two entity sets (or one entity set relating to itself).

In the conceptual data model constraints

are used to handle restrictions on which attribute values and which relationships are allowed.

Composite attribute

composite attribute consists of many components. For example, the Name attribute has the components last name and first name.

A subset

consists in all attributes of the superset and takes over all relationship sets of the superset.

Data modeling

defines all the required information from the real world.

A data model

describes how data is represented and accessed. It defines data elements and relationships between them.

Attributes

determine, explain, or categorize an entity set.

Conceptual modeling

emphasizes information as seen by the business world. It identifies entities and relationships of the business.

In the Logical Model,

entities are called tuples, and entity sets are called relations. For example, you can create a relation called PERSON with two attributes: NAME and ADDRESS. In this case you write: PERSON=(NAME, ADDRESS)

You need to read a relationship set in double sense,

from one entity set to the other.

Physical modeling

implements a given logical model specifically to a particular database product and version.

Data

is a collection of letters, numbers, facts and documents that can be interpreted in many ways.

An attribute

is a data item that describes a property of an entity set.

Conceptual data model

is a mental image of a familiar physical object and are not specific to a database.

An ERD

is a representation of structured data.

A constraint

is a requirement that entity sets must satisfy in a relationship.

An entity

is a self-determining and distinguishable item within an entity set.

An entity set

is a set of entities of the same type that share the same properties.

A relationship set

is a set of relationships between two or more sets of entities, and are regularly represented using a verb.

A data model

is a tool for data abstraction, represents the organization itself and help users clearly and accurately to communicate their understanding of the organizational data

A model

is an abstraction or representation of the real world that reveals all the features of interest to the users of the information in the model.

A relationship

is an instance of a relationship set and establishes an association between entities that are related.

An entity

is an instance of an entity set.

A database model

is an integrated collection of concepts for data description, data relationships, data semantics, and data constraints.

Logical modeling

is based on a mathematical model. It presents the information and entities in a fully normalized manner where there is no duplication of data.

Data in a database at a particular point in time

is called an extension of the database.

An entity relationship diagram (ERD)

is often used to represent data requirements regardless of the type of database used, or even if a database is used at all.

Data modeling

is the first part of the database development process.

Intension or schema

is the logical model of a database and is represented by entity sets.

The objective of a conceptual database design

is to build a conceptual data model.

Usually, a database model

is used for a database schema description.

A noun

is used to represent an entity set.

A database model

is used to represent data about data, also known as metadata.

Extension

refers to the current set of tuples in a relation, it is an instance of the record sets and the relationship sets between them.

Foreign keys

these keys exist usually when there are two or more relations. An attribute from one relation has to exist in the other(s) relation. Relationship sets exist between the two attributes. A special situation is encountered when you meet a unary relationship set. In this situation, you have to use a foreign key inside the same relation.

Simple keys

these keys have a single attribute.

Composite keys

these keys have multiple attributes.

Cardinalities

they are based on the number of possible relationship sets for every entity set

At a high-level

they describe the things that an organization wants to collect data from and the relationships between these objects.

Constraints may refer

to a single attribute of an entity set, or to relationship sets between entities.

You use a model

to provide a representation of items, events and relationship sets between them, and to provide all the basic concepts and rules for good communication between developers and users.

Using subsets and supersets

you can create hierarchies.

History

you need to preserve data along databases.