Database Management System (BCS403)

Module 2
Chapter 1: The Relational Data Model

Introduction
The relational Model of Data is based on the concept of a Relation. A Relation is a mathematical
concept based on the ideas of sets. The strength of the relational approach to data management comes from the
formal foundation provided by the theory of relations. The model was first proposed by Dr. E.F. Codd of IBM
in 1970 in the following paper: "A Relational Model for Large Shared Data Banks," Communications of the
ACM, June 1970.

2.1 Relational Model Concepts

 Relational model can represent as a table with columns and rows. Each row is known as a tuple. Each table of the
column has a name or attribute.
 When 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 is called a tuple, a column header is called an
attribute, and the table is called a relation. The data type describing the types of values that can appear
in each column is represented by a domain of possible values.


2.1.1 Structure of Relational model

Bhavya T N, [Link] CSE(DS), SVIT 1

 Database Management System (BCS403)

Domain:
A domain is the set of possible values that an attribute (column) can have. Think of it as the type of data
that can be stored in a column.

Example: For a "Student" table:

 The domain of "Age" could be positive integers (e.g., 18, 19, 20, …).
 The domain of "Name" could be a string of alphabets (e.g., "John", "Emma").

Attribute :
An attribute is a column in a table. It represents a property of an entity.
Example: Student_ID, NAME, etc.

Tuple :

A tuple is a row in a table. It is a single record that contains values for all the attributes.
Example: A row in the "Student" table:
Student_ID Name Age Course
101 John 19 Math This entire row is a tuple.
Relation :
A relation is the whole table that contains multiple tuples (rows) with the same attributes (columns).

Example : The "Student" table is a relation because it consists of multiple tuples.

Student_ID Name Age Course

101 John 19 Math
102 Emma 20 Science
103 Alex 18 English

Relation Schema:
A relation schema is like a blueprint of a table. It defines the table name and the attributes (columns) it has.

Example : For the "Student" table, the relation schema is:

Student (Student_ID, Name, Age, Course)

This tells us that the "Student" table has these four attributes.

Relation Instance or Relation State

A Relation Instance (or Relation State) refers to the actual data stored in a table at a specific moment
in time. It keeps changing whenever we add, update, or delete rows from the table.
Bhavya T N, [Link] CSE(DS), SVIT 2
 Database Management System (BCS403)

2.1.2 Characteristics of Relations

 Ordering of tuples in a relation r(R):

 Tuples ordering is not part of a relation definition because it is defined as a set of tuples.
 Many logical orders can be specified on the relation.
 There is no preference for one logical ordering over another.
 When a relation is implemented as a file, a physical ordering may be specified on the records of
the file.
 Ordering of values within each tuple:
 The tuple is an ordered list, so the ordering of values in a tuple is important.
 At a logical level, the order is not important as long as the correspondence between the attribute
and its value is mentioned.

Fig: The relation STUDENT with a different order of tuples

 Values and NULLs in the tuple:
 All values are considered atomic (indivisible).
 Multivalued attributes must be represented by separate relations.
 Composite attributes are represented only by their simple component attributes.
 A special null value is used to represent values that are unknown or inapplicable to certain
tuples.
 Interpretation (Meaning) of a Relation:
 The relational schema can be interpreted as a declaration or type of assertion.
 For example, schema of the STUDENT relation interprets that, a student entity has Name, USN,
Home_phone, Address, Office_phone, Age, Gpa.
 Alternative interpretation of a relation schema is as a predicate, that values in each tuple are
interpreted as values that satisfy the predicate.

Bhavya T N, [Link] CSE(DS), SVIT 3

 Database Management System (BCS403)

2.2 Relational model constraints and Relational Database Schemas

 Constraints are restrictions on the actual values in a database state. These constraints are derived from
the rules in the miniworld that the database represents. Constraints on databases can generally be
divided into three main categories:
 Inherent model-based or Implicit constraints:-
Constraints that is inherent in the data model.
Example: 1. Each row in a table must be unique.
2. There should be no duplicate rows.
 Schema-based or explicit constraints:-

Constraints that can be directly applied in schema of the data model using DDL. The
schema-based constraints include domain constraints, key constraints, constraints
on NULLs, entity integrity constraints, and referential integrity constraints.
Example: A Student_ID column must be unique and cannot be left blank.
 Application-based or semantic constraints or business rules:-
Constraints that cannot be directly applied in schema of the data model.
Example: 1. Total Marks cannot be more than 100.
2. Voting authority cannot be less than 18 Years

2.2.1 Domain Constraints

 Domain Constraints specify that within each tuple, the value of each attribute A must be an atomic value
from the domain dom(A).
 The data types associated with domains typically include standard numeric data types for integers (such
as short integer, integer, and long integer) and real numbers (float and double precision float).
 Characters, Booleans, fixed-length strings, and variable-length strings are also available, as are dates,
time, timestamp, and money, or other special data types.

2.2.2 Key Constraints and Constraints on NULL Values

 All tuples in a relation must also be distinct. This means that no two tuples can have the same
combination of values for all their attributes.

Bhavya T N, [Link] CSE(DS), SVIT 4

 Database Management System (BCS403)
 There are other subsets of attributes of a relation schema R with the property that no two tuples
in any relation state r of R should have the same combination of values for these attributes.
 Suppose that we denote one such subset of attributes by SK; then for any two distinct tuples t1
and t2 in a relation state r of R, we have the constraint that: t1 t2[SK] . Such set of attributes SK
is called a superkey of the relation schema R
KEY

 Super Keys : A Super Key is a set of one or more attributes that are taken collectively and
can identify all other attributes uniquely.
For Example,
(BookId) (BookId,BookName) (BookId, BookName, Author) (BookId,
Author) (BookName, Author)

 Candidate Keys : Candidate keys are a super key which are not having any redundant
attributes. In other words candidate keys are minimal super keys.
For Example,
(BookId) (BookName,Author)

 These two keys can be candidate keys, as remaining keys are having redundant attributes. Means
in super key (BookId, BookName) record can be uniquely identify by just BookId and therefore
BookName is redundant attribute

 Primary Key: A key which is used to uniquely identify each record is known as primary key.
From above Candidate keys any one can be the primary key.

2.2.3 Relational Databases and Relational Database Schema

 A relational database schema S is a set of relation schemas S = {R1, R2, ..., Rm} and a set of
integrity constraints IC.
 A relational database state DB of S is a set ofrelation states DB = {r1, r2, ..., rm} such that each ri
is a state of Ri and such that theri relation states satisfy the integrity constraints specified in IC. 

Bhavya T N, [Link] CSE(DS), SVIT 5

 Database Management System (BCS403)
Figure shows a relational database schema that we call
COMPANY ={EMPLOYEE, DEPARTMENT,DEPT_LOCATIONS,PROJECT, WORKS_ON,
DEPENDENT}. The underlined attributes represent primary keys. A database state that does
not obey all the integrity constraints iscalled an invalid state, and a state that satisfies all the
constraints in the defined setof integrity constraints IC is called a valid state.

2.2.4 Integrity, Referential Integrity, and Foreign Keys

 The entity integrity constraint states that no primary key value can be NULL. This is because the
primary key value is used to identify individual tuples in a relation. Having NULL values for the
primary key implies that we cannot identify some tuples.
For example, if two or more tuples had NULL for their primary keys, we maynot be able to
distinguish them if we try to reference them from other [Link] constraints and entity integrity
constraints are specified on individual relations.
 The referential integrity constraint is specified between two relations and is used to maintain the
consistency among tuples in the two relations. Informally, the referential integrity constraint
states that a tuple in one relation that refers to another relation must refer to an existing tuple
in that relation.

For example, The attribute Dno of EMPLOYEE gives the department number for which each
employee works; hence, its value in every EMPLOYEE tuple must match the Dnumber value of
some tuple in the DEPARTMENT relation.

Bhavya T N, [Link] CSE(DS), SVIT 6

 Database Management System (BCS403)

 The formal definition of referential integrity is, Consider a set of attributes FK in relation schema
R1 is a foreign key of R1 that references relation R2 if it satisfies the following rules:

1. The attributes in FK have the same domain(s) as the primary key attributes PK of R2; the
attributes FK are said to reference or refer to the relation R2.
2. A value of FK in a tuple t1 of the current state r1(R1) either occurs as a value of PK for
some tuple t2 in the current state r2(R2) or is NULL. According to previous case,
t1[FK] = t2[PK]. The tuple t1 references orrefers to the tuple t2.

 In this definition, R1 is called the referencing relation and R2 is the referenced relation. If these two
conditions hold, a referential integrity constraint from R1 to R2 is said to hold. In a database of many
relations, there are usually many referential integrity constraints.

For example, In the EMPLOYEE relation, the attribute Dno refers to the department for which an
employee works; hence, we designate Dno to be a foreign key of EMPLOYEE referencing the
DEPARTMENT relation. This means that a value of Dno in any tuple t1 of the EMPLOYEE relation
must match a value of the primary key of DEPARTMENT.

Bhavya T N, [Link] CSE(DS), SVIT 7

 Database Management System (BCS403)

2.3 Update Operations, Transactions, and Dealing with Constraint Violations

 The basic update operations of the relational model are:
 Insert
 Delete
 Update (or Modify)
 All integrity constraints specified on the database schema should not be violated by the update
operations.
 Several update operations may have to be grouped together.
 Updates may propagate to cause other updates automatically.
 This may be necessary to maintain integrity constraints.

The Insert Operation

 INSERT may violate any of the four types of constraints:
 Domain constraint:
If one of the attribute values provided for the new tuple is not of the specified attribute
domain.
 Key constraint:
If the value of a key attribute in the new tuple already exists in another tuple in the
relation.
 Referential integrity:
If a foreign key value in the new tuple references a primary key value that does not exist
in the referenced relation.
 Entity integrity:
If the primary key value is null in the new tuple.
 Example:-
 Operation
Insert <‘Vinod’, ’S’, ‘Joseph’, NULL, ‘1986-04-05’, ‘#123, Bangalore’, F, 28000, NULL, 4>
into EMPLOYEE
Result: Violates Entity IC , so it is rejected.

 Operation
Insert <‘Alice’, ’J’, ‘Zelaya’, ‘999887777’, ‘1986-04-05’, ‘#334, Bangalore’, F, 28000,
‘98764321’, 4> into EMPLOYEE
Result: Violates Key IC , so it is rejected.

Bhavya T N, [Link] CSE(DS), SVIT 8

 Database Management System (BCS403)
 Operation
Insert <‘Cecilia’, ’F’, ‘Kolonsky’, ‘677678989’, ‘1986-04-05’, ‘#454, Bangalore’, F, 28000,
‘99876436’, 7> into EMPLOYEE
Result: Violates Referential IC , so it is rejected

The Delete Operation

 DELETE may violate only referential integrity:
 If the primary key value of the tuple being deleted is referenced from other tuples in the database.
 Can be remedied by several actions: RESTRICT, CASCADE, SET NULL.
 RESTRICT option: reject the deletion.
 CASCADE option: propagate deletion by deleting tuples that reference the tuple that is deleted.
 SET NULL option: set the foreign keys of the referencing tuples to NULL.
 One of the above options must be specified during database design for each foreign key constraint.
 Example:
 Operation
Delete the WORKS_ON tuple with ESSN-’999887777’ and Pno=10.
Result: Acceptable. Deletes exactly one tuple
 Operation
Delete the EMPLOYEE tuple with SSN= ‘999887777’.
Result: Not Acceptable. Violates Referential IC.
 Operation
Delete the EMPLOYEE tuple with SSN= ‘333445555’.
Result: Not Acceptable. Violates Referential IC.

The Update Operation

 UPDATE may violate domain constraint and NOT NULL constraint on an attribute being modified.
 Any of the other constraints may also be violated, depending on the attribute being updated:
 Updating the primary key (PK):
 Similar to a DELETE followed by an INSERT.
 Need to specify similar options to DELETE.
 Updating a foreign key (FK):
 May violate referential integrity.
 Updating an ordinary attribute (neither PK nor FK):
 Can only violate domain constraints.

Bhavya T N, [Link] CSE(DS), SVIT 9

 Database Management System (BCS403)
 Example:
 Operation
Update the salary of an EMPLOYEE tuple with SSN =‘999887777’ to 28000.
Result: Acceptable
 Operation
Update the Dno of the EMPLOYEE tuple with SSN=‘999887777’ to 7.
Result: Violates Referential IC
 Operation
Update the SSN of the EMPLOYEE tuple with SSN=‘999887777’ to ‘987654321’.
Result: Violates Primary key constraints

2.3.1 The Transaction Concept

 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. At the end of the
transaction, it must leave the database in a valid or consistent state that satisfies all the constraints
specified on the database schema.
 For example, a transaction to apply a bank withdrawal will typically read the user account record,
check if there is a sufficient balance, and then update the record by the withdrawal amount.A large
number of commercial applications running against relational databases in online transaction
processing (OLTP) systems are executing transactions at rates that reach several hundred per
second.

Bhavya T N, [Link] CSE(DS), SVIT 10

 Database Management System (BCS403)

Module 2

Chapter 2: The Relational Algebra

2.1 Introduction:
Relational algebra is the basic set of operations for the relational model. These operations enable a user to
specify basic retrieval requests as relational algebra expressions. The result of an operation is a new relation,
which may have been formed from one or more input relations.

Importance of Relational Algebra

 Formal Foundation for the Relational Model

 Query Processing and Optimization
 Incorporation into SQL

2.2 Unary Relational Operations: SELECT and PROJECT

Unary operations are performed on a single relation (table).
2.2.1 The SELECT Operation.
 The SELECT operation is used to choose a subset of the tuples from a relation that satisfies a
selection condition.
 The SELECT operation is visualized as a horizontal partition of the relation into two sets of tuples.
Those tuples that satisfy the condition are selected, and those tuples that do not satisfy the condition
are discarded.
 In general, the SELECT operation is denoted by σ <selection condition>(R) where the symbol
σ (sigma) is used to denote the SELECT operator and the selection condition is a Boolean expression
(condition) specified on the attributes of relation R.
 The Boolean expression specified in <selection condition> is made up of a number of clauses of
the form
<attribute name><comparison op><constant value>
Or
<attribute name><comparison op><attribute name>

Examples:
1. Select the EMPLOYEE tuples whose department number is 4.

σDno=4(EMPLOYEE)
2. Select the employee tuples whose salary is greater than $30,000.

σSalary>3000(EMPLOYEE)
Bhavya T N, [Link] CSE(DS), SVIT 1
 Database Management System (BCS403)
3. 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

σ (Dno=4 AND Salary>25000) OR (Dno=5 AND Salary>30000)(EMPLOYEE)

2.2.2 The PROJECT Operation
 The SELECT operation chooses some of the rows from the table while discarding other rows. The
PROJECT operation, on the other hand, selects certain columns from the table and discards the
other columns.
 The general form of the PROJECT operation is π<attribute list>(R) where π (pi) is the symbol used
to represent the PROJECT operation, and <attribute list> is the desired sub list of attributes from the
attributes of relation R
 The PROJECT operation removes any duplicate tuples, so the result of the PROJECT operation is a
set of distinct tuples, and hence a valid relation. This is known as duplicate elimination.

Examples:
1. To list each employee’s first and last name and salary, we can use the PROJECT
operation as follows:
πLname, Fname, Salary (EMPLOYEE)
2. To list each employee’s Gender and Salary, we can use the PROJECT operation
as follows:
πSex, Salary (EMPLOYEE)

Example 1 Output Example 2 Output

2.2.3 The RENAME Operation

 The Renaming operation is used to change the name of a relation (table) or its attributes (columns) to
improve readability.
 The RENAME operation is denoted by ρ<New Relation Name>(R)

Bhavya T N, [Link] CSE(DS), SVIT 2

 Database Management System (BCS403)
 The RENAME operation is used to:
o Avoid confusion when different tables have the same column names.
o Make column names easier to understand for users.
o Keep old queries working even when table structures change.
Example:

2.3 Relational Algebra Operations from Set Theory

2.3.1 The UNION, INTERSECTION, and MINUS Operations

 UNION: The result of this operation, denoted by R ∪ S, is a relation that includes all tuples that
are either in R or in S or in both R and S. Duplicate tuples is eliminated.
 INTERSECTION: The result of this operation, denoted by R ∩ S, is a relation that includes all
tuples that are in both R and S.
 SET DIFFERENCE (or MINUS): The result of this operation, denoted by R – S, is a relation
that includes all tuples that are in R but not in S.
Example: Consider the following two relations: STUDENT & INSTRUCTOR

Bhavya T N, [Link] CSE(DS), SVIT 3

 Database Management System (BCS403)
STUDENT ∪ INSTRUCTOR STUDENT ∩ INSTRUCTOR

STUDENT – INSTRUCTOR. INSTRUCTOR – STUDENT.

2.3.2 The CARTESIAN PRODUCT (CROSS PRODUCT) Operation.

 The CARTESIAN PRODUCT operation—also known as CROSS PRODUCT or CROSS
JOIN—which is denoted by X.
 Cartesian Product produces a new element by combining every member (tuple) of one relation
(set) with every member (tuple) from the other relation (set).
 The result of R(A1, A2, ..., An) X S(B1, B2, ..., Bm) is a relation Q with degree n + m attributes
Q(A1, A2, ..., An, B1, B2, ..., Bm).
 The CARTESIAN PRODUCT creates tuples with the combined attributes of two relations. We
can SELECT related tuples from the two relations by specifying an appropriate selection condition
after the Cartesian product.

Bhavya T N, [Link] CSE(DS), SVIT 4

 Database Management System (BCS403)

2.4 Binary Relational Operations: JOIN and DIVISION

2.4.1 The JOIN Operation
 The JOIN operation, denoted by , is used to combine related tuples from two relations into
single ―longer‖ tuples.
 The general form of a JOIN operation on two relations.
R(A1, A2, ..., An) and S(B1,B2, ..., Bm) is R <join condition>S.

 The main difference between CARTESIAN PRODUCT and JOIN. In JOIN, only combinations of
tuples satisfying the join condition appear in the result, whereas in the CARTESIAN PRODUCT
all combinations of tuples are included in the result.
 Example 1: Join Operations allows you to retrieve related information from different tables by
matching values in specific columns.


 The JOIN operation on the Student table and the Department table retrieves matching values
from both tables. The retrieved result can be stored in the Stu_studyin_Dept table.

 The JOIN operation on the EMPLOYEE table and the DEPARTMENT table retrieves
matching values from both tables. The retrieved result can be stored in the DEPT_MGR table.
 Retrieved the name of the manager of each Department(means Dname, Lname, Fname) from
DEPT_MGR table.

Bhavya T N, [Link] CSE(DS), SVIT 5

 Database Management System (BCS403)
[Link] Variations of JOIN:
Inner Join Operation:
An inner join returns only the rows that have matching values in the specified columns of both
tables. It combines the rows from the tables that satisfy the join condition.
There are 3 types of inner join operation:
 Theta Join (θ) Syntax: A ⋈θ B
 Equi-Join (=) Syntax: A ⨝ [Link] B = [Link] B (B)
 Natural Join (⋈) Syntax: A ⨝ B
Example:

1. Theta Join (θ)

 A Theta Join is a join that uses a comparison operator (like <, >, <=, >=, !=) other than
just equality.
 It allows more flexible join conditions.
 Example: Find students who are older than 21
Relational Algebra Syntax:

σAge > 21(Students)

Output:

StudentID Name Age ClassName

3 Charlie 22 English

4 David 23 History

2. Equi-Join (=)
 Equi Join is a special case of Theta Join where the only comparison operator used is
"=" (equality).
 It ensures that we only get matching records.
 Example: Match students with their respective classes
Relational Algebra Syntax:

Students ⋈[Link]=[Link] Classes

Bhavya T N, [Link] CSE(DS), SVIT 6
 Database Management System (BCS403)
Output:
StudentID Name Age ClassID ClassName Teacher
1 Alice 20 101 Math Mr. Smith
2 Bob 21 102 Science Ms. Johnson
3 Charlie 22 103 English Mr. Brown

3. Natural Join (⋈)

 A Natural Join automatically joins tables based on common attribute names.
 It removes duplicate columns in the output.
 If there is no common column, it behaves like a Cartesian Product.
 Example: Automatically join students with their respective classes.
Relational Algebra Syntax:

Students ⋈ Classes
Output:
StudentID Name Age ClassID ClassName Teacher
1 Alice 20 101 Math Mr. Smith

2 Bob 21 102 Science Ms. Johnson

3 Charlie 22 103 English Mr. Brown

[Link] A Complete Set of Relational Algebra Operations

1. The Set of Relational Algebra Operations:

The fundamental operations of relational algebra include: σ , π , ∪ , ρ , − , × . These operations are
sufficient to express any other relational algebra operation.

2. Expression of INTERSECTION using UNION and MINUS:

The INTERSECTION (R ∩ S) operation can be rewritten in terms of UNION (∪) and MINUS (−):
R∩S ≡ (R ∪ S − (( R – S ) ∪ (S − R))

3. JOIN Operation as CARTESIAN PRODUCT + SELECTION:

A JOIN operation can be represented using a CARTESIAN PRODUCT (×) followed by a
SELECTION (σ):

R⋈<condition>S=σ<condition>(R×S)
Similarly, a NATURAL JOIN can be specified as a CARTESIAN PRODUCT preceded by RENAME and
followed by SELECT and PROJECT operations. Hence, the various JOIN operations are also not strictly
necessary for the expressive power of the relational algebra.

Bhavya T N, [Link] CSE(DS), SVIT 7

 Database Management System (BCS403)

2.4.2 The DIVISION Operation

 The division operator is an interesting operator that is useful in answering queries that involve
―for all‖ statements.
 The DIVISION operation, denoted by ÷, is useful for a special kind of query that sometimes
occurs in database applications.
 Syntax: R1 ÷ R2 (Tuples of R1 associated with all tuples of R2).

List of the various basic relational algebra operations:

Bhavya T N, [Link] CSE(DS), SVIT 8

 Database Management System (BCS403)

2.4.3 Notation for Query Trees

Structure of a Query Tree:

 Leaf Nodes: Represent the input relations (tables) involved in the query.

 Internal Nodes: Represent relational algebra operations (such as selection, projection, and join).

 Root Node: Represents the final query result after all operations are performed.

Execution of a Query Tree:

1. The internal nodes are executed from the bottom-up whenever their child nodes (operands) have been
processed.

2. The execution continues until the root node is processed, which gives the final query result.

Example Query (Q2):

Query: "For every project located in 'Stafford', list the project number, the controlling department
number, and the department manager‘s last name, address, and birth date."

Relational Algebra Expression:

πPnumber,Dnum,Lname,Address,Bdate((σPlocation ‗Stafford‘(PROJECT))⋈Dnum Dnumber(DEPARTMENT)⋈Mgrssn Ssn EMPLOYEE))

= = = (

 First, selection (σ) is applied to filter projects in ‗Stafford‘.

 Then, joins (⋈) are performed between PROJECT, DEPARTMENT, and EMPLOYEE relations.
 Finally, projection (π) selects the required attributes for output.

Bhavya T N, [Link] CSE(DS), SVIT 9

 Database Management System (BCS403)

2.5 Additional Relational Operations in RDBMSs (Simplified)

2.5.1 Generalized Projection

This operation extends the basic projection (π) by allowing mathematical operations on attributes.

Example: Suppose we have an EMPLOYEE table with:

 Ssn (Social Security Number)

 Salary
 Deduction
 Years of Service

A report may require calculations like:

 Net Salary = Salary - Deduction

 Bonus = 2000 * Years of Service
 Tax = 0.25 * Salary

We can use generalized projection to compute these values:

REPORT ← ρ(Ssn, Net_salary, Bonus, Tax)(πSsn, Salary – Deduction, 2000 * Years_service, 0.25 *
Salary(EMPLOYEE))

2.5.2 Aggregate Functions and Grouping

Aggregate functions summarize data using operations like SUM, AVERAGE, COUNT, MAX, MIN.

Example: We want to find:

 Number of employees in each department

 Average salary in each department

The operation:

ρR(Dno, No_of_employees, Average_sal)(Dno ℑ COUNT Ssn, AVERAGE Salary (EMPLOYEE))

2.5.3. Recursive Closure Operations

Used for queries that involve recursive relationships, such as employees and their supervisors.

Example: Find all employees supervised by ‗James Borg‘ (direct and indirect).

 Level 1 (direct supervisees):

 RESULT1 ← πSsn1(SUPERVISION Ssn2=SsnBORG_SSN)
 Level 2 (supervisees of supervisees):
 RESULT2 ← πSsn1(SUPERVISION Ssn2=SsnRESULT1)
 Combine all levels:
 RESULT ← RESULT2 ∪ RESULT1

Bhavya T N, [Link] CSE(DS), SVIT 10

 Database Management System (BCS403)

OUTER JOIN Operations

 The result includes unmatched rows of one of the tables, or of both tables.
 The matching is based on the join condition.
 Types of Outer Join:
 Left Outer Join (⟕)
 Right Outer Join (⟖)
 Full Outer Join (⟗)

Left Outer Join (⟕) Operation:

Returns all records from the left table and matching records from the right table. If there's no match,
NULL is returned for columns from the right table.
Student Course Student ⟕ Course
StuID Name CourseID StuID Course StuID Name CourseID Course
1 John 101 1 Math 1 John 101 Math
2 Alice 102 2 Science 2 Alice 102 Science
3 Bob 103 4 History 3 Bob NULL NULL

Right Outer Join (⟖) Operation:

Returns all records from the right table and matching records from the left table. If there's no match,
NULL is returned for columns from the left table.
Student Course Student ⟖ Course
StuID Name CourseID StuID Course StuID Name CourseID Course
1 John 101 1 Math 1 John 101 Math
2 Alice 102 2 Science 2 Alice 102 Science
3 Bob 103 4 History NULL NULL 103 History

Bhavya T N, [Link] CSE(DS), SVIT 11

 Database Management System (BCS403)

Full Outer Join (⟗) Operation:

Returns all records when there is a match in either left or right table. If there's no match, NULL is
returned for missing values from either table.
Student Course Student ⟗ Course
StuID Name CourseID StuID Course StuID Name CourseID Course
1 John 101 1 Math 1 John 101 Math
2 Alice 102 2 Science 2 Alice 102 Science
3 Bob 103 4 History 3 Bob NULL NULL

NULL NULL 103 History

Examples of Queries in Relational Algebra

Bhavya T N, [Link] CSE(DS), SVIT 12

 Database Management System (BCS403)

Answers:

Bhavya T N, [Link] CSE(DS), SVIT 13

 Database Management System (BCS403)

Bhavya T N, [Link] CSE(DS), SVIT 14

 Database Management System (BCS403)

Bhavya T N, [Link] CSE(DS), SVIT 15

 Database Management System (BCS403)

Relation Algebra Expressions

(a) Select employees who either work in department 4 and make over $25,000 per year, or
work in department 5 and make over $30,000

σdno=4∧ salary>25000(Emp) ∪ σdno=5∧ salary>30000(Emp)

(b) Retrieve name and salary of all male employees working in the research department

πFname,Lname,Salary(σ[Link]="Research"∧[Link]="Male"(Emp ⋈ Dept))

(c) Retrieve names of managers of each department

πFname, Lname (Emp⋈[Link]=[Link](Dept)

(d) For each project, retrieve project number, project name, and number of employees
who worked on each project

πPnumber,Pname,count(ESSN)(Project⋈Works_on) GROUP BY Pnumber

(e) Retrieve names of employees who have no dependents

R1 πFname,Lname(Emp⋈[Link]=[Link])

R2 πFname,Lname(Emp)

R3 (R2 – R1)

Bhavya T N, [Link] CSE(DS), SVIT 16

 Database Management System (BCS403)

Module 2

Chapter 3: Mapping Conceptual Design into a Logical Design

3.1 Introduction:

Mapping Conceptual Design into Logical Design is a crucial step in database development. It involves
transforming a high-level conceptual model (such as an ER diagram) into a structured logical schema that
can be implemented in a database management system (DBMS).

 Conceptual Design: Focuses on the business requirements and represents entities, attributes, and
relationships using Entity-Relationship (ER) models.
 Logical Design: Converts the conceptual model into a normalized relational schema, ready for
database implementation.

This step ensures that the database structure is accurate, efficient, and free of redundancies, making it
suitable for storage, retrieval, and management of data.

3.2 Relational Database Design using ER-to-Relational mapping

Figure: ER diagram of company database

Bhavya T N, [Link] CSE(DS), SVIT 1

 Database Management System (BCS403)

Step 1: Mapping Regular Entities

 For each entity in the ER diagram, create a table.
 Include all simple attributes as columns.
 Choose a primary key (unique identifier).
 If an attribute is composite, only include its simple components.
 Example:
• EMPLOYEE(Ssn, Name, Address, Birthdate, Salary)
• DEPARTMENT(Dnumber, Dname, Location)
• PROJECT(Pnumber, Pname, PLocation)

Step 2: Mapping Weak Entities

 A weak entity depends on another entity (owner).
 Create a table for the weak entity.
 Add a foreign key column that refers to the owner's primary key.
 The primary key of this table is a combination of the foreign key and its own unique
attribute.
 Example:
• DEPENDENT(Essn, Dependent_name, Relationship, Birthdate)
• Essn (references [Link])
• Dependent_name (partial key)

Step 3: Mapping 1:1 Relationships

There are three ways:
1. Foreign Key Approach: Add a foreign key column in one table.
Example: Add Mgr_ssn (manager's Ssn) to DEPARTMENT.
2. Merged Approach: If both entities have total participation, merge them into one table.
3. Cross-Reference Table: Create a separate table with foreign keys from both entities.

Step 4: Mapping 1:N Relationships

• The "many" side of the relationship gets a foreign key.
• Example:
• EMPLOYEE works for a DEPARTMENT → Add Dno (Department Number) to EMPLOYEE.
• PROJECT is controlled by a DEPARTMENT → Add Dnum to PROJECT.

Bhavya T N, [Link] CSE(DS), SVIT 2

 Database Management System (BCS403)

Step 5: Mapping M:N Relationships

• Create a new table to store the relationship.
• The table includes:
• Foreign keys from both entities.
• Any attributes of the relationship itself.
• Example:
• WORKS_ON(Essn, Pno, Hours)
• Essn (employee’s Ssn)
• Pno (project’s Pnumber)
• Hours (extra attribute)

Step 6: Mapping Multivalued Attributes

• If an entity has multiple values for an attribute, create a separate table.
• The primary key is a combination of the entity's key and the attribute.
• Example:
• DEPT_LOCATIONS(Dnumber, Dlocation)
• Dnumber (Department's key)
• Dlocation (location)

Step 7: Mapping N-ary Relationships

• If a relationship involves three or more entities, create a separate table.
• Include:
• Foreign keys from all participating entities.
• Any attributes of the relationship.
• Example:
• SUPPLY(Sname, Part_no, Proj_name, Quantity)
• Sname (Supplier)
• Part_no (Part)
• Proj_name (Project)

Bhavya T N, [Link] CSE(DS), SVIT 3