Database Concepts L3

Learning Objectives of the Relational Data Model

  • The relational database model provides a logical view of data, abstracting it from physical storage complexities.

  • The model's basic component is the relation.

  • Relations are logical constructs comprised of rows, known as tuples, and columns, known as attributes.

  • Relations are implemented physically as tables within a Relational Database Management System (RDBMS).

  • The study includes relational database operators, the function of the data dictionary, and the system catalog.

  • It addresses how data redundancy is managed and the importance of indexing for data retrieval efficiency.

Foundations of the Relational Model

  • The foundation of the relational model is a mathematical concept known as a relation.

  • To manage the complexity of abstract mathematical theory, a relation is viewed as a two-dimensional structure composed of intersecting rows and columns.

  • Each row in a relation is formally called a tuple.

  • Each column in a relation represents an attribute.

  • Database Example: Ch02 InsureCo.

    • Table name: AGENT (contains attributes like AGENT_CODEAGENT\_CODE, AGENT_LNAMEAGENT\_LNAME, AGENT_FNAMEAGENT\_FNAME, AGENT_INITIALAGENT\_INITIAL, AGENT_AREACODEAGENT\_AREACODE, and AGENT_PHONEAGENT\_PHONE).

    • Table name: CUSTOMER (contains attributes like CUS_CODECUS\_CODE, CUS_LNAMECUS\_LNAME, CUS_FNAMECUS\_FNAME, CUS_INITIALCUS\_INITIAL, CUS_AREACODECUS\_AREACODE, CUS_PHONECUS\_PHONE, CUS_INSURE_TYPECUS\_INSURE\_TYPE, CUS_INSURE_AMTCUS\_INSURE\_AMT, CUS_RENEW_DATECUS\_RENEW\_DATE, and AGENT_CODEAGENT\_CODE).

    • These tables are linked through the common attribute AGENT_CODEAGENT\_CODE.

Relational Database Management Systems (RDBMS)

  • The RDBMS performs basic functions similar to hierarchical and network DBMS systems.

  • It makes the relational data model much easier to understand and implemented logically.

  • One of its primary roles is to hide the inherent complexities of the relational model (and physical storage) from the end-user.

The Logical View of Data

  • The relational model enables programmers and users to view data logically rather than physically.

  • A Table (Relation) has advantages of structural and data independence.

  • From a conceptual point of view, a table resembles a file but is part of a more sophisticated structure.

  • Logical simplicity yields simple and effective database design methodologies.

  • Relationships are facilitated through logical constructs based on these relations.

Mathematical Definition of a Relation

  • The concept is modeled on a rigorous mathematical construct.

  • Formal Mathematical Definition: Given a number of sets D1,D2,…,DnD_1, D_2, \dots, D_n (which are not necessarily distinct), RR is a relation on these nn sets if it is a set of tuples, each of which has its first element from D1D_1, its second element from D2D_2, and so on until DnD_n.

  • Concrete Example:

    • Assume n=2n = 2.

    • Set 1: student's last names STU_LNAME={Bowser, Smithson, Brewer, Robertson}STU\_LNAME = \{\text{Bowser, Smithson, Brewer, Robertson}\}.

    • Set 2: department codes DEPT_CODE={BIOL, CIS, EDU}DEPT\_CODE = \{\text{BIOL, CIS, EDU}\}.

    • The relation RR defined over these sets could be: R={(Bowser, BIOL),(Smithson, CIS),(Brewer, EDU),(Robertson, EDU)}R = \{(\text{Bowser, BIOL}), (\text{Smithson, CIS}), (\text{Brewer, EDU}), (\text{Robertson, EDU})\}.

Fundamental Properties of a Relation

  1. A table is perceived as a two-dimensional structure composed of rows and columns.

  2. Each table row (tuple) represents a single entity occurrence within the entity set and must be distinct; duplicate rows are strictly prohibited.

  3. Each table column represents an attribute and must possess a distinct name.

  4. Each cell (the intersection of a row and column) must contain only an atomic value, meaning a single data value. Multiple values (multivalued attributes) are not allowed within a cell.

  5. All values in a column must conform to the same data format (e.g., if an attribute is designated as an integer, every value in that column must be an integer).

  6. Each column has a specific range of values known as the attribute domain.

  7. The order of rows and columns is irrelevant to the DBMS.

  8. Each table must have an attribute (or a combination of attributes) that uniquely identifies each row.

Attributes, Domains, and Schema

  • Attributes: Named columns within a relational table drawing values from a domain.

  • Domain: The range of permitted values for an attribute. These must be atomic and non-divisible into further components.

  • Relational Schema: A textual representation of database tables. It is written as the table name followed by the list of attributes in parentheses.

    • Formal definition: R={a1,a2,…,an}R = \{a_1, a_2, \dots, a_n\}, where a1…ana_1 \dots a_n are the set of attributes belonging to relation RR.

    • Example: Department={Dept_Name, Dept_Address, Dept_Extension}\text{Department} = \{\text{Dept\_Name, Dept\_Address, Dept\_Extension}\}.

Degree and Cardinality

  • Degree: The number of attributes (columns) in a relation.

  • Cardinality: The number of tuples (rows) in a relation.

  • The total number of attribute values in a relation is the mathematical product of its degree and its cardinality: Degree×Cardinality=Total Values\text{Degree} \times \text{Cardinality} = \text{Total Values}.

  • A relation with NN columns and NN rows is said to be of degree NN and cardinality NN.

Relational Database Keys

  • Key Concept: Consists of one or more attributes that determine other attributes based on the principle of determination. If the value of attribute AA is known, the value of attribute BB can be determined.

  • Primary Key (PK): An attribute (or combination) that uniquely identifies any given row/entity.

  • Composite Key: A key composed of more than one attribute.

  • Key Attribute: Any attribute that forms part of a key.

  • Superkey: Any key (attribute or combination) that uniquely identifies each row.

  • Candidate Key: A superkey that contains no redundancies (a minimal superkey).

  • Secondary Key: A key used strictly for data retrieval purposes (e.g., searching by phone number instead of a primary ID).

  • Foreign Key (FK): An attribute whose values match the Primary Key values in a related table, allowing for the linking of tables.

Determination Example: Student Classification

  • Hours Completed < 3030: Classification = UG1UG1

  • Hours Completed 3030 to 5959: Classification = UG2UG2

  • Hours Completed 6060 to 8989: Classification = UG3UG3

  • Hours Completed 9090 or more: Classification = PGPG

Handling Null Values

  • A Null represents the absence of a data entry.

  • Nulls are NOT permitted in a primary key (Entity Integrity).

  • Nulls should generally be avoided in other attributes.

  • A Null can represent:

    1. An unknown attribute value.

    2. A known but missing attribute value.

    3. A "not applicable" condition.

  • Problems with Nulls: They can cause errors in aggregate functions like COUNTCOUNT, AVERAGEAVERAGE, and SUMSUM, and create logical issues when linking relational tables.

Redundancy Management

  • Controlled Redundancy: This makes the relational database functional. Tables share common attributes (Foreign Keys) to enable linking.

  • Necessary Duplication: Multiple occurrences of values are not considered redundant if they are required to establish relationships Between tables.

  • Unnecessary Redundancy: Exists only when there is duplicate attribute information that does not serve to link tables, leading to data anomalies (insertion, deletion, and update anomalies).

Integrity Rules

  • Entity Integrity:

    • Requirement: All primary key entries must be unique, and no part of a primary key may be null.

    • Purpose: Ensures every row has a unique identity and allows foreign keys to reference them accurately.

    • Example: No invoice can have a duplicate number or a null number.

  • Referential Integrity:

    • Requirement: A foreign key may have a null entry (if not part of its own table's PK); if not null, the FK value must match an existing primary key value in the related table.

    • Purpose: Prevents invalid entries and ensures that relationships between tables remain consistent. It allows for the controlled deletion of records.

    • Example: A customer might not have an assigned sales rep (null allowed), but it is impossible to assign a sales rep number that does not exist.

  • Domain Integrity:

    • Requirement: Column values must fall within the acceptable range defined by the domain.

    • Purpose: Includes data type, length, null acceptance, range constraints, and default values.

    • Example: It is impossible to have negative prices (Price >0> 0).

The Data Dictionary and System Catalog

  • Data Dictionary:

    • Provides a detailed accounting of all tables in the user/designer-created database.

    • Contains metadata (data about data), including attribute names, types (CHAR, VARCHAR, etc.), formats, domains, and whether the attribute is required or acts as a PK/FK.

    • Recorded as the "database designer's database" reflecting design decisions.

  • System Catalog:

    • A detailed system-level data dictionary describing all objects within the database.

    • Can be queried like any other table.

Relationships within the Relational Database

  • 1:* (One-to-Many): The ideal relational modeling norm. Examples: One Painter paints many Paintings; one Agent serves many Customers.

  • 1:1 (One-to-One): Rare and should be used sparingly. Often suggests that two entities should be in the same table. Example: A Department is chaired by one Lecturer.

  • : (Many-to-Many): Cannot be directly implemented in a relational model. It must be converted into two 1:∗1:* relationships.

    • Conversion involves a bridge (composite) entity/table.

    • Example: Many Students take many Classes. This is resolved by an ENROLL table linking STU_NUMSTU\_NUM and CLASS_CODECLASS\_CODE.

Indexing

  • An arrangement used to logically access rows in a table without scanning the entire structure.

  • Index Key: The pointer or reference point that identifies the data location.

  • Unique Index: An index where the index key can have only one pointer value (row) associated with it.

  • Indexes are associated with a single table to speed up data retrieval.

Relational Algebra and Operators

Relational algebra is a set of mathematical principles for manipulating table contents. It features the property of "closure," where operators applied to relations produce new relations.

  • Unary Operators:

    • Select (Restrict): Yields a horizontal subset of a table (specific rows based on criteria). Example: Select price < $2.00.

    • Project: Yields a vertical subset of a table (specific columns). Example: Project P_DESCRIPTP\_DESCRIPT and PRICEPRICE.

  • Binary/Set Operators:

    • Union: Combines all rows from two union-compatible tables, excluding duplicates.

    • Union-compatible: Tables must have the same number of columns and compatible domains.

    • Intersect: Yields only rows appearing in both union-compatible tables.

    • Difference: Yields rows in the first table that are not found in the second (tables must be union-compatible).

    • Product: Yields all possible pairs of rows from two tables (RowA×RowBRow A \times Row B).

    • Join: Combines information from two or more tables intelligently.

    • Divide: Uses a 2-column table as a dividend and a 1-column table as a divisor to find associated values.

Types of Joins

  • Natural Join: Links tables by selecting rows with common values in joint columns.

  • Equijoin: Links tables based on an equality condition comparing specified columns.

  • Theta Join: An extension of the natural join using a theta subscript.

  • Inner Join: Returns only matched records from the joined tables.

  • Outer Join: Retains matched pairs and keeps unmatched values from the other table as null.

    • Left Outer Join: Yields all rows from the first (left) table and matching rows from the second.

    • Right Outer Join: Yields all rows from the second (right) table and matching rows from the first.

Questions & Discussion

  • Quick Concept Check Questions:

    1. How do you identify primary, foreign, composite, and secondary keys in a given dataset?

    2. How would you explain the three integrity rules (Entity, Referential, Domain) using specific tables?

    3. What is the specific effect of referential integrity violations?

    4. Identify which relational operator is represented by: "Display all products with price below R100" (Select), "Display name and surname" (Project), "Combine lists in Table1 and Table2" (Union), "Customers in both" (Intersect), "In Table1 but not Table2" (Difference).

  • Revision Questions:

    1. Describe relational table characteristics.

    2. Define secondary keys.

    3. Why are entity and referential integrity important?

    4. What are the requirements for union-compatibility?

    5. What is the purpose of the system catalog?