DBMS Complete Note
DBMS Complete Note
DBMS Complete Note
System Fourth
SEMESTER
Notes Prepared By
Raju Poudel
MCA, Purbanchal University
DBMS Overview
• A database management system (DBMS) is a software package designed to
define, manipulate, retrieve and manage data in a database.
• A DBMS generally manipulates the data itself, the data format, field names,
record structure and file structure. It also defines rules to validate and manipulate
this data.
• A DBMS relieves users of framing programs for data maintenance. Fourth-
generation query languages, such as SQL, are used along with the DBMS package
to interact with a database.
• Some other DBMS examples include: MySQL, SQL Server, Oracle, dBASE, FoxPro,
MS-Access etc.
Database
• A database is a collection of information that is organized so that it can be easily
accessed, managed and updated. Data is organized into rows, columns and tables,
and it is indexed to make it easier to find relevant information.
Characteristics of DBMS
• Provides security and removes redundancy (duplication)
• Insulation between programs and data abstraction
• Support of multiple views of the data
• Sharing of data and multiuser transaction processing
• DBMS allows entities and relations among them to form tables.
• It follows the ACID concept (Atomicity, Consistency, Isolation, and Durability).
• DBMS supports multi-user environment that allows users to access and
manipulate data in parallel.
Importance of DBMS
• A database management system is important because it manages data efficiently
and allows users to perform multiple tasks with ease.
• A database management system stores, organizes and manages a large amount of
information within a single software application. Use of this system increases
efficiency of business operations and reduces overall costs.
• Database management systems are important to businesses and organizations
because they provide a highly efficient method for handling multiple types of
data.
• Some of the data that are easily managed with this type of system include:
employee records, student information, payroll, accounting, project management,
inventory and library books. These systems are built to be extremely versatile.
• Without database management, tasks have to be done manually and take more
time. Data can be categorized and structured to suit the needs of the company or
organization.
• Data is entered into the system and accessed on a routine basis by assigned users.
Each user may have an assigned password to gain access to their part of the
system. Multiple users can use the system at the same time in different ways.
Advantages of DBMS
• DBMS offers a variety of techniques to store & retrieve data
• DBMS serves as an efficient handler to balance the needs of multiple applications
using the same data
• Application programmers never exposed to details of data representation and
storage.
• A DBMS uses various powerful functions to store and retrieve data efficiently.
• Offers Data Integrity and Security
• The DBMS implies integrity constraints to get a high level of protection against
prohibited access to data.
• A DBMS schedules concurrent access to the data in such a manner that only one
user can access the same data at a time
• Reduced Application Development Time
Application of DBMS
In this text, we focus initially on the writing of database queries and the design of
database schemas. Database Design is a collection of processes that facilitate the
designing, development, implementation and maintenance of database systems. It helps
produce database systems
1. That meet the requirements of the users
2. Have high performance.
The description that arises from this design phase serves as the basis for specifying the
conceptual structure of the database. Here are the major characteristics of the
university.
For the system to be usable, it must retrieve data efficiently. The need for efficiency has
led designers to use complex data structures to represent data in the database.
Since many database-system users are not computer trained, developers hide the
complexity from users through several levels of abstraction, to simplify users’
interactions with the system:
Physical level. The lowest level of abstraction describes how the data are actually
stored. The physical level describes complex low-level data structures in detail.
Logical level. The next-higher level of abstraction describes what data are stored
in the database, and what relationships exist among those data. Database
administrators, who must decide what information to keep in the database, use
the logical level of abstraction.
View level. The highest level of abstraction describes only part of the entire
database. Even though the logical level uses simpler structures, complexity
remains because of the variety of information stored in a large database. Many
users of the database system do not need all this information; instead, they need
to access only a part of the database. The view level of abstraction exists to
simplify their interaction with the system. The system may provide many views for
the same database.
Relational Model
The relational model uses a collection of tables to represent both data and the
relationships among those data.
Each table has multiple columns, and each column has a unique name. Tables are
also known as relations. Each table contains records of a particular type. Each
record type defines a fixed number of fields, or attributes.
The columns of the table correspond to the attributes of the record type. The
relational data model is the most widely used data model, and a vast majority of
current database systems are based on the relational model.
Entity-Relationship Model.
The entity-relationship (E-R) data model uses a collection of basic objects, called entities,
and relationships among these objects. An entity is a “thing” or “object” in the real world
that is distinguishable from other objects. The entity-relationship model is widely used in
database design.
Historically, the network data model and the hierarchical data model preceded the
relational data model. These models were tied closely to the underlying implementation,
and complicated the task of modeling data. As a result, they are used little now, except in
old database code that is still in service in some places.
Data-Manipulation Language(DML)
A data-manipulation language (DML) is a language that enables users to access or
manipulate data as organized by the appropriate data model.
Examples of DML:
• SELECT – is used to retrieve data from the a database.
• INSERT – is used to insert data into a table.
• UPDATE – is used to update existing data within a table.
• DELETE – is used to delete records from a database table.
DBMS Structure
A database system is partitioned into modules that deal with each of the responsibilities
of the overall system. The functional components of a database system can be broadly
divided into the storage manager and the query processor components.
Transaction Manager
A transaction is a collection of operations that performs a single logical function in
a database application.
Each transaction is a unit of both atomicity and consistency. Thus, we require that
transactions do not violate any database-consistency constraints.
That is, if the database was consistent when a transaction started, the database
must be consistent when the transaction successfully terminates.
Transaction - manager ensures that the database remains in a consistent (correct)
state despite system failures (e.g., power failures and operating system crashes)
and transaction failures.
DBMS Architecture
DBMS architecture helps in design, development, implementation, and maintenance of a
database. A database stores critical information for a business. Selecting the correct
Database Architecture helps in quick and secure access to this data.
1 Tier Architecture
The simplest of Database Architecture are 1 tier where the Client, Server, and
Database all reside on the same machine.
Anytime you install a DB in your system and access it to practise SQL queries it is 1
tier architecture. But such architecture is rarely used in production.
3 Tier Architecture
3-tier schema is an extension of the 2-tier architecture. 3-tier architecture has following
layers:
1. Presentation layer (your PC, Tablet, Mobile, etc.)
2. Application layer (server)
3. Database Server
Data Independence
The main purpose of data abstraction is achieving data independence in order to save
time and cost required when the database is modified or altered.
Data independence is the ability of to make changes to data characteristics without have
to make changes to the programs that access the data. It's important because of the
savings in time and potential errors caused by reducing modifications to data access
software.
Components of a ER Diagram
Weak Entity:
An entity that cannot be uniquely identified by its own attributes and relies on the
relationship with other entity is called weak entity. The weak entity is represented
by a double rectangle. For example – a bank account cannot be uniquely
identified without knowing the bank to which the account belongs, so bank
account is a weak entity.
2. Attribute
An attribute describes the property of an entity. An attribute is represented as Oval in an
ER diagram. There are four types of attributes:
a) Key attribute
b) Composite attribute
c) Multivalued attribute
d) Derived attribute
a) Key attribute
A key attribute can uniquely identify an entity from an entity set. For example,
student roll number can uniquely identify a student from a set of students.
Key attribute is represented by oval same as other attributes however the text of
key attribute is underlined.
c) Multivalued attribute
An attribute that can hold multiple values is known as multivalued attribute. It is
represented with double ovals in an ER Diagram.
For example – A person can have more than one phone numbers so the phone
number attribute is multivalued.
d) Derived attribute
A derived attribute is one whose value is dynamic and derived from another
attribute. It is represented by dashed oval in an ER Diagram. For example – Person
age is a derived attribute as it changes over time and can be derived from another
attribute (Date of birth).
3. Relationship
A relationship is represented by diamond shape in ER diagram, it shows the relationship
among entities. There are four types of relationships:
a) One to One
b) One to Many
c) Many to One
d) Many to Many
b) One to Many
When a single instance of an entity is associated with more than one instances of
another entity then it is called one to many relationships. For example – a
customer can place many orders but an order cannot be placed by many
customers.
c) Many to One
When more than one instances of an entity is associated with a single instance of
another entity then it is called many to one relationship. For example – many
students can study in a single college but a student cannot study in many colleges
at the same time.
d) Many to Many
When more than one instances of an entity are associated with more than one
instances of another entity then it is called many to many relationships.
For example, a can be assigned to many projects and a project can be assigned to
many students.
Generalization
Generalization is like a bottom-up approach in which two or more entities of lower level
combine to form a higher level entity if they have some attributes in common. In
generalization, an entity of a higher level can also combine with the entities of the lower
level to form a further higher level entity.
Generalization is more like subclass and superclass system, but the only difference is the
approach. Generalization uses the bottom-up approach. In generalization, entities are
combined to form a more generalized entity, i.e., subclasses are combined to make a
superclass. For example, Faculty and Student entities can be generalized and create a
higher level entity Person.
For example: Center entity offers the Course entity act as a single entity in the
relationship which is in a relationship with another entity visitor.
In the real world, if a visitor visits a coaching center then he will never enquiry about the
Course only or just about the Center instead he will ask the enquiry about both.
Structure of RDBMS
A relational database is a type of database. It uses a structure that allows us to identify
and access data in relation to another piece of data in the database. Often, data in a
relational database is organized into tables.
1. Super key
A superkey is a group of single or multiple keys which identifies rows in a table. A Super
key may have additional attributes that are not needed for unique identification.
2. Primary Key
A column or group of columns in a table which helps us to uniquely identifies
every row in that table is called a primary key.
This DBMS can't be a duplicate. The same value can't appear more than once in
the table.
3. Candidate Key
A super key with no repeated attribute is called candidate key.
The Primary key should be selected from the candidate keys. Every table must
have at least a single candidate key.
4. Foreign key
A foreign key is a column which is added to create a relationship with another
table. Foreign keys help us to maintain data integrity and also allows navigation
between two different instances of an entity.
Every relationship in the model needs to be supported by a foreign key.
6. Alternate key
All the keys which are not primary key are called an alternate key. It is a key
which is currently not the primary key.
However, a table may have single or multiple choices for the primary key.
Relational Algebra
Every database management system must define a query language to allow users
to access the data stored in the database.
Relational Algebra is a procedural query language used to query the database
tables to access data in different ways.
In relational algebra, input is a relation (table from which data has to be accessed)
and output is also a relation (a temporary table holding the data asked for by the
user).
The primary operations that we can perform using relational algebra are: Select,
Project, Union, Set Difference, Cartesian Product and Join.
f) Join
Here are the different types of the JOINs in SQL:
• (INNER) JOIN (⋈) : Returns records that have matching values in both tables.
• LEFT (OUTER) JOIN (⟕): Return all records from the left table, and the matched
records from the right table
• RIGHT (OUTER) JOIN (⟖): Return all records from the right table, and the
matched records from the left table
• FULL (OUTER) JOIN (⟗): Return all records when there is a match in either left or
right table
Department
Dept_Id Dept_Name Floor
Advantages of Normalization
• A smaller database can be maintained as normalization eliminates the duplicate
data. Overall size of the database is reduced as a result.
• As databases become lesser in size, the passes through the data becomes faster
and shorter thereby improving response time and speed.
• Avoid redundant fields or columns.
• More flexible data structure i.e. we should be able to ad new rows and data
values easily
• Better understanding of data.
• Easier to maintain data structure i.e. it is easy to perform operations and complex
queries can be easily handled.
Disadvantages of Normalization
• Database systems are complex, difficult, and time-consuming to design.
• Substantial hardware and software start-up costs.
• Initial training required for all programmers and users.
• On Normalizing the relations to higher normal forms i.e. 4NF, 5NF the
performance degrades.
• It is very time consuming and difficult process in normalizing relations of higher
degree.
• Careless decomposition may lead to bad design of database which may leads to
serious problems.
Functional Dependencies
• Functional dependency in DBMS, as the name suggests is a relationship between
attributes of a table dependent on each other.
• Introduced by E. F. Codd, it helps in preventing data redundancy and gets to know
about bad designs.
• For Example, consider the following table,
Multivalued Dependency
• Multivalued dependency occurs when two attributes in a table are independent of
each other but, both depend on a third attribute.
• A multivalued dependency consists of at least two attributes that are dependent
on a third attribute that's why it always requires at least three attributes.
• For example,
Transitive Dependency
• If non-primary key attributes depend upon other non-primary key attributes than
there occurs transitive dependency.
• A transitive is a type of functional dependency which happens when t is indirectly
formed by two functional dependencies.
• A transitive functional dependency is when changing a non-key column, might
cause any of the other non-key columns to change.
• Consider the table, Changing the non-key column Full Name may change
Salutation.
• {Company} -> {CEO} (if we know the company, we know its CEO's name)
• {CEO } -> {Age} If we know the CEO, we know the Age
• Therefore according to the rule of rule of transitive dependency:
• { Company} -> {Age} should hold, that makes sense because if we know the
company name, we can know his age.
• Note: You need to remember that transitive dependency can only occur in a
relation of three or more attributes.
Database Anomalies
• Database anomalies are the problems in relations that occur due to redundancy in
the relations.
• They can occur in poorly planned, un-normalised databases where all the data is
stored in one table.
• These anomalies affect the process of inserting, deleting and modifying data in
the relations.
• Some important data may be lost if a relation is updated that contains database
anomalies.
• It is important to remove these anomalies in order to perform different
processing on the relations without any problem.
Types of Anomalies
• Insertion Anomalies
• Deletion Anomalies
• Modification Anomalies
Insertion Anomaly
• An Insert Anomaly occurs when certain attributes cannot be inserted into the
database without the presence of other attributes.
• For example, we can't add a new course unless we have at least one student
enrolled on the course.
• If we want to add a new course, then student details will become null. So, course
can’t be inserted without having student details. This scenario forms insertion
anomaly.
• For example, consider what happens if Student S13 is the last student to leave the
course - All information about the course is lost.
Modification Anomaly
• The modification anomaly occurs when the record is updated in the relation. In
this anomaly, the modification in the value of specific attribute requires
modification in all records in which that value occurs.
Above table does not satisfy 1NF because column phone contains multiple values. Hence,
we need to create new table contact to store phone numbers.
Example:
Table: Stud_id, Course_id, Stud_name, Course_Name
Where: Primary Key = Stud_id + Course_id
Then: To determine name of student we use only Stud_id, which is part of primary key.
{Stud_id} -> {Stud_Name}
In above table, there is transitive dependency on sname and salutation. Both are non-
primary key attributes. Change in sname might cause change in salutiation. For eg, if we
change name Ram to Maya then we need to change salutiation too.
• In the above table student_id, subject together form the primary key, because
using student_id and subject, we can find all the columns of the table.
• Also, there is a dependency between subject and professor, where subject
depends on the professor name.
• This table satisfies the 1st Normal form because all the values are atomic, column
names are unique and all the values stored in a particular column are of same
domain.
• This table also satisfies the 2nd Normal Form as their is no Partial Dependency.
• And, there is no Transitive Dependency, hence the table also satisfies the 3rd
Normal Form.
• But this table is not in Boyce-Codd Normal Form.
To make this relation(table) satisfy BCNF, we will decompose this table into two tables,
student table and professor table.
Below we have the structure for both the tables.
Introduction to SQL
SQL is a standard language for accessing and manipulating databases.
SQL stands for Structured Query Language
SQL lets you access and manipulate databases
SQL became a standard of the American National Standards Institute (ANSI) in
1986, and of the International Organization for Standardization (ISO) in 1987
Syntax,
Example,
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
Syntax,
DROP TABLE table_name;
The following SQL deletes the "Email" column from the "Customers" table:
SQL Constraints
SQL constraints are used to specify rules for data in a table. Constraints can be specified
when the table is created with the CREATE TABLE statement, or after the table is created
with the ALTER TABLE statement.
Syntax
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);
Altering Constraints
1) Primary Key
To create a PRIMARY KEY constraint on the "ID" column when the table is already
created, use the following SQL:
ALTER TABLE Persons
ADD PRIMARY KEY (ID);
2) Unique Constraint
To create a UNIQUE constraint on the "ID" column when the table is already created, use
the following SQL:
ALTER TABLE Persons
ADD UNIQUE (ID);
3) Foreign Key
To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is
already created, use the following SQL:
ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
4) Check Constraint
To create a CHECK constraint on the "Age" column when the table is already created, use
the following SQL:
5) Default Constraint
To create a DEFAULT constraint on the "City" column when the table is already created,
use the following SQL:
Here, column1, column2, ... are the field names of the table you want to select data from.
If you want to select all the fields available in the table, use the following syntax:
OR Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
NOT Syntax
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
Examples,
SELECT * FROM Customers
WHERE Country='Germany' AND City='Berlin';
The following SQL statement selects all fields from "Customers" where country is NOT
"Germany" and NOT "USA":
Aggregate Functions
SQL MIN() and MAX() Functions
The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.
Syntax,
SELECT MIN(column_name)
FROM table_name
WHERE condition;
SELECT MAX(column_name)
FROM table_name
WHERE condition;
The following SQL statement finds the price of the most expensive product:
Syntax,
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
SELECT AVG(column_name)
FROM table_name
WHERE condition;
SELECT SUM(column_name)
FROM table_name
WHERE condition;
SELECT COUNT(ProductID)
FROM Products;
The following SQL statement finds the average price of all products:
SELECT AVG(Price)
FROM Products;
The following SQL statement finds the sum of the "Quantity" fields in the "OrderDetails"
table:
SELECT SUM(Quantity)
FROM OrderDetails;
Here are some examples showing different LIKE operators with '%' and '_' wildcards:
The following SQL statement selects all customers with a CustomerName starting with
"a":
The following SQL statement selects all customers with a CustomerName ending with "a":
The following SQL statement selects all customers with a CustomerName that have "or"
in any position:
The following SQL statement selects all customers with a CustomerName that have "r" in
the second position:
The following SQL statement selects all customers with a CustomerName that starts with
"a" and are at least 3 characters in length:
The following SQL statement selects all customers with a ContactName that starts with
"a" and ends with "o":
The following SQL statement selects all customers with a CustomerName that does NOT
start with "a":
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
The following SQL statement selects all customers that are located in "Germany",
"France" and "UK":
The following SQL statement selects all customers that are NOT located in "Germany",
"France" or "UK":
The following SQL statement selects all customers that are from the same countries as
the suppliers:
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
The following SQL statement selects all products with a price BETWEEN 10 and 20:
To display the products outside the range of the previous example, use NOT BETWEEN:
The following SQL statement selects all customers from the "Customers" table, sorted by
the "Country" column:
The following SQL statement selects all customers from the "Customers" table, sorted
DESCENDING by the "Country" column:
The following SQL statement selects all customers from the "Customers" table, sorted by
the "Country" and the "CustomerName" column. This means that it orders by Country,
but if some rows have the same Country, it orders them by CustomerName:
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
The following SQL statement lists the number of customers in each country:
The following SQL statement lists the number of customers in each country, sorted high
to low:
The following SQL statement inserts a new record in the "Customers" table:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
The following SQL statement will update the contactname to "Juan" for all records where
country is "Mexico":
UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';
SQL Joins
A JOIN clause is used to combine rows from two or more tables, based on a related
column between them.
Here are the different types of the JOINs in SQL:
(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched
records from the right table
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched
records from the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left or
right table
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
SQL Views
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields
from one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data
as if the data were coming from one single table.
The following SQL creates a view that shows all customers from Brazil:
The following SQL adds the "City" column to the "Brazil Customers" view:
DELIMITER //
CREATE PROCEDURE SelectAllCustomers()
BEGIN
SELECT * FROM customer;
END //
DELIMITER ;
DELIMITER //
CREATE PROCEDURE SelectAllCustomers()
BEGIN
SELECT name,age FROM customer WHERE age>20;
END //
DELIMITER ;
(Only change query for insert, update and delete operations, other contents remain
same)
IN parameters
IN is the default mode. When you define an IN parameter in a stored procedure, the
calling program has to pass an argument to the stored procedure. In addition, the value
of an IN parameter is protected. It means that even the value of the IN parameter is
changed inside the stored procedure, its original value is retained after the stored
procedure ends. In other words, the stored procedure only works on the copy of
the IN parameter.
OUT parameters
The value of an OUT parameter can be changed inside the stored procedure and its new
value is passed back to the calling program. Notice that the stored procedure cannot
access the initial value of the OUT parameter when it starts.
INOUT parameters
An INOUT parameter is a combination of IN and OUT parameters. It means that the
calling program may pass the argument, and the stored procedure can modify
the INOUT parameter, and pass the new value back to the calling program.
Suppose that you want to find offices locating in the USA, you need to pass an
argument (USA) to the stored procedure as shown in the following query:
To get the number of orders that are in-process, you call the stored
procedure GetOrderCountByStatus as follows:
DELIMITER //
CREATE PROCEDURE insertRecords(
IN id INT,
IN nm VARCHAR(30),
IN ad VARCHAR(30)
)
BEGIN
INSERT INTO student VALUES(id,nm,ad);
END //
Executing,
CALL insertRecords(5,'Hari','Ktm');
DELIMITER //
CREATE PROCEDURE updateRecords(
IN id INT,
IN nm VARCHAR(30),
IN ad VARCHAR(30)
)
BEGIN
UPDATE student set name=nm, address=ad WHERE sid=id;
END //
Executing,
CALL updateRecords(5,'Hari','Ktm');
DELIMITER //
CREATE PROCEDURE deleteRecords(
IN id INT
)
BEGIN
UPDATE FROM student WHERE sid=id;
END //
Executing,
CALL deleteRecords(3);
SQL Index
The CREATE INDEX statement is used to create indexes in tables. Indexes are used to
retrieve data from the database very fast. The users cannot see the indexes; they are just
used to speed up searches/queries.
The SQL statement below creates an index named "idx_lastname" on the "LastName"
column in the "Persons" table:
If you want to create an index on a combination of columns, you can list the column
names within the parentheses, separated by commas:
SQL Triggers
A trigger is a stored procedure in database which automatically invokes whenever a
special event in the database occurs. For example, a trigger can be invoked when a row is
inserted into a specified table or when certain table columns are being updated.
Explanation of syntax:
1. create trigger [trigger_name]: Creates or replaces an existing trigger with the
trigger_name.
2. [before | after]: This specifies when the trigger will be executed.
3. {insert | update | delete}: This specifies the DML operation.
4. on [table_name]: This specifies the name of the table associated with the trigger.
5. [for each row]: This specifies a row-level trigger, i.e., the trigger will be executed for
each row being affected.
6. [trigger_body]: This provides the operation to be performed as trigger is fired
Example:
Given Student Report Database, in which student marks assessment is recorded. In such
schema, create a trigger so that the total and average of specified marks is automatically
inserted whenever a record is insert.
Here, as trigger will invoke before record is inserted so, BEFORE Tag can be used.
mysql> insert into Student values(0, "ABCDE", 20, 20, 20, 0, 0);
Query OK, 1 row affected (0.09 sec)
mysql> select * from Student;
+-----+-------+-------+-------+-------+-------+------+
| tid | name | subj1 | subj2 | subj3 | total | per |
+-----+-------+-------+-------+-------+-------+------+
| 100 | ABCDE | 20 | 20 | 20 | 60 | 36 |
+-----+-------+-------+-------+-------+-------+------+
1 row in set (0.00 sec)
In this way trigger can be created and executed in the databases.
Trigger Limitations
Only one INSTEAD OF trigger is allowed for each event type (INSERT, UPDATE, or
DELETE) per table.
If a table has a BEFORE trigger, it cannot have an INSTEAD OF trigger. The reverse
is also true.
If a table has an INSTEAD OF X trigger, AFTER X triggers defined for that table will
not fire (where X is an event type).
The __error table can only have one row. Delete operations are not currently
allowed on the __error table.
VarChar fields are not supported in the __old and __new tables.
When using the Advantage Local Server, if a trigger fails for any reason, the
database is left as is. This means any operations the trigger may have already
performed will be persistent.
Nested and recursive triggers are limited to 64 levels of server re-entrance before
an error is returned.
It refers to the range of activities that are involved in extracting data from the database.
It includes translation of queries in high-level database languages into expressions that
can be implemented at the physical level of the file system. In query processing, we will
actually understand how these queries are processed and how they are optimized.
Selection Operation
File scan – search algorithms that locate and retrieve records that fulfill a selection
condition.
Algorithm A1 (linear search). Scan each file block and test all records to see
whether they satisfy the selection condition.
– Cost estimate (number of disk blocks scanned) = br
- br denotes number of blocks containing records from relation r
– If selection is on a key attribute, cost = (br/ 2) (stop on finding record)
External Sort–Merge
Query Optimization
A single query can be executed through different algorithms or re-written in
different forms and structures.
Hence, the question of query optimization comes into the picture – Which of
these forms or pathways is the most optimal? The query optimizer attempts to
determine the most efficient way to execute a given query by considering the
possible query plans.
A query optimizer is a critical database management system (DBMS) component
that analyses Structured Query Language (SQL) queries and determines efficient
execution mechanisms.
A query optimizer generates one or more query plans for each query, each of
which may be a mechanism used to run a query. The most efficient query plan is
selected and used to run the query.
Database users do not typically interact with a query optimizer, which works in
the background.
First, it provides the user with faster results, which makes the application seem
faster to the user.
Secondly, it allows the system to service more queries in the same amount of
time, because each request takes less time than unoptimized queries.
Thirdly, query optimization ultimately reduces the amount of wear on the
hardware (e.g. disk drives), and allows the server to run more efficiently (e.g.
lower power consumption, less memory usage).
The top responsibility of a DBA professional is to maintain data integrity. This means the
DBA will ensure that data is secure from unauthorized access but is available to users.
A database administrator will often have a working knowledge and experience with a
wide range of database management products such as Oracle-based software, and SQL,
in addition to having obtained a degree in Computer Science and practical field
experience and additional, related IT certifications.
Types of DBA
There are different types of DBAs depending on an organization's requirements:
1. Administrative DBA – maintains the servers and databases and keeps them
running. Concerned with backups, security, patches, replication. These are
activities mostly geared towards maintaining the database and software platform,
but not really involved in enhancing or developing it.
2. Development DBA - works on building SQL queries, stored procedures, and so on,
that meet business needs. This is the equivalent of a programmer, but specializing
in database development. Commonly combined the role of Administrative DBA.
3. Data Architect – designs schemas, builds tables indexes, data structures and
relationships. This role works to build a structure that meets a general business
needs in a particular area.
Database Security
Database security refers to the collective measures used to protect and secure a
database or database management software from illegitimate use and malicious
threats and attacks.
Database security covers and enforces security on all aspects and components of
databases. This includes:
– Data stored in database
– Database server
– Database management system (DBMS)
– Other database workflow applications
Database security is generally planned, implemented and maintained by a
database administrator and or other information security professional.
Access Protection/Control
Access control is a security technique that regulates who or what can view or use
resources in a computing environment. It is a fundamental concept in security that
minimizes risk to the business or organization.
To secure a facility, organizations use electronic access control systems that rely on user
credentials, access card readers, auditing and reports to track employee access to
restricted business locations and proprietary areas, such as data centres.
Advantages
• MAC provides tighter security because only a system administrator may access or
alter controls.
• MAC policies reduce security errors.
Advantages
• ACL maintenance or capability
• Grant and revoke permissions maintenance
Numerous algorithms are used for encryption. These algorithms generate keys
related to the encrypted data.
These keys set a link between the encryption and decryption procedures. The
encrypted data can be decrypted only by using these keys.
Let’s take an example of a simple transaction. Suppose a bank employee transfers Rs 500
from A's account to B's account. This very simple and small transaction involves several
low-level tasks.
1) Active − In this state, the transaction is being executed. This is the initial state of
every transaction.
4) Aborted − If any of the checks fails and the transaction has reached a failed state,
then the recovery manager rolls back all its write operations on the database to
bring the database back to its original state where it was prior to the execution of
ACID Properties
A transaction is a very small unit of a program and it may contain several low-level tasks.
A transaction in a database system must maintain Atomicity, Consistency, Isolation, and
Durability − commonly known as ACID properties − in order to ensure accuracy,
completeness, and data integrity.
1) Atomicity
• This property states that a transaction must be treated as an atomic unit, that
is, either all of its operations are executed or none.
• There must be no state in a database where a transaction is left partially
completed.
• States should be defined either before the execution of the transaction or
after the execution/abortion/failure of the transaction.
• Consider the following transaction T consisting of T1 and T2: Transfer of 100
from account X to account Y.
2) Consistency
• The database must remain in a consistent state after any transaction.
• No transaction should have any adverse effect on the data residing in the
database.
• If the database was in a consistent state before the execution of a transaction, it
must remain consistent after the execution of the transaction as well.
3) Isolation
• In a database system where more than one transaction is being executed
simultaneously and in parallel, the property of isolation states that all the
transactions will be carried out and executed as if it is the only transaction in the
system.
• No transaction will affect the existence of any other transaction.
Suppose T has been executed till Read (Y) and then T’’ starts. As a result,
interleaving of operations takes place due to which T’’ reads correct value of X but
incorrect value of Y and sum computed by
T’’: (X+Y = 50, 000+500=50, 500)
is thus not consistent with the sum at end of transaction:
T: (X+Y = 50, 000 + 450 = 50, 450).
This results in database inconsistency, due to a loss of 50 units. Hence,
transactions must take place in isolation and changes should be visible only after
a they have been made to the main memory.
4) Durability
• The database should be durable enough to hold all its latest updates even if the
system fails or restarts.
• If a transaction updates a chunk of data in a database and commits, then the
database will hold the modified data.
• If a transaction commits but the system fails before the data could be written on
to the disk, then that data will be updated once the system springs back into
action.
Types of Serializability
There are two types of Serializability –
1. Conflict Serializability
2. View Serializability
1) Conflict Serializability
Conflict Serializability is one of the type of Serializability, which can be used to check
whether a non-serial schedule is conflict serializable or not. A schedule is called conflict
serializable if we can convert it into a serial schedule after swapping its non-conflicting
operations.
Conflicting operations
Two operations are said to be in conflict, if they satisfy all the following three conditions:
1. Both the operations should belong to different transactions.
2. Both the operations are working on same data item.
3. At least one of the operation is a write operation.
To convert this schedule into a serial schedule we must have to swap the R(A) operation
of transaction T2 with the W(A) operation of transaction T1.
However, we cannot swap these two operations because they are conflicting operations,
thus we can say that this given schedule is not Conflict Serializable.
2) View Serializability
View Serializability is a process to find out that a given schedule is view serializable or not.
Two schedules S1 and S2 are said to be view equal if below conditions are satisfied:
a) Initial Read
If a transaction T1 reading data item A from initial database in S1 then in S2 also
T1 should read A from initial database.
b) Updated Read
If Ti is reading A which is updated by Tj in S1 then in S2 also Ti should read A
which is updated by Tj.
Concurrency Control
Concurrency control is the procedure in DBMS for managing simultaneous
operations without conflicting with each another.
Concurrent access is quite easy if all users are just reading data. There is no way
they can interfere with one another.
Though for any practical database, would have a mix of reading and WRITE
operations and hence the concurrency is a challenge.
Concurrency control is used to address such conflicts which mostly occur with a
multi-user system.
It helps you to make sure that database transactions are performed concurrently
without violating the data integrity of respective databases.
Therefore, concurrency control is a most important element for the proper
functioning of a system where two or multiple database transactions that require
access to the same data, are executed simultaneously.
Binary Locks: A Binary lock on a data item can either locked or unlocked states.
Shared/exclusive: This type of locking mechanism separates the locks based on
their uses. If a lock is acquired on a data item to perform a write operation, it is
called an exclusive lock.
Deadlock Handling
Deadlock refers to a specific situation where two or more processes are waiting
for each other to release a resource or more than two processes are waiting for
the resource in a circular chain.
A deadlock is a condition where two or more transactions are waiting indefinitely
for one another to give up locks.
Deadlock is said to be one of the most feared complications in DBMS as no task
ever gets finished and is in waiting state forever.
Deadlock Avoidance
When a database is stuck in a deadlock state, then it is better to avoid the
database rather than aborting or restating the database. This is a waste of time
and resource.
Deadlock avoidance mechanism is used to detect any deadlock situation in
advance. A method like "wait for graph" is used for detecting the deadlock
situation but this method is suitable only for the smaller database. For the larger
database, deadlock prevention method can be used.
Deadlock Prevention
• Deadlock prevention method is suitable for a large database. If the resources are
allocated in such a way that deadlock never occurs, then the deadlock can be
prevented.
• The Database management system analyzes the operations of the transaction
whether they can create a deadlock situation or not. If they do, then the DBMS
never allowed that transaction to be executed.
Deadlock Detection
In a database, when a transaction waits indefinitely to obtain a lock, then the
DBMS should detect whether the transaction is involved in a deadlock or not.
The lock manager maintains a Wait for the graph to detect the deadlock cycle in
the database.
Unit – I
1. What is DBMS? Explain features of DBMS.
2. Differentiate database and DBMS.
3. Explain advantages and disadvantages of DBMS.
4. Explain objective and importance of DBMS.
5. List and explain application of DBMS.
6. Differentiate traditional flat file system and database.
Unit –II
7. What do you mean by database design? Explain overall database designing
process in detail.
8. What is abstraction? List and explain different levels of abstraction.
9. Explain structure of DBMS.
10. What do you mean by Data Independence? Explain physical and logical data
independence in detail.
11. What is database architecture? Explain 1 tier, 2 tier and 3 tier database
architecture.
12. Differentiate DDL and DML with examples.
13. What do you mean by data model? Explain different data models in detail.
14. What is QBE? Explain working and advantages of QBE.
15. Define entity and attributes. Differentiate strong and weak entity set.
16. Explain aggregation and generalization with examples.
17. Explain the process of converting ER Diagrams to Tables.
18. Construct an E-R diagram for a car insurance company whose customers own one
or more cars each. Each car has associated with it zero to any number of recorded
accidents. Each insurance policy covers one or more cars, and has one or more
premium payments associated with it. Each payment is for a particular period of
time, and has an associated due date, and the date when the payment was
received.
19. Design a database for an automobile company to provide to its dealers to assist
them in maintaining customer records and dealer inventory and to assist sales
staff in ordering cars. Each vehicle is identified by a vehicle identification
number(VIN). Each individual vehicle is a particular model of a particular brand
offered by the company (e.g., the XF is a model of the car brand Jaguar of Tata
Motors). Each model can be offered with a variety of options, but an individual car
may have only some (or none) of the available options. The database needs to
store information about models, brands, and options, as well as information
about individual dealers, customers, and cars. Your design should include an E-R
diagram, a set of relational schemas, and a list of constraints, including primary-
key and foreign-key constraints.
20. Design a database for an airline. The database must keep track of customers and
their reservations, flights and their status, seat assignments on individual flights,
and the schedule and routing of future flights. Your design should include an E-R
Unit – III
22. What is RDBMS? Explain the structure of RDBMS with example.
23. Define database schema. Develop database schema for the following:
a) Hotel Management System
b) Library Management System
c) Bus Ticketing System
24. What do you mean by key? Explain different types of keys with example.
25. What is Relational Algebra? Explain different operations performed in relational
algebra with examples.
26. Write relational algebra for the following relations:
Employee
Emp_Id Name Address Salary Dept_Id
Department
Dept_Id Dept_Name Floor
i. Select name and address of employees whose salary is between 10000 and
20000.
ii. Select employee id, employee name and department name of employees
working in first floor.
iii. Select all records of department which are in second floor.
iv. Select name, address and department name of employees which are from
Birtamode.
v. Select employee id and name of employees having salary more than 10000
and from Kathmandu.
Unit – IV
27. What is Normalization? Explain advantages and disadvantages of Normalization.
28. What are differernt anomalies? Explain each of them with suitable examples.
29. What is functional dependency? Explain different types of functional
dependencies with example.
30. Explain 1NF, 2NF, 3NF, BCNF and 4NF with suitable example.
31. Convert the following:
Unit – VIII
42. Explain query processing with suitable diagram. How query cost is measured?
43. Explain different algorithms used for selection operation.
44. Explain sorting operation with the help of external merge sort.
45. What do you mean by query optimization? Explain query optimization process.
46. Define query optimizer. Explain importance of query optimization.
47. What do you mean by DBA? Explain different types of DBA.
48. What are the different roles and responsibilities of DBA?
49. Define database security. What are the different database security issues?
50. What are the different types of database security? Differentiate MAC and DAC
with example.
51. What do you mean by encryption and decryption? Explain public key and secret
key cryptography with example.
Department
Dept_Id Dept_Name Floor
47. Use all types of joins to select employee id, name and department name of
employees.
48. Select name and address of employees whose salary is between 10000 and 20000.
49. Select employee id, employee name and department name of employees working
in first floor.
50. Select all records of department which are in second floor.
51. Select name, address and department name of employees which are from
Birtamode.
52. Select employee id and name of employees having salary more than 10000 and
from Kathmandu.
53. Select name, department name and floor of employee whose name start with
letter ‘R’ and age is greater than 30.
54. Select employee id and department name of employees whose floor is ‘first’ by
arranging in ascending order on the basis of salary.
55. Select total number of employee working in each department.
Lab Sheet – 2
Consider the following table,
Employee
Emp_Id Name Address Salary Dept_Id
1. Create a view named Birtamode Employees that shows Employee id, name and
address of employees having address Birtamode.
2. Create a view named Our Salary that shows employee name and salary whose
salary is greater than 20,000.
3. Write SQL query to drop view Our Salary.
4. Write SQL query to add column salary on view Birtamode Employees.
5. Write SQL query to create stored procedure named SelectRecords that selects all
records from Employee table.
6. Write SQL query to create stored procedure named MyEmployees that selects
employees records of a particular address.
7. Write SQL query to create stored procedure named MyEmployees1 that selects
employees records of a particular address and department id.
8. Write SQL query to drop above stored procedure SelectRecords.
9. Write SQL query to insert records in above table using stored procedure.
10. Write SQL query to delete record of employee whose employee id is given in
parameter using stored procedure.
11. Write SQL query to update name and address of employee on the basis of salary.
(Here, name, address and salary is given in parameter)
12. Write SQL query to update name and salary of employee on the basis of employee
id and salary. (Here, name, employee id and salary is given in parameter)
13. Write SQL query to display maximum salary returned by stored procedure.
14. Write SQL query to display average salary of employees returned by stored
procedure.
15. Write SQL query to create a trigger named MyTrigger.
16. Write SQL query to drop above trigger.
17. Write SQL query to create index named MyIndex on name of employee.
18. Write SQL query to create index named MyIndex1 on address and salary of
employee.
19. Write SQL query to drop index MyIndex.
20. Write SQL query to drop index MyIndex1.