Unit2 Dbms (Long)
Unit2 Dbms (Long)
Unit2 Dbms (Long)
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.
CSE
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-
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.
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:
DML is used for accessing and manipulating data in a database. The following operations on
database comes under DML:
Thechanges in the database that we made using DML commands are either performed or
rollbacked using TCL.
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.
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:
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”.
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.
SELECT (symbol: σ)
PROJECT (symbol: π)
RENAME (symbol: )
UNION (υ)
INTERSECTION ( ),
DIFFERENCE (-)
CARTESIAN PRODUCT ( x )
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
p is prepositional logic
Example 1
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:
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:
- Symbol denotes it. The result of A - B, is a relation which includes all tuples that are in A but
not in B.
Intersection
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
σ column 2 = '1' (A X B)
Output – The above example shows all rows from relation A and B whose column 2 has value 1
Join Operations
JOIN operation also allows joining variously related tuples from different relations.
Types of JOIN:
Inner Joins:
Theta join
EQUI join
Natural join
Outer 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 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
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.
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.
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.
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:
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-
Example-
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
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.