Download Database management system and more Lecture notes Database Management Systems (DBMS) in PDF only on Docsity!
UNIT – II
Introduction to the Relational Model: Integrity constraint over relations, enforcing integrity constraints, querying relational data, logical data base design, introduction to views, destroying/altering tables and views. Relational Algebra, Tuple relational Calculus, Domain relational calculus.
1. RELATIONAL MODEL
Relational data model is the most popular data model used widely around the world for data storage. In this model data is stored in the form of tables.
Relational Model Concepts
Table is also called Relation. Let the below table name be SUDENT_DATA Attribute / Column / Field Degree = No of columns = 4
Tuple / Row / Record Cardinality = No of rows = 3
Table: In relational model the data is saved in the form of tables. A table has two properties rows and columns. Rows represent records and columns represent attributes. Attribute: Each column in a Table is an attribute. Attributes are the properties that define a relation. e.g., HTNO, NAME, AGE, CITY in the above relation. Tuple: Every single row of a table is called record or tuple. Relation Schema: It represents the name of the relation (Table) with its attributes. Eg., STUDENT_DATA( htno, name, age, city) Degree: The total number of attributes in the relation is called the degree of the relation. Cardinality: Total number of rows present in the Table.
htno Name age city 501 Amar 19 Hyderabad 502 Akbar 18 Warngal 503 Antony 19 Karimnagar
2. INTEGRITY CONSTRAINT
Integrity constraints are a set of rules that the database should not violate. Integrity constraints ensure that authorized changes (update deletion, insertion) made to the database should not affect data consistency. Integrity constraints may apply to attribute or to relationships between tables.
TYPES OF INTEGRITY CONSTRAINTS The integrity constraints supported by DBMS are:
- Domain Integrity Constraint
- Entity Integrity Constraint
- Referential Integrity Constraint
- Key Constraints
Domain Constraint: These are attribute level constraints. An attribute can only take
values which lie inside the domain range. Example: If a constrain AGE > 0 is applied on STUDENT relation, inserting negative value of AGE will result in failure. If the domain of AGE is defined as integer , inserting an alphabet in age column is not accepted. Example:
Not allowed. Because AGE is an integer attribute
Entity integrity constraints: The entity integrity constraint states that primary key
value can't be null. This is because the primary key value is used to identify individual rows in relation. A table can contain a null value other than the primary key field.
ID NAME SEMESTER AGE
1001 TOM I 18
1002 JHONSON IV 20
1003 KATE VI 21
1004 JHON II 19
1005 MORGAN II A
Integrity constraint
Domain constraint
Entity Integrity constraint
Referential Integrity constraint
Key constraint
iv. Foreign key: It is a key used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. Composite Key: If any single attribute of a table is not capable of being the key i.e it cannot identify a row uniquely, then we combine two or more attributes to form a key. This is known as a composite key. Secondary Key: Only one of the candidate keys is selected as the primary key. The rest of them are known as secondary keys.
3. ENFORCING INTEGRITY CONSTRAINTS
Database Constraints are declarative integrity rules of defining table structures. They include the following 7 constraint types:
- Data type constraint: This defines the type of data, data length, and a few other attributes which are specifically associated with the type of data in a column.
- Default constraint: This defines what value the column should use when no value has been supplied explicitly when inserting a record in the table.
- Nullability constraint: This defines that if a column is NOT NULL or allow NULL values to be stored in it.
- Primary key constraint: This is the unique identifier of the table. Each row must have a distinct value. The primary key can be either a sequentially incremented integer number or a natural selection of data that represents what is happening in the real world (e.g. Social Security Number). NULL values are not allowed in primary key values.
- Unique constraint: This defines that the values in a column must be unique and no duplicates should be stored. Sometimes the data in a column must be unique even though the column does not act as Primary Key of the table. Only one of the values can be NULL.
- Foreign key constraint: This defines how referential integrity is enforced between two tables.
- Check constraint: This defines a validation rule for the data values in a column so it is a user-defined data integrity constraint. This rule is defined by the user when designing the column in a table.
4. LOGICAL DATABASE DESIGN
- Each entity in the ER model will become a table and all attributes of that entity will become columns of the table. Key attribute of the entity will become primary key in the table. Example:
Employee Department EmpID EName salary
CREATE TABLE Employee ( EmpID NUMBER(3), EName VARCHAR(20), Salary NUMBER(5), PRIMARY KEY(EmpID) );
CREATE TABLE Department ( DeptID NUMBER(3), DName VARCHAR(15), Location VARCHAR(15), PRIMARY KEY(DeptID) );
- Each relationship in the ER model will become a table. Key attributes of participating entities in the relationship will become columns of the table. If the relationship has any attributes, then they also will become columns of the table. Example: From the above ER diagram, the Works_In relationship converted as Employee Department
Works_In
CREATE TABLE Works_In ( EmpID NUMBER(3), DeptID NUMBER(3), Since PRIMARY KEY(EmpID, DeptID), DATE, FOREIGN KEY (EmpID) REFERENCES Employee(EmpID), FOREIGN KEY (DeptID) REFERENCES Department(DeptID), );
DeptID DName Location
EmpID EName Salary DeptID^ DName^ Location
EmpID DeptID since
EmpID Employee Works_In Department
EName salary since DeptID (^) DName
Location
5. INTRODUCTION TO VIEWS
A view is virtual tables whose rows are not explicitly stored in the database but are computed as needed from a view definition. They are used to restrict access to the database or to hide data complexity. A view contains rows and columns, just like a real table. Creating a view does not take any storage space as only the view query is stored in the data dictionary and the actual data is not stored. The tables referred in the views are known as Base tables. Views do not contain data of their own. They take data from the base tables.
The reasons for using views are
Security is increased - sensitive information can be excluded from a view. Views can represent a subset of the data contained in a table. Views can join and simplify multiple tables into a single virtual table. Views take very little space to store; the database contains only the definition of a view, not a copy of all the data it presents. Different views can be created on the same base table for different categories of users.
Creating Views syntax:
Examples: Consider the below given employees table. employees(eid, name, salary, experience) employees eid ename salary Experience 101 Jhon 20000 2 105 Sam 18000 2 108 Ram 30000 4
If we want to hide the salary column from accessing a group of users, then we can create view on employees table as follows. CREATE VIEW emp AS SELECT eid, ename, experience FROM employees ; emp eid Name Experience 101 Jhon 2 105 Sam 2 108 Ram 4
CREATE VIEW view_name AS SELECT column_list FROM table_name [ WHERE condition] ;
The view emp is a virtual table. The data in the emp table is not saved in the database but collected from employees table whenever emp table is referred in SQL query. We can perform all operations (INSERT, DELETE, UPDATE) on a view just like on a table but under some restrictions.
When can insertion, delete or update performed on view?
The view is defined from one and only one table. The view must include the PRIMARY KEY of the base table. The base table columns which are not part of view should not have NOT NULL constraint. The view should not have any field made out of aggregate functions. The view must not have any DISTINCT clause in its definition. The view must not have any GROUP BY or HAVING clause in its definition. The view must not have any SUBQUERIES in its definitions. i. Inserting Rows into a View: A new row can be inserted into a view in a similar way as you insert them in a table. When an insert operation performed on view, first a new row is inserted into the base table and the same is reflected in the view. ii. Deleting Rows into a View: A row(s) can be deleted from a view in a similar way as you delete them from a table. When an delete operation performed on view, first row(s) is/are deleted from the base table and the same is reflected in the view. iii. Updating Rows into a View: A row(s) can be updated in a view in a similar way as you update them in a table. When an update operation performed on view, first data is updated in the base table and the same is reflected in the view. iv. Dropping/Destroying View: Whenever you do not need the view anymore, we can destroy the view by using DROP command. The syntax is very simple and is given below −
Example: DROP VIEW emp;
6. RELATIONAL ALGEBRA
Relational Algebra is procedural query language, which takes Relation as input and generates relation as output. Relational algebra mainly provides theoretical foundation for relational databases and SQL.
DROP VIEW view_name;
Where r and s are either database relations or relation result set (temporary relation). r U s
returns a relation instance containing all tuples that occur in either relation instance r or
relation instance s (or both). For a union operation to be valid, the following conditions must
hold: r and s must have the same number of attributes. Attribute domains must be compatible in r and s. Example: ∏ (^) author(Books) ∪ ∏ (^) author(Articles) Output: Projects the names of the authors who have either written a book or an article or both.
iv. Intersection Operation (∩): It performs intersection operation between two given
relations. It collect only rows which are common in the two given relations.
Notation: R ∩ S
R ∩ S returns a relation instance containing all tuples that occur in both R and S. The relations R and S must be union-compatible, and the schema of the result is defined to be identical to the schema of R.
∏ author(Books) ∩ ∏ author(Articles)
Output: Projects the names of the authors who have written both book and an article.
v. Set Difference (−): It finds tuples which are present in one relation but not in the second
relation. Notation: r − s Finds all the tuples that are present in r but not in s. Example: ∏ (^) author (Books) − ∏ (^) author (Articles) Output − Provides the name of authors who have written books but not articles.
vi. Cartesian Product (Χ): It returns a relation instance whose schema contains all the
fields of table-1 (in the same order as they appear in table-1) followed by all the fields of table-2. It combines every row in first table with every row in the second table. Notation: r Χ s Where r and s are relations and their output will be defined as : r Χ s = { q t | q ∈ r and t ∈ s}
vii. Natural join ( ): The most general version of the join operation accepts a join condition
c and a pair of relation instances as arguments and returns a relation instance. The join condition is identical to a selection condition in form. The operation is defined as follows:
R (^) c S = σ c(R X S) Thus is defined to be a cross-product followed by a selection. Note that the condition c can refer to attributes of both Rand S. Note: If the condition c in R (^) c S contain equal operator, then it is called equi-join
viii. Natural Join( ): In this case, we can simply omit the join condition; the default is that
the join condition is a collection of equalities on all common fields. We call this special case as natural join, and it has the nice property that the result is guaranteed not to have two fields with the same name.
ix. Rename Operation (ρ): The results of relational algebra are also relations but without
any name. The rename operation allows us to rename the output relation. 'rename' operation is denoted with small Greek letter rho ρ. Notation: ρ ( temp, E) Where the result of expression E is saved with name of temp.
x. Division ( / ): Consider two relation instances A and B in which A has (exactly) two fields x
and y and B has just one field y, with the same domain as in A. We define the division operation A / B as the set of all x values (in the form of unary tuples) such that for every y value in (a tuple of) B, there is a tuple (x,y) in A. Example:
A
P
Sample Queries: We present a number of sample queries using the following schema:
Sailors ( sid: integer, sname: string, rating: integer, age: real) Boats ( bid: integer, bname: string, color: string) Reserves ( sid: integer, bid: integer, day: date)
SNO PNO S1 P S1 P S1 P S1 P S2 P S2 P S3 P S4 P S4 P
SNO S S S S
PNO P PNO P P SNO S S
PNO P P4 (^) SNO S
B
B
B
A / B
A / B
A / B
We identify the set of all the rows that are either red or green from boats table. We rename this result as Tempboats. Then we join Tempboats with Reserves to identify sid’s of sailors. Finally, we join with Sailors to find the names of Sailors with those sids.
(Q6) Find the names of sailors who have reserved a red and a green boat ρ ( T empboats 2 , ( σcolor = ′red′^ Boats ) ∩ ( σcolor = ′green′^ Boats )) πsname ( Tempboats 2 Reserves Sailors )
However, this solution is incorrect-it instead tries to compute sailors who have reserved a boat that is both red and green. A boat can be only one color; this query will always return an empty answer set. The right answer is ρ(T empred, πsid((σcolor=′red′ Boats) Reserves)) ρ ( T empgreen, πsid (( σcolor = ′green′^ Boats ) Reserves )) πsname (( Tempred ∩ Tempgreen ) Sailors )
The two temporary relations compute the sids of sailors, and their intersection identifies sailors who have reserved both red and green boats. (Q7) Find the names of sailors who have reserved at least two boats.
ρ ( Reservations, πsid,sname,bid ( Sailors Reserves )) ρ ( Reservationpairs (1 → sid 1 , 2 → sname 1 , 3 → bid 1 , 4 → sid 2 , 5 → sname 2 , 6 → bid 2) ,Reservations × Reservations ) πsname 1 σ ( sid 1= sid 2) ∩ (^) ( bid 1= bid 2) Reservationpairs
First, we compute tuples of the form (sid, sname, bid), where sailor sid has made a reservation for boat bid; this set of tuples is the temporary relation Reservations. Next we find all pairs of Reservations tuples where the same sailor has made both reservations and the boats involved are distinct. Here is the central idea: To show that a sailor has reserved two boats, we must find two Reservations tuples involving the same sailor but distinct boats. Finally, we project the names of such sailors.
(Q8) Find the sids of sailors with age over 20 who have not reserved a red boat. πsid ( σage> 20 Sailors ) −πsid (( σcolor = ′red′^ Boats ) Reserves Sailors ) This query illustrates the use of the set-difference operator. Again, we use the fact that sid is
the key for Sailors. We first identify sailors aged over 20 instances and then discard those who have reserved a red boat to obtain the answer.
(Q9) Find the names of sailors who have reserved all boats. The use of the word all (or every ) is a good indication that the division operation might be applicable: ρ ( Tempsids, ( πsid,bidReserves ) / ( πbidBoats )) πsname ( Tempsids Sailors ) (Q10) Find the names of sailors who have reserved all boats called Interlake. ρ ( Tempsids, ( πsid,bid Reserves ) / ( πbid ( σbname = ′Interlake′ Boats ))) πsname ( Tempsids Sailors )
7. RELATIONAL CALCULUS
Relational calculus is an alternative to relational algebra. In contrast to the algebra, which is procedural, the calculus is nonprocedural, or declarative , in that it allows us to describe the set of answers without being explicit about how they should be computed.
7.1 Tuple Relational Calculus
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.
where t = resulting tuples, P(t) = known as Predicate and these are the conditions that are used to fetch t. Thus, it generates set of all tuples t, such that Predicate P(t) is true for t.
P(t) may have various conditions logically combined with OR (∨), AND (∧), NOT(¬). 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.
In Tuple Relational Calculus, a query is expressed as {t| P(t)}
(Q14) Find sailors who have reserved all red boats. {S | S ∃ Sailors ∈ ∀ B ∈ Boats
( B.color = ′red′^ => ( ∃ R ∈ Reserves ( S.sid = R.sid ∧ R.bid = B.bid ))) }
7.2 Domain Relational Calculus
A domain variable is a variable that ranges over the values in the domain of some attribute (e.g., the variable can be assigned an integer if it appears in an attribute whose domain is the set of integers). A DRC query has the form { 〈 x 1 , x 2 ,... , xn 〉 | p (〈 x 1 ,x 2 ,.. ., xn 〉) } where each xi is either a domain variable or a constant and p (〈 x 1 ,x 2 ,.. ., xn 〉) denotes a DRC formula whose only free variables are the variables among the xi, 1 ≤ i ≤ n. The result of this query is the set of all tuples 〈 x 1 , x 2 ,.. .,xn 〉 for which the formula evaluates to true. A DRC formula is defined in a manner very similar to the definition of a TRC formula. The main difference is that the variables are now domain variables. Let op denote an operator in the set {<, >, =, ≤, ≥ , ≠} and let X and Y be domain variables. An atomic formula in DRC is one of the following:
( x 1 , x 2 ,... , xn ) ∈ Rel, where Rel is a relation with n attributes; each xi , 1 ≤ i ≤ n is
either a variable or a constant X op Y X op constant , or constant op X A formula is recursively defined to be one of the following, where P and q are themselves formulas and p(X) denotes a formula in which the variable X appears: any atomic formula ┐p, P /\ q, P V q, or p => q ∃X(p(X)), where X is a domain variable ∀ X(p(X)), where X is a domain variable (Q1) Find the names of sailors who have reserved boat 103. { ( N ) | ∃ I, T, A (〈 I, N, T, A 〉∈ Sailors ∧ ∃ Ir, Br, D (〈 Ir, Br, D 〉∈ Reserves ∧ Ir = I ∧ Br = 103) ) }
(Q2) Find the names of sailors who have reserved a red boat.
{ 〈 N 〉 | ∃ I, T, A (〈 I, N, T, A 〉∈ Sailors ∧ ∃〈 I, Br, D 〉∈ Reserves ∧ ∃〈 Br, BN,′red′ 〉∈ Boats ) } (Q7) Find the names of sailors who have reserved at least two boats. { 〈 N 〉 | ∃ I, T, A (〈 I, N, T, A 〉∈ Sailors ∧ ∃ Br 1 , Br 2 , D 1 , D 2 (〈 I, Br 1 , D 1 〉∈ Reserves ∧〈 I, Br 2 , D 2 〉∈ Reserves ∧ Br 1 ≠ Br 2)
(Q9) Find the names of sailors who have reserved all boats. { 〈 N 〉 | ∃ I, T, A (〈 I, N, T, A 〉∈ Sailors ∧ ∀ B, BN, C ( ¬ (〈 B, BN, C 〉∈ Boats ) V (∃〈 Ir, Br, D 〉∈ Reserves ( I = Ir ∧ Br = B )))) }
