Relational Model: Predicate Logic & Set Theory

• The relational model was introduced in 1970 and is based on predicate logic and set theory.

Predicate Logic

• Predicate logic is used extensively in mathematics
• It provides a framework in which an assertion (statement of fact) can be verified as either true or false
• Example: A student with a student ID of 324452 is named Mark Reyes

Set Theory

• Set theory deals with sets, or groups of things
• It forms the basis for data manipulation in the relational model
• Example: Set A contains 15, 23, and 52 while Set B contains 41, 52, 70, 12.
• It can be concluded that the A and B sets share a common value, 52

Relational Model Components

• Logical data structure represented by relations
• Set of integrity rules ensures consistent data over time
• Set of operations defines how data is manipulated

Table

• A table (relation) is a two-dimensional structure
• It is composed of rows and columns
• Each table row (tuple) represents data about an entity
• Each table column represents an attribute, and each column has a distinct name
• Each intersection of a row and column represents a single data value
• All values in a column must conform to the same data format
• Each column has a specific range of values known as the attribute domain
• The order of the rows and columns is not important in a DBMS

Table Conclusions

• The STUDENT table is composed of six (6) rows/tuples and five (5) columns/attributes
• Each of the six (6) rows describes a student such as Martin S. Cruz
• The STU_MI values are limited to characters A to Z, the domain is [A, Z].
• STU_NUM is the primary key since it is guaranteed unique for each student
• STU_LNAME would not be a good primary key, as students can have the same last name
• Same reason applies to other attributes

Keys

• A key is an attribute or group of attributes that determines the values of other attributes
• An invoice number identifies all of the invoice attributes such as invoice date
• Determination is the state in which knowing the value of an attribute makes it possible to determine the value of another
• It is based on the relationships among the attributes

Functional Dependence

• Functional dependence means that the value of one or more attributes determines the value of one or more other attributes
• The attribute whose value determines another is called the determinant or the key
• The attribute whose value is determined by the other attribute is called the dependent
• The standard notation for representing the relationship between attributes is: ATT_A → ATT_B
• STU_NUM is the determinant and STU_LNAME is the dependent
• When given a value for STU_NUM, you can determine the value for STU_LNAME because only one 1 value of STU_LNAME is associated with any given value of STU_NUM
• Functional dependence can involve a determinant that comprises multiple attributes
• STU_NUM → (STU_LNAME, STU_FNAME, STU_MI, STU_SECT)
• A composite key is a key that is composed of more than one attribute
• An attribute that is a part of a key is called a key attribute

Types of Keys

• Superkey: An attribute or combination of attributes that uniquely identifies any row in the table. Ex. STU_NUM
• Candidate Key: A superkey without any unnecessary attributes. Ex. STU_NUM
• Primary Key: A candidate key selected to uniquely identify all other attribute values in any given row, cannot contain null entries. Ex. STU_NUM
• Foreign Key: An attribute or combination of attributes in one table whose values must either match the primary key in another table or be null
• Secondary Key: An attribute or combination of attributes used strictly for data retrieval purposes Ex. (STU_LNAME, STU_FNAME, STU_MI)

Integrity Rules

• Entity integrity condition: Each row in a table has its own unique identity
• Referential integrity conditional: Every reference to an entity instance by another entity instance is valid
• Integrity rules are followed to maintain a good database design

Entity Integrity

• Requirement: All primary key entries are unique, and no part of a primary key may be null
• Purpose: Each row will have a unique identity, and foreign key values can properly reference primary key values

Referential Integrity

• Requirement: A foreign key may have either a null entry, as long as it is not a part of its table’s primary key, or an entry that matches the primary key value in a table to which it is related
• Every non-null foreign key value must reference an existing primary key value
• Purpose: It is possible for an attribute not to have a corresponding value, but it will be impossible to have an invalid entry

STUDENTS Table

• The STUDENTS primary key (STU_NUM) column has no null entries, and all entries are unique
• Similarly, the SECTIONS table’s primary key is STU_SECT, and this primary key column is also free of null entries
• The STUDENTS table contains a foreign key, STU_SECT, that links entries in the STUDENTS table to the SECTIONS table
• The STU_CODE row identified by the (primary key) number 324257 contains a null entry in its STU_SECT foreign key because Marco Velasco does not yet have a section assigned to him
• The remaining STU_SECT entries in the STUDENTS table all match the STU_SECT entries in the SECTIONS table
• Special codes called flags are used to indicate the absence of some value

Relational Algebra

• Relational algebra is a set of mathematical principles that form the basis for manipulating relational table contents
• Using relational algebra operators on existing relations (tables) produces new relations, called closure
• The condition to be evaluated is known as a predicate

Fundamental Relational Operators

• SELECT: Retrieves a subset of rows
• PROJECT: Retrieves a subset of columns
• UNION: Merges union-compatible tables into a new table, dropping duplicate rows
• INTERSECT: Retrieves rows that are common to union-compatible tables
• DIFFERENCE: Rows from one that are not found in another
• PRODUCT: Possible pairs of rows from two tables (Cartesian Product)
• JOIN: Rows from two tables based on criteria (Ex. Rows with common values in their common attributes)
• DIVIDE: Retrieves values