ICT 285 Databases Comprehensive Study Guide Questions and Answers Already Passed
Latest Update 2025-2026
Database - Answers A shared collection of logically related data and its description.
Database Management System (DBMS) - Answers The software allowing definition, creation,
manipulation, and maintenance of the database, providing controlled access.
Common DBMS examples - Answers Oracle, MySQL, SQL Server.
Three-Level Architecture of a Database - Answers External (user), Conceptual, and Internal.
Logical Data Independence - Answers Changes to the conceptual schema without affecting user
views.
Physical Data Independence - Answers Changes to the internal schema without affecting logical
or conceptual views.
Relations - Answers Relations are represented as tables.
Tuples - Answers Rows in a table.
Attributes - Answers Columns in a table.
Properties of a Valid Relation - Answers Each tuple must be unique. Each tuple is about ONE
entity. Each attribute contains data from the same domain. Each attribute has a unique name.
Each cell is atomic.
Superkey - Answers Any attribute or combination of attributes with unique values for each tuple.
Candidate Key - Answers A minimal superkey (cannot remove any attribute without it ceasing to
be a key).
Primary Key (PK) - Answers A chosen candidate key to uniquely identify each tuple.
Alternate Key - Answers A candidate key not chosen as the primary key.
Foreign Key (FK) - Answers An attribute or set of attributes in one relation that refers to the
primary key of another (or the same) relation.
Entity Integrity - Answers Primary key values cannot be NULL.
Referential Integrity - Answers Foreign key values must either match a primary key value in the
referenced table or be NULL (if allowed).
Restrict (Selection) - Answers Produces a subset of tuples based on a condition.
Project - Answers Produces a subset of attributes (columns).
, Join - Answers Combines data from two or more relations based on a common attribute.
Outer Joins - Answers Include unmatched tuples.
Division - Answers Matches on a subset of values (e.g., 'Suppliers who supply all parts').
SQL Overview - Answers SQL is the industry-standard language for relational databases.
Data Definition Language (DDL) - Answers Includes CREATE, ALTER, DROP (for tables, views,
indexes, constraints).
Data Manipulation Language (DML) - Answers Includes SELECT, INSERT, DELETE, UPDATE.
Data Control Language (DCL) - Answers Includes GRANT, REVOKE.
Transaction Control Language (TCL) - Answers Includes COMMIT, ROLLBACK.
SQL/Persistent Stored Modules (SQL/PSM) - Answers Extensions for procedural programming
(functions, triggers, procedures).
CREATE TABLE - Answers Define columns, data types, and integrity constraints (PRIMARY KEY,
FOREIGN KEY, CHECK, NOT NULL, UNIQUE).
CREATE TABLE AS - Answers Create a new table based on an existing table's structure and/or
data.
ALTER TABLE - Answers Modify an existing table's structure (add/drop columns, add/drop
constraints, modify column definitions).
DROP TABLE - Answers Delete a table structure and its contents entirely.
CREATE VIEW - Answers Create a virtual table or stored query.
WITH CHECK OPTION - Answers Ensure view updates comply with underlying table constraints.
DROP VIEW - Answers Delete a view.
CREATE INDEX - Answers Create unique and non-unique secondary indexes to improve access
performance.
DROP INDEX - Answers Delete an index.
NULL Values in SQL - Answers Understand that NULL is not a value; it represents unknown or
inapplicable data.
NULL Comparisons - Answers SQL's inconsistent or non-logical treatment of NULL in
comparisons (e.g., NULL = NULL is unknown, DISTINCT treats all NULLs as the same for
grouping).
Latest Update 2025-2026
Database - Answers A shared collection of logically related data and its description.
Database Management System (DBMS) - Answers The software allowing definition, creation,
manipulation, and maintenance of the database, providing controlled access.
Common DBMS examples - Answers Oracle, MySQL, SQL Server.
Three-Level Architecture of a Database - Answers External (user), Conceptual, and Internal.
Logical Data Independence - Answers Changes to the conceptual schema without affecting user
views.
Physical Data Independence - Answers Changes to the internal schema without affecting logical
or conceptual views.
Relations - Answers Relations are represented as tables.
Tuples - Answers Rows in a table.
Attributes - Answers Columns in a table.
Properties of a Valid Relation - Answers Each tuple must be unique. Each tuple is about ONE
entity. Each attribute contains data from the same domain. Each attribute has a unique name.
Each cell is atomic.
Superkey - Answers Any attribute or combination of attributes with unique values for each tuple.
Candidate Key - Answers A minimal superkey (cannot remove any attribute without it ceasing to
be a key).
Primary Key (PK) - Answers A chosen candidate key to uniquely identify each tuple.
Alternate Key - Answers A candidate key not chosen as the primary key.
Foreign Key (FK) - Answers An attribute or set of attributes in one relation that refers to the
primary key of another (or the same) relation.
Entity Integrity - Answers Primary key values cannot be NULL.
Referential Integrity - Answers Foreign key values must either match a primary key value in the
referenced table or be NULL (if allowed).
Restrict (Selection) - Answers Produces a subset of tuples based on a condition.
Project - Answers Produces a subset of attributes (columns).
, Join - Answers Combines data from two or more relations based on a common attribute.
Outer Joins - Answers Include unmatched tuples.
Division - Answers Matches on a subset of values (e.g., 'Suppliers who supply all parts').
SQL Overview - Answers SQL is the industry-standard language for relational databases.
Data Definition Language (DDL) - Answers Includes CREATE, ALTER, DROP (for tables, views,
indexes, constraints).
Data Manipulation Language (DML) - Answers Includes SELECT, INSERT, DELETE, UPDATE.
Data Control Language (DCL) - Answers Includes GRANT, REVOKE.
Transaction Control Language (TCL) - Answers Includes COMMIT, ROLLBACK.
SQL/Persistent Stored Modules (SQL/PSM) - Answers Extensions for procedural programming
(functions, triggers, procedures).
CREATE TABLE - Answers Define columns, data types, and integrity constraints (PRIMARY KEY,
FOREIGN KEY, CHECK, NOT NULL, UNIQUE).
CREATE TABLE AS - Answers Create a new table based on an existing table's structure and/or
data.
ALTER TABLE - Answers Modify an existing table's structure (add/drop columns, add/drop
constraints, modify column definitions).
DROP TABLE - Answers Delete a table structure and its contents entirely.
CREATE VIEW - Answers Create a virtual table or stored query.
WITH CHECK OPTION - Answers Ensure view updates comply with underlying table constraints.
DROP VIEW - Answers Delete a view.
CREATE INDEX - Answers Create unique and non-unique secondary indexes to improve access
performance.
DROP INDEX - Answers Delete an index.
NULL Values in SQL - Answers Understand that NULL is not a value; it represents unknown or
inapplicable data.
NULL Comparisons - Answers SQL's inconsistent or non-logical treatment of NULL in
comparisons (e.g., NULL = NULL is unknown, DISTINCT treats all NULLs as the same for
grouping).
