1.

i) Answer:

Database users are the one who really use and take the benefits of database. There will be different types of
users depending on their need and way of accessing the database.
Application Programmers
1. Sophisticated Users
2. Specialized Users
3. Stand-alone Users
4. Native Users
5. Database Administrators

1.ii) Answer

A DBMS can be classification based on the number of users. It can be a single-user database system, which
supports one user at a time, or a multiuser database system, which supports multiple users concurrently.
Centralized Database
It comforts the users to access the stored data from different locations through several applications.
Distributed Database
Distributed Database system, data is distributed among different database systems of an organization. These
database systems are connected via communication links.
1.iii) Answer

Entity Set is a collection of entities of the same entity type. For example STUDENT is a entity type, a collection
of entities from the Student entity type would form an entity set. We can say that entity type is a superset of
the entity set as all the entities are included in the entity type.

1.iv) Answer
Trigger is procedural code that is automatically executed in response to certain events on a particular table or
view in a database. The trigger is mostly used for maintaining the integrity of the information on the database.

1.v) Answer
Generalization is a bottom-up approach in which two lower level entities combine to form a higher level entity.
In generalization, the higher level entity can also combine with other lower level entity to make further higher
level entity.

1.vi) Answer
ALTER TABLE Table_Name DROP COLUMN Column_Name;

1.vii) Answer
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;

1.viii) Answer
Fragmentation is a process of dividing the whole or full database into various subtables or sub relations so that
data can be stored in different systems.

Replication is the process of storing the same data in multiple locations to improve data availability and
accessibility.

1.ix) Answer
CREATE ROLE role_name

1.x) Answer
Join Dependency is lossless decomposition and form relation R based on nontrivial multivalued dependencies.
An instance, a relation R(A B C) is decomposed into relation R1(A B), R2(B C) and R3(A C) based on nontrivial
multivalued functional dependencies.

Q2) Answer
transaction is one or more SQL statements that make up a unit of work performed against the database, and
either all the statements in a transaction are committed as a unit or all the statements are rolled back as a unit.
This unit of work typically satisfies a user request and ensures data integrity.

The ACID model is one of the oldest and most important concepts of database theory. It sets forward four goals
that every database management system must strive to achieve: atomicity, consistency, isolation and
durability.
Atomicity:
Atomicity states that database modifications must follow an all or nothing rule. Each transaction is said to be
atomic. If one part of the transaction fails, the entire transaction fails.
Consistency:
Consistency states that only valid data will be written to the database. If, for some reason, a transaction is
executed that violates the databases consistency rules, the entire transaction will be rolled back and the
database will be restored to a state consistent with those rules.
Isolation:
In Database, isolation refers to the ability to concurrently process multiple transactions in a way that one does
not affect another.
Durability:
Durability ensures that any transaction committed to the database will not be lost. Durability is ensured
through the use of database backups and transaction logs that facilitate the restoration of committed
transactions in spite of any subsequent software or hardware failures.
Q3) Answer
A multivalued attribute is capable of storing more than one value in a single attribute i.e nothing but it can
hold multiple values for the single attribute. Multi-valued attributes make sorting data in a database extremely
difficult, if not impossible. They must be resolved, and there are two ways to do so: create additional attributes
for an entity, or. create an entirely new entity.

Here, Employee name is a multi valued attribute.

SSN BDate FName Minit LName Address Salry Sex

1111 12/12/1999 Hari Bahadur Acharya KTM 19000 Male
1112 12/12/2001 Madan bahadur Shrestha Pokhara 18000 Male

Now, Multi valued attribute is treated to single valued attribute in relation.

Q4) Answer
In a Relationship, Participation constraint specifies the existence of an entity when it is related to another
entity in a relationship type. It is also called minimum cardinality constraint. This constraint specifies the
number of instances of an entity that can participate in a relationship type. There are two types of Participation
constraint:
1, Total Participation
2, Partial Participation

Total Participation
Each entity in the entity set is involved in at least one relationship in a relationship set.Consider two entities
Employee and Department related via Works_For relationship. Now, every Employee works in at least one
department therefore an Employee entity exist if it has at least one Works_For relationship with Department
entity. Thus the participation of Employee in Works_For is total relationship.

Partial Participation
Each entity in entity set may or may not occur in at least one relationship in a relationship set.
For example: Consider two entities Employee and Department and they are related to each other via Manages
relationship. An Employee must manage a Department, he or she could be the head of the department. But not
every Employee in the company manages the department. So, participation of employee in the Manages
relationship type is partial i.e. only a particular set of Employees will manage the Department but not all.
Q5) Answer
Left join or left outer join (⟕):
The left join is written as R ⟕ S where R and S are relations. The result of the left join is the set of all
combinations of tuples in R and S that are equal on their common attribute names, in addition to tuples
in R that have no matching tuples in S.
For an example consider the tables Employee and Dept and their left outer join:
Employee Dept Employee ⟕ Dept
Name EmpId DeptName DeptName Manager Name EmpId DeptName Manager
Harry 3415 Finance Sales Harriet Harry 3415 Finance ω
Sally 2241 Sales Production Charles Sally 2241 Sales Harriet
George 3401 Finance George 3401 Finance ω
Harriet 2202 Sales Harriet 2202 Sales Harriet
Tim 1123 Executive Tim 1123 Executive ω

In the resulting relation, tuples in S which have no common values in common attribute names with tuples
in R take a null value, ω. Since there are no tuples in Dept with a DeptName of Finance or Executive, ωs occur in
the resulting relation where tuples in Employee have a DeptName of Finance orExecutive.

Right join or right outer join (⟖):

The right join behaves almost identically to the left join, but the roles of the tables are switched.
The right join of relations R and S is written as R ⟖ S. The result of the right join is the set of all combinations of
tuples in R and S that are equal on their common attribute names, in addition to tuples in S that have no
matching tuples in R. For example, consider the tables Employee and Dept and their right outer join:
Employee Dept Employee ⟖ Dept
Name EmpId DeptName DeptName Manager Name EmpId DeptName Manager
Harry 3415 Finance Sales Harriet Sally 2241 Sales Harriet
Sally 2241 Sales Production Charles Harriet 2202 Sales Harriet
George 3401 Finance ω ω Production Charles
Harriet 2202 Sales
Tim 1123 Executive
In the resulting relation, tuples in R which have no common values in common attribute names with tuples
in S take a null value, ω. Since there are no tuples in Employee with a DeptName of Production, ωs occur in the
Name attribute of the resulting relation where tuples in DeptName had tuples ofProduction.
Q6) Answer
Table: Students
SQL> create table Students(sid int primary key, sname varchar(20),program varchar(20));

To Fragment above table, Horizontal Fragmentation is suitable.

Horizontal Fragmentation for BBA students:
SQL> Select * into BBA_Students from Students Where program='bba';
SQL> Select * from BBA_Students;

Horizontal Fragmentation for BBM students

SQL> Select * into BBM_Students from Students Where program='bbm';
SQL> Select * from BBM_Students;

Horizontal Fragmentation for BIM students

SQL> Select * into BIM_Students from Students Where program='bim';
SQL> Select * from BIM_Students;

Reconstruct of all fragmented tables.

SQL> Select * from BBA_Students union Select * from BBM_Students union Select * from BIM_Students ;
Q7) Answer
Database management systems are increasingly being used to store information about all aspects of an
enterprise. The data stored in a DBMS is often vital to the business interests of the organization and is regarded
as a corporate asset. In addition to protecting the intrinsic value of the data, corporations must consider ways
to ensure privacy and to control access to data that must not be revealed to certain groups of users for various
reasons.

There are three main objectives to consider while designing a secure database application:
1. Secrecy: Information should not be disclosed to unauthorized users. For example,
a student should not be allowed to examine other students' grades.
2. Integrity: Only authorized users should be allowed to modify data. For example, students may be allowed to
see their grades, yet not allowed to modify them.
3. Availability: Authorized users should not be denied access. For example, an instructor who wishes to change
a grade should be allowed to do so.

The database administrator (DBA) is the central authority for managing a database system. The DBA’s
responsibilities include granting privileges to users who need to use the system and classifying users and data in
accordance with the policy of the organization. The DBA has a DBA account in the DBMS, sometimes called a
system or superuser account, which provides powerful capabilities that are not made available to regular
database accounts and users. DBA-privileged commands include commands for granting and revoking privileges
to individual accounts, users, or user groups and for performing the following types of actions:
1. Account creation. This action creates a new account and password for a user or a group of users to enable
access to the DBMS.
2. Privilege granting. This action permits the DBA to grant certain privileges to certain accounts.
3. Privilege revocation. This action permits the DBA to revoke (cancel) certain privileges that were previously
given to certain accounts.
4. Security level assignment. This action consists of assigning user accounts to the appropriate security clearance
level.

Types of Authorization:
A privilege is a type of permission for an authorization name, or a permission to perform an action or a task.
The privilege allows a user to create or access database resources. Privileges are stored in the database
catalogs. Authorized users can pass on privileges on their own objects to other users by using the GRANT
statement. Privileges can be granted to individual users, to groups, or to PUBLIC. PUBLIC is a special group that
consists of all users, including future users. Users that are members of a group will indirectly take advantage of
the privileges granted to the group, where groups are supported.
The different permissions for authorizations available are:
Primary Permission - This is granted to users publicly and directly.
Secondary Permission - This is granted to groups and automatically awarded to a user if he is a member of
the group.
Public Permission - This is publicly granted to all the users.
Context sensitive permission - This is related to sensitive content and only granted to a select users.

The categories of authorization that can be given to users are:

System Administrator - This is the highest administrative authorization for a user. Users with this
authorization can also execute some database administrator commands such as restore or upgrade a database.
System Control - This is the highest control authorization for a user. This allows maintenance operations on
the database but not direct access to data.
System Maintenance - This is the lower level of system control authority. It also allows users to maintain the
database but within a database manager instance.
System Monitor - Using this authority, the user can monitor the database and take snapshots of it.

8.I) Answer
select empname from employee where salary=(select max(salary) from employee);

8.II)
select dept_name,count(dept_name) from employee group by dept_name having count(dept_name)<10;
8.III) Answer
select E_Name from employee where address in('Mechi','Nepalganj','Pokhara');

8.IV) Answer
select E_ID,E_Name from employee where E_Name like 's%m';

8.V) Answer
update employee
set salary=salary+(salary*50/100) where salary<10000;

Answer:
Given relation is unnormalize form.
We have to convert in 1NF.
A relation will be 1NF if it contains an atomic value. It states that an attribute of a table cannot hold multiple
values. It must hold only single-valued attribute. First normal form disallows the multi-valued
attribute,composite attribute, and their combinations.
Given relation Name,Address and Contact_No attributes are multi valued attributes.
1NF: Students Table
S_ID FNAME LNAME City District Landline1 Contact2 ProgID ProgName
S01 lalu Yadav Jawlakhel Lalitpur 900215 56785 P1 BBA
S02 Ritika Rai Thimi Bhaktapur 22553 89764 P2 BBM
S03 Abas Ali Lekhnath Pokhara 67675 P1 BBA
S04 Santoshi Basnet Ghorahi Dang 32325 P3 BHM
S05 John Maharjan Damak Jhapa 23451 12567 P2 BBM

As per 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. Each
table should be organized into rows, and each row should have a primary key that distinguishes it as unique.
The Primary key is usually a single column, but sometimes more than one column can be combined to create a
single primary key. So, Above table is 1NF.
Second Normal Form (2NF):
2nd Normal Form Definition
A database is in second normal form if it satisfies the following conditions:
 It is in first normal form
 All non-key attributes are fully functional dependent on the primary key
 Remove partial dependencies
Partial dependency means that a nonprime attribute is functionally dependent on part of a candidate key. In
above table, According to the records, minimal super keys are S_ID and ProgID fields.
FD: S_ID->FNAME,LNAME,City,District,Landline1,Contact2
FD: ProgID->ProgName
So, S_ID and ProgID attributes are candidate keys. To remove partial dipendency, we must break the table.

Table: Students
S_ID FNAME LNAME City District Landline1 Contact2 ProgID
S01 lalu Yadav Jawlakhel Lalitpur 900215 56785 P1
S02 Ritika Rai Thimi Bhaktapur 22553 89764 P2
S03 Abas Ali Lekhnath Pokhara 67675 P1
S04 Santoshi Basnet Ghorahi Dang 32325 P3
S05 John Maharjan Damak Jhapa 23451 12567 P2

Table: Course
ProgID ProgName
P1 BBA
P2 BBM
P3 BHM

Third Normal Form (3NF):

3rd Normal Form Definition
A database is in third normal form if it satisfies the following conditions:
• It is in second normal form
• There is no transitive functional dependency
Transitive dependency when an non key attribute is functionally dependent on another non-key attribute. For
Example, if A → B and B → C, then A → C. This is called transitive dependency.
Here, A->B and A->C holds the dependency but B->C can’t hold the dependency because B is itself non key
attribute and B is determining another non key attribute C.
S_ID FNAME LNAME City District Landline1 Contact2 ProgID ProgName
S01 lalu Yadav Jawlakhel Lalitpur 900215 56785 P1 BBA
S02 Ritika Rai Thimi Bhaktapur 22553 89764 P2 BBM
S03 Abas Ali Lekhnath Pokhara 67675 P1 BBA
S04 Santoshi Basnet Ghorahi Dang 32325 P3 BHM
S05 John Maharjan Damak Jhapa 23451 12567 P2 BBM

Above table, FD: S_ID->FNAME,LNAME,City,District,Landline1,Contact2 holds but ProgID is playing as key role
for ProgName. Here, ProgID is itself non-key attribute which determining the non-key attribute ProgName.
To decompose above table in 3 NF:
Table: Students
S_ID FNAME LNAME City District Landline1 Contact2
S01 lalu Yadav Jawlakhel Lalitpur 900215 56785
S02 Ritika Rai Thimi Bhaktapur 22553 89764
S03 Abas Ali Lekhnath Pokhara 67675
S04 Santoshi Basnet Ghorahi Dang 32325
S05 John Maharjan Damak Jhapa 23451 12567

Table: Course
ProgID ProgName
P1 BBA
P2 BBM
P3 BHM

Table: Students_Course
S_ID ProgID
S01 P1
S02 P2
S03 P1
S04 P3
S05 P2

S_ID and ProgID are Foreign key