Some Important Old Question Solution ( SEE 2072, 2073)

1.    Define Database Management System.

A database-management system (DBMS) is a computer-software application that interacts with end-users, other applications, and the database itself to capture and analyze data. A general-purpose DBMS allows the definition, creation, querying, update, and administration of databases.

OR

A database management system (DBMS) is a collection of programs that enables you to store, modify, and extract information from a database.

OR

A database management system (DBMS) is a software package designed to define, manipulate, retrieve and manage data in a database. A DBMS generally manipulates the data itself, the data format, field names, record structure and file structure.

OR

A database management system (DBMS) is the software that allows a computer to store, retrieve, add, delete, and modify data. A DBMS manages all primary aspects of a database, including managing data manipulation, such as user authentication, as well as inserting or extracting data.

2.    What is Select Operator?

3.    Define relation in SQL.

In SQL, a database language for relational databases, relations are represented by tables, where each row of a table represents a single tuple, and where the values of each attribute form a column.

4.    What do you mean by data integrity?

Data integrity refers to the accuracy and consistency of data stored in a database, data warehouse, data mart or other construct.

5.    Define Data normalization.

Normalization is the process of removing redundant data from tables in order to improve storage efficiency, data integrity and scalability.

6.    What is transaction recovery?

A transaction is a set of changes that must all be made together. It is a program unit whose execution may or may not change the contents of a database. Transaction is executed as a single unit.

7.    What do you mean by security?

Security is the defense of digital information and IT assets against internal and external, malicious and accidental threats. This defense includes detection, prevention and response to threats through the use of security policies, software tools and IT services.

Group B (Short Questions)

8.    Why E-R Model is used? Explain in short.

E-R Model is used because it maps well to the relational model. The constructs used in the ER model can easily be transformed into relational tables. It is simple and easy to understand with a minimum of training. Therefore, the model can be used by the database designer to communicate the design to the end user.

9.    Write any four operators used in relational algebra.

Operators used in relational algebra

a.    Common Set Operators

§  Union (U)

§  Intersection(∩)

§  Difference (─)

§  Product(X)

b.    Native Relational Operators

§  Selection(σ)

§  Projection (π)

§  Join(⋈ )

§  Division

10.  What is the syntax of tuple relational calculus?

{t¦P(t)} ( to express a query in TRC)

t[A] ( To denote the value of tuple t on attribute A

11.  Define domain relational calculus.

In DRC, the variables take their values from domains of attributes rather than tuples of relation. To form a relation of degree "n" for a query result, we must have "n" of these domain variables. (i.e. one for each attributes). In general, a DRC expression is expressed as {d1, d2, … … …dn | p(d1, d2, … … … dm)} where m>=n . Here d1, d2, … dn represent the domain variable and P(d1, d2, … dm) represent a formula composed of atom.

12.  Define embedded SQL.

Embedded SQL is a method of combining the computing power of a programming language and the database manipulation capabilities of SQL. Embedded SQL statements are SQL statements written in line with the program source code of the host language.

13.  Write the features of SQL. Write any four.

Following are the features of SQL.

a.    It can contain SQL Procedural Language statements and features which support the implementation of control-flow logic around traditional static and dynamic SQL statements.

b.    SQL Are easy to implement, because they use a simple high-level, strongly typed language.

c.    SQL functions are more reliable than equivalent external functions.

d.    It Support input parameters.

e.    SQL scalar functions return a scalar value.

f.     SQL table functions return a table result set.

g.    It Support a simple, but powerful condition and error-handling model.

h.    It Allow us to easily access the SQLSTATE and SQLCODE values as special variables.

i.      Reside in the database and are automatically backed up and restored as part of backup and restore operations.

j.      It can be invoked wherever expressions in an SQL statement are supported.

k.    It Support nested functions calls to other SQL functions or functions implemented in other languages.

l.      It Support recursion (when dynamic SQL is used in compiled functions).

m.   It can be invoked from triggers.

n.    Many SQL statements can be included within SQL functions, however there are exceptions. For the complete list of SQL statements that can included and executed in SQL functions, 

14.  How is view constructed? Write its syntax.

CREATE VIEW View_Name
AS SELECT Column_Name(s)
FROM Table_Name
WHERE Condition
GROUP BY [ASC/DSC];

15.  List the constraints in data integrity.

PRIMARY KEY Constraints

FOREIGN KEY Constraints

UNIQUE Constraints

CHECK Constraints

DEFAULT Definitions

Allowing Null Values

16.  Define transaction constraints.

17.  What re the conditions to be a relation in 2NF?

18.  What is media recovery? Explain in short.

19.  What do you mean by intent locking?

20.  Why is recovery necessary?

21.  What is the use of encryption?

Group C (Long Questions)

22.  Write any five differences between relational calculus and relational algebra.

23.  What is aggregate function? List out the aggregate functions and give example of any one of them.

24.  Explain integrity constraints.

25.  What is 3NF? Explain with example.

26.  What is trigger? What are the advantages and disadvantages of trigger?

27.  Explain two phase commit protocol with example.

28.  What is statistical database? How does statistical database ensure data security?

2073 (2017)

Group A (Very Short Questions)

1.    What do you understand by data and information?

2.    Define tuple and domain.

The basic relational building block of relational database is the domain (somewhat similar, but not equal to, a data type). A tuple is a finite sequence of attributes, which are ordered pairs of domains and values.

3.    What is catalog?

In computing, a catalog is a directory of information about data sets, files, or a database. A catalog usually describes where a data set, file or database entity is located and may also include other information, such as the type of device on which each data set or file is stored.

4.    Define data integrity.

5.    What do you understand by database normalization?

6.    What are the states of transaction?

7.    Why security is necessary in database?

Group B (Short Questions)

8.    Write any four applications of DBMS.

(i) Effective and efficient management of data

(ii) Query processing and management

(iii) Easy to understand and user friendly

(iv) Security and integrity of data

(v) Better Decision making

(vi) Data sharing and storage

(vii) Better access to accurate data

(viii) Ensures error free information

9.    Give general syntax of domain relational calculus.

10.  Why γ (Gamma) operator is used in relational algebra?

11.  What is project (π) operator?

12.  Define primary key and foreign key.

Primary Key: The primary key is an attribute or a set of attributes that uniquely identify a specific instance of an entity. Every entity in the data model must have a primary key whose values uniquely identify instances of the entity.

Foreign Key: Foreign keys are the attributes of a table, which refers to the primary key of some another table. Foreign Keys permit only those values, which appears in the primary key of the table to which it refers or may be null.

13.  What are the types of data model? Define any one.

Data Model can be defined as an integrated collection of concepts for describing and manipulating data, relationships between data, and constraints on the data in an organization.

They fall into three broad categories:

·         Object Based Data Models

• Entity-Relationship

The Entity-Relationship model has emerged as one of the main techniques for modeling database design and forms the basis for the database design methodology.

• Object Oriented

The object oriented data model extends the definition of an entity to include, not only the attributes that describe the state of the object but also the actions that are associated with the object, that is, its behavior. The object is said to encapsulate both state and behavior.

• Semantic

Entities in semantic systems represent the equivalent of a record in a relational system or an object in an OO system but they do not include behavior (methods). They are abstractions 'used to represent real world (e.g. customer) or conceptual (e.g. bank account) objects.

• Functional

The functional data model is now almost twenty years old. The original idea was to' view the database as a collection of extensionally defined functions and to use a functional language for querying the database.

·         Physical Data Models

Physical data models describe how data is stored in the computer, representing information such as record structures, record ordering, and access paths. There are not as many physical data models as logical data models, the most common one being the Unifying Model.

·         Record Based Data Models

Record based logical models are used in describing data at the logical and view levels. In contrast to object based data models, they are used to specify the overall logical structure of the database and to provide a higher-level description of the implementation. Record based models are so named because the database is structured in fixed format records of several types. Each record type defines a fixed number of fields, or attributes, and each field is usually of a fixed length.

The three most widely accepted record based data models are:

• Hierarchical Model

• Network Model

• Relational Model

14.  Mention any four SQL commands.

DDL : Data Definition Language

All DDL commands are auto-committed. That means it saves all the changes permanently in the database.

Command	Description
CREATE	to create new table or database
ALTER	for alteration
TRUNCATE	delete data from table
DROP	to drop a table
RENAME	to rename a table

DML : Data Manipulation Language

DML commands are not auto-committed. It means changes are not permanent to database, they can be rolled back.

Command	Description
INSERT	to insert a new row
UPDATE	to update existing row
DELETE	to delete a row
MERGE	merging two rows or two tables

TCL : Transaction Control Language

These commands are to keep a check on other commands and their affect on the database. These commands can annul changes made by other commands by rolling back to original state. It can also make changes permanent.

Command	Description
COMMIT	to permanently save
ROLLBACK	to undo change
SAVEPOINT	to save temporarily

DCL : Data Control Language

Data control language provides command to grant and take back authority.

Command	Description
GRANT	grant permission of right
REVOKE	take back permission.

DQL : Data Query Language

Command	Description
SELECT	retrieve records from one or more table

15.  Define 1NF and 2NF.

First Normal Form: No two Rows of data must contain repeating group of information i.e. each set of column must have a unique value, such that multiple columns cannot be used to fetch the same row.

Second Normal Form: As per the Second Normal Form there must not be any partial dependency of any column on primary key. It means that for a table that has concatenated primary key, each column in the table that is not part of the primary key must depend upon the entire concatenated key for its existence.

16.  What is view? Give syntax of update view.

View is a subset of the database that can join information from several tables together and displays one or more database records on the same page. It allow us to easily reduce the complexity of the end user experience and limit their ability to access data contained in database tables by limiting the data presented to the end user.

Syntax to Update a View is,

UPDATE view-name SET value WHERE condition;

17.  Write any four advantages of database normalization.

The following are the advantages of Database Normalization

·         More efficient data structure.

·         Avoid redundant fields or columns.

·         More flexible data structure i.e. we should be able to add new rows and data values easily

·         Better understanding of data.

·         Ensures that distinct tables exist when necessary.

·         Easier to maintain data structure i.e. it is easy to perform operations and complex queries can be easily handled.

·         Minimizes data duplication.

·         Close modeling of real world entities, processes and their relationships.

18.  Explain system recovery in short.

System recovery is the art of restoring lost or damaged files. This damage can occur when system crashes, a virus infects, accidentally reformat a disk that contains precious data, or we experience some other catastrophe of considerable dimension.

19.  What are the major causes of transaction failure?

20.  Define two-phase commit protocol.

21.  Why is statistical database used?

Group C (Long Questions)

22.  List any four operators used in relational algebra. Explain any one with example.

23.  Why is SQL important? Write any five points.

SQL is important because;

·         It is a database language.

·         SQL is an ANSI and ISO standard computer language for creating and manipulating databases.

·         SQL allows the user to create, update, delete, and retrieve data from a database.

·         SQL is very simple and easy to learn.

·         SQL works with database programs like DB2, Oracle, MS Access, Sybase, MS SQL Sever etc.

24.  What is an integrity constraint? Explain in short.

25.  Study the given table and answer the questions.

Student_ID	Student_Name	Major	Advisor	City
123	Reena	C++	P.K.	Pokhara
123	Reena	Math	Shyam	Dharan
456	Gita	Math	Shyam	Dharan
789	Kuber	DBMS	Sceren	Kathmandu

           

           i In which normal form is the table? Why?

The table is the 2NF because it is in 1st normal form and no non-prime attribute is dependent on any proper subset of any candidate key of the table.

            ii.        Draw a functional dependency diagram of above table.

              iii       Identify transitive dependency.

26.    Write five advantages and disadvantages of trigger.

27.    Explain the properties of transaction.

Atomicity:

This property states that a transaction must be treated as an atomic unit, that is, either all of its operations are executed or none. There must be no state in a database where a transaction is left partially completed. States should be defined either before the execution of the transaction or after the execution/abortion/failure of the transaction.

Consistency:

The database must remain in a consistent state after any transaction. No transaction should have any adverse effect on the data residing in the database. If the database was in a consistent state before

the execution of a transaction, it must remain consistent after the execution of the transaction as well.

Durability:

The database should be durable enough to hold all its latest updates even if the system fails or restarts. If a transaction updates a chunk of data in a database and commits, then the database will hold the modified data. If a transaction commits but the system fails before the data could be written on to the disk, then that data will be updated once the system springs back into action.

Isolation:

In a database system where more than one transaction are being executed simultaneously and in parallel, the property of isolation states that all the transactions will be carried out and executed as if it is the only transaction in the system. No transaction will affect the existence of any other transaction.

28.    Explain DAC and MAC.

Discretionary Access Control (DAC)

In computer security, discretionary access control (DAC) is a type of access control defined by the Trusted Computer System Evaluation Criteria "as a means of restricting access to objects based on the identity of subjects and/or groups to which they belong. The controls are discretionary in the sense that a subject with certain access permission is capable of passing that permission (perhaps indirectly) on to any other subject (unless restrained by mandatory access control)".

Mandatory Discretionary Access Control (MAC)

In computer security, mandatory access control (MAC) refers to a type of access control by which the operating system constrains the ability of a subject or initiator to access or generally perform some sort of operation on an object or target. In practice, a subject is usually a process or thread; objects are constructs such as files, directories, TCP/UDP ports, shared memory segments, IO devices, etc. Subjects and objects each have a set of security attributes. Whenever a subject attempts to access an object, an authorization rule enforced by the operating system kernel examines these security attributes and decides whether the access can take place. Any operation by any subject on any object is tested against the set of authorization rules (aka policy) to determine if the operation is allowed. A database management system, in its access control mechanism, can also apply mandatory access control; in this case, the objects are tables, views, procedures, etc.