Playing as a guest? to earn XP and track your progress.
🛢️ Relational Databases Quiz
🛢️ Relational Databases
What is the primary difference between a database and a Database Management System (DBMS)?
A database is the software; a DBMS is the physical hard drive
A database is the organized collection of data; a DBMS is the software used to manage it
They are exactly the same thing
A database is local; a DBMS is always on the cloud
A database is the collection of structured data itself. A DBMS is the software application (like MySQL) that controls creation, retrieval, and management of the database.
Which of the following is a key limitation of traditional data storage systems that store files directly on disk without a DBMS?
Data Redundancy
Fast Concurrent Access
Automated Backups
Integrity Constraints
Traditional file systems store duplicate data across different files (redundancy), leading to inconsistency when data is modified.
What is 'Data Inconsistency' in database systems?
Storing different types of data in the same column
Having multiple mismatched copies of the same data in different places
Losing data due to system crashes
Having too many users querying the system
Data inconsistency occurs when redundant copies of the same data are not updated simultaneously, leaving mismatched values in the system.
How does a DBMS eliminate data redundancy?
By compressing files into ZIP archives
By centralizing storage so data is stored in one place and referenced elsewhere
By limiting database access to only one user
By deleting old records automatically every month
Centralization in a DBMS ensures that a specific piece of information is stored in a single table, reducing or eliminating duplicated data.
What does 'Data Independence' mean in a DBMS?
The ability to run the database without an operating system
Separation of data structure and storage details from the application programs
Allowing data files to be saved on any external drive
Ensuring data is not related to any other tables
Data independence means that changes to the physical or logical organization of the database do not require changes to the application programs that use it.
In a university database, a teacher can edit marks, but a student can only view them. Which feature of a DBMS does this illustrate?
Concurrent Access
Data Independence
Security and Access Control
Backup and Recovery
A DBMS supports user-level privilege management, allowing different permissions (read, write, admin) to be granted to different users.
Who proposed the Relational Database Model, and in what year?
Tim Berners-Lee, 1991
Dr. E. F. Codd, 1970
Monty Widenius, 1995
Vint Cerf, 1969
Dr. E. F. Codd proposed the relational model in his landmark 1970 paper while working at IBM.
In the Relational Model, what is the formal mathematical term for a Table?
Tuple
Attribute
Relation
Domain
A table in a relational database is formally called a Relation.
In the Relational Model, what is the formal term for a Row and a Column respectively?
Attribute and Tuple
Tuple and Attribute
Relation and Domain
Domain and Relation
A row is formally called a Tuple, and a column is formally called an Attribute.
What does the term 'Domain' represent in a relational database table?
The web address of the database server
The set of all unique candidate keys
The set of all allowed or valid values for an attribute
The number of rows in the table
A domain is the pool of legal values from which an attribute draws its values (e.g., age domain between 0 and 120).
Which of the following is a fundamental property of a Relation?
Each cell can contain lists or nested tables
The order of rows and columns is not significant
Two columns can share the exact same name
Duplicate rows are allowed if the primary key is empty
In a relation, the order of rows and columns is mathematically irrelevant; they can be stored in any order. Duplicate rows are strictly forbidden.
Which rule ensures that cells in a relation are indivisible (contain single, non-composite values)?
Atomic Value Property
Referential Integrity
Entity Integrity
Domain Constraint
The atomic value property states that each intersection of a row and a column must contain a single, indivisible (atomic) value.
What is the 'Degree' of a relation?
The total number of rows (tuples) in the relation
The number of attributes (columns) in the relation
The maximum value of the primary key
The security level of the table
The degree of a relation is the number of attributes (columns) it contains.
What is the 'Cardinality' of a relation?
The number of attributes (columns) in the relation
The number of tuples (rows) in the relation
The total number of unique keys
The index size of the primary key
Cardinality refers to the number of tuples (rows) currently present in a relation.
If a table Student has columns RollNo, Name, Age, and Gender, and it contains 45 student records, what are its Degree and Cardinality?
Degree = 45, Cardinality = 4
Degree = 4, Cardinality = 45
Degree = 5, Cardinality = 45
Degree = 4, Cardinality = 4
Columns = 4 (Degree), Rows = 45 (Cardinality).
What is a 'Relation Schema'?
The physical binary file stored on the disk
The actual data rows populated in the table
The logical structure or design definition of a relation (its name and attributes)
The system password for database access
A relation schema is the logical definition or blueprint of the table, written as RelationName(Attribute1, Attribute2, ...).
What is a 'Relation Instance'?
The database server execution instance
The set of tuples (data rows) present in the table at a specific point in time
The schema definition command
The foreign key relationship link
A relation instance is the actual data populated in the relation at any given moment, which changes as rows are added, updated, or deleted.
What is a 'Candidate Key' in the Relational Model?
An attribute that can never be null
Any attribute or set of attributes that can uniquely identify each tuple in a relation
A column that links to another table
A key generated by the MySQL server automatically
A candidate key is a minimal set of attributes that uniquely identifies a row in a relation. A table can have multiple candidate keys.
How does a 'Primary Key' differ from a 'Candidate Key'?
They are unrelated concepts
The Primary Key is the specific candidate key chosen by the database designer to uniquely identify tuples
Primary Keys can contain duplicate values
Candidate Keys cannot be null, but Primary Keys can
Out of all candidate keys (which are all eligible to uniquely identify rows), one is selected to be the Primary Key.
What is an 'Alternate Key'?
A key used when the primary key fails
Any candidate key that was NOT chosen as the primary key
A foreign key that connects to a view
A key composed of multiple columns
Candidate keys that are left over after selecting the primary key are called alternate keys.
A table Employee has candidate keys EmpID and AadharNo. If EmpID is chosen as the Primary Key, what is AadharNo?
Foreign Key
Alternate Key
Primary Key
Composite Key
Since AadharNo is a candidate key not chosen as the primary key, it is an alternate key.
What is a 'Foreign Key'?
A key used to encrypt connection requests from external networks
An attribute in one table whose values must match the primary key of another table
A key that must contain unique values only
A temporary key used for sorting data
A foreign key is a column or set of columns in a table that references the primary key of another table, establishing a link.
Which integrity rule states that a foreign key value must either match an existing primary key value in the referenced table, or be NULL?
Entity Integrity Rule
Referential Integrity Rule
Domain Constraint Rule
Unique Key Constraint
Referential integrity ensures that relationships between tables remain consistent; foreign key values must always point to valid primary keys.
Which constraint ensures that the primary key of a table can never contain NULL values?
Referential Integrity
Entity Integrity
Domain Integrity
Check Constraint
Entity integrity states that no primary key attribute can be NULL, ensuring every row is identifiable.
What is a 'View' in database systems?
A screenshot of the query results
A virtual table based on a SELECT query that does not physically store its own data
The physical file path where databases are saved
A graphical user interface tool like phpMyAdmin
A view is a virtual table derived from base tables. It is defined by a query and recalculated live whenever it is queried.
Which of the following is a key advantage of using database Views?
They increase physical disk space usage
They simplify complex queries and enhance security by hiding sensitive columns
They automatically index all columns
They prevent users from using SELECT statements
Views provide security by exposing only a subset of columns/rows to specific users, and they hide query complexity.
What happens to a View when data in its underlying base tables is modified?
The view becomes invalid and must be recreated
The view is automatically updated and displays the new data instantly
The changes are ignored by the view
The view locks the database to prevent edits
Because a view is virtual and executes its defining query dynamically, any changes in the base tables reflect in the view immediately.
MySQL works on which of the following architectural models?
Peer-to-Peer
Client-Server
Monolithic
Decentralized Blockchain
MySQL uses a Client-Server architecture where client applications send SQL statements to a central database server, which processes them.
Which of the following is the background daemon/service executable of the MySQL Server?
mysql.exe
mysqld.exe
mysqlclient.exe
mysqladmin.exe
mysqld (MySQL Daemon) is the background service program that runs the MySQL server.
What command is used in the command-line interface to connect to a local MySQL server as the default admin user 'root'?
mysql -connect root
mysql -u root -p
mysql -u admin -p
start mysql root
mysql -u root -p executes the client, specifying the user (-u) as root and prompting for a password (-p).
Why must every SQL command typed at the mysql> prompt end with a semicolon ';'?
To encrypt the command before transmission
To signal the MySQL client that the statement is complete and ready to execute
To declare variables
To bypass password checks
Semicolon ';' is the default delimiter in MySQL that tells the parser where a statement ends.
What is Structured Query Language (SQL)?
A proprietary programming language exclusive to Oracle databases
The standard declarative language used to communicate with relational database systems
An operating system for database servers
A graphical database design tool
SQL is the standard language used to define, manipulate, and query data in relational database management systems.
Which SQL statement category is used to define, modify, or delete the logical structure of database objects?
DML (Data Manipulation Language)
DDL (Data Definition Language)
TCL (Transaction Control Language)
DCL (Data Control Language)
DDL (Data Definition Language) commands work on schemas/structures (e.g., CREATE, ALTER, DROP).
Which of the following is classified as a Data Definition Language (DDL) command?
INSERT
UPDATE
ALTER
DELETE
ALTER modifies the structure of a table, making it a DDL command. INSERT, UPDATE, and DELETE are DML.
Which SQL statement category handles retrieving, inserting, modifying, and deleting the actual data rows in tables?
DDL
DML
DCL
TCL
DML (Data Manipulation Language) commands operate directly on the data rows (records) inside tables.
Which of the following is a Data Manipulation Language (DML) command?
CREATE
DROP
UPDATE
TRUNCATE
UPDATE modifies existing records in a table, making it a DML command.
Under which category do the SQL commands GRANT and REVOKE fall?
DDL
DML
DCL (Data Control Language)
TCL
DCL (Data Control Language) commands are used to manage privileges and access rights.
Which category of SQL statements handles transaction boundaries (making changes permanent or rolling them back)?
DDL
DML
DCL
TCL (Transaction Control Language)
TCL handles transaction management via commands like COMMIT and ROLLBACK.
What does the TCL command COMMIT do?
Terminates the MySQL server connection
Saves all changes made in the current transaction permanently to the database
Undoes the last batch of changes
Locks the table for editing
COMMIT writes all transaction changes to disk, making them permanent.
What does the TCL command ROLLBACK do?
Saves a backup copy of the database
Undoes all changes made in the current transaction, returning the database to its previous committed state
Deletes the table structure
Lists the last 10 executed queries
ROLLBACK aborts the current transaction and reverts all modifications since the last commit.
What is the difference between DROP and TRUNCATE commands?
DROP is DDL; TRUNCATE is DML
DROP deletes the table structure + data; TRUNCATE removes all rows but retains the empty table structure
TRUNCATE can use a WHERE clause, but DROP cannot
TRUNCATE can be rolled back, but DROP cannot
DROP TABLE deletes the table schema entirely. TRUNCATE TABLE deletes all data rows instantly but leaves the empty table definition intact.
How does TRUNCATE differ from DELETE (without a WHERE clause)?
TRUNCATE is slower than DELETE
TRUNCATE is a DDL command (auto-committed and cannot be rolled back); DELETE is a DML command (can be rolled back)
DELETE retains table structure, while TRUNCATE deletes it
DELETE can only remove one row at a time
TRUNCATE is a DDL operation that drops/recreates the table internally (auto-committed, cannot be rolled back). DELETE is a DML operation that deletes rows sequentially and can be rolled back.
Which SQL command is used to permanently delete an entire database schema and all its tables?
DELETE DATABASE
DROP DATABASE
TRUNCATE DATABASE
REMOVE DATABASE
DROP DATABASE database_name deletes the database and all its contents permanently.
Can DDL statements like CREATE, ALTER, or DROP be undone using the ROLLBACK command in standard MySQL?
Yes, always
No, because DDL statements are auto-committed immediately upon execution
Only if the database is configured in read-only mode
Only by the root user
DDL operations in MySQL trigger an implicit commit. They cannot be rolled back.
In the SQL statement SELECT * FROM Student WHERE Marks > 90;, under which category does SELECT fall?
DDL
DQL (Data Query Language)
TCL
DCL
SELECT retrieves data, making it part of DQL (Data Query Language) or DML.
A column SubjectID in the Enrollment table references SubjectID in the Subjects table. What is SubjectID in Enrollment?
Primary Key
Alternate Key
Foreign Key
Composite Key
A column that references another table's primary key is a foreign key.
Which property of a relation states that no two tuples (rows) in a relation can be identical?
Unique Column Property
Atomic Value Property
Tuple Uniqueness Property
Domain Constraint Property
A relation is mathematically a set of tuples. Since sets do not allow duplicates, every tuple in a relation must be unique.
What is a 'Composite Primary Key'?
A primary key composed of two or more attributes to uniquely identify a tuple
A key that links to multiple foreign tables
A primary key that contains a float data type
A key generated by joining two different tables
A composite primary key is a primary key that consists of more than one column.
If a relation has 5 columns and 10 rows, what is its Degree and Cardinality?
Degree = 10, Cardinality = 5
Degree = 5, Cardinality = 10
Degree = 15, Cardinality = 50
Degree = 5, Cardinality = 5
Degree = columns (5), Cardinality = rows (10).
What does the SQL command 'ALTER TABLE' allow you to do?
Modify data rows inside the table
Modify the structure of an existing table (add, modify, or drop columns)
Delete the entire table
Change user privileges
ALTER TABLE is a DDL command used to add, remove, or change columns and constraints in an existing table structure.
What is the correct way to delete all records from a table Student but keep its columns and constraints for future data insertion?
DROP TABLE Student;
TRUNCATE TABLE Student; or DELETE FROM Student;
REMOVE TABLE Student;
ALTER TABLE Student DROP ALL;
Both TRUNCATE TABLE Student; and DELETE FROM Student; clear all rows but retain the structure. DROP destroys the structure.