Day 1: Foundational Concepts & Relational Model

1. Basic Database Concepts & ER Modeling

 Database: A structured collection of data. A DBMS (Database

Management System) is the software that allows you to manage and
access it.

 DBMS Advantages: It provides better data security, reduces data

redundancy, and enables concurrent access for multiple users.

 ER Modeling: A high-level visual tool for database design.

o Entities: Real-world objects (e.g., Student, Course). Represented

by rectangles.

o Attributes: Properties of an entity (e.g., student_ID, name).

Represented by ovals.

o Relationships: The links between entities (e.g., a Student

enrolls in a Course). Represented by diamonds.

o Cardinality: Defines how many entities can be associated with

another (1:1, 1:N, M:N).

2. Relational Model & Algebra

 Relational Model: Data is organized into tables (relations).

o Tuple: A row in a table.

o Attribute: A column in a table.

o Primary Key: A unique identifier for a row.

o Foreign Key: A column that links to the primary key of another

table.

 Relational Algebra: A procedural query language. You use operators

like SELECT, PROJECT, and JOIN to tell the system how to get the
data.

Most Important 25 MCQs for Day 1

1. A database management system (DBMS) is a collection of interrelated

data and a set of programs to access that data. (True/False)
 o Answer: True.

2. Which of the following is a drawback of the file-based system? a) Data

redundancy and inconsistency b) Difficulty in accessing data c) Data
isolation d) All of the above

o Answer: d) All of the above.

3. The three-schema architecture is a way to achieve: a) Physical data

independence b) Logical data independence c) Both a and b d) Neither
a nor b

o Answer: c) Both a and b.

4. In the three-schema architecture, the external schema is the: a)

Physical view of the database b) Logical view of the database c) User's
view of the database d) All of the above

o Answer: c) User's view of the database.

5. Which data model is the most popular and widely used? a) Hierarchical
model b) Network model c) Relational model d) Object-oriented model

o Answer: c) Relational model.

6. In the relational model, a row is also known as a: a) Field b) Attribute c)

Tuple d) Relation

o Answer: c) Tuple.

7. A table in a relational database is called a: a) Tuple b) Attribute c)

Relation d) Entity

o Answer: c) Relation.

8. The number of attributes in a relation is called its: a) Cardinality b)

Degree c) Primary key d) Foreign key

o Answer: b) Degree.

9. A key that uniquely identifies each tuple in a relation is called the: a)

Foreign key b) Candidate key c) Primary key d) Super key

o Answer: c) Primary key.

10. A foreign key is a field in one table that refers to the: a) Primary
key of the same table b) Primary key of another table c) Foreign key of
the same table d) Foreign key of another table
 o Answer: b) Primary key of another table.

11. In an ER diagram, entities are represented by which shape? a)

Oval b) Rectangle c) Diamond d) Circle

o Answer: b) Rectangle.

12. Relationships in an ER diagram are represented by which shape?

a) Oval b) Rectangle c) Diamond d) Circle

o Answer: c) Diamond.

13. An attribute that can be divided into smaller subparts is called a:

a) Simple attribute b) Composite attribute c) Derived attribute d) Multi-
valued attribute

o Answer: b) Composite attribute.

14. An attribute that can have more than one value for a single
entity occurrence is called a: a) Simple attribute b) Composite attribute
c) Derived attribute d) Multi-valued attribute

o Answer: d) Multi-valued attribute.

15. What is the cardinality ratio for a relationship where one entity
can be associated with one other entity? a) 1:1 b) 1:N c) N:M d) N:1

o Answer: a) 1:1.

16. The relational algebra operator that selects tuples satisfying a

given predicate is: a) Project (π) b) Select (σ) c) Union (∪) d) Join (⋈)

o Answer: b) Select (σ).

17. The relational algebra operator that eliminates duplicate tuples

and returns a vertical subset of a relation is: a) Project (π) b) Select (σ)
c) Union (∪) d) Join (⋈)

o Answer: a) Project (π).

18. Which of the following is a procedural language? a) Relational

Algebra b) Relational Calculus c) SQL d) DDL

o Answer: a) Relational Algebra.

19. A superkey is a set of attributes that can uniquely identify a

tuple. (True/False)

o Answer: True.
 20. A candidate key is a minimal superkey. (True/False)

o Answer: True.

21. The term used to define the inter-relatedness of data is: a) Data
integrity b) Data redundancy c) Data independence d) Data modeling

o Answer: c) Data independence.

22. Which key can be null? a) Primary Key b) Foreign Key c) Both a
and b d) None of the above

o Answer: b) Foreign Key.

23. In the ER Model, a weak entity set is represented by a: a) Single

rectangle b) Double rectangle c) Double oval d) Diamond

o Answer: b) Double rectangle.

24. What is a schema? a) The physical arrangement of the data b)

The logical design of the database c) The relationship between tables
d) A database instance

o Answer: b) The logical design of the database.

25. The overall design of the database is called: a) Instance b)

Schema c) Relation d) Attribute

o Answer: b) Schema.

Day 2: SQL, RDBMS, & Normalization

1. Structured Query Language (SQL) & RDBMS

 SQL: The standard language for managing and querying data in

RDBMS.

 RDBMS: A DBMS that uses the relational model (e.g., Oracle, MySQL).

 Commands:

o DDL (Data Definition Language): Defines the database

structure. Commands include CREATE, ALTER, DROP.

o DML (Data Manipulation Language): Manages data within

tables. Commands include SELECT, INSERT, UPDATE, DELETE.
  Project Focus: Your group project will involve applying these
commands to implement a small database. This is your chance to put
the theory into practice.

2. Functional Dependencies & Normal Forms

 Functional Dependency: A relationship where one attribute's value

uniquely determines another's (e.g., StudentID -> StudentName).

 Normalization: The process of organizing data to reduce redundancy

and avoid data anomalies (e.g., insertion, update, and deletion
anomalies).

o 1NF: All attributes are atomic (indivisible) and single-valued.

o 2NF: In 1NF and all non-key attributes are fully dependent on

the primary key.

o 3NF: In 2NF and there are no transitive dependencies (no

non-key attribute depends on another non-key attribute).

Most Important 25 MCQs for Day 2

1. Which SQL command is used to retrieve data from a database? a)

UPDATE b) INSERT c) SELECT d) DELETE

o Answer: c) SELECT.

2. The DROP command is an example of which language? a) DDL b) DML

c) TCL d) DCL

o Answer: a) DDL.

3. Which of the following is NOT a DML command? a) SELECT b) INSERT c)

UPDATE d) CREATE

o Answer: d) CREATE.

4. The WHERE clause in SQL is used for: a) Filtering rows b) Sorting

results c) Grouping data d) Joining tables

o Answer: a) Filtering rows.

5. Which clause is used to sort the result-set in SQL? a) WHERE b) GROUP

BY c) ORDER BY d) HAVING

o Answer: c) ORDER BY.

6. The JOIN clause in SQL is used to: a) Create a new table b) Combine
rows from two or more tables c) Sort the results d) Filter the data

o Answer: b) Combine rows from two or more tables.

7. A relation is in 1NF if all attributes are: a) Atomic b) Primary keys c)

Foreign keys d) Composite

o Answer: a) Atomic.

8. Which normal form removes partial dependencies? a) 1NF b) 2NF c)

3NF d) BCNF

o Answer: b) 2NF.

9. Which normal form removes transitive dependencies? a) 1NF b) 2NF c)

3NF d) BCNF

o Answer: c) 3NF.

10. A functional dependency X -> Y is a transitive dependency if: a)

X -> Z and Z -> Y b) X -> Y and Y -> Z c) Z -> X and Y -> Z d) Y -> X
and Z -> Y

o Answer: a) X -> Z and Z -> Y.

11. Which of the following is an anomaly that can occur in an un-

normalized database? a) Insertion anomaly b) Deletion anomaly c)
Update anomaly d) All of the above

o Answer: d) All of the above.

12. The GROUP BY clause is used with which aggregate functions? a)

COUNT() b) SUM() c) AVG() d) All of the above

o Answer: d) All of the above.

13. Which command is used to delete all rows from a table without
logging the individual row deletions? a) DELETE b) REMOVE c)
TRUNCATE d) DROP

o Answer: c) TRUNCATE.

14. The TRUNCATE command is a: a) DDL command b) DML

command c) TCL command d) DCL command

o Answer: a) DDL command.

15. What is the purpose of the HAVING clause in a SELECT
statement? a) To filter rows before grouping b) To filter groups after
grouping c) To sort the results d) To join tables

o Answer: b) To filter groups after grouping.

16. The ALTER TABLE command is used to: a) Add a new row to a
table b) Modify the structure of an existing table c) Delete a table d)
Change the data within a table

o Answer: b) Modify the structure of an existing table.

17. What is an RDBMS? a) A database based on the hierarchical

model b) A database based on the relational model c) A database
based on the network model d) A database based on the object-
oriented model

o Answer: b) A database based on the relational model.

18. The PRIMARY KEY constraint ensures: a) Uniqueness of values b)

Null values are allowed c) Uniqueness of values and no null values d)
All of the above

o Answer: c) Uniqueness of values and no null values.

19. Which of the following is a key goal of normalization? a) To

increase data redundancy b) To improve query performance c) To avoid
data anomalies d) To simplify data modeling

o Answer: c) To avoid data anomalies.

20. A table is in BCNF if and only if for every non-trivial functional

dependency X -> Y, X is a: a) Primary key b) Candidate key c) Foreign
key d) Super key

o Answer: b) Candidate key.

21. What is the SQL operator used to check for the presence of a
value in a list of values? a) LIKE b) IN c) BETWEEN d) EXISTS

o Answer: b) IN.

22. The ORDER BY clause defaults to which sort order? a) Ascending

b) Descending c) Random d) None of the above

o Answer: a) Ascending.
 23. What command is used to change the password of a user? a)
CREATE USER b) ALTER USER c) SET PASSWORD d) GRANT

o Answer: b) ALTER USER.

24. Which clause is used to combine the result sets of two or more
SELECT statements? a) JOIN b) UNION c) INTERSECT d) EXCEPT

o Answer: b) UNION.

25. What is the full form of SQL? a) Structured Query Language b)

Standard Query Language c) Simple Query Language d) Sequential
Query Language

o Answer: a) Structured Query Language.

Day 3: Advanced Concepts & Physical Design

1. Transaction & Concurrency Control

 Transaction: A logical unit of work that must be completed entirely or

not at all.

 ACID Properties:

o Atomicity: All-or-nothing.

o Consistency: Moves the database from one valid state to

another.

o Isolation: Transactions don't interfere with each other.

o Durability: Committed changes are permanent.

 Concurrency Control: Techniques to manage simultaneous

transactions to prevent problems like lost updates and dirty reads.

 Locking: The primary mechanism. Shared locks are for reading,

while exclusive locks are for writing.

2. Physical Design & Security

 Physical Database Design: How data is actually stored on a disk.

 Indexed Files: A data structure (like a table) that maps a search key
to a specific record's location on the disk, drastically speeding up
queries.
  B-trees: A self-balancing tree data structure widely used for indexing.
They are optimized for disk-based storage, minimizing disk I/O.

 Database Security: Protecting the database from unauthorized

access. This involves authentication (verifying identity) and
authorization (granting privileges like SELECT or UPDATE).

Most Important 25 MCQs for Day 3

1. The "all-or-nothing" principle of a transaction is known as: a)

Consistency b) Atomicity c) Isolation d) Durability

o Answer: b) Atomicity.

2. Which of the following is NOT one of the ACID properties? a) Atomicity

b) Consistency c) Integrity d) Durability

o Answer: c) Integrity.

3. What is the main purpose of concurrency control? a) To manage

multiple transactions running at the same time b) To speed up queries
c) To secure the database d) To perform backups

o Answer: a) To manage multiple transactions running at the

same time.

4. What problem occurs when a transaction reads uncommitted data

written by another transaction? a) Lost update b) Dirty read c)
Unrepeatable read d) Phantom read

o Answer: b) Dirty read.

5. What problem occurs when one transaction's update is overwritten by

another's? a) Lost update b) Dirty read c) Unrepeatable read d)
Phantom read

o Answer: a) Lost update.

6. A shared lock is primarily used for which type of database operation?

a) INSERT b) UPDATE c) DELETE d) SELECT

o Answer: d) SELECT.

7. An exclusive lock is required for which type of database operation? a)

SELECT b) UPDATE c) READ d) All of the above

o Answer: b) UPDATE.
8. What is the purpose of database recovery techniques? a) To perform
database backups b) To restore the database to a consistent state after
a system failure c) To optimize query performance d) To manage
concurrent access

o Answer: b) To restore the database to a consistent state after a

system failure.

9. Log-based recovery uses a log file to perform: a) UNDO operations b)

REDO operations c) Both UNDO and REDO operations d) Neither UNDO
nor REDO

o Answer: c) Both UNDO and REDO operations.

10. The ROLLBACK command is used to: a) Commit a transaction b)

Revert a transaction c) Start a transaction d) All of the above

o Answer: b) Revert a transaction.

11. Which data structure is widely used for indexing in databases? a)

Linked list b) Array c) Stack d) B-tree

o Answer: d) B-tree.

12. What is the purpose of an index in a database? a) To store the

data b) To speed up data retrieval c) To enforce data integrity d) To
protect the database

o Answer: b) To speed up data retrieval.

13. A dense index has an index entry for every: a) File b) Block c)
Search key value in the data file d) All of the above

o Answer: c) Search key value in the data file.

14. Which of the following is a physical database design concept? a)

Normalization b) ER modeling c) B-trees d) Functional dependencies

o Answer: c) B-trees.

15. What is the process of granting specific access rights to users

called? a) Authentication b) Authorization c) Encryption d) Auditing

o Answer: b) Authorization.

16. The GRANT command in SQL is used for: a) Authentication b)

Authorization c) Encryption d) Auditing
 o Answer: b) Authorization.

17. The REVOKE command in SQL is used to: a) Grant a user access
b) Remove a user's access c) Create a new user d) Delete a user

o Answer: b) Remove a user's access.

18. What is the primary purpose of database tuning? a) To improve

database performance b) To increase storage capacity c) To improve
security d) To normalize the database

o Answer: a) To improve database performance.

19. Which of the following is a common technique for database

tuning? a) Normalization b) Indexing c) ER modeling d) Concurrency
control

o Answer: b) Indexing.

20. A COMMIT command in a transaction means that the changes

are: a) Temporary b) Permanent c) Reverted d) Hidden

o Answer: b) Permanent.

21. The checkpoint in a database is used for: a) Starting a new

transaction b) Committing a transaction c) Creating a point of
consistency for recovery d) All of the above

o Answer: c) Creating a point of consistency for recovery.

22. The term "files with variable length records" refers to: a) Files
where all records have the same size b) Files where record sizes can
vary c) Files with a fixed number of records d) Files that cannot be
updated

o Answer: b) Files where record sizes can vary.

23. The Two-Phase Locking Protocol ensures: a) Atomicity b)

Consistency c) Isolation d) Durability

o Answer: c) Isolation.

24. Which type of database attack involves injecting malicious code

into a query? a) Denial of Service (DoS) b) SQL Injection c) Phishing d)
Ransomware

o Answer: b) SQL Injection.

25. Database security is concerned with protecting data from: a)
Unauthorized access b) Data loss c) Malicious attacks d) All of the
above

o Answer: d) All of the above.