Relational Database Model: Tables and Logic

Chapter 3 Study Notes

• The relational database model takes a logical view of data.
• Basic components of the relational model that are relations implemented through tables, in a relational DBMS.
• Relations are organized in tables of rows (tuples) and columns (attributes).
• Relational database operators, the data dictionary, and the system catalog are essential components.
• Data redundancy is handled in the relational database model.
• Indexing refers to the systematic arrangement of data to facilitate easy retrieval and usage. This process is crucial in various fields such as databases, libraries, and information systems, where vast amounts of information need to be organized efficiently. By creating indices, one can enhance the speed and efficiency of searching for specific data, allowing users to locate relevant information quickly. Various indexing techniques exist, such as inverted indexing, which is commonly used in search engines, and B-trees, often employed in database systems. Understanding the principles and methods of indexing is essential for effective data management and retrieval. plays a crucial role in enhancing the performance of relational databases by significantly speeding up data retrieval processes. It allows for efficient querying, sorting, and filtering, thereby improving overall database efficiency.

Logical View of Data

• The relational model enables programmers to view data logically instead of physically.
• Tables offer structural and data independence.
• Tables resemble a file from a conceptual point of view.
• Tables are easier to understand than hierarchical and network database predecessors.

Tables and Relations

• A table is a two-dimensional structure composed of rows and columns
• A table contains group of related entities, also known as an entity set.
• Terms 'entity set' and 'table' are often used interchangeably.
• Table can also be called a relation.
• Codd, the creator of the relational model, used "relation" as a synonym for table.
• Tables can be thought of as persistent relations, where contents are saved for future use.
• Columns of tables are called attributes and rows of tables are called tuples.

Properties of a Relation in a Relational Database

• A table is perceived as a two-dimensional structure composed of rows and columns.
• Each table row, also known as a tuple, represents a single entity occurrence within the entity set and must be distinct. Duplicate rows are not allowed in a relation.
• Each table column represents an attribute, with each column having a distinct name.
• Each cell, or the intersection of a column and row, should contain only one data value.
• Multiple values are not allowed in cells of a relation.
• All values in a column must conform to the same data format, e.g., if an attribute is assigned an integer data format, all values in the column representing that attribute must be integers.
• Each column has a specific range of values known as the attribute domain.
• The order of the rows and columns is immaterial to the DBMS.
• Each table must have an attribute, or a combination of attributes, that uniquely identifies each row.

Relational Schema

• A relational schema can be defined for each entity.
• A relational schema is a textual representation of database tables.
• Each table is described by its name followed by the list of its attributes in parentheses, exemplified by LECTURER(EMP_NUM, LECTURER_OFFICE, LECTURER_EXTENSION, LECTURER_HIGH_DEGREE).

Attributes and Domains

• Each attribute is a named column within the relational table and draws its values from a domain.
• A domain is the set of possible values for an attribute.
• The domain of values for an attribute should contain only atomic values and any one value should not be divisible into components.
• Only attributes with one value are allowed.
• Each domain is defined by its data type, for example, string, integer.
• Relating different entities to one another can only be achieved by comparison of their values.
• Values can only be meaningfully compared if they are drawn from the same domain.

Degree and Cardinality in the Relational Model

• Degree and cardinality are two key properties.
• A relation with N columns and N rows has a degree of N and a cardinality of N.
• The degree of a relation is the number of its attributes.
• The cardinality of a relation is the number of its tuples.
• The product of a relation's degree and cardinality is the number of attribute values it contains.

Keys

• STU_NUM determines STU_LNAME.
• STU_NUM determines STU_LNAME, STU_FNAME, and STU_INIT.
• STU_LNAME does not determine STU_NUM.
• STU_CLASS depends on Hours completed.
• Functional dependence is when the attribute B is functionally dependent on A if each value in column A determines one and only one value in column B.
• For example, STU_PHONE is functionally dependent in STU_NUM.
• STU_NUM is not functionally dependent on STU_PHONE because phone 2267 is associated with two student numbers.

Relational Database Keys

• A composite key is composed of more than one attribute.
• A key attribute is any attribute that is part of a key.
• A superkey is any key that uniquely identifies each row.
• A candidate key is a superkey without redundancies, also it is a minimal superkey.
• STU_NUM and STU_LNAME together are a superkey but not a candidate key.
• A primary key must be unique to ensure that each row is uniquely identified.
• Primary keys exhibit entity integrity and cannot contain nulls.
• Primary keys are underlined in relational schemas like: LECTURER(EMP_NUM, LECTURER_OFFICE, LECTURER_EXTENSION, LECTURER_HIGH_DEGREE).
• Nulls represent no data entry but should be avoided in attributes other than the primary key when possible.
• Nulls can represent an unknown attribute value, a known but missing attribute value, or a "not applicable" condition.
• Nulls can create problems when functions, like COUNT, AVERAGE, and SUM, are used.
• Nulls can create logical problems when relational tables are linked.
• Controlled redundancy helps make the relational database work.
• Tables within the database must share the attributes that enable them to be linked together.
• Occurrences of values in a table are not redundant when they are required to make the relationship work.
• Redundancy exists only when there is unnecessary duplication of attribute values.
• Foreign keys (FK) are attributes whose values match primary key values in the related table, they establish a referential integrity.
• Referential integrity is when a foreign key contains a value that refers to an existing valid tuple in another relation.
• Secondary keys are strictly for data retrieval and provides key as CUST_LNAME, and CUST_PHONE to return data.
• Secondary keys do not necessarily yield a unique outcome.

Integrity Rules

• Entity integrity ensures all primary key entries are unique and no part of a primary key can be null.
• Each row has a unique identity, and foreign key values can properly reference primary key values.
• Referential integrity dictates that a foreign key may have a null entry or match a primary key value in the related table. Every non-null foreign key must reference an existing primary key value.
• In the absence of a corresponding value, it's possible for an attribute to not have one; however, there cannot be an invalid entry.
• The referential integrity constraint prevents the deletion of a row in one table if its primary key has matching foreign key values in another table.
• NOT NULL constraint prevents a table entry from being saved without a value in a specific column.
• UNIQUE constraint restricts placing any duplicate values in a column

Data Dictionary

• Data dictionaries provide detailed accounting of all tables found within the user/designer-created database.
• A data dictionary contains at least all the attribute names and characteristics for each table in the system.
• Data dictionaries contain metadata, which is data about data.
• Data dictionaries are sometimes described as "the database designer's database" because they record the design decisions about tables and their structures.
• A system catalog is a detailed system data dictionary that describes all objects within the database, including table creator and creation date.
• Terms "system catalog” and “data dictionary” are often used interchangeably.
• Can be queried just like any user/designer-created table.

Relationships within the Relational Database

• 1:* relationships should be the norm in relational database design and are the ideal relationship.
• 1:1 relationships should be rare in any relational database design.
• : relationships cannot be implemented as such in the relational model.
• : relationships can be changed into two 1:* relationships.

The 1:* Relationship

• 1:* relationships are a relational database norm is found in any database environment.
• They are implemented by putting the primary key of the "1" side on the table of the "many" side as a foreign key.

The 1:1 Relationship

• With a 1:1 relationship, one entity can be related to other entity, and vice versa
• 1:1 relationships implies that entity components are not defined properly.
• It could mean that the two entites actually belong in the same table
• As rare as 1:1 relationships should be, certain conditions absolutely require their use

The : Relationship

• The *: * relationship occurs by students enrolling for courses
• : * can be implemented by breaking it up to produce a set of 1: relationships.
• *: * can avoid problems inherent to relationship by creating a composite entity or bridge entity.
• A composite entity requires *: to 1: conversion.
• The composite entity table must contain at least the primary keys of original tables.
• The linking table contains multiple occurrences of the foreign key values.
• Additional attributes may be assigned as needed.

Data Redundancy Revisited

• Data redundancy leads to data anomalies.
• Anomalies can destroy the effectiveness of the database.
• Foreign keys control data redundancies by using common attributes shared by tables.
• Foreign keys are crucial to exercising data redundancy control.
• Sometimes, data redundancy is necessary.

Indexes

• Indexes are an arrangement used to logically access rows in a table.
• An index key is the index's reference point, points to data location identified by the key, and supports Ordered arrangement of keys and pointers.
• A unique index means the index key can have only one pointer value (row) associated with it.
• Each index is associated with only one table.

Codd's Relational Database Rules

• In 1985, Codd published a list of 12 rules to define a relational database system because vendors were marketing products as relational even though those products did not meet minimum relational standards.
• Rule 0: The database must use its relational facilities exclusively to manage the database.
• Rule 1: All information is logically represented as column values in rows within tables.
• Rule 2: Every value is accessible through a combination of table name, primary key value and column name.
• Rule 3: Nulls must be treated in a systematic way, independent of data type.
• Rule 4: Metadata must be stored as ordinary data within the database and use standard database relational language.
• Rule 5: The database must support one declarative language with data definition, view definition, data manipulation, integrity constraints, authorization and transaction management.
• Rule 6: Any view that is theoretically updatable must be updatable through the system.
• Rule 7: The database must support set-level inserts, updates and deletes.
• Rule 8: Application programs and ad hoc facilities are logically unaffected when physical access methods or storage structures are changed.
• Rule 9: Application programs and ad hoc facilities are logically unaffected when changes are made to the table structures.
• Rule 10: All relational integrity constraints must be definable in the relational language and stored in the system catalog, not at the application level.
• Rule 11: The end users and application programs are unaware and unaffected by the data location.
• Rule 12: There must not be a way to bypass the integrity rules of the database.

Summary

• Relations are basic building blocks of relational database.
• Keys are central to the use of relational tables.
• Keys define functional dependencies: superkey, candidate key, primary key, secondary key, foriegn key.
• Each table row must have a primary key which uniquely identifies all attributes.
• Tables can be linked by common attributes where primary key can appear as foreign key in another table for linking.
• Good design begins by identifying appropriate entities and attributes and the relationships among the entities as relationships (1:1, 1:*, and :).