Relational Model Concepts

• Dr. E. F. Codd proposed the relational model for database systems in 1970.
• Relational database management systems (RDBMS) are based on the relational model.
• The relational model consists of the following:
  • Objects like domain, attribute, relation, tuple, primary key, degree, and cardinality.
  • Data integrity measures for accuracy and consistency, including entity and referential integrity.
  • Operators to act on the relations using relational algebra and relational calculus.

Relational Model Objects

• A domain is a set of atomic, indivisible values.
• A domain specifies name, data type, and format.
• Values in attributes are drawn from these domains.
• Examples of domains:
  • Gender domain: a one-character string with "M", "m", "F", or "f".
  • Name domain: a twenty-character string.
  • Credit limit domain: money in the range of $1,000 to $99,999.

Concept of NULL

• NULL is not a value.
• NULL represents that there is NO VALUE.
• Reasons for a NULL:
  • VALUE NOT APPLICABLE:
    • In an employee relation with employee number, department number, salary, and commission, commission applies only to staff in the sales department.
  • VALUE UNKNOWN:
    • An employee's salary is currently unknown.
• Other reasons for NULL:
  • VALUE DOES NOT EXIST:
    • A tax file number does not exist for an employee.
  • VALUE UNDEFINED:
    • Certain items explicitly undefined, such as division by zero.
    • If number of payments = 0, then average is undefined.

Relations

• Relation is a named set of attributes that
  • Consists of two parts: a heading and a body.
  • May map to the concept of a table.

Relation Heading

• Also called a relational schema with a fixed set of attributes.

Relation Body

• Tabular data rows representation

Relation Properties

• No duplicate tuples are allowed, as sets do not contain duplicate elements; tuples are unique.
• Tuples are unordered within a relation, by definition sets are not ordered; tuples can only be accessed by content.
• No ordering of attributes is allowed within a tuple, as sets are not ordered.
• No multivalued attributes are allowed in a relation; an entry at an intersection of each row and column is atomic (single-valued).

Logical View of Data

• Relational databases focus on logical representation rather than physical specifics.
• Using a logical tabular data representation offers advantages:
  • Structural and data independence
  • Records are stored in independent tables
  • Logical simplicity
• Allows for more effective design strategies.
• An entity is a person, place, event, or concept about which data is collected.
• Attributes are characteristics of the entity.
• Tables include related entity occurrences (an entity set) and consist of rows and columns.

Basic Building Blocks

• Entities refer to the entity set and not to a single entity occurrence (instance)
  • For example, the 'set' of all students.
• Entities correspond to a table and not to a row in the relational environment.
• Entities are represented by a rectangle containing the entity's name in both the Chen and Crow's Foot models.
• Attributes are characteristics of entities
  • Attributes include a domain which is the set of all possible values.
  • Primary keys are underlined.

Entity Relationship (ER) Models

• There are two types of Entity Relationship Model:
  • Chen Model
  • Crow's Foot Model
• In both models, an entity is represented by a rectangle containing the entity's name.
• Typically, an entity name is a noun written in capital letters in singular form e.g. STUDENT, not STUDENTS.

Types of attributes

• The Student entity may be described with the following attributes: stu_Email, stu_MiddleName, stu_FirstName, stu_DOB, stu_Phone, stu_LastName.

Simple attributes

• Cannot be subdivided.
  • Example: stu_firstname, stu_lastname.

Composite Attributes:

• Can be subdivided.
  • Example: stu_Name, which could be divided into stu_FirstName, stu_LastName, stu_MiddleName.

Required Attributes:

• Must have a value.
  • Example: stu_FirstName, stu_LastName.

Optional Attributes:

• May not have a value.
  • Example: stu_MiddleName.

Single-valued Attribute:

• Can have only one value.
  • Example: stu_LastName.

Multi-Valued Attribute:

• Can have multiple values.
  • Example: stu_Address

Key Attribute:

• Attribute or group of attributes that uniquely identify that entity (usually the primary, composite key).
  • Example: stu_EMAIL.

Non-key Attribute:

• All attributes other than key attributes.
  • Example: stu_MiddleName, stu_FirstName, stu_DOB, stu_Phone, stu_LastName.

Stored Attribute:

• Value is stored and supplied to another attribute.
  • Example: stu_DOB.

Derived Attribute

• Value is calculated or derived.
  • Example: stu_Age.

Resolving Multivalued Attribute Problems

• The conceptual model can depict multivalued attributes, but those attributes cannot be implemented directly in the relational model.
• When creating several new attributes for each component of the original multivalued attribute can cause major structural problems in the table.
• Create a new entity composed of original multivalued attribute's components.
  • The new entity is related to the original entity in a 1:M relationship.
• Office phone numbers only needs to be defined for offices that have phone numbers.
  • This is done in the PHONE_SERIALNO attribute of the new entity OFFICE_PHONE.

Key constraints

• A primary key (PK) is a subset of attributes which uniquely identify a row.
  • A primary key in one entity cannot be the same as the primary key in another entity i.e. it must be unique.
  • Primary keys for the CUSTOMER relation can be PK(Customer) = (custno, custname) or PK(Customer) = (custno)
• A relation may have more than one potential primary key, this is known as a candidate key.
  • EMPLOYEE(emp#,taxfile#)
  • Each key is a candidate key (CK) that is chosen as the primary key (PK).
• A Foreign Key is an attribute whose values match the primary key in the related table.

Types of Keys

• Primary key
  • Uniquely identifies each entity.
• Candidate key
  • Any potential primary key.
• Secondary key
  • Used only for data retrieval.
• Composite key
  • Composed of more than one key attribute.
• Key attribute
  • Any attribute that is part of a key.
• Foreign key
  • Values must match a primary key in another table.

Relationships

• Relationships are associations between entities
• Entities that are connected called participants.
• Relationships between entities always operate in both directions.

Connectivity

• Describes relationship classification
  • 1:1
  • 1:M
  • M:N

Cardinality

• Expresses number of entity occurrences associated with one occurrence of related entity.

Crow's Foot Participation Symbols

• (0,N) means "Many" side is optional.
• (1,N) means "Many" side is mandatory.
• (1,1) means "1" side is mandatory.
• (0,1) means "1" side is optional.

Relationship Participation

• Optional:
  • One entity occurrence does not require a corresponding entity occurrence in a particular relationship.
• Mandatory:
  • One entity occurrence requires a corresponding entity occurrence in a particular relationship.

Existence dependence

• An entity exists in a database only when it is associated with another related entity occurrence.
  • e.g. CUSTOMER places ORDER

Existence independence

• Entity can exist apart from one or more related entities.
• Sometimes such an entity is referred to as a strong or regular entity.
• Many (most) entities on an ER are existence dependant

Relationship Degree

• Indicates number of associated entities or participants.

Unary relationship

• Association is maintained within a single entity

Binary relationship

• Two entities are associated

Ternary relationship

• Three entities are associated

Composite Entity

• The entity composed of keys of the two related entities is known as a COMPOSITE or ASSOCIATIVE or BRIDGING ENTITY
  • associative entity is existence-dependent
• M:N is equivalent to two 1:M and M:1 relationships
  • Sometimes better to model in this manner, especially when composite is a 'real' thing or you wish to add relationships to composite

This lecture explained

• Features of the Relational Model:
  • Components of a relation
  • Properties of a relation
  • Null values
  • Relational Operators
• Relational database model takes a logical view of data and how data redundancy is dealt with.
• Described basic relational database components:
  • Entities
  • Attributes
  • Relationships amongst entities
  • Integrity constraints
  • Data Dictionary
• The relational table's components and characteristics and contrast of the table with the relation.
• How keys are used in the relational database environment:
  • Candidate keys
  • Primary keys
  • Alternate keys
  • Foreign keys
  • Secondary keys