Lecture - Relational Databases

Introduction

  • SPU is the only private university in the Pacific Northwest listed in U.S. News & World Report’s 2018 "Best National Universities".

Database Models

  • Types of Database Models include:

    • Hierarchical database model

    • Network model

    • Relational model

    • Object-oriented database model

    • Entity-relationship model

    • Star schema (OLAP)

    • Document model

    • Entity-attribute-value model

    • Object-relational model

Relational Model Terminology

  • Relation: A table with columns and rows.

    • Pertains to the logical structure, not the physical structure.

  • Attribute: A named column of a relation.

  • Domain: Set of allowable values for one or more attributes.

Additional Relational Terminology

  • Tuple: A row of a relation.

  • Degree: Number of attributes in a relation.

  • Cardinality: Number of tuples in a relation.

  • Relational Database: Collection of normalized relations with distinct relation names.

Instances of Branch and Staff Relations

  • Attributes of Branch:

    • branchNo

    • street

    • city

    • postcode

  • Staff Attributes:

    • staffNo

    • fName

    • lName

    • position

    • sex

    • DOB

    • salary

    • branchNo

Examples of Attribute Domains

  • branchNo: Branch Numbers, size 4, range B001-B999

  • street: Street Names, size 25

  • city: City Names, size 15

  • postcode: Postcodes, size 8

  • sex: Sex (M or F), size 1

  • DOB: Dates of Birth, formatted dd-mmm-yy

  • salary: Salaries, range 6000.00 - 40000.00

Alternative Terminology for the Relational Model

  • Relation: Also known as Table or File.

  • Tuple: Also known as Row or Record.

  • Attribute: Also known as Column or Field.

Mathematical Definition of Relation

  • Using sets D1 = {2, 4} and D2 = {1, 3, 5}:

    • Cartesian Product D1 × D2 = {(2, 1), (2, 3), (2, 5), (4, 1), (4, 3), (4, 5)}.

Further Mathematical Definitions

  • Any subset of the Cartesian product is a relation.

    • Example: R = {(2, 1), (4, 1)}

  • Can specify pairs in relation using conditions or functions.

Three Sets and Cartesian Products

  • For sets D1, D2, D3 = {1, 3}, {2, 4}, {5, 6}:

    • Cartesian Product D1 × D2 × D3 leads to ordered triples:

      • e.g., (1, 2, 5), (1, 2, 6).

Database Relations

  • Relation Schema: Named relation defined by attribute and domain name pairs.

  • Relational Database Schema: Set of relation schemas with distinct names.

Properties of Relations

  • Distinct relation names.

  • Each cell contains exactly one atomic value.

  • Each attribute has a distinct name, and all values come from the same domain.

  • Tuples are distinct with no duplicates.

  • Order of attributes and tuples is insignificant.

Relational Keys

  • Superkey: Attribute or set of attributes uniquely identifying a tuple.

  • Candidate Key: Minimal Superkey unable to be further reduced while preserving uniqueness.

Superkeys and Candidate Keys in Student Relation

  • Student Relation Attributes:

    • ID, first_name, last_name, birth_day, sex, phone_number.

  • Identifications of Superkeys and Candidate Keys.

Primary and Foreign Keys

  • Primary Key: The chosen Candidate Key for unique identification of tuples.

  • Alternate Keys: Candidate Keys not chosen as Primary Key.

  • Foreign Key: Attribute that matches a Candidate Key in another relation.

Key Facts about SQL Keys

  • SQL Keys identify rows in tables.

  • Super Keys can have redundant attributes.

  • Candidate Keys are required attributes for unique identification.

  • There can be multiple Super Keys, but only one Primary Key.

Integrity Constraints

  • Null Value: Represents an unknown or not applicable attribute value.

  • Distinction from zero or space values.

  • Entity Integrity: No attribute of a primary key can be null.

  • Referential Integrity: Foreign key must match a candidate key value or be null.

  • General Constraints: Additional user-defined rules on the database structure.

Views

  • Base Relation: Corresponds to entities stored in the database.

  • View: Dynamic relation based on a query from base relations.

  • Benefits: Security mechanisms, customized data access.

Updating Views

  • All updates to base relations reflect immediately on views.

  • Restrictions exist for updates involving views, mainly concerning base relations and aggregation.

Classes of Views

  • Defined as:

    • Theoretically not updatable.

    • Theoretically updatable.

    • Partially updatable.

Conclusion

  • Example of a View: Employees.current_salaries.