Asian cab Hetauda IMS Morgan Nagarjuna NCC

Asian

What are the advantages of DBMS ? [Chapter – 1]

=> The advantages of DBMS are :

• It is consistent .
• It has high security measures.

 Write down the basic SQL syntax to update a relation. [Chapter – 3]
=> The basic syntax to  update a relation is:
            UPDATE table ‘table_name’
            SET name = ‘new name’ where id = ‘1’;
 What is CHECK constraints? [Chapter – 4]
=> The CHECK constraint is used to limit the value range that can be placed in a column.
 Differentiate between centralize database system and distributed database system. [Chapter – 1]
=> The difference between centralized database and distributed database system is that
Centralized database works with single database file whereas Distributed Database works with multiple databases file.
 What is sub-query? [Chapter – 3]
=>A subquery is a query embedded within the WHERE clause of another query to further restrict data returned by the query. A subquery is a query within another query, also known as a nested query.
 Explain the difference between an ERD and EER. [Chapter – ]
=> An EER includes everything in an ERD and an EER allows for more complex relationships than an ERD. An EER allows for object-oriented data modeling and include supertypes and subtypes entities and inheritance.
 What is update anomaly? [Chapter – 5]
=> The errors that occurs when one or more instances of duplicate data is updated. It is a case of data redundancy that occurs from partial update.
 Explain some main goals of Normalization. [Chapter – 5]
=> Some main goals of normalization is :

1. to make relational table free form redundancy.
2. all relational databases should be in 3NF form.

 What do you mean by serializability? [Chapter – 6]
=> Serializability is the classical concurrency scheme.  It ensures that a schedule for executing concurrent transactions is equivalent to the  one that executes the transactions in serial order.
 Define concurrent transactions? [Chapter – 6]
=>  Concurrent transactions in database means that multi users can access the database file at the same period of time. The transactions taken by multiple user  at the same time is known as concurrent transactions.

 CAB

 
Define data dictionary.
 It is a set of information describing the contents, format, and structure of a database and the relationship between its elements, used to control access to and manipulation of the database.
What is the use of a View?
To prevent direct access to database tables for security purposes,
To present the information data on a different point of view, and
            To simplify the access to information of a high complexity of database schema.
Define ER model with examples.
ER model is a database model based on real world objects known as entity having various attributes, and their relationship with other entities.
List the use of ORDER BY and LIMIT clause.
ORDER BY is used to sort the results from a query. LIMIT is used to regulate or limit the number of tuples in a result.
What do you mean by concurrent transactions?
If multiple transactions related to the same data on a database are carried out at the same time, then it is known as concurrent transactions.
Write the application of database.
 Banking: Customer accounts, loans and other transactions
Education: Course registration, result and other information
            E-commerce: Business activity such as online shopping, booking of holiday package etc.
Human resources: Information about employees, salaries, benefits, taxes etc.
Define composite key with example.
Composite key is a key formed by two or more attributes. Eg: (roll_no,s_name).
Why are domain constraints required?
Domain constraints are necessary to make sure ay unauthorized or unwanted data are not inserted into the database.
List the different aggregate function.
àAVG,SUM,MIN,MAX
Differentiate between specialization and generalization.

Specialization	Generalization
Process of classifying objects based on their differences.	Grouping different objects based on their similarities.
It is a top to bottom process.	It is a bottom to top processs.

 
Section ‘B’

11. Write down the SQL statements to create the following tables.
    Department(dept_no(PK),d_name, city default constraint ‘KTM’)
    Employee(emp_Id(PK), e_name, phone, salary, date_of_birth)
                Works(dept_no(FK), emp_Id(FK))

CREATE TABLE Department(
dept_no int PRIMARY KEY,
d_name varchar(20),
ciy varchar(20)            DEFAULT ‘KTM’
);
CREATE TABLE Employee(
Emp_Id int PRIMARY KEY,
e_name varchar(20),
phone varchar(13),
salary bigint,
date_of_birth date
);
CREATE TABLE Works(
dept_no int,
emp_Id int,
FOREIGN KEY(dept_no) REFERENCES Department(dept_no),
FOREIGN KEY(emp_Id) REFERENCES Employee(emp_Id)
);
 
 
 
Section ‘C’
Write DML Statement to insert any five records in each tables.
INSERT INTO Department
VALUES(001,’Finance’,’Kathmandu’), (002,’Selling’,’Pokhara’),
(003,’Operating’,’Kathmandu’), (004,’Distribution’,’Janakpur’),
(005,’CentralOffice’,’Kathmandu’);
INSERT INTO Employee
VALUES(001,’Ram’,10000),(002,’Hari’,15000),(003,’Sita’,20000),(004,’Gita’,20000),
(005,’Rita’,25000);
INSERT INTO Works
VALUES(001,003),(003,001),(004,005),(002,002),(005,004);
Display the name of the employees.
SELECT e_name
FROM Employee;
Find the name of the emoloyees whose salary is greater than 10000.
SELECT e_name
FROM Employee
WHERE salary>10000;
Find the department(d_name) of the employee ‘Binek’.
SELECT d_name from Department
WHERE dept_no = (
SELECT dept_name FROM Works
WHERE emp_Id=(
SELECT emp_Id
FROM Employee
WHERE e_name=’Binek’
)
);
Update the department name management by IT.
UPDATE Department
SET d_name=’IT’
WHERE d_name=’management’;
 

Hetauda

1) Define functional dependencies.
=>A functional dependency is denoted by x->y holds if whenever two tuples have the same value of x , they must have the same value for y.
2)  Why Armstrong Axiom is called sound and complete?
=> Armstrong axiom is called sound because they don’t generate incorrect functional dependencies and complete as it allow us to generate all the closure of f(f+) for any given f.
3) What are the diff state of transaction?
=> They are

• i) Active
• ii) Partially Committed
• iii) Failed
• iv) Aborted
• v) Committed

4) Define Lossy join?
=>when a relation is decomposed into two or more relational schemas, the loss of information is unavoidable when the original relation is retrieved which is known as lossy join.
5) Dependency preservation in normalization?
=> If we decompose a relation R into relations R1 and R2, All dependencies of R either must be a part of R1 or R2 or must be derivable from combination of FD’s of R1 and R2.
For Example, A relation R (A, B, C, D) with FD set{A->BC} is decomposed into R1(ABC) and R2(AD) which is dependency preserving because FD A->BC is a part of R1(ABC).
6) Define closure of functional dependencies?
=>closure of FD means the complete set of all possible attribute that can be functionally derived from given functional dependencies using Armstrong axiom.
If f is a functional dependency then closure of functional dependency is denoted by f+.
7) Why trigger is used in database?
=> Trigger is mainly used in database for alert purpose. Triggers help the database designer ensure certain actions, such as maintaining an audit file, are completed regardless of which program or user makes changes to the data
8) Define embedded sql.
=>In embedded sql we embed sql statement directly into source code file that will create an application .This technique is referred as embedded sql.
9) Syntax to create and delete view in sql.
=>CREATE view view-name As select col1, column2… from table-name where [condition];
=>DELETE from view-name where [condition];
10) Define deadlock in transaction.
=>In a Database deadlock is an unwanted situation in which two or more transactions are waiting indefinitely for one another to give up locks.
Ex: Transaction t1 will wait for Transaction t2 to give up locks whereas Transaction t2 will wait for Transaction t1 to give up locks.
 

Group B

 

 

IMS

1.Differentiate between instance and schema in database.

BASIS FOR COMPARISON	SCHEMA	INSTANCE
Basic	Description of the database.	Snapshot of a database at a specific moment.
Change occurrence	Rare	Frequent
Initial state	Empty	Always have some data.

2.What is the total participation of an entity set in a relationship set?
 Total Participation of an entity set in a relationship set is a set which occurs when each entity in the entity setoccurs in at least one relationship in that relationship set.
3.Define domain of an attribute.
In computing, the attribute domain is the set of values allowed in an attribute. … Alas, the domain is an elementary type, and attribute domain thedomain a given attribute belongs to an abstraction belonging to or characteristic of an entity.
4.What is weak entity set?
The entity set which does not have sufficient attributes to form a primary key is called as Weak entity .
5.What are the properties of transaction?

• Atomicity
• consistency
• isolation
• durability

6.Define candidate key.
A candidate key is a column, or set of columns, in a table that can uniquely identify any database record without referring to any other data. Each table may have one or more candidate keys, but one candidate key is unique, and it is called the primary key.
7.Write a syntax for creating a view.
CREATE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;
8.What are the foundamental operation for relational algebra?
Selection, Projection, Cartesian product, Union, and Set Difference.
9.What is null value?
A NULL value in a table is a value in a field that appears to be blank. A field with aNULL value is a field with no value. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.
10.What is derived attribute?
If an attribute’s value can be determined from the values of other attributes, then the attribute is derivable, and is said to be a derived attribute.
 

Morgan

 

Nagarjuna

Consider the following relations
Users(userId, username,password,email,dateOfBirth,gender,registeredDate)
Categories(categoryID,categoryName,upperLimit)
Expenses(expenseID,spentDate,amount,categoryID,userID)
Write down the SQL statements for the following:

1. Insert new user

• INSERT INTO USERS

VALUES(101,’RAM’,’RAM123′, ‘[email protected]’,’1990-01-20′,’M’,CURDATAE());
Find users whose date of birth is before jan 01, 200.

• SELECT USERNAME FROM USERS
• WHERE DATEOFBIRTH<‘2000-01-01’;

Find top 5 categories on which users spend their money.

• SELECT C.CATEGORYNAME

FROM CATEGORIES C, EXPENSES E
WHERE C.CATEGORYID=E.CATEGORYID
GROUP BY C.CATEGORYNAME
ORDER BY E.AMOUNT DESC
LIMIT 5;
Find categories in which no expenditure has been made so far.
SELECT CATEGORYNAME
FROM CATEGORIES
WHERE CATEGORYID NOT IN
(SELECT CATEGORYID FROM EXPENSES) ;
 Find those users whose expenditure is not less than that of userID 405.
SELECT U.USERNAME FROM USERS U, EXPENSES E 
 WHERE U.USERID= E.USERID
GROUP BY E.USERID
HAVING SUM(E.AMOUNT) >=
(SELECT SUM(AMOUNT)
FROM EXPENSES
WHERE USERID =405);
 

NCC

 
Define the term data dictionary.

• Data dictionary is defined as the files or set of files that contain data about database.

What do u mean by ‘spurious tuple’ in database?

• Spurious tuple is a tuple formed when two tables are joined poorly.

Why having clause is used in database?

• Having clause is used in database because where clause cannot be used with aggregate functions.

What do u mean by cascading rollback?

• When any error or failure occurs in the transaction (T1) and the rollback is performed in the database, it is called cascading rollback.

In which operation SET operation is used?

• In update operation SET operation is used.

Why is it necessary to lock the database during the update of database?

• Because other transaction or other operation may be performed with old data which causes loss of consistency of data.

What do u mean by descriptive attributes?

• The attributes which describes other attributes in a database it is called descriptive attributes.

What is the benefit of INDEX creation?

• In order to retrieve data from the database very fast.

Whether the following query is right or wrong.
Insert into student (name, marks) select ‘John’,23’; Wrong query
Right query: Insert into student (name, marks) values (‘John’,23);
Which datatype is used for Unicode text in DBMS?

• Char is used for Unicode text in DBMS