Relational Database Concepts

Every good relational database solution begins with a solid design and implementation
strategy. A well-designed relational database ensures that the users and applications
that depend on the data will know that it is:

●​ Accurate. Can you rely on the accuracy of the data as new information is added
or it is modified?
●​ Easy to access. Is the data organized to make it fast, easy, and predictable to
query and maintain?
●​ Reliable. Can your database design ensure data integrity and maintain consistent
and reliable data?
●​ Flexible. Can you update or expand on the design to meet future data
requirements?

Key Concepts
●​ Data Integrity: Data integrity refers to the accuracy and consistency of data
within a database, which is maintained through constraints and validation rules.
●​ Relational Database: A relational database is a type of database that stores
data in structured formats using rows and columns, allowing for easy access and
management of data relationships.
●​ Relational Database Management Systems (RDBMS): RDBMS are software
systems that facilitate the creation, management, and manipulation of relational
databases, providing tools for data storage and retrieval.
●​ Entity Relationship Diagram (ERD): An ERD is a visual representation of the
entities within a database and their relationships, serving as a blueprint for
database design.
●​ SQL (Structured Query Language): SQL is a standardized programming
language used to manage and manipulate relational databases, allowing users to
perform tasks such as querying data and updating records.
Review of Data Fundamentals

Data Structures Overview

●​ Data is defined as unorganized information that becomes meaningful through
processing, encompassing facts, observations, numbers, and symbols.
●​ Data can be categorized into three main types: structured, unstructured, and
semi-structured, each with distinct characteristics.

Structured Data
●​ Highly organized and follows a predefined format, typically arranged in tables
with rows and columns, ensuring consistency and easy retrieval.
●​ Examples include Excel spreadsheets, SQL databases, and online forms that
collect customer information.

Unstructured Data
●​ Lacks a specific format or organization, making it challenging to process and
analyze using traditional methods.
●​ Examples include text files, media files (images, audio, video), web pages, and
social media content.

Semi-Structured Data
●​ Possesses some organizational properties but does not adhere to a strict tabular
structure, often using hierarchical structures or tags.
●​ Examples include JSON files, XML documents, and emails with structured fields
but unstructured message bodies.

Data Transfer and Storage

●​ Data can be transferred between systems in various file formats, including
delimited text files (CSV, TSV), spreadsheets, and language files (XML, JSON).
 ●​ Data repositories, such as relational and non-relational databases, are essential
for storing and managing data efficiently.

Relational vs. Non-Relational Databases

●​ Relational databases store structured data in related tables, minimizing data
duplication and ensuring transactional integrity, commonly used for OLTP
systems.
●​ Non-relational databases offer flexibility for handling diverse and unstructured
data, with examples like MongoDB and Cassandra.

Information and Data Models

Information Models
●​ Represent entities, their properties, relationships, and functions in an abstract
manner.
●​ Provide a high-level view of information, helping stakeholders understand data
structure without implementation details.

Data Models
●​ Serve as blueprints for practical database structures, detailing organization,
storage, and retrieval mechanisms.
●​ Specify data elements, structures, constraints, and relationships, often tailored to
specific database management systems (DBMS).

Differences Between Information and Data Models

●​ Information models focus on broad business concepts and relationships, while
data models emphasize technical implementation.
●​ Business analysts use information models for conceptual agreement, whereas
database designers utilize data models for system construction.
Hierarchical Model
●​ Acts as a physical implementation of an information system, structuring data in a
tree-like format.
●​ While it provides a clear representation, it has limitations regarding
many-to-many relationships, which can lead to data redundancy.

Types of Data Models

●​ Common types include the relational model, which allows for data independence
and stores data in tables, and the entity-relationship model, which conceptualizes
databases as collections of entities and their relationships.

Key Concepts in Database Management

●​ Logical data independence allows changes to the database structure without
affecting user access.
●​ Physical data independence enables internal organization tweaks without
impacting user views.
●​ Physical storage independence allows data movement on storage devices
without affecting applications.

ERDs and Types of Relationships

Understanding ERDs
●​ An ERD is a visual representation that illustrates the relationships and
interactions between entities in a database, showcasing the logical structure of
the database.
●​ Fundamental components of ERDs include entities, relationship sets, and crow's
foot notations.
Types of Relationships
●​ One-to-One Relationship: Each entity is linked to exactly one instance of another
entity.
●​ One-to-Many Relationship: An entity can be linked to multiple instances of
another entity, while each instance of the second entity is linked to only one
instance of the first.
●​ Many-to-Many Relationship: Multiple instances of one entity can be linked to
multiple instances of another entity.

Visual Representation Techniques

●​ Entities are represented as rectangles, while relationship sets are depicted as
diamonds, with lines connecting associated entities.
●​ Crow's foot notation uses symbols to indicate the nature and quantity of
relationships, helping in the design and understanding of complex databases.

Mapping Entities to Tables

Understanding ERDs
●​ An ERD is a graphical representation of entities and their relationships in a
database, used for visualizing the database structure.
●​ Key components of an ERD include entities (real-world objects), attributes
(characteristics of entities), and relationships (how entities interact).

Mapping Entities to Tables

●​ The process begins with creating an ERD, which is then mapped to tables in a
relational database.
●​ Entities become tables with the same name, and attributes are translated into
columns within those tables.
Best Practices for Relational Database Design
●​ Designate a primary key for each table to uniquely identify entries.
●​ Implement data validation to ensure data accuracy and consistency.
●​ Assign default values to streamline data entry.
●​ Utilize views for simplified data presentation and reporting.
●​ Implement concurrency control to manage simultaneous access and
modifications to the database.

Data Types

Data Types Overview

●​ Data types define the kind of data that can be stored in a database column,
ensuring consistency and accuracy.
●​ Each column in a database table should contain data of the same type, such as
text, dates, or numbers.

Varchar Data Type

●​ Varchar (variable character) can store strings of varying lengths, up to a specified
maximum, making it space-efficient.
●​ For example, Varchar(100) allows storage of up to 100 characters, using only the
space needed for the actual string length.

Common Data Types

●​ Other common data types include date, time, float, decimal, integer, binary, and
char, each serving specific purposes.
●​ Understanding these types is crucial for effective database design and data
management, as they influence how data is stored and processed.
Advantages of Using Appropriate Data Types
●​ Correctly defining data types helps prevent incorrect data entry and allows for
accurate sorting and calculations.
●​ Using the right data type enhances data integrity and optimizes storage
efficiency, which is essential for database performance.

Relational Model Concepts

Set Operations
●​ A set is defined as a collection of unique elements without a specified order, and
it can be represented using curly braces or set builder notation.
●​ Key operations include membership, subsets, union, intersection, and difference,
which help in understanding relationships between sets.

Relations and Their Properties

●​ Relations describe connections between elements of sets and include types such
as binary relations and ordered pairs.
●​ Important properties of relations include reflexivity, symmetry, transitivity, and
antisymmetry, which define how elements relate to one another.

Relational Schema and Instance

●​ A relation consists of a schema, which specifies the structure (name and type of
attributes), and an instance, which represents the actual data in rows and
columns.
●​ Key concepts include degree (number of attributes) and cardinality (number of
tuples), which are crucial for understanding the structure of a relation.

Summary and Highlights

●​ There are three main data categories. These include: structured, unstructured,
and semi-structured.
 ●​ Data repositories store and manage data centrally, including relational and
non-relational databases.
●​ Information Models provide abstract representations of entities and relationships,
whereas Data Models serve as blueprints for practical database structures.
●​ An Entity-Relationship Diagram (ERD) is a visual representation that illustrates
the relationships and interactions between entities in a database.
●​ The fundamental components that form the structure of a relationship include
entities, relationship sets, and crow’s foot notations.
●​ Varchar, an abbreviation for variable character, is a data type that stores
character strings.
●​ Sets characterized by their unordered collections include operations such as
membership, subsets, union, and intersection.
●​ Relations describe connections between set elements and consist of two
essential components: The Relation Schema and the Relation Instance.

Database Architecture

Deployment Topologies
●​ Single-Tier Architecture: All components of an application, including the user
interface, application logic, and data storage, are deployed on a single server or
machine.
●​ Client-Server Architecture (Two-Tier): This architecture divides the application
into two layers: a client layer for the user interface and a server layer for
application logic and data storage, with the database hosted on a remote server.

Three-Tier Architecture
●​ Structure: Involves three distinct layers: the presentation layer (user interface),
the business logic layer (application server), and the data layer (database
server).
 ●​ Functionality: The client application interacts with the application server, which
communicates with the database server, enhancing security, performance, and
maintainability.

Cloud-Based Deployment
●​ Advantages: Cloud deployment allows databases to reside in a cloud
environment, making them accessible from anywhere with an internet
connection, eliminating the need for local installation and maintenance.
●​ Flexibility: Suitable for various purposes, including development, testing, and
production environments, cloud databases offer scalability and ease of access
for users.

Distributed Architecture and Clustered Databases

Types of Database Architecture

●​ Shared Disk Architecture: Multiple database servers work in parallel, connecting
to shared storage. This setup allows for effective workload distribution and high
availability during server failures.
●​ Shared Nothing Architecture: Utilizes replication or partitioning to distribute
workloads across nodes, enhancing fault tolerance and resource utilization.

Techniques for Managing Data

●​ Database Replication: Involves copying changes from one server to replicas,
improving performance and ensuring high availability during server failures.
●​ Database Partitioning and Sharding: Tables with large data volumes are divided
into segments (shards) placed on separate nodes, allowing for parallel
processing and improved performance.
Database Usage Patterns

Database User Categories

●​ Data Engineers and Database Administrators (DBAs): Their primary role involves
managing databases, including establishing and supervising database objects,
implementing access controls, and monitoring performance.
●​ Data Scientists and Business Analysts: These users focus on data analysis,
deriving insights, and making data-driven predictions, often using existing data
sources.
●​ Application Developers: They create applications that require database access,
typically using programming languages and frameworks.

Tools and Interfaces Used

●​ For Data Engineers and DBAs:
○​ Graphical User Interfaces (GUIs): Commonly used for database
interaction, such as Oracle SQL Developer.
○​ Command-Line Interfaces: Essential for issuing database commands
directly, with examples including SQL scripts and interactive shells.
○​ Application Programming Interfaces (APIs): Used for automated access to
perform administrative tasks.
●​ For Data Scientists and Business Analysts:
○​ Data Analysis Tools: Tools like Jupyter, R Studio, and SPSS are frequently
used for data analysis.
○​ Business Intelligence Tools: Applications such as Microsoft PowerBI and
Tableau abstract SQL interactions for reporting and dashboarding.
●​ For Application Developers:
○​ Programming Languages: Languages like Python, Java, and C# are used
to write applications that interact with databases.
○​ Object Relational Mapping (ORM) Frameworks: Tools like Django and
Hibernate simplify database interactions by abstracting SQL complexities.
Benefits of Using Load Data Utility
●​ The load data utility in the Db2 Web Console simplifies the process of importing
data, making it quicker and more scalable compared to traditional methods.

Recap
●​ Loading data is quicker, more efficient, and more scalable than using multiple INSERT
statements
●​ You can load data from a variety of data sources, including delimited text files and Cloud
Object Storage
●​ The Load Data utility is a simple-to-use interface in the Db2 Web Console

Summary and Highlights

●​ DDL (Data Definition Language) statements, such as CREATE, ALTER, and
DROP, are used for defining, modifying, and deleting objects in a database, like
tables and their attributes.
●​ When creating tables, key considerations involve selecting a schema, defining
columns with names, datatypes, and optional values like primary key constraints,
and referencing an Entity Relationship Diagram (ERD).
●​ Methods for creating tables include using visual interfaces like the Db2 on Cloud
console, SQL statements, and administrative APIs.
●​ Post-creation actions may include generating SQL code, modifying table
structure, exploring dependencies, and dropping the table if necessary.
●​ Data manipulation involves DML (Data Manipulation Language) statements for
working with data within tables, such as inserting, updating, deleting, or querying
records.
●​ Data movement tasks include populating databases initially, adding or appending
data, making copies for development tests or disaster recovery, and ensuring
scalability.
●​ Utilities like BACKUP and RESTORE are used to create and recover copies of
entire databases, including tables, views, constraints, and their data.
 ●​ The IMPORT utility facilitates inserting data into tables from different formats like
DEL/CSV, ASC, and IXF, while the EXPORT utility saves table data into various
formats like CSV.
●​ LOAD utilities enable high-performance insertion of large volumes of data into
specified tables, offering quicker and more efficient data loading compared to
multiple INSERT statements.
●​ Loading data can be done from various sources, including delimited text files and
Cloud Object Storage, and can be managed through user-friendly interfaces like
the Load Data utility in the Db2 Web Console.

Database Objects and Hierarchy (Including

Schemas)

Hierarchical Structure of Database Objects

●​ RDBMSs organize various objects like tables, constraints, and indexes in a
hierarchical manner, which aids in managing security, maintenance, and
accessibility.
●​ An instance serves as a logical boundary for a database, allowing for the
organization of database objects and configuration parameters.

Schemas and Their Functionality

●​ A schema is a logical grouping of database objects that helps define naming
conventions and prevents ambiguous references.
●​ Schemas can include tables, views, triggers, and other objects, and they provide
a naming context to distinguish between objects with the same name.

Database Objects and Their Relationships

●​ Database objects include tables, constraints, indexes, views, and aliases, which
are essential for data storage, management, and retrieval.
 ●​ The design process involves defining these objects and their relationships, with
the option to partition large tables for improved performance in scenarios like
data warehousing.

Recap
●​ An instance is a logical and configuration boundary
●​ A relational database is a set of related objects used to store, manage, and access data
●​ A schema provides a way to logically group views, tables, triggers, nicknames,
packages, functions, and other database objects
●​ User schemas contain database objects like tables, views, functions
●​ Systems schemas contain configuration and metadata for the database
●​ You can split large tables across multiple partitions to enhance performance

Primary Keys and Foreign Keys

Primary Keys
●​ A primary key uniquely identifies each row in a table, often derived from a
naturally occurring unique attribute (e.g., book_id, employee_id).
●​ If no unique attribute exists, a new column can be added, or a combination of
columns can be used as a primary key (e.g., site_id and employee_id).

Creating Primary Keys

●​ Primary keys can be created during table creation using the PRIMARY KEY
clause in the CREATE TABLE statement.
●​ Existing tables can have primary keys added using the ADD PRIMARY KEY
clause in the ALTER TABLE statement.

Foreign Keys
●​ A foreign key is a column in one table that references the primary key in another
table, establishing a relationship between the two.
●​ For example, a copy table may reference book_id from a book table, ensuring
that each book copy corresponds to a valid book.
Creating Foreign Keys
●​ Foreign keys can be defined during table creation using the CONSTRAINT name
FOREIGN KEY clause, linking to the primary key of another table.
●​ Rules can be set for actions on updates or deletions in the parent table, such as
cascading deletes or setting foreign key values to null.

Recap
●​ You can use primary keys to enforce the uniqueness of rows in a table
●​ Foreign keys are columns in a table which contain the same information as the primary
key in another table
●​ You can use primary and foreign keys to create relationships between tables.

Overview of Indexes

Understanding Indexes
●​ An index is a data structure that allows for quick retrieval of specific rows in a
database table based on certain criteria, similar to a library catalog.
●​ Indexes improve database performance by enabling faster searches without
scanning the entire table.

Benefits of Indexing
●​ Improved performance of select queries: Indexes allow for quicker access to
rows matching search criteria.
●​ Reduced need to sort data: Indexes can maintain order, eliminating the need for
additional sorting after data retrieval.

Drawbacks of Indexing
●​ Use of disk space: Each index consumes disk space, similar to how additional
pages in a book require more physical space.
●​ Decreased performance for insert, update, and delete operations: Modifying data
in indexed tables can be slower due to the need to maintain the index structure.
Recap
●​ An index is a data structure that allows you to quickly find specific rows in a table based
on certain criteria
●​ Indexing is an essential tool for optimizing database performance
●​ An index works by storing pointers to each row in the table so that when you request a
particular row, the SQL processor can use the index to locate the row quickly
●​ You should create an index only when you stand to gain more from the advantages than
you might lose from the disadvantages.

Normalization

Normalization Overview
●​ Normalization reduces redundant data and improves data integrity by organizing
data into multiple related tables.
●​ It helps speed up transactions by allowing updates, additions, and deletions to be
performed in a single location.

First Normal Form (1NF)

●​ A table is in 1NF when each row is unique and each cell contains only a single
value.
●​ Example: In a book table, if multiple formats are listed in one cell, they should be
split into separate rows to meet 1NF requirements.

Second Normal Form (2NF)

●​ A table must be in 1NF to qualify for 2NF, which requires separating groups of
values that apply to multiple rows into new tables.
●​ Example: For a book that comes in multiple formats, the format information
should be in a separate table linked by a primary key.

Third Normal Form (3NF)

●​ A table must be in 1NF and 2NF to achieve 3NF, which involves eliminating
columns that do not depend on the primary key.
 ●​ Example: If shipping details depend on the publisher rather than the book ID,
they should be moved to a separate publisher table.

Higher Normal Forms

●​ Beyond 3NF, there are higher normal forms like Boyce-Codd Normal Form
(BCNF) and others, which may be necessary for specific scenarios.
●​ In transactional systems (OLTP), data is typically normalized to 3NF for efficient
processing, while analytical systems (OLAP) may denormalize data for
performance.

Purpose of Normalization
●​ Reduces redundant data and inconsistencies in databases.
●​ Organizes data by dividing larger tables into multiple related tables.

First Normal Form (1NF)

●​ Each row must be unique.
●​ Each cell must contain only a single value (no lists).

Second Normal Form (2NF)

●​ Must be in 1NF first.
●​ Separate groups of values that apply to multiple rows into new tables.
●​ Establish primary and foreign keys to maintain relationships.

Third Normal Form (3NF)

●​ Must be in 1NF and 2NF first.
●​ Eliminate columns that do not depend on the primary key.

Higher Normal Forms

●​ Beyond 3NF, there are Boyce-Codd Normal Form (BCNF) and others for specific
scenarios.
Application in Systems
●​ OLTP systems typically normalize data to 3NF for efficient transaction
processing.
●​ OLAP systems may denormalize data for improved read performance.

Recap
●​ Normalization reduces redundancy and increases the consistency of data
●​ In the first normal form or 1NF, each row must be unique, and each cell must contain
only a single item
●​ In the second normal form or 2NF, you must create separate tables for sets of values
that apply to multiple records
●​ In the third normal form or 3NF, eliminate any columns that do not depend on the key

Relational Model Constraints - Advanced

Comprehensive Guide to Relational Model Constraints

1.​ Introduction to Relational Model Constraints
○​ Relational databases must adhere to specific rules or constraints to
maintain data integrity.
2.​ Types of Relational Model Constraints
○​ Entity Integrity Constraint
■​ Ensures that each tuple (row) in a relation (table) is uniquely
identifiable by a primary key.
■​ The primary key must not contain null values, preventing duplicate
entries.
■​ Example: In an "author" table, the author ID serves as the primary
key, uniquely identifying each author.
○​ Referential Integrity Constraint
■​ Defines relationships between tables using foreign keys.
■​ Ensures that a foreign key in one table corresponds to a valid
primary key in another table.
 ■​ Example: A book must be linked to an existing author in the
"author" table.
○​ Semantic Integrity Constraint
■​ Enforces the correctness of the data's meaning.
■​ Prevents meaningless or incorrect values from being entered.
■​ Example: An "author" table should not have a city value like
"garbage."
○​ Domain Constraint
■​ Checks that attribute values fall within a specified set of valid
values.
■​ Example: A country attribute must contain a two-letter country code
(e.g., "CA" for Canada).
○​ Null Constraint
■​ Ensures that certain attributes cannot have null values.
■​ Example: An author must have a first and last name; these
attributes cannot be null.
○​ Check Constraint
■​ Limits the values accepted by an attribute based on specific
conditions.
■​ Example: A publication year must not exceed the current year.
3.​ Implementation of Constraints
○​ Constraints are implemented using indexes in the database.
○​ They help maintain data integrity and ensure that the data adheres to the
defined rules.

Summary

These are the different types of relational model constraints that are crucial for
maintaining data integrity in relational databases. The key constraints include:

●​ Entity Integrity: Ensures unique identification of tuples with primary keys that
cannot be null.
 ●​ Referential Integrity: Maintains valid relationships between tables using foreign
keys.
●​ Semantic Integrity: Ensures meaningful data entries.
●​ Domain Constraint: Validates that attribute values are within acceptable ranges.
●​ Null Constraint: Prevents null values in critical attributes.
●​ Check Constraint: Limits attribute values based on specific conditions.

Understanding these constraints is essential for designing and managing relational

databases effectively.

Recap
●​ Entity integrity constraint checks that the value in the primary key is a unique value
which identifies each tuple (or row)
●​ Referential integrity constraint specifies the relationships which exist between tables
●​ Semantic integrity constraint enforces the correctness of the meaning of the data
●​ Domain constraint ensures that valid values are entered for a given attribute
●​ Null constraint ensures that attribute values should not be null
●​ Check constraint limits the values that are separated by an attribute

Summary and Highlights

●​ An instance serves as both a logical and configuration boundary within a
relational database system.
●​ A relational database comprises related objects used for storing, managing, and
accessing data organized within schemas.
●​ Schemas logically group various database objects like tables, views, functions,
and triggers, with user schemas containing user-defined objects and system
schemas containing configuration and metadata.
●​ Large tables can be partitioned across multiple partitions to optimize
performance.
●​ Database objects encompass the items existing within the database, including
tables, views, and functions.
●​ Primary keys enforce the uniqueness of rows in tables, while foreign keys
establish relationships between tables.
●​ Indexes, essential for optimizing database performance, are data structures
facilitating quick retrieval of specific rows based on certain criteria.
●​ Indexing involves storing pointers to rows in a table, aiding the SQL processor in
swiftly locating requested data.
●​ Indexes should be created judiciously, balancing advantages against potential
disadvantages.
●​ Normalization, including first (1NF), second (2NF), and third (3NF) normal forms,
reduces redundancy and enhances data consistency by organizing data logically.
●​ Entity integrity constraints ensure the uniqueness of primary key values, while
referential integrity constraints specify table relationships.
●​ Semantic integrity constraints maintain the correctness of data meaning, and
domain constraints enforce valid attribute values.
●​ Null constraints mandate non-null attribute values, and check constraints restrict
accepted attribute values.