Code: CB3401 R21

B.Tech./ B.E. Regular/Supplementary Examination November/December 2024

DATABASE MANAGEMENT SYSTEMS AND SECURITY

II Year IV Semester B.E. Computer Science and Engineering (Cyber Security)

Answer All Questions

PART ––A (10 x 2 = 20 Marks)

1. Structured query language (SQL) is a standard language for database creation and manipulation. SQL is
a domain-specific language used to manage data, especially in a relational database management system
(RDBMS). It is particularly useful in handling structured data, i.e., data incorporating relations among
entities and variables. (2)

2. The Entity Relationship Model (ER model) is a graphical representation of how entities relate
to each other in a system. It's also known as an Entity Relationship Diagram (ERD).(2)

3. Whenever some indirect relationship happens to cause functional dependency (FC), it is known as
Transitive Dependency. Thus, if A -> B and B -> C are true, then A -> C happens to be a transitive
dependency.(2)

4. The question is framed incorrectly. If the student tried to show that the FDs satisfy 2NF and 3NF
properties and not BCNF properties, the mark (2) will be awarded.

5.

(2)

6. In DBMS, there are two main types of locks: shared locks and exclusive locks. Shared locks allow
multiple transactions to read a resource simultaneously, while exclusive locks permit only one
transaction to modify the resource, preventing others from accessing it concurrently.(2)

7. List the below or give statements on different security needs (2)

8. List the below: (2)

9. Database security is a set of tools, processes, and controls that protect a database from unauthorized
access, manipulation, or destruction. The goal of database security is to keep sensitive data safe and
maintain the database's confidentiality, integrity, and availability.(2)

10. In a database management system (DBMS), inference can refer to a technique to find hidden information
or to a set of rules for deriving functional dependencies. Inference rules in databases are also known as
Armstrong's Axioms in Functional Dependency. (2)

PART ––B (5 x 13 = 65 Marks)

11(a).
 Definition of Relational Model (2)
 Important Terminologies and its meaning with examples (6)

 Types of Keys and Constraints.(3)

 Merits, Demerits, and Characteristics of Relational Model. (2)

11(b)

 Define Fragmentation (2)

 Types of Fragmentation: (5)
o Horizontal Fragmentation
 Primary
 Derived
 Complete
o Vertical Fragmentation
o Mixed or Hybrid Fragmentation
  Define Replication (2)
 Types of Replication: (4)
o Transactional
o Snapshot
o Merge

12(a).

 Define Functional Dependencies (2)

 Types of Functional Dependencies: (8)
o Trivial FDs
o Non-Trivial FDs
o Transitive FDs
o Fully FDs
o Partial FDs
 Examples of each (3)

12(b).

 Define Normalization (2)

 Need for Normalization (3)

 Statements of INF, 2NF, and 3NF (6)

 Examples of each (2)

13(a). (i) List and explain each with an example (8)

 (ii)
o Definition of Concurrency Control (1)
o Types: (4)
 Lock-Based
 Timestamp-Based
13(b).
 Define Lock-based Concurrency Control (2)
 List Types: (2)
o Single Phase
o Two Phase
 Shrinking & Expanding
 Transactions and Working Model of Two-Phase Concurrency Control with example
(6)
 Mention Cascading rollback and deadlock (2)
14(a).
 Define SQL injection attack (2)
 Working Principle (5)
 Example Code (3)
 Most common types of SQL inject attacks (3)

14(b).
(i) SQLi Attack Avenues. (6)

(ii) Database security.

 Definition (1)
 Need for Database Security (3)
 Types of Database Security attacks (3)

15(a).
  Define Database Access Control (2)
 Need for Database Access Control (2)
 Types: (7)
o Role-based Access Control
o Discretionary Access Control
 Comparison (2)

15(b).(i) Cascading Authorizations.

 Definition (2)
 Mechanism (4)
o Cascading permissions: When a parent role's permissions are cascaded to a
child role, the child role will have access to the same set of permissions.
o Revoking access rights: When a user revokes an access right, any cascaded
access rights are also revoked, unless the access right would still exist even
if the original grant had not occurred.
o Limiting access rights: For additional security, access rights for the child
can be limited.
(ii) Database encryption.
 Definition (2)
 Encryption Types: (5)
o Symmetric Encryption
o Asymmetric Encryption
o Transparent Data Encryption

PART–– C (1 x 15 = 15 Marks)
16(a).
DDL Commands (5)
CREATE TABLE Student
(
stud_name varchar(20),
stud_id int(3),
DOB varchar(15),
branch varchar(10),
DOJ varchar(15),
);
CREATE TABLE Course
(
course_name varchar(20),
course_id int(5),
stud_id int(3),
facult_name varchar(20),
faculty_id varchar(5),
marks real
);

DML Commands (5)

The commands which we will use here are insert and select. The insert command is used to insert
the values into database tables. Using the select command, the database values can be displayed.
(1) Inserting values into Student table
insert into Student(stud_name,stud_id,DOB,branch,DOJ)
values('AAA',11,'01-10-1999', 'computers','5-3-2018')
insert into Student(stud_name,stud_id,DOB,branch,DOJ)
values('BBB',12, 24-5-1988', 'Mechanical', '17-2-2016')
insert into Student(stud_name, stud_id,DOB,branch,DOJ)
values('CCC',13,'8-1-1990', 'Electrical','22-9-2017')
(2) Inserting values into Course table
insert into Course (course_name,course_id,stud_id,faculty_name, faculty_id,marks)
values('Basic',101,11,'Archana', 'F001','50')
insert into Course(course_name,course_id,stud_id,faculty_name, faculty_id,marks)
values('Intermediate',102,12,'Rupali', 'F002','70')
insert into Course (course_name,course_id,stud_id,faculty_name, faculty_id,marks)
values('Advanced',103,13,'Sunil','F003', '100')
(3) Displaying records of Student table
Select * from Student;
(4) Displaying records of Course table
Select * from Course;

DCL Commands (5)

The DCL command is used to control privileges in Database. To perform any operation in the
database, such as for creating tables, sequences or views, a user needs privileges.
(1) GRANT Command
SQL GRANT is a command used to provide access or privileges on the database objects to the
users.
Syntax
GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];
privilege_name is the access right or privilege granted to the user. Some of the access rights are
ALL, EXECUTE, and SELECT.
• object_name is the name of an database object like TABLE, VIEW, STORED PROC and
SEQUENCE.
• user_name is the name of the user to whom an access right is being granted.
• PUBLIC is used to grant access rights to all users.
• roll_name are a set of privileges grouped together.
• WITH GRANT OPTION - allows a user to grant access rights to other users.
Example
GRANT SELECT ON student_details TO user1
This query will grant the SELECT permission to student_details table to user named user1.
Similarly we can GRANT more than one privileges to user in a table
GRANT SELECT, INSERT, DELETE, UPDATE ON student details TO user1
For granting all privileges to user we use sysdba. The sysdba is a set of priviliges which has all
the permissions in it.
GRANT sysdba TO user1
(2) REVOKE
The REVOKE command removes user access rights or privileges to the database objects.
Syntax
REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name}
Example
To remove access right for SELECT to the table student_details for userl we write the query
REVOKE SELECT ON student details FROM user1;

16(b).i) Deadlock prevention schemes.

 Define Deadlock (1)
 Deadlock Conditions (2)
 Mechanisms (5)

ii) The states of a transaction

 Diagram (4)

 Explanation of each state (3)

**************