Moradabad Institute of Technology, Moradabad

Computer Science & Engineering Department

Class Test -1 (Semester: 5th)
Session: 2022-23
Solution Set-1

Subject Name: Database Management System Subject Code: (KCS 501) Max
Marks: 15 Time: 1 hr :15 min

Q1. How is it possible to get more information from the same amount of data by using a
database approach as opposed to a file approach?

Solution. In File based approach Data is scattered and users of one program doesn't know if there is any
useful data held by other programs while Database approach is a shared collection of logically
related data which can be shared among application also the database using query approach to
retrieve the data stored in the database hence it is possible to get more information from the
same amount of data by using a database approach .

Q2. Define Query Processor and Data dictionary.

Solution. Query Processing is the activity performed in extracting data from the database. In query
processing, it takes various steps for fetching the data from the database. The steps involved are:
1. Parsing and translation
2. Optimization
3. Evaluation

• Data dictionary is the table which contains the information about database objects. It contains
information like
1 external, conceptual and internal database description
2 description of entities , attributes as well as meaning of data elements
3 synonyms, authorization and security codes
4 database authorization
5 The data stored in the data dictionary is called meta data.
Q3. Draw E-R relationship diagram showing the cardinality for the following: An operator can
work on many machines and each machine has many operators. Each machine belongs to one
department but a department can have many machines.

Solution:

M N M 1
Operator Works Machine Belongs
to
Department
on

Q4. Discuss different types of outer joins with suitable example

Solution: The outer join operation is an extension of the join operation. It is used to deal with missing
information.
An outer join is basically of three types:
Left outer join, Right outer join, Full outer join, Left outer join:

• Left outer join contains the set of tuples of all combinations in R and S that are equal on their
common attribute names.
In the left outer join, tuples in R have no matching tuples in S.
It is denoted by ⟕.
Example: Using the above EMPLOYEE table and FACT_WORKERS table
Input:
EMPLOYEE ⟕ FACT_WORKERS

Right outer join:

Right outer join contains the set of tuples of all combinations in R and S that are equal on
their common attribute names.
In right outer join, tuples in S have no matching tuples in R.
It is denoted by ⟖.
Example: Using the above EMPLOYEE table and FACT_WORKERS Relation
Input:
 • EMPLOYEE ⟖ FACT_WORKERS
• Output:

Full outer join:

• Full outer join is like a left or right join except that it contains all rows from both tables.
• In full outer join, tuples in R that have no matching tuples in S and tuples in S that have no
matching tuples in R in their common attribute name.
• It is denoted by ⟗.
• Example: Using the above EMPLOYEE table and FACT_WORKERS table
• Input:
• EMPLOYEE ⟗ FACT_WORKERS
• Output:

Q5. Consider the following schema and write the queries in relational algebra.
Sailors (sid, sname, rating, age),
Boats (bid, bname, color),
Reserves (sid, bid, day)
Write following queries in Relational Algebra by using the above database.
a. Find the names of sailors who have reserved at least one boat.
b. Find the sid of sailors who have reserved all red boat.
c. Give an expression in SQL to each of the following queries:
1. σB= 17 (r) 2. ∏A, F (σ C=D(r x s))
Solution:
a. ∏sname(Reserved ⋈ Sailors )
b. R1
∏bid (σ Color=Red (Boat))
R2
∏ sid, bid(Reserved)
Result = R2 / R1

c. 1. Select * from r where B = 17

2. Select A, F from r, s where r.c = s.d
Q6. Define the following terms with example:
a) Keys constraints, Entity Ingerity constraints.
b) Foreign Key and discuss the concept behind declaration of foreign keys.

Solution a. Key constraints

• Keys are the entity set that is used to identify an entity within its entity set uniquely.
• An entity set can have multiple keys, but out of which one key will be the primary key. A primary
key can contain a unique value in the relational table.
• Example:

Entity integrity constraints

• The entity integrity constraint states that primary key value can't be null.
• This is because the primary key value is used to identify individual rows in relation and if
the primary key has a null value, then we can't identify those rows.
• A table can contain a null value other than the primary key field.
• Example:

b. FOREIGN KEY is a column that creates a relationship between two tables. The purpose of Foreign
keys is to maintain data integrity and allow navigation between two different instances of an entity. It
acts as a cross-reference between two tables as it references the primary key of another table.
• Foreign keys are the column of the table which is used to point to the primary key of another
table.
• In a company, every employee works in a specific department, and employee and department are
two different entities. So we can't store the information of the department in the employee table.
That's why we link these two tables through the primary key of one table.
• We add the primary key of the DEPARTMENT table, Department_Id as a new attribute in the
EMPLOYEE table.
• Now in the EMPLOYEE table, Department_Id is the foreign key, and both the tables are related.