Solution of DBMS (SLC_2071)

Group A (Any four)   4 X 1 =4

1.    Write the Full form of DBMS.

Database Management System

2.    Define Relational Calculus.

 Relational calculus is non-procedural which has the same expressive power as relational algebra, i.e. it is operationally complete. It is a formal language based upon a branch of mathematical logic called "predicate calculus". There are two approaches: tuple relational calculus and domain relational calculus. 

3.    What is View?

In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

OR

In a database management system, a view is a way of portraying information in the database. This can be done by arranging the data items in a specific order, by highlighting certain items, or by showing only certain items. For any database, there are a number of possible views that may be specified. Databases with many items tend to have more possible views than databases with few items. Often thought of as a virtual table, the view doesn't actually store information itself, but just pulls it out of one or more existing tables. Although impermanent, a view may be accessed repeatedly by storing its criteria in a query.

4.    What is meant by Data Integrity?

It is the accuracy and consistency of stored data, indicated by an absence of any alteration in data between two updates of a data record. Data integrity is imposed within a database at its design stage through the use of standard rules and procedures, and is maintained through the use of error checking and validation routines.

5.    What does Functional Dependency mean?

Functional dependency is a relationship that exists when one attribute uniquely determines another attribute.

If R is a relation with attributes X and Y, a functional dependency between the attributes is represented as X->Y, which specifies Y is functionally dependent on X. Here X is a determinant set and Y is a dependent attribute. Each value of X is associated precisely with one Y value.

Functional dependency in a database serves as a constraint between two sets of attributes. Defining functional dependency is an important part of relational database design and contributes to aspect normalization.

6.    Define Transaction.

A transaction is a set of changes that must all be made together. It is a program unit whose execution mayor may not change the contents of a database. Transaction is executed as a single unit. If the database was in consistent state before a transaction, then after execution of the transaction also, the database must be in a consistent. For example, a transfer of money from one bank account to another requires two changes to the database both must succeed or fail together.

7.    What is Data Encryption?

Data encryption is the process of converting data, within a database, in plain text format into a meaningless cipher text by means of a suitable algorithm.

Group B (any eight) 8X2=16

8.    What is ER Model? Write its notation.

An Entity–Relationship model (ER model) is a data model for describing the data or information aspects of a business domain or its process requirements, in an abstract way that lends itself to ultimately being implemented in a database such as a relational database. The main components of ER models are entities (things) and the relationships that can exist among them.

OR

The entity-relationship model (or ER model) is a way of graphically representing the logical relationships of entities (or objects) in order to create a database. The ER model was first proposed by Peter Pin-Shan Chen of Massachusetts Institute of Technology (MIT) in the 1970s.

Notations used in ER model are;

ENTITIES

RELATIONSHIPS

ATTRIBUTES

Physical ERD Symbols

ERD Notation

9.    What are TRC and DRC?

TRC

Tuple calculus is a calculus and a part of the relational model, in order to provide a declarative database-query language for this data model.

DRC

Domain Relational Calculus (DRC) is a calculus that was introduced by Michel Lacroix and Alain Pirotte as a declarative database query language for the relational.

In DRC, queries have the form:

where each X₁ is either a domain variable or constant, and 

 denotes a DRC formula. The result of the query is the set of tuples Xi to Xn which makes the DRC formula true.

This language uses the same operators as Tuple calculus, the logical connectives ∧ (and), ∨ (or) and ¬ (not). The existential quantifier (∃) and the universal quantifier (∀) can be used to bind the variables.

Its computational expressiveness is equivalent to that of Relational algebra. 

10.  Define the Relational Algebra.

It is a procedural language with operators that is performed on one or more existing relation to derive result relation without changing the original relation. It defines complete scheme for each of the result relations. It consists of a set of relational operators in which each operator has one or more relations as its input and produces a relation as its output.

11.  Write any four name of operators used in Relational calculus.

The operators used in Relational Calculus are;

a.     AND  (∧)

b.     OR (∨)

c.     NOT (¬)

d.     existential quantifier (∃)

e.     universal quantifier (∀)

12.  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. The relational database was invented by E. F. Codd at IBM in 1970.

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

The features of SQL can be pointed as below

·         Allows users to access data in relational database management systems.

·         Allows users to describe the data.

·         Allows users to define the data in database and manipulate that data.

·         Allows embedding within other languages using SQL modules, libraries & pre-compilers.

·         Allows users to create and drop databases and tables.

·         Allows users to create view, stored procedure, functions in a database.

·         Allows users to set permissions on tables, procedures, and views

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

In SQL, a view is a virtual table based on the result-set of an SQL statement.A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.We can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

Syntax

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Example

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

15.  List the constraints in data integrity.

a.    CHECK Constraint 

b.    FOREIGN KEY Constraint 

c.    NOT NULL Constraint 

d.    PRIMARY KEY Constraint 

e.    UNIQUE Constraint 

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

Syntax of view to retrieve

SELECT * FROM [View_Name]

Syntax of view to update

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

17.  Write any two differences between Trivial and Non-Trivial dependencies.

18.  What is two-phase commit protocol?

It is a distributed algorithm that coordinates all the processes that participate in a distributed atomic transaction on whether to commit or abort (roll back) the transaction (it is a specialized type of consensus protocol). The protocol achieves its goal even in many cases of temporary system failure (involving either process, network node, communication, etc. failures), and is thus widely utilized. 

OR

The two phase commit protocol is a distributed algorithm which lets all sites in a distributed system agrees to commit a transaction. The protocol results in either all nodes committing the transaction or aborting, even in the case of site failures and message losses. 

19.   Define deadlock with block diagram.

In concurrent programming, a deadlock is a situation in which two or more competing actions are each waiting for the other to finish, and thus neither ever does.

In a transactional database, a deadlock happens when two processes each within its own transaction updates two rows of information but in the opposite order. For example, process A updates row 1 then row 2 in the exact timeframe that process B updates row 2 then row 1. Process A can't finish updating row 2 until process B is finished, but process B cannot finish updating row 1 until process A is finished. No matter how much time is allowed to pass, this situation will never resolve itself and because of this database management systems will typically kill the transaction of the process that has done the least amount of work.

Both processes need resources to continue execution. P1 requires additional resource R1 and is in possession of resource R2, P2 requires additional resource R2 and is in possession of R1; neither process can continue.

20.  What is transaction concurrency?

21.  Define database security mechanism.

Group C (Any Four) 4 X 5 = 20

22.  Consider the following database and write relational algebra for the given problems.

Employee

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

i). Find the name of Employee who earn more than 38,000.

ii). Find the name of all employee who work in A.

iii). Find the name list of all employee.

iv). Find the name of employee who earn least.

v). Re-arrange the name of cities in ascending order.

23.  Define optimization with block diagram.

24.  Explain the three levels of Architecture.

25.  Explain the closure set of dependencies.

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

27.  Explain Serialibility and isolation levels,

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