Relational Database Concepts Quiz

Lecture 2: Introduction to Relational Model and Fundamental Relational Algebra Operators

• This lecture introduces the relational model and fundamental relational algebra operators.
• Relational databases consist of one or more relations.
• Data in an enterprise is divided into parts, each stored in a relation.
• Examples of relations include account (information about accounts), depositor (which customer owns which account), and customer (information about customers).

Big Picture: Design Phases

• Database design phases include conceptual, logical, and physical design followed by implementation.
• Application requirements drive the design process.
• Conceptual model captures application requirements, entities, relationships, and consistency constraints. The Entity-Relationship Model is commonly used for conceptual modeling.
• Logical model defines data structures and relationships to represent the entities specified in the conceptual model. It facilitates data manipulation and retrieval.
• Physical model describes the internal data storage structures and organization of the DBMS.
• A relational DBMS uses particular data structures (e.g., tables, keys, and constraints), with query languages like Structured Query Language (SQL).

Objectives and Outline

• Objectives:
  • Understand the relational database structure
  • Express queries using relational algebra
• Relational Database Model:
  • Database components (tables, columns, rows, relations, attributes, domains, tuples)
  • Data consistency constraints (Unique Tuple Identification, Super Key, Candidate Key, Primary Key, Referential Integrity, Foreign Keys).

Why Study the Relational Database Model?

• The relational model is widely used in DBMS. Examples include Oracle, MS SQL, IBM DB2, PostgreSQL, Microsoft SQL Server.
• It offers a simple yet very useful single data modeling concept (relation, aka table).
• It supports clean and powerful manipulation languages.

Basic Structure of Relational Database

• A relational database is a collection of relations (tables), each with a unique name.
• A table is a 2-dimensional structure made up of columns (fields, attributes) and rows (records, instances, tuples).
• Each attribute has a domain (set of permitted values) which must be atomic in pure relational databases.
• Each value is indivisible and simple (e.g., age, name, balance).

Example of a Relation (Table)

• A relation (table) example displays account_number, branch_name, and balance
• Data is presented in columns.

Basic Structure of Relational Database - 2

• A relation is a set of records (tuples).
• Order of records does not matter.
• Each record must be uniquely distinguishable from others within a relation.
• No duplicate tuples are permitted in a relation.

Domain of Attribute

• Domain is a set of permitted values for an attribute.
• An example uses branch_name as the domain.
• A physical level domain is a character string specifying 30 characters.

A Null Value

• A null value signifies no value or unknown data.
• It's often used if complete data is not available, to avoid overly complex design situations.

Formal Definition of Relation

• A relation is a subset of Cartesian product of domains of its attributes
• Relation elements are tuples.
• Each tuple is an instance of attribute values in each domain.

Relation Schema

• A relation schema defines the structure of a relation.
• The schema includes names for its attributes.

Relation Instance

• A relation instance represents the current content of a relation at a given time.

Relations are Unordered

• Tuples in relations do not have any inherent ordering. Ordering does not affect relational equality .

Relational Database

• Information in an enterprise is divided into relations in a relational database.
• Relations store parts of the information.

Relational Database - 2

• A single relation (table) including all information has data redundancy, making data inconsistent.
• Separating information into multiple relations resolves these issues.

A simple Relational Database example

• customer, account, and depositor tables are used as examples.

Recall the Fact about Relation

• Each tuple in a relation must be different to distinguish.
• Uniquely identifying tuples in a relation requires a key.

Different Types of Keys and their Relationships

• Superkey: set of attributes uniquely identifying a tuple in a relation.
• Candidate Keys: minimal superkeys.
• Primary Key: chosen candidate keys to aid in identifying tuples.

Superkey

• A superkey uniquely identifies a tuple in a relation within a given relation scheme.
• A set of one or more attributes uniquely identify a tuple. The set of all attributes in a relation scheme is a superkey.

Candidate Key

• A candidate key is a minimal superkey.
• All candidate keys uniquely identify tuples.

Primary Key

• The primary key is a candidate key selected for primary use; it is preferred for easy identification and may not change over time.

What are Primary Keys in this Relational Database?

• Identifying the primary keys for account, Depositor, and customer relations.
• Often, uniquely identifying values like account number provide good primary keys.

Database Constraints vs Business Logic

• Database schema specify database content constraints.
• These constraints specify acceptable attribute values in domain specifications.
• An example is restricting customer age to integers from 1 to 199.

Basic Constraints in Database

• Constraints include: Domain constraints, Key constraints, and Referential Integrity constraints (Foreign key constraints).

Key Specification (Constraint)

• Primary and candidate keys provide a constraint mechanism in relation schemas.
• Choice of primary keys in relations should consider application requirements and business logic.

Foreign Key and Referential Integrity

• A foreign key in one relation links to a primary key in another related relation.
• Relationships between relations must maintain integrity: Values used in a foreign key must already be in the corresponding primary key of a referenced relation.

Question

• Questions about relational integrity and adding tuples to the Depositor relation.

Representing Database Schema

• Diagrammatic representation of how tables (branch, account, depositor, customer, loan, borrower) in a banking system relate to each other.

Typical Steps in Defining Relational Database Schema

• Steps involved in defining a relational database schema: defining relations, attributes, and domains, selecting/defining keys (primary and candidate), specifying foreign keys and integrity constraints.

Another Simple Example

• Example relation schemas (Teacher, Course, Class, Taught-by) demonstrate how relational data is structured.
• • Questions about relation purposes, primary and foreign keys are asked.

Query Languages

• Query Languages like SQL are used to retrieve and manipulate relational data.
• They are based on formal mathematical foundations.

Basics of Algebra

• Algebra is the study of applying mathematical symbols and operators for arithmetic expressions.

Relational Algebra

• Operators are variables for relation instances (sets of tuples). -- Examples involving select, project, union, set difference, Cartesian product, and rename operators for manipulating database relations are demonstrated in the diagrams.

Additional Relational Algebra Operators

• Additional operators add convenience to relational algebra expressions, including set intersection, natural join, division, other operations for advanced query design, and modification operations (add, deletion, or modifying tuples)

In this lecture

• Relational algebra is used to demonstrate data retrieval from relational databases.

Select Operation

• The select operation is a unary operation to retrieve tuples based on given conditions (predicates). Selection predicates are formulas composed of terms connected by and, or, and not operations

Select Operation Example

• Examples demonstrating the select operation on loan and account relations to retrieve data matching certain criteria.

Project Operation

• A unary operator that produces a new relation by selecting certain attributes to construct a subset of attributes for the relation(s)

Project Operation Examples

• Examples demonstrating the project operation on different relations (tables), showing how only certain columns of the relations are extracted.

Union Operation

• The union operation combines tuples from two relations (tables).
• The two input relations must have the same number of attributes, and the attributes should have compatible domains for valid union operations

Union Operation Examples

• Examples showing how two related tables (depositor & borrower) can be processed with union. Resulting relations give full list of customers with various deposit or loan accounts.

Set Difference Operation

• The set difference operation returns tuples from the first relation (r) that are not in the second relation.
• For this to be valid operation, the two relations need the same attributes and comparable data types between attributes.

Set Difference Operation Examples

• Examples demonstrating set difference processing two relations (tables).

Cartesian-Product Operation

• The Cartesian product operation creates a new relation from two input relations by combining all possible ordered pairs of tuples from each input relation. Distinct relational schemas and attributes typically are used.

Cartesian-Product Operation Examples

• Examples demonstrating Cartesian product processes two relations to show combined results.

Rename Operation

• Relational algebra operation to rename relations or attributes for clarity or readability while expressing complex operations.
• Renaming allows specifying a single relationship in multiple forms in the schema.

Rename Operation Examples

• Examples showing rename application on different relations

Example Queries (Banking)

• Example queries in relational algebra that retrieve specific information from relations in a banking database.
• Strategies for finding desired results/ data using the relational algebra approach using appropriate operators as demonstrated.

Classroom Exercise

• Exercise questions covering various concepts and query design.

Additional Exercises

• More complex exercises for practicing relational algebra query design.

ToDo Task

• The ToDo task assigns the completion of worksheet 2 as a group work assignment.
• The task specifies a due date, method of submission, and group submission requirements.

Test your knowledge about relational databases with this quiz that covers key concepts such as superkeys, candidate keys, and tuples. Learn more about the fundamental operations and relations used in database management. Perfect for students studying database systems!