Relational Database Model

• The relational database model takes a logical view of data.
• Relations are components in a relational model which are implemented through tables in a relational DBMS.
• Relations tables are made up rows and columns.

Relational Data Structure

• A relation is a table with columns and rows
• An attribute is a named column of a relation
• A record is a logically connected set of one or more fields that describe a person, place, or thing
• A domain is the set of allowable values for one or more attributes.

Characteristics of a Relational Table

• A relational table is perceived as a two-dimensional structure composed of rows and columns.
• Each row (tuple) represents a single entity and must be distinct.
• Each column represents an attribute and has a distinct name.
• Each cell must contain an atomic value—a single cell 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 inconsequential to the DBMS.
• Each table must have an attribute that uniquely identifies each row.

Relational Schema Terminology

• Relational schema is the textual representation of database tables.
• Each table is described by its name followed by a list of its attributes in parentheses.
• Example Schema: Student(Stud_Num, FirstName, Surname, Age)
• Cardinality of a relation refers to the tally its rows.
• Degree of a relation refers to its attributes/columns.

Keys

• Keys consist of one or more attributes that determine other attributes
• Candidate key is an attribute that determines all the other attributes in the relation; the primary key can be chosen from these.
• Candidate keys can uniquely identify database records without referring to other data. The one unique key is called the primary key, and is usually the best one to use for identification.
• Primary Key (PK) is an attribute that uniquely identifies any given row.
• Composite key is a key composed of more than one attribute.
• Foreign Key (FK) is an attribute whose values match primary key values in the related table.
• Keys' role is based on determination.
• If the value of attribute A is known, attribute B can be determined when applying determination
• Functional dependence signifies that Attribute B is functionally dependent on attribute A if each value in column A determines only one unique value in column B

Null Values

• Nulls signifies no data entry
• Must not be set in a primary key
• Should be avoided in other attributes if possible
• Represents:
  • An unknown attribute value
  • A known, but missing, attribute value
  • Or a "not applicable" condition
• Can create problems when functions such as COUNT, AVERAGE, and SUM are in operation

Controlled Redundancy

• Controlled redundancy is when tables within the database share primary/foreign keys allowing linking the tables together.
• Multiple occurrences of values in a table are not redundant when they are required to make the relationship work. Example: foreign key
• Redundancy exists only when attribute values are duplicated.
• Foreign keys avoid duplicating attributes; in that way, there's redundancy control.

Integrity

• Two types:
  • Entity integrity
  • Referential integrity

Entity Integrity

• Ensures that each row is uniquely identified by the primary key.
• Ensures that a search will find an existing row will always be successful.
• If no match is found then the record is missing in the table

Referential Integrity

• Referential integrity concerns how tables reference each other.
  • This relationship with FK
  • FK can EITHER be null or must be a valid entry in the table to which it is related
  • FK value must reference an existing primary key value
• It is possible for an attribute to NOT have a corresponding value, but it will be impossible to have an invalid entry
• Example
  • A customer may not yet have an assigned sales representative (number)
  • It will be impossible to have an invalid sales representative (number).

Data Dictionary

• A data dictionary provides a detailed summary for all tables found within the database
• Contains (at least) all the attribute names and characteristics for each table in the system.
• Data Dictionaries contain “metadata”—data about data.

System Catalog

• System catalogue is a detailed system data dictionary.
• Describes all objects within the database.
• Includes data about table names, the table's creator & creation date, the number of columns in each table, the data type of each column, index filenames, index creators and authorized users and access privileges.
• DBMS only provides a system catalogue from which the database designer can construct his/her data dictionary.

Indexes

• Indexes function as logical arrangements used to logically access rows in a table like finding a book in the library or a topic in a book.
• An index consist of an index key and a set of pointers.
• The index key is an index's reference point like a primary key.
• Pointers are identifiers, e.g. numbers that point to the data location, which is identified by the key.
• A unique index is one in which the index key can have only one pointer value (row) associated with it.

Relationships within the Relational Database

• Three types:
• 1:* relationship
• 1:1 relationship
• : relationships

One-to-Many relationship

• Relational modeling ideal.
• Should be the norm in any relational database design.

One-to- One Relationship

• Should be rare in any relational database design.

Many-to-Many Relationship

• Cannot be implemented as such in the relational model
• Can be changed into two 1:* relationships.

Codd's Relational Database Rules

• In 1985, E F Codd published a list of 12 rules to define a relational database system.
• The list existed because many vendors were marketing products as "relational" even though those products did not meet minimum relational standards.