Introduction to Relational Database Model

The relational database model organizes data into tables with rows
and columns, where each row represents a record and each column
represents an attribute.
A database schema defines the structure of the database,
including table names, column names, and data types. Keys, such as
primary keys and foreign keys, establish relationships between
tables and ensure data integrity.
Relational query languages, like SQL, allow users to retrieve and
manipulate data. Relational operations, such as selection,
projection, and joins, are used to query and transform data within
the relational model.
Structure of a Relational Database:
Tables (Relations):
• The fundamental structure, consisting of rows (records or
tuples) and columns (attributes).
Rows (Records/Tuples):
• Represent individual instances of an entity (e.g., a specific
customer, product, or order).
Columns (Attributes):
• Represent the characteristics or properties of an entity (e.g.,
customer name, product price, order date).
Data Types:
• Each column has a specific data type (e.g., integer, text, date)
that defines the kind of data it can hold.
Database Schema:
• The Schema defines the structure of the
database, including the tables, columns, and
their data types.
• It acts as a blueprint for the database,
outlining how data is organized and stored.
• Schemas ensure consistency and integrity by
defining constraints on the data.
Keys:
• Primary Key: A unique identifier for each record within a
table.
• Foreign Key: A column in one table that references the
primary key of another table, establishing a relationship
between the two tables.
• Candidate Key: Any attribute or set of attributes that can
uniquely identify a record.
• Super Key: Any attribute or set of attributes that uniquely
identifies a record, including the primary key and other
candidate keys.
• Alternate Key: Any candidate key that is not the primary key.
Keys are crucial for data integrity, ensuring uniqueness and
consistency, and enabling efficient data retrieval.
Schema Diagrams:
• Visual representations of the database
schema, showing tables, columns, and
relationships between them.
• Help users understand the structure and
organization of the database.
• Often used to document and communicate
the database design.
Relational Query Languages:
• SQL (Structured Query Language): The most common
language for interacting with relational databases.
• Procedural Languages: Require users to specify the
steps needed to retrieve data.
• Declarative Languages: Allow users to specify what
data they want without specifying how to retrieve it
(e.g., SQL).
• Relational Algebra: A set of operations used to
manipulate relations (tables) and retrieve data.
Relational Operations:
• Selection: Filters rows based on a specified condition.
• Projection: Selects specific columns from a table.
• Union: Combines rows from two tables with the same structure.
• Intersection: Returns common rows from two tables with the same
structure.
• Difference: Returns rows from the first table that are not present in
the second table (same structure).
• Join: Combines rows from two or more tables based on a related
column.
• Product (Cartesian Product): Creates all possible combinations of
rows from two tables.
• Division: Used to find entities that have a relationship with all entities
in another relation (less common).
Symbols are used in relational algebra for operations like selection (σ),
projection (∏), union (∪), and Cartesian product (X).
 Database Design and ER Model
Database design using the Entity-Relationship (ER) model involves
visually representing entities, relationships, and their attributes
within a database to structure data logically and efficiently. Here's
a breakdown of the key concepts:
The ER Model:
Entities:
• These represent real-world objects or concepts like "students,"
"courses," or "departments". They are depicted as rectangles in
an ER diagram.
Attributes:
• These describe properties of an entity, such as a student's name,
ID, or major. They are represented within the entity boxes.
Relationships:
• These represent connections between entities, like "a student enrolls
in a course". They are depicted as diamonds in an ER diagram.
Cardinality:
• This specifies the number of instances of one entity that can relate to
another entity. Common cardinalities include one-to-one, one-to-
many, many-to-one, and many-to-many.
Design Process:
• Requirement Gathering: Understand the user needs and identify the
entities, attributes, and relationships involved.
• Conceptual Design: Create an initial ER diagram to visually represent
the relationships between entities.
• Normalization: Refine the ER model to eliminate redundancy and
ensure data consistency.
• Logical Design: Translate the ER diagram into a relational schema
(tables).
• Physical Design: Implement the database schema on a specific
database management system.
Constraints:
• Primary Keys: A unique identifier for each entity.
• Foreign Keys: Attributes in one entity that reference the primary
key of another entity, establishing a relationship.
• Cardinality Constraints: Define the allowed number of instances
of relationships between entities.
• Participation Constraints: Specify whether an entity is required
to participate in a relationship.
Removing Redundant Attributes:
• Redundancy occurs when the same data is stored in multiple entities,
leading to data inconsistencies. Normalization techniques are used to
eliminate redundancy by splitting entities into smaller, more
normalized tables.
Entity Relationship Diagrams (ERDs):
• ERDs are graphical representations of the ER model. They use
symbols to visualize entities, attributes, relationships and their
cardinality.
Reduction to Relational Schemas:
• Converting an ER diagram into relational schemas involves creating
tables for each entity and including appropriate attributes and
foreign keys to represent relationships.
Extended ER Features:
Specialization/Generalization:
• Allows for creating hierarchical relationships where entities
inherit attributes and properties from a more general entity
(super type).
Sub typing:
• A form of specialization where an entity inherits all
attributes and relationships from its supertype.
Weak Entities:
• Entities that depend on the existence of a related entity for
their primary key
 ER Design issues
• ER design issues in database management systems
(DBMS) revolve around how to best represent real-
world entities, their attributes, and relationships
within an ER diagram.
• Key challenges include distinguishing between
entities and attributes, deciding on binary versus n-
ary relationships, and determining the appropriate
placement of relationship attributes.
• These decisions impact the clarity, efficiency, and
accuracy of the database schema.
Here's a breakdown of the main issues:
1. Entity vs. Attribute:
• The core issue is determining whether a concept should be modeled as an
independent entity or as an attribute of another entity.
• For example, should a phone number be an attribute of an employee
entity or a separate entity related to the employee?
• This decision impacts how data is stored and retrieved, and how
relationships are defined.
2. Entity vs. Relationship:
• Another challenge is deciding whether a concept should be represented as
a relationship between entities or as a separate entity.
• For instance, should "works-on" be a relationship between an employee
and a project, or should it be an entity with attributes like "start date" and
"end date"?
• The choice depends on the complexity of the relationship and whether it
has its own attributes.
3. Binary vs. N-ary Relationships:
• N-ary relationships (involving more than two entities) can be difficult to
represent and understand.
• One solution is to break them down into multiple binary relationships or
represent them using an associative entity.
• For example, a ternary relationship (e.g., a student taking a course from a
professor) can be converted into three binary relationships (student-takes-
course, course-offered-by-professor, student-related-to-professor through
course) or a single entity "Enrollment" connecting student, course and
professor.
4. Placement of Relationship Attributes:
• When a relationship has attributes, it's crucial to determine where these
attributes should be placed.
• For one-to-many relationships, attributes are often placed on the "many" side.
• For many-to-many relationships, an associative entity is usually created to hold
the attributes.
5. Cardinality and Participation Constraints:
• Cardinality (e.g., one-to-one, one-to-many, many-to-many) and
participation (total or partial) constraints define the rules
governing relationships between entities.
• Incorrectly specifying these constraints can lead to data
inconsistencies or incomplete representations of real-world
scenarios.
6. Modeling Complex Business Rules:
• ER models may not always be sufficient to represent complex
business rules.
• Additional mechanisms (e.g., constraints, triggers) might be
needed to enforce data integrity and business logic.
7. Scalability and Maintainability:
• ER models can become complex and difficult to
maintain as the database grows.
• Modifying an existing ER diagram can be time-
consuming and require significant rework.
• By carefully considering these design issues,
database designers can create more accurate,
efficient, and maintainable ER models that
accurately represent the data requirements of an
application.
 Removing Redundant Attributes in Entity
sets
• Removing redundant attributes in entity sets is a crucial step in
database design to optimize storage, improve data consistency, and
simplify the database structure. Redundant attributes are those that
can be derived or calculated from other attributes within the entity
set or from relationships with other entities.
Why remove redundant attributes?
Reduced storage space:
• Eliminating redundant attributes frees up storage space by avoiding
the duplication of data.
Improved data consistency:
• When redundant attributes are removed, updates only need to be
made in one place, ensuring data remains consistent.
Simplified database structure:
• A database with fewer attributes is easier to understand, maintain, and
query.
How to identify and remove redundant attributes:
1. Identify potential redundancies:
• Review the entity set and its relationships with other entities. Look for
attributes that can be derived or calculated from other attributes or
relationships.
2. Determine redundancy:
• For each potential redundant attribute, check if it can be derived from other
attributes or relationships. If it can, it is redundant.
3. Remove the attribute:
• Once a redundant attribute is identified, it should be removed from the
entity set.
4. Normalize the database:
• Normalization techniques can be used to further optimize the
database structure by eliminating redundancy and improving
data integrity.
Example:
• Consider an entity set "Employee" with attributes "FirstName",
"LastName", and "FullName". "FullName" can be derived from
"FirstName" and "LastName". Therefore, "FullName" is a
redundant attribute and can be removed.

In essence, removing redundant attributes is a key step in

designing efficient, consistent, and well-structured databases.
 Entity Relationship Diagrams
• An entity-relationship diagram (ERD) is a visual representation of
how entities (people, objects, concepts) relate to each other
within a system or database. It's a type of flowchart used to
design or document databases, especially relational databases.
• ERDs use symbols like rectangles for entities, ovals for attributes,
and diamonds for relationships to illustrate the structure of data
and how it's connected.
Key Components of an ERD:
• Entities: Represent things of interest in the system, like
customers, products, or orders.
• Attributes: Describe the characteristics of an entity, such as a
customer's name, address, or phone number.
• Relationships: Show how entities are connected, like a
customer places an order.
Purpose of ERDs:
• Database Design: ERDs serve as blueprints for creating
and organizing databases.
• Data Modeling: They help visualize the logical structure
of data and how it relates to different parts of a system.
• Communication: ERDs provide a clear way to
communicate database structures to developers,
designers, and other stakeholders.
Benefits of Using ERDs:
Improved Database Design:
• ERDs help ensure that databases are well-structured and efficient.
Reduced Errors:
• By visualizing the data model, ERDs can help identify potential errors or
inconsistencies early in the design process.
Better Understanding:
• They provide a clear and concise way to understand the relationships between
different data elements.
Enhanced Communication:
• ERDs facilitate communication between different teams involved in database
development.
In essence, ERDs are a valuable tool for anyone working with databases, helping
to ensure that data is organized and related in a meaningful and efficient way.
ER Diagram Symbols
 Specialization & Generalization
• Specialization and Generalization are two
complementary processes used in database modeling
and object-oriented programming.
• Generalization is a bottom-up approach where
common attributes and behaviors of multiple entities
are combined into a more general, higher-level entity.
• Specialization is the reverse process, a top-down
approach where a general entity is divided into more
specific subclasses based on unique characteristics.
Generalization:
Definition:
• Combines similar entities into a more general class.
Purpose:
• Simplifies the database structure and reduces redundancy by identifying
commonalities.
Example:
• In a database, you might generalize "Savings Account" and "Current Account"
into a single "Account" entity.
Approach:
• Bottom-up, starting from specific entities and moving towards a more
general one.
Relationship:
• Represented by a generalization relationship in UML diagrams, where
subclasses inherit from a super class.
Specialization:
Definition: Divides a general entity into more specific
subclasses.
Purpose: Captures unique characteristics and behaviors of
different types of entities.
Example: You might specialize the "Account" entity into
"Checking Account" and "Savings Account".
Approach: Top-down, starting from a general entity and
creating more specialized subclasses.
Relationship: A subclass inherits attributes and behaviors
from its super class.