Relational Database Model: Keys & Attributes

Relational Database Model

• Introduced in 1970 by E. F. Codd
• Based on predicate logic and set theory
• Predicate logic verifies assertions as true or false
• Set theory deals with sets for data manipulation
• It features logical data structures represented by relations
• Includes integrity rules for data consistency
• Operations manipulate data
• Tables are two-dimensional, containing rows (tuples) and columns (attributes)
• Rows represent an entity and columns an attribute
• Attributes have a specific range of values known as the attribute domain
• The order of row and columns isn't important

Keys

• Keys are made of one or more attributes determining the values of other attributes
• Determination refers to knowing the value of one attribute through another
• Functional dependence means one or more attribute values determines another value
• A determinant comprises multiple attributes
• A determinant, such as a key attribute, determines the value of another attribute
• A dependent attribute is determined by another attribute
• Key attributes are part of a key
• Superkeys uniquely identify each row
• Candidate keys are without unnecessary attributes
• Primary keys are candidate keys that uniquely identify all other attribute values
• Foreign keys match primary keys in another table
• Secondary keys are used strictly for data retrieval

Integrity Rules

• Entity integrity means each table row has a unique identity
• Referential integrity means every instance by another entity instance is valid
• Flags, or special codes, avoid nulls
• Primary keys must be unique and not null
• Each row should have a unique identity and foreign key values should reference primary key values

Relational Algebra

• Allows one to generate useful information
• Is based on mathematical principles
• Forms the basis for manipulating relational table content
• Closure means operators on existing relations produce new relations
• A predicate is the condition to be evaluated
• SELECT (σ) retrieves a subset of rows
• PROJECT (π) retrieves a subset of columns
• UNION (∪) merges two union-compatible tables
• INTERSECT (∩) retrieves common rows from two tables
• DIFFERENCE (−) retrieves rows from one table and not another
• PRODUCT (×) retrieves the Cartesian Product of two tables
• JOIN (⨝) uses common values to retrieve rows
• DIVIDE (÷) retrieves values satisfying a division condition

Entity Relationship Modeling

• Entity Relationship Model (ERM) is a conceptual representation of entities, attributes, and relationships
• Entity Relationship Diagram (ERD) depicts an entity relationship model’s entities, attributes, and relations
• Entities are represented by rectangles
• Entity instances (or occurrences) are each row in the relational table
• Entity sets are collections of similar entities
• Relationships are associations among entities
• Relationships are represented by lines
• Connectivity is the relationship between entities
• Connectivity type is also represented by lines
• Chen Notation uses diamonds for relationships
• Crow’s Foot Notation uses lines and symbols to represent cardinality

Attributes

• Each entity consists of attributes
• In Chen Notation, attributes are ovals connected to entities
• In Crow’s Foot Notation, attributes are inside the entity rectangle

Normalization

• Primary Key (PK) and Foreign Key (FK) are marked in bold
• Unnormalized data is raw data in its original state
• Unnormalized data contains redundant, multivalued data, and/or other data anomalies
• Data anomaly is an undesirable consequence of data modification
• Normalization assigns attributes to entities, reducing data redundancies
• The aim is for each table to represents a single subject
• The aim is for each row-column intersection to contain only one value
• The aim is to avoid unnecessary data duplication
• The aim is to ensure nonprime attributes depend on the primary key
• The aim is to eliminate insertion, update, or deletion anomalies
• Normal form is each rule in database normalization
• First Normal Form (1NF) contains no repeating groups, primary key identified
• Second Normal Form (2NF) exists in 1NF, where all columns depend on the primary key
• Third Normal Form (3NF) exists in 2NF, where no non-key attributes are determined by another non-key attribute
• Boyce-Codd Normal Form (BCNF) means every determinant is a candidate key
• Fourth Normal Form (4NF) exists in 3NF with no independent multivalued dependencies
• An unnormalized table contains repeating groups
• 1NF breaks down repeating groups into atomic values
• 2NF ensures full functional dependency
• 3NF removes transitive dependencies
• The final structure has separate tables linked with foreign keys