Mod 1,2 Minor

Amity School of Engineering & Technology
Amity School of Engineering and Technology

Course Level: UG
Course Code: CSE201
Course Title: Introduction to Database Management Theory
1
Module-I Introduction
1. Concept and goals of DBMS,
2. Database Languages,
3. Database Users,
4. Database Abstraction.
5. Database architecture,
6. The Relational Data Model and
7. Relational Database Constraints,
8. Basic Concepts of ER Model,
9. Relationship sets,
10. Keys,
11. Mapping,
12. Design of ER Model
2
Pre-requisites:
• Knowledge about the raw data
• knowledge about the operating system and organization
3
Course Objectives:
• The objective of this course is to get students familiar with
Databases and their use.
• They can identify different types of available database model,

concurrency techniques and new applications of the DBMS.
4
Course Learning Outcomes:

On the successful completion of the course, the student will be able
to
• Understand the database fundamentals along with conceptual
modeling to deal real life applications.
• Develop the ability to retrieve and miniplate information for business
decision making from databases
• Apply normalization techniques for consistent database
development.
• Understand the query processing techniques to automate the real
time problems of databases.
• Analyse the problems of data management in a concurrent
environment.
5
Data Vs Information
Data: Raw fact, Unorganized, Unprocessed information
Exp: Marks /result of classes and school
Information: Processing of data, Organized

Exp: Avg. Marks, Pass % of school
6
Objectives
• Mass Storage  large amount of data
• Remove Duplicates  Remove
• Multiuser  Access can be done by multiple user
• Protection Security for data access
7
Types of databases
• Centralized database  One DB + Multiple users
• Distributed Database  Multiple DB + Multiple users
• Personal Database Small DB+ Single used
• End user Database Application –based on the role
• Commercial Database  User have to pay for accessing data
• Relational Database  Data Table Row & column
Row  Tuple , Column  Attributes
• Cloud Database  Data is store in the cloud and built for virtual
environment
• Object Oriented Database  OO programming + Relational Database
• Graph Database  node entity , edge relationship between entities
8
Introduction to DBMS
 DBMS stands for Database Management System. We can break it like this
DBMS = Database + Management System.
 Database is a collection of data and Management System is a set of programs

to store and retrieve those data.
 Based on this we can define DBMS like this: DBMS is a collection of inter-
related data and set of programs to store & access those data in an easy and
effective manner.
What is the need of DBMS?
Database systems are basically developed for large amount of data. When dealing
with huge amount of data, there are two things that require optimization: Storage
of data and retrieval of data.
9
Storage: According to the principles of database systems, the data is stored in such a
way that it acquires lot less space as the redundant data (duplicate data) has been
removed before storage.
Fast Retrieval of data: Along with storing the data in an optimized and systematic
manner, it is also important that we retrieve the data quickly when needed. Database
systems ensure that the data is retrieved as quickly as possible.
10
Purpose of Database Systems

The main purpose of database systems is to manage the data. Consider a university
that keeps the data of students, teachers, courses, books etc.
To manage this data we need to store this data somewhere where we can add new
data, delete unused data, update outdated data, retrieve data, to perform these
operations on data we need a Database management system that allows us to store
the data in such a way so that all these operations can be performed on the data
efficiently.
11
Database Applications – DBMS

Applications where we use Database Management Systems are:
 Telecom: There is a database to keeps track of the information regarding calls made, network
usage, customer details etc. Without the database systems it is hard to maintain that huge amount
of data that keeps updating every millisecond.
 Industry: Where it is a manufacturing unit, warehouse or distribution centre, each one needs a
database to keep the records of ins and outs. For example distribution centre should keep a track
of the product units that supplied into the centre as well as the products that got delivered out
from the distribution centre on each day; this is where DBMS comes into picture.
 Banking System: For storing customer info, tracking day to day credit and debit transactions,
generating bank statements etc. All this work has been done with the help of Database
management systems.
 Sales: To store customer information, production information and invoice details.
 Airlines: To travel though airlines, we make early reservations, this reservation information
along with flight schedule is stored in database.
 Education sector: Database systems are frequently used in schools and colleges to store and
retrieve the data regarding student details, staff details, course details, exam details, payroll data,
attendance details, fees details etc. There is a hell lot amount of inter-related data that needs to be
stored and retrieved in an efficient manner.
 Online shopping: You must be aware of the online shopping websites such as Amazon, Flipkart
etc. These sites store the product information, your addresses and preferences, credit details and
provide you the relevant list of products based on your query. All this involves a Database12
management system.
Advantages of DBMS over file system

 No redundant data: Redundancy removed by data normalization. No data
duplication saves storage and improves access time.
 Data Consistency and Integrity: As we discussed earlier the root cause of data
inconsistency is data redundancy, since data normalization takes care of the data
redundancy, data inconsistency also been taken care of as part of it
 Data Security: It is easier to apply access constraints in database systems so that
only authorized user is able to access the data. Each user has a different set of access
thus data is secured from the issues such as identity theft, data leaks and misuse of
data.
 Privacy: Limited access means privacy of data.
 Easy access to data – Database systems manages data in such a way so that the data
is easily accessible with fast response times.
 Easy recovery: Since database systems keeps the backup of data, it is easier to do a
full recovery of data in case of a failure.
 Flexible: Database systems are more flexible than file processing systems.
13
Disadvantages of DBMS:
DBMS implementation cost is high compared to the file system
1. Complexity: Database systems are complex to understand

2. Performance: Database systems are generic, making them suitable for
various applications. However this feature affect their performance for some
applications
Drawbacks of File system
1. Data redundancy:
2. Data Inconsistency
3. Data Isolation
4. Dependency on application programs:
5. Atomicity issues:
6. Data Security
14
DBMS Architecture
The architecture of DBMS depends on the computer system on which it runs.
For example, in a client-server DBMS architecture, the database systems at server

machine can run several requests made by client machine.
Types of DBMS Architecture
There are three types of DBMS architecture:
1. Single tier architecture

2. Two tier architecture
3. Three tier architecture
15
1. Single tier architecture
1. In this type of architecture, the database is readily available on the client machine,
any request made by client doesn’t require a network connection to perform the
action on the database.
2. For example, lets say you want to fetch the records of employee from the database
and the database is available on your computer system, so the request to fetch
employee details will be done by your computer and the records will be fetched
from the database by your computer as well.
3. This type of system is generally referred as local database system.
16
17
2. Two tier architecture
1. In two-tier architecture, the Database system is present at the server machine and
the DBMS application is present at the client machine, these two machines are
connected with each other through a reliable network as shown in the above
diagram.
2. Whenever client machine makes a request to access the database present at server
using a query language like sql, the server perform the request on the database and
returns the result back to the client.
3. The application connection interface such as JDBC, ODBC are used for the
interaction between server and client.
18
3. Three tier architecture
1. In three-tier architecture, another layer is present between the client machine and
server machine.
2. In this architecture, the client application doesn’t communicate directly with the
database systems present at the server machine, rather the client application
communicates with server application and the server application internally
communicates with the database system present at the server.
19
20
DBMS – Three Level Architecture
21
This architecture has three levels:
1. External level
2. Conceptual level
3. Internal level
1. External level
It is also called view level. The reason this level is called “view” is because several
users can view their desired data from this level which is internally fetched from
database with the help of conceptual and internal level mapping.
The user doesn’t need to know the database schema details such as data structure,
table definition etc. user is only concerned about data which is what returned back to
the view level after it has been fetched from database (present at the internal level).
External level is the “top level” of the Three Level DBMS Architecture.
22
2. Conceptual level
 It is also called logical level. The whole design of the database such as
relationship among data, schema of data etc. are described in this level.
 Database constraints and security are also implemented in this level of

architecture. This level is maintained by DBA (database administrator).
3. Internal level
 This level is also known as physical level. This level describes how the data is
actually stored in the storage devices.
 This level is also responsible for allocating space to the data. This is the lowest
level of the architecture.
23
View of Data in DBMS
 Abstraction is one of the main features of database systems. Hiding irrelevant

details from user and providing abstract view of data to users, helps in easy and
efficient user-database interaction.
 The three level of DBMS architecture, The top level of that architecture is “view
level”.
 The view level provides the “view of data” to the users and hides the irrelevant
details such as data relationship, database schema, constraints, security etc from the
user.
24
Data Abstraction in DBMS
Database systems are made-up of complex data structures. To ease the user
interaction with database, the developers hide internal irrelevant details from users.
This process of hiding irrelevant details from user is called data abstraction.
We have three levels of abstraction:
1. Physical level: This is the lowest level of data abstraction. It describes how data
is actually stored in database. You can get the complex data structure details at
this level.
2. Logical level: This is the middle level of 3-level data abstraction architecture. It
describes what data is stored in database
3. View level: Highest level of data abstraction. This level describes the user
interaction with database system.
25
26
Example: Let’s say we are storing customer information in a customer table.

At physical level these records can be described as blocks of storage (bytes, gigabytes,
terabytes etc.) in memory. These details are often hidden from the programmers.
At the logical level these records can be described as fields and attributes along with
their data types, their relationship among each other can be logically implemented. The
programmers generally work at this level because they are aware of such things about
database systems.
At view level, user just interact with system with the help of GUI and enter the details at
the screen, they are not aware of how the data is stored and what data is stored; such
details are hidden from them.
27
Instance and schema in DBMS

DBMS Schema
Definition of schema: Design of a database is called the schema. Schema is

of three types: Physical schema, logical schema and view schema.
For example: In the following diagram, we have a schema that shows the
relationship between three tables: Course, Student and Section.
The diagram only shows the design of the database, it doesn’t show the data
present in those tables. Schema is only a structural view(design) of a
database as shown in the diagram below.
The design of a database at physical level is called physical schema, how

the data stored in blocks of storage is described at this level.
28
29
Design of database at logical level is called logical schema, programmers and database
administrators work at this level, at this level data can be described as certain types of
data records gets stored in data structures, however the internal details such as
implementation of data structure is hidden at this level (available at physical level).
Design of database at view level is called view schema. This generally describes end
user interaction with database systems.
30
DBMS Instance
Definition of instance: The data stored in database at a particular moment of time is

called instance of database. Database schema defines the variable declarations in tables
that belong to a particular database; the value of these variables at a moment of time is
called the instance of that database.
For example, lets say we have a single table student in the database, today the table has
100 records, so today the instance of the database has 100 records. Lets say we are
going to add another 100 records in this table by tomorrow so the instance of database
tomorrow will have 200 records in table. In short, at a particular moment the data
stored in database is called the instance, that changes over time when we add or delete
data from the database.
31
DBMS languages
Types of DBMS languages:
32
Data Definition Language (DDL)
DDL is used for specifying the database schema. It is used for creating tables, schema,
indexes, constraints etc. in database. Lets see the operations that we can perform on
database using DDL:
1. To create the database instance – CREATE

2. To alter the structure of database – ALTER
3. To drop database instances – DROP
4. To delete tables in a database instance – TRUNCATE
5. To rename database instances – RENAME
6. To drop objects from database such as tables – DROP
7. To Comment – Comment
All of these commands either defines or update the database schema that’s why they
come under Data Definition language.
33
Data Manipulation Language (DML)
1. DML is used for accessing and manipulating data in a database. The following
operations on database comes under DML:
1. To read records from table(s) – SELECT

2. To insert record(s) into the table(s) – INSERT
3. Update the data in table(s) – UPDATE
4. Delete all the records from the table – DELETE
Data Control language (DCL)
DCL is used for granting and revoking user access on a database –
1. To grant access to user – GRANT

2. To revoke access from user – REVOKE
34
In practical data definition language, data manipulation language and data

control languages are not separate language, rather they are the parts of a
single database language such as SQL.
Transaction Control Language(TCL)
The changes in the database that we made using DML commands are either
performed or rollbacked using TCL.
1. To persist the changes made by DML commands in database – COMMIT

2. To rollback the changes made to the database – ROLLBACK
35
Data models in DBMS
Data Model is a logical structure of Database. It describes the design of database to

reflect entities, attributes, relationship among data, constrains etc.
Types of Data Models
There are several types of data models in DBMS. We will cover them in detail in
separate articles(Links to those separate tutorials are already provided below). In
this guide, we will just see a basic overview of types of models.
Object based logical Models – Describe data at the conceptual and view levels.
1. E-R Model
2. Object oriented Model
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
ASET
Keys
Introduction ASET
• Establish and identify relationships between tables and

also to uniquely identify any record or row of data
inside a table.
• A Key can be a single attribute or a group of attributes,
where the combination may act as a key.
Objective ASET
• Keys in DBMS
• Various types of keys
• Need of keys
Need ASET
• In tables data is unsorted and unorganized

• To fetch any particular record from such dataset you
will have to apply some conditions, but if there is
duplicate data present and every time you try to fetch
some data by applying certain condition, you may get
the wrong data
• Keys are defined to easily identify any row of data in a
table.
Types ASET
• Super Key
• Candidate Key
• Primary key
• Composite keys
Example ASET
table-1
Super key ASET
• Super Key is defined as a set of attributes within a

table that can uniquely identify each record within a
table.
• In the table-1 defined super key would
include student_id, (student_id, name), phone etc
ASET
Candidate Key
• Candidate keys are defined as the minimal set of fields
which can uniquely identify each record in a table.
• It is an attribute or a set of attributes that can act as a
Primary Key for a table to uniquely identify each record
in that table.
• There can be more than one candidate key.
• In table-1, student_id and phone both are candidate
keys for table Student.
ASET
Primary Key
• Primary key is a candidate key that is most appropriate
to become the main key for any table.
• It is a key that can uniquely identify each record in a
table.
ASET
Composite Key
• Key that consists of two or more attributes that uniquely identify any
record in a table is called Composite key.
• the attributes which together form the Composite key are not a key
independentely or individually
ASET
Other keys
• Alternative key
– The candidate key which are not selected as primary key are
known as secondary keys or alternative keys.
• Non-key Attributes
– Non-key attributes are the attributes or fields of a table, other
than candidate key attributes/fields in a table.
• Non-prime Attributes
– Non-prime Attributes are attributes other than Primary Key
attribute(s)
Example ASET
table-1
Q1 ASET
• Which one of the following cannot be taken as a

primary key ?
• Id
• Register number
• Dept_id
• Street
Q2 ASET
• Which one of the following is a set of one or more

attributes taken collectively to uniquely identify a
record?
• Candidate key
• Sub key
• Super key
• Foreign key
Q3 ASET
• Consider attributes ID , CITY and NAME . Which one of

this can be considered as a super key?
• NAME
• ID
• CITY
• CITY , ID
Q4 ASET
• Which one of the following attribute can be taken as a

primary key?
• Name
• Street
• Id
• Department
Example ASET
table-1
Amity School of Engineering Technology

MODULE I
Topic: Constraints
B.Tech.(CSE)
DBMS(CSE-201)
1
Learning outcomes Amity School of Engineering Technology
• Students will be able to know about

– constraints
– Types of constrains
– How to apply constraints in SQL
2
Integrity Constraints
• Integrity constraints are used to ensure accuracy and consistency of the data in a
relational database.
• Integrity constraints are set of rules that the database is not permitted to violate.
• Constraints may apply to each attribute, or they may apply to relationship between
tables.
• Integrity constraints ensure the changes (update, deletion, insertion) made to database
by authorized users do not result in a loss of data consistency.
• Thus, integrity constraints guard against accidental damage to the database.
• Example: A blood group must be ‘A’,’B’,’AB’ or ‘O’ only.
Domain constraint
• Domain constraints defines the domain or the valid set of values for an attribute.
• The data type of domain includes string character, integer, time, date, currency etc. the
value of the attribute must be available in the corresponding domain.
Entity constraint
• The entity integrity constraints states that primary key value can not be null.
• This is because the primary key value is used to identify individual rows in relation and if
the primary key has a null value, then we can not identify those rows.
• The table can contain a null value other than the primary key field.
EMP_ID EMP_NAME SALARY

111 RAM 20000
112 SUMIT 50000
113 KUMAR 30000
AMIT 20000
Referential integrity constraints

• A referential integrity constraint is specified between two tables.
• It is enforced when a foreign key references the primary key of a table.
• In the RIC if a foreign key in table 1 refer to the primary key of Table 2 then either every
value of the foreign key in Table 1 must be available in primary key value of Table 2 or it
must be null.
• The Rules are
– You can not delete record from a primary table if matching record exist in the related table.
– You can not change a primary key value in the primary table if that record has related records.
– You can not insert a value in the foreign key field of the related table that does not exist in the primary key
of the primary key.
– However you can enter a null value in the foreign key, specifying that the records are unrelated.
Referential integrity example

Key constraints
• An entity set can have multiple keys or candidate keys(minimal superkey) but out of
which one key will be the primary key.
• Key constraint specifies that in any relation-
– All the values of primary key must be unique
– The value of primary key must not be null.
EMP_ID EMP_NAME SALARY Dept

111 RAM 20000 CS
112 SUMIT 50000 IT
113 KUMAR 30000 EC
111 AMIT 25000 ME
Key constraint
constraints in SQL
• DEFAULT Constraint − Provides a default value for a column when none is
specified.
• NOT NULL Constraint − Ensures that a column cannot have NULL value.
• UNIQUE Constraint − Ensures that all values in a column are different.
• PRIMARY Key − Uniquely identifies each row/record in a database table.
• FOREIGN Key − Uniquely identifies a row/record in any of the given database

table
Primary key Amity School of Engineering Technology
• CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
• ALTER TABLE Persons
ADD PRIMARY KEY (ID);
• ALTER TABLE Persons
DROP PRIMARY KEY;
Foreign key Amity School of Engineering Technology
• CREATE TABLE Orders (

OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(
PersonID)
);
• CREATE TABLE Orders (

OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (Pe
rsonID)
REFERENCES Persons(PersonID)
);
Check Amity School of Engineering Technology

ID int NOT NULL,
Age int,
CHECK (Age>=18)
);
CREATE TABLE Persons (

ID int NOT NULL,
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND
City='Sandnes')
);
default Amity School of Engineering Technology

ID int NOT NULL,
Age int,
City varchar(255) DEFAULT 'Sandnes'
);
Thank You

MODULE I
Topic: ER diagrams
B.Tech.(CSE)
DBMS(CSE-201)
1
Learning outcomes
• Students will be able to know about
– Introduction and basic
– Entity and Entity set
– Attributes and type
– Relationships
– Strong and weak entity set
2
Introduction
• Introduced by Peter chen in 1976.
• A non-technical design method works on conceptual level
based on the perception of real world.
• Consists of collection of basic objects entities and of
relationship among these objects and attributes which define
their properties.
• Free from ambiguities and provides a standard and logical way
of visualizing data.
• Basically, it is a diagrammatic representation easy to
understand even by non technical user
Entity and Entity Set

• Entity: An entity is a thing or an object in the real world that is
distinguishable from other object based or the values of the attributes is
person.
• Types Entities:
– Tangible : Entity which physically exists in real word eg. Car, pen, bank
locker
– Intangible: entities which exists logically eg account
• Entity Set: collection/ set of same type of entities that share some
properties or attributes called entity set.
• Entity can not be represented in a E-R diagram as it is instance/data.
• Entity can be represented in a relational model by raw/tuple/record.
• Entity set represent by rectangle in E-R diagram.
Attributes
• Are the units that describe the characteristic of entities.
• For each attributes there is a set of permitted values call

domain.
• In E-R diagram represented by ellipse or ovel which is in the

relational model by a separate column.
Type of Attributes
Example Amity School of Engineering Technology
Relationships
• Is an association between two and more entities of the same or
different entity set.
• No representation in E-R diagram as it is an instance or data.
• In relational model represented using a raw in the table.
Relationship Set/Type: A set of similar type of relationship.
• In a E-R diagram represented using diamond
• In relational model either by a separate table or by separate
Column (F.Key)
• Every relationship type has 3 components
– Name , Degree, Cordiality /participation
Degree of a Relationship Set

• Means no. of entity set associated (participated) in a relationship.
• Binary , Ternary , Quaternary
• Most of the relationship in the E-R diagram are binary.

Mapping cardinalities /cardinality ratio

• Express the no of entities to which other entity can be related via a
relationship .
• 1:1
• 1:M
• M:1
• M:N
Participation
• Specifies weather the existence of an entity depends on its being related
to another entity via a relationship type.
• These constraints specify the minimum and maximum no of relationship
instances that each entity can/must participates.
• Max Cardinality: it defines the max no of times an entity occurrence
participating in a relationship.
• Min. Cardinality: it define the min no of times an entity occurrence
participating in a relationship
• Partial participation
• Total participation
• Weak Entity
Assumptions : ER Model For a college DB

• A college contains many departments
• Each department can offer any number of courses
• Many instructors can work in a department
• An instructor can work only in one department
• For each department there is a Head
• An instructor can be head of only one department
• Each instructor can take any number of courses
• A course can be taken by only one instructor
• A student can enroll for any number of courses
• Each course can have any number of students
Steps in ER Modeling
• Identify the Entities
• Find relationships
• Identify the key attributes for every Entity
• Identify other relevant attributes
• Draw complete E-R diagram with all attributes including Primary Key
• Review your results with your Business users
• Step1: Identify the Entities

• • DEPARTMENT • STUDENT
• COURSE
• INSTRUCTOR
• Step 2: Find the relationships
• One course is enrolled by multiple students and one student enrolls for multiple courses,
hence the cardinality between course and student is Many to Many.
• The department offers many courses and each course belongs to only one department,
hence the cardinality between department and course is One to Many.
• One department has multiple instructors and one instructor belongs to one and only one
department , hence the cardinality between department and instructor is one to Many.
• Each department there is a “Head of department” and one instructor is “Head of
department “,hence the cardinality is one to one .
• One course is taught by only one instructor, but the instructor teaches many courses,
hence the cardinality between course and instructor is many to one.
Step3: Identify the key attributes

Deptname is the key attribute for the Entity “Department”, as it identifies the
Department uniquely.
Course# (CourseId) is the key attribute for “Course” Entity.
Student# (Student Number) is the key attribute for “Student” Entity.
Instructor Name is the key attribute for “Instructor” Entity.
Step 4: Identify other relevant attributes

For the department entity, the relevant attribute is location
For course entity, course name, duration, prerequisite
For instructor entity, room#, telephone#
For student entity, student name, date of birth
Case Study – Banking Business Scenario

• Assumptions :
• There are multiple banks and each bank has many branches. Each
branch has multiple customers
• Customers have various types of accounts
• Some Customers also had taken different types of loans from these bank
branches
• One customer can have multiple accounts and Loans

Summary Amity School of Engineering Technology
• Components of the Entity-Relationship Model

• Entity Types, Entity Sets
• Weak Entity Types
• Relationship Types, Relationship Sets, Roles
• Attributes, Attribute Classification, Keys
Extended ER Model
Generalization
Example: Generalization
Specialization
Example: Specialization
Inheritance
Aggregation
Example: Relationship of Relations

Example: Redundant Relationship

Aggregation
Example Aggregation
Thank You
Amity School of Engineering and Technology

Course Title: Database Management System
Course Level: UG
Course Code: CSE201
1
Course Objectives:
1. The objective of this course is to get students familiar with Databases and
their use.
2. They can identify different types of available database model, concurrency

techniques and new applications of the DBMS.
Pre-requisites: Knowledge about the data, knowledge about the operating

system and organization
Pedagogy for Course Delivery:
1. Classroom Teaching through lectures and presentation
2. Case studies
3. Lab work
2
Course Learning Outcomes
1. Understand the database fundamentals along with conceptual modeling to deal real
life applications.
2. Develop the ability to retrieve and manipulate information for business decision
making from databases
3. Apply normalization techniques for consistent database development.
4. Understand the query processing techniques to automate the real time problems of
databases.
5. Analyze the problems of data management in a concurrent environment.
3
Module II Relational Model

1. The Relational model ,
2. The Catalogue, Types, Keys,
3. Relational algebra,
4. Domain relational calculus
5. SQL-Query writing related to database-I
6. SQL-Query writing related to database-I
7. Tuple relational Fundamental operations,
8. Additional operations
9. SQL fundamentals,
10.Integrity SQL-Query writing related to database-II
11.SQL-Query writing related to database-II
12.Triggers and views
4
Relational Data Model

 Relational data model is the primary data model, which is used widely
around the world for data storage and processing.
 This model is simple and it has all the properties and capabilities
required to process data with storage efficiency..
5
What is Relational Model?
Relational Model (RM) represents the database as a collection of relations. A

relation is nothing but a table of values. Every row in the table represents a
collection of related data values. These rows in the table denote a real-world entity
or relationship.
The table name and column names are helpful to interpret the meaning of values in
each row. The data are represented as a set of relations. In the relational model, data
are stored as tables. However, the physical storage of the data is independent of the
way the data are logically organized.
Some popular Relational Database management systems are:
1. DB2 and Informix Dynamic Server - IBM

2. Oracle and RDB – Oracle
3. SQL Server and Access - Microsoft
6
Relational Model Concepts

1. Attribute: Each column in a Table. Attributes are the properties which define a
relation. e.g., Student_Rollno, NAME,etc.
2. Tables – In the Relational model the, relations are saved in the table format. It is
stored along with its entities. A table has two properties rows and columns. Rows
represent records and columns represent attributes.
3. Tuple (Row)– It is nothing but a single row of a table, which contains a single
record.
4. Relation Schema: A relation schema represents the name of the relation with its
attributes.
5. Degree: The total number of attributes which in the relation is called the degree of
the relation.
6. Cardinality: Total number of rows present in the Table.
7. Column: The column represents the set of values for a specific attribute.
8. Relation instance – Relation instance is a finite set of tuples in the RDBMS system.
Relation instances never have duplicate tuples.
9. Relation key - Every row has one, two or multiple attributes, which is called
relation key.
10. Attribute domain – Every attribute has some pre-defined value and scope which is
known as attribute domain 7
8
In practical data definition language, data manipulation language and data

control languages are not separate language, rather they are the parts of a
single database language such as SQL.
Transaction Control Language(TCL)
The changes in the database that we made using DML commands are either
performed or rollbacked using TCL.
1. To persist the changes made by DML commands in database – COMMIT

2. To rollback the changes made to the database – ROLLBACK
9
Key Constraints
An attribute that can uniquely identify a tuple in a relation is called the key of the
table. The value of the attribute for different tuples in the relation has to be unique.
Example:
In the given table, CustomerID is a key attribute of Customer Table. It is most likely
to have a single key for one customer, CustomerID =1 is only for the CustomerName
=" Google".
10
Student(Enrollnment, Name, DOB, Address, Batch, Age, Coursename,
Hostel, gender, ContactNo, Year_of_Passing, Grades, Email-id, Date_of
Attendance)
Student- Entity, Relation, table_name, Schema, Database

Enrollnment No, name, Dob, etc…attributes
Enrollnment No- Primary Key

Emailid,
ContactNo- alternate keys
Library(Lib_id, Enrollnment_No,….)- foreign Key
11
Referential Integrity Constraints(Foreign Key)
1. Referential Integrity constraints in DBMS are based on the concept of Foreign

Keys.
2. A foreign key is an important attribute of a relation which should be referred to
in other relationships.
3. Referential integrity constraint state happens where relation refers to a key
attribute of a different or same relation. However, that key element must exist in
the table.
12
In the above example, we have 2 relations, Customer and Billing.

Tuple for CustomerID =1 is referenced twice in the relation Billing. So we know
CustomerName=Google has billing amount $300 13
Operations in Relational Model
Four basic update operations performed on relational database model are

Insert, update, delete and select.
1. Insert is used to insert data into the relation

2. Delete is used to delete tuples from the table.
3. Modify allows you to change the values of some attributes in existing tuples.
4. Select allows you to choose a specific range of data.
Whenever one of these operations are applied, integrity constraints specified on the
relational database schema must never be violated.
14
Insert Operation
The insert operation gives values of the attribute for a new tuple which should be
inserted into a relation.
15
Update Operation
You can see that in the below-given relation table CustomerName= 'Apple' is updated
from Inactive to Active.
16
Select Operation
In the above-given example, CustomerName="Amazon" is selected
17
Delete Operation
To specify deletion, a condition on the attributes of the relation selects the tuple to be
deleted.
In the above-given example, CustomerName= "Apple" is deleted from the table.

The Delete operation could violate referential integrity if the tuple which is deleted is
referenced by foreign keys from other tuples in the same database.
18
19
RELATIONAL ALGEBRA
It is a widely used procedural query language. It collects instances of relations as input and gives
occurrences of relations as output. It uses various operations to perform this action. SQL
Relational algebra query operations are performed recursively on a relation. The output of these
operations is a new relation, which might be formed from one or more input relations.
Basic SQL Relational Algebra Operations- divided in various group
 Unary Relational Operations

1. SELECT (symbol: σ)-where
2. PROJECT (symbol: π)-Select
3. RENAME (symbol: ρ)
 Relational Algebra Operations From Set Theory

1. UNION (υ)
2. INTERSECTION ( ),
3. DIFFERENCE (-)
4. CARTESIAN PRODUCT ( x )
 Binary Relational Operations

1. JOIN
2. DIVISION
20
21
22
23
24
25
1. SELECT (σ)
 The select operation selects tuples that satisfy a given predicate.
 It is denoted by sigma (σ).
Notation: σ p(r)
Where:
 σ is used for selection prediction
 r is used for relation
 p is used as a propositional logic formula which may use connectors like: AND OR and
NOT. These relational can use as relational operators like =, ≠, ≥, <, >, ≤.
For example: LOAN Relation
BRANCH_NAME LOAN_NO AMOUNT
Downtown L-17 1000

Redwood L-23 2000
Perryride L-15 1500
Downtown L-14 1500
Mianus L-13 500
Roundhill L-11 900
Perryride L-16 1300
26
Example 1
σ topic = "Database" (Tutorials)
Output – Selects tuples from Tutorials (tablename) where topic = ‘Database’.
Example 2
σ topic = "Database" and author = “Korth"( Tutorials)
Output – Selects tuples from Tutorials where the topic is ‘Database’ and ‘author’ is Korth.
Example 3
σ sales > 50000 (Customers)
Output – Selects tuples from Customers (tablename) where sales is greater than 50000
27
Input:
σ BRANCH_NAME="perryride“ or BRANCH_NAME=“DOWNTOWN” (LOAN)
σ LOAN_NO < L-17 (LOAN)
Select * from loan where loan_no < L-17;
Perryride L-15 1500

Perryride L-16 1300
28
29
2. Project Operation:
 This operation shows the list of those attributes that we wish to appear in the result. Rest of
the attributes are eliminated from the table.
 It is denoted by ∏.
Notation: ∏ A1, A2, An (r)
Where
A1, A2, A3 is used as an attribute name of relation r.
Example: CUSTOMER RELATION
NAME STREET CITY
Jones Main Harrison

Smith North Rye
Hays Main Harrison
Curry North Rye
Johnson Alma Brooklyn
Brooks Senator Brooklyn
Input:
∏ NAME, CITY (CUSTOMER)
Select name, city from customer; 30

Output
NAME CITY
Jones Harrison
Smith Rye
Hays Harrison
Curry Rye
Johnson Brooklyn
Brooks Brooklyn
∏ NAME, CITY (σ city=“harrison" (CUSTOMER ))
Select name, city from customer where city=“harrison”;
31
32
Downtown L-17 1000

Redwood L-23 2000
Perryride L-15 1500
Downtown L-14 1500
Mianus L-13 500
Roundhill L-11 900
Perryride L-16 1300
33
3. Union Operation:
 Suppose there are two tuples R and S. The union operation contains all the tuples that are
either in R or S or both in R & S.
 It eliminates the duplicate tuples. It is denoted by ∪.
Notation: R ∪ S
A union operation must hold the following condition:
 R and S must have the attribute of the same number.
 Duplicate tuples are eliminated automatically.
Example: DEPOSITOR RELATION
CUSTOMER_NAME ACCOUNT_NO
Johnson A-101
Smith A-121
Mayes A-321
Turner A-176
Johnson A-273
Jones A-472
Lindsay A-284
34
BORROW RELATION
CUSTOMER_NAME LOAN_NO
Jones L-17
Smith L-23
Hayes L-15
Jackson L-14
Curry L-93
Smith L-11
Williams L-17
Input:
∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSITOR)
35
Output
CUSTOMER_NAME
Johnson
Smith
Hayes
Turner
Jones
Lindsay
Jackson
Curry
Williams
Mayes
36
5. Set Difference:
 Suppose there are two tuples R and S. The set intersection operation contains all
tuples that are in R but not in S.
 It is denoted by intersection minus (-).
Notation: R - S
Example: Using the above DEPOSITOR table and BORROW table

Input:
∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR)
Output:
CUSTOMER_NAME
Jackson
Hayes
Willians
Curry
37
4. Set Intersection:
 Suppose there are two tuples R and S. The set intersection operation contains all
tuples that are in both R & S.
 It is denoted by intersection ∩.
Notation: R ∩ S
Example: Using the above DEPOSITOR table and BORROW table
Input:∏ CUSTOMER_NAME (BORROW) ∩ ∏ CUSTOMER_NAME (DEPOSITO

R)
Output:
CUSTOMER_NAME
Smith
Jones
38
6. Cartesian product
 The Cartesian product is used to combine each row in one table with each row in
the other table. It is also known as a cross product.
 It is denoted by X.
Notation: E X D
Example: EMPLOYEE
EMP_ID EMP_NAME EMP_DEPT
1 Smith A
2 Harry C
3 John B
DEPARTMENT
DEPT_NO DEPT_NAME
A Marketing
B Sales
C Legal
39
Input: EMPLOYEE X DEPARTMENT

Output:
EMP_ID EMP_NAME EMP_DEPT DEPT_NO DEPT_NAME
1 Smith A A Marketing
1 Smith A B Sales
1 Smith A C Legal
2 Harry C A Marketing
2 Harry C B Sales
2 Harry C C Legal
3 John B A Marketing
3 John B B Sales
3 John B C Legal
40
Examples of queries
41
Examples of queries
42
Examples of queries
43
44
Additional Operators
45
7. Rename Operation:
The rename operation is used to rename the output relation. It is denoted by rho (ρ).
Example: We can use the rename operator to rename STUDENT relation to

STUDENT1.
ρ(STUDENT1, STUDENT)
46
Join Operations: A Join operation combines related tuples from different relations, if
and only if a given join condition is satisfied. It is denoted by ⋈.
Example: EMPLOYEE
EMP_CODE EMP_NAME
101 Stephan
102 Jack
103 Harry
SALARY
EMP_CODE SALARY
101 50000
102 30000
103 25000
47
Operation: (EMPLOYEE ⋈ SALARY)

RESULT
EMP_CODE EMP_NAME SALARY
101 Stephan 50000

102 Jack 30000
103 Harry 25000
48
Types of Join operations:
SQL-
1. Equi
2. Non-Equi
3. Self join
4. Outer Join
(+)
5. Between
49
1. Natural Join:
 A natural join is the set of tuples of all combinations in R and S that are equal on
their common attribute names.
 It is denoted by ⋈.
Example: Let's use the above EMPLOYEE table and SALARY table:
Input:
∏EMP_NAME, SALARY (EMPLOYEE ⋈ SALARY)
(Select emp_name, salary from employee e, salary s where e.emp_code=s.emp_code; )
Output:
EMP_NAME SALARY
Stephan 50000
Jack 30000
Harry 25000
50
2. Outer Join:
The outer join operation is an extension of the join operation. It is used to deal with
missing information.
Example: EMPLOYEE
EMP_NAME STREET CITY
Ram Civil line Mumbai

Shyam Park street Kolkata
Ravi M.G. Street Delhi
Hari Nehru nagar Hyderabad
FACT_WORKERS
EMP_NAME BRANCH SALARY
Ram Infosys 10000

Shyam Wipro 20000
Kuber HCL 30000
Hari TCS 50000
51
Input:
(EMPLOYEE ⋈ FACT_WORKERS)
Output:
EMP_NAME STREET CITY BRANCH SALARY
Ram Civil line Mumbai Infosys 10000

Shyam Park street Kolkata Wipro 20000
Hari Nehru nagar Hyderabad TCS 50000
Full Outer Join
52
An outer join is basically of three types:

a) Left outer join
b) Right outer join
c) Full outer join
a. Left outer join:
 Left outer join contains the set of tuples of all combinations in R and S that are
equal on their common attribute names.
 In the left outer join, tuples in R have no matching tuples in S.
 It is denoted by ⟕.
Example: Using the above EMPLOYEE table and FACT_WORKERS table
Input: EMPLOYEE ⟕ FACT_WORKERS


Hari Nehru street Hyderabad TCS 50000
Ravi M.G. Street Delhi NULL NULL
53
b. Right outer join:

 Right outer join contains the set of tuples of all combinations in R and S that are
equal on their common attribute names.
 In right outer join, tuples in S have no matching tuples in R.
 It is denoted by ⟖.
Example: Using the above EMPLOYEE table and FACT_WORKERS Relation
Input: EMPLOYEE ⟖ FACT_WORKERS
Output:
EMP_NAME BRANCH SALARY STREET CITY
Ram Infosys 10000 Civil line Mumbai

Shyam Wipro 20000 Park street Kolkata
Hari TCS 50000 Nehru street Hyderabad
Kuber HCL 30000 NULL NULL
54
c. Full outer join:

 Full outer join is like a left or right join except that it contains all rows from
both tables.
 In full outer join, tuples in R that have no matching tuples in S and tuples in S
that have no matching tuples in R in their common attribute name.
 It is denoted by ⟗.
Example: Using the above EMPLOYEE table and FACT_WORKERS table
Input: EMPLOYEE ⟗ FACT_WORKERS
Output:

Hari Nehru street Hyderabad TCS 50000
Ravi M.G. Street Delhi NULL NULL
Kuber NULL NULL HCL 30000
55
3. Equi join:
It is also known as an inner join. It is the most common join. It is based on matched
data as per the equality condition. The equi join uses the comparison operator(=).
Example: CUSTOMER RELATION
CLASS_ID NAME
1 John
2 Harry
3 Jackson
PRODUCT
PRODUCT_ID CITY
1 Delhi
2 Mumbai
3 Noida
56
Input: CUSTOMER ⋈ PRODUCT

Output:
CLASS_ID NAME PRODUCT_ID CITY
1 John 1 Delhi
2 Harry 2 Mumbai
3 Jackson 3 Noida
57
Surprise-Class Test
58
We will consider the following 3 tables as examples to explain these RA operations.

We have a Member table (details about members), Book table (details about books)
and Borrow table (details about borrowed books).
Surprise-Class Test
59
Query: Member IDs of members who have borrowed books.

Query: Member IDs of members and the Book IDs of the books they have borrowed
books.
Query: Details of the members who were born on 21/10/1997.
Query: Rename the Member relation as LibraryMemebr.
Query: Member ✕ Borrow
Query: Member ⋈ Borrow
Query: Borrowing details of members who have burrowed more than one book.
Query: Book IDs of the books borrowed by Charlie and Mike.
Query: Member IDs of the members who have borrowed both the books “Fences” and
“Inheritance”.
Query: Member IDs of the members who have never borrowed books.
60
61
62
63
64
65
Note that Member relation has 5 tuples and Burrow relation has 5 tuples.
Hence Member ✕ Borrow has 5✕5 = 25 tuples.
66
Note that the common attribute in this example is Member ID.
67
68
69
70
R1 will return details of all the members Alice, Bob, Charlie, Mike and Katie.
R2 will return details of Alice, Charlie, Mike and Katie as they have borrowed
books. When we take the difference between R1 and R2, the details of Bob
will be returned.
71
References
https://towardsdatascience.com/a-quick-guide-to-relational-algebra-
operators-in-dbms-1ff2ddeca
https://slideplayer.com/slide/4814012/
chrome-
extension://ohfgljdgelakfkefopgklcohadegdpjf/https://cdn.prexams.com/62
86/Chapter4Ex.pdf
https://www.coursehero.com/file/p45ur8b/78-Q15-Find-the-sids-of-
suppliers-who-supply-every-part-Solution-1-%CF%80-sidpid/
72
Relational Calculus
73
1. Relational calculus is a non-procedural query language, and instead of algebra, it

uses mathematical predicate calculus. The relational calculus is not the same as that
of differential and integral calculus in mathematics but takes its name from a branch
of symbolic logic termed as predicate calculus. When applied to databases, it is
found in two forms. These are
 Tuple relational calculus which was originally proposed by Codd in the year
1972 and
 Domain relational calculus which was proposed by Lacroix and Pirotte in the
year 1977
2. In first-order logic or predicate calculus, a predicate is a truth-valued function with

arguments. When we replace with values for the arguments, the function yields an
expression, called a proposition, which will be either true or false.
2. For example, steps involved in listing all the employees who attend the 'Networking'
Course would be:
 SELECT the tuples from COURSE relation with COURSENAME =
'NETWORKING‘
PROJECT the COURSE_ID from above result 74
SELECT the tuples from EMP relation with COURSE_ID resulted above.
Tuple Relational Calculus
 A nonprocedural query language, where each query is of the form

{t | P (t ) }
where t = resulting tuples,
P(t) = known as Predicate and these are the conditions that are used to
fetch t
 Thus, it generates set of all tuples t, such that Predicate P(t) is true for
t.
 P(t) may have various conditions logically combined with OR (∨), AND
(∧), NOT(¬).
 t  r denotes that tuple t is in relation r
 P is a formula similar to that of the predicate calculus 75
Predicate Calculus Formula

1. Set of attributes and constants
2. Set of comparison operators: (e.g., , , , , , )
3. Set of connectives: and (), or (v)‚ not ()
4. Implication (): x  y, if x if true, then y is true
x  y x v y
5. It also uses set of quantifiers:
 t r (Q (t )) ”there exists” a tuple in t in relation r
such that predicate Q (t ) is true
 t r (Q (t )) Q is true “for all” tuples t in relation r
76
Existential quantifier is represented by the symbol ∃ and the
syntax of the query with the existential quantifier can be represented
as:
∃T ε Cond (T)
The query will succeed only if the condition turns to be true for at
least some tuples in T.
Universal quantifier is represented by a symbol ∀ and the syntax of

the query with universal quantifier can be represented as:
∀ T ε Cond (T)
The query will succeed only if the condition comes out to be true for
all the tuples in T.
77
Tuple Relational Calculus

In the tuple relational calculus, you will have to find tuples for which a predicate is true.
The calculus is dependent on the use of tuple variables. A tuple variable is a variable that
'ranges over' a named relation: i.e., a variable whose only permitted values are tuples of
the relation.
1. For example, to specify the range of a tuple variable S as the Staff relation, we write:
Staff(S)
2. To express the query 'Find the set of all tuples S such that F(S) is true,' we can write:
{S | F(S)}
Here, F is called a formula (well-formed formula, or wff in mathematical logic).
3. For example, to express the query 'Find the staffNo, fName, lName, position, DOB,
salary, and branchNo of all staff earning more than £10,000', we can write:
{S | Staff(S) ∧ S.salary > 10000} 78

4. {t | TEACHER (t) ∧ t.SALARY>20000}-
It implies that it selects the tuples from the TEACHER in such a way that the
resulting teacher tuples will have a salary higher than 20000. This is an example
of selecting a range of values.
5. {t | TEACHER (t) ∧ t.DEPT_ID = 8}-
T select all the tuples of teachers' names who work under Department 8. Any
tuple variable with 'For All' (?) or 'there exists' (?) condition is termed as a bound
variable.
In the last example, for any range of values of SALARY greater than 20000, the
meaning of the condition does not alter. Bound variables are those ranges of tuple
variables whose meaning will not alter if another tuple variable replaces the tuple
variable.
In the second example, you have used DEPT_ID= 8, which means only for
DEPT_ID = 8 display the teacher details. Such a variable is called a free variable.
Any tuple variable without any 'For All' or 'there exists' condition is called Free
Variable.
79
Examples
1. Find the last names and hire dates of employees who make more than $100000.
{ W |  E  Employee(W .lastname = E .lastname ^ W.hiredate = E.hiredate ^ E.salary

> 100000)}
2. Retrieve details of all films that were released in 2010. The output schema should be
the same as that of the Film table.
{ F ∈ Film | F.year = 2010 }

σyear=2010(Film)
3. Retrieve details of all actors that are not in their thirties. The output schema should
be the same as that of the Film table.
{ A ∈ Actor | A.age < 30 ∨ A.age > 39 }

σ(age<30)∨(age>39)(Actor)
or σage<30(Actor) ∪ σage>39(Actor)
4. Retrieve the names of all directors.

{ T | ∃D ∈ Director . (T.name = D.name) }
πname(Director)
80
5. Retrieve the names of all American directors.
{ T | ∃D ∈ Director (D.nationality = ’American’ ∧ T.name = D.name) }

πname(σnationality=’American’(Director))
6. Find out the names of all British actors above the age of 40.
{ T | ∃A ∈ Actor (A.nationality = ’British’ ∧ A.age > 40 ∧ T.name = A.name) }
πname(σ(nationality=’British’∧age>40)(Actor))
7. Retrieve the name of each actor together with the titles of the films he/she
has performed in.
{ T | ∃A ∈ Actor, P ∈ Performance, F ∈ Film (A.actorId = P.actorId ∧ P.filmId =
F.filmId ∧ T.name = A.name ∧ T. title = F. title) }
πname, title (Actor (Performance ./ Film))
81
8. Find out the names of all actors that have played the character of Bruce Wayne
(Batman; see also Marshall et al., Physics Special Topics 10(1):2011).
{ T | ∃A ∈ Actor, P ∈ Performance (A.actorId = P.actorId ∧ P.character = ’Bruce Wayne’ ∧

T.name = A.name) }
πname(Actor ./ (σcharacter=’Bruce Wayne’(Performance))
9. Retrieve the names of all actors that have played the character of Bruce Wayne,
together with the year the corresponding films were released.
{ T | ∃A ∈ Actor, P ∈ Performance, F ∈ Film (A.actorId = P.actorId ∧ P.filmId = F.filmId ∧

P.character = ’Bruce Wayne’ ∧ T.name = A.name ∧ T.year = F.year) }
πname,year(Actor ./ (σcharacter=’Bruce Wayne’(Performance ./ Film)))
10. Retrieve all actors that appeared in Inception. The output schema should be the
same as that of the Actor table.
{ A ∈ Actor | ∃P ∈ Performance, F ∈ Film (A.actorId = P.actorId ∧ P.filmId = F.filmId ∧ F.

title = ’Inception’ )}
πactorId,name,nationality,age(Actor ./ (Performance ./ (σ title =’Inception’(Film))))
82
11. Find out the names of all actors that have performed in a film directed by Christopher
Nolan.
{ T | ∃A ∈ Actor, P ∈ Performance, F ∈ Film, D ∈ Director(A.actorId = P.actorId ∧ P.filmId

= F.filmId∧ F.directorId = D.directorId ∧ D.name = ’Christopher Nolan’ ∧ T.name =
A.name) }
πActor.name(Actor ./ Performance ./ Film ./ (σname=’Christopher Nolan’(Director)))
12. Retrieve the titles of all films in which Leonardo Di Caprio and Kate Winslet have co-
acted.
{ T | ∃A1 ∈ Actor, A2 ∈ Actor, P 1 ∈ Performance, P 2 ∈ Performance, F ∈

Film(A1.actorId = P 1.actorId ∧ A2.actorId = P 2.actorId ∧ A1.name = ’Leonardo
DiCaprio’ ∧ A2.name = ’Kate Winslet’ ∧ P 1.filmId = P 2.filmId ∧ F.filmId = P 1.filmId ∧ T.
title = F. title) }
π title (Film ./ (πfilmId(σname=’Kate Winslet’(Actor) ./ Performance)
∩ πfilmId(σname=’Leonardo DiCaprio’(Actor) ./ Performance)))
13. Assuming that the actorId and directorId values for actors and directors are
consistent across the tables, retrieve details of all actors that have directed a film.
{ A ∈ Actor | ∃D ∈ Director (A.actorId = D.directorId) }

πactorId,name,nationality,age(Actor ./actorId=directorId Director) 83
Example Queries
 Find the ID, name, dept_name, salary for instructors
whose salary is greater than $80,000
{t | t  instructor  t [salary ]  80000}
Notice that a relation on schema (ID, name, dept_name, salary)

is implicitly defined by the query
 As in the previous query, but output only the ID attribute value
{t |  s instructor (t [ID ] = s [ID ]  s [salary ]  80000)}
84
Example Queries
 Find the names of all instructors whose department is in the Watson

building
{t | s  instructor (t [name ] = s [name ]  u  department (u [dept_name ] =

s[dept_name] “  u [building] = “Watson” ))}
 Find the set of all courses taught in the Fall 2009 semester, or in
the Spring 2010 semester, or both
{t | s  section (t [course_id ] = s [course_id ]  s [semester] = “Fall”  s [year]
= 2009 v u  section (t [course_id ] = u [course_id ]  u [semester] =
“Spring”  u [year] = 2010 )}
85
Example Queries
 Find the set of all courses taught in the Fall 2009 semester, and in
the Spring 2010 semester
{t | s  section (t [course_id ] = s [course_id ] 
s [semester] = “Fall”  s [year] = 2009
 u  section (t [course_id ] = u [course_id ] 
u [semester] = “Spring”  u [year] = 2010 )}
 Find the set of all courses taught in the Fall 2009 semester, but not in
{t | s  section (t [course_id ] = s [course_id ] 

s [semester] = “Fall”  s [year] = 2009
  u  section (t [course_id ] = u [course_id ] 
u [semester] = “Spring”  u [year] = 2010 )}
Domain Relational Calculus


• A nonprocedural query language equivalent in power to the
tuple relational calculus
• Each query is an expression of the form:
{  x1, x2, …, xn  | P (x1, x2, …, xn)}
– x1, x2, …, xn represent domain variables

– P represents a formula similar to that of the predicate calculus

1. In the tuple relational calculus, you have use variables that have a series of
tuples in a relation. In the domain relational calculus, you will also use
variables, but in this case, the variables take their values from domains of
attributes rather than tuples of relations.
2. A domain relational calculus expression has the following general format:
 {d1, d2, . . . , dn | F(d1, d2, . . . , dm)} m ≥ n

 where d1, d2, . . . , dn, . . . , dm stand for domain variables and F(d1, d2, . . . ,
dm) stands for a formula composed of atoms.
3. select TCHR_ID and TCHR_NAME of teachers who work for department 8,

(where suppose - dept. 8 is Computer Application Department)
{<tchr_id, tchr_name=""> | <tchr_id, tchr_name=""> ? TEACHER Λ DEPT_ID

= 8}
89
Example Queries 1
90
Solution
91
Example Queries 2
92
Example Queries 3
93
Example Queries 4
• Find the ID, name, dept_name, salary for instructors whose salary is
greater than $80,000
– {< i, n, d, s> | < i, n, d, s>  instructor  s  80000}
• As in the previous query, but output only the ID attribute value
– {< i> | < i, n, d, s>  instructor  s  80000}
• Find the names of all instructors whose department is in the Watson
building
{< n > |  i, d, s (< i, n, d, s >  instructor
  b, a (< d, b, a>  department  b = “Watson” ))}
 Find the set of all courses taught in the Fall 2009 semester, or in
the Spring 2010 semester, or both
{<c> |  a, s, y, b, r, t ( <c, a, s, y, b, r, t >  section 
s = “Fall”  y = 2009 )
v  a, s, y, b, r, t ( <c, a, s, y, b, r, t >  section ] 
s = “Spring”  y = 2010)}
This case can also be written as
( (s = “Fall”  y = 2009 ) v (s = “Spring”  y = 2010))}
 Find the set of all courses taught in the Fall 2009 semester, and in

s = “Fall”  y = 2009 )
  a, s, y, b, r, t ( <c, a, s, y, b, r, t >  section ] 
s = “Spring”  y = 2010)}
Example Queries 5
96
Thanks
97

Mod 1,2 Minor

Uploaded by

Document Informationclick to expand document informationdatabase notes

Document Informationclick to expand document information

Copyright:

Available Formats

Mod 1,2 Minor

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Mod 1,2 Minor

Uploaded by

Copyright:

Available Formats

Amity School of Engineering & Technology

Amity School of Engineering and Technology

• knowledge about the operating system and organization

• They can identify different types of available database model,

Course Learning Outcomes:

Information: Processing of data, Organized

 Database is a collection of data and Management System is a set of programs

What is the need of DBMS?

Purpose of Database Systems

Database Applications – DBMS

Advantages of DBMS over file system

1. Complexity: Database systems are complex to understand

Drawbacks of File system

For example, in a client-server DBMS architecture, the database systems at server

Types of DBMS Architecture

There are three types of DBMS architecture:

1. Single tier architecture

1. Single tier architecture

3. This type of system is generally referred as local database system.

2. Two tier architecture

3. Three tier architecture

DBMS – Three Level Architecture

This architecture has three levels:

 Database constraints and security are also implemented in this level of

View of Data in DBMS

 Abstraction is one of the main features of database systems. Hiding irrelevant

Data Abstraction in DBMS

We have three levels of abstraction:

Example: Let’s say we are storing customer information in a customer table.

Instance and schema in DBMS

Definition of schema: Design of a database is called the schema. Schema is

The design of a database at physical level is called physical schema, how

Definition of instance: The data stored in database at a particular moment of time is

Data Definition Language (DDL)

1. To create the database instance – CREATE

Data Manipulation Language (DML)

1. To read records from table(s) – SELECT

Data Control language (DCL)

DCL is used for granting and revoking user access on a database –

1. To grant access to user – GRANT

In practical data definition language, data manipulation language and data

Transaction Control Language(TCL)

1. To persist the changes made by DML commands in database – COMMIT

Data models in DBMS

Data Model is a logical structure of Database. It describes the design of database to

Types of Data Models

• Establish and identify relationships between tables and

• In tables data is unsorted and unorganized

• Super Key is defined as a set of attributes within a

• Which one of the following cannot be taken as a

• Which one of the following is a set of one or more

• Consider attributes ID , CITY and NAME . Which one of

• Which one of the following attribute can be taken as a

Amity School of Engineering & Technology

• Students will be able to know about

EMP_ID EMP_NAME SALARY

Referential integrity constraints