DBMS Basics — Set 3

Correct Answer: A. Data Definition Language

• **DDL (Data Definition Language)** = the subset of SQL commands used to define, modify, and remove the structural components of a database — its tables, schemas, indexes, and constraints. DDL commands describe the blueprint of the database rather than the data itself. • **Core DDL commands** — CREATE builds new tables or databases, ALTER modifies existing structures (adds or drops columns), and DROP permanently removes a table or database. These commands auto-commit, meaning their changes cannot be rolled back in most RDBMS. • DDL is distinct from DML (which manipulates data) and DCL (which controls permissions); together, these three categories cover all SQL operations. • 💡 Option B (Data Distribution Link) is wrong because no such SQL category exists; Option C (Digital Data Logic) is wrong because it is an invented phrase with no standard computing definition; Option D (Detailed Database List) is wrong because DDL is about structural commands, not listing or reporting database contents.

Correct Answer: D. INSERT

• **INSERT** = a DML (Data Manipulation Language) command that adds one or more new rows of data into an existing table. It operates on the actual stored data rather than the table structure, and its effects can be rolled back within a transaction unlike DDL commands. • **DML vs. DDL** — DML commands (SELECT, INSERT, UPDATE, DELETE) work on the data stored within tables; DDL commands (CREATE, ALTER, DROP) work on the table structures themselves. The distinction matters because DDL changes auto-commit while DML changes can be transactional. • A single INSERT statement can add multiple rows at once using a comma-separated list of value sets, making bulk data loading efficient. • 💡 Option A (CREATE) is wrong because CREATE is a DDL command that builds new tables or databases, not inserts data; Option B (DROP) is wrong because DROP is a DDL command that permanently deletes a table including its structure; Option C (ALTER) is wrong because ALTER is a DDL command that modifies an existing table's structure, such as adding or dropping a column.

Correct Answer: A. Changes remain permanent even in case of system failure

• **Durability** = the ACID property that guarantees once a transaction is committed, its changes are permanently recorded in non-volatile storage and will survive any subsequent system failure — power loss, crash, or hardware fault. The database will restore to the committed state when it recovers. • **How it is achieved** — Durability is implemented through write-ahead logging (WAL): changes are first written to a transaction log on disk before being applied to the actual data files; on recovery, the DBMS replays the log to restore committed transactions. • Durability is the final guarantee of the ACID quartet and works together with Atomicity (ensures complete execution) and Consistency (ensures valid state) to make database transactions fully reliable. • 💡 Option B (Data remains unchanged for years) is wrong because Durability refers to surviving system failures after a commit, not long-term archival preservation; Option C (The database can handle many users) is wrong because that describes concurrency or scalability, not Durability; Option D (The speed of the database query) is wrong because query speed is a performance metric, entirely separate from transaction guarantees.

Correct Answer: D. A real-world object or concept

• **Entity** = any real-world object, concept, or thing that has independent existence and about which data is stored in a database. In an ER diagram, entities are shown as rectangles; examples include Student, Employee, Product, or Bank Account. • **Entity vs. Entity Instance** — the entity 'Student' is the type or category, while a specific student like 'Ramesh (Roll No. 101)' is an entity instance or occurrence. A table in the relational model corresponds to an entity type, and each row corresponds to one instance. • Entities have attributes (properties like Name, Age, ID) and participate in relationships with other entities, which are shown as diamonds in ER diagrams. • 💡 Option A (A mathematical function) is wrong because a function maps inputs to outputs, a concept from mathematics and programming unrelated to ER modelling; Option B (A network cable) is wrong because that is physical networking hardware; Option C (A type of programming error) is wrong because programming errors are exceptions or bugs in code, not data modelling concepts.

Correct Answer: C. WHERE

• **WHERE clause** = the SQL clause that filters which rows are included in the result of a SELECT, UPDATE, or DELETE statement, based on a specified condition. Only rows for which the condition evaluates to TRUE are processed or returned. • **Versatility** — WHERE can use comparison operators (=, >, <, >=, <=, !=), logical operators (AND, OR, NOT), pattern matching (LIKE), range checks (BETWEEN), and set membership (IN), making it the primary tool for precision data retrieval. • Example: SELECT * FROM Employees WHERE Salary > 50000 returns only employees earning more than 50,000, ignoring all other rows. • 💡 Option A (GROUP BY) is wrong because GROUP BY groups rows sharing a common value into summary rows, typically used with aggregate functions like SUM or COUNT; Option B (ORDER BY) is wrong because ORDER BY sorts the result set in ascending or descending order but does not filter rows; Option D (SELECT) is wrong because SELECT specifies which columns to retrieve, not which rows to include.

Correct Answer: A. Unnecessary repetition of data

• **Redundancy** = the unnecessary storage of the same piece of data in two or more places within a database. When the same information is duplicated, any update must be made in every location; missing even one location creates inconsistency — different parts of the database show different values for the same fact. • **Problems caused** — redundancy leads to three types of anomalies: insertion anomaly (cannot add data without adding unrelated data), deletion anomaly (deleting one fact accidentally destroys another), and update anomaly (changing one copy of data while missing others). • Normalization, particularly achieving 3NF, is the principal technique for eliminating redundancy by ensuring each data fact is stored in exactly one place. • 💡 Option B (Data security) is wrong because security refers to protecting data from unauthorised access, not its storage duplication; Option C (Data backup) is wrong because backup is a deliberate copy for recovery purposes, a different and intentional form of duplication; Option D (Speed of data access) is wrong because access speed is a performance characteristic, not related to storage duplication.

Correct Answer: D. Data is organized in tables with rows and columns

• **Relational Database** = a database that stores data in structured tables (relations) made up of rows (tuples) and columns (attributes), with tables linked to each other through common fields — typically foreign keys referencing primary keys. • **Why tables-and-columns design dominates** — the tabular structure maps naturally to real-world entities, supports powerful SQL queries spanning multiple tables via joins, and enforces data integrity through constraints; these advantages have made relational databases the industry standard for over four decades. • Examples of relational databases include MySQL, Oracle, PostgreSQL, and Microsoft SQL Server, all of which power everything from small web apps to large enterprise systems. • 💡 Option A (Data stored in circular files) is wrong because no mainstream database model uses circular file structures; Option B (Cannot be used by more than one person) is wrong because relational databases are specifically designed for concurrent multi-user access; Option C (Only supports text data) is wrong because relational databases support many data types including integers, dates, binary data, and JSON.

Correct Answer: A. To retrieve data from a database

• **SELECT statement** = the most fundamental SQL command, used to retrieve specific data from one or more database tables. It returns a result set — a virtual table of rows and columns matching the query — without modifying any stored data. • **Flexibility** — SELECT can retrieve all columns (SELECT *) or specific columns, filter rows with WHERE, sort results with ORDER BY, group rows with GROUP BY, and combine data from multiple tables with JOIN clauses, making it the most versatile SQL command. • SELECT is a read-only operation classified under DQL (Data Query Language), and it has no side effects on the stored data regardless of how complex the query is. • 💡 Option B (To delete a table) is wrong because deleting a table is done with the DROP DDL command; Option C (To change a user's password) is wrong because password management uses DCL commands like ALTER USER or SET PASSWORD; Option D (To shut down the server) is wrong because server management is outside the scope of SQL data commands.

Correct Answer: C. NOT NULL

• **NOT NULL constraint** = a column-level rule that mandates every row must supply a value for that column — the DBMS will reject any INSERT or UPDATE that leaves the column empty (NULL). It ensures mandatory fields always contain meaningful data. • **NULL vs. empty string** — NULL means the absence of any value (unknown or missing), while an empty string is still a value. NOT NULL prevents the former but does not prevent an empty string from being stored; a CHECK constraint is needed for additional validation. • NOT NULL is commonly applied to columns like primary key fields, required registration details (Name, Date of Birth), and status flags where missing data would break application logic. • 💡 Option A (UNIQUE) is wrong because UNIQUE ensures no two rows have the same value in the column but does allow NULL (in most RDBMS, one NULL is permitted); Option B (DEFAULT) is wrong because DEFAULT assigns a fallback value when none is provided, but still allows NULL if DEFAULT NULL is set; Option D (CHECK) is wrong because CHECK validates that inserted values meet a custom expression, not specifically that they are non-null.

Correct Answer: C. Full Outer Join

• **FULL OUTER JOIN** = a join operation that returns all rows from both the left and right tables. Where a row in one table has no matching row in the other, the missing columns are filled with NULL, ensuring no records from either table are lost in the result. • **How it combines LEFT and RIGHT joins** — a LEFT JOIN keeps all rows from the left table (filling NULLs for unmatched right-side columns), a RIGHT JOIN keeps all rows from the right table (filling NULLs for unmatched left-side columns), and FULL OUTER JOIN is the union of both, keeping all rows from both sides. • FULL OUTER JOIN is useful when comparing two datasets to find records that exist in one source but not the other, such as customers in Database A but missing from Database B. • 💡 Option A (Inner Join) is wrong because INNER JOIN returns only rows where there is a match in both tables, discarding all unmatched rows; Option B (Left Join) is wrong because LEFT JOIN only guarantees all rows from the left table, not the right; Option D (Cross Join) is wrong because CROSS JOIN returns the Cartesian product of both tables — every row from the left paired with every row from the right — regardless of any matching condition.