s.y. diploma : sem. iii [co/cm/if/cd/cw] relational...

1

S.Y. Diploma : Sem. III [CO/CM/IF/CD/CW]

Relational Database Management System Time: 3 Hrs.] Prelim Question Paper Solution [Marks : 100

Q.1(a) (i) DATA INDEPENDENCE The ability to modify a schema definition in one level without affecting a schema definition in the next higher level is called data independence. There are two levels of data independence. 1) Physical data Independence : It is the ability to modify the physical

schema without causing application programs to be rewritten. 2) Logical data Independence : It is the ability to modify the logical schema

without causing application programs to be rewritten. Logical data independence is more difficult to achieve than i.e. the physical data independence.

Q.1(a) (ii) Advantages of Database Management System

1) Centralized Management and Control over data : Database administrator is the person having central control over the system.

2) Reduction of Redundancies : Centralized control of data by DBA avoids unnecessary duplication of data.

Q.1(a) (iii) Objectives of Normalization

1) To develop "good" database design. 2) Each normal form has a number of constraints (conditions) associated

with it which ensures that various types of anomalies (problems) and inconsistencies are not introduced in the database.

Q.1(a) (iv) Types of data models are :

(1) Relational Model (2) Network Model (3) Hierarchical Model (4) Physical Data Model

Q.1(a) (v) Multivalued dependency In database theory, multivalued dependency is a full constraint between two

sets of attributes in a relation. In contrast to the functional dependency, the multivalued dependency

requires that certain tuples be present in a relation. Therefore, a multivalued dependency is a special case of tuple-generating dependency. The multivalued dependency plays a role in the 4NF database normalization.

A multivalued dependency is a special case of a join dependency, with only

two sets of values involved, i.e. it is a 2-ary join dependency.

Vidy

alank

art affecting a ting a

endence. There Ther

modify the physical y the physical be rewritten. en.

modify the logical schema the logical schema be rewritten. Logical data be rewritten. Logical da

ve than i.e. the physical data than i.e. the physical d

ent System stem Control over data over data : Database admin : Databasea

control over the system. er the system. es : Centralized control of data by entralized control of ds

n of data. ta.

ization tion od" database design. base design.

form has a number of has a number of constraints conich ensures that various types of sures that various

tencies are not introduced in the da are not introduced i

of data models are : models are Relational Model Relational Mode

2) Network Model Network Model (3) Hierarchical Model Hierarchical Model

(4) Physical Data Model Physical Data Mode

1(a) (v) Multivalued dependency1(a) (v) Multivalued dependencIn database theory, muIn database theory, musets of attributes in asets of attribu

In contrast to In conrequires that dependenmultival

A

Vidyalankar : S.Y. Diploma RDBMS

2

Formal definition : The formal definition is given as follows. Let R be a relation schema and let R and R (subsets). The

multivalued dependency (which can be read as multidetermines ) holds on R if, in any legal relation r(R), for all pairs of tuples t1 and t2 in r such that t1[ ] = t2[ ], there exist tuples t3 and t4 in r such that

t1[ ] = t2[ ] = t3[ ] = t4[ ] t3[ ] = t1[ ] t3[R ] = t2[R ] t3[ ] = t2[ ] t4[R ] = t1[R ] In more simple words the above condition can be expressed as follows: if we

denote by (x, y, z) the tuple having values for , , R collectively equal to x, y, z correspondingly, then whenever the tuples (a, b, c) and (a, d, e) exist in r, the tuples (a, b, e) and (a, d, c) should also exist in r.

Example : Consider this example of a database of teaching courses, the

books recommended for the course, and the lecturers who will be teaching the course:

Course Book Lecturer AHA Silberschatz John D AHA Nederpelt William M AHA Silberschatz William M AHA Nederpelt John D AHA Silberschatz Christian G AHA Nederpelt Christian G OSO Silberschatz John D OSO Silberschatz William M

Because the lecturers attached to the course and the books attached to the

course are independent of each other, this database design has a multivalued dependency; if we were to add a new book to the AHA course, we would have to add one record for each of the lecturers on that course, and vice versa.

Put formally, there are two multivalued dependencies in this relation : {course} {book} and equivalently {course} {lecturer}.

Databases with multivalued dependencies thus exhibit redundancy.

Q.1(a) (vi) SERIALIZABILITY For a transaction always a serial schedule results in a consistent database

and not every concurrent Schedule can result in a consistent database. A concurrent schedule results in a consistent state if its result is equivalent to

a serial schedule of that transaction. Such concurrent schedule is known as serializable.

Vidy

alank

ar expressed as follows: if we expressed as follows: if we , R , R collectively equal ctively eq

tuples (a, b, c) and (a, d, e) exist s (a, b, c) and (a, d, e) d also exist in r. in r.

of a database of teaching courseabase of teachingrse, and the lecturers who will be t the lecturers who w

Bookok LecturerectuananannnannSilberschatzSilbersch John DJohn DananannlannA NederpeltNederpelt William MWilliamlllaaalaAHA Silberschatzilberschatz William MWilllaaaalaAHAA NederpeltNederpelt John DJ

alalalaaaalaAHAAHA SilberschatzSilberschatz ChrisalalalaaaalaAHAAHA NederpeltNederpel Calalalyyal

OSOSO Silberschatzbersc

yyayayayyaOSOO SilberschatzSilbeyyayayadydyayayaBecause the lecturers attached tuse the lecturers

course are independent of urse are independemultivalued dependency; if multivalued dependencywe would have to add one would have to add oand vice versa. and vice versa

Put formally, therePut for {course} {co

Databases

Q.1(a) (vi) SERIA ( Fo

Prelim Question Paper Solution

3

A serializable schedule is defined as: Given a concurrent schedule for n transactions the following conditions hold for each transaction in the set. i) All transactions are correct i.e. if anyone of the transaction is executed

on a consistent database, the resulting database is also consistent. ii) A given concurrent schedule is said to be serializable if it produces the

same result as some serial schedule of the transaction.

Since a serial schedule gives the same result as some serial schedule and since that serial schedule is correct, then the serializable schedule is also correct. Thus, given any schedule we say it is correct if we can show that it is a serializable schedule we use the precedence graph to test serializability of a schedule.

Q.1(a) (vii) UPDATE COMMAND

To modify the existing data in the database, the Update command is used. With this command, zero or more rows in a table can be updated.

The syntax of the update command is : Syntax : Update table name Set column_name 1 = Expression 1, [Column_name 2 = Expression 2], …… , [Column_name n = Expression n] [Where condition]; Table name is the name of the table that is to be updated. Column name is a

column in the table that is to be updated. The expression is a valid SQL expression. The condition is a valid SQL condition. The update statement references a single table and assigns an expression to at least one column. The 'where' clause is optional. If an update statement doesn't contain a where clause, the assignment of a value to a column will, be applied to all rows in the table.

Example : Update branch Set balance = 4000; The above statement will set the balance of all the branches to 4000.

Q.1(a) (viii) TRANSACTION Definition : A collection of operations that forms a single logical unit of work

is called transaction. A transaction is thus a program unit whose execution accesses and possibly updates the contents of a database. After every transaction, the database should be in a consistent state. A transaction is the result of execution of a user program, written in a high-level data manipulation language or programming language.

Q.1(b) (i) SQL is a special-purpose programming language designed for managing

data held in a relational database management system (RDBMS). Originally based upon relational algebra and tuple relational calculus, SQL

consists of a data definition language and a data manipulation language. The scope of SQL includes data insert, query, update and delete, schema creation and modification, and data access control. Although SQL is often

Vidy

alank

ar schedule and ule an

schedule is also s also can show that it is how that it is

o test serializability of serializability of

the Update command is used. Update command is us a table can be updated. n be updated.

s : olumn_name 1 = Expression 1, name 1 = Expressio [Column_name 2 = Expression 2n_name 2 = Exp

[Column_name n = Expressio Column_name [Where condition]; [Where condition

me of the table t f the table hat is to be updatehat is to be e that is to be updated. The exp to be updated. T

condition is a valid SQL conditioition is a valid SQL c single table and assigns an expres table and assigns a

e' clause is optional. If is optional. If an updatean lause, the assignment of a value he assignment of a t

n the table. e.

Example : Updateample : Update branch bra Set balaThe above statement will set the above statement w

a) (viii) TRANSACTION a) (viii) TRANSACTIO Definition : Defin A collection A collection

is called transaction. Ais called transaccesses and possaccesses antransaction, the dansacresult of exeult manipulation

Q.1(b) (i) (i) SQL is data


4

described as, and to a great extent is, a declarative language (4GL), it also includes procedural elements.

Features : SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database SQL can create new databases SQL can create new tables in a database SQL can create stored procedures in a database SQL can create views in a database SQL can set permissions on tables, procedures, and views VIEWS IN SQL

A view in SQL terminology is a single table that is derived from other tables. The other tables could be base tables or previously defined views. A view does not necessarily exist in physical form; it is considered a virtual table in contrast to base tables whose tuples are actually stored in the database. Since the views don't exist physically there are certain limits for the possible update operations that can be applied to views but it does not provide any limitations on querying a view. Thus a view can be thought of as a way of specifying a table that need not exist physically. We define a view in SQL by making use of the create view command. To define a view, we must give the view a name, and must state the query that computes the view. The form of the create view command is as follows. Create view V as < query expression> Where < query expression> is any legal query expression. The view name is represented by V. The query expression specifies the contents of a view. A view is always updated if we modify the base tables on which the view is defined, the view automatically reflects these changes. It is the responsibility of DMBS and not the user to make sure that the view is up to date. If we do not need a view any more, we can use the drop view command to dispose it off. Syntax: Drop view view name;

Q.1(b) (ii) Domain

There is a domain of possible values associated with every attribute. Attributes An entity is represented by a set of attributes. They are properties possessed

by each member of an entity set. For each attribute, there is a set of domains.

Vidy

alank

are

dures, and views res, and vie

table that is derived from other tab hat is derived frobles or previously defined views. A previously defined

ysical form; it is considered a virtuam; it is consideree tupleses are actually stored in the are actually stored

physically thally there are certain limits foere are certaan be applied toan be applied to views but it does views but

g a view. Thus a view. Thus a view can be thouview can at need not exist physically. not exist physically

iew in SQL by making use of the SQL by making usew, we must give the viewust give the view a name, a

the view. The form of the create vieew. The form of the c

ate view V as < query expression> s < query expreWhere < query expression> is any lehere < query expression> isrepresented by V. The query expreesented by V. The

A view is always updated if w view is always updatedefined, the view automatiefined, the view automaof DMBS and not the usof DMBS and not the us

If we do not need If we dispose it off. sposeSyntax: Drop

Q.1(b) (ii) Domain(b) (ii) There


5

Statement Used in PL to display the output : begin dbms_output.put_line(‘Test’); end;

Q.1(b) (iii) Desirable Properties of Decomposition Any decomposition requires that the information contained in the original

relation should be maintained. Thus in turn requires that the decomposition must be: Loss-less join decomposition. Dependency preserving. Without any repetition of information. Loss Less Join Decomposition To have a loss less join decomposition it is necessary to determine if

decomposition is lossy or not. This can be done by using the following rule. Let R be a relation schema, and let F be a set of functional dependencies on

R. Let R1 and R2 form a decomposition of R. This decomposition is a loss less join decomposition of R if at least one of the following functional dependencies are in F+:

R1 R2 R1 R1 R2 R2 Consider again the Loan schema = (Loanno, amount) The set of functional dependencies that we require to hold on the loan

schema are brname brcity, assets. Loanno amount, brname. The Loan schema as seen earlier is a bad database design. Assume we

decompose it into the following two relations: Branchschema = (brname, assets, brcity) Loaninfo schema = (brname, customernm, loanno, amount). Since we have the FD brname brcity, assets the augmentation rule of

functional dependencies implies that. Brname brname, brcity, assets. Since, branch schema Loan schema = {brname} it follows that our initial

decomposition is a loss-less-join decomposition.

Q.2(a) FILE PROCESSING SYSTEM AND DISADVANTAGES OF FILE PROCESSING SYSTEM Before the advent of DBMS, information was stored using file-processing system. In this system, data is stored in permanent system files. Different application programs are written to extract the data from these files and to add records to these files. But, there are number of major disadvantages in using file processing system, to store the data.

Vidy

alank

ar the original gina

t is necessary to determine if necessary to determin done by using the following rule. sing the following

F be a set of functional dependencie et of functional depposition of R. This decomposition i of R. This decompo

if at least one of the following one of the fo

e Loan schema = (Loanno, amount) ema = (Loanno, actional dependencies that we req dependencies that

e brcity, assets. rcity, assets. nno amount, brname. amount, brname.

he Loan schema as seen earlier ie Loan schema as seen edecompose it into the following twoompose it into the f

Branchschema = (brname, aBranchschema = (b Loaninfo schema = (brnam Loaninfo schema = (

Since we have the FD Since we have the FD functional dependencfunctional dep

Since, branch s Since, decompositio

Q.2(a) FILE PROC(a) FILESYSTEM SYBefore In thp


6

1) Data Redundancy and Inconsistency Since the data files and application programs are created by different

programmers over a long period. (i) The data files are likely to have different formats. (ii) Programs may be written in several programming languages. (iii) The same information may be duplicated in several plates. This results in

data redundancy and inconsistency. Consider following two data files :

(i) Savings account data file - stores information about customer (account-no, name, social-security, address, telephone-no.)

(ii) Checking account data file- stores information about customer (account-no, name, social-security, address, telephone-no.)

Fields (name, social-security, address and telephone-no.) are same in both

the files, i.e. duplication of data is there which results in data redundancy. Data redundancy increases the cost of storing and retrieving the data.

If the values of these common fields are not matching for some records in

both files, then it results in inconsistency of data. 2) Difficulty in accessing the data Conventional file processing system does not allow needed data to be

retrieved in a convenient and efficient manner. e.g. consider a data file Savings account data file with fields (acc-no, name, social-security, address, and balance) Application programs to access the data are written. But if user wants to display only those records for which balance is greater than Rs. 10,000. And if that program is not written, then it is difficult to access that data.

3) Data Isolation Because data are scattered in various files and file may be in different

formats, it is difficult to write new application programs to retrieve the appropriate data.

4) Integrity problems The data values stored in the database must satisfy certain types of

consistency constraints. For example the balance of a bank account may never fall below a prescribed amount (say Rs.500). Developers enforce these constraints in the system by adding appropriate code in the various application programs. However, when a new constraint is to be added, it is difficult to change the programs to enforce the new constraint.

5) Atomicity Problem A computer system is subject to failure. In many applications it is crucial to

ensure that once a failure has occurred and has been detected, the data are stored to the consistent state that existed prior to the failure. It is difficult to ensure this property in a conventional file-processing system, e.g. consider a program to transfer Rs. 500 from account A to account B. If failure occurs after removing Rs. 500 from account A and before adding Rs. 500 to account B. This results in inconsistent state.

esults

ustomer (account-er (account-

bout customer (account-omer (account-no.)

elephone-no.) are same in both phone-no.) are same in b which results in data redundancy. sults in data redun

toring and retrieving the data. and retrieving the data

ds are not matching for some reco not matching for soistency of data. f data

data ssing systsing system does not allow neeem does not allo

nt and efficient manner. e.g. conside and efficient manner. e.g. with fields (acc-no, name, social-sds (acc-no, name,

on programs to access the grams to access the data are d those records for which balance is g records for which b

am is not written, then written, then it is difficult to s dif

solation ause data are scattered in variouause data are scattered in

ormats, it is difficult to write newmats, it is difficult to wrappropriate data. ropriate data.

4) Integrity problems ntegrity problThe data values stored he data values storedconsistency constraintsconsistency constraintsnever fall below a never fall bethese constraints these application progplicadifficult to cha

5) Atomic5) A comen


7

6) Concurrent Access Anomalies In case of file-processing system, data is not centralized. If two or more users

want to access the database at the same time, interaction of concurrent updates may result in inconsistent data.

7) Security Problem Not every user of the database system should be able to access all the data.

For example in banking system, payroll personnel need to see only that part of database that has information about the various bank employees. They do not need access to information about customer accounts. But since application programs are added to the system in an ad hoc manner it is difficult to enforce security constraints.

Q.2(b) DATABASE ADMINISTRATOR

The person having central control over the system is called database administrator. Following are the functions of database administrator. Schema Definition : database administrator writes Database schemas.

These database schemas are translated by DDL compiler to a set of tables that are permanently stored in the data dictionary.

Storage structure and access method definition : The DBA creates appropriate Storage structures and access methods by writing a set of definitions, which is translated by data-storage and data definition language compiler.

Schema and Physical organization modification : DBA writes a set of definitions to modify the database schema or description of physical storage organization.

Granting of authorisation for data access : DBA is responsible for granting the access to the database.

Integrity-constraint specification : Integrity constraints are written by DBA and they are stored in a special file, which is accessed by database manager, while updating the data.

Vidy

alank

ar all the data. ata

only that part at paployees. They do hey do

ounts. But since But since ad hoc manner it is c manner it is

e system is called database ystem is called databtabase administrator. dministrator.

istrator writes Database schemar writes Database ated by DDL compiler to a set of ta DDL compiler to a

data dictionary. ctionass method definition defin :n The DBAT

res and access methods by writnd access methated by dat data-storage and data defia-storage and d

cal organization modificationrganization modificatio :n Dfy the database schemabase schema or descrip or

authorisation for data accesssation for data acce : DBAs to the database. tabase.

y-constraint specificationaint specification : Integrity : Innn are stored in a special file, which is a n a special file, w

dating the data. dating the d


8

Q.2(c) E-R diagram for Library Management System

Q.2(d) (i) The select operations : The select operation selects tuples that satisfy a given predicate. The lower case Greek letter sigma ( ) is used to denote selection. The predicate appears as a subscript to . The argument relation is given in parenthesis following the . Thus for e.g. to select those tuples of the loan relation where the branch name = Pimpri we write branch_name = "Pimpri" (loan).

Comparisons can be done by using =, #, <, _, >, _ in the selection predicate.

Several predicates can be combined together into a larger predicate by using logical connectivity’s and (^) and or (v) for example, to find tuples related to loans of more than 1200 amount and pertaining to the branch "Pimpri" form the loan relation.

branch_name = "Pimpri" /\ amount> 1200 (loan) The selection predicate may also include comparisons between two

attributes. For example, to find, customers who have same name as the supplier from the relation (table) customer _supplier.

customer_name = supplier_name (customer_supplier)

Vidy

alank

ar

(i) The select operations :e select operations : The sepredicate. The lower case Grredicate. The lower caseThe predicate appears as he predicate appears asparenthesis following thparenthesis following threlation where the brarelation where

Comparisons c CompaSeveral predlogical conloans othe lo


9

(ii) The project operation : The project operation is a unary operation that returns its argument relation, with certain attributes left out. Projection is denoted by the Greek letter pi ( ). We list those attributes that we wish to appear in the result as a subscript to . The argument relation follows in parenthesis. For example, to list all roll numbers and names of student from the student relation we write :

roll no,name (student). Thus projection operation picks a column out of relation. It can be used to

change the order of the attributes in a relation.

Q.2(e) AGGREGATE FUNCTIONS Aggregate functions are the functions that take a collection of values as input and return a single value. SQL offers the following 5 built in aggregate functions : Functions : Average : Avg. Minimum : Min. Maximum : Max. Total : Sum Count : Count. Avg function : The avg function computes the column's average value. The input to average function is a collection of number. e.g.: Find the avg of the salary from the Employee table. SQL > Select avg (salary) from employee; Min & Max functions : The min and max functions return the minimum and maximum values for the specified columns e.g.: Find the maximum and minimum values of salary from the employee table. e.g.: SQL > select max (salary), min (salary) from employee; Sum function : The sum function computes the column's total value. Input to this function must be a collection of numbers. e.g.: Calculate the sum of salary from Employee table. SQL> Select sum (salary) from employee; Count function : The count function counts the number of rows. They are of two forms Count (*): It counts all the rows in a table that satisfy any specified criteria. Count (column name): It counts all rows in a table that have a non-null value for the column name and satisfy the specified criteria. e.g. : Select count (*) from employee. It will count the total number of records stored in the employee table.

Q.2(f) (1) Simple and composite attributes Simple attributes are those attributes that are not divided into subparts.

Composite attributes on the other hand can be divided into subparts. Consider, customer entity with following attributes- customer_no,

customer_address.

Vidy

alank

art

can be used to sed to

ction of values as input and ction of values as input and t in aggregate functions : in aggregate functions :

ction computes the column's averction computes the colum a collection of number. collection of number.

salary from the Employee table. from the Employee tab avg (salary)y) from from employee; employee

ctions : : The min and max functioThe min and maes for the specified columns specified columns

e maximum and minimum values ofmum and minimum vaL > select max max (salary), ary), min min (salary

m function : nction : The sum function compThe sum fnction must be a collection of numb must be a collectio

e.g.: Calculate the sum of sa Calculate the sum of salary f SQL> SelectSelec sum (sala (s

Count function : Count function : The couTforms ms Count t (*): It counts a(*): ICount (column nCount (cothe column namthee.g. :e.g. Select It will counIt w

) (1) S


10

Here customer_no is a simple attribute and customer_address is a composite attribute, which has attributes flat_no, street, city, state.

(2) Single valued and multivalued attributes Single valued attribute has single value for a particular entity. For example,

the loan_no attribute for a specific loan refers to only one loan number. Such attributes are said to be single valued. Consider the customer entity with the following attributes: customer_no, customer_address.

A customer may have one or more addresses. Here customer_no is a single valued attribute and customer_address is multivalued attribute. Thus multivalued attributes are those that take up multiple values.

Q.3(a) THREE LEVEL ARCHITECTURE OF THE DATABASE SYSTEM : The figure below shows the three level architecture of the database system.

The architecture of the database is divided into three general levels: Internal, Conceptual and external. The internal level is the one closest to physical storage, that one concerned with the way how data is actually stored in the database. It does not depend on the hardware or operating system. The external level is the one closest to the users that is, the one concerned with the way in which the data is viewed by individual users. It does not depend on the operating system or the hardware but depends upon the Data Model employed (Relational, Network, etc.). The conceptual level is a level of indirection between the other two. It is dependent on the database management system, operating system, hardware etc. There will be many “external views” each consisting of a more or less abstract representation of some portion of the database. There is a single “conceptual

customer_no

Simple attributes

Composite attributes

customer_address

City Street State

Flat No.

customer_no

Single Valued attribute

Multi-valued attributes

customer_address

City Street State

Flat No.

Vidy

alank

arntity. For example, For example,

ne loan number. Such number. Such customer entity with the r entity with the

ss. Here customer_no is a single Here customer_no is a sing

s multivalued attribute. Thus multivalued attribute. T p multiple values. e values.

ECTURE OF THE DATABASE SYSURE OF THE DATABAS the three level e lev leve architecture of the dchitecturechit ctur

The architecture of the database architecture of the dataConceptual and external. Conceptual and exter

The internal level is the oThe internal level the way how data is ac way how dahardware or operatinware o

The external levThthe way in wthe the operatthe employe

The

ributes

ananMulti-valued aMultananomenknkCity nknktreet kk

ananannkk


11

view”. Likewise there is single “internal view” representing the total database as actually stored.

Q.3(b) Dr. E. F. Codd's rules i) Foundation Rule : A relational database management system must manage

its stored data using only its relational capabilities.

ii) Information Rule : All information in the database should be represented in one and only one way - as values in a table.

iii) Guaranteed Access Rule : Each and every datum (atomic value) is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.

iv) Systematic Treatment of Null Values : Null values (distinct from empty character string or a string of blank characters and distinct from zero or any other number) are supported in the fully relational DBMS for representing missing information in a systematic way, independent of data type.

Q.3(c) SECOND NORMAL FORM (2NF)

Definition : "A relation R is in second normal form (2NF) if and only if it is in INF and every non-key attribute (non prime attribute) is fully functionally dependent on the primary key." An attribute is a non-key or non-prime if it does not participate in the primary key. By splitting the supplier relation of Table 1 into supplier details and product relation we can eliminate the insertion, deletion and Updation problems encountered in 1NF relation. The decomposition of the supplier relation is as shown in Table 2.

Table 1 : Supplier Relation Supplierno Status City Productno Qty

S1 20 LATUR P1 300 S1 20 LATUR P2 200 S1 20 LATUR P3 400 S1 20 LATUR P4 200 S1 20 LATUR P5 100 S1 20 LATUR P6 100 S2 10 PUNE P1 300 S2 10 PUNE P2 400 S3 10 LATUR P2 200 S4 20 LATUR P2 200 S4 20 LATUR P4 300 S4 20 LATUR P5 400

Table 2 : Supplier details Table.

Supplierno Status City S1 20 LATUR S2 10 PUNE S3 10 PUNE S4 20 LATUR S5 30 AGRA

Vidy

alank

arpresented in d in

(atomic value) is c value) is combination of table nation of table

alues (distinct from empty distinct from empty and distinct from zero or any and distinct from zero or a

lational DBMS for representing nal DBMS for represendependent of data type. nt of data type.

normal form (2NF) if and only if it is form (2NF) if and onrime attribute) is fully functionally dute) is fully funct

ute is a non-key or non-prime if a non-key or

relation of Table 1 into supplier tion of Table 1 into suinate the insertion, deletion ane insertion, dele

relation. The decomposition of thon. The decompositio

Table 1 : Table 1 : Supplier Relation Supplier RSuppliernorno StatusStatu CityayaaS1 2020 LATUyayaaS1 2020 LATyady

aaS1S1 20 L

dya

dyaa

S11 20

dydyS1S 20dydyS1S1 20dyidyS2S2idyidyS2S2idyidyS3ididS4ididS4iiVV


12

Product Table Supplierno Productno Qty

S1 P1 300 S1 P2 200 S1 P3 400 S1 P4 200 S1 P5 100 S1 P6 100 S2 P1 300 S2 P2 400 S3 P2 200 S4 P2 200 S4 P4 300 S4 P5 400

Functional dependencies for the relations supplier details and products are as shown in Figure 1. Thus the problems encountered in 1NF supplier relation are solved as follows: i) Insertion: Information regarding the supplier S5 can be added into the table

supplier details even if he is not supplying any product. ii) Deleting: The tuple (S3, P2) in the product table can be deleted without

losing the information that S3 is located in Pune. iii) Updating: Since redundancy is eliminated the problems during updation is

less. However the relations supplier details and product cause certain problems in the 3 operations.

1) Insertion Anomaly: The information that a particular city has a particular status value cannot be inserted until some supplier is located in that city, because until a supplier exists there is no appropriate primary key value.

2) Deletion Anomaly: If a tuple of the supplier details is deleted, then

information regarding the supplier and the city is lost i.e. if the tuple pertaining to S5 is deleted then the status of Agra is also lost.

3) Updation Anomaly: Redundancy still exists in the supplier details relation because status for a city is still repeated in the supplier details relation. This leads to the same updation problem as in INF.

Q.3(d) BOYCE CODD NORMAL FORM

A database design is in BCNF if each, member of the set of relation schemas that constitutes the design is in BCNF. A relation schema R is in BCNF with respect to a set F of functional dependencies if for all functional dependencies in F+ of the form whereas R and R at least one of the following holds

Fig. 1

Vidy

a2) Deletion Ano2) D

information infopertaining

3) Updatrelare

) BOY

yalan

kar

k00

kk3003

kk400400 kkkkpplier details and products are as ails and productsountered in 1NF supplier relation aed in 1NF supplier re

he supplier S5 can be added into thlier S5 can be adde supplying any product. any pr

2) in the product table can be dehe product table S3 is located in Pune. cated in Pun

dancy is eliminated the problems ancy is eliminated the pro relations supplier details and proons supplier details a

operations.

Anomaly:aly: The information that a pa The informatio alue cannot be inserted until some nnot be inserted unt

use until a supplier exists there il a supplier exists th is no

A


13

i) is a trivial functional dependency (i.e. ). ii) is a super key for schema R. Consider the following relation schemas and their respective FD. i) Customer_schema = (customer_name, customer_street, customer_city)

Customer_name customer_street, customer_city. This relation is in BCNF Customer_name customer_street, customer_city is a non-trivial FD but

the candidate key for this schema is customer_name. ii) Branch_schema = (branch_name, assets, branch_city) Branch_name assets branch_city is a non-trivial FD but branch_name is

the candidate key. iii) Loan_info schema = (branch_name, customer_name, loan_no, amount). Loan_no. amount branch name. This schema is not in BCNF, because loan_no is not a super key and loan_no amount branch name is not a trivial FD. The loan_info schema is not in a desirable form since it suffers from repetition of information. To eliminate this redundancy the relation schema can be decomposed in those schemas, which are in BCNF. Consider the decomposition of loan into schema into two schemas. Loan_schemas = (branch_name, loan_no., amount). Borrower_schema = (customer_name, loan_no.)

The decomposition is loss-less join decomposition because loan_no amount branch name holds an loan_schema. Loan_no is candidate key of loan_schema only trivial dependencies hold on borrower schema. These both schema of decomposition are in BCNF.

Q.3(e) DATA TYPES IN SQL SQL Supports the following data types : i) Char Data type : The char data type is used when fixed length character

string is required. It can store alphanumeric values. If a user enters a value shorter than the specified length then the database blank-pads to the fixed length.

ii) Varchar2 Data type : The varchar2 data type supports a variable length character string. It also stores alphanumeric values. Using varchar2 saves disk space as compared to char. For e.g. consider a column assigned with varchar2 data type of size 30 bytes, if the user enters 10 bytes of character, then the length in that row would be 10 bytes and not 30 bytes. In the case of char, it would still occupy 30 bytes because the, remaining would be blank padded by SQL.

iii) Long Data type : This data type is used to store variable character length. The restriction on long data type is that only one column in a table Fan have long data type. This column moreover should not contain unique or primary constraint.

Vidy

alank

ar_

-trivial FD but FD b

FD but branch_name is ranch_name is

_name, loan_no, amount). ame, loan_no, amount).

n_no is not a super key and loan_ s not a super k

rable form since it suffers from reperm since it suffers frredundancy the relation schema the relation

which are in BCNF. are in BCNF.

n of loan into schema into two schem of loan into schema into twanch_name, loan_no., amount). name, loan_no., amou

= (customer_name, loan_no.) er_name, loan_n

n is loss-less join decomposition bss-less join decomolds an loan_schema. oan_schema.

s candidate key of loan_schema ate key of loan_er schema. These both schema of der schema. These both schem

ATA TYPES IN SQL YPES IN SQL SQL Supports the following data ty Supports the following di) Char Data type :har Data type The chare c

string is required. It can sring is required. It can shorter than the specifshorter than the speciflength. length.

ii) Varchar2 Data archacharacter strinacdisk space varchar2 then thchap

iii


14

iv) Number Data type : The number data type can store positive numbers, negative numbers, zeros, fixed-point numbers and floating-point numbers. It can take up the following form :

(1) Column-name number (P) for integer data, where P is the number. For example : studentid number (5). (2) Column-name (P, S) for floating point number where P is the total

number of digits and S refers to the number of digits to the right of the decimal point.

For example : Rate (4, 2) causes the value 44.20 to be stored exactly. v) Date Data type : It is used to store date and time in a table. SQL database

makes use of its own format to store date in a fixed length of 7 bytes each for century, month, day, year, hour, minute and second. Default date data type is "dd-mmm-yy".

Q.3(f) DOMAIN INTEGRITY CONSTRAINTS

There is a domain of possible values associated with every attribute. Domain constraints are the most elementary form for integrity constraint. It is possible for several attributes to have the same domain, while others will be distinctly different.

The check clause in SQL-92 that will do that extension: create domain hourly-wage numeric(5,2) constraint wage- value test check( value >= 4.00)

In this example, the hourly-wage is declared to be a decimal number with a total of five digits, two of which are placed after the decimal point and has a range of 4.00 to 999.99.

The check clause can be used for things like value not null, or value in ("Checking", "Savings").

Q.4(a) QUERY PROCESSOR COMPONENTS

i) DML precompiler : It translates DML statements in a query language into low level instructions that query evaluation engine understand. It also attempts to transform user's request into an equivalent but more efficient form.

ii) Embedded DML precompiler : It converts DML statements embedded in an application program to normal procedure calls in the host language. The precompiler must interact with the DML compiler to generate the appropriate code.

iii) DDL Interpreter : It interprets the DDL statements and records them in a set of tables containing Meta data or data dictionary.

iv) Query Evaluation Engine : It executes low-level instructions generated by the DML compiler.

DATA STRUCTURES Following data structures are required as a part of the physical system implementation. i) Data Files : It stores the database ii) Data Dictionary : It stores Meta data (data about data) about the structure of the

Database. iii) Indices : Provide fart access to data items that hold particular values. iv) Statistical Data : It stores statistical information about the data in the

database. This information is used by query processor to select efficient ways to execute query.

Vidy

alank

ar the to

right of the the

ored exactly. tly. ble. SQL database QL database

gth of 7 bytes each for bytes each for Default date data type is ate data type is

ciated with every attribute. Domain h every attribute. m for integrity constraint. It is possibtegrity constraint. It is

e domain, while otherain, while others will be distis w

that will do that extension: creatdo that extensiostraint wage- value test check( valu wage- value te

y-wage is declared to be a decimal is declared to be a f which are placed after the decima which are placed after the

99. can be used for things like valu used for things li

avings"). ).

ESSOR COMPONENTS COMPONENTScompiler : ler It translates DML statemIt translates DML

nstructions that query evaluatis that query eval on ensform user's request into an equsform user's request into an ival

Embedded DML precompiler : mbedded DML prec It capplication program to normal pcation program toprecompiler must interact with ecompiler must interaccode. ode

iii) DDL Interpreter :iii) DDL Interpreter It interp nterof tables containing Meof tables conta

iv) Query Evaluation E) Query Evaluthe DML compilerhe DM

DATA STRUCTUDFollowing datFolimplementaimpi) Data ii) Da

i


15

Q.4(b) Only in rare cases does a 3NF table not meet the requirements of BCNF. A 3NF table which does not have multiple overlapping candidate keys is guaranteed to be in BCNF. Depending on what its functional dependencies are, a 3NF table with two or more overlapping candidate keys may or may not be in BCNF. An example of a 3NF table that does not meet BCNF is :

Today's Court Bookings Court Start Time End Time Rate Type

1 09:30 10:30 SAVER 1 11:00 12:00 SAVER 1 14:00 15:30 STANDARD 2 10:00 11:30 PREMIUM-B 2 11:30 13:30 PREMIUM-B

2 15:00 16:30 PREMIUM-A Each row in the table represents a court booking at a tennis club that has

one hard court (Court 1) and one grass court (Court 2) A booking is defined by its Court and the period for which the Court is

reserved Additionally, each booking has a Rate Type associated with it. There are four

distinct rate types: SAVER, for Court 1 bookings made by members STANDARD, for Court 1 bookings made by non-members PREMIUM-A, for Court 2 bookings made by members PREMIUM-B, for Court 2 bookings made by non-members The table's superkeys are: S1 = {Court, Start Time} S2 = {Court, End Time} S3 = {Rate Type, Start Time} S4 = {Rate Type, End Time} S5 = {Court, Start Time, End Time} S6 = {Rate Type, Start Time, End Time} S7 = {Court, Rate Type, Start Time} S8 = {Court, Rate Type, End Time} ST = {Court, Rate Type, Start Time, End Time}, the trivial superkey

Note that even though in the above table Start Time and End Time attributes have no duplicate values for each of them, we still have to admit that in some other days two different bookings on court 1 and court 2 could start at the same time or end at the same time. This is the reason why {Start Time} and {End Time} cannot be considered as the table's superkeys. However, only S1, S2, S3 and S4 are candidate keys (that is, minimal superkeys for that relation) because e.g. S1 S5, so S5 cannot be a candidate key.

Vidy

alank

arkRD

kkkUM-B

kkkEMIUM-BMIUM kkkPREMIUM-AUM-A kkkkk court booking at a tennis club that ooking at a tennisrass court (Court 2) urt (Court 2)

ourt and the period for which the he period for w

has a Rate Ty Rate Type associated with it. pe associate

1 bookiookings made by members ngs made by memb or Court 1 bookings made by non-m bookings made b

A, for Court 2 bookings made by me Court 2 bookings madeM-B, for Court 2 bookings made by r Court 2 bookings

superkeys are: ys are: Court, Start Time} rt Ti

= {Court, End Time} = {Court, End TimeS3 = {Rate Type, Start Time} = {Rate Type, Start

S4 = {Rate Type, End Time} = {Rate Type, End Ti S55 = {Court, Start Time, End T = {Court, Start Time, E S66 = {Rate Type, Start Tim = {Rate Type, Start Tim S 7 = {Court, Rate Type, = {Court, Rate Type, S S88 = {Court, Rate Typ = {Court, R SSTT = {Court, Rate T = {

Note that even tNhave no duplichavother days tothetime or encannot

H


16

Recall that 2NF prohibits partial functional dependencies of non-prime attributes (i.e. an attribute that does not occur in ANY candidate key) on candidate keys, and that 3NF prohibits transitive functional dependencies of non-prime attributes on candidate keys. In Today's Court Bookings table, there are no non-prime attributes: that is, all attributes belong to some candidate key. Therefore the table adheres to both 2NF and 3NF. The table does not adhere to BCNF. This is because of the dependency Rate Type Court, in which the determining attribute (Rate Type) is neither a candidate key nor a superset of a candidate key. Dependency Rate Type Court is respected as a Rate Type should only ever apply to a single Court. The design can be amended so that it meets BCNF:

Rate Types Rate Type Court Member Flag

SAVER 1 Yes STANDARD 1 No PREMIUM-A 2 Yes PREMIUM-B 2 No

Today's Bookings

Rate Type Start Time End Time SAVER 09:30 10:30 SAVER 11:00 12:00 STANDARD 14:00 15:30 PREMIUM-B 10:00 11:30 PREMIUM-B 11:30 13:30 PREMIUM-A 15:00 16:30

The candidate keys for the Rate Types table are {Rate Type} and {Court, Member Flag}; the candidate keys for the Today's Bookings table are {Rate Type, Start Time} and {Rate Type, End Time}. Both tables are in BCNF. Having one Rate Type associated with two different Courts is now impossible, so the anomaly affecting the original table has been eliminated.

Q.4(c) E-R model Entity Relationship model : ER model is based on a perception of a real world that Consists of a set of basic objects called entities and of relationships among these objects. Entity : An entity is a thing or object in the real world that is distinguishable from all other objects. For example, Student is an entity, Account is an entity; Each Person in an enterprise is an entity.

Vidy

alank

arat is,

res to both both

dependency Rate ndency Rate e Type) is neither a ) is neither a

Rate Type should only ever Rate Type should only ev

s BCNF: F:

es nknknknkrt Member Flagmber F nknkank

1 Yeses

aaannan1 NoNaaannanAA 2 YesYesaaannllanM-B 22 NoolllalalalllalalaToday's BookingsToday's BookingsalaRate Type Typ Start TimeStart Tim End alalalalSAVERER 09:3009:3aaayyaSAVERER 11:00yyayayayyaSTANDARDSTANDAR 14:00yyayayadyyaPREMIUM-BPREMIUM- 10

dydyayayadyya

PREMIUM-BPREMIUM-BdydyyydyyPREMIUM-APREMIUM-AdydyyydydyyThe candidate keys for thThe candidate keys for thMember Flag}; the candember Flag}; thType, Start Time} ande, Staone Rate Type assone Rate Tanomaly affectingan

Q.4(c) E-R model c) E-REntity ReConsisobje


17

An entity has a set of properties or attributes and the values for some set of attributes or properties may uniquely identify an entity. For example, an account is an entity with attributes accno and Balance. Accno of a person uniquely identifies the Account. Entity set : An entity set is a set of entities of the same type that share the same properties or attributes. For example : The customers at a given bank can be defined as the entity set Customer. Attributes: An entity is represented by a set of attributes. They are properties possessed by each member of an entity set. For each attribute, there is a set of domains. Hierarchical Model It is similar to Network model, in the sense that records and links represent data and relationship among data respectively. But in this model the records in databases are organised as collections of trees rather than arbitrary graphs.

Q.4(d) GRANTING PERMISSIONS USING THE GRANT STATEMENT The grant statement provides various types of access to database objects such as tables, views and sequences. Syntax: Grant {object privileges} ON object name To < user list > [With Grant Option]

Rajesh Mumbai

Vijay Thane

Ajay Delhi

Mahesh Mumbai

A-101 500 A-302 700 A-222 400

A-201 350

A-327 750

A-303 900

Fig. : ER diagram corresponding to customers and loan.

Vidy

alank

ar he sam

ank can be n be

hey are properties re properties bute, there is a set of here is a set of

ork model, in the sense that recordsdel, in the sense that among data respectively. But in data respectively

organised as collections ofd as collections of trees rat t

Q.4(d) GRANTIN GRThe graas taSy

yydya

dyaVijay T

Vid

Vid101 500 dd

ViViVididyyViya

m corresponding to customers and lm corresponding to custome


18

Explanation of Syntax : Object Privileges: Each object privilege that is granted-authorizes the grantee to perform some operation on the object. The user can grant all the privileges or grant only specific object privileges.

The list of object privileges is as follows: (1) Alter : It allows the grantee to change the table definitions with the Alter

Table command. (2) Delete : It allows the grantee to remove the records from the table with the

delete command. (3) Index : It allows the grantee to create an index on the table with the create

index command. (4) Insert : It allows the grantee to add records to the table with the Insert

command. (5) Select : It allows the grantee to query the table with the select command. (6) Update : It allows the grantee to modify the records in the table with the

update command.

The Object name refers to a relation name or a view name. The with grant option allows the grantee to grant object privileges to other users.

Examples : 1) To grant a privileges on the table product to the user Ajay: Grant ALL On Product To Ajay; 2) To grant select and update privileges on the table product to Vijay. Grant select, update On product To Vijay;

Q.4(e) COMMIT COMMAND It is used to permanently record all changes that a user has made to the database system, since the last commit command was issued or since the beginning of the database session (transaction). Thus a commit command ends the current transaction and makes permanent any changes that were made during that transaction. While a transaction is in progress no other user can see the data being processed. Syntax: Commit; ROLLBACK COMMAND The rollback statement does the exact opposite operation of the commit statement. It ends the transaction but undoes any changes made during the transaction. Rollback is useful for 2 reasons: i) If one has made a mistake, such as deleting the wrong row for a table, one

can use rollback to restore the original data. ii) Rollback is useful if you have started a transaction that you cannot complete.

In such cases, rollback allows you to return to the starting point to allow you to take corrective actions and perhaps try again.)

Vidy

alank

arh the Alte Alte

e table with the with th

table with the create with the create

the table with the Insert ble with the Insert

e with the select command. th the select command. the records in the ords in the table with the table

me or a view name. view name. grantee to grant object privileges to grant object p

the table product to the user Ajay: he table product to the us

ect and update privileges on the tab update privileges onect, update date

duct ijay;

MMIT COMMAND T COMMAND t is used to permanently record sed to permanently

database system, since the lasase system, since thebeginning of the database sesbeginning of the database sethe current transaction andthe current transaction andduring that transaction. Wuring that transacthe data being process data being pSyntax: CommitSyntax: Co ;

ROLLBACK CROThe rollbacThestatementstatransaci) I


19

Syntax: Rollback [To [SAVEPOINT] Save point] For example : i) To rollback entire transaction: ROLLBACK; ii) To rollback to save point SP5; The syntax used as: Rollback to Save point SP5.

Q.4(f) TRIGGER A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed. Syntax : CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition) BEGIN --- sql statements END; Event Condition Action (ECA) is a short-cut for referring to the structure of active rules in event driven architecture and active database systems. Such a rule traditionally consisted of three parts: The event part specifies the signal that triggers the invocation of the rule The condition part is a logical test that, if satisfied or evaluates to true,

causes the action to be carried out. The action part consists of updates or invocations on the local data

This structure was used by the early research in active databases which started to use the term ECA. Current state of the art ECA rule engines use many variations on rule structure. Also other features not considered by the early research is introduced, such as strategies for event selection into the event part.

Q.5(a) Client Server Architecture of the database The architecture of a database system is generally influenced by the underlying computer system on which the database system runs. Networking of computers allow some tasks to be executed on the server system, and some tasks to be executed on client systems. This division of work has led to the development of client-server database systems. The general structure of a client –server system is shown in the figure drawn below :

Vidy

alank

arements like like

er is triggered ggere

]

n (ECA)CA) is a short-cut for referring is a short-cut for rven architecture and active databasecture and active

onally consisted of three parts: consisted of three p part specifies the signal that triggerifies the signal that

ndition part is a logical test that, part is a logical tess the action to be carried out. n to be carried o

e action part consists of updates or e action part consists of upd

his structure was used by the earlyucture was used by to use the term ECA. Current se the term ECA. Curvariations on rule structure. Avariations on rule structure. research is introduced, such research is introduced, such

Vid5(a) Client Server A

Vid rchite

Vicomputer sVallVexeVclieVV


20

Database functionality can be broadly divided into two parts – the front end and the back end as shown in the above figure. The back end manages access structure, query evaluation and optimisation, concurrency control, and recovery. The front end of a database system consists of tools such as forms, report writers, and graphical user interface facilities. The interface between front-end and back-end is through SQL, or through an application program.

Q.5(b) Multivalued dependency In database theory, multivalued dependency is a full constraint between two sets of attributes in a relation.

In contrast to the functional dependency, the multivalued dependency requires that certain tuples be present in a relation. Therefore, a multivalued dependency is a special case of tuple-generating dependency. The multivalued dependency plays a role in the 4NF database normalization. Formal definition : The formal definition is given as follows. Let R be a relation schema and let R and R (subsets). The multivalued dependency (which can be read as multidetermines ) holds on R if, in any legal relation r(R), for all pairs of tuples t1 and t2 in r such that t1[ ] = t2[ ], there exist tuples t3 and t4 in r such that t1[ ] = t2[ ] = t3[ ] = t4[ ] t3[ ] = t1[ ] t3[R ] = t2[R ] t3[ ] = t2[ ] t4[R ] = t1[R ] Functional Dependency Given a relation R, a set of attributes X in R is said to functionally determine another set of attributes Y, also in R, (written X Y) if, and only if, each X value is associated with precisely one Y value; R is then said to satisfy the functional dependency X Y. Equivalently, the projection X, Y R is a function, i.e. Y is a function of X. In simple words, if the values for the X attributes are known (say they are x), then the values for the Y attributes corresponding to x can be

Fig. : General Structure of Client-Server System.

Front-end and Back end Functionality

Vidy

alank

arrrararararakakanka

nk two parts – the front

nk The back end mank concurrency control,

ants of tools such

ancilities. The in

angh an application p

y ltivalued dependency is a full const dependency is a f

ion.

e functional dependenonal dependency, the multicy, ples be present in a rela present in a relation. Theretion

case of tuple-generating dependef tuple-generating de ole in the 4NF database normalizatiNF database nor

mal definition : definition : The formal definitioThe foet R be a relation schema and let e a relation schema

dependency ndency (which can bech cany legal relation r(R), for all pany legal relation r(R), for althere exist tuples tthere exist tuples t33 and t a 4 in in t t11[[ ] = t = t22[[ ] = t] = t3[ ] = t tt33[[ ] = t] = 1[ ] t 3[R R ] = t2[R t 3[ ] = t2[ ] t 4[R ] =

FunctionGiven ano

ionali


21

determined by looking them up in any tuple of R containing x. Customarily X is called the determinant set and Y the dependent set.

Q.5(c) (i) Entity : An entity is a thing or object in the real world that is distinguishable from all other objects. For example, Student is an entity, Account is an entity; Each Person in an enterprise is an entity.

(ii) Entity set : An entity set is a set of entities of the same type that share the

same properties or attributes. For example, the customers at a given bank can be defined as the entity set Customer.

Q.5(d) (i) SELECT * FROM emp

WHERE Salary >= 5000 and Salary <= 20000 (ii) SELECT * FROM emp WHERE Job = ‘Manager’

Q.5(e) UNION operator In SQL the UNION clause combines the results of two SQL queries into a single table of all matching rows. The two queries must result in the same number of columns and compatible data types in order to unite. Any duplicate records are automatically removed unless UNION ALL is used. UNION can be useful in data warehouse applications where tables aren't perfectly normalized. A simple example would be a database having tables sales2005 and sales2006 that have identical structures but are separated because of performance considerations. A UNION query could combine results from both tables. Note that UNION does not guarantee the order of rows. Rows from the second operand may appear before, after, or mixed with rows from the first operand. In situations where a specific order is desired, ORDER BY must be used. Note that UNION ALL may be much faster than plain UNION. Examples : Given these two tables:

sales2005 person amount

Joe 1000 Alex 2000 Bob 5000

sales2006

person amount Joe 2000 Alex 2000 Zach 35000

Vidy

alank

ar

that share the are th at a given bank bank

he results of two SQL queries into ats of two SQL querieo queries must result in the same nmust result in the

pes in order to unite. Any duplicate order to unite. A UNION ALL is used. ALL is used

n data warehouseata warehouse applications pplica A simple example would be a d example would

es2006 that have ident that have identical structicarmance considerations. A UNION q considerations. A

s.

UNION does not guarantee the oroes not guaranted may appear before, after, or mixed may appear before, after,

tions where a specific order is desirons where a specific

Note that UNION ALL may be muc hat UNION ALL may b

Examples : Examples Given these two en these two yy


22

Executing this statement: SELECT * FROM sales2005 UNION SELECT * FROM sales2006;

yields this result set, though the order of the rows can vary because no ORDER BY clause was supplied:

person amount Joe 1000 Alex 2000 Bob 5000 Joe 2000

Zach 35000 Note that there are two rows for Joe because those rows are distinct across their columns. There is only one row for Alex because those rows are not distinct for both columns. UNION ALL gives different results, because it will not eliminate duplicates. Executing this statement: SELECT * FROM sales2005 UNION ALL SELECT * FROM sales2006;

would give these results, again allowing variance for the lack of an ORDER BY statement:

person amount Joe 1000 Joe 2000 Alex 2000 Alex 2000 Bob 5000 Zach 35000

The discussion of full outer joins also has an example that uses UNION. INTERSECT operator The SQL INTERSECT operator takes the results of two queries and returns only rows that appear in both result sets. For purposes of duplicate removal the INTERSECT operator does not distinguish between NULLs. The INTERSECT operator removes duplicate rows from the final result set. The INTERSECT ALL operator does not remove duplicate rows from the final result set. Example : The following example INTERSECT query returns all rows from the Orders table where Quantity is between 50 and 100. SELECT * FROM Orders WHERE Quantity BETWEEN 1 AND 100

Vidy

alank

ar ORD

e rows are distinct across their rows are distinct across tuse those rows are not distinct for e rows are not dist

because it will not e it will not eliminate dupelimina

006; 006

, again allowing variance for the lan allowing variance fo

personperson amountamount

alaaaalaaJoeoe 10001000alaaalJoeJoe 20002alyaal

AlexAle 2000

yayaaAlexAlex 2000yayaaBobBob 50yayaaZachyayayaThe discussion of full outer joins a iscussion of full outer jo

INTERSECT operator INTERSECT operatThe SQL INTERSECT ophe SQL INTERSrows that appear in bs that INTERSECT operatINTERSECoperator removesoperator roperator does ope

Example EOrders


23

INTERSECT SELECT * FROM Orders WHERE Quantity BETWEEN 50 AND 200; EXCEPT operator The SQL EXCEPT operator takes the distinct rows of one query and returns the rows that do not appear in a second result set. The EXCEPT ALL operator (not supported in MSSQL) does not remove duplicates. For purposes of row elimination and duplicate removal, the EXCEPT operator does not distinguish between NULLs. Notably, the Oracle platform provides a MINUS operator which is functionally equivalent to the SQL standard EXCEPT DISTINCT operator. Example : The following example EXCEPT query returns all rows from the Orders table where Quantity is between 1 and 49, and those with a Quantity between 76 and 100. Worded another way; the query returns all rows where the Quantity is between 1 and 100, apart from rows where the quantity is between 50 and 75. SELECT * FROM Orders WHERE Quantity BETWEEN 1 AND 100 EXCEPT SELECT * FROM Orders WHERE Quantity BETWEEN 50 AND 75; Alternatively, in implementations of the SQL language without the EXCEPT operator, the equivalent form of a LEFT JOIN where the right hand values are NULL can be used instead. Example : The following example is equivalent to the above example but without using the EXCEPT operator. SELECT o1.* FROM ( SELECT * FROM Orders WHERE Quantity BETWEEN 1 AND 100) o1 LEFT JOIN ( SELECT * FROM Orders WHERE Quantity BETWEEN 50 AND 75) o2 ON o1.id = o2.id WHERE o2.id IS NULL

Vidy

alank

ar returns the the

operator (not or (nourposes of row of row

oes not distinguish t distinguish

rator which is functionally which is functionally operator. operator.

query returns all rows from the eturns all rows f and 49, and those with a Quant 49, and those with a

rns all rows where the Quantity is bs where the Qua he quantity is between 50 and 75. ntity is between

TWEEN 1 AND 100 EEN 1 AND 100

Orders RE Quantity BETWEEN 50 AND 75ity BETWEEN 50

natively, in implementations of thatively, in implemeerator, the equivalent form of a LE the equivalent fo

NULL can be used instead. can be used instead.

Example : xample The following exam ollowing exausing the EXCEPT operatousing the EXCEPT

SELECT o1.* SELECT o1.* FROM ( FROM SELECT * SE FROM O WHER LEFT J S


24

Q.5(f) Advantages of PL/SQL Block Structures: PL SQL consists of blocks of code, which can be nested

within each other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in the database and reused.

Procedural Language Capability: PL SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).

Better Performance: PL SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.

Error Handling : PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program. Once an exception is caught, specific actions can be taken depending upon the type of the exception or it can be displayed to the user with a message.

Q.6(a) SEQUENCES

In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key. The syntax for a sequence is: CREATE SEQUENCE sequence_name MINVALUE value MAXVALUE value START WITH value INCREMENT BY value CACHE value; For example: CREATE SEQUENCE supplier_seq MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 20; This would create a sequence object called supplier_seq. The first sequence number that it would use is 1 and each subsequent number would increment by 1 (i.e: 2,3,4,...}. It will cache up to 20 values for performance. If you omit the MAXVALUE option, your sequence will automatically default to: MAXVALUE 999999999999999999999999999 So you can simplify your CREATE SEQUENCE command as follows: CREATE SEQUENCE supplier_seq MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20;

Vidy

alank

arg

oops li

QL statements emen raffic.

ffectively during the ely during the on is caught, specific aught, specific

he exception or it can be on or it can be

d by using sequences. A sequence sequences. A serate a number sequence. This can number sequence. Th

number to act as a primary key. to act as a primar

ence_name name

value

E SEQUENCE supplier_seq UENCE supplier_seq VALUE 1

AXVALUE 99999AXVALUE 999999999999999999999999999 9 START WITH 1 TART WITH 1

INCREMENT BY 1 REMENT BY 1 CACHE 20; CACHE 20;

This would create a sequenThis would create a sequennumber that it would use isnumber that it wou(i.e: 2,3,4,...}. It will cach: 2,3,4,...}. It w

If you omit the MAXIf you omit MAXVALUE

So you canSo CRE


25

Q.6(b) INDEX An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes. Create an Index The syntax for creating a index is: CREATE [UNIQUE] INDEX index_name ON table_name (column1, column2, ... column_n) [ COMPUTE STATISTICS ]; UNIQUE indicates that the combination of values in the indexed columns must be unique. COMPUTE STATISTICS tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose a "plan of execution" when SQL statements are executed.

Example CREATE INDEX supplier_idx ON supplier (supplier_name); Rename an Index The syntax for renaming an index is: ALTER INDEX index_name RENAME TO new_index_name;

Example ALTER INDEX supplier_idx RENAME TO supplier_index_name;

Q.6(c) GENERALIZATION It is a containment relationship that exits between a higher-level entity set and one or more lower level entity sets. Attributes of higher-level entity set and of lower level entity set are common. This commonality is expressed by generalization.

In our example, account is the higher-level entity set and savings-account and checking-account are lower level entity sets. The higher + lower level entity sets also may be designated by the terms super class and subclass respectively. The account is the super class of the savings-account and checking-account sub-classes.

Specialization stems from a single entity set, it emphasizes differences among entities within the set by creating distinct lower level entity sets. These lower level entity sets may have attributes or may participate in relationships that do not apply to all the entities in the higher-level entity set.

Generalization proceeds from the recognition that a number of entity sets share some common features. Based on their commonalities, generalization synthesizes these entity sets into a single, higher-level entity sets. It is used to emphasize the similarities among lower level entity sets and to hide the differences.

Vidy

alank

ar indexed columns must columns must

atistics during the creation of tistics during the creation optimizer to choose a "plan of mizer to choose a "plan

.

ndex is: ndename me

_index_name; name

EX supplier_idx lier_idx E TO supplier_index_name; plier_index_name;

ALIZATION containment relationship that containment relationship tha exits

or more lower level entity sets. A more lower level ewer level entity set are commevel entity set a

generalization. alization.

In our example, account is thIn our example, account is tchecking-account are lowechecking-account are lowealso may be designated so may be desigaccount is the super ount isclasses. classes.

Specialization sSpentities withinentientity sets entall the e

Gen


26

Fig. : Specialization and generalization.

Fig. : E-R diagram with aggregation.

Q.6(d) CONCEPT OF AGGREGATION

Vidy

alank

aralization and generalization. on and generaliza

ATION ON


27

i) One limitation of the ER model is that it is not possible to express relationships amongst relationships.

ii) To illustrate the need for such a construct consider a database describing information about employees who work on a particular project and use a number of different machines in this work.

iii) It may appear that the relationship sets "work" and "uses" can be combined into one single relationship set see Figure.

iv) Nevertheless they should not be combined, since doing so would obscure the logical structure of this schema.

v) The solution to it is to use aggregation. vi) Aggregation is an abstraction through which relationships are treated as

higher-level entities. vii) Thus in our ego we regard the relationship set work and the entity sets

employee and project as a higher level entity set called as "work". viii) Such an entity set is treated in the same manner as any other entity set.

Q.6(e) JOINS Joining of multiple tables (Equi-joins): Sometimes we require to treat more than one table as though it were a single entity. Then a single SQL sentence can manipulate data from all the tables as though the tables were not separate objects, but one single entity. To achieve this, we have to join tables. Tables are joined on columns that have the same data type and data width in the tables. A join, which is based on equalities, is called equi-join. In equi-join the comparison operator equal to (=) is used to perform a join. It retrieves rows from tables having a common column. It is also called as simple join. Syntax : select table1.column, table 1. Column, table2.column, table2.column, . from table1, table2 where tablel.column1 = table2.column2; Example : Display order information like s_order_no, client name, s_order date and client_no for all the orders placed by the client. Here the data required is in two tables i.e. the sales order table and the client_table. These tables have to be accessed as though they were one entity. For example : Sales order table.

Sales_order table

Sorder_no Client_no Sorder_date

101 1 12-oct-95 102 2 10-oct-95 103 3 11-oct-95 104 4 12-oct-95

Vidy

alank

arombin

would obscure bscur

ships are treated as are treated as

work and the entity sets and the entity sets set called as "work". et called as "work".

ner as an as any other entity set. y other entity set.

ometimes we require to treat more es we require to tr entity. Then a single SQL senten Then a single SQL

s as though the tables were not gh the tables w achieve this, we have to join tablese this, we have

e same data type and data width in data type and data

on equalities, is called equi-joi equalities, is called qual to (=) is used to perform a join) is used to perfor

mon column. It is also called as simolumn. It is also called

ect table1.column, table 1. Column1.column, table 1. C table2.columtablefrom table1, table2 ble1, table2

where wh tablel.column1 = table2blel.column1 =

xample : Display order information : Display order ineand client_no for all the orders plaient_no for all the ordetwo tables i.e. the sales order ta ables i.e. the sales ordeaccessed as though they weraccessed as though they we

For example : r example : Sales ordS


28

Client table Client_no Client_name

1 Ajay 2 Vijay 3 Raj 4 Ravi

We will require using the following SQL statement to retrieve the required information. SQL>Select sorder_no, sorderdate, name, client.client_no from sales_order, client where client.client_no = sales_order.client_no; In case of Joins we use the technique of specifying the table name prior the column name, separated by a period in the where condition because the column name in both the tables are identical. The output will be

Output

Types of Join Inner Join Outer Join Self-Join Non equi-join

Q.6(f) studrecord (Name, Mark, Age, Place, Phone, birthdate)

(i) SELECT Name from studrecord WHERE Phone IS NULL

(ii) SELECT * FROM studrecord WHERE Place IN (‘Nashik’, ‘Pune’);

(iii) UPDATE studrecord SET Mark AS 88 WHERE Name = ‘Monika’

(iv) SELECT * from studrecord WHERE Name = ‘Amit’ AS T; SELECT * from studrecord WHERE Age = T.Age

Sorder_no Sorder_date Name Client.client_no 101 12-0ct.-95 Ajay 1 101 10-0ct.-98 Vijay 2 102 1l-Oct.-99 Raj 3 103 12-0ct.-99 Ravi 4

Vidy

alan oin

n equi-join n equi-join

studrecord (Name, Mark, Arecord (Name, Mar ge, Pl

(i) SELECT Name from studre(i) SELECT Name from stud WHERE Phone IS NU WHERE Phone IS N

(ii) SELECT * FROM stu) SELECT * FR WHERE Place W

(iii) UPDATE stud(iii) UPDAT WHERE

(iv) SELEC(iv) N SE

12-0ct.-99-99 Raviavi 4lanka

r the required equire

ying the table name prior the ng the table name prior re condition because the column ndition because the co

put te Name Client.client_nome Client.client_n

anaan-95 AjayAjay 11anaanct.-98ct. VijayVijay 22anlaan-Oct.-99ct.-99 RajRaj 3lalaalaalaalaalaaa

s.y. diploma : sem. iii [co/cm/if/cd/cw] relational...

Documents