UNIT-II

Design Concepts: Relational database model - logical view of data-keys -

Integrity rules - relational set operators - data dictionary and the system catalog
- relationships -data redundancy revisited -indexes - codd's rules. Entity
relationship model - ER diagram

RELATIONAL DATABASE MODEL:

The Relational Model organizes data using tables (relations) consisting of rows and
columns. Each column represents a specific attribute with a unique name, while each row holds
data about a real-world entity or relationship. As a record-based model, it stores data in fixed-
format records with defined attributes.
Introduced by E.F. Codd, the relational model transforms conceptual designs from ER diagrams
into implementable structures. These structures are used in relational database systems like Oracle
SQL and MySQL.
What is the Relational Model?
The relational model represents how data is stored and managed in Relational Databases.
Data is organized into tables, each known as a relation, consisting of rows (tuples) and columns
(attributes).
Each row represents an entity or record, and each column represents a particular
attribute of that entity. A relational database consists of a collection of tables each of which is
assigned a unique name.

For example, consider a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS, PHONE,
and AGE shown in the table.

Key Terms in the Relational Model

1. Attribute: Attributes are the properties that define an entity.
• Example: ROLL_NO, NAME, ADDRESS etc.
2. Relation Schema: A relation schema defines the structure of the relation and represents the
name of the relation with its attributes.
• Example: STUDENT (ROLL_NO, NAME, ADDRESS, PHONE, and AGE) is the relation schema for
STUDENT. If a schema has more than 1 relation it is called Relational Schema.
3. Tuple: A Tuple represents a row in a relation. Each tuple contains a set of attribute values that
describe a particular entity.
• Example: (1, RAM, DELHI, 9455123451, 18) is a tuple in the STUDENT table.
4. Relation Instance: The set of tuples of a relation at a particular instance of time is called a relation
instance. It can change whenever there is an insertion, deletion or update in the database.
5. Degree: The number of attributes in the relation is known as the degree of the relation.
• Example: The STUDENT relation has a degree of 5, as it has 5 attributes.
6. Cardinality: The number of tuples in a relation is known as cardinality.
• Example: The STUDENT relation defined above has cardinality 4.
7. Column: The column represents the set of values for a particular attribute.
• Example: The column ROLL_NO is extracted from the relation STUDENT.
8. NULL Values: The value which is not known or unavailable is called a NULL value. It is
represented by NULL.
• Example: PHONE of STUDENT having ROLL_NO 4 is NULL.

LOGICAL VIEW OF DATA

A logical view in database design represents how users conceptually organize and
understand data, independent of its physical storage. It focuses on the structure, relationships, and
constraints of the data as seen by users or applications, rather than the specific implementation
details. This view is crucial for data independence, allowing changes to the physical storage without
affecting the logical structure seen by users.
Here's a more detailed breakdown:
Key Concepts:
• Abstraction:
The logical view provides a layer of abstraction, hiding the physical storage details from
users. This means users interact with data through a simplified, logical representation, rather than
directly with the underlying files, storage structures, or access paths.
• Data Independence:
A significant benefit of the logical view is data independence. Changes to the physical storage (like
using a different storage medium or optimizing storage structures) can be made without impacting
the logical view. This means applications relying on the logical view don't need to be modified when
the physical implementation changes.
• Relationships:
The logical view defines how different data elements are related to each other, often through
relationships like one-to-one, one-to-many, or many-to-many. These relationships are crucial for
understanding the structure and meaning of the data.
• Constraints:
The logical view also specifies constraints on the data, such as data types, allowed values, and
uniqueness requirements. These constraints ensure data integrity and consistency.
• In essence, the logical view:
• Represents data in terms of tables, columns, and relationships.
• Hides the physical storage details.
• Enables data independence.
• Provides a consistent view of data for different users and applications.
 • Serves as a blueprint for the physical database design.

Example:
Imagine a database storing employee information. The logical view might represent this as a
table called "Employees" with columns like "EmployeeID", "Name", "Department", and
"Salary". Users can access and query this table, regardless of how the data is actually stored (e.g., in
a specific file format or storage location). The logical view would also define relationships between
the "Employees" table and other tables like "Departments" or "Projects" if they exist.

KEYS IN RELATIONAL MODEL

In the context of a relational database, keys are one of the basic requirements of a relational database
model.
• Keys are fundamental components that ensure data integrity, uniqueness and efficient access.
It is widely used to identify the tuples(rows) uniquely in the table.
• We also use keys to set up relations amongst various columns and tables of a relational
database.
Why do we require Keys in a
DBMS?
Keys are important in a Database
Management System (DBMS) for
several reasons:
• Uniqueness: Keys ensure that
each record in a table is unique and
can be identified distinctly.
• Data Integrity: Keys prevent
data duplication and maintain the
consistency of the data.
• Efficient Data Retrieval: By
defining relationships between tables, keys enable faster querying and better data
organization. Without keys, it would be extremely difficult to manage large datasets and
queries would become inefficient and prone to errors.

Types of Database Keys

1. Super Key
The set of one or more attributes (columns) that can uniquely identify a tuple (record) is known
as Super Key. It may include extra attributes that aren't important for uniqueness but still uniquely
identify the row. For Example, STUD_NO, (STUD_NO, STUD_NAME), etc.
• A super key is a group of single or multiple keys that uniquely identifies rows in a table. It
 supports NULL values in rows.
A super key can contain extra attributes that aren’t necessary for uniqueness.
•
For example, if the "STUD_NO" column can uniquely identify a student, adding "SNAME" to it
•
will still form a valid super key, though it's unnecessary.
Example: Consider the STUDENT table
STUD_NO SNAME ADDRESS PHONE

1 Shyam Delhi 123456789

2 Rakesh Kolkata 223365796

3 Suraj Delhi 175468965

A super key could be a combination of STUD_NO and PHONE, as this combination uniquely identifies
a student.

2. Candidate Key
The minimal set of attributes that can uniquely identify a tuple is known as a candidate key. For
Example, STUD_NO in STUDENT relation.
• A candidate key is a minimal super key, meaning it can uniquely identify a record but contains
no extra attributes.
• It is a super key with no repeated data is called a candidate key.
• The minimal set of attributes that can uniquely identify a record.
• A candidate key must contain unique values, ensuring that no two rows have the same value
in the candidate key’s columns.
• Every table must have at least a single candidate key.
• A table can have multiple candidate keys but only one primary key.
Example: For the STUDENT table below, STUD_NO can be a candidate key, as it uniquely identifies
each record.
STUD_NO SNAME ADDRESS PHONE

1 Shyam Delhi 123456789

2 Rakesh Kolkata 223365796

3 Suraj Delhi 175468965

 STUD_NO TEACHER_NO COURSE_NO

1 001 C001
Table: STUDENT_COURSE
2 056 C005

A composite candidate key example: {STUD_NO, COURSE_NO} can be a candidate key for a
STUDENT_COURSE table.

3. Primary Key
There can be more than one candidate key in relation out of which one can be chosen as the primary
key. For Example, STUD_NO, as well as STUD_PHONE, are candidate keys for relation STUDENT but
STUD_NO can be chosen as the primary key (only one out of many candidate keys).
• A primary key is a unique key, meaning it can uniquely identify each record (tuple) in a table.
• It must have unique values and cannot contain any duplicate values.
• A primary key cannot be NULL, as it needs to provide a valid, unique identifier for every
record.
• A primary key does not have to consist of a single column. In some cases, a composite primary
key (made of multiple columns) can be used to uniquely identify records in a table.
• Databases typically store rows ordered in memory according to primary key for fast access
of records using primary key.
Example:
STUDENT table -> Student(STUD_NO, SNAME, ADDRESS, PHONE) , STUD_NO is a primary key
Table: STUDENT
STUD_NO SNAME ADDRESS PHONE

1 Shyam Delhi 123456789

2 Rakesh Kolkata 223365796

3 Suraj Delhi 175468965

4. Alternate Key
An alternate key is any candidate key in a table that is not chosen as the primary key. In other words,
all the keys that are not selected as the primary key are considered alternate keys.
• An alternate key is also referred to as a secondary key because it can uniquely identify records
in a table, just like the primary key.
• An alternate key can consist of one or more columns (fields) that can uniquely identify a
record, but it is not the primary key
Example: In the STUDENT table, both STUD_NO and PHONE are candidate keys. If STUD_NO is
chosen as the primary key, then PHONE would be considered an alternate key.
 Primary Key,
Candidate Key, and Alternate Key
5. Foreign Key
A foreign key is an attribute in one table that refers to the primary key in another table. The table that
contains the foreign key is called the referencing table and the table that is referenced is called the
referenced table.

Relation between Primary Key and Foreign Key

• A foreign key in one table points to the primary key in another table, establishing a
relationship between them.
• It helps connect two or more tables, enabling you to create relationships between them. This
is important for maintaining data integrity and preventing data redundancy.
• They act as a cross-reference between the tables.
Example: Consider the STUDENT_COURSE table
STUD_NO TEACHER_NO COURSE_NO

1 005 C001

2 056 C005
Explanation:
• Here, STUD_NO in the STUDENT_COURSE table is a foreign key that references the STUD_NO
primary key in the STUDENT table.
• Unlike the Primary Key of any given relation, Foreign Key can be NULL as well as may contain
duplicate tuples i.e. it need not follow uniqueness constraint. For Example, STUD_NO in the
STUDENT_COURSE relation is not unique.
• It has been repeated for the first and third tuples. However, the STUD_NO in STUDENT
relation is a primary key and it needs to be always unique and it cannot be null.

6. Composite Key
Sometimes, a table might not have a single column/attribute that uniquely identifies all the records
of a table. To uniquely identify rows of a table, a combination of two or more columns/attributes can
be used. It still can give duplicate values in rare cases. So, we need to find the optimal set of attributes
that can uniquely identify rows in a table.
• It acts as a primary key if there is no primary key in a table
• Two or more attributes are used together to make a composite key .
• Different combinations of attributes may give different accuracy in terms of identifying the
rows uniquely.
Example: In the STUDENT_COURSE table, {STUD_NO, COURSE_NO} can form a composite key to
uniquely identify each record.

Different Types of Keys

INTEGRITY RULES
In DBMS systems, integrity rules, which take a prime place, are designed to ensure that the
quality of data is always high, with no inconsistencies or errors. The set of principles, also known as
the integrity rules or constraints, helps to manage the data stored in the system in the right way and
determine whether it’s suitable for certain purposes. It is not without reason that these rules are so
crucial since they are key to the overall integrity of the database and its quality. Herein, we investigate
the centrality of governance regulations, illustrate the different types that exist, and provide their
implementation in DBMS.
What are Integrity Rules?
Integrity rules normally represent the pre-existing conditions, or constraints, of data stored
in the database that will ensure the data is valid and consistent. These rules, defining permissible
values, relationships, and data operations enclosed in the database system, ensure the accuracy and
reliability of the data to be used for its purposes. Rules for integrity are considered barriers towards
the possibility of some operations over the database, such as deletion, modification, or insertion of
data whose accuracy is doubtful or inconsistent, thus enabling the database to be of high quality.
Types of Integrity Rules
• Entity Integrity: Also called business key integrity, this rule proclaims that each row in a
table must contain some unique data, which is known as the business key. Through this
verification, a primary key will be ensured of non-duplication, which also implies that no null
values are allowed in the primary key column, thus uniquely identifying each instance of an
entity in the table.
• Referential Integrity: Referential integrity guarantees the consistent pass of key reference
constraints between data through main table and foreign keys. This rule means that for every
foreign key value in a child table, that value has to possess the same primary key value in the
parent table. This is the only way to maintain foreign keys and make father child relationships
work properly.
• Domain Integrity: Verifying domain validity means finding out if the value lies within the
allowed range for a particular attribute's column in the data table. It implements data
integrity by specifying data formats, ranges, and constraints, for example,
check constraints and limitations, that ensure that all officially recognized data are kept.
• User-defined Integrity: This is an enterprise-specific set of integrity rules that would be
established by different organizations depending on their business edges and the rules
ascribed to sensitive data. These rules can be the user-defined ones like business rules or
validation criteria or can even be the custom constraints that are different from organization
to organization.
Implementation of Integrity Rules
Integrity rules are typically implemented through a combination of mechanisms within the DBMS,
including:
• Constraints: Constraints including a primary key constraint, a foreign key constraint, a
unique constraint, and a check constraint are to maintain the integrity rules at the schema
level.
• Triggers: Triggers are database elements that are activated on the fly to conduct given
activities (executed on insert, update, or delete operations, for example) on specific database
tables. Invoking triggers provides leverage to impose tougher restrictions, thus enabling the
enforcement of more advanced integrity rules or conducting validation operations, which are
a more complex kind of comparison beyond checks of constraints.
• Stored Procedures: Stored procedures are precompiled sets of SQL operations that specify
business logic and data processing operations, and they are stored in a database. They may
need to be applied to the forces of rule-following and data consistency tasks during the events
of transactions.
Significance of Integrity Rules
The rules of integrity, including the truth, credibility, and consistency, is one of the key elements in
ensuring proper functioning of the data in database. Their significance extends to various aspects of
database management, including:Their significance extends to various aspects of database
management, including:
• Data Quality: Integrity rules make sure data is precisely moderated, elaborated, and of high-
quality in the database, just like the data quality and reliability.
• Data Security: Integrity rules classify together while limiting and validating requests. They
are effective in defending against unauthorized access, data breaches, and data manipulation
attacks.
• Data Consistency: Ethical parameters help create a homogeneous database, preventing data
inconsistencies, anomalies, and errors that could arise from invalid and incorrect data
keystrokes, hence allowing for data stemming from valid data sources that are consistently
coherent and reliable both qualitatively and quantitatively.
• Compliance: Extent rules make sure all regulations, industry standards, and company
policies governing data security, privacy, and management are complied with.
 Integrity regulations constitute the fundamental codes of the DBMS that assure proper, correct,
and readable data. Through the specification and enforcement of the normal definition as well as
relationships and actions, the integrity rules preserve the data integrity and the trust in the database
systems. The correct place of integrity rules in the data industry is to take further steps in ensuring
that data is of good quality, secure, and in compliance with regulations. With the increasing demand
to employ data as an imperative strategic resource, sticking to the rules that are related to
information safety becomes a must for ensuring the trustworthiness and dependability of database
schemes.