Basics of DBMS

Basic terminology

100

WHAT IS TUPLE?

ROW

200

WHAT IS ENTITY

REAL WORLD OBJECT

300

WHAT IS PRIMARY KEY?

IT IS A KEY THAT ACCEPT UNIQUE DATA WITH NO NULL VALUES

400

Explain the difference between a super key and a candidate key.

A super key is any set of attributes that uniquely identifies a tuple. A candidate key is a minimal super key.

500

Why is normalization important in relational databases?

Normalization eliminates redundancy and maintains data integrity.

Relational model

100

A named column in a relational table that holds a specific type of data

ATTRIBUTE

200

WHAT IS A RELATION IN CONTEXT OF DBMS

TABLE WITH ROWS & COLUMNS

300

Given a relation STUDENT(SID, Name, Dept, Age), identify a suitable primary key

SID is the primary key as it uniquely identifies each student.

400

Apply the concept of foreign key in a relation COURSE(CID, Title, SID). What is the foreign key?

SID is a foreign key referencing STUDENT(SID).

500

Design a relational schema for an online bookstore with customers, orders, books, and reviews.

• CUSTOMER(CID, Name, Email)

• BOOK(BID, Title, Author, Price)

• ORDER(OID, CID, Date)

• ORDER_DETAILS(OID, BID, Quantity)

• REVIEW(RID, CID, BID, Rating, Comment)

Joins Concept

100

What is a (natural) join?

This operation combines two tables based on a related column, producing a new table with matched rows.

200

Name any two types of joins.

inner join

outer join

Natural join/Equi join/cross join

left outer/right outer/full outer

300

Explain the difference between inner join and outer join.

Inner join returns only matching rows; outer join returns matching rows plus non-matching rows with NULLs.

400

Apply a LEFT JOIN between EMP(EID, Name) and DEPT(DID, EID) to get all employees with or without department.

SELECT EMP.Name, DEPT.DID  

FROM EMP  

LEFT JOIN DEPT ON EMP.EID = DEPT.EID;

500

Create a query to list all employees and their project names, including those not assigned any project.

SELECT EMP.Name, PROJECT.ProjectName  

FROM EMP  

LEFT JOIN PROJECT ON EMP.EID = PROJECT.EID;

Normalization

100

This rule states that every non-primary-key attribute must be functionally dependent on the entire primary key (not just part of it).

2NF

200

A relation is in this normal form if it has no transitive dependencies and is already in 2NF

3NF

300

What is the main requirement for a table to be in 1NF?

All attributes must have atomic (indivisible) values.

400

Apply 2NF to this table: Order(OrderID, ProductID, ProductName, Qty)

• Orders(OrderID)

• Products(ProductID, ProductName)

• OrderDetails(OrderID, ProductID, Qty)

500

Should every database be normalized to 5NF? Why or why not?

Not always. It depends on application requirements. Over-normalization can reduce performance.

Transaction concept

100

what is the sequence of operations performed as a single logical unit of work.

Transaction

200

What does the atomicity property ensure in a transaction?

It ensures that either all operations in a transaction are performed or none are.

300

Apply the concept of a transaction to an ATM withdrawal.

Deduct money from account → Update balance → Print receipt — all must succeed together or fail completely.

400

Analyze the result if isolation is not maintained in concurrent transactions.

It can lead to problems like dirty read, lost update, or phantom read.

500

Design a transaction for transferring money from Account A to Account B with proper checks.

• Check balance of A

• Deduct amount from A

• Add amount to B

• Commit transaction