Computer Science: Database Fundamentals

What is a Primary Key in a relational database?

A Primary Key is a unique identifier for each record in a table. It must contain unique values and cannot contain NULL values.

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

The SELECT statement is used to select data from a database, which is then stored in a result table called a result-set.

What does the 'A' in ACID properties stand for, and what does it guarantee?

Atomicity. It guarantees that a transaction is treated as a single 'unit', which either succeeds completely or fails completely; there is no partial completion.

What is the purpose of a Foreign Key?

A Foreign Key is a field (or collection of fields) in one table that refers to the Primary Key in another table, establishing a link between the data in the two tables.

Which SQL aggregate function is used to calculate the total number of rows that match a specified criterion?

The COUNT() function returns the number of rows that matches a specified criterion.

What is the difference between the DELETE and DROP commands?

DELETE is a DML command used to remove rows from a table based on a condition, while DROP is a DDL command that removes the entire table structure and its data from the database.

Explain the difference between an INNER JOIN and a LEFT JOIN.

An INNER JOIN returns records that have matching values in both tables. A LEFT JOIN returns all records from the left table and the matched records from the right table; if no match exists, NULL values are returned for the right side.

What are the requirements for a table to be in First Normal Form (1NF)?

A table is in 1NF if it contains only atomic (indivisible) values, and each attribute contains only a single value from its domain (no repeating groups or multi-valued attributes).

How does Second Normal Form (2NF) differ from 1NF?

To be in 2NF, a table must first be in 1NF and all non-key attributes must be fully functionally dependent on the entire primary key, meaning no partial dependencies on a composite key.

What is a transitive dependency, and which normal form aims to eliminate it?

A transitive dependency occurs when a non-key attribute depends on another non-key attribute rather than the primary key. Third Normal Form (3NF) aims to eliminate these.