MATRUSRI ENGINEERING COLLEGE�DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

SUBJECT NAME: DataBase Management Systems

FACULTY NAME: K Sunil Manohar Reddy

Insert Your Photo here🡪

MATRUSRI

ENGINEERING COLLEGE

DATABASE MANAGEMENT SYSTEMS

COURSE OBJECTIVES:

• To Learn mathematical concepts as applied in computer
• To introduce three scheme architecture and DBMS functional components.
• To learn formal and commercial query languages of RDBMS
• To Study different file organization and indexing techniques
• To familiarize theory of serializablity and implementation of concurrency control, and recovery

COURSE OUTCOMES:

​

• Understand three schema architecture and DBMS functional concepts, model E-R Diagrams
• Learn formal and commercial query languages
• Develop Database applications using SQL and Embedded SQL and understand Database Design
• Gain the knowledge of different file organization
• Familiarize theory of serializability and Understand the working of concurrency control and recovery mechanisms.

MATRUSRI

ENGINEERING COLLEGE

INTRODUCTION: ��THIS UNIT DEALS WITH THE RELATIONAL MODEL AND STORING, ACCESSING AND MANAGING DATA USING VARIOUS FORMAL AND COMMERCIAL QUERY LANGUAGES.

UNIT-II

OUTCOMES:

​

Upon completion of this unit, student will be able to:

• Describe about concepts of Relational model
• Write basic SQL queries

MATRUSRI

ENGINEERING COLLEGE

CONTENTS:�RELATIONAL MODEL: STRUCTURE OF RELATIONAL DATABASES, FUNDAMENTAL RELATIONAL-ALGEBRA OPERATIONS, ADDITIONAL RELATIONAL-ALGEBRA OPERATIONS, EXTENDED RELATIONAL-ALGEBRA OPERATIONS, NULL VALUES, MODIFICATION OF THE DATABASES ��

OUTCOMES:

​

Upon completion of this module, student will be able to:

• Explain the concepts of Relational Model.

​

MODULE-I

MATRUSRI

ENGINEERING COLLEGE

Relational Model

Relational Model represents the database as a collection of relations.

• A relation is nothing but a table of values with rows and columns.
• Each row is known as a tuple. Each column has a name or attribute.

​

Attribute: It contains the name of a column in a particular table.

​

Domain: It contains a set of atomic values that an attribute can take.

​

Degree of a Relation: The number of the attributes in a given relation is called degree of the relation.

​

Relational schema: A relational schema contains the name of the relation and name of all columns or attributes.

​

Relational key: In the relational key, each row has one or more attributes. It can identify the row in the relation uniquely.

​

MATRUSRI

ENGINEERING COLLEGE

Relational Schema

MATRUSRI

ENGINEERING COLLEGE

Structure of Relational Database

• A relational database consists of a collection of tables. Each table is assigned a unique name.
• A row in a table represents a relationship among a set of values.

For basic structure, consider the deposit table of the following figure:

​

MATRUSRI

ENGINEERING COLLEGE

Keys in Relational Database

1. Primary Key: The primary key is defined as a data item (attribute) which uniquely identifies a record (one row) in a relation. A primary key cannot accept NULL values. A composite primary key is a primary key comprising of more than one attributes.
2. Candidate Key: In a relation in which there is more than one attribute combination possessing the unique identification property, all the various combinations of attributes, which serve as a primary key are called the candidate keys of the given relation.

Note: Subset of Candidate keys is not a Primary Key.

3. Alternate Key: A candidate key that is not the primary key, called as alternate key.
4. Secondary Key: System may use a key which does not identify a unique record or tuple but which identifies all those which have certain property. This is referred to as a Secondary Key.
5. Super Key: A group of attribute that can uniquely identify a tuple from a relational schema.
6. Unique Key: same as primary key, but can accept NULL values.
7. Foreign Key: An attribute of a relation E, which is dependant on the primary key of another relation E1.
8. Surrogate Key: A surrogate key is a key which does not have any contextual or business meaning. It is manufactured “artificially” and only for the purposes of data analysis. The most frequently used version of a surrogate key is an increasing sequential integer or “counter” value (i.e. 1, 2, 3).

​

​

MATRUSRI

ENGINEERING COLLEGE

Fundamental Relational-Algebra Operations

Relational Algebra is a procedural language consisting of a set of operations that take one or two relations as input and produce a new relation as their result.

​

Six basic operators

• select: σ
• project: ∏
• union: ∪
• set difference: –
• Cartesian product: x
• rename: ρ

MATRUSRI

ENGINEERING COLLEGE

Select Operation

The select operation selects tuples that satisfy a given predicate.

​

Notation: σ _p (r)

​

p is called the selection predicate

​

Example: select those tuples of the instructor relation where the instructor is in the “Physics” department.

Query: σ _{dept_name=“Physics”} (instructor)

Result:

​

​

MATRUSRI

ENGINEERING COLLEGE

Select Operation

• We allow comparisons using =, ≠, >, ≥. <. ≤ in the selection predicate.

​

• We can combine several predicates into a larger predicate by using the connectives: ∧ (and), ∨ (or), ¬ (not)

​

Example: Find the instructors in Physics with a salary greater $90,000, we write:

σ _{dept_name=“Physics”} ∧ _{salary > 90,000} (instructor)

​

• The select predicate may include comparisons between two attributes.

​

Example: Find all departments whose name is the same as their building name:

σ _{dept_name=building} (department)

MATRUSRI

ENGINEERING COLLEGE

Project Operation

• A unary operation that returns its argument relation, with certain attributes left out.

Notation:

∏ _{A1,A2,A3 ….Ak} (r)

where A₁, A₂, …, A_k are attribute names and r is a relation name.

​

• The result is defined as the relation of k columns obtained by erasing the columns that are not listed

​

• Duplicate rows removed from result, since relations are sets

​

MATRUSRI

ENGINEERING COLLEGE

Cartesian-Product Operation

The Cartesian-product operation (denoted by X) allows us to combine information from any two relations.

Example: the Cartesian product of the relations instructor and teaches is written as:

instructor X teaches

We construct a tuple of the result out of each possible pair of tuples: one from the instructor relation and one from the teaches relation (see next slide)

​

Since the instructor ID appears in both relations we distinguish between these attribute by attaching to the attribute the name of the relation from which the attribute originally came.

instructor.ID

teaches.ID

​

MATRUSRI

ENGINEERING COLLEGE

The instructor X teaches table

MATRUSRI

ENGINEERING COLLEGE

Join Operation

• The Cartesian-Product

instructor X teaches

associates every tuple of instructor with every tuple of teaches.

• Most of the resulting rows have information about instructors who did NOT teach a particular course.
• To get only those tuples of “instructor X teaches “ that pertain to instructors and the courses that they taught, we write:

σ _{instructor.id = teaches.id} (instructor x teaches ))

We get only those tuples of “instructor X teaches” that pertain to instructors and the courses that they taught.

• The result of this expression, shown in the next slide

​

MATRUSRI

ENGINEERING COLLEGE

Join Operation

• The table corresponding to:

σ _{instructor.id = teaches.id} (instructor x teaches))

​

�

MATRUSRI

ENGINEERING COLLEGE

Union Operation

The union operation allows us to combine two relations

​

Notation: r ∪ s

​

For r ∪ s to be valid:

1. r, s must have the same arity (same number of attributes)

2. The attribute domains must be compatible (example: 2^nd

column of r deals with the same type of values as does the

2^nd column of s)

�

MATRUSRI

ENGINEERING COLLEGE

Union Operation

Example: to find all courses taught in the Fall 2017 semester, or in the Spring 2018 semester, or in both.

Query:

∏_{course_id} (σ _{semester=“Fall” Λ year=2017} (section)) ∪ � ∏_{course_id} (σ _{semester=“Spring” Λ year=2018} (section))

​

Result:

MATRUSRI

ENGINEERING COLLEGE

Set Difference Operation

The set-difference operation allows us to find tuples that are in one relation but are not in another.

Notation: r – s

Set differences must be taken between compatible relations.

r and s must have the same arity

attribute domains of r and s must be compatible

​

Example: To find all courses taught in the Fall 2017 semester, but not in the Spring 2018 semester.

​

Query: ∏_{course_id} (σ _{semester=“Fall” Λ year=2017} (section)) − � ∏_{course_id} (σ _{semester=“Spring” Λ year=2018} (section))

​

Result:

MATRUSRI

ENGINEERING COLLEGE

The Rename Operation

The results of relational-algebra expressions do not have a name that we can use to refer to them. The rename operator, ρ , is used for that purpose

​

The expression ρ_x (E) returns the result of expression E under the name x

​

Another form of the rename operation:

ρ_{x(A1,A2, .. An)} (E)

​

MATRUSRI

ENGINEERING COLLEGE

Additional Relational-Algebra Operations

• Join operations take two relations and return another relation as a result .

​

• A join operation is a Cartesian product which requires that tuples in the two relations match (under some condition). It also specifies the attributes that are present in the result of the join

​

• The join operations are typically used as subquery expressions in the from clause

​

• Three types of joins are:

​

• Natural join
• Inner join
• Outer join

​

​

​

MATRUSRI

ENGINEERING COLLEGE

Intersection Operation

Example: to find all courses taught in the Fall 2017 semester, or in the Spring 2018 semester, or in both.

Query:

∏_{course_id} (σ _{semester=“Fall” Λ year=2017} (section)) ν � ∏_{course_id} (σ _{semester=“Spring” Λ year=2018} (section))

​

Result:

MATRUSRI

ENGINEERING COLLEGE

NULL Values

It is possible for tuples to have a null value, denoted by null, for some of their attributes. null signifies an unknown value or that a value does not exist.

• The result of any arithmetic expression involving null is null.
• Aggregate functions simply ignore null values
• For duplicate elimination and grouping, null is treated like any other value, and two nulls are assumed as same. As an alternative, assume each null is different from each other like in SQL.

Arithmetic Operations and Comparisons with null values return the special truth value unknown or null.

For logical operators with an input as null:

OR: (unknown or true) = true, (unknown or false) = unknown, (unknown or unknown) = unknown

AND: (unknown and true) = unknown, (unknown and false) = false, (unknown and unknown) = unknown

 NOT: (not unknown) = unknown

MATRUSRI

ENGINEERING COLLEGE

Modifying the Database

The content of the database may be modified using the following operations:

• Deletion
• Insertion
• Updating

MATRUSRI

ENGINEERING COLLEGE