DC-10 DATABASE MANAGEMENT SYSTEMS
PART-I, VOL-I

TYPICAL QUESTIONS & ANSWERS

OBJECTIVE TYPE QUESTIONS

Each question carries 2 marks.

Choose the correct or best alternative in the following:

Q.1 In the relational model, cardinality is termed as:

(A) Number of tuples. (B) Number of attributes.

Ans: A In the relational model, cardinality is termed as Number of tuples.

(Cardinality refers to the number of rows)

Q.2 Relational calculus is a

(A) Procedural language. (B) Non- Procedural language.

Ans: B Relation calculus is a non-procedural language.

(Relational calculus specifies only the information required, not how it is obtained)

Q.3 The view of total database content is

(A) Conceptual view. (B) Internal view.

Ans: A The view of total database content is conceptual view.

(Conceptual view represents the global view of the data)

Q.4 Cartesian product in relational algebra is

(A) a unary operator. (B) a binary operator.

Ans: B Cartesian product in relational algebra is a binary operator.

(It requires two operands. e.g., P X Q)

Q.5 DML is provided for

(A) Description of logical structure of database.

(B) Addition of new structures in the database system.

(D) Definition of physical structure of database system.

Ans: C DML is provided for manipulation & processing of database.

(Data stored in the database is processed or manipulated using data manipulation language commands as its name)

Q.6 ‘AS’ clause is used in SQL for

(A) Selection operation. (B) Rename operation.

Ans: B ‘AS’ clause is used in SQL for rename operation.

(e.g., SELECT ENO AS EMPLOYEE_NO FROM EMP)

Q.7 ODBC stands for

(A) Object Database Connectivity.

(B) Oral Database Connectivity.

(D)Open Database Connectivity.

Ans: D ODBC stands for open database connectivity.

(ODBC provides an application programming interface (API), which allows client-side programs to call the DBMS)

Q.8 Architecture of the database can be viewed to have

(A) two levels. (B) four levels.

Ans: C Architecture of the database can be viewed to have three levels.

(The three levels of abstraction are internal level, conceptual level and external level)

Q.9 In a relational model, relations are termed as

(A) Tuples. (B) Attributes

Ans: C In a relational model, relations are termed as tables.

(The term ‘relation’ is the technical name of the ‘table’ in RDBMS)

Q.10 The database schema is written in

(A) HLL (B) DML

Ans: C The database schema is written in DDL.

(Data definition language commands are used to define the schemas and sub-schemas)

Q.11 In the architecture of a database system external level is the

(A) physical level. (B) logical level.

Ans: D In the architecture of a database system external level is the view level.

Q.12 An entity set that does not have sufficient attributes to form a primary key is a

(A) strong entity set. (B) weak entity set.

Ans: B An entity set that does not have sufficient attributes to form a primary key is a weak entity set.

Q.13 In a Hierarchical model records are organized as

(A) Graph. (B) List.

Ans: D In a Hierarchical model, records are organized as a tree.

(Inverted tree (or just tree) structures are used to represent the hierarchies, such as organization chart)

Q.14 In an E-R diagram attributes are represented by

(A) rectangle. (B) square.

Ans: C In an E-R diagram attributes are represented by ellipse.

(Rectangles or squares represent the entity sets and triangles represent the generalization and specialization)

Q.15 In case of entity integrity, the primary key may be

(A) not Null (B) Null

Ans: A In case of entity integrity, the primary key may be not Null.

(Entity integrity rule states that the primary key of a relation must not accept duplicate or Null values)

Q.16 In tuple relational calculus is equivalent to

(A) (B)

Ans: A In tuple relational calculus P₁ à P₂ is equivalent to ØP₁ Ú P₂.

(The logical implication expression A à B, meaning if A then B, is equivalent to ØA Ú B)

Q.17 The language used in application programs to request data from the DBMS is referred to as the

(A) DML (B) DDL

(D) VDL (D) SDL

Ans: A The language used in application programs to request data from the DBMS is referred to as the DML.

(DML commands are used to manipulate, process and query the data stored in the database)

Q.18 A logical schema

(A) is the entire database.

(B) is a standard way of organizing information into accessible parts.

(D) both (A) and (C)

Ans: A A logical schema describes the entire database

Q.19 Related fields in a database are grouped to form a

(A) data file. (B) data record.

Ans: B Related data fields in a database are grouped to form a data record.

(A record is a collection of related fields)

Q.20 The database environment has all of the following components except:

(A) users. (B) separate files.

Ans: A The database environment has all of the following components except users.

(The users, interact with the DBMS, are outside the boundary of the database environment)

Q.21 The language which has recently become the defacto standard for interfacing application programs with relational database system is

(A) Oracle. (B) SQL.

Ans: B The language which has recently become the defacto standard for interfacing application programs with relational database system is SQL.

(SQL is a standard language widely used to perform various operation on database including interfacing between application programs and RDBMS)

Q.22 The way a particular application views the data from the database that the application uses is a

(A) module. (B) relational model.

Ans: D The way a particular application views the data from the database that are application uses is a sub-schema.

(Application programs performs the operations on the external views or sub-schemas)

Q.23 In an E-R diagram an entity set is represent by a

(A) rectangle. (B) ellipse.

Ans: A In an E-R diagram an entity set is represent by a rectangle.

Q.24 A report generator is used to

(A) update files. (B) print files on paper.

Ans: B A report generator is used to print files on paper.

(A report generator is used to generate the reports. The reports can be stored in the files and can be printed later)

Q.25 The property / properties of a database is / are :

(A) It is an integrated collection of logically related records.

(B) It consolidates separate files into a common pool of data records.

(D) All of the above.

Ans: D The properties of a database are: (A) It is an integrated collection of logically related records, (B) It consolidates separate files into a common pool of data records, and (C) data stored in a database is independent of the application programs using it.

Q.26 The DBMS language component which can be embedded in a program is

(A) The data definition language (DDL).

(B) The data manipulation language (DML).

(D) A query language.

Ans: B The DBMS language component which can be embedded in a program is the data manipulation language (DML).

(The host languages uses the embedded DML to interact with the DBMS)

Q.27 A relational database developer refers to a record as

(A) a criteria. (B) a relation.

Ans: C A relational database developer refers to a record as a tuple.

Q.28 The relational model feature is that there

(A) is no need for primary key data.

(B) is much more data independence than some other database models.

(D) are tables with many dimensions.

Ans: B The relational model feature is that there is much more data independence than some other database models.

(In relational model, the data is represented in the form of independent tables, which provides more flexibility and data independence)

Q.29 Conceptual design

(A) is a documentation technique.

(B) needs data volume and processing frequencies to determine the size of the database.

(D) is designing the relational model.

Ans: C Conceptual design involves modeling independent of the DBMS.

(Conceptual design provide concepts that are close to the way many users perceive data, which is independent of implementation and physical details)

Q.30 The method in which records are physically stored in a specified order according to a key field in each record is

(A) hash. (B) direct.

Ans: A A method in which records are physically stored in a specified order according to a key field in each record is hash.

(In hash method, a hash function is performed on the key value to determine the unique physical address of the record to store or retrieve)

Q.31 A subschema expresses

(A) the logical view. (B) the physical view.

Ans: C A subschema expresses the external view.

(External schemas are called also called as subschemas)

Q.32 Count function in SQL returns the number of

(A) values. (B) distinct values.

Ans: A Count function in SQL returns the number of values.

(Count function counts all the not null values in the specific column. If we want to count only distinct values than the DISTINCT keyword is also to be used)

PART-II, VOL-I

DESCRIPTIVES

Q.1 a. What is a database? Describe the advantages and disadvantages of using of DBMS. (7)

Ans:

Database – A database is a collection of related data and/or information stored so that it is available to many users for different purposes.

ADVANTAGES OF DBMS

1. Centralized Management and Control - One of the main advantages of using a database system is that the organization can exert, via the DBA, centralized management and control over the data.

2. Reduction of Redundancies and Inconsistencies - Centralized control avoids unnecessary duplication of data and effectively reduces the total amount of data storage required. Removing redundancy eliminates inconsistencies.

3. Data Sharing - A database allows the sharing of data under its control by any number of application programs or users.

4. Data Integrity - Data integrity means that the data contained in the database is both accurate and consistent. Centralized control can also ensure that adequate checks are incorporated in the DBMS to provide data integrity.

5. Data Security - Data is of vital importance to an organization and may be confidential. Such confidential data must not be accessed by unauthorized persons. The DBA who has the ultimate responsibility for the data in the DBMS can ensure that proper access procedures are followed. Different levels of security could be implemented for various types of data and operations.

6. Data Independence - Data independence is the capacity to change the schema at one level of a database system without having to change the schema at the next level. It is usually considered from two points of view: physical data independence and logical data independence. Physical data independence is the capacity to change the internal schema without having to change conceptual schema. Logical data independence is the capacity to change the conceptual schema without having to change external schemas or application programs.

7. Providing Storage Structures for Efficient Query Processing - Database systems provide capabilities for efficiently executing queries and updates. Auxiliary files called indexes are used for this purpose.

8. Backup and Recovery - These facilities are provided to recover databases from hardware and/or software failures.

9. Some other advantages are:

§ Reduced Application Development Time

§ Flexibility

§ Availability of up-to-date Information

DISADVANTAGES OF DBMS

1. Cost of Software/Hardware and Migration - A significant disadvantage of the DBMS system is cost.

2. Reduced Response and Throughput - The processing overhead introduced by the DBMS to implement security, integrity, and sharing of the data causes a degradation of the response and throughput times.

3. Problem with Centralization - Centralization also means that the data is accessible from a single source namely the database. This increases the potential of security breaches and disruption of the operation of the organization because of downtimes and failures.

b. Explain five duties of Database Administrator. (7)

Ans:

1. DBA administers the three levels of the database and, in consultation with the overall user community, sets up the definition of the global view or conceptual level of the database.

2. Mappings between the internal and the conceptual levels, as well as between the conceptual and external levels, are also defined by the DBA.

3. DBA ensures that appropriate measures are in place to maintain the integrity of the database and that the database is not accessible to unauthorized users.

4. DBA is responsible for granting permission to the users of the database and stores the profile of each user in the database.

5. DBA is responsible for defining procedures to recover the database from failures with minimal loss of data.

Q.2 a. Explain the terms primary key, candidate key and foreign key. Give an example for each. (7)

Ans:

Primary Key – Primary key is one of the candidate keys that uniquely identifies each row in the relation.

Candidate Key – A candidate key of an entity set is a minimal superkey, that uniquely identifies each row in the relation.

Foreign Key – Let there are two relations (tables) R and S. Any candidate key of the relation R which is referred in the relation S is called the foreign

key in the relation S and referenced key in the relation R. The relation R is also called as parent table and relation S is also called as child table.

For example:

STUDENT

Enrl No	Roll No	Name	City	Mobile
11	17	Ankit Vats	Delhi	9891663808
15	16	Vivek Rajput	Meerut	9891468487
6	6	Vanita	Punjab
33	75	Bhavya	Delhi	9810618396

GRADE

Roll No	Course	Grade
6	C	A
17	VB	C
75	VB	A
6	DBMS	B
16	C	B

§ Roll No is the primary key in the relation STUDENT and Roll No + Course is the primary key of the relation GRADE.

§ Enrl No and Roll No are the candidate keys of the relation STUDENT.

§ Roll No in the relation GRADE is a foreign key whose values must be one of those of the relation STUDENT.

b. Differentiate between logical database design and physical database design. Show how this separation leads to data independence. (7)

Ans:

Basis	Logical Database Design	Physical Database Design
Task	Maps or transforms the conceptual schema (or an ER schema) from the high-level data model into a relational database schema.	The specifications for the stored database in terms of physical storage structures, record placement, and indexes are designed.
Choice of criteria	The mapping can proceed in two stages: § System-independent mapping but data model-dependent § Tailoring the schemas to a specific DBMS	The following criteria are often used to guide the choice of physical database design options: § Response Time § Space Utilization § Transaction Throughput
Result	DDL statements in the language of the chosen DBMS that specify the conceptual and external level schemas of the database system. But if the DDL statements include some physical design parameters, a complete DDL specification must wait until after the physical database design phase is completed.	An initial determination of storage structures and the access paths for the database files. This corresponds to defining the internal schema in terms of Data Storage Definition Language.

The database design is divided into several phases. The logical database design and physical database design are two of them. This separation is generally based on the concept of three-level architecture of DBMS, which provides the data independence. Therefore, we can say that this separation leads to data independence because the output of the logical database design is the conceptual and external level schemas of the database system which is independent from the output of the physical database design that is internal schema.

Q.3 Consider the following relation schemes: (2´7=14)

PROJECT (Project#, Project_name, chief_architect)

Employee (Emp#, Empname)

Assigned_To (Project#, Emp#)

Give expression in Tuple calculus and Domain calculus for each of the queries below:

(i) Get the employee numbers of employees who work on all projects.

Ans:

Tuple Calculus:

{t[Emp#] | t Î ASSIGNED_TO Ù "p (p Î PROJECT à $u (u Î ASSIGNED_TO

Ù p[Project#] = u[Project#] Ù t[Emp#] = u[Emp#]))}

Domain Calculus:

{e | $p (<p, e> Î ASSIGNED_TO Ù "p₁ (<p₁, n₁, c₁> Î PROJECT

à <p₁, e> Î ASSIGNED_TO))}

(ii) Get the employee numbers of employees who do not work on the COMP123 project.

Ans:

Tuple Calculus:

{t[Emp#] | t Î ASSIGNED_TO Ù Ø$u (u Î ASSIGNED_TO

Ù u[Project#] = ‘COMP123’Ù t[Emp#] = u[Emp#])}

Domain Calculus:

{e | $p (<p, e> Î ASSIGNED_TO Ù "p₁, e₁ (<p₁, e₁> Ï ASSIGNED_TO

Ú p₁ ¹ ‘COMP123’ Ú e₁ ¹ e))}

Q.4 a. What is ODBC? How does Oracle act as ODBC and give examples of front end uses with ODBC. (7)

Ans:

ODBC – Open DataBase Connectivity (ODBC) enable the integration of SQL with a general-purpose programming language. ODBC exposes database capabilities in a standardized way to the application programmer through an application programming interface (API). Using ODBC, an application can access not just one DBMS but several different ones simultaneously.

ODBC achieve portability at the level of the execution by introducing an extra level of indirection. All direct interaction with a specific DBMS happens through a DBMS-specific driver. A driver is a software program that translates the ODBC calls into DBMS-specific calls. As the DBMS, which is accessed by an application, is known only at run time. So the drivers are loaded dynamically on demand. Available drivers are registered with a driver manager. The Oracle database driver translates the SQL commands from the application into equivalent commands that the Oracle DBMS understands and takes the result from the DBMS and translates it into equivalent form for the application.

Example: Let there be a DSN named EMPLOYEE through, which we want to access the Oracle database in Visual Basic.

Dim CN As New ADODB.Connection

Dim RS As New ADODB.Recordset

CN.Open “DSN=employee”, “scott”, “tiger”

RS.Open “Select * From Emp”, CN

b. Define the five basic operators of relational algebra with an example each. (7)

Ans:

Five basic operators of relational algebra are:

Let P and Q be two relations.

1. Union (È) - Selects tuples that are in either P or Q or in both of them. The duplicate tuples are eliminated.

R = P È Q

2. Minus (–) - Removes common tuples from the first relation.

R = P – Q

3. Cartesian Product or Cross Product (´) - The cartesian product of two relations is the concatenation of tuples belonging to the two relations and consisting of all possible combination of the tuples.

R = P ´ Q

For Example:

P:		Q:
ID	Name	ID	Name
101	Jones	100	John
103	Smith	104	Lalonde
104	Lalonde

R = P È Q		R = P – Q
ID	Name	ID	Name
100	John	101	Jones
101	Jones	103	Smith
103	Smith
104	Lalonde

R = P ´ Q
P.ID	P.Name	Q.ID	Q.Name
101	Jones	100	John
101	Jones	104	Lalonde
103	Smith	100	John
103	Smith	104	Lalonde
104	Lalonde	100	John
104	Lalonde	104	Lalonde

4. Projection (p) - The projection of a relation is defined as a projection of all its tuples over some set of attributes, i.e., it yields a vertical subset of the relation. It is used to either reduce the number of attributes (degree) in the resultant relation or to reorder attributes. The projection of a relation T on the attribute A is denoted by p_A(T),.

5. Selection (s) - Selects only some of the tuples, those satisfy given criteria, from the relation. It yields a horizontal subset of a given relation, i.e., the action is defined over a complete set of attribute names but only a subset of the tuples are included in the result.

R = s_B(P)

For Example:

EMPLOYEE:
Id	Name		Name
101	Jones		Jones
103	Smith	à	Smith
104	Lalonde		Lalonde
106	Byron		Byron

Projection of relation EMPLOYEE over attribute Name

EMPLOYEE:			Result of Selection
Id	Name		Id	Name
101	Jones	à	104	Lalonde
103	Smith		106	Byron
104	Lalonde
106	Byron

Result of Selection over EMPLOYEE for ID > 103

Q.5 a. Explain entity integrity and referential integrity rules in relational model. Show how these are realized in SQL. (7)

Ans:

Entity Integrity Rule – No primary key value can be null.

Referential Integrity Rule – In referential integrity, it is ensured that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.

In SQL, entity integrity and referential integrity rules are implemented as constraints on the relation called as primary key constraint and reference key constraint respectively. These constraints can be specified with relation at the time of creation of the relations or after the creation of the relations by altering the definition of the relations. For example:

CREATE TABLE DEPT

(DEPTNO NUMBER PRIMARY KEY,

DNAME VARCHAR2(15));

CREATE TABLE EMP

(EMPNO NUMBER PRIMARY KEY,

ENAME VARCHAR2(15),

JOB VARCHAR2(10),

DEPTNO NUMBER REFERENCES DEPT(DEPTNO));

b. What are the advantages of embedded query language? Give an example of a embedded SQL query.

(7)

Ans:

Embedded query language – SQL can be implemented in two ways. It can be used interactively or embedded in a host language or by using API. The use of SQL commands within a host language (e.g., C, Java, etc.) program is called embedded query language or Embedded SQL. Although similar capabilities are supported for a variety of host languages, the syntax sometimes varies. Some of the advantages of embedded SQL are:

§ SQL statements can be used wherever a statement in the host language is allowed.

§ It combines the strengths of two programming environments, the procedural features of host languages and non-procedural features of SQL.

§ SQL statements can refer to variables (must be prefixed by a colon in SQL statements) defined in the host program.

§ Special program variables (called null indicators) are used to assign and retrieve the NULL values to and from the database.

§ The facilities available through the interactive query language are also automatically available to the host programs.

§ Embedded SQL along with host languages can be used to accomplish very complex and complicated data access and manipulation tasks.

Example: The following Embedded SQL statement in C inserts a row, whose column values are based on the values of the host language variables contained in it.

EXEC SQL

INSERT INTO Sailors VALUES (:c_sname, :c_sid, :c_rating, :c_age);

Q.6 a. Consider the following relations: (3.5 x 2=7)

S (S#, SNAME, STATUS, CITY)

SP (S#, P#, QTY)

P (P#, PNAME, COLOR, WEIGHT, CITY)

Give an expression in SQL for each of queries below:

(i) Get supplier names for supplier who supply at least one red part

Ans:

SELECT SNAME FROM S

WHERE S# IN (SELECT S# FROM SP

WHERE P# IN (SELECT P# FROM P

WHERE COLOR = RED’))

(ii) Get supplier names for supplier who do not supply part P2.

Ans:

SELECT SNAME FROM S

WHERE S# NOT IN (SELECT S# FROM SP WHERE P# = ‘P2’)

b. Define a view and a trigger. Construct a view for the above relations which has the information about suppliers and the parts they supply. The view contains the S#, SNAME, P# , PNAME renamed as SNO, NAME, PNO, PNAME. (7)

Ans:

View – A view is a virtual table which is based on the one or more physical tables and/or views. In other words, a view is a named table that is represented, not by its own physically separate stored data, but by its definition in terms of other named tables (base tables or views).

Trigger – A trigger is a procedure that is automatically invoked by the DBMS in the response to specified changes to the database. Triggers may be used to supplement declarative referential integrity, to enforce complex business rules or to audit changes to data.

Command:

CREATE VIEW SUP_PART (SNO, NAME, PNO, PNAME) AS

SELECT S.S#, SNAME, P.P#, PNAME

FROM S, SP, P

WHERE S.S# = SP.S# AND P.P# = SP.P#

Q.7 a. Differentiate between the following: (10)

(i) Theta Join.

Ans:

Theta Join – The theta join operation is an extension to the natural-join operation that allows us to combine selection and a Cartesian product into a single operation. Consider relations r(R) and s(S), and let θ be a predicate on attributes in the schema R È S. The theta join operation r wv _q s is defined as follows:

r wv _q s = s_q (r x s)

(ii) Equi Join.

Ans:

Equi Join – It produces all the combinations of tuples from two relations that satisfy a join condition with only equality comparison (=).

(iii) Natural Join.

Ans:

Natural Join - Same as equi-join except that the join attributes (having same names) are not included in the resulting relation. Only one set of domain compatible attributes involved in the natural join are present.

(iv) Outer Join.

Ans:

Outer Join - If there are any values in one table that do not have corresponding value(s) in the other, in an equi-join that will not be selected. Such rows can be forcefully selected by using the outer join. The corresponding columns for that row will have NULLs. There are actually three forms of the outer-join operation: left outer join (), right outer join () and full outer join ().

b. What are temporary tables? When are they useful? Justify with an

example. (4)

Ans:

Temporary tables exists solely for a particular session, or whose data persists for the duration of the transaction. The temporary tables are generally used to support specialized rollups or specific application processing requirements. Unlike a permanent table, a space is not allocated to a temporary table when it is created. Space will be dynamically allocated for the table as rows are inserted. The CREATE GLOBAL TEMPORARY TABLE command is used to create a temporary table in Oracle.

CREATE GLOBAL TEMPORARY TABLE <table_name> (

) ON COMMIT {PRESERVE|DELETE} ROWS;

Q.8 a. Draw and explain the there level architecture of the database system. (7)

Ans:

A DBMS provides three levels of data is said to follow three-level architecture. The goal of the three-schema architecture is to separate the user applications and the physical database. The view at each of these levels is described by a schema. The processes of transforming requests and results between levels are called mappings. In this architecture, schemas can be defined at the following three levels:

§ External Level or Subschema – It is the highest level of database abstraction where only those portions of the database of concern to a user or application program are included. Any number of user views (some of which may be identical) may exist for a given global or conceptual view. Each external view is described by means of a schema called an external schema or subschema.

§ Conceptual Level or Conceptual Schema - At this level of database abstraction all the database entities and the relationships among them are included. One conceptual view represents the entire database. This conceptual view is defined by the conceptual schema. There is only one conceptual schema per database. The description of data at this level is in a format independent of its physical representation. It also includes features that specify the checks to retain data consistency and integrity.

§ Internal Level or Physical Schema – It is closest to the physical storage method used. It indicates how the data will be stored and describes the data structures and access methods to be used by the database. The internal view is expressed by the internal schema.

b. What is data independence? Differentiate between physical and logical data independence. (7)

Ans:

Data independence is the capacity to change the schema at one level of a database system without having to change the schema at the next level. The three-schema architecture allows the feature of data independence. Data independence occurs because when the schema is changed at some level, the schema at the next level remains unchanged; only the mapping between the two levels is changed. Types of data independence are:

§ Physical Data Independence – It is capacity to change the internal schema without having to change conceptual schema. Hence, the external schemas need not be changed as well. Changes to the internal schema may be needed because some physical files had to be reorganized to improve the performance of retrieval or update. If the same data as before remains in the database, the conceptual schema need not be changed.

§ Logical Data Independence - It is the capacity to change the conceptual schema without having to change external schemas or application programs. The conceptual schema may be changed to expand the database (by adding a record type or data item), to change constraints, or to reduce the database (by removing a record type or data item). Only the view definition and the mappings need be changed in a DBMS that supports logical data independence. Changes to constraints can be applied to the conceptual schema without affecting the external schemas or application programs.

Q.9 a. How is Oracle used in PL/SQL? Define the features of procedures and how they are defined. (7)

Ans:

PL/SQL – PL/SQL is Oracle’s procedural language (PL) superset of the Structured Query Language (SQL). PL/SQL is block-structured language that enables developers to code procedures, functions and unnamed blocks that combine SQL with procedural statements. PL/SQL has its own processing engine that executes PL/SQL blocks and subprograms. If the host program does not have the PL/SQL engine, the blocks of code are sent to the Oracle server for processing. The following figure shows the PL/SQL engine as an integrated component of Oracle server. The engine executes procedure statements and passes SQL statements to the SQL statement executor in the Oracle server.

Procedures – Sophisticated business rules and application logic can be stored as procedures within Oracle. Stored procedures – groups of SQL and PL/SQL statements – allow you to move code that enforces business rules from the application to the database. As a result the code will be stored once for all applications to use. Procedures can be defined or created with the CREATE PROCEDURE command. The syntax in Oracle to define a procedure is:

CREATE [OR REPLACE] PROCEDURE [<user>.]<procedure_name>
[(<arg1> [IN|OUT|IN OUT] <data type>
[,<arg2> [IN|OUT|IN OUT] <data type>] ...)] {IS|AS}

[<declaration>]

BEGIN

<body>

[EXCEPTION

WHEN <exception_name> THEN

[WHEN <exception_name> THEN

...

...]

[WHEN OTHERS THEN

<error handling statements>]]

END;

b. Explain the integrity constraints: Not Null, Unique, Primary Key with an example each. Is the combination ‘Not Null, Primary Key’ a valid combination. Justify. (7)

Ans:

Not Null – Should contain valid values and cannot be NULL.

Unique – An attribute or a combination of two or more attributes must have a unique value in each row. The unique key can have NULL values.

Primary Key – It is same as unique key but cannot have NULL values. A table can have at most one primary key in it.

For example:

STUDENT

Roll No	Name	City	Mobile
17	Ankit Vats	Delhi	9891663808
16	Vivek Rajput	Meerut	9891468487
6	Vanita	Punjab	NULL
75	Bhavya	Delhi	9810618396

§ Roll No is a primary key.

§ Name is defined with NOT NULL, means each student must have a name.

§ Mobile is unique.

‘Not Null, Primary Key’ is a valid combination. Primary key constraint already includes ‘Not Null’ constraint in it but we can also add ‘Not Null’ constraint with it. The use of ‘Not Null’ with ‘Primary Key’ will not have any effect. It is same as if we are using just ‘Primary Key’.

Q.10 Explain the followings : (14)

(i) Nested Queries.

Ans:

Nested Queries – A SELECT query can have subquery(s) in it. A SELECT query having another SELECT query in it, is called a nested query. Some operations cannot be performed with single SELECT command or with join operation. They can only be performed with the help of nested queries (also referred to as subqueries). For example, we want to compute the second highest salary:

SELECT MAX(SAL) FROM EMP WHERE SAL < (SELECT MAX(SAL) FROM EMP)

Some operations can be performed both by Join and subqueries. The Join operation is costlier in terms of time and space. Therefore, the solution based on subqueries is preferred.

(ii) Cursors in SQL.

Ans:

Cursors in SQL – An object used to store the output of a query for row-by-row processing by the application programs. Cursors are constructs that enable the user to name a private memory area to hold a specific statement for access at a later time. Cursors are used to process multi-row result sets one row at a time. Additionally, cursors keep track of which row is currently being accessed, which allows for interactive processing of the active set.

(iii) RDBMS.

Ans:

RDBMS – RDBMS is a database management system (DBMS) that stores data in the form of relations. Relational databases are powerful because they require few assumptions about how data is related or how it will be extracted from the database. As a result, the same database can be viewed in many different ways. An important feature of relational system is that a single database can be spread across several tables. This differs from flat-file databases, in which each database is self-contained in a single table.

(iv) View.

Ans:

View – A view is a relation (virtual rather than base) and can be used in query expressions, that is, queries can be written using the view as a relation. In other words, a view is a named table that is represented, not by its own physically separate stored data, but by its definition in terms of other named tables (base tables or views). The base relations on which a view is based are sometimes called the existing relations. The definition of a view in a create view statement is stored in the system catalog. The syntax to create a view is:

CREATE [OR REPLACE] VIEW <view_name> [(<aliases>)] AS <query>

WITH {READ ONLY|CHECK OPTION [CONSTRAINT <constraint_name>]};

(v) Application Programming Interface.

Ans:

Application Programming Interface – Commercial SQL implementations take one of the two basic techniques for including SQL in a programming language – embedded SQL and application program interface (API). In the application program interface approach, the program communicates with the RDBMS using a set of functions called the Application Program Interface (API). The program passes the SQL statements to the RDBMS using API calls and uses API calls to retrieve the results. In this method, the precompiler is not required.

Q.11 a. Consider the following relational schema: (7)

PERSON (SS#, NAME, ADDRESS)

CAR (REGISTRATION_NUMBER, YEAR, MODEL)

ACCIDENT (DATE, DRIVER, CAR_REG_NO)

OWNS (SS#, LICENSE)

Construct the following relational algebra queries:

(i) Find the names of persons who are involved in an accident.

Ans: p_NAME(PERSON) Ç p_DRIVER(ACCIDENT)

(ii) Find the registration number of cars which were not involved in any accident.

Ans: p_{REGISTRATION_NUMBER}(CAR) – p_{CAR_REG_NO}(ACCIDENT)

b. What is a key? Explain Candidate Key, Alternate Key and Foreign Key. (7)

Ans:

Key – A single attribute or a combination of two or more attributes of an entity set that is used to identify one or more instances (rows) of the set (table) is called as key.

Candidate Key – A candidate key is a minimal superkey, which can be used to uniquely identify a tuple in the relation.

Alternate Key – All the candidate keys except primary key are called as alternate keys.

Foreign Key – Let there are two relations (tables) R and S. Any candidate key of the relation R which is referred in the relation S is called the foreign key in the relation S and referenced key in the relation R. The relation R is also called as parent table and relation S is also called as child table.

Q.12 a. What is data independence? Explain the difference between physical and logical data independence. (7)

Ans:

§ Logical Data Independence - It is the capacity to change the conceptual schema without having to change external schemas or application programs. The conceptual schema may be changed to expand the

database (by adding a record type or data item), to change constraints, or to reduce the database (by removing a record type or data item). Only

the view definition and the mappings need be changed in a DBMS that supports logical data independence. Changes to constraints can be applied to the conceptual schema without affecting the external schemas or application programs.

b. Explain entity integrity and referential integrity. Give an example for each. (7)

Ans:

Entity Integrity Rule – No primary key value can be null.

Referential Integrity Rule – In referential integrity, it is ensured that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.

For example:

STUDENT

Enrl No	Roll No	Name	City	Mobile
11	17	Ankit Vats	Delhi	9891663808
15	16	Vivek Rajput	Meerut	9891468487
6	6	Vanita	Punjab
33	75	Bhavya	Delhi	9810618396

GRADE

Roll No	Course	Grade
6	C	A
17	VB	C
75	VB	A
6	DBMS	B
16	C	B

§ Roll No is the primary key in the relation STUDENT and Roll No + Course is the primary key of the relation GRADE. (Entity Integrity)

§ Roll No in the relation GRADE is a foreign key whose values must be one of those of the relation STUDENT. (Referential Integrity)

Q.13 Consider the following relational schemas:

EMPLOYEE (EMPLOYEE_NAME, STREET, CITY)

WORKS (EMPLOYEE_NAME, COMPANYNAME, SALARY)

COMPANY (COMPANY_NAME, CITY)

a. Specify the table definitions in SQL. (5)

Ans:

CREATE TABLE EMPLOYEE

( EMPLOYEE_NAME VARCHAR2(20) PRIMARY KEY,

STREET VARCHAR2(20),

CITY VARCHAR2(15));

CREATE TABLE COMPANY

( COMPANY_NAME VARCHAR2(50) PRIMARY KEY,

CITY VARCHAR2(15));

CREATE TABLE WORKS

( EMPLOYEE_NAME VARCHAR2(20)

REFERENCES EMPLOYEE(EMPLOYEE_NAME,

COMPANYNAME VARCHAR2(50)

REFERENCES COMPANY(COMPANY_NAME,

SALARY NUMBER(6),

CONSTRAINT WORKS_PK PRIMARY KEY(EMPLOYEE_NAME, COMPANY_NAME));

b. Give an expression in SQL for each of queries below: (9)

(i) Find the names of all employees who work for first Bank Corporation.

Ans:

SELECT EMPLOYEE_NAME

FROM WORKS

WHERE COMPANYNAME = ‘First Bank Corporation’;

(ii)Find the names and company names of all employees sorted in ascending order of company name and descending order of employee names of that company.

Ans:

SELECT EMPLOYEE_NAME, COMPANYNAME

FROM WORKS

ORDER BY COMPANYNAME, EMPLOYEE_NAME DESC;

(iii) Change the city of First Bank Corporation to ‘New Delhi’

Ans:

UPDATE COMPANY

SET CITY = ‘New Delhi’

WHERE COMPANY_NAME = ‘First Bank Corporation’;

Q.14 a. Differentiate between logical database design and physical database design. (7)

Ans:

Logical Database Design vs. Physical Database Design

Basis	Logical Database Design	Physical Database Design
Task	Maps or transforms the conceptual schema (or an ER schema) from the high-level data model into a relational database schema.	The specifications for the stored database in terms of physical storage structures, record placement, and indexes are designed.
Choice of criteria	The mapping can proceed in two stages: § System-independent mapping but data model-dependent § Tailoring the schemas to a specific DBMS	The following criteria are often used to guide the choice of physical database design options: § Response Time § Space Utilization § Transaction Throughput
Result	DDL statements in the language of the chosen DBMS that specify the conceptual and external level schemas of the database system. But if the DDL statements include some physical design parameters, a complete DDL specification must wait until after the physical database design phase is completed.	An initial determination of storage structures and the access paths for the database files. This corresponds to defining the internal schema in terms of Data Storage Definition Language..

Q.15 a. Explain the concepts of relational data model. Also discuss its advantages and disadvantages. (7)

Ans:

Relational Data Model – The relational model was first introduced by Prof. E.F. Codd of the IBM Research in 1970 and attracted immediate attention due to its simplicity and mathematical foundation. The model uses the concept of a mathematical relation (like a table of values) as its basic building block, and has its theoretical basis in set theory and first-order predicate logic. The relational model represents the database as a collection of relations. The relational model like all other models consists of three basic components:

§ a set of domains and a set of relations

§ operation on relations

§ integrity rules

ADVANTAGES

· Ease of use – The revision of any information as tables consisting of rows and columns is quite natural and therefore even first time users find it attractive.

· Flexibility – Different tables from which information has to be linked and extracted can be easily manipulated by operators such as project and join to give information in the form in which it is desired.

· Security – Security control and authorization can also be implemented more easily by moving sensitive attributes in a given table into a separate relation with its own authorization controls. If authorization requirement permits, a particular attribute could be joined back with others to enable full information retrieval.

· Data Independence – Data independence is achieved more easily with normalization structure used in a relational database than in the more complicated tree or network structure. It also frees the users from details of storage structure and access methods.

· Data Manipulation Language – The possibility of responding to ad-hoc query by means of a language based on relational algebra and relational calculus is easy in the relational database approach. Provides simplicity in the data organization and the availability of reasonably simple to very powerful query languages.

DISADVANTAGES

· Performance – If the number of tables between which relationship is to be established are large and the tables themselves are voluminous, the performance in responding to queries is definitely degraded.

· Unsuitable for Hierarchies – While the relational database approach is a logically attractive, commercially feasible approach, but if the data is for example naturally organized in a hierarchical manner and stored as such, the hierarchical approach may give better results.

Q.16 Consider the following relational schema: (14)

Doctor(DName,Reg_no)

Patient(Pname, Disease)

Assigned_To (PNAME,DNAME)

Give expression in both Tuple calculus and Domain calculus for each of the queries:

(i) Get the names of patients who are assigned to more than one doctor.

Ans:

Tuple Calculus:

{p[PName] | p Î PATIENT Ù $a_1,a₂ (a₁ Î ASSIGNED_TO Ù a₂ Î ASSIGNED_TO

Ù p[PName] = a₁[PName] Ù a₁[PName] = a₂[PName] Ù a₁[DName] ¹ a₂[DName])}

Domain Calculus:

{p | $p₁, d₁, p₂, d₂ (<p, s> Î PATIENT Ù <p₁, d₁> Î ASSIGNED_TO

Ù <p₂, d₂> Î ASSIGNED_TO Ù p₁ = p₂Ù d₁ ¹ d₂)}

(ii) Get the names of doctors who are treating patients with ‘Polio’.

Ans:

Tuple Calculus:

{u[Dname] | u Î ASSIGNED_TO Ù $t (t Î PATIENT Ù t[Disease] = ‘Polio’

Ù t[PName] = u[PName])}

Domain Calculus:

{d | $p₁, p₂, s₂ (<p₁, d> Î ASSIGNED_TO Ù <p₂, s₂> Î PATIENT

Ù p₁ = p₂ Ù s₂ = ‘Polio’)}

Q.17 a. What are the features of embedded SQL? Explain. (7)

Ans:

Embedded SQL – SQL can be implemented in two ways. It can be used interactively or embedded in a host language or by using API. The use of SQL commands within a host language (e.g., C, Java, etc.) program is called embedded query language or Embedded SQL. Although similar capabilities are supported for a variety of host languages, the syntax sometimes varies. Some of the features of embedded SQL are:

§ SQL statements can be used wherever a statement in the host language is allowed.

§ It combines the strengths of two programming environments, the procedural features of host languages and non-procedural features of SQL.

§ SQL statements can refer to variables (must be prefixed by a colon in SQL statements) defined in the host program.

§ Special program variables (called null indicators) are used to assign and retrieve the NULL values to and from the database.

§ The facilities available through the interactive query language are also automatically available to the host programs.

§ Embedded SQL along with host languages can be used to accomplish very complex and complicated data access and manipulation tasks.

b. Differentiate between Theta Join, Equi Join and Natural join (7)

Ans:

(i) Theta Join – The theta join operation is an extension to the natural-join operation that allows us to combine selection and a Cartesian product into a single operation. Consider relations r(R) and s(S), and let θ be a predicate on attributes in the schema R È S. The theta join operation r wv _q s is defined as follows:

r wv _q s = s_q (r X s)

(ii) Equi Join – It produces all the combinations of tuples from two relations that satisfy a join condition with only equality comparison (=).

(iii) Natural Join - Same as equi-join except that the join attributes (having same names) are not included in the resulting relation. Only one sets of domain compatible attributes involved in the natural join are present.

Q.18 Explain the followings: (14)

(i) View.

Ans:

The syntax to create a view is:

CREATE [OR REPLACE] VIEW <view_name> [(<aliases>)] AS <query>

WITH {READ ONLY|CHECK OPTION [CONSTRAINT <constraint_name>]};

(iii) Cursors in SQL.

Ans:

(iv) Nested Queries.

Ans:

Nested Queries – A SELECT query can have subquery(s) in it. When a SELECT query has another SELECT query in it, then it is called a nested query. Some operations cannot be performed with single SELECT command or with join operation. They can be performed with the help of

nested queries (also referred to as subqueries). For example, we want to compute the second highest salary:

SELECT MAX(SAL) FROM EMP

WHERE SAL < (SELECT MAX(SAL) FROM EMP)

Some operations can be performed both by Join and subqueries. The Join operation is much costlier in terms of time and space. Therefore, the solution based on subqueries is preferred.

(v) Temporary Tables.

Ans:

Temporary Tables – Temporary tables exists solely for a particular session, or whose data persists for the duration of the transaction. The temporary tables are generally used to support specialized rollups or specific application processing requirements. Unlike a permanent table, a temporary table does not automatically allocate space when it is created. Space will be dynamically allocated for the table as rows are inserted.

The CREATE GLOBAL TEMPORARY TABLE command is used to create a temporary table in Oracle.

CREATE GLOBAL TEMPORARY TABLE <table_name>

(

)

ON COMMIT {PRESERVE|DELETE} ROWS;

(vi) Integrity Constraints.

Ans:

Integrity Constraints – A database is only as good as the information stored in it, and a DBMS must therefore help prevent the entry of incorrect information. An integrity constraint is a condition specified on a database schema and restricts the data that can be stored in an instance of the database. If a database instance satisfies all the integrity constraints specified on the database schema, it is a legal instance. A DBMS enforces integrity constraints, in that it permits only legal instances to be stored in the database. Integrity constraints are specified and enforced at different times:

§ When the DBA or end user defines a database schema, he or she specifies the integrity constraints that must hold on any instance of this database.

§ When a database application is run, the DBMS checks for violations and disallows changes to the data that violate the specified integrity constraints.

Many kinds of integrity constraints can be specified in the relational model, such as, Not Null, Check, Unique, Primary Key, etc.

Q.19 a. Explain different types of failures that occur in Oracle database. (7)

Ans:

Types of Failures – In Oracle database following types of failures can occurred:

§ Statement Failure

· Bad data type

· Insufficient space

§ Insufficient Privileges (e.g., object privileges to a role)

§ User Process Failure

· User performed an abnormal disconnect

· User's session was abnormally terminated

· User's program raised an address exception

§ User Error

· User drops a table

· User damages data by modification

§ Instance Failure

§ Media Failure

· User drops a table

· User damages data by modification

§ Alert Logs

· Records informational and error messages

· All Instance startups and shutdowns are recorded in the log

· Every Create, Alter, or Drop operation on a rollback segment, tablespace or database is record in the log

§ Recovery Views

§ DB Verify

· Used to insure that a datafile is valid before a restore

b. What is ODBC? What are the uses of ODBC? Under what circumstances we use this technology? (7)

Ans:

ODBC – Open DataBase Connectivity (ODBC) enable the integration of SQL with a general-purpose programming language. ODBC expose database capabilities in a standardized way to the application programmer through an application programming interface (API). In contrast to Embedded SQL, ODBC allows a single execution to access different DBMSs without recompilation. Thus, while Embedded SQL is DBMS-independent only at the source code level, applications using ODBC are DBMS-independent at the source code level and at the level of the execution.

All direct interaction with a specific DBMS happens through a DBMS-specific driver. A driver is a software program that translates the ODBC calls into DBMS-specific calls. Drivers are loaded dynamically on demand since the DBMSs, the application is going to access are known only at run-time. Available drivers are registered with a driver manager. The driver translates the SQL commands from the application into equivalent commands that the DBMS understands. An application that interacts with a data source through ODBC selects a data source, dynamically loads the corresponding driver, and establishes a connection with the data source.

ODBC achieves portability at the level of the execution by introducing an extra level of indirection. In addition, using ODBC, an application can access not just one DBMS but several different ones simultaneously.

Q.20 a. List any two significant differences between a file processing system and a DBMS. (4)

Ans:

File Processing System vs. DBMS

Data Independence - Data independence is the capacity to change the schema at one level of a database system without having to change the schema at the next level. In file processing systems the data and applications are generally interdependent, but DBMS provides the feature of data independence.

Data Redundancy – Data redundancy means unnecessary duplication of data. In file processing systems there is redundancy of data, but in DBMS we can reduce data redundancy by means of normalization process without affecting the original data. If we do so in file processing system, it becomes too complex.

b. Differentiate between various levels of data abstraction. (5)

Ans:

Data Abstraction – Abstraction is the process to hide the irrelevant things from the users and represent the relevant things to the user. Database systems are often used by non-computer professionals so that the complexity must be hidden from database system users. This is done by defining levels of abstraction at which the database may be viewed. There is logical view or external view, conceptual view and internal view or physical view.

o External View – This the highest level of abstraction as seen by a user. It describes only the part of entire database, which is relevant to a particular user.

o Conceptual View – This is the next higher level of abstraction which is the sum total of Database Management System user's views. It describes what data is actually stored in the database. It contains information about entire database in terms of a small number of relatively simple structure.

o Internal View – This is the lowest level of abstraction. It describes how the data is physically stored

Q.21 a. What are the various symbols used to draw an E-R diagram? Explain with the help of an example how weak entity sets are represented in an E-R diagram. (6)

Ans:

Various symbols used to draw an E-R diagram

Weak Entity Sets - An entity set that does not have a key attribute is called weak entity set. A weak entity must participate in an identifying relationship type with an owner or identifying entity type Entities are identified by the combination of:

§ A partial key of the weak entity set

§ The primary key of the identifying entity set

Example:

Suppose that a DEPENDENT entity is identified by the dependent’s first name and birhtdate, and the specific EMPLOYEE that the dependent is related to. DEPENDENT is a weak entity type with EMPLOYEE as its identifying entity type via the identifying relationship type DEPENDENT_OF

b. Define the following terms: (8)

a. Primary key.

Ans:

Primary Key – Primary key is one of the candidate keys. It should be chosen such that its attribute values are never, or very rarely, changed.

b. DML.

Ans:

Data Manipulation Language (DML) – A data manipulation language is a language that enables users to access or manipulate data as organized by the appropriate data model.

c. Multivalued attribute.

Ans:

Multivalued Attribute – Multivalued attribute may have more than one value for an entity. For example, PreviousDegrees of a STUDENT.

d. Relationship instance.

Ans:

Relationship Instance – A relationship is an association among two or more entities. An instance of relationship set is a set of relationships.

Q.22 a. Define a table in SQL called Client, which is used to store information about the clients. Define CLIENT_NO as the primary key whose first letter must start with ‘C’. Also ensure that the column ‘NAME’ should not allow NULL values.

Column name	Data type	Size
CLIENT_NO	Varchar2	6
NAME	Varchar2	20
ADDRESS1	Varchar2	30
ADDRESS2	Varchar2	30
CITY	Varchar2	15
STATE	Varchar2	15
PINCODE	Number	6
BAL_DUE	Number	10, 2

(7)

Ans:

CREATE TABLE CLIENT

( CLIENT_NO VARCHAR2(6) PRIMARY KEY CHECK (CLIENT_NO LIKE ‘C%’),

NAME VARCHAR2(20) NOT NULL,

ADDRESS1 VARCHAR2(30),

ADDRESS2 VARCHAR2(30),

CITY VARCHAR2(15),

STATE VARCHAR2(15),

PINCODE NUMBER(6),

BAL_DUE NUMBER(10,2))

b. An orchestra database consists of the following relations: (3.5 x 2=7)

CONDUCTS (conductor, composition)

REQUIRES (composition, Instrument)

PLAYS (Player, Instrument)

Give the relational calculus queries for the following:

(i) List the compositions and the players.

Ans:

Tuple Calculus:

{r[Composition] || p[Player] | r Î REQUIRES Ù p Î PLAYS

Ù r[Instrument] = p[Instrument]}

Domain Calculus:

{c || p | $i₁, i₂ (<c, i₁> Î REQUIRES Ù <p, i₂> Î PLAYS Ù i₁ = i₂)}

(ii) List the compositions which require the ‘violin’ and the ‘congo’

Ans:

Tuple Calculus:

{r[Composition] | r Î REQUIRES Ù $u (u Î REQUIRES

Ù r[Composition] = u[Composition] Ù r[Instrument] = ‘violin’

Ù u[Instrument] = ‘congo’)}

Domain Calculus:

{c | $i₁, c₂, i₂ (<c, i₁> Î REQUIRES Ù <c₂, i₂> Î REQUIRES

Ù c₁ = c Ù i₁ = ‘violin’ Ù i₂ = ‘congo’)}

Q.23 a. What is ODBC? How do data access APIS work? (7)

Ans:

ODBC – Open DataBase Connectivity (ODBC) enables the integration of SQL with a general-purpose programming language. ODBC expose database capabilities in a standardized way to the application programmer through an application programming interface (API). In contrast to Embedded SQL, ODBC allows a single execution to access different DBMSs without recompilation. In the application program interface approach, the program communicates with the RDBMS using a set of functions called the Application Program Interface (API). The program passes the SQL statements to the RDBMS using API calls and uses API calls to retrieve the results. In this method, the precompiler is not required.

All direct interaction with a specific DBMS happens through a DBMS-specific driver. A driver is a software program that translates the ODBC calls into DBMS-specific calls. Drivers are loaded dynamically on demand since the DBMSs the application is going to access are known only at run-time. Available drivers are registered with a driver manager. The driver translates the SQL commands from the application into equivalent commands that the DBMS understands. An application that interacts with a data source through ODBC selects a data source, dynamically loads the corresponding driver, and establishes a connection with the data source.

b. What is an INDEX as defined in ORACLE? Write the syntax of creating an INDEX.

Create an index for the table Client, field CLIENT_NO of Q. 23 (a). (2+2+3)

Ans:

Indexes in Oracle – Index is typically a listing of keywords accompanied by the location of information on a subject. In other words, An index can be viewed as an auxiliary table which contains two fields: the key and the location of the record of that key. Indexes are used to improve the performance of the search operation. Indexes are not strictly necessary to running Oracle, they do speed the process.

Syntax of Creating an Index:

CREATE [BITMAP] [UNIQUE] INDEX <index_name> ON

<table_name>(<column_name1> [, <column_name2>] . . .);

Command:

CREATE INDEX client_client_no ON client(client_no);

Q.24 Consider the following relational database:

STUDENT (name, student#, class, major)

COURSE (course name, course#, credit hours, department)

SECTION (section identifier, course#, semester, year, instructor)

GRADE_REPORT (student#, section identifier, grade)

PREREQUISITE (course#, presequisite#)

Specify the following queries in SQL on the above database schema. (3.5 x 4=14)

(i) Retrieve the names of all students majoring in ‘CS’ (Computer Science).

Ans:

SELECT NAME FROM STUDENT WHERE MAJOR = ‘CS’

(ii) Retrieve the names of all courses taught by Professor King in 1998.

Ans:

SELECT COURSE_NAME FROM COURSE C, SECTION S

WHERE C.COURSE# = S.COURSE#

AND INSTRUCTOR = ‘KING’ AND YEAR = 1998

SELECT COURSE_NAME FROM COURSE

WHERE COURSE# IN (SELECT COURSE# FROM SECTION

WHERE INSTRUCTOR = ‘KING’ AND YEAR = 1998)

(iii) Delete the record for the student whose name is ‘Smith’ and whose student number is 17.

Ans:

DELETE FROM STUDENT WHERE NAME = ‘Smith’ AND STUDENT# = 17

(iv) Insert a new course <’Knowledge Engineering’, ‘CS4390’, 3, ‘CS’>

Ans:

INSERT INTO COURSE

VALUES(‘Knowledge Engineering’, ‘CS4390’, 3, ‘CS’)

Q.25 a. Explain the concept of a data model. What data models are used in

database management systems? (7)

Ans:

Data Model – Model is an abstraction process that hides irrelevant details while highlighting details relevant to the applications at hand. Similarly, a data model is a collection of concepts that can be used to describe structure of a database and provides the necessary means to achieve this abstraction. Structure of database means the data types, relationships, and constraints that should hold for the data. In general a data model consists of two elements:

· A mathematical notation for expressing data and relationships.

§ Operations on the data that serve to express queries and other manipulations of the data.

Data Models used in DBMSs:

§ Hierarchical Model - It was developed to model the many types of hierarchical organizations that exist in the real world. It uses tree structures to represent relationship among records. In hierarchical model, no dependent record can occur without its parent record occurrence and no dependent record occurrence may be connected to more than one parent record occurrence.

§ Network Model - It was formalised in the late 1960s by the Database Task Group of the Conference on Data System Language (DBTG/CODASYL). It uses two different data structures to represent the database entities and relationships between the entities, namely record type and set type. In the network model, the relationships as well as the navigation through the database are predefined at database creation time.

§ Relational Model - The relational model was first introduced by E.F. Codd of the IBM Research in 1970. The model uses the concept of a mathematical relation (like a table of values) as its basic building block, and has its theoretical basis in set theory and first-order predicate logic. The relational model represents the database as a collection of relations.

§ Object Oriented Model – This model is based on the object-oriented programming language paradigm. It includes the features of OOP like inheritance, object-identity,

encapsulation etc. It also supports a rich type system, including structured and collection types.

§ Object Relational Model – This model combines the features of both relational model and object oriented model. It extends the traditional relational model with a variety of features such as structured and collection types.

b. Briefly explain the differences between a stand alone query language,

embedded query language and a data manipulation language. (7)

Ans:

Stand alone Query Language – The query language which can be used interactively is called stand alone query language. It does not need the support of a host language.

Embedded Query Language – A query language (e.g., SQL) can be implemented in two ways. It can be used interactively or embedded in a host language. The use of query language commands within a host language (e.g., C, Java, etc.) program is called embedded query language. Although similar capabilities are supported for a variety of host languages, the syntax sometimes varies.

Data Manipulation Language (DML) – A data manipulation language is a language that enables users to access or manipulate data as organized by the appropriate data model.

Q.26 a. Consider the following relations for a database that keeps track of business trips of salespersons in a sales office:

SALESPERSON (SSN, Name, start_year, Dept_no)

TRIP (SSN, From_city, To_city, Departure_Date, Return_Date, Trip_ID)

EXPENSE(TripID, Account#, Amount)

Specify the following queries in relational algebra: (4 x 3 = 12)

(i) Give the details (all attributes of TRIP) for trips that exceeded $2000 in expenses.

Ans: p_TRIP. _*(s _{amount > 2000} (TRIP wv EXPENSE))

(ii) Print the SSN of salesman who took trips to ‘Honolulu’.

Ans: p_SSN(s _{to_city = ‘Honolulu’} (TRIP))

(iii) Print the trip expenses incurred by the salesman with SSN= ‘234-56-7890’.Note that the salesman may have gone on more than one trip. List them individually.

Ans: p_{EXPENSE.tripid,
amount}(s _{SSN = ‘234-56-7890’} (TRIP wv EXPENSE))

b. What is the difference between a key and a superkey? (2)

Ans:

Key – A key a single attribute or a combination of two or more attributes of an entity set that is used to identify one or more instances (rows) of the set (table). It is a minimal combination of attributes.

Super Key – A super key is a set of one or more attributes that, taken collectively, allows us to identify uniquely a tuple in the relation.

Q.27 a. Why are cursors necessary in embedded SQL? (2)

Ans:

A cursor is an object used to store the output of a query for row-by-row processing by the application programs. SQL statements operate on a set of data and return a set of data. On other hand, host language programs operate on a row at a time. The cursors are used to navigate through a set of rows returned by an embedded SQL SELECT statement. A cursor can be compared to a pointer.

b. Write a program in embedded SQL to retrieve the total trip expenses of the salesman named ‘John’ for the relations of Q. 29 (a) (6)

Ans:

EXEC SQL BEGIN DECLARE SECTION;

long total_expenses;

EXEC SQL END DECLARE SECTION;

EXEC SQL

SELECT SUM(AMOUNT) INTO :total_expenses FROM EXPENSE WHERE TRIPID IN (SELECT TRIP_ID FROM TRIP

WHERE SSN = (SELECT SSN FROM SALEPERSON WHERE NAME = ‘John’));

Printf(“\nThe total trip expenses of the salesman John is: %ld”, total_expenses);

c. What are views? Explain how views are different from tables. (6)

Ans:

A view in SQL terminology is a single table that is derived from other tables. These 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. This limits the possible update operations that can be applied to views, but it does not provide any limitations on querying a view. A view represents a different perspective of a base relation(s). The definition of a view in a create view statement is stored in the system catalog. Any attribute in the view can be updated as long as the attribute is simple and not derived from a computation involving two or more base relation attribute. View that involve a join may or may not be updatable. Such views are not updatable if they do not include the primary keys of the base relations.

Q.28 a. What do you mean by integrity constraints? Explain the two constraints, check and foreign key in SQL with an example for each. Give the syntax. (8)

Ans:

Integrity Constraints –An integrity constraint is a condition specified on a database schema and restricts the data that can be stored in an instance of the database. If a database instance satisfies all the integrity constraints specified on the database schema, it is a legal instance. A DBMS enforces integrity constraints, in that it permits only legal instances to be stored in the database.

CHECK constraint – CHECK constraint specifies an expression that must always be true for every row in the table. It can’t refer to values in other rows.

Syntax:

ALTER TABLE <table_name>

ADD CONSTRAINT <constraint_name> CHECK(<expression>);

FOREIGN KEY constraint – A foreign key is a combination of columns with values based on the primary key values from another table. A foreign key constraint, also known as referential integrity constraint, specifies that the values of the foreign key correspond to actual values of the primary or unique key in other table. One can refer to a primary or unique key in the same table also.

Syntax:

ALTER TABLE <table_name>

ADD CONSTRAINT <constraint_name> FOREIGN KEY(<column_name(s)>)

REFERENCES <base_table>(<column_name>) ON {DELETE | UPDATE}

CASCADE;

b. Define the following constraints for the table client of Q.23 (a). (6)

(i) BAL_DUE must be at least 1000.

Ans:

ALTER TABLE CLIENT

ADD CONSTRAINT CLIENT_BAL_DUE_C1 CHECK(BAL_DUE < 1000);

(ii) NAME is a unique key.

Ans:

ALTER TABLE CLIENT

ADD CONSTRAINT CLIENT_NAME_U UNIQUE(NAME);

Q.29 a. What are the different types of database end users? Discuss the main activities of each. (7)

Ans:

End-Users – End users are the people whose jobs require access to the database for querying, updating, and generating reports; the database primarily exists for their use. The different types of end-users are:

§ Casual end-users – occasionally access the database, need different information each time

§ Naive or Parametric end users – includes tellers, clerks, etc., they make up a sizable portion of database end-users, their main job function revolves around constantly querying and updating the database

§ Sophisticated end-users – includes engineers, scientists, business analyst, etc., use for their complex requirements

§ Stand-alone users – maintain personal databases by using ready-made program packages, provide easy-to-use menu-based or graphics-based interfaces

b. Discuss the typical user friendly interfaces and the types of users who use each. (7)

Ans:

User-friendly interfaces provided by a DBMS may include the following:

· Menu-Based Interfaces for Web Clients or Browsing – These interfaces present the user with lists of options, called menus, that lead the user through the formulation of a request. Pull-down menus are a very popular techniques in Web-based user interfaces. They are also used in browsing interfaces, which allow a user to look through the contents of a database in an exploratory and unstructured manner.

· Forms-Based Interfaces – A forms-based interface displays a form to each user. Forms are usually designed and programmed for naive users and interfaces to canned transactions. Many DBMSs have forms specification languages.

· Graphical User Interfaces (GUIs) – A GUI typically displays a schema to the user in diagrammatic form. The user can then specify a query by manipulating the diagram. In many cases, GUIs utilizes both menus and forms. Most GUIs use a pointing device to pick certain parts of the displayed schema diagram.

· Natural Language Interfaces – These interfaces accept requests written in English or some other language and attempt to “understand” them. A natural language interface usually has its own “schema,” which is similar to the database conceptual schema, as well as a dictionary of important words.

· Interfaces for Parametric Users – Parametric users, such as bank tellers, often have a small set of operations that they must perform repeatedly. The interfaces for these users usually have a small set of abbreviated commands with the goal of minimizing the number of keystrokes required for each request.

· Interfaces for the DBA (Database Administrator)– Most database systems contain privileged commands that can be used only by the DBA’s staff. These include commands for creating accounts, setting system parameters, granting account authorization, changing a schema, and reorganizing the storage structures of a database.

Q.30 a. Differentiate between (3 x 3)

(i) Procedural and non procedural languages.

Ans:

Procedural and non procedural languages - A procedural language specifies the operations to be performed on the existing data to derive the results. It also specifies the sequence of operations in which they will be performed. But, a non procedural language specifies only the result or information required not how it is obtained.

(ii)Key and superkey.

Ans:

Key and superkey - A key a single attribute or a combination of two or more attributes of an entity set that is used to identify one or more instances (rows) of the set (table). If we add some additional attributes to a primary key then that augmented key is called as super key. Therefore, the primary key is the minimum super key.

(iii) Primary and secondary storage.

Ans:

Primary and secondary storage – Primary storage device stores the data temporarily. Primary storage is generally used by the processing unit to temporary store the data, intermediate results, and the final results before storing to the secondary storage because the secondary storage devices are not directly accessible by the CPU. But, if we want to store data permanently then the secondary storage devices are required. Secondary storage devices are slower than the primary storage devices.

b. With the help of an example show how records can be deleted and updated in QBE. (5)

Ans:

(i) Increase Pay_Rate of employees with the skill of ‘cook’ by 10%.

EMPLOYEE

Emp#

Name

Skill

Pay_Rate

Cook

PX

PX * 1.1

(ii) Delete employee record for Emp# 123459.

EMPLOYEE	Emp#	Name	Skill	Pay_Rate
D.	123459

Q.31 a. Describe cardinality ratios and participation constraints for relationship types. (4)

Ans:

Cardinality Ratios – The cardinality ratios for a relationship type specifies the maximum number of relationship instances that an entity can participate in. The possible cardinality ratios for relationship types are one-to-one (1:1), one-to-many or many-to-one (1:M or M:1), and many-to-many (M:N).Participation Constraints – The participation constraint specifies whether the existence of an entity depends on its being related to another entity via the relationship type. This constraint specifies the minimum number of relationship instances that each entity can participate in. It is sometimes called the minimum cardinality constraint. There are two types of participation constraints – total and partial.

b. Information about a bank is about customers and their account. Customer has a name, address which consists of house number, area and city, and one or more phone numbers. Account has number, type and balance. We need to record customers who own an account. Account can be held individually or jointly. An account cannot exist without a customer.

Arrive at an E-R diagram. Clearly indicate attributes, keys, the cardinality ratios and participation constraints. (10)

Ans:

Q.32 a. Describe the static hash file with buckets and chaining and show how insertion, deletion and modification of a record can be performed. (9)

Ans:

In static hash file organization, the term bucket is used to denote a unit of storage that can store one or more records. A file consists of buckets 0 through N-1, with one primary page per bucket initially and additional overflow

pages chained with bucket, if required later.

In hashing scheme, a hash function, h, is performed on the key of the record to identify the bucket to which data record belongs to. The hash function is an important component of the hashing approach. The main problem with static hash file is that the number of buckets is fixed.

Insertion of a record – To insert a data entry, the hash function is used to identify the correct bucket and then put the data entry there. If there is no space for this data entry, a new overflow page will be allocated, put the data entry on this page, and the page to the overflow chain of the bucket.

Deletion of a record – To delete a data entry, the hash function is used to identify the correct bucket, locate the data entry by searching the bucket, and then remove it. If the data entry is the last in an overflow page, the overflow page is removed from the overflow chain of the bucket and added to a list of free pages.

Modification of a record – To modify a data entry, the hash function is used to identify the correct bucket, locate the data entry by searching the bucket and get it, modify the data entry, and then rewrite the modified data entry on it.

b. What are the reasons for having variable length records? What types of separator characters are needed for each? (5)

Ans:

Variable-Length Records – Variable-length records are those records, which are of different sizes. A file may contain variable-length records in any of the following situations:

· Records having variable length fields – In this case, the end-of-field symbol along with end-of-record symbol can be used as the separator characters.

· Records having repeating fields – In this case, the number of repetitions of repeating fields can be used with end-of-record symbol as the separator characters.

· Records having optional fields – In this case, the end-of-field symbol along with end-of-record symbol can be used as the separator characters.

· File containing records of different record types - In this case, a special field (or control field) can be prefixed with each record and end-of-record symbol can be used as the separator characters.

In each of the above situations either we can use end-of-record symbol or the length of each record can be prefixed at the beginning of the record for the separation of the variable-length records.

Q.33 Define the following terms (7 x 2 = 14)

(i) Derived and stored attribute.

Ans:

Derived and Stored Attribute - In some cases, two or more attribute values are related, for example, Age and BirthDate attributes of a person. For particular person entity, the value of Age can be determined from the current date and the value of that person’s BirthDate. Hence, the attribute Age is called as derived attribute and the attribute BirthDate is called as stored attribute.

(ii) Distributed system.

Ans:

Distributed System – A distributed system consists of a number of processing elements that are interconnected by a computer network and that cooperate in performing certain assigned tasks.

(iii) Interblock gap.

Ans:

Interblock Gap – A track of a disk is divided into equal-sized disk blocks. Blocks are separated by fixed-size gaps, called as interblock gaps, which include specially coded control information written during disk initialization.

(iv) Degree of a relation.

Ans:

Degree of a Relation – The degree or arity of a relation is the number of attributes n of its relation schema.

(v) Catalog.

Ans:

Catalog – A relational DBMS maintains information about every table and index that it contains. A catalog is a collection of special tables, which stores the descriptive information of every table and index.

(vi) Conceptual schema.

Ans:

Conceptual Schema – Conceptual schema describes the structure of the whole database for a community of users. It hides the details of physical storage structures and concentrates on describing entities, data types, relationships and constraints.

(vii) DDL and SDL.

Ans:

DDL and SDL – The data definition language (DDL) is used by DBA and database designers to define conceptual schema, internal schema, and mappings between these two. In some DBMSs, a clear separation is maintained between conceptual schema and internal schema. In that case, DDL is used to specify the conceptual schema only. Another language, storage definition language (SDL) is used to specify the internal schema. The mappings between the two schemas may be specified in either one of these languages.

Q.34 a. Define a relation. (2)

Ans:

Relation – A relation is a named two-dimensional table of data. Mathematically, a relation can be defined as a subset of the cartesian product of a list of domains. Each relation consists of a set of named columns and an arbitrary number of rows. The columns correspond to the fields describing each tuple in the table or relation. The rows correspond to each instance of the entity described by the table or relation.

b. Describe entity integrity and referential integrity. Give an example of each. (6)

Ans:

Entity Integrity Rule – If the attribute A of relation R is a prime attribute of R then A cannot accept null values.

Referential Integrity Rule – In referential integrity, it is ensured that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.

For example:

STUDENT

Enrl No	Roll No	Name	City	Mobile
11	17	Ankit Vats	Delhi	9891663808
15	16	Vivek Rajput	Meerut	9891468487
6	6	Vanita	Punjab
33	75	Bhavya	Delhi	9810618396

GRADE

Roll No	Course	Grade
6	C	A
17	VB	C
75	VB	A
6	DBMS	B
16	C	B

§ Roll No is the primary key in the relation STUDENT and Roll No + Course is the primary key of the relation GRADE. (Entity Integrity)

§ Roll No in the relation GRADE (child table) is a foreign key, which is referenced from the relation STUDENT (parent table). (Referential Integrity).

c. Consider the two relations given below

R S

A	B	C
A1	b1	c1	D	A	F
Null	b2	null	d1	a1	f1
a1	b1	c1	d1	a2	null

Given that A is the primary key of R, D is the primary key of S and there is a referential integrity between S.A and R.A, discuss all integrity constraints that are violated. (6)

Ans:

(i) Primary key of R contains the ‘null’ value and the value ‘a1’ is duplicated, hence it violates the entity integrity constraint in the relation R.

(ii) In primary key of S, the value ‘d1’ is duplicated, hence it violates the entity integrity constraint in the relation S.

(iii) The foreign key S.A contains the value ‘a2’, which is not available in the parent key R.A, hence it violates the referential integrity constraint in the relation S.

Q.35 Given the following relations

TRAIN (NAME, START, DEST)

TICKET (PNRNO., START, DEST, FARE)

PASSENGER (NAME, ADDRESS, PNRNO.)

Write SQL expressions for the following queries: (3.5 x 4 = 14)

Note: Assume NAME of Train is a column of Ticket.

(i) List the names of passengers who are travelling from the start to the destination station of the train.

Ans:

SELECT P.NAME FROM TRAIN T, TICKET I, PASSENGER P

WHERE P.PNRNO = I.PNRNO AND T.NAME = I.NAME

AND T.START = I.START AND T.DEST = I.DEST

(ii) List the names of passengers who have a return journey ticket.

Ans:

SELECT NAME FROM PASSENGER

WHERE PNRNO IN (SELECT DISTINCT A.PNRNO

FROM TICKET A, TICKET B WHERE A.PNRNO = B.PNRNO

AND A.START = B.DEST AND A.DEST = B.START)

(iii) Insert a new Shatabdi train from Delhi to Bangalore.

Ans:

INSERT INTO TRAIN

VALUES(‘Shatabdi’, ‘Delhi’, ‘Banglore’)

(iv) Cancel the ticket of Tintin.

Ans:

DELETE FROM TICKET

WHERE PNRNO = (SELECT PNRNO FROM PASSENGER

WHERE NAME = ‘Tintin’)

Q.36 a. Define outer union operation of the relational algebra. Compute the outer union for the relations R ans S given below. (2 x 3)

R S

A	B	C	D	A	F
a1	b1	c1	d1	a1	f1
a3	b2	c2	d1	a2	null

Ans:

R.A	B	C	D	S.A	F
a1	b1	c1	d1	a1	f1
a3	b2	c2	Null	Null	Null
Null	Null	Null	d1	a2	Null

b. Given the following relations (3 x 3)

Vehicle (Reg_no, make, colour)

Person(eno, name, address)

Owner(eno, reg_no)

Write expressions in the relational algebra to answer the following queries:-

(i) List the reg_no of vehicles owned by John.

Ans:

p _{reg_no} (s _{name=’John’} (PERSON wv OWNER))

(ii) List the names of persons who own maruti cars.

Ans:

p _name (s _{make=’maruti’} (PERSON wv OWNER wv VEHICAL))

(iii) List all the red coloured vehicles.

Ans:

s _{colour=’red’} (VEHICAL)

Q.37 a. Describe DROP TABLE command of SQL with both the options – CASCADE and RESTRICT. (5)

Ans:

DROP TABLE command – This command drops the specified table. Dropping a table also drops indexes and grants associated with it. Objects built on dropped tables are marked invalid and cease to work.

CASCADE and RESTRICT options – The DROP TABLE with RESTRICT option destroy the table unless some view or integrity constraint refer to the given table; if so, the command fails. But with the CASCADE option, any referencing views or integrity constraints are (recursively) dropped as well.

b. With respect to Oracle describe the following: (3 x 3)

(i) Data Block.

Ans:

Data Block – In oracle the data blocks are referred to as tablespaces. A tablespace is an area of disk consisting of one or more disk files. A tablespace can contain many tables, indexes, or clusters.

(ii) Data dictionary.

Ans:

Data Dictionary – Data dictionaries are the system tables that contain descriptions of the database objects and how they are structured.

(iii) Segments.

Ans:

Segments – Each table has single area of disk space, called segment, set aside for it in the tablespace. Segments consists of contiguous sections called extents.

ADVANTAGES OF DBMS

DISADVANTAGES OF DBMS

STUDENT

Name

Mobile

GRADE

Grade

R = P È Q

R = P – Q

R = P ´ Q

P:

Name

101

Jones

100

John

103

Smith

104

Lalonde

104

Lalonde

R = P È Q

Name

100

John

101

Jones

101

Jones

103

Smith

103

Smith

104

Lalonde

R = P ´ Q

P.Name

101

Jones

100

John

101

Jones

104

Lalonde

103

Smith

100

John

103

Smith

104

Lalonde

104

Lalonde

100

John

104

Lalonde

104

Lalonde

R = sB(P)

Smith

Lalonde

Projection of relation EMPLOYEE over attribute Name

Smith

Lalonde

Referential Integrity Rule – In referential integrity, it is ensured that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation.

CREATE TABLE DEPT

CREATE TABLE EMP

Command:

CREATE VIEW SUP_PART (SNO, NAME, PNO, PNAME) AS

STUDENT

Name

Mobile

Q.10 Explain the followings : (14)

STUDENT

Name

Mobile

R = s_B(P)