DBMS viva questions and answers (35) plus operating system viva questions (25), in spoken form

What is a DBMS?

A database management system is software that stores, organises and retrieves data for many users while handling security, concurrency and recovery. Examples are MySQL, Oracle and PostgreSQL. In the lab I used MySQL to create and query the tables in my record.

Name the DBMS you actually used; the follow-up will be about it.

What is the difference between a DBMS and a file system?

A file system stores data in separate files with no control over redundancy, consistency or concurrent access; each program must parse the files itself. A DBMS gives a single structured store with a query language, constraints, transactions, access control and backup, so data is not duplicated or corrupted.

What is an RDBMS?

A relational DBMS stores data in tables, called relations, made of rows and columns, with relationships expressed through keys rather than pointers. It follows Codd's relational model and is queried with SQL. MySQL, Oracle, SQL Server and PostgreSQL are RDBMSs; MongoDB is not.

What are the three levels of database architecture?

The internal level is how data is physically stored; the conceptual level is the logical structure, the tables and relationships; the external level is each user's view. The separation gives data independence: a storage change does not force an application change.

What is an ER model? Define entity, attribute and relationship.

The entity-relationship model is a diagram of the data before the tables are built. An entity is a real-world object like Student, an attribute is a property like roll number, and a relationship is an association like Student enrols in Course, with cardinality one-to-one, one-to-many or many-to-many.

What is a primary key?

A column or set of columns that uniquely identifies each row, cannot be null and has one per table. In my student table, roll number is the primary key because two students can share a name but never a roll number.

What are super key, candidate key and alternate key?

A super key is any set of columns that uniquely identifies a row, even with extra columns. A candidate key is a minimal super key. We pick one candidate key as the primary key; the remaining candidate keys are alternate keys, like email when roll number is the primary key.

What is a foreign key, and what is referential integrity?

A foreign key is a column in one table that refers to the primary key of another. Referential integrity means every foreign key value must exist in the referenced table or be null, so the DBMS blocks an issue row for a book id that is not in the book table.

What is the difference between a primary key and a unique constraint?

Both force unique values. A primary key never allows null and a table has only one; a unique constraint allows null in most systems and a table can have several. Email would be unique while roll number is primary.

What is a composite key?

A primary key made of two or more columns together, because no single column is unique. In my enrolment table, student id and course id together form the composite key: a student may take many courses and a course has many students, but each pair appears once.

What are DDL, DML, DCL and TCL?

DDL defines structure: create, alter, drop, truncate. DML works on data: select, insert, update, delete. DCL controls access: grant and revoke. TCL manages transactions: commit, rollback and savepoint. The lab list usually starts with DDL and DML.

What is the difference between a schema and an instance?

The schema is the structure of the database, the tables, columns, types and constraints, which changes rarely. An instance is the actual data at one moment, which changes with every insert or update. The schema is the blueprint; the instance is the building.

What is normalisation and why do we do it?

Normalisation splits a large table into smaller related tables so that each fact is stored once. It removes redundancy and the insertion, update and deletion anomalies that come with it. For example, storing the department name with every student means a rename must touch every row.

What is a functional dependency?

Column B is functionally dependent on column A if each value of A determines exactly one value of B, written A arrow B. Roll number determines name. Functional dependencies are what the normal forms are defined on.

What is first normal form?

A table is in 1NF when every cell holds a single atomic value and there are no repeating groups. A phone column holding two numbers separated by a comma breaks 1NF; the fix is a separate phone table with one number per row.

What is second normal form?

1NF plus no partial dependency: every non-key column must depend on the whole primary key, not part of it. In an enrolment table keyed on student id and course id, storing the student's name depends only on student id, so it is moved to the student table.

What is third normal form?

2NF plus no transitive dependency: a non-key column must not depend on another non-key column. If the student table stores department id and department name, name depends on department id, not on the roll number, so department name moves to a department table.

What is BCNF, and how is it stricter than 3NF?

Boyce-Codd normal form requires that for every functional dependency, the left side is a super key. 3NF allows an exception when the right side is part of a candidate key; BCNF does not. Most lab schemas that reach 3NF are already in BCNF.

What is denormalisation, and when is it used?

Deliberately adding redundancy, such as a computed total or a copied name, to make reads faster by avoiding joins. It is used in reporting databases where reads dominate writes. The cost is that updates must keep the copies consistent.

What is a join, and what are the types?

A join combines rows from two tables on a matching condition. Inner join returns only matching rows; left and right outer joins also keep unmatched rows from one side with nulls; full outer join keeps unmatched rows from both; cross join gives every combination.

What is the difference between an inner join and a left join?

An inner join of student and marks returns only students who have a marks row. A left join returns every student, with null marks for those who have none. If the examiner asks for a list of students who have not appeared, the left join with a where marks is null is the answer.

What is the difference between a natural join and an equi join?

An equi join uses an equality condition that I write explicitly, such as s dot dept id equals d dot dept id, and keeps both columns. A natural join matches automatically on all columns with the same name and shows the common column once. I avoid natural join because an accidental common column breaks it.

What is a self join, and when do you use it?

Joining a table with itself using two aliases, to compare rows within one table. The classic case is an employee table whose manager id refers to another employee: a self join lists each employee next to their manager's name.

What is a Cartesian product and when does it happen by mistake?

Every row of one table paired with every row of the other, so a hundred students times ten courses gives a thousand rows. It happens by mistake when I list two tables in from and forget the join condition in where, which is the most common wrong output in the lab.

What is the difference between delete, truncate and drop?

Delete removes rows one at a time, can take a where clause and can be rolled back. Truncate removes all rows at once, is faster, resets identity and in most systems cannot be rolled back. Drop removes the table itself with its structure.

What is the difference between where and having?

Where filters rows before grouping, so it cannot use aggregate functions. Having filters groups after group by, so it can. To find departments with more than fifty students I group by department and use having count greater than fifty.

What are aggregate functions?

Functions that compute one value from many rows: count, sum, avg, min and max. With group by they give one result per group, such as average marks per course. Count star counts rows; count of a column ignores nulls.

What is a view?

A virtual table defined by a stored select query; it has no data of its own and shows the current result each time it is used. Views simplify complex joins and hide columns, for example a view of students' names and marks without their phone numbers.

What is an index, and what is its cost?

A data structure, usually a B-tree, that lets the DBMS find rows by a column value without scanning the whole table, like a book's index. The cost is extra storage and slower inserts and updates, so I index only columns used in where and join conditions.

What is the difference between a stored procedure and a function?

Both are named SQL blocks stored in the database. A procedure is called with a call statement, may return several values through out parameters and can run DML. A function must return exactly one value and is used inside a select. In my PL/SQL experiment I wrote a procedure to update fees.

What is a trigger?

A block that runs automatically before or after an insert, update or delete on a table. I wrote one that inserts a row into an audit table whenever marks are updated. Triggers enforce rules the application might forget, but too many make behaviour hard to trace.

What is a subquery, and when would you use a join instead?

A query nested inside another, such as select name from student where marks greater than select avg marks from student. A correlated subquery runs once per outer row. When I need columns from both tables, a join is clearer and usually faster.

What is a transaction, and what are the ACID properties?

A transaction is a set of SQL statements treated as one unit. Atomicity: all or nothing. Consistency: the database moves from one valid state to another. Isolation: concurrent transactions do not see each other's half-done work. Durability: a committed change survives a crash. A bank transfer is the example.

What do commit, rollback and savepoint do?

Commit makes the transaction's changes permanent. Rollback undoes everything since the last commit. Savepoint marks a point inside the transaction so I can roll back to it without losing the earlier work. In the lab I used rollback to undo a wrong update before committing.

What problems does concurrency cause, and how does the DBMS control it?

Lost update, dirty read, non-repeatable read and phantom read, when two transactions touch the same data at once. The DBMS prevents them with locks, shared for reading and exclusive for writing, and with isolation levels from read uncommitted up to serializable.

What is an operating system, and what are its main functions?

The system software between the hardware and the user programs. Its functions are process management, memory management, file system management, device management and security. Linux, Windows and Android are examples; in the lab we wrote scheduling and synchronisation programs on Linux.

What is the difference between the kernel and the shell?

The kernel is the core of the OS that talks to the hardware and manages processes and memory; it runs in privileged mode. The shell is the user interface, such as bash, that takes commands and asks the kernel to run them through system calls.

What is the difference between a program and a process?

A program is a passive file on disk. A process is that program running, with its own memory, registers, program counter and a process control block in the kernel. One program opened twice gives two processes.

What are the process states?

New, when it is created; ready, waiting for the CPU; running, on the CPU; waiting or blocked, for I/O or an event; and terminated. A running process moves to ready when its time slice ends and to waiting when it asks for I/O.

What is a process control block?

The kernel's record for each process: its id, state, program counter, CPU registers, scheduling priority, memory limits and open files. On a context switch the kernel saves the running process's registers into its PCB and loads the next one's.

What is the difference between a process and a thread?

A thread is a lightweight unit of execution inside a process. Threads of one process share its code, data and open files but have their own stack and registers, so creating and switching them is cheaper. A browser uses many threads for tabs and downloads.

What is a context switch?

Saving the state of the running process in its PCB and loading the state of the next one so the CPU can run it. It is pure overhead, no useful work is done during it, which is why very small time quanta in round robin hurt performance.

What is a system call? Give examples.

The way a user program asks the kernel for a service it cannot do itself, switching from user mode to kernel mode. Examples are fork to create a process, exec to run a program, read and write for files, and wait. printf eventually calls write.

What is the difference between user mode and kernel mode?

In user mode a program can run only unprivileged instructions and touch its own memory. In kernel mode the OS can execute any instruction and access all hardware. The mode bit switches on a system call or interrupt and back on return, protecting the system from a faulty program.

What are long-term, short-term and medium-term schedulers?

The long-term scheduler decides which jobs enter the ready queue and controls the degree of multiprogramming. The short-term or CPU scheduler picks which ready process runs next, many times a second. The medium-term scheduler swaps processes out of memory and back in.

Explain FCFS, SJF, priority and round robin scheduling in one line each.

First come first served runs processes in arrival order and suffers the convoy effect. Shortest job first runs the shortest burst next and gives minimum average waiting time but needs the burst time in advance. Priority runs the highest priority first and can starve low ones. Round robin gives each process a fixed time quantum in turn, which is fair and suits time-sharing.

What is the difference between preemptive and non-preemptive scheduling?

In non-preemptive scheduling a process keeps the CPU until it finishes or blocks, as in FCFS. In preemptive scheduling the OS can take the CPU away, on a timer in round robin or when a shorter job arrives in SRTF. Preemption gives better response time at the cost of context switches.

What is starvation, and what is aging?

Starvation is a process waiting indefinitely because higher-priority or shorter processes keep arriving ahead of it. Aging fixes it by gradually raising the priority of a process the longer it waits, so it eventually runs.

What is a race condition, and what is the critical section?

A race condition is when the result depends on the order in which two processes access shared data, like two threads incrementing the same counter and losing an update. The critical section is the code that touches the shared data, and only one process may be inside it at a time.

What is a semaphore, and how is it different from a mutex?

A semaphore is an integer with two atomic operations, wait and signal, used to control access to a resource. A mutex is a binary lock owned by the thread that acquired it, so only the owner can release it. A counting semaphore can allow several processes into a pool of resources.

Explain the producer-consumer problem.

A producer puts items into a bounded buffer and a consumer removes them. The producer must wait when the buffer is full and the consumer when it is empty, and both must not touch the buffer together. The lab solution uses two counting semaphores, empty and full, plus a mutex.

What is a deadlock, and what are the four necessary conditions?

A deadlock is a set of processes each waiting for a resource held by another, so none can proceed. The four conditions are mutual exclusion, hold and wait, no preemption and circular wait. All four must hold at once; breaking any one prevents deadlock.

What is the difference between deadlock prevention, avoidance and detection?

Prevention makes one of the four conditions impossible, for example by ordering resources. Avoidance grants a request only if the state stays safe, as in the Banker's algorithm. Detection lets deadlocks happen, finds them with a wait-for graph and recovers by killing or rolling back a process.

What is the Banker's algorithm?

A deadlock avoidance method where each process declares its maximum need in advance. On every request the OS pretends to grant it and checks whether a safe sequence still exists in which every process can finish; if not, the process waits. I implemented it in the lab with the available, max and allocation matrices.

What is the difference between paging and segmentation?

Paging divides memory into fixed-size pages and frames, invisible to the programmer, and removes external fragmentation. Segmentation divides a program into variable-size logical segments like code, data and stack, which matches how programmers think but suffers external fragmentation. Modern systems page within segments.

What is virtual memory?

A technique that lets a process use more memory than is physically present by keeping only the needed pages in RAM and the rest on disk. The page table maps virtual addresses to physical frames. It allows more processes to run at once and simplifies programming.

What is a page fault, and what is demand paging?

A page fault is the trap raised when a process accesses a page not currently in RAM; the OS loads it from disk and retries. Demand paging is loading a page only on its first fault instead of loading the whole program at start, which is how virtual memory works.

Explain FIFO, LRU and optimal page replacement.

When a frame is needed, FIFO evicts the page that has been in memory longest and can suffer Belady's anomaly. LRU evicts the page unused for the longest time and is a good approximation of optimal. Optimal evicts the page that will not be used for the longest time, which is the lower bound but needs future knowledge.

What is thrashing?

A state where a process spends more time servicing page faults than executing, because it has too few frames for its working set. CPU utilisation drops and the scheduler may add more processes, making it worse. The fix is the working-set model or page-fault frequency control.

What is the difference between internal and external fragmentation?

Internal fragmentation is wasted space inside an allocated block, like a 3 KB process in a 4 KB page. External fragmentation is free memory split into gaps too small for a large request, as in variable-partition allocation. Paging removes external fragmentation; compaction reduces it.