Question 1

What are the two broad categories of databases? Name examples of each.

Accepted Answer

- SQL: Type - Relational (RDBMS). Examples - PostgreSQL, MySQL, Oracle, SQL Server.
- NoSQL: Type - Non-relational. Examples - MongoDB, Cassandra, Redis, Neo4j.

Question 2

What is an RDBMS, and where does the word "relational" come from?

Accepted Answer

A Relational Database Management System stores data as tables of rows and columns. "Relational" refers to the ability to create relationships between tables: in a blog, users create posts, posts receive comments, comments are written by users, and all of that is linked through keys.

Question 3

What problems do relational databases solve, and when are they the ideal choice?

Accepted Answer

They organize large amounts of structured data, prevent duplication, maintain consistency, support complex relationships and enable efficient querying. Ideal when data has a predefined structure, relationships between entities matter, and integrity and consistency are critical.

Question 4

Define entity, table, attribute and record.

Accepted Answer

- Entity: a real-world object or concept you store information about (User, Product, Order, Course). Usually one table per entity.
- Table: a collection of related data in rows and columns representing one entity, e.g. a users table.
- Attribute (column): a property of the entity, e.g. id, first_name, last_name, mobile.
- Record (row): one complete instance of the entity with a value for every attribute, e.g. 1, Rahul, Sharma, 9876543210.

Question 5

What naming conventions does the course recommend for tables and columns?

Accepted Answer

- Tables plural (users, posts, comments, orders) because a table holds many records.
- Columns singular and descriptive (first_name, created_at, updated_at).
- Most tables carry a unique id as the primary key.

Question 6

Why are relationships the most powerful feature of a relational database?

Accepted Answer

They connect tables so data can be linked logically and questions can be answered efficiently: which user created this post? which comments belong to this post? which user wrote this comment? In a blog schema, posts.user_id and comments.post_id carry those links.

Question 7

Which SQL operations form the foundation of database interaction?

Accepted Answer

CREATE tables, INSERT records, SELECT to retrieve, UPDATE to modify, DELETE to remove.

Question 8

When do you pick a relational database? Give real examples.

Accepted Answer

When data is highly structured, relationships matter, consistency is critical, transactions must be reliable, and business rules need strict enforcement. Examples: banking systems, e-commerce platforms, ERP, student management, inventory management.

Question 9

What is a database constraint, and why enforce rules at the database layer instead of only in application code?

Accepted Answer

A constraint is a rule that restricts the values that can be inserted, updated or deleted in a table, to maintain integrity and reliability. It lives in the database, so it protects data regardless of which application touches it.

If validation lives only in app code: different services implement different rules, bugs bypass validations, and direct database operations can insert invalid data. Constraints are the final line of defense.

Question 10

List the six constraint types with an example each.

Accepted Answer

- PRIMARY KEY: Purpose - Uniquely identifies each record. Example - User ID.
- FOREIGN KEY: Purpose - Creates relationships between tables. Example - user_id in posts.
- NOT NULL: Purpose - Prevents empty values. Example - User name.
- UNIQUE: Purpose - Prevents duplicate values. Example - Email address.
- CHECK: Purpose - Validates data against a condition. Example - Age ≥ 18.
- DEFAULT: Purpose - Assigns a value automatically. Example - Status = active.

Question 11

Primary key: characteristics, and what breaks without one.

Accepted Answer

Must be unique, cannot be NULL, only one per table, usually an auto-incrementing ID. It is the identity of a record.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

Without it: duplicates are hard to identify, updates and deletes may hit the wrong rows, and relationships between tables cannot be established reliably.

Question 12

Foreign key and referential integrity: explain with the posts example.

Accepted Answer

A foreign key references the primary key of another table and guarantees the referenced record exists. posts.user_id references users.id, so every post must belong to an existing user. If users has only IDs 1 and 2, inserting a post with user_id = 99 fails.

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    title VARCHAR(200),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Benefits: maintains relationships, prevents invalid references, keeps related records consistent.

Question 13

NOT NULL: what it guarantees and where it is typically applied.

Accepted Answer

The column must always contain a value. A row with a NULL mobile may be fine, but first_name and last_name are mandatory. Typical: names, emails, passwords, order IDs, product names.

Question 14

UNIQUE vs PRIMARY KEY: how do they differ?

Accepted Answer

UNIQUE forbids duplicate values in a column (emails, mobile numbers, usernames, employee IDs, account numbers); a table can have many UNIQUE columns. PRIMARY KEY is unique and NOT NULL, there is exactly one per table, and it is the record's identity used by foreign keys.

email VARCHAR(100) UNIQUE

Question 15

CHECK constraint: example and use cases.

Accepted Answer

Validates that data satisfies a condition before it is stored; the operation is rejected if the condition is false.

age INTEGER CHECK (age >= 18)   -- 18, 25, 40 accepted; 16 rejected

Use cases: age limits, positive balances, percentage ranges, product ratings, quantity validation.

Question 16

DEFAULT constraint: example and use cases.

Accepted Answer

Assigns a predefined value when none is supplied on insert.

status VARCHAR(20) DEFAULT 'active'
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

INSERT INTO users(id) VALUES (1);   -- stored as id=1, status='active'

Use cases: account status, creation timestamps, boolean flags, user roles, default preferences.

Question 17

Show a production-style users table that combines constraints.

Accepted Answer

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INTEGER CHECK (age >= 13),
    status VARCHAR(20) DEFAULT 'active'
);

Every user has a unique identity, names cannot be empty, emails cannot repeat, invalid ages are rejected, and a default status is assigned.

Question 18

What happens to child rows when a parent is deleted? Explain the four foreign key delete behaviours.

Accepted Answer

- ON DELETE CASCADE: Delete all related child rows too (delete a user, their posts and comments go)
- ON DELETE SET NULL: Keep the child row, set its foreign key to NULL
- ON DELETE RESTRICT: Refuse to delete the parent while children exist
- ON DELETE NO ACTION: Like RESTRICT; the default in many databases

Question 19

What is a join, and why is it needed at all?

Accepted Answer

A join combines rows from two or more tables using a related column, usually a primary key–foreign key pair. A well-designed relational database splits data into logical entities (users, blogs, comments) to avoid duplication; joins reconnect them when related information must be shown together. Joins directly affect query performance, data modelling and scalability.

Question 20

Name the four types of table relationships.

Accepted Answer

One-to-many, many-to-one, many-to-many, one-to-one.

Databases

Databases