Unit2 Dbms (Long)

Download as docx, pdf, or txt
Download as docx, pdf, or txt
You are on page 1of 37

1)EXPLAIN INTEGRITY CONSTRAINTS OVER RELATION

Integrity constraints are used to ensure accuracy and consistency of data in a relational
database.
 Database integrity refers to the validity and consistency of stored data. Integrity is usually
expressed in terms of constraints, which are consistency rules that the database is not
permitted to violate. Constraints may apply to each attribute or they may apply to
relationships between tables.
 Integrity constraints ensure that changes (update deletion, insertion) made to the database
by authorized users do not result in a loss of data consistency. Thus, integrity constraints
guard against accidental damage to the database.
TYPES OF INTEGRITY CONSTRAINTS
Various types of integrity constraints are-
1. Domain Integrity
2. Entity Integrity Constraint
Referential Integrity Constraint

3. Key Constraints

Domain Integrity-
Domain integrity means the definition of a valid set of values for an attribute. You define
data type, length or size, is null value allowed , is the value unique or not for an attribute ,the
default value, the range (values in between) and/or specific values for the attribute.
Entity Integrity Constraint-
This rule states that in any database relation value of attribute of a primary key can't be null.
EXAMPLE- Consider a relation "STUDENT" Where "Stu_id" is a primary key and it must not
contain any null value whereas other attributes may contain null value e.g "Branch" in the
following relation contains one null value.
3.Referential Integrity Constraint-
It states that if a foreign key exists in a relation then either the foreign key value must match a
primary key value of some tuple in its home relation or the foreign key value must be null.
The rules are:

1. You can't delete a record from a primary table if matching records exist in a related table.
2. You can't change a primary key value in the primary table if that record has related
records.
3. You can't enter a value in the foreign key field of the related table that doesn't exist in the
primary key of the primary table.
4. However, you can enter a Null value in the foreign key, specifying that the records are
unrelated.

S_id name branch


11255234 Aman

CSE

111 XYZ CSE


112 YXZ IT

EXAMPLE-
Consider 2 relations "stu" and "stu_1" Where "Stu_id " is the primary key in the "stu" relation
and foreign key in the "stu_1" relation.
Relation "stu"
Examples
Rule 1. You can't delete any of the rows in the ”stu” relation that are visible since all the ”stu”
are in use in the “stu_1” relation.
Rule 2. You can't change any of the ”Stu_id” in the “stu” relation since all the “Stu_id” are in use
in the ”stu_1” relation. * Rule 3.* The values that you can enter in the” Stu_id” field in the
“stu_1” relation must be in the” Stu_id” field in the “stu” relation.
Rule 4 You can enter a null value in the "stu_1" relation if the records are unrelated.
Key Constraints-
A Key Constraint is a statement that a certain minimal subset of the fields of a relation is a
unique identifier for a tuple. The types of key constraints-

1. Primary key constraints


2. Unique key constraints
3. Foreign Key constraints
4. NOT NULL constraints

1. Primary key constraints


Primary key is the term used to identify one or more columns in a table that make a row of data
unique. Although the primary key typically consists of one column in a table, more than one
column can comprise the primary key.
For example, either the employee's Social Security number or an assigned employee
identification number is the logical primary key for an employee table. The objective is for every
record to have a unique primary key or value for the employee's identification number. Because
there is probably no need to have more than one record for each employee in an employee table,
the employee identification number makes a logical primary key. The primary key is assigned at
table creation.
The following example identifies the EMP_ID column as the PRIMARY KEY for the
EMPLOYEES table:

3. Foreign Key Constraints


A foreign key is a column in a child table that references a primary key in the parent
table. A foreign key constraint is the main mechanism used to enforce referential integrity
between tables in a relational database. A column defined as a foreign key is used to
reference a column defined as a primary key in another table.

Unique Constraints
A unique column constraint in a table is similar to a primary key in that the value in that column
for every row of data in the table must have a unique value. Although a primary key constraint is
placed on one column, you can place a unique constraint on another column even though it is not
actually for use as the primary key.
. NOT NULL Constraints

NOT NULL is a constraint that you can place on a table's column. This constraint disallows the
entrance of NULL values into a column; in other words, data is required in a NOT NULL
column for each row of data in the table. NULL is generally the default for a column if NOT
NULL is not specified, allowing NULL values in a column.

2.Explain briefly the language supported by database?


Database languages are used to read, update and store data in a database. There are several such
languages that can be used for this purpose; one of them is SQL (Structured Query Language).

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:

 To create the database instance – CREATE


 To alter the structure of database – ALTER
 To drop database instances – DROP
 To delete tables in a database instance – TRUNCATE
 To rename database instances – RENAME
 To drop objects from database such as tables – DROP
 To Comment – Comment

Data Manipulation Language (DML)

DML is used for accessing and manipulating data in a database. The following operations on
database comes under DML:

 To read records from table(s) – SELECT


 To insert record(s) into the table(s) – INSERT
 Update the data in table(s) – UPDATE
 Delete all the records from the table – DELETE

Data Control language (DCL)

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

 To grant access to user – GRANT


 To revoke access from user – REVOKE

Transaction Control Language(TCL)

Thechanges in the database that we made using DML commands are either performed or
rollbacked using TCL.

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


 To rollback the changes made to the database – ROLLBACK
3. Explain in detail about views?
SQL Views
A VIEW is a virtual table, through which a selective portion of the data from one or more
tables can be seen. Views do not contain data of their own. They are used to restrict access
to the database or to hide data complexity. A view is stored as a SELECT statement in the
database. DML operations on a view like INSERT, UPDATE, DELETE affects the data in
the original table upon which the view is based.
 view_name is the name of the VIEW.
 The SELECT statement is used to define the columns and rows that you want to display
in the view.
For Example: to create a view on the product table the sql query would be like

SQL Updating a View


CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

SQL Dropping a View


DROP VIEW view_name;

Creating View from multiple tables: In this example we will create a View named MarksView
from two tables StudentDetails and StudentMarks.

To create a View from multiple tables we can simply include multiple tables in the SELECT
statement.

To display data of View MarksView:

Output:
4.How destroying and altering views can be done in views?

DELETING VIEWS

We have learned about creating a View, but what if a created View is not needed any more?
Obviously we will want to delete it. SQL allows us to delete an existing View. We can delete or
drop a View using the DROP statement.

Syntax:

For example, if we want to delete the View MarksView, we can do this as:

We can use the CREATE OR REPLACE VIEW statement to add or remove fields from a
view.

Syntax:

Inserting a row in a view:


We can insert a row in a View in a same way as we do in a table. We can use the INSERT INTO
statement of SQL to insert a row in a View.

Syntax:

Example:

In the below example we will insert a new row in the View DetailsView which we have created
above in the example of “creating views from a single table”.

If we fetch all the data from DetailsView now as,


Deleting a row from a View

Deleting rows from a view is also as simple as deleting rows from a table. We can use the
DELETE statement of SQL to delete rows from a view. Also deleting a row from a view first
delete the row from the actual table and the change is then reflected in the view.

Syntax:

Example:
In this example we will delete the last row from the view DetailsView which we just added in the
above example of inserting rows.
4.Write short notes on in detail about the operators of relational algebra.
Relational algebra is a procedural query language, which takes instances of relations as input and
yields instances of relations as output. It uses operators to perform queries. An operator can be
either unary or binary. They accept relations as their input and yield relations as their output.

Basic Relational Algebra Operations:

Relational Algebra devided in various groups

Unary Relational Operations

 SELECT (symbol: σ)
 PROJECT (symbol: π)
 RENAME (symbol: )

Relational Algebra Operations From Set Theory

 UNION (υ)
 INTERSECTION ( ),
 DIFFERENCE (-)
 CARTESIAN PRODUCT ( x )

Binary Relational Operations

 JOIN
 DIVISION

SELECT (σ)

The SELECT operation is used for selecting a subset of the tuples according to a given selection
condition. Sigma(σ)Symbol denotes it. It is used as an expression to choose tuples which meet
the selection condition. Select operation selects tuples that satisfy a given predicate.

σp(r)

σ is the predicate

r stands for relation which is the name of the table

p is prepositional logic
Example 1

Output - Selects tuples from Tutorials where topic = 'Database'.

Example 2

Projection(π)

The projection eliminates all attributes of the input relation but those mentioned in the projection
list. The projection method defines a relation that contains a vertical subset of Relation.

This helps to extract the values of specified attributes to eliminates duplicate values. (pi) The
symbol used to choose attributes from a relation. This operation helps you to keep specific
columns from a relation and discards the other columns.

Example of Projection:

Consider the following table


Union operation (υ)

UNION is symbolized by ∪ symbol. It includes all tuples that are in tables A or in B. It also
eliminates duplicate tuples. So, set A UNION set B would be expressed as:

The result <- A ∪ B

For a union operation to be valid, the following conditions must hold -

 R and S must be the same number of attributes.


 Attribute domains need to be compatible.
 Duplicate tuples should be automatically removed.

Consider the following tables.


Set Difference (-)

- Symbol denotes it. The result of A - B, is a relation which includes all tuples that are in A but
not in B.

 The attribute name of A has to match with the attribute name in B.


 The two-operand relations A and B should be either compatible or Union compatible.
 It should be defined relation consisting of the tuples that are in relation A, but not in B.
Example

Intersection

An intersection is defined by the symbol ∩

A∩B

Defines a relation consisting of a set of all tuple that are in both A and B. However, A and B
must be union-compatible.

Example:

Cartesian product(X)
This type of operation is helpful to merge columns from two relations. Generally, a Cartesian
product is never a meaningful operation when it performs alone

Example – Cartesian product

σ column 2 = '1' (A X B)

Output – The above example shows all rows from relation A and B whose column 2 has value 1

5.Write in detail about joins operations in relational algebra?

Join Operations

Join operation is essentially a cartesian product followed by a selection criterion.

join operation denoted by ⋈.

JOIN operation also allows joining variously related tuples from different relations.

Types of JOIN:

Various forms of join operation are:

Inner Joins:

 Theta join
 EQUI join
 Natural join

Outer join:

 Left Outer Join


 Right Outer Join
 Full Outer Join
Inner Join:

In an inner join, only those tuples that satisfy the matching criteria are included, while the rest
are excluded. Let's study various types of Inner Joins:

Theta Join:

The general case of JOIN operation is called a Theta join. It is denoted by symbol θ

Example

EQUI join:

When a theta join uses only equivalence condition, it becomes aequi join.

For example:
EQUI join is the most difficult operations to implement efficiently in an RDBMS and one reason
why RDBMS have essential performance problems.

NATURAL JOIN (⋈)

Natural join can only be performed if there is a common attribute (column) between the
relations. The name and type of the attribute must be same.

Example

Consider the following two tables


OUTER JOIN

In an outer join, along with tuples that satisfy the matching criteria, we also include some or all
tuples that do not match the criteria.

Left Outer Join(A B)

In the left outer join, operation allows keeping all tuple in the left relation. However, if there is
no matching tuple is found in right relation, then the attributes of right relation in the join result
are filled with null values.
Right Outer Join: ( A B)

In the right outer join, operation allows keeping all tuple in the right relation. However, if there is
no matching tuple is found in the left relation, then the attributes of the left relation in the join
result are filled with null values.
Full Outer Join: ( A B)

In a full outer join, all tuples from both relations are included in the result, irrespective of the
matching condition.

6.Explain Tuple relational calculus?


Tuple Relational Calculus (TRC) in DBMS

Tuple Relational Calculus is a non-procedural query language unlike relational algebra. Tuple
Calculus provides only the description of the query but it does not provide the methods to solve
it. Thus, it explains what to do but not how to do.
In Tuple Calculus, a query is expressed as

P(t) may have various conditions logically combined with OR (∨), AND (∧), NOT(¬).
It also uses 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(t) is true “for all” tuples in relation r.

Example:
Table-1: Customer
Table-6: Depositor

Queries-1: Find the loan number, branch, amount of loans of greater than or equal to 10000
amount.
Resulting relation:

Queries-2: Find the loan number for each loan of an amount greater or equal to 10000.

7.Explain in detail about domain relational calculus?

Domain Relational Calculus in DBMS

Domain Relational Calculus is a non-procedural query language equivalent in power to Tuple


Relational Calculus. Domain Relational Calculus provides only the description of the query but it
does not provide the methods to solve it. In Domain Relational Calculus, a query is expressed as,
where, < x1, x2, x3, …, xn > represents resulting domains variables and P (x1, x2, x3, …, xn )
represents the condition or formula equivalent to the Predicate calculus.

Predicate Calculus Formula:


1. Set of all comparison operators
2. Set of connectives like and, or, not
3. Set of quantifiers
Example:Table-1: Customer

Table-2: Loan

Table-3: Borrower
Resulting relation:

Query-2: Find the loan number for each loan of an amount greater or equal to 150.
Query-3: Find the names of all customers having a loan at the “Main” branch and find the loan
amount .

8.What is null attribute? With suitable diagram explain weak and strong
entity set?

SQL supports a special value known as NULL which is used to represent the values of attributes
that may be unknown or not apply to a tuple. For example, the Apartment_number attribute of an
address applies only to address that are in apartment buildings and not to other types of
residences.
 It is important to understand that a NULL value is different from zero value.
 A NULL value is used to represent a missing value, but that it usually has one of three
different interpretations:
 Value unknown (value exists but is not known)
 Value not available (exists but is purposely withheld)
 Attribute not applicable (undefined for this tuple)
 It is often not possible to determine which of the meanings is intended. Hence, SQL does
not distinguish between the different meanings of NULL.
In general, each NULL value is considered to be different from every other NULL in the
database. When a NULL is involved in a comparison operation, the result is considered to be
UNKNOWN. Hence, SQL uses a three-valued logic with values True, False and Unknown. It is,
therefore, necessary to define the results of three-valued logical expressions when the logical
connectives AND, OR, and NOT are used.
SQL allows queries that check whether an attribute value is NULL. Rather than using = or to
compare an attribute value to NULL, SQL uses IS and IS NOT. This is because SQL considers
each NULL value as being distinct from every other NULL value, so equality comparison is not
appropriate.
Examples:
Consider the following Employee Table,
weak and strong entity set:

Weak Entity Set in ER diagrams

An entity type should have a key attribute which uniquely identifies each entity in the entity set,
but there exists some entity type for which key attribute can’t be defined. These are called Weak
Entity type.
The entity sets which do not have sufficient attributes to form a primary key are known as weak
entity sets and the entity sets which have a primary key are known as strong entity sets.
As the weak entities do not have any primary key, they cannot be identified on their own, so they
depend on some other entity (known as owner entity). The weak entities have total participation
constraint (existence dependency) in its identifying relationship with owner identity. Weak entity
types have partial keys. Partial Keys are set of attributes with the help of which the tuples of the
weak entities can be distinguished and identified.

Note – Weak entity always has total participation but Strong entity may not have total
participation.
Weak entity is depend on strong entity to ensure the existence of weak entity. Like strong entity,
weak entity does not have any primary key, It has partial discriminator key. Weak entity is
represented by double rectangle. The relation between one strong and one weak entity is
represented by double diamond.

Weak entities are represented with double rectangular box in the ER Diagram and the identifying
relationships are represented with double diamond. Partial Key attributes are represented with
dotted lines.

Example-1:
In the below ER Diagram, ‘Payment’ is the weak entity. ‘Loan Payment’ is the identifying
relationship and ‘Payment Number’ is the partial key. Primary Key of the Loan along with the
partial key would be used to identify the records.
Example-2:
The existence of rooms is entirely dependent on the existence of a hotel. So room can be seen as
the weak entity of the hotel.
Example-3:
The bank account of a particular bank has no existence if the bank doesn’t exist anymore.
Example-4:
A company may store the information of dependants (Parents, Children, Spouse) of an
Employee. But the dependents don’t have existence without the employee. So Dependent will be
weak entity type and Employee will be Identifying Entity type for Dependant.
1. Strong Entity Set-

 A strong entity set is an entity set that contains sufficient attributes to uniquely identify all its
entities.
 In other words, a primary key exists for a strong entity set.
 Primary key of a strong entity set is represented by underlining it.

Symbols Used-

 A single rectangle is used for representing a strong entity set.


 A diamond symbol is used for representing the relationship that exists between two strong
entity sets.
 A single line is used for representing the connection of the strong entity set with the
relationship set.
 A double line is used for representing the total participation of an entity set with the
relationship set.
 Total participation may or may not exist in the relationship.

Example-

Consider the following ER diagram-


In this ER diagram,
 Two strong entity sets “Student” and “Course” are related to each other.
 Student ID and Student name are the attributes of entity set “Student”.
 Student ID is the primary key using which any student can be identified uniquely.
 Course ID and Course name are the attributes of entity set “Course”.
 Course ID is the primary key using which any course can be identified uniquely.
 Double line between Student and relationship set signifies total participation.
 It suggests that each student must be enrolled in at least one course.
 Single line between Course and relationship set signifies partial participation.
 It suggests that there might exist some courses for which no enrollments are made.

9.How we can convert ER Diagram into Tables using generalization and


specification?
Generalization
in our Employee example, we have seen different types of employees like Engineer, Accountant,
Salesperson, Clerk etc. Similarly each employee belongs to different departments. We can
represent it in an ER diagram as below. When you see this diagram for the first time, you will
not understand it quickly. One will take time to understand it or he might misunderstand some
requirement.
What if we group all the sub departments into one department and different employees into one
employee? However sub departments and different employee types have same features in their
own domain. So if we merge the child entities into their parent, it makes the diagram simpler,
hence easy to understand. This method of merging the branches into one is called generalization.
We can see the generalized structure of requirement to understand it quickly. So above ER
diagram will be changed to as below:

Specialization

It is opposite approach of generalization. Here, each entity is further divided into sub levels to
understand it deeper. In the above example, Department entity is further divided into sub
departments to understand how they are scattered. This method of representation helps the
developer to code correctly and quickly. It is a top down approach of breaking higher level entity
to low level entity. Once the entities are understood at higher level, it makes easy to understand
the requirement at low level.
One more example of specialization would be Person. We can further divide person as
STUDENT, TEACHER, ENGINEER, SOLDIER etc. (Merging STUDENT, TEACHER,
ENGINEER etc into PERSON is an example of generalization).

Aggregation

Look at below ER diagram of STUDENT, COURSE and SUBJECTS. What does it infer?
Student attends the Course, and he has some subjects to study. At the same time, Course offers
some subjects. Here a relation is defined on a relation. But ER diagram does not entertain such a
relation. It supports mapping between entities, not between relations. So what can we do in this
case?

If we look at STUDENT and COURSE from SUBJECT’s point of view, it does not differentiate
both of them. It offers it’s subject to both of them. So what can we do here is, merge STUDENT
and COURSE as one entity. This process of merging is called aggregation. It is completely
different from generalization. In generalization, we merge entities of same domain into one
entity. In this case we merge related entities into one entity.
Here we have merged STUDENT and COURSE into one entity STUDENT_COURSE. This new
entity forms the mapping with SUBJECTS. The new entity STUDENT_COURSE, in turn has
two entities STUDENT and COURSE with ‘Attends’ relationship.

Short answers

1.List the primitive operators in Relational Algebra.

2.Write brief notes on altering tables and views.

3.Explain integrity constraints over relations

4.Create a table with employee details like eno, ename, bdate, address, dno, age, phone number.
List the name. eno, dname and phone number of the employee who are also the managers of the
respective departments.

5.With a suitable example explain division operation in relational algebra.

You might also like