Relational Database Model: Tables and Structure

Relational Database Model Basics

• The relational database model is a logical view of data
• The model's basic components are relations that are implemented through tables in a relational Database Management System (DBMS)
• Relations are structured in tables that are composed of rows and columns

Relational Data Structure

• A relation is a table containing columns and rows
• An attribute is a named column within a relation
• A record is a logically connected set of single or multiple fields with info on a person, place, or thing
• A domain is the set of allowable values for a number of attributes

Relational Table Characteristics

• A table is a two-dimensional structure composed of rows and columns
• Each row (tuple) represents a single entity and must be distinct
• Each table column signifies an attribute with a distinct name
• Each cell should contain an atomic 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 immaterial to a DBMS
• Each table must have an attribute that uniquely identifies each row

Relational Schema

• This is a textual representation of database tables
• Each table is described by its name and list of attributes in parentheses
• Example: Student (Stud_Num, FirstName, Surname, Age)

Cardinality and Degree

• The cardinality of a relation is the number of rows
• The degree of a relation is the number of attributes or columns

Keys in Relational Databases

• Keys consist of one or more attributes that determine other attributes
• A candidate key is an attribute determining all other attributes in the relation, and the primary key is chosen from these
• Primary key (PK) is an attribute that uniquely identifies a 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

Key Determination

• A key's role is based on determination
• If the value of attribute A is known, then you can determine the value of attribute B
• Example: A → B (Student number → Student name)
• Functional dependence is when Attribute B is functionally dependent on attribute A
• Each value in a column A determines one and only one value in column B

Nulls

• Nulls are essentially no data entry
• Nulls are not permitted in primary key attributes
• Nulls are generally avoided in other attributes
• Nulls can represent:
  • An unknown attribute value
  • A known but missing attribute value
  • A "not applicable" condition
• Nulls can create issues using the COUNT, AVERAGE, and SUM functions

Redundancy

• Tables share primary/foreign keys that enable tables to be linked together
• Multiple occurrences of values in a table are not redundant when they are required to make the relationship work.
• Redundancy exists only when attribute values are duplicated when they shouldn't
• Having a foreign key controls redundancy, and means attributes aren't unnecessarily duplicated

Integrity in a Relational Database

• Entity integrity ensures that each row is uniquely identified by the primary key
• A proper search for an existing row will always be successful
• Failure to find a match means the specified row does not exist
• Referential integrity concerns how tables reference each other
• Achieved through the foreign key (FK)
• FK can be null or be a valid entry in the table to which it is related

Data Dictionary

• This provides detailed accounting of all tables inside the database
• It includes all the attribute names and characteristics for each table in the system, which is known as metadata

System Catalogue

• This is a detailed system data dictionary:
• Describes all objects within the database
• Contains data about table names, creator, creation date, and other relevant properties
• DBMS provides a system catalogue from which the database designer can construct his/her data dictionary

Indexes

• Indexes provide a way to logically access rows in a table, like finding a book in the library or a topic in the book
• An index has an index key and a set of pointers where:
  • Index key provides a reference point
  • Pointers are identifiers that point to the data location identified by the key
• A unique index specifies each index key has only one pointer value associated with it

Relationships within a Relational Database

• 1:* relationship
• The Relational modeling ideal
• Should be the norm in any relational database design
• 1:1 relationship
• Should be rare in any relational database design
• : relationships
• Cannot be implemented as such in the relational model
• Can be changed into two 1:* relationships

Codd's Relational Database Rules

• Published a list of 12 rules in 1985 to define a relational database system
• Many vendors are marketing products as "relational" even though those products did not meet minimum relational standards
• Information - All data must be stored in tables
• Guaranteed Access - Access every value using Table + Column + Key
• Systematic treatment of nulls - Nulls must be represented systematically
• Dynamic on-line catalogue based on the relational model - The metadata must be stored and managed as ordinary data
• Comprehensive data sublanguage - The relational database must support a declarative language.
• View updating - Any view that is theoretically updatable must be updatable through the system
• High-level insert, update and delete - Database must support set-level inserts, updates and deletes
• Physical data independence - Application programs are logically unaffected by physical access methods
• Logical data independence - Application programs are logically unaffected by table structure changes that preserve original table values
• Integrity independence - Relational integrity constraints must be definable in the relational language
• Distribution independence - All relational integrity constraints must be definable in the relational language
• Non-subversion - There must not be a way to bypass the integrity rules of the database
• Rule Zero - For a database to be considered relational, it must use its relational facilities exclusively to manage the database