DBMS Module-2 notes for engineering BE vtu

Module-2:Relational Model,Relational
Algebra,Mapping Conceptual Design
into a Logical Design
Prepared By
Indu K S
Assistant Professor
Dept Of ISE,TOCE

Chapter 5(5.1 to 5.3)
Relational Model: Relational Model Concepts,
Relational Model Constraints and relational database schemas,
Update operations,
Transactions, and
Dealing with constraint violations.
Relational Algebra: Unary and Binary relational operations,
additional relational operations (aggregate, grouping, etc.)
Examples of Queries in relational algebra.
Mapping Conceptual Design into a Logical Design:
Relational Database Design using ER-to-Relational mapping

5.1 Relational Model Concepts
• The relational model is a way of structuring and querying data in a
database
• The relational model represents the database as a collection of
relations.
• Informally, each relation resembles a table of values or, to some
extent, a flat file of records .
• A relation is thought of as a table of values, each row in the table
represents a collection of related data values.
• A row represents a fact that typically corresponds to a real-world
entity or relationship.
• The table name and column names are used to help to interpret
the meaning of the values in each row.
• In the formal relational model terminology, a row a tuple, a
column header an attribute, and the table a relation.
• The data type describing the types of values that can appear in
each column is represented by a domain of possible values.

5.1.1 Domains, Attributes, Tuples, and Relations
• A domain D is a set of atomic values.
• By atomic means each value in the domain is indivisible in formal
relational model.
• A common method of specifying a domain is to specify a data type
from which the data values forming the domain are drawn.
Some examples of domains follow:
• USA_phone_number: string of digits of length ten
• SSN: string of digits of length nine
• Name: string of characters beginning with an uppercase letter
• GPA: a real number between 0.0 and 4.0
• Gender: a member of the set { female, male }
• Dept_Code: a member of the set { CMPS, MATH, ENGL, PHYS,
PSYC, ... }

• A relation schema R, denoted by R(A1, A2, … , An), is
made up of a relation name R and a list of attributes, A1,
A2, … , An.
• Attribute: Ai is the name of a role played by some domain D
in the relation schema R.
• D is called the domain of Ai and is denoted by dom(Ai).
• Tuple: A tuple is a mapping from attributes to values drawn
from the respective domains of those attributes.
• A tuple is intended to describe some entity (or relationship
between entities) in the miniworld.

• R is called the name of this relation.
• The degree (or arity) of a relation is the number of attributes n of
its relation schema.
• A relation of degree seven, which stores information about
university students, would contain seven attributes describing each
student as follows: STUDENT(Name, Ssn, Home_phone,
Address, Office_phone, Age, Gpa)
• Relational Database: A collection of relations, each one consistent
with its specified relational schema.
• A relation (or relation state) r of the relation schema R(A1, A2, …
, An), also denoted by r(R), is a set of n-tuples r = {t1, t2, … , tm}.
Each n-tuple t is an ordered list of n values t =<v1,v2….vn>.

5.1.2 Characteristics of Relations

5.1.3 Relational Model Notation

5.2 Relational Model Constraints and Relational Database Schemas
Relational Model Constraints on databases can generally be divided into
three main categories:
1. Constraints that are inherent in the data model known as inherent
model-based constraints or implicit constraints.
2. Constraints that can be directly expressed in the schemas of the data
model, typically by specifying them in the DDL known as schema-
based constraints or explicit constraints.
3. Constraints that cannot be directly expressed in the schemas of the
data model, and hence must be expressed and enforced by the
application programs or in some other way known as application-based
or semantic constraints or business rules.

The schema-based constraints include domain constraints, key
constraints, constraints on NULLs, entity integrity constraints, and
referential integrity constraints.

5.2.4 Entity Integrity, Referential Integrity, and Foreign Keys
Entity Integrity:
Entity Integrity ensures that each table has a primary key and that the values in this
primary key are unique and not null. This guarantees that each record (tuple) in the
table can be uniquely identified.
Primary Key: A set of one or more columns whose combined values uniquely
identify each row in the table. A table can have only one primary key, which
enforces both uniqueness and non-nullability on its columns.

A foreign key :
Is a column or a set of columns in a relational database table that
provides a link between data in two tables. It acts as a cross-
reference between tables by referencing the primary key of another
table, thereby ensuring referential integrity.

Referential Integrity
Referential Integrity ensures that a foreign key value always
refers to an existing, valid row in another table. This maintains
the logical consistency between related tables.

In the EMPLOYEE relation,
the attribute Dno refers to the
department for which an
employee works; hence, it is
designated Dno to be a
foreign key of EMPLOYEE
referencing the
DEPARTMENT relation
In this definition, EMPLOYEE is
called the referencing relation and
DEPARTMENT is the referenced
relation

5.2.5 Other Types of Constraints
The salary of an employee should not exceed the salary of the
employee’s supervisor and the maximum number of hours an
employee can work on all projects per week is 56.
Such constraints can be specified and enforced within the
application programs that update the database, or by using a
general-purpose constraint specification language. Sometimes
called as Semantic Integrity constraint.

5.3 Update Operations, Transactions, and Dealing with Constraint Violations

The Delete Operation :
The Delete operation can violate only referential integrity.
This occurs if the tuple being deleted is referenced by foreign keys from
other tuples in the database.
To specify deletion, a condition on the attributes of the relation selects
the tuple (or tuples) to be deleted

5.3.4 The Transaction Concept
A database application program running against a relational
database typically executes one or more transactions.
A transaction is an executing program that includes some
database operations, such as reading from the database, or
applying insertions, deletions, or updates to the database.

The basic set of operations for the formal relational model is the
relational algebra. These operations enable a user to specify basic
retrieval requests as relational algebra expressions.
8.1 Unary Relational Operations: SELECT and PROJECT

For example, to select the tuples for all employees who either work in
department 4 and make over $25,000 per year, or work in department
5 and make over $30,000, we can specify the following SELECT
operation:
σ(Dno=4 AND Salary>25000) OR (Dno=5 AND Salary>30000)(EMPLOYEE)

Results of a sequence of operations.
(a) πFname, Lname, Salary
(σDno=5(EMPLOYEE)).

To rename the attributes in a relation, we simply list the new attribute
names in parentheses, as in the following example:
TEMP ← σDno=5(EMPLOYEE)
R(First_name, Last_name, Salary) ← πFname, Lname, Salary(TEMP)
These two operations are illustrated in Figure 8.2(b).

8.2 Relational Algebra Operations from Set Theory
8.2.1 The UNION, INTERSECTION, and MINUS
Operations

DBMS Module-2 notes for engineering BE vtu

DBMS Module-2 notes for engineering BE vtu

DBMS Module-2 notes for engineering BE vtu

More Related Content

What's hot

Similar to DBMS Module-2 notes for engineering BE vtu

Recently uploaded

DBMS Module-2 notes for engineering BE vtu