Some Important Old Question Solution (SEE 2071)

1.    Write the full form of DBMS.

Database Management System

2.    Define relational calculus.

Relational Calculus is a non-procedural language that can be used to formulate the definition of a relation in terms of one or more database relations.

3.    What is 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.

4.    What is meant by data integrity?

Integrity of data means that data in database is always accurate, such that incorrect information cannot be stored in database. In order to maintain the integrity of data, some integrity constraints are enforced on the database.

5.    What does functional dependency mean?

A functional dependency is an association between two attributes of the same relational database table.

6.    Define transaction.

A transaction is a series of database operations, carried out by a single user or application program, which accesses or changes the contents of the database.

7.    What is data encryption?

Data Encryption is the process of translating plain text data (plaintext) into something that appears to be random and meaningless (cipher text).

Group B (Short Questions)

8.    What is E-R model? Write its notations.

ER model is a conceptual data model that views the real world as entities and relationships. The ER Model notations are;

1.    What is TRC and DRC?

TRC: The tuple relational calculus is based on specifying a number of tuple variables. Each such tuple variable normally ranges over a particular database relation. This means that the variable may take any individual tuple from that relation as its value.

DRC: In domain calculus the variables range over single values from domains of attributes rather than ranging over tuples. To form a relation of degree 'n' for a query result, we must have 'n' of these domain variables-one for each attribute.

2.    Define relational Algebra.

Relational Algebra is a procedural language that can be used to tell the DBMS how to build a new relation from one or more relations in the database. It is procedural language with operations that is performed on one or more existing relation to derive result (another) relations without changing the original relations.

3.    Write any four name of operators used in relational calculus Algebra.

SELECT (σ)

PROJECT (

RENAME (P)

UNION (U)

SET DIFFERENCE (-)

CARTESIAN PRODUCT (X)

SET INTERSECTION (∩)

ASSIGNMENT (←)

4.    What is relational database?

A relational database is a collection of data items organized as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables.

5.    What are the important features of SQL? Write any four of them.

·         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.

6.    How can you create view? Write down the syntax.

A view in SQL is a logical subset of data from one or more tables. View is used to restrict data access.

Syntax for creating a View,

CREATE or REPLACE view view_name AS

SELECT column_name(s) FROM table_name WHERE condition;

7.    List the constraints in data integrity.

There are five types of constraints:

·         A NOT NULL constraint 

·         A unique constraint 

·         A primary key constraint 

·         A foreign key constraint (also referred to as a referential constraint or a referential integrity constraint)

·         A (table) check constraint (also called a check constraint)

8.    Write the syntax of view to retrieve and update.

Syntax to retrieve a View,

SELECT * FROM View_Name; OR

SELECT Column_Name(s) FROM View_Name WHERE Condition;

Syntax to Update a View

UPDATE view-name set value WHERE condition;

If we update a view it also updates base table data automatically.

9.    Write any two differences between trivial and Non-trivial dependencies.

10.  What is two phase commit protocol?

Two-phase commit (2PC) is a protocol for assuring update consistency across distributed databases in which each participant in the transaction commit operation votes to either commit or abort the changes. The two-phase commit protocol provides an automatic recovery mechanism in case a system or media failure occurs during execution of the transaction.

11.  Define dead lock with block diagram.

A deadlock is a condition wherein two or more tasks are waiting for each other in order to be finished but none of the task is willing to give up the resources that other task needs. In this situation no task ever gets finished and is in waiting state forever.

1.    What is transaction concurrency?

When more than one transactions are running simultaneously there are chances of a conflict to occur which can leave database to an inconsistent state. To handle these conflicts we need transaction concurrency (concurrency control) in DBMS, which allows transactions to run simultaneously but handles them in such a way so that the integrity of data remains intact.

OR

Transaction Concurrency (Concurrency control) is a process to ensure that data is updated correctly and appropriately when multiple transactions are concurrently executed in DBMS .

2.    Define the data security mechanism.

It is defined as a mechanism that is designed to detect, prevent, or recover data from a security attack.

Data security mechanism is a mechanism that is designed to detect, prevent, or recover data from a security attack (Any action that compromises the security of information).

Group C (Long Questions)

3.    Consider the following database relation and write relational algebra for the given problems. (Perform SQL query not relational algebra in new syllabus)

Employee

Emp_ID	Name	City	Comp_Name	Salary
`001	Sajina Gurung	Birganj	A	44,000
002	Kiran Sharma	Kathmandu	A	40,000
003	Punam Kanwar	Pokhara	B	38,000
004	Asmit Shreshtha	Palpa	C	25,000

              I.        Find the name of employees who earns more than 38000.

SELECT Nane FROM Employee WHERE Salary >=38000;

            II.        Find the name of all employees who works in A.

SELECT  Name FROM Employee WHERE Comp_Name='A' ;

           III.        Find the name list of all employees.

SELECT Name from Employee ;

          IV.        Re-arrange the name of cities in ascending order.

SELECT *FROM Employee ORDER BY City ;

4.    Define optimization with block diagram.

Finding an alternative with the most cost effective or highest achievable performance under the given constraints, by maximizing desired factors and minimizing undesired ones is referred as optimization.

Database optimization refers to a variety of strategies for reducing database system response time.  Database optimization involves maximizing the speed and efficiency with which data is retrieved. Database designers, administrators and analysts work together to optimize system performance through diverse methods.

1.    Explain the three levels of architecture.

There are three levels of database architecture.

      I.        External or View Level:

It is the user's view of the database and highest level of abstraction that deals with the way in which data is viewed by the individual user. The external level insulates the user from the details of internal and conceptual level. he external view is described by means of a schema called external schema corresponds to different views of the data. The external schema consists of the definition of the logical records and the relationship in the internal view. Any database has only one internal (physical) schema and one conceptual schema because it has just one set of stored relations but it has several external schema each of which created as per the user's need and it is written using external data definition language.

    II.        Conceptual or Logical Level:

It is the global view of the database that describes what data is stored in the database and the relationship among those data. It is the complete view of the data requirements of the organization that is dependent of any storage considerations which contains the logical structure of the entire database as seen by the DBA. The conceptual Level represents;

a.    All entities, their attributes and their relationship.

b.    The constraint on the data.

c.    Semantic information about the data.

d.    Security and integrity information.

   III.        Internal Level or Storage Level:

It is the physical representation of the database on the computer that describes how the data is stored in the database. This level covers the physical implementation of the database to achieve runtime performance and storage space utilization. This level also covers the data structure and file organizations used to store data on storage device. This level is concerned with the following.

a.    Storage space allocation for data and indexes.

b.    Record description for storage with stored sizes for data items.

c.    Record placement.

d.    Data compression and data encryption techniques.

2.    Explain the closure set of dependencies.

The closure of a set F of functional dependencies is the set of all functional dependencies logically implied by F.

We denote the closure of F by F+ . To compute F+ , we can use some rules of inference called Armstrong's Axioms:

Given a set F set of functional dependencies, there are certain other functional dependencies that are logically implied by F. The set of all functional dependencies logically implied by F is the closure of F.  We denote the closure of F by F +. We can find all of F+ by applying Armstrong’s Axioms:

– if β ⊆ α, then α → β (reflexivity)

– if α → β, then γα → γβ (augmentation)

– if α → β and β → γ, then α → γ (transitivity)

These rules are sound and complete.

We can further simplify computation of F + by using the following additional rules.

– If α → β holds and α → γ holds, then α → βγ holds (union)

– If α → βγ holds, then α → β holds and α → γ holds (decomposition)

– If α → β holds and γβ → δ holds, then αγ → δ holds (pseudotransitivity)

The above rules can be inferred from Armstrong’s axioms.

These rules are sound because they do not generate any incorrect functional dependencies. They are also complete as they generate all of F+ .

3.    Explain the database normalization process from un-normalized to 2NF with example.

Database normalization process

1^st Normal Form

1.    Create un-normalized Relation

a.    list attributes

b.    Choose Primary Key.

c.    Indicate repeating attributes

2.    Separate repeating and non-repeating attributes

a.    Split original table into two and select key for repeating attributes.

b.    Create concatenated key for table with repeating attributes.

2^nd Normal Form

3.    Remove Partial dependency.

a.    Identify Non-key attributes that depend on only half the key.

b.    Create new table with dependent attributes and half the key.

3^rd Normal Form

4.    Remove transitive Dependency

a.    Identify non-key attributes that depend on each other.

b.    Create new table with dependent attributes.

c.    Create Foreign Key.

4^th Normal Form

5.    Remove Multi value dependency

a.    Identify the MVD in relation.

b.    If it exist like AààB|C and B and C are independent on each other then decompose it to (A,B) and (A,C)

5^th Normal Form

6.    Decompose tables such that further decomposition is not possible.

a.    Identify the join dependency in a relation R.

b.    Join dependency should be implied by candidate keys of relation R.

Example: Consider the following un-normalized table STUDENT

Course_Code	Course_Name	Tname	RNo	SName	System	Rate	Total_Hrs
A1	DBMS	Ajay	1	Ritesh	P-II	10	2
			2	Ranjan	P-IV	12	1
			3	Sita	AMD	15	2
			4	Rohit	Celron	20	1
B2	OS	Rajiv	2	Ranjan	P-IV	5	3
			5	Mukesh	AMD	40	7
			6	Nimit	P-I	10	1
			3	Sita	AMD	40	2

In this relation, there are so many repeating groups so this table can not be considered as relation because there are entries with more than one value. We need to normalize this table into 1NF. There are basically two approaches to normalize into 1NF.

Method 1: Flattering the table:  In this method we remove repeating groups by filling in the missing entries of each incomplete row of the table with the copies of their corresponding non-repeating attributes. i.e.

Course_Code	Course_Name	Tname	RNo	SName	System	Rate	Total_Hrs
A1	DBMS	Ajay	1	Ritesh	P-II	10	2
A1	DBMS	Ajay	2	Ranjan	P-IV	12	1
A1	DBMS	Ajay	3	Sita	AMD	15	2
A1	DBMS	Ajay	4	Rohit	Celron	20	1
B2	OS	Rajiv	2	Ranjan	P-IV	5	3
B2	OS	Rajiv	5	Mukesh	AMD	40	7
B2	OS	Rajiv	6	Nimit	P-I	10	1
B2	OS	Rajiv	3	Sita	AMD	40	2

But this normalized table is not a relation because it does not have primary key. To transform this relation into a relation, a primary key needs to be defined and suitable PK of this table is the composite key: (Course_Code, RNo)

Method 2: Decompose the table: In this approach of normalization, we decompose the table into two new tables that will replace the original table. For this, we follow the certain rules of decomposition.

Rule 1: One of the two tables contains the tables-identifier (PK) of the original table + all the non-repeating attributes.

Rule 2: The other table contains a copy of the table identifier (PK) + all repeating attributes.

So, using this approach, we normalize this table then we need to replace it by two tables. i.e.

COURSE

Course_Code	Course_Name	Tname
A1	DBMS	Ajay
B2	OS	Rajiv

COURSE_STUDENT

Course_Code	RNo	SName	System	Rate	Total_Hrs
A1	1	Ritesh	P-II	10	2
A1	2	Ranjan	P-IV	12	1
A1	3	Sita	AMD	15	2
A1	4	Rohit	Celron	20	1
B2	2	Ranjan	P-IV	5	3
B2	5	Mukesh	AMD	40	7
B2	6	Nimit	P-I	10	1
B2	3	Sita	AMD	40	2

4.    Introduce DAC (Discretionary Access Control) and MAC (Mandatory Access Control).

The term Access Control actually refers to the control over access to system resources after a user's account credentials and identity have been authenticated and access to the system granted. For example, a particular user, or group of users, might only be permitted access to certain files after logging into a system, while simultaneously being denied access to all other resources.

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)".

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.