CIE-1 SCHEME & SOLUTION UG

ಗ್ಲೋಬಲ್ ಅಕಾಡೆಮಿ ಆಫ್ ಟೆಕ್ನಾಲಜಿ,ಬೆಂಗಳೂರು

Global Academy of Technology , Bengaluru
Second Internal Assessment –April 22nd 2025
DEPARTMENT OF CSE(AI&ML)
Database
Subject Name Management Sem IV Subject Code C M L 2 3 4 0 3
System
Q.
Questions Marks
No.
1 Explain the Set theory operations from Relational Algebra (UNION, INTERSECTION,
MINUS), giving suitable examples for each. 10

Explanation of UNION, INTERSECTION, MINUS carries 10 marks

UNION, INTERSECTION, and MINUS Operations

■ UNION: The result of this operation, denoted by R ∪ S, is a relation that includes all
tuples that are either in R or in S or in both R and S. Duplicate tuples are eliminated.
■ INTERSECTION: The result of this operation, denoted by R ∩ S, is a relation that
includes all tuples that are in both R and S.
■ SET DIFFERENCE (or MINUS): The result of this operation, denoted by R – S, is a
relation that includes all tuples that are in R but not in S. ✓ These are binary operations;
that is, each is applied to two sets (of tuples).
✓ Two relations R(A1, A2, … , An) and S(B1, B2, … , Bn) are said to be union
compatible (or type compatible) if they have the same degree n and if dom(Ai) = dom(Bi)
for 1 ≤ i ≤ n. This means that the two relations have the same number of attributes and
each corresponding pair of attributes has the same domain.
✓ For example, to retrieve the Social Security numbers of all employees who either work
in department 5 or directly supervise an employee who works in department 5,

DEP5_EMPS ← σDno=5(EMPLOYEE)
RESULT1 ← πSsn(DEP5_EMPS)
RESULT2(Ssn) ← πSuper_ssn(DEP5_EMPS)
RESULT ← RESULT1 ∪ RESULT2

✓ Both UNION and INTERSECTION are commutative operations; that is,

R ∪ S = S ∪ R and R ∩ S = S ∩ R
 ✓ Both UNION and INTERSECTION can be treated as n-ary operations applicable to
any number of relations because both are also associative operations; that is, ✓ The
MINUS operation is not commutative; that is, in general, R − S ≠ S − R
✓ The INTERSECTION can be expressed in terms of union and set difference as follows:
R ∪ (S ∪ T ) = (R ∪ S) ∪ T and (R ∩ S) ∩ T = R ∩ (S ∩ T)
R ∩ S = ((R ∪ S) − (R − S)) − (S − R)

OR
2 Explain the Unary relational operations: Select, Project and Rename used in relational 10
algebra, with examples each.

Explanation of all 3 carries 10 marks

The SELECT Operation

The SELECT operation is used to choose a subset of the tuples from a relation that
satisfies a selection condition.
It restricts the tuples in a relation to only those tuples that satisfy the condition.
It can also be visualized as a horizontal partition of the relation into two sets of tuples—
those tuples that satisfy the condition and are selected, and those tuples that do not
satisfy the condition and are discarded.
For example, to select the EMPLOYEE tuples whose department is 4, or those whose
salary is greater than $30,000

σDno=4(EMPLOYEE)
σSalary>30000(EMPLOYEE)

In general, the SELECT operation is denoted by

σ<selection condition>(R)
where the symbol σ (sigma) is used to denote the SELECT operator and the selection
condition is a Boolean expression (condition) specified on the attributes of relation R.
The Boolean expression specified in is made up of a number of clauses of the form :
<attribute name><comparison op><constant value>
Or
<attribute name><comparison op><attribute name>
Clauses can be connected by the standard Boolean operators and, or, and not to form a
general selection condition.
For example, to select the tuples for all employees who either work in department 4 and
make over
$25,000 per year, or work in department 5 and make over $30,000:
σ(Dno=4 AND Salary>25000) OR (Dno=5 AND Salary>30000)(EMPLOYEE)

The Boolean conditions AND, OR, and NOT have their normal interpretation, as
follows:
 (cond1 AND cond2) is TRUE if both (cond1) and (cond2) are TRUE;
 otherwise,it is FALSE.
 (cond1 OR cond2) is TRUE if either (cond1) or (cond2) or both are TRUE;
otherwise, it is FALSE.
 (NOT cond) is TRUE if cond is FALSE; otherwise, it is FALSE.
 The SELECT operator is unary; that is, it is applied to a single relation. Hence,
selection conditions cannot involve more than one tuple.
 The degree of the relation resulting from a SELECT operation—its number of
attributes—is the same as the degree of R.
 The SELECT operation is commutative; that is,
 σ (cond1)(σ(cond2)(R)) = σ(cond2)(σ(cond1)(R))

The PROJECT Operation

• The PROJECT operation, selects certain columns from the table and discards
the other columns.
• The result of the PROJECT operation can be visualized as a vertical partition
of the relation into two relations: one has the needed columns (attributes) and contains
the result of the operation, and the other contains the discarded columns.
• For example, to list each employee’s first and last name and salary, we can use
the PROJECT operation as follows:

πLname, Fname, Salary(EMPLOYEE)

The general form of the PROJECT operation is

π<attribute list>(R)

 where (pi) is the symbol used to represent the PROJECT operation, and is the
desired sublist of attributes from the attributes of relation R.
 The result of the PROJECT operation has only the attributes specified in in the
same order as they appear in the list. Hence, its degree is equal to the number of
attributes in <attribute list>.
 The PROJECT operation removes any duplicate tuples, so the result of the
PROJECT operation is a set of distinct tuples, and hence a valid relation. This is
known as duplicate elimination.

Sequences of Operations and the RENAME Operation

 The relations shown above depict operation results do not have any names.
 Either we can write the operations as a single relational algebra expression by
nesting the operations, or we can apply one operation at a time and create
intermediate result relations.
 In the latter case, we must give names to the relations that hold the intermediate
results.
 For example, to retrieve the first name, last name, and salary of all employees
who work in department number 5, apply a SELECT and a PROJECT operation.
 πFname, Lname, Salary(σDno=5(EMPLOYEE))

Alternatively, we can explicitly show the sequence of operations, giving a name to each
intermediate relation, and using the assignment operation, denoted by ← (left arrow), as
follows:
DEP5_EMPS ← σDno=5(EMPLOYEE)
RESULT ← πFname, Lname, Salary(DEP5_EMPS)

3 What is meant by integrity constraint? Explain the importance of key and referential 10
integrity constraints. How referential integrity constraint is implemented in SQL?
Define-2 Marks
Explaination-8 Marks

Integrity constraints in a Database Management System (DBMS) are rules that help keep
the data in a database accurate, consistent and reliable. They act like a set of guidelines
that ensure all the information stored in the database follows specific standards.
For example:
 Making sure every customer has a valid email address.
 Ensuring that an order in the database is always linked to an existing customer.

Referential integrity is specified via the FOREIGN KEY clause

 A referential integrity constraint is violated when rows are inserted or deleted, or when a
foreign key or primary key attribute value is modified.
 The default action that SQL takes for an integrity violation is to reject the update
operation that will cause a violation.
 However, the schema designer can specify an alternative action to be taken if a
referential integrity constraint is violated, by attaching a referential triggered action
clause to any foreign key constraint.
 The options include SET NULL, CASCADE, and SET DEFAULT. An option must be
qualified with either ON DELETE or ON UPDATE.
 One possible database state for the COMPANY database
OR
4 Explain with an example, the basic constraints that can be specified when creating a
table in SQL. 10
Basic constraints explanation-6 marks.
Example explanation-4 marks.

Basic SQL Constraints:

1. NOT NULL – Ensures that a column cannot have a NULL value.
2. UNIQUE – Ensures all values in a column are different.
3. PRIMARY KEY – Uniquely identifies each row in a table (combines NOT
NULL and UNIQUE).
4. FOREIGN KEY – Ensures referential integrity by linking to a primary key in
another table.
5. CHECK – Ensures the value in a column meets a specific condition.
6. DEFAULT – Sets a default value for a column if no value is specified.

Example:

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY, -- PRIMARY KEY constraint
FirstName VARCHAR(50) NOT NULL, -- NOT NULL constraint
LastName VARCHAR(50) NOT NULL, -- NOT NULL constraint
Email VARCHAR(100) UNIQUE, -- UNIQUE constraint
Age INT CHECK (Age >= 18), -- CHECK constraint
DepartmentID INT,
Salary DECIMAL(10, 2) DEFAULT 30000.00, -- DEFAULT constraint
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) --
 FOREIGN KEY constraint
);
Explanation:
 EmployeeID: Must be unique and not null.
 FirstName and LastName: Cannot be null.
 Email: Must be unique across all employees.
 Age: Must be 18 or older.
 Salary: If not specified, defaults to 30000.00.
 DepartmentID: Must correspond to a valid DepartmentID in the Departments
table.

5 The commercial bank wants keep track of the customer’s account information. Each
customer may have any number of accounts and account can be shared by any number of
customers. The system will keep track of the date of last transaction.
We store the following details.
a) Account: unique account-number, type and balance
b) Customer: unique customer-id, name and several addresses composed of street, city
and state
Perform the following operations on the database:
a. Create necessary tables and insert few tuples to all the relations.
b. Add 5% interest to the customer who have less than 10000 balance.
c. List joint accounts involving more than three customers.
d. Find the total interest credited to each customer for a particular year.
e. Find the customer who has not done any transaction.
5 queries carries 10 marks

a. Create necessary tables and insert few tuples to all the realations 10
b. Add 5% interest to the customer who have less than 10000 balances
SELECT * FROM ACCOUNT;
UPDATE ACCOUNT
SET BALANCE = BALANCE * 1.05
WHERE BALANCE < 10000;
SELECT * FROM ACCOUNT;

c. List joint accounts involving more than three customers.

SELECT CA.ACCOUNT_NO, COUNT(CA.CUSTOMER_ID) AS NUM_CUSTOMERS
FROM CUSTOMER_ACCOUNT CA
GROUP BY CA.ACCOUNT_NO
HAVING COUNT(CA.CUSTOMER_ID) > 3;
 d. Find all the customers who have at least two accounts in the Bank.
SELECT CUSTOMER_ID
FROM CUSTOMER_ACCOUNT
GROUP BY CUSTOMER_ID
HAVING COUNT(ACCOUNT_NO) >= 2;

e. Find the customer who has not done any transaction.

SELECT C.CUSTOMER_ID, C.NAME
FROM CUSTOMER C,
CUSTOMER_ACCOUNT , ACCOUNT A
WHERE C.CUSTOMER_ID = CA.CUSTOMER_ID AND
CA.ACCOUNT_NO = A.ACCOUNT_NO AND
TRANSACTION_DATE IS NULL;

OR
6 Consider the schema for CAR-ACCIDENT Database:
PERSON (driver_id, name, address)
CAR (reg_num, model, year)
ACCIDENT (report_num, accident_date, location)
OWNS (driver_id, reg_num)
PARTICIPATED (driver_id, reg_num, report_num, damage_amount)
Write SQL queries to
1. Display the entire CAR relation in the ascending order of manufacturing year. 10
2. Find the number of accidents in which cars belonging to a specific
model(example ‘Nissan’) were involved.
3. Find the total number of people who owned cars that involved in accidents in the
year 2022.
4. Delete the tuple whose damage amount is below the average damage amount.
5. Create a list of each person’s name and the car models they own, but only for
those cars involved in accidents that occurred in the same location as the person’s
address.

5 queries carries 10 marks

1. Display the entire CAR relation in ascending order of manufacturing year.

SELECT *
FROM CAR
ORDER BY year ASC;

2. Find the number of accidents in which cars belonging to a specific model (e.g.,
‘Nissan’) were involved.
SELECT COUNT (DISTINCT A.report_num)
 FROM ACCIDENT A, PARTICIPATED P, CAR C
WHERE A.report_num = P.report_num AND
P.reg_num = C.reg_num AND
C.model = 'Nissan';

3. Find the total number of people who owned cars that were involved in accidents
in the year 2022.
SELECT COUNT(DISTINCT O.driver_id)
FROM OWNS O, PARTICIPATED P, ACCIDENT A
WHERE O.reg_num = P.reg_num AND
P.report_num = A.report_num AND
YEAR(A.accident_date) = 2022;

4. Delete the tuple whose damage amount is below the average damage amount.
DELETE FROM PARTICIPATED
WHERE damage_amount < (
SELECT AVG(damage_amount)
FROM PARTICIPATED
);

5. Create a list of each person’s name and the car models they own, but only for
those cars involved in accidents that occurred in the same location as the person’s
address.
SELECT DISTINCT P.name, C.model
FROM PERSON P, OWNS O, CAR C PARTICIPATED PT, ACCIDENT A
WHERE P.driver_id = O.driver_id AND
O.reg_num = C.reg_num AND
C.reg_num = PT.reg_num AND
PT.report_num = A.report_num AND
P.address = A.location;

7 Write SQL syntax with example for the following SQL statements:
a) CREATE TABLE
b) SELECT statement
c) UPDATE command
d) ALTER TABLE

Explanation of each command carries 2.5 marks

The CREATE TABLE Command in SQL:
 The CREATE TABLE command is used to specify a new table by giving it a name and
specifying its attributes and initial constraints.

 The attributes are specified first, and each attribute is given a name, a data type to
specify its domain of values, and any attribute constraints, such as NOT NULL.
  The key, entity integrity, and referential integrity constraints can be specified within the
CREATE TABLE statement after the attributes are declared, or they can be added later
using the ALTER TABLE command.
 We can explicitly attach the schema name to the relation name, separated by a period.
CREATE TABLE COMPANY. EMPLOYEE...
rather than
CREATE TABLE EMPLOYEE …

The relations declared through CREATE TABLE statements are called “base tables” or base
relations; this means that the relation and its rows are actually created and stored as a file by the
DBMS.
Base relations are distinguished from “virtual relations”, created through the CREATE VIEW
statement, which may or may not correspond to an actual physical file.
Figure 8.1 shows sample data definition statements in SQL for the COMPANY database.
10

The UPDATE Command:

➢ The UPDATE command is used to change attribute values of one or more selected tuples.
➢ As in the DELETE command, a WHERE clause in the UPDATE command selects the tuples
to be modified from a single relation.
➢ An additional SET clause in the UPDATE command specifies the attributes to be modified
and their new values.
➢ For example, to change the location and controlling department number of project number 10
to 'Bellaire' and 5, respectively, we use U5:

Several tuples can be changed with a single UPDATE command.

Example to give all employees in the 'Research' department a 10 percent rise in salary

SELECT Command
The basic form of the SELECT statement, sometimes called a mapping or a select- from-
where block, is formed of the three clauses SELECT, FROM, and WHERE and has the
following form:
➢ <attribute list> is a list of attribute names whose values are to be retrieved by the query.
➢ <table list> is a list of the relation names required to process the query.
➢ <condition> is a conditional (Boolean) expression that identifies the tuples to be retrieved by
the query.

SELECT <attribute list> FROM <table list> WHERE <condition>;

Where
• In SQL, the basic logical comparison operators for comparing attribute values with one
another and with literal constants are =, <, <=, >, >=, and <>.
•
CONSIDER THE COMPANY DATABASE (PREVIOUS FIG) FOR PREPARING QUERIES

This query involves only the EMPLOYEE relation listed in the FROM clause. The query
selects the EMPLOYEE tuples that satisfy the condition of the WHERE clause, then projects the
result on the BDATE and ADDRESS attributes listed in the SELECT clause.

The ALTER Command

 The definition of a base table or of other named schema elements can be changed by
using the ALTER command. For base tables, the possible alter table actions include
adding or dropping a column (attribute), changing a column definition, and adding or
 dropping table constraints.
 For example, to add an attribute for keeping track of jobs of employees to the
EMPLOYEE base relation in the COMPANY schema , we can use the command:
ALTER TABLE COMPANY.EMPLOYEE ADD COLUMN Job VARCHAR(12);

Alter Table - Alter/Modify Column

To change the data type of a column in a table, use the following syntax:
ALTER TABLE table_name
MODIFY column_name datatype;
OR
8 Explain the data types that are allowed for SQL attributes.

Explanation of any 5 data types(each carries 2 marks)

The basic data types available for attributes include numeric, character string, bit
string, boolean, date, and time.

a) Numeric data types include: ➢ integer numbers of various sizes (INTEGER

or INT, and SMALLINT).
➢ floating-point (real) numbers of various precision (FLOAT or REALand
DOUBLE PRECISION).
➢ Formatted numbers which can be declared by using DECIMAL(i,j)or
DEC(i,j) or NUMERIC(i,j)-where i, the precision, is the total number of decimal digits 10
and j, the scale, is the number of digits after the decimal point. The default for scale is
zero, and the default for precision is implementation-defined.

b) Character-string data types are either: ➢ fixed length--CHAR(n) or

CHARACTER(n), where n is the number of characters.
➢ varying length-VARCHAR(n) or CHAR VARYING(n) or CHARACTER
VARYING(n), where n is the maximum number of characters.
➢ When specifying a literal string value, it is placed between single quotation
marks (apostrophes), and it is case sensitive (a distinction is made between uppercase
and lowercase.
➢ For fixed-length strings, a shorter string is padded with blank characters to
the right.

For example, if the value 'Smith' is for an attribute of type CHAR(10), it is padded with
five blank characters to become 'Smith ' if needed.
➢ Padded blanks are generally ignored when strings are compared.

c) Bit-string data types are either of fixed length -BIT(n) or varying length-BIT
VARYING(n), where ‘n’ is the maximum number of bits.
➢ The default for ‘n’, the length of a character string or bit string, is 1.
➢ Literal bit strings are placed between single quotes but preceded by a B to distinguish
them from character strings;

For example, B'10101

d) A Boolean data type has the traditional values of TRUE or FALSE in SQL.
➢ Because of the presence of NULL values, a three-valued logic is used, so a third
 possible value for a boolean data type is UNKNOWN.

e) New data types for date and time were added in SQL2. ➢ The DATE data
type has ten positions, and its components are YEAR, MONTH, and DAY in the form
YYYY-MM-DD.
➢ The TIME data type has at least eight positions, with the components HOUR,
MINUTE,and SECOND in the form HH:MM:SS.
➢ The < (less than) comparison can be used with dates or times-an earlier date
is considered to be smaller than a later date, and similarly with time.
➢ Literal values are represented by single-quoted strings preceded by the
keyword DATE or TIME;

For example, DATE '2002-09-27' or TIME '09: 12:47'.

Signature of Course Coordinator Signature of Module Coordinator Signature of

HOD