Computer Science Exit Exam Study Guide · 226 questions
Read the question first. Select the eye beside the reading hint, study the cited module section, then reveal the answer. Each eye controls only its own block. All hints and answers start closed whenever this page opens.
Read the linked study-guide sections before revealing the answer. PDF page references count from the first page of the document.
226 of 226 questions
Question 1
Which one of the following is not correct when mapping ER diagrams to relational tables?
A. Multi-valued attributes are mapped to new tables
B. Foreign key are used to map relationships to create relation between tables
C. Table names are directly mapped from entity names
D. Composite attribute are mapped as it is
Hint / Module Reference (Where to Read)
Apply the ER mapping rule for the given attribute or entity type.
Correct answer: D — Composite attribute are mapped as it is
Atomic relational values exclude storing an undecomposed composite attribute. Relation names need not differ from entity names.
Evidence in the complete study note
Create one relation per strong entity type, preserve its simple attributes and select a candidate key. Expand composite attributes into their useful simple components; do not copy a composite ER structure unchanged into a flat atomic design. Derived values can usually be calculated, avoiding stale stored copies.FDB10
Table names may follow entity names; naming does not invalidate a mapping.FDB10
Map each multivalued attribute to a separate relation containing the owner key and one value.FDB10
Choice by choice
A · incorrect — Multivalues are separated to remove repeating groups (PDF 12, 17.)
B · incorrect — Foreign-key mapping is explicitly described (PDF 12, 17.)
C · incorrect — Naming a relation after its entity is compatible with the mapping (PDF 12, 17.)
D · correct — Keeping a composite value unchanged violates atomicity (PDF 12, 17.)
Question 2
In DBMS-independent logical relational design, before vendor-specific refinement, which statement is NOT correct?
A. It consider a particular DBMS
B. Normalization is undertaken in logical phase
C. It is based on specific data model
D. It is independent from physical considerations
Hint / Module Reference (Where to Read)
Identify the design phase from its inputs and intended deliverable.
Correct answer: A — A. It consider a particular DBMS
Logical design maps the conceptual model into relations, keys and constraints and checks normalization. It therefore uses a particular data model while separating storage decisions from logical structure. Under the stated DBMS-independent methodology, considering a particular vendor's DBMS is the false claim. Normalization, a chosen model and independence from physical storage are all consistent with this phase.
Evidence in the complete study note
First collect business facts, required transactions, reports, volumes, privacy rules and quality targets. Conceptual design turns them into an ER/EER model. Logical design maps the conceptual model to relations, keys and constraints and checks normalization. Physical design chooses file organization, indexes and storage. Implementation creates and loads the actual objects; testing checks requirements.FDB05
Whether logical design is DBMS-dependent depends on the stated design methodology.FDB05
Logical relational design is tied to a data model but can remain independent of a particular DBMS.FDB05
Choice by choice
A · correct — False in the stated phase: particular-DBMS implementation choices are deferred.
B · incorrect — True: normalization checks the mapped relations.
C · incorrect — True: relational logical design uses a specific data model.
D · incorrect — True: file layouts and indexes belong to physical design.
Question 3
Which SQL command would you use if you want to specify constraints to be applied on tuples?
A. GRANT
B. SELECT
C. REVOKE
D. CHECK
Hint / Module Reference (Where to Read)
Read each SQL constraint's scope and enforcement rule.
CHECK verifies criteria on attribute values within rows; GRANT and REVOKE control permission, and SELECT retrieves data.
Evidence in the complete study note
Example: CREATE TABLE Enrollment (StudentID INTEGER NOT NULL REFERENCES Student(StudentID), CourseID INTEGER NOT NULL REFERENCES Course(CourseID), Grade INTEGER CHECK (Grade BETWEEN 0 AND 100), PRIMARY KEY (StudentID,CourseID)); Each column declaration gives its name then type, with commas between declarations. CHECK is a constraint clause, not a standalone GRANT-like statement. POSITIVE is not a general SQL constraint keyword; CHECK (Amount > 0) expresses positivity.FDB16
A CHECK can pass when its result is unknown, so add NOT NULL if missing values must be forbidden.FDB16
DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE.FDB26
Choice by choice
A · incorrect — Permission grant is not a tuple predicate (PDF 49, 54.)
B · incorrect — Retrieval is not constraint specification (PDF 49, 54.)
C · incorrect — Permission removal is not a tuple predicate (PDF 49, 54.)
D · correct — CHECK is explicitly shown verifying criteria (PDF 49, 54.)
Question 4
Which data management approach allows application programs to define and manage their own data?
A. Manual approach
B. Database approach
C. Hierarchical approach
D. Traditional file based approach
Hint / Module Reference (Where to Read)
Review the database approach and match each claimed benefit or cost to its mechanism.
Correct answer: D — Traditional file based approach
The file approach lacks the database approach separation between programs and data; application-owned data corresponds to traditional file processing. B reverses that distinction.
Evidence in the complete study note
A database is a coherent collection of related facts with meaning for an application. A DBMS is software that defines, stores, retrieves, updates, protects and shares that data. The database is the content; the DBMS manages it. A database system also includes applications, people and infrastructure.FDB01
Traditional file applications define their own formats and access procedures.FDB01
Data abstraction hides irrelevant details; program–data insulation separates data definitions from application code.FDB04
Choice by choice
A · incorrect — Manual records do not describe application-owned files (PDF 5, 6.)
B · incorrect — Program/data insulation contradicts the stem (PDF 5, 6.)
C · incorrect — Hierarchical is a model rather than this processing distinction (PDF 5, 6.)
D · correct — Traditional file processing matches the contrast (PDF 5, 6.)
Question 5
Which database constraints are specified in the data definition language?
A. Implicit or inherent model based constraints
B. Semantic constraints
C. Application based constraints
D. Schema based constraints
Hint / Module Reference (Where to Read)
Match the validity requirement to the appropriate constraint definition.
The question asks how constraints are specified, rather than which business meaning they express. FDB15 distinguishes constraints inherent in a data model from constraints explicitly declared in schema DDL. That second category is schema-based. Semantic or application rules may require triggers or application code when ordinary DDL cannot express them.
Evidence in the complete study note
Inherent constraints come from the data model; schema-based constraints are declared through the DDL; semantic/application rules may need triggers or application logic when DDL cannot express them. Integrity improves data validity; authorization controls who may access it. Uniqueness and domain validation are both integrity mechanisms. If a Department primary key is NULL and Manager=107 lacks a referenced employee, entity integrity certainly fails and referential integrity also fails if that foreign key is declared. One INSERT can violate multiple constraints.FDB15
Choice by choice
A · incorrect — Inherent model constraints follow from the data model rather than explicit schema declarations.
B · incorrect — Semantic rules express application meaning and may exceed what ordinary schema DDL can enforce.
C · incorrect — Application-based constraints may require program logic rather than declarations in the database definition.
D · correct — Schema-based constraints are explicitly specified in data definition language declarations.
Question 6
Which SQL command removes an existing relation's table object, including its definition?
A. Delete
B. Drop
C. Remove
D. UPDATE
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
Removing a relation object means removing its definition as well as its contents. DROP TABLE performs that operation. DELETE affects rows while retaining the table, and UPDATE changes values within rows. REMOVE is not the SQL table-removal command. The distinction is between schema removal and operations on data in an existing schema.
Evidence in the complete study note
DELETE removes rows and keeps the table; DROP TABLE removes the table object; ALTER changes its definition. TRUNCATE empties a table using implementation-specific mechanisms. PostgreSQL allows transactional rollback of TRUNCATE, so no rollback information is retained is not a universal SQL property. SQL Server permits DROP DATABASE Sales, suppliers; that comma-list syntax is vendor-specific. SQLite's .databases lists attached names/files in its command-line shell; .database is not the documented command.FDB26
Choice by choice
A · incorrect — DELETE removes rows and retains the table definition.
B · correct — DROP TABLE removes the table object and its definition.
C · incorrect — REMOVE is not the SQL command for dropping a table.
D · incorrect — UPDATE changes existing row values.
Question 7
Which of the following statement best describe relational data model?
A. It is a database model that allows multiple records to be linked to the same owner
file.
B. It structures data in a tree like structure using parent to child relationship.
C. It is a database model to manage data as tuples grouped into relations
D. It is a model that use concepts such as entities, attributes, and relationships
E. Describe how data is stored in the computer, representing information such as
record structures, record ordering, and access paths.
Hint / Module Reference (Where to Read)
Compare the data models by how they organize records and relationships.
Correct answer: C — It is a database model to manage data as tuples grouped into relations
A relational database consists of relations with tuple rows and attribute columns.
Evidence in the complete study note
A data model supplies concepts for structure, relationships, constraints and operations. Conceptual ER models describe entities and relationships. Relational models use tables; hierarchical models organize records in a parent–child tree; classic network models connect records through pointer-based owner/member links, allowing multiple parents. Object models represent objects with identity, state and behavior. A physical model specifies record layouts and access paths. These are different representation choices, not stages of one query.FDB02
A tuple/row/record contains related attribute values; an attribute/column/field describes a property.FDB03
Choice by choice
A · incorrect — Owner-file links do not define tuple relations (PDF 13, 7.)
B · incorrect — A tree is not the stated relational representation (PDF 13, 7.)
C · correct — Tuple rows in relations match the definition (PDF 13, 7.)
D · incorrect — Entities and relationships describe conceptual ER modeling (PDF 13, 7.)
E · incorrect — Storage details define the physical model (PDF 13, 7.)
Question 8
Department(Dept_Name, Manager) has primary key Dept_Name and foreign key Manager referencing Employee(Emp_ID). Employee contains IDs 101–105 only. Consider INSERT INTO Department (Dept_Name, Manager) VALUES (NULL, 107); Which named integrity rules are violated? Select all that apply. Treat key integrity as uniqueness and domain integrity as type/allowed-value checks.
A. Domain constraint
B. Key constraint
C. Entity Integrity constraint
D. Referential Integrity constraint
Hint / Module Reference (Where to Read)
Match the validity requirement to the appropriate constraint definition.
Correct answers (select all): C, D — C. Entity Integrity constraint; D. Referential Integrity constraint
The inserted row has two independent defects. Dept_Name is the primary key, so NULL violates entity integrity. Manager is a non-NULL foreign key, yet employee 107 is absent, violating referential integrity. These are separate requirements and the same insertion fails both. Under the question's taxonomy, neither a duplicate-key violation nor an incompatible-domain value is being asserted.
Evidence in the complete study note
Domain integrity restricts type and allowed values. Key integrity enforces uniqueness. Entity integrity requires every primary-key component to be non-NULL. Referential integrity requires a non-NULL foreign-key value to match an eligible referenced unique/candidate key; self-references are allowed. A parent need not have a child.FDB15
Choice by choice
A · incorrect — The stated domain checks are satisfied; primary-key NULL is classified separately.
B · incorrect — No duplicate key value is inserted; this category tests uniqueness.
C · correct — NULL in the primary key violates entity integrity.
D · correct — Manager 107 has no referenced employee and violates referential integrity.
Question 9
Assume atomic attribute values. Consider R(A,B,C,D) with functional dependencies F = {C→D, C→B, B→C}. Which statement about R is true?
A. The relation R is in second normal form
B. The relation R is in third normal form
C. The candidate key for R is BC
D. The relation R is in Boyce–Codd normal form
E. None of the other statements is true
Hint / Module Reference (Where to Read)
Derive every candidate key before testing dependency-based normal forms.
Correct answer: E — None of the other statements is true
Every candidate key must contain A because no dependency derives it. Both AB and AC determine all attributes and are minimal; BC determines only B, C and D. The nonprime attribute D depends on B or C alone, a proper subset of either composite key, so 2NF fails. The dependency C→D also violates 3NF and BCNF because C is not a superkey and D is nonprime. None of the other statements is true.
Evidence in the complete study note
For R(A,B,C,D), let C→D, C→B and B→C. A never appears on a right-hand side, so every key needs A. (AB)+ gives ABCD through B→C→D; (AC)+ also gives ABCD. AB and AC are minimal keys. BC cannot derive A and is not a key. A, B and C are prime; D is nonprime.FDB18
2NF requires 1NF and no nonprime attribute partially dependent on any candidate key.FDB20
For every nontrivial FD X→A, 3NF permits it when X is a superkey or A is prime. BCNF requires X to be a superkey.FDB21
Choice by choice
A · incorrect — False: D depends on a proper subset of a candidate key, violating 2NF.
B · incorrect — False: C→D has a nonsuperkey determinant and a nonprime dependent attribute, violating 3NF.
C · incorrect — False: (BC)+ = {B,C,D}, so BC cannot determine A and is not a key.
D · incorrect — False: C determines D but is not a superkey, violating BCNF.
E · correct — Correct: each of the other four claims is false.
Question 10
Which schema of the three-schema Architecture describe the structure of the whole
database?
A. Internal schema
B. Conceptual schema
C. External schema
D. schema
Hint / Module Reference (Where to Read)
Trace which schema levels a proposed change affects.
The conceptual schema describes the whole database while internal describes storage and external describes each user view.
Evidence in the complete study note
The external level gives each user group a selected view. The conceptual level describes the whole logical database: entity types, relationships, data types and constraints. The internal/physical level describes files, record placement and access paths. These are abstraction levels, distinct from client/server deployment tiers.FDB04
Choice by choice
A · incorrect — Internal covers physical storage (PDF 8.)
B · correct — Conceptual explicitly covers the whole database (PDF 8.)
C · incorrect — External covers a user subset (PDF 8.)
D · incorrect — Generic schema does not identify the required level (PDF 8.)
Question 11
Which of the following is the disadvantage of a database approach?
A. Data consistency
B. Sharing of data
C. Improved data integrity
D. Increased concurrency
E. Enforcement of standards
F. Complexity
G. None
Hint / Module Reference (Where to Read)
Review the database approach and match each claimed benefit or cost to its mechanism.
Correct answer: F — Complexity is a database-approach disadvantage.
FDB01 identifies complexity and administration costs as possible disadvantages of a DBMS. Consistency, sharing, integrity and centrally supported standards are benefits. Increased concurrency means useful concurrent access here; only uncontrolled concurrent access causes anomalies. Thus complexity is the sole listed disadvantage, even though database management can improve the other listed properties.
Evidence in the complete study note
A database is a coherent collection of related facts with meaning for an application. A DBMS is software that defines, stores, retrieves, updates, protects and shares that data. The database is the content; the DBMS manages it. A database system also includes applications, people and infrastructure.FDB01
Concurrent access itself is useful; uncontrolled concurrent access causes anomalies.FDB01
Separate files can repeat facts, disagree after updates, isolate useful data and make shared integrity or concurrent updates difficult.FDB01
Choice by choice
A · incorrect — Data consistency is improved by shared definitions and controlled updates, making it a benefit.
B · incorrect — Sharing data is a purpose and benefit of the database approach.
C · incorrect — Improved integrity means better enforcement of valid data and is a benefit.
D · incorrect — Concurrent access is useful; uncontrolled interference, rather than increased concurrency itself, causes problems.
E · incorrect — Central management supports consistent definitions and standards, which is a benefit.
F · correct — A DBMS introduces administrative and technical complexity that can outweigh benefits for small applications.
G · incorrect — A listed disadvantage exists: the complexity of database management.
Question 12
Which of the following is a correct statement about relation? (Choose all that applies)
A. Values of each columns is a repeating group/array
B. Values in a column can be from different domain
C. A relation is in the INF
D. Two rows in a relational table can be identical
E. The sequence of columns and rows is insignificant
F. None
Hint / Module Reference (Where to Read)
Distinguish a relation's structure from its current contents and its descriptive metadata.
Correct answers (select all): C, E — A relation is in the INF; The sequence of columns and rows is insignificant
Read INF as 1NF. Relations have atomic cells rather than repeating groups; the relational representation is independent of tuple sequence. The column sequence likewise does not alter named attributes. Distinct tuples and typed attribute domains rule out A,B,D.
Evidence in the complete study note
A relation is a set of tuples, represented as a table. A tuple/row/record contains related attribute values; an attribute/column/field describes a property. Degree counts columns; cardinality counts rows. Classical relations have distinct tuples, atomic cells and domains for attributes; row order has no meaning. Column order has no logical meaning when attributes are identified by name. SQL tables can contain duplicate rows unless constrained.FDB03
1NF requires atomic values in the chosen relational domains and no repeating groups in cells.FDB20
A mapped ER design that correctly splits composite and multivalued structures gives ordinary 1NF tables; ER drawing alone guarantees no higher form.FDB20
D · incorrect — Duplicate tuples conflict with relation uniqueness (PDF 14, 17.)
E · correct — Presentation order does not change named tuple values (PDF 14, 17.)
F · incorrect — C and E already supply valid statements (PDF 14, 17.)
Question 13
Which of the following statement is correct about executing the following SQL query on
the employee database given in question 2? (Choose all that applies)
SELECT Emp_ID, First_Name, Last_Name
FROM Employee E, Department D
WHERE Department <> Sales' and AVG (salary) > 35000 and
E.Department=D.Dept_Name
GROUP BY Department;
A. the query will execute successfully and will obtain all departments with an average
salary higher than 35,000
B. the query will not execute because Department attribute in Group by statement
was not included in the select statement list
C. the query will not execute because we can't include aggregate function in the
where statement
D. the query will execute successfully and will obtain employees of all departments with
an average salary higher than 35,000
Hint / Module Reference (Where to Read)
Apply grouping before filtering groups and computing aggregate output.
Correct answer: C — AVG(salary) cannot be used directly in WHERE.
The query places AVG(salary) > 35000 in WHERE. FDB32 requires ordinary aggregate predicates in HAVING. Omitting Department from SELECT is not itself an error. Selecting ungrouped employee details is another defect unless functional dependence is established. The query therefore does not successfully produce either claimed result.
Evidence in the complete study note
COUNT(*) counts rows, COUNT(x) counts non-NULL x values; SUM and AVG summarize non-NULL numeric values. SQL's usual average function is AVG, not AVERAGE. GROUP BY creates one group per distinct grouping value; HAVING tests aggregate groups after WHERE has filtered individual rows.FDB32
The grouping column need not itself appear in SELECT; check the functional dependence of other selected columns.FDB32
SELECT Department, AVG(Salary) FROM Employee WHERE Department <> 'Sales' GROUP BY Department HAVING AVG(Salary) > 35000; lists qualifying department averages.FDB32
Choice by choice
A · incorrect — The aggregate predicate in WHERE prevents this query from successfully producing department averages.
B · incorrect — A grouping column need not appear in SELECT; that omission is not the stated error.
C · correct — WHERE filters individual rows before grouping, so the ordinary AVG predicate belongs in HAVING.
D · incorrect — The invalid aggregate predicate prevents execution; employee columns also require valid grouping or functional dependence.
Question 14
Which of the following keys is generally used to represent the relationships between the tables?
A. None of the choices is correct
B. Secondary key
C. Primary key
D. Foreign key
Hint / Module Reference (Where to Read)
Match the validity requirement to the appropriate constraint definition.
A foreign key is the child-side reference linking to an existing parent candidate key.
Evidence in the complete study note
Domain integrity restricts type and allowed values. Key integrity enforces uniqueness. Entity integrity requires every primary-key component to be non-NULL. Referential integrity requires a non-NULL foreign-key value to match an eligible referenced unique/candidate key; self-references are allowed. A parent need not have a child.FDB15
A primary key is one selected candidate key; remaining candidates are alternate keys.FDB07
Secondary indexes provide other access paths and must represent all searchable values with record pointers or pointer lists.FDB25
Choice by choice
A · incorrect — A named key does supply the link (PDF 48.)
B · incorrect — Secondary key is not the child reference (PDF 48.)
C · incorrect — Primary key is the target identifier (PDF 48.)
D · correct — Foreign key is the explicit link (PDF 48.)
Question 15
Which of the following refers to the level of data abstraction that describes exactly how the data is actually stored?
A. Conceptual Level
B. Physical Level
C. File Level
D. Logical Level
Hint / Module Reference (Where to Read)
Trace which schema levels a proposed change affects.
The external level gives each user group a selected view. The conceptual level describes the whole logical database: entity types, relationships, data types and constraints. The internal/physical level describes files, record placement and access paths. These are abstraction levels, distinct from client/server deployment tiers.FDB04
Choice by choice
A · incorrect — Conceptual hides storage (PDF 7.)
B · correct — Physical covers storage details (PDF 7.)
C · incorrect — File is not a three-schema level (PDF 7.)
D · incorrect — Logical describes structure rather than layout (PDF 7.)
Question 16
Which one of the following given statements possibly contains the error?
A. select empid where empid = 1009 and Lastname = 'ADDIS';
B. select empid from emp where empid = 10006;
C. select empid from emp;
D. select * from emp where empid = 10003;
Hint / Module Reference (Where to Read)
Trace how SELECT forms its result columns and rows.
Correct answer: A — The first SELECT is missing its FROM source.
The first query reads empid and Lastname but never names a table in FROM. FDB27 allows literal-only SELECT statements without FROM, but this statement is not literal-only. The other three statements include emp as their source and use ordinary valid selection syntax, assuming those table and column names exist.
Evidence in the complete study note
SELECT Name,Address FROM Customer; produces only those mailing-label fields. SELECT * selects all columns; DISTINCT removes duplicate result rows; AS supplies an alias; ALL permits duplicates and does not mean all columns. SQL normally preserves duplicates without DISTINCT. A query reading table columns needs an appropriate FROM source. A literal-only query such as SELECT 1 may legally omit FROM.FDB27
SELECT alone normally reads without changing the database state.FDB27
Typical written clause order is SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY ….FDB27
Choice by choice
A · correct — It refers to table columns without a FROM clause identifying their source.
B · incorrect — It selects empid from emp with a syntactically ordinary equality predicate.
C · incorrect — It supplies emp as the table source and validly selects its empid column.
D · incorrect — It supplies a table source, selects all columns, and filters by empid.
Question 17
Which one of the following is a type of Data Manipulation Command?
A. Delete
B. Alter
C. All choices are correct
D. Create
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
DELETE is explicitly DML; ALTER and CREATE are DDL.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
Choice by choice
A · correct — DELETE is in DML list (PDF 26, 26.)
B · incorrect — ALTER is in DDL list (PDF 26, 26.)
C · incorrect — Other choices include DDL (PDF 26, 26.)
D · incorrect — CREATE is in DDL list (PDF 26, 26.)
Question 18
Which one of the following refers to the "data about data"?
A. Warehouse
B. Directory
C. Sub Data
D. Meta Data
Hint / Module Reference (Where to Read)
Distinguish a relation's structure from its current contents and its descriptive metadata.
The description of database structure and constraints is metadata.
Evidence in the complete study note
A schema describes names, structures and constraints; an instance, state or snapshot is the data at a particular time. Inserting a student changes the state, while adding a column changes the schema. Metadata is data describing data. The catalog or data dictionary records table definitions, types, constraints and often access privileges and optimizer statistics. A schema diagram draws tables and key references; labels such as database diagram can overlap.FDB03
Choice by choice
A · incorrect — Warehouse is not this term (PDF 5.)
B · incorrect — Directory is not this term (PDF 5.)
C · incorrect — Sub Data is not this term (PDF 5.)
D · correct — Meta-data describes the data structure (PDF 5.)
Question 19
A language that used to formulate the schema of database structure is______.
A. Data Manipulation language
B. Data Definition Language
C. Transaction Control Language
D. Data Control Language
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
DDL supplies definitions, while DML handles data operations.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
Choice by choice
A · incorrect — Manipulates contents (PDF 26.)
B · correct — Defines database structure (PDF 26.)
C · incorrect — Controls transactions (PDF 26.)
D · incorrect — Controls privileges (PDF 26.)
Question 20
A table can have only one
A. Unique Key
B. Candidate key
C. Primary key
D. Foreign key
Hint / Module Reference (Where to Read)
Test uniqueness and minimality separately when classifying keys.
One candidate is selected as the primary key; multiple candidates and multiple inter-table references can coexist.
Evidence in the complete study note
A superkey uniquely identifies a tuple. A candidate key is a minimal superkey: removing any component loses uniqueness. Minimal means no unnecessary attribute, not necessarily the fewest columns among all keys. A primary key is one selected candidate key; remaining candidates are alternate keys. A composite key contains multiple attributes and need not be the chosen primary key.FDB07
A table can have several candidates and foreign keys but at most one declared primary-key constraint, possibly containing several columns.FDB07
Prefer stable, compact identifiers when practical, but Email, SSN or EmployeeID is a candidate only if business rules guarantee uniqueness.FDB07
Choice by choice
A · incorrect — Uniqueness need not be the selected identifier (PDF 11, 11.)
B · incorrect — Several candidates are explicitly allowed (PDF 11, 11.)
C · correct — Primary key is the selected candidate (PDF 11, 11.)
D · incorrect — Separate relationships can use separate foreign keys (PDF 11, 11.)
Question 21
When the values in one or more attributes being used as a foreign key must exist there must
be a corresponding primary key value in another table in the database, we have
created
A. Domain Integrity constraint
B. Referential Integrity Constraint
C. Transitive Dependency
D. Entity Integrity Constraints
Hint / Module Reference (Where to Read)
Match the validity requirement to the appropriate constraint definition.
Correct answer: B — Referential Integrity Constraint
Matching foreign-key values to existing parent occurrences is the referential-integrity rule.
Evidence in the complete study note
Domain integrity restricts type and allowed values. Key integrity enforces uniqueness. Entity integrity requires every primary-key component to be non-NULL. Referential integrity requires a non-NULL foreign-key value to match an eligible referenced unique/candidate key; self-references are allowed. A parent need not have a child.FDB15
Full dependency requires all determinant attributes; partial dependency holds through a proper subset.FDB17
A prime attribute belongs to some candidate key; a nonprime attribute belongs to none.FDB17
Single-user-only contradicts the explicit multiuser DBMS requirement.
Evidence in the complete study note
A DBMS centralizes metadata and supports multiple views, authorization, transactions, backup and recovery. It controls redundancy rather than guaranteeing zero duplication.FDB01
A transaction groups operations into one logical unit. Atomicity applies all its effects or none. Consistency means a correct transaction preserves declared invariants. Isolation controls interference between concurrent transactions; serializable isolation provides behavior equivalent to some serial execution. Durability preserves committed effects after a crash, typically through durable logging rather than forcing every data page immediately.FDB37
Concurrent access itself is useful; uncontrolled concurrent access causes anomalies.FDB01
Choice by choice
A · incorrect — ACID is a requirement (PDF 6, 66.)
B · correct — Multiuser support disproves only (PDF 6, 66.)
C · incorrect — Security is supported (PDF 6, 66.)
D · incorrect — Normalization aims to reduce redundancy (PDF 6, 66.)
Question 23
Which of the following is not a function of the database?
A. Analyzing code
B. Provide Backup and recovery
C. Manipulating data
D. Managing stored data
Hint / Module Reference (Where to Read)
Review the database approach and match each claimed benefit or cost to its mechanism.
Data definition, manipulation and recovery are database responsibilities. Arbitrary program-code analysis is not a listed database function; parsing database queries is a narrower task.
Evidence in the complete study note
A database is a coherent collection of related facts with meaning for an application. A DBMS is software that defines, stores, retrieves, updates, protects and shares that data. The database is the content; the DBMS manages it. A database system also includes applications, people and infrastructure.FDB01
A DBMS centralizes metadata and supports multiple views, authorization, transactions, backup and recovery.FDB01
Choice by choice
A · correct — General code analysis is outside the stated functions (PDF 5, 97.)
B · incorrect — Recovery is expressly covered (PDF 5, 97.)
C · incorrect — Manipulation is expressly covered (PDF 5, 97.)
D · incorrect — DBMS manages databases (PDF 5, 97.)
Question 24
Read each pair left→right. Buildings and locations are 1:1; each employee belongs to one department and a department may have many employees; a hospital serves many patients, each registered at one hospital; authors and books are M:N. Which pair is many-to-one?
A. [ Building — Location]
B. [ Employee — Department]
C. [ Hospital — Patient]
D. [ Author — Book]
Hint / Module Reference (Where to Read)
Read the relationship in both directions and check participation constraints.
Many-to-one means several left-side entities may share a right-side entity while each left entity has at most one right partner. Employees share departments under the stated assignment rule. Building→Location is one-to-one, Hospital→Patient is one-to-many and Author→Book is many-to-many. Reading the direction and both maximum counts determines the answer rather than the entity names.
Evidence in the complete study note
Cardinality specifies maximum associated occurrences: 1:1, 1:N or M:N. Minimum cardinality specifies optional/partial participation (zero permitted) or mandatory/total participation (at least one). A maximum of one does not mean participation is compulsory. Relationship degree counts participating entity roles: unary recursive, binary or ternary.FDB09
If each employee belongs to one department and each department has many employees, Employee→Department is N:1; Department→Employee is 1:N.FDB09
Building→Location could also be N:1, so names alone cannot select a unique example.FDB09
Choice by choice
A · incorrect — Building→Location is explicitly 1:1.
B · correct — Many employees can share one department; each employee has one department.
C · incorrect — Hospital→Patient is 1:N under the registration rule.
D · incorrect — Author→Book is explicitly M:N.
Question 25
__________ is a single elementary unit of information representing an attribute of an entity.
A. Entity Set
B. Record
C. File
D. Field
Hint / Module Reference (Where to Read)
Distinguish a relation's structure from its current contents and its descriptive metadata.
A field corresponds to one attribute rather than the collection of attributes describing a whole record. FDB03 equates attribute, column and field, while tuple, row and record refer to related attribute values together. An entity set groups entities, and a file groups stored records, so both are larger collections.
Evidence in the complete study note
A relation is a set of tuples, represented as a table. A tuple/row/record contains related attribute values; an attribute/column/field describes a property. Degree counts columns; cardinality counts rows. Classical relations have distinct tuples, atomic cells and domains for attributes; row order has no meaning. Column order has no logical meaning when attributes are identified by name. SQL tables can contain duplicate rows unless constrained.FDB03
An entity type describes common properties; its current collection of entities is an entity set.FDB06
In Chen notation rectangles represent entity types, diamonds relationships and ellipses attributes; double ellipses denote multivalued attributes and dashed ellipses derived attributes.FDB06
Choice by choice
A · incorrect — An entity set collects entities of one type rather than representing a single attribute.
B · incorrect — A record contains related values for multiple attributes, rather than one elementary field.
C · incorrect — A file groups records and is not an elementary attribute unit.
D · correct — A field represents one attribute or property within a record.
Question 26
An ER model has entity types A and B connected by an attribute-free relationship R. Cardinalities are read A→B. Which conditions allow R to be represented by a single B-key foreign-key column in A, permitting NULL for partial participation? Select all that apply.
A. Relationship R is one -to —many and the participation of A in R is Total.
B. Relationship R is one -to —many and the participation of A in R is Partial.
C. Relationship R is many —to —one and the participation of A in R is Total.
D. Relationship R is many —-to —one and the participation of A in R is Partial.
Hint / Module Reference (Where to Read)
Locate the relationship's cardinality and participation before placing a foreign key.
Correct answers (select all): C, D — C. Relationship R is many —to —one and the participation of A in R is Total.; D. Relationship R is many —-to —one and the participation of A in R is Partial.
A single foreign-key column in A can represent R whenever each A is related to at most one B. This is exactly the many-to-one direction. Total participation makes the reference mandatory; partial participation permits NULL. One-to-many in the opposite direction needs the reference on B or another representation, because one A may have multiple related B values.
Evidence in the complete study note
For 1:N, place the one-side key as a foreign key in the many-side table; store relationship attributes there. Department(DeptID) and Employee(EmpID,DeptID,HireDate) implement many employees per department. If participation is total, make the reference NOT NULL. If partial, a nullable reference can represent no assignment.FDB11
For A→B many-to-one, A can absorb the relationship reference under either total or partial participation; requiring total participation as the only possible case overstates the rule.FDB11
Prefer the totally participating side to reduce NULLs; NOT NULL enforces that side's mandatory reference.FDB11
Choice by choice
A · incorrect — For A→B one-to-many, B normally holds A's key; total participation does not reverse that mapping.
B · incorrect — Partial participation does not make a one-to-many collection fit a single ordinary foreign key in A.
C · correct — Many-to-one with total A participation stores a non-NULL B reference in A.
D · correct — Many-to-one with partial A participation stores a nullable B reference in A.
Question 27
Under set relational algebra, which listed operation is not commutative? Interpret Join as an inner join, adjust its predicate when swapping inputs, and ignore attribute order.
A. Union
B. Join
C. Intersection
D. None
Hint / Module Reference (Where to Read)
Apply each algebra operator to the input relations step by step.
Union and intersection give the same tuple sets when their inputs are exchanged. An inner join also gives corresponding matches after the predicate is adjusted for the swapped inputs; the question explicitly ignores attribute order. Thus none of the three named operations lacks commutativity here. This conclusion depends on the stated inner-join interpretation; preservation of one designated outer side would use a different rule.
Evidence in the complete study note
Selection σ filters tuples; projection π retains attributes and removes duplicate tuples under set semantics. Rename ρ changes relation or attribute names. Union, difference and intersection require union-compatible schemas. Difference R−S is not commutative; union and intersection are. Inner joins are commutative up to attribute order when predicates are adjusted; left outer joins are not generally commutative. Identify the join variant before predicting its behavior.FDB35
Choice by choice
A · incorrect — Union is commutative on union-compatible relations.
B · incorrect — Inner join is commutative under the stated predicate and schema convention.
C · incorrect — Intersection is commutative on union-compatible relations.
D · correct — All three named operators are commutative under these assumptions.
Question 28
Which of the following is a benefit of normalization rather than a potential cost or disadvantage?
A. May require joins to reconstruct decomposed records
B. May reduce system performance
C. Time consuming
D. Reduce duplicate data
Hint / Module Reference (Where to Read)
Distinguish dependency-based design problems from the contents of one sample table.
Normalization organizes facts according to their dependencies to reduce avoidable duplication and update anomalies. That reduction is a benefit. Decomposing a table may require joins when an application needs the original combined information, which can increase retrieval cost for some workloads. Dependency analysis and schema design also take time. These are possible costs, not claims that normalization always slows a system. Declared dependencies suffice for normalization even when the current sample data shows no errors.
Evidence in the complete study note
Normalization separates facts according to their determinants, reducing avoidable repetition and associated anomalies. It does not require seeing bad current data: declared dependencies suffice. Adding a generated row ID may make insertion technically possible, but does not remove repeated facts or fix the dependency design. Reduced redundancy can improve integrity and storage use, but extra keys and joins mean neither storage savings nor faster retrieval is guaranteed.FDB19
Choice by choice
A · incorrect — A possible cost: reconstructing information from decomposed tables may require joins.
B · incorrect — A possible cost: additional joins can reduce performance for some workloads, although this is not inevitable.
C · incorrect — A possible cost: analyzing dependencies and designing a decomposition takes time.
D · correct — Correct: reducing duplicate facts is a principal benefit, rather than a disadvantage.
Question 29
Let you have two relations A and B, each table has 200 and 100 rows of records respectively. So
after applying cross product on those tables, how many rows of records you will have?
A. 300
B. 20000
C. 2000
D. 100
Hint / Module Reference (Where to Read)
Apply each algebra operator to the input relations step by step.
The Cartesian-product example explicitly creates 1000*50 tuples from inputs of sizes 1000 and 50 (PDF p.28, lines 6–8). Applying the same all-pairs construction to 200 and 100 rows yields 200*100 = 20000. Each of the 200 rows is paired with each of the 100 rows; no filtering or duplicate-eliminating projection is specified.
Evidence in the complete study note
A Cartesian product has |R|×|S| tuples and degree degree(R)+degree(S), renaming conflicting attributes if necessary. Thus 200×100=20,000 and 5×7=35. Join equals selection over a product; intersection can be derived from difference. A common minimal basis contains selection, projection, union, difference, product and rename. Assignment is a useful notation, not an indispensable primitive; lists of basic operations vary across textbooks.FDB35
Choice by choice
A · incorrect — 300 adds input sizes instead of counting pairs (PDF p.28).
B · correct — 20000 is 200*100, the illustrated Cartesian pair-count rule (PDF p.28).
C · incorrect — 2000 is ten times too small for the all-pairs count (PDF p.28).
D · incorrect — 100 counts only one input and omits its 200 pairings per row (PDF p.28).
Question 30
______It defines how and where data are organized in physical data storage
A. Internal Schema
B. Conceptual Schema
C. External Schema
D. External and Conceptual Schema
Hint / Module Reference (Where to Read)
Trace which schema levels a proposed change affects.
Internal schema specifies physical structure and access paths.
Evidence in the complete study note
The external level gives each user group a selected view. The conceptual level describes the whole logical database: entity types, relationships, data types and constraints. The internal/physical level describes files, record placement and access paths. These are abstraction levels, distinct from client/server deployment tiers.FDB04
Choice by choice
A · correct — Exact storage schema (PDF 7.)
B · incorrect — Conceptual hides physical detail (PDF 7.)
Conceptual design represents the required entities, attributes and relationships in an ER model. Logical design later maps that conceptual model into relations, keys and constraints and checks normalization. Physical refinement chooses storage organizations and access paths, while application and security work concerns behavior and protection rather than the modeling stage requested. The stem asks for the primary stage building the ER model, not every later activity that might consult it.
Evidence in the complete study note
First collect business facts, required transactions, reports, volumes, privacy rules and quality targets. Conceptual design turns them into an ER/EER model. Logical design maps the conceptual model to relations, keys and constraints and checks normalization. Physical design chooses file organization, indexes and storage. Implementation creates and loads the actual objects; testing checks requirements.FDB05
Choice by choice
A · incorrect — Incorrect: application and security work addresses behavior and protection, rather than building the conceptual ER model.
B · incorrect — Incorrect: physical refinement chooses storage organization and access paths.
C · correct — Correct: conceptual design turns requirements into the ER model.
D · incorrect — Incorrect: schema refinement checks or improves a logical schema, rather than naming the initial ER-model stage.
Question 32
Employee(empid, ...) already exists, has no primary key, and empid values are unique and non-NULL. Which SQL statement adds a primary key on empid?
A. ADD TABLE Employee ADD PRIMARY KEY (empid)
B. ATTACH TABLE Employee ADD PRIMARY KEY (empid)
C. ADD TABLE Employee ATTACH PRIMARY KEY (empid)
D. ALTER TABLE Employee ADD PRIMARY KEY (empid);
Hint / Module Reference (Where to Read)
Distinguish table definition changes from changes to stored rows.
Correct answer: D — D. ALTER TABLE Employee ADD PRIMARY KEY (empid);
Adding a constraint to an existing table uses ALTER TABLE, followed by ADD PRIMARY KEY and the parenthesized key column. The existing unique, non-NULL empid values satisfy the prerequisite for the constraint. Option D has this structure. ADD TABLE and ATTACH TABLE do not perform this alteration, and ATTACH PRIMARY KEY is not the required constraint clause.
Evidence in the complete study note
CREATE TABLE Employee (EmpID INTEGER PRIMARY KEY, Name VARCHAR(80)); declares two columns. The name and its type stay together; the comma separates whole column declarations. To add a primary key later: ALTER TABLE Employee ADD PRIMARY KEY (EmpID); existing data must be unique and non-NULL. A table cannot gain a second primary key without replacing the existing constraint.FDB30
Choice by choice
A · incorrect — ADD TABLE does not modify an existing table's constraints.
B · incorrect — ATTACH TABLE is not the required table-alteration syntax.
C · incorrect — Neither ADD TABLE nor ATTACH PRIMARY KEY forms this SQL alteration.
D · correct — The corrected ALTER TABLE statement adds the primary-key constraint.
Question 33
While checking normalization process, the problem that doesn't exist on
transitive dependency is
A. Modification Anomaly
B. Revoke Anomaly
C. Insertion Anomaly
D. Deletion Anomaly
Hint / Module Reference (Where to Read)
Distinguish dependency-based design problems from the contents of one sample table.
The defined anomalies are insertion, deletion and update; revoke concerns privileges.
Evidence in the complete study note
Suppose Enrollment repeats StudentName for every course. An update anomaly requires changing every copy of the student's name; missed copies disagree. An insertion anomaly prevents recording a new student until some enrollment exists. A deletion anomaly loses the only record of a student's details when their last enrollment is deleted. Revoke anomaly is not a normalization anomaly.FDB19
Normalization separates facts according to their determinants, reducing avoidable repetition and associated anomalies.FDB19
Adding a generated row ID may make insertion technically possible, but does not remove repeated facts or fix the dependency design.FDB19
Choice by choice
A · incorrect — Modification means update (PDF 16.)
B · correct — Revoke is not a normalization anomaly (PDF 16.)
C · incorrect — Insertion is defined (PDF 16.)
D · incorrect — Deletion is defined (PDF 16.)
Question 34
You need to produce a report for mailing labels for customers. The mailing label must
have only the customer name and address. The CUSTOMERS table has these columns,
ID ,NAME,ADDRESS,PHONE ,the SELECT statement to accomplishes this task?
*
A. SELECT name, address FROM customers;
B. SELECT*FROM customers;
C. SELECT id, name, address, phone FROM customers;
D. SELECT cust_name, cust_address FROM customers;
Hint / Module Reference (Where to Read)
Trace how SELECT forms its result columns and rows.
Correct answer: A — SELECT name, address FROM customers;
The required output contains exactly NAME and ADDRESS from the listed schema. FDB27 distinguishes naming specific columns from selecting all columns with *. A names the two available columns explicitly. B and C include unwanted ID and PHONE fields, while D uses column names not supplied by the question.
Evidence in the complete study note
SELECT Name,Address FROM Customer; produces only those mailing-label fields. SELECT * selects all columns; DISTINCT removes duplicate result rows; AS supplies an alias; ALL permits duplicates and does not mean all columns. SQL normally preserves duplicates without DISTINCT. A query reading table columns needs an appropriate FROM source. A literal-only query such as SELECT 1 may legally omit FROM.FDB27
Typical written clause order is SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY ….FDB27
SELECT alone normally reads without changing the database state.FDB27
Choice by choice
A · correct — It selects exactly the two existing columns required for the mailing label.
B · incorrect — The wildcard also includes ID and PHONE, exceeding the required output.
C · incorrect — It explicitly includes ID and PHONE in addition to the requested label fields.
D · incorrect — The supplied schema names NAME and ADDRESS, not CUST_NAME and CUST_ADDRESS.
Question 35
If the maximum cardinality is equal to 1 in both directions of a relationship, then it is
classified as:
A. Optional
B. Many-to-many
C. One-to-one
D. One-to-many
Hint / Module Reference (Where to Read)
Read the relationship in both directions and check participation constraints.
At most one related entity on each side is the one-to-one case.
Evidence in the complete study note
Cardinality specifies maximum associated occurrences: 1:1, 1:N or M:N. Minimum cardinality specifies optional/partial participation (zero permitted) or mandatory/total participation (at least one). A maximum of one does not mean participation is compulsory. Relationship degree counts participating entity roles: unary recursive, binary or ternary.FDB09
Read business rules in both directions.FDB09
If each employee belongs to one department and each department has many employees, Employee→Department is N:1; Department→Employee is 1:N.FDB09
Choice by choice
A · incorrect — Optional describes minimum participation (PDF 11.)
B · incorrect — Many-to-many allows multiple (PDF 11.)
C · correct — Both maxima match one-to-one (PDF 11.)
D · incorrect — One-to-many permits multiple on one side (PDF 11.)
Question 36
Which SQL statement sets PRICE to 600 in BOOK for rows whose ID is 'B001'?
A. CHANGE BOOK SET PRICE =600 Where ID ='B001';
B. UPDATE TO CHANGE BOOK SET PRICE =600 Where ID ='B001';
C. UPDATE BOOK SET PRICE =600 Where ID ='B001';
D. GRANT BOOK SET PRICE =600 Where ID ='B001';
Hint / Module Reference (Where to Read)
Track which rows each data-changing command affects.
Correct answer: C — UPDATE BOOK SET PRICE =600 Where ID ='B001';
UPDATE changes existing row values. The table name follows UPDATE, SET supplies the assignment and WHERE restricts the rows. C therefore assigns 600 only where ID equals the quoted string B001. CHANGE is not the row-update command, the extra TO CHANGE words break the required UPDATE structure, and GRANT controls privileges rather than performing a row assignment. These malformed alternatives are definite incorrect selections.
Evidence in the complete study note
UPDATE Book SET Price = 600 WHERE ID = 'B001'; changes matching rows. UPDATE without WHERE targets every row. DELETE FROM Publisher WHERE Name = 'XWZ'; deletes matching publisher rows, subject to reference policies. DELETE FROM r; removes every row but leaves r defined. Both clear relation entries and delete rows describe that effect. Several UPDATE statements can all be valid while performing different tasks; most appropriate requires a requested change and a specified quoting dialect.FDB29
DCL manages privileges through GRANT and REVOKE.FDB26
Choice by choice
A · incorrect — Incorrect: the required row-change statement begins UPDATE, not CHANGE BOOK SET.
B · incorrect — Incorrect: UPDATE must be followed by its target table, not TO CHANGE.
C · correct — Correct: the statement assigns 600 to PRICE for precisely the matching ID rows.
D · incorrect — Incorrect: GRANT manages privileges; it cannot perform this SET assignment.
Question 37
While listing limitation of File Processing System identify the wrong statement?
A. Difficult in accessing data
B. Data isolation and integrity.
C. Data redundancy & inconsistency available.
D. Concurrent access is possible.
Hint / Module Reference (Where to Read)
Review the database approach and match each claimed benefit or cost to its mechanism.
Correct answer: D — Concurrent access is possible.
The file-system limitation is concurrent-access anomalies; merely allowing concurrent access is not itself the listed limitation.
Evidence in the complete study note
A database is a coherent collection of related facts with meaning for an application. A DBMS is software that defines, stores, retrieves, updates, protects and shares that data. The database is the content; the DBMS manages it. A database system also includes applications, people and infrastructure.FDB01
Concurrent access itself is useful; uncontrolled concurrent access causes anomalies.FDB01
Traditional file applications define their own formats and access procedures.FDB01
Choice by choice
A · incorrect — Access difficulty is listed (PDF 5.)
B · incorrect — Isolation and integrity problems are listed (PDF 5.)
C · incorrect — Redundancy and inconsistency are listed (PDF 5.)
D · correct — Possibility of access does not name the anomaly (PDF 5.)
Question 38
The Entity Relationship (E-R) Model
A. Entities are represented by circle
B. Attributes are represented by rectangle
C. It represents conceptual view
D. It corresponds to row
Hint / Module Reference (Where to Read)
Review entity, attribute and relationship notation before interpreting the model.
ER depicts the conceptual database schema and entity rectangles.
Evidence in the complete study note
An entity is a distinguishable real or conceptual thing, such as one student or one course. An entity type describes common properties; its current collection of entities is an entity set. A relationship associates entities. In Chen notation rectangles represent entity types, diamonds relationships and ellipses attributes; double ellipses denote multivalued attributes and dashed ellipses derived attributes. Always inspect the diagram legend.FDB06
Conceptual design turns them into an ER/EER model.FDB05
Logical design maps the conceptual model to relations, keys and constraints and checks normalization.FDB05
Choice by choice
A · incorrect — Entity uses rectangle (PDF 9, 10.)
B · incorrect — Attribute is a property rather than an entity rectangle (PDF 9, 10.)
C · correct — Conceptual depiction matches ER (PDF 9, 10.)
D · incorrect — A model is not one tuple (PDF 9, 10.)
Question 39
Removal of transitivity property should be full filled in
A. First Normal form
B. Fourth Normal form
C. Third Normal form
D. Second Normal form
Hint / Module Reference (Where to Read)
Check atomicity, candidate keys and partial dependencies in that order.
3NF requires nonprime attributes to depend nontransitively on keys after reaching 2NF.
Evidence in the complete study note
2NF requires 1NF and no nonprime attribute partially dependent on any candidate key. For Enrollment(SID,CID,SName,CTitle,Grade), assume key (SID,CID), SID→SName and CID→CTitle. Split into Student(SID,SName), Course(CID,CTitle) and Enrollment(SID,CID,Grade). Grade still depends on the whole pair. A relation whose every candidate key is single-attribute has no nonprime partial-key dependency. Higher forms inherit 2NF, so no partial dependencies establishes the minimum guaranteed stage only when 1NF is also assumed.FDB20
With atomic values the highest form is 1NF, even though a transitive dependency also exists.FDB21
The mnemonic whole key and nothing but the key describes the common single-key case; it cannot replace the full tests when keys overlap.FDB21
Choice by choice
A · incorrect — 1NF requires atomicity (PDF 18.)
B · incorrect — 4NF is not the stated rule (PDF 18.)
The module explicitly multiplies input tuple counts for a Cartesian product (PDF p.28, lines 6–8). Hence 5 rows paired with 7 rows give 5*7 = 35 distinct concatenated tuples, before any filtering/projection.
Evidence in the complete study note
A Cartesian product has |R|×|S| tuples and degree degree(R)+degree(S), renaming conflicting attributes if necessary. Thus 200×100=20,000 and 5×7=35. Join equals selection over a product; intersection can be derived from difference. A common minimal basis contains selection, projection, union, difference, product and rename. Assignment is a useful notation, not an indispensable primitive; lists of basic operations vary across textbooks.FDB35
Choice by choice
A · incorrect — 5+7 is the sum, not all pair combinations (PDF p.28).
B · incorrect — 7-5 is the size difference, not the pair count (PDF p.28).
C · correct — 5*7 gives the 35 combinations required by the product construction (PDF p.28).
D · incorrect — 5/7 is not even an integer tuple count and is not pair enumeration (PDF p.28).
Question 41
The language associated with a database management system that is employed by end
users and programmers to alter data values in the database is the:
A. Data translation language.
B. Data definition language.
C. Data manipulation language.
D. Data presentation language.
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
The language for INSERT, UPDATE and DELETE is DML.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
Choice by choice
A · incorrect — Not a defined language category (PDF 26.)
B · incorrect — DDL defines structure (PDF 26.)
C · correct — DML changes stored data (PDF 26.)
D · incorrect — Not a defined language category (PDF 26.)
Question 42
Using standard SQL string literals, which update changes only the name of the student whose ID is '123' to 'abel'?
A. UPDATE student ID='123';
B. UPDATE student SET ID='123';
C. UPDATE student SET name='abel' WHERE ID='123';
D. UPDATE student SET name='abel';
Hint / Module Reference (Where to Read)
Track which rows each data-changing command affects.
Correct answer: C — C. UPDATE student SET name='abel' WHERE ID='123';
UPDATE needs SET to state the assignment and WHERE to restrict the target rows. C assigns the desired name only to the student with ID '123'. B changes IDs in every row, while D changes every student's name because it has no filter. A omits SET and is malformed. Correct syntax and the requested scope both matter.
Evidence in the complete study note
INSERT INTO Hotel(HotelID,HotelName,SubCity) VALUES (1,'Hilton','Yeka'); pairs each named column with a compatible value. Counts and types must match; keys and other constraints still apply. Without a column list, values must follow the table's declared column order.FDB29
UPDATE Book SET Price = 600 WHERE ID = 'B001'; changes matching rows.FDB29
Use single quotes for SQL string literals: WHERE ID = 'B001'.FDB28
Choice by choice
A · incorrect — SET is missing before the assignment.
B · incorrect — This sets every student's ID and does not change the name.
C · correct — SET changes name and WHERE restricts the operation to ID '123'.
D · incorrect — Without WHERE, every name is changed.
Question 43
A weak entity is identified by its partial key together with the key of an independently keyed associated entity. Which listed terms describe that associated entity set? Select all that apply.
A. Identifying set
B. Strong entity set
C. Neighbor set
D. Owner set
Hint / Module Reference (Where to Read)
Check how a dependent entity obtains its complete identity.
Correct answers (select all): B, D — B. Strong entity set; D. Owner set
A weak entity cannot identify itself using its own attributes alone. Its owner contributes the missing key component, and in the specified case that owner has an independent key, so it is a strong entity set. Both owner set and strong entity set describe it. The identifying relationship connects the sets; neighbor does not identify the required ownership role.
Evidence in the complete study note
A weak entity lacks a complete independent key. Its owner is normally a strong entity, so owner entity set and strong entity set may both describe the associated set. Its partial key, also called discriminator, distinguishes weak entities of the same owner. Identification combines the owner key and partial key.FDB08
The weak entity participates totally in its identifying relationship.FDB08
Chen notation uses a double rectangle, double identifying diamond and dashed underline for a partial key.FDB08
Choice by choice
A · incorrect — Identifying normally names the owner relationship rather than the defined owner entity-set role.
B · correct — The owner supplying the independent key is normally a strong entity set.
C · incorrect — Neighbor does not express the ownership and identification needed for a weak entity.
D · correct — Owner set names the entity set whose key contributes to weak-entity identification.
Question 44
A language that allows the DBA or user to describe and name the entities, attributes, and
relationships required for the application is
A. Transaction control language (TCL)
B. Data control language (DCL)
C. .
Data Manipulation Language (DML)
D. Data Definition Language (DDL)
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
Correct answer: D — Data Definition Language (DDL)
Defining schema objects belongs to DDL.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
Choice by choice
A · incorrect — Transaction control is a different task (PDF 26.)
B · incorrect — Privileges are a different task (PDF 26.)
C · incorrect — Manipulation operates on contents (PDF 26.)
D · correct — DDL supplies definitions (PDF 26.)
Question 45
In relational-table terminology, which listed terms name a unit of related field values treated together by an application? Select all that apply.
A. Rows
B. Field
C. Record
D. Database
Hint / Module Reference (Where to Read)
Distinguish a relation's structure from its current contents and its descriptive metadata.
Correct answers (select all): A, C — A. Rows; C. Record
A relational tuple groups related attribute values into one unit. Row and record are names for that same unit, so both options qualify even though one is plural. A field refers to one property rather than the whole grouping. A database is the larger coherent collection containing tables and their data, rather than one individual tuple.
Evidence in the complete study note
A relation is a set of tuples, represented as a table. A tuple/row/record contains related attribute values; an attribute/column/field describes a property. Degree counts columns; cardinality counts rows. Classical relations have distinct tuples, atomic cells and domains for attributes; row order has no meaning. Column order has no logical meaning when attributes are identified by name. SQL tables can contain duplicate rows unless constrained.FDB03
A database is a coherent collection of related facts with meaning for an application.FDB01
It controls redundancy rather than guaranteeing zero duplication.FDB01
Choice by choice
A · correct — A relational row groups related field values and is synonymous with a record.
B · incorrect — A field represents one attribute, not the collection of related fields in the definition.
C · correct — A record is the unit containing related fields described by the stem.
D · incorrect — A database contains a larger coherent collection, rather than this single field grouping.
Question 46
Which template correctly creates a table with one column? Treat table_name, column_name and column_type as placeholders for valid identifiers and a data type.
A. CREATE table_name (column_name, column type);
B. CREATE table_name (column_type, column name);
C. CREATE TABLE table_name (column_name column_type);
D. CREATE TABLE table_name (column_type, column_name);
Hint / Module Reference (Where to Read)
Distinguish table definition changes from changes to stored rows.
Correct answer: C — C. CREATE TABLE table_name (column_name column_type);
A column declaration places its name immediately before its data type. Commas separate complete declarations when more than one column is present. The corrected C uses CREATE TABLE and a properly paired name and type. A and B omit TABLE, while D separates and reverses the name/type parts rather than declaring the intended column.
Evidence in the complete study note
CREATE TABLE Employee (EmpID INTEGER PRIMARY KEY, Name VARCHAR(80)); declares two columns. The name and its type stay together; the comma separates whole column declarations. To add a primary key later: ALTER TABLE Employee ADD PRIMARY KEY (EmpID); existing data must be unique and non-NULL. A table cannot gain a second primary key without replacing the existing constraint.FDB30
Choice by choice
A · incorrect — TABLE is missing and the name/type pair is split by a comma.
B · incorrect — TABLE is missing and the type/name order is wrong.
C · correct — CREATE TABLE is followed by one correctly ordered name/type declaration.
D · incorrect — A comma separates type from name and no valid intended declaration is formed.
Question 47
From the following alternatives which one is FALSE about database system?
A. " I is very difficult to protect a file under the system
B. * iy database system the user is not required to write the procedures
C. « tn database system, DBMS provides a good protection mechanism.
D. ° i contains a wide variety of sophisticated techniques to store and retrieve the data.
Hint / Module Reference (Where to Read)
Review the database approach and match each claimed benefit or cost to its mechanism.
Correct answer: A — " I is very difficult to protect a file under the system
DBMS permission and authentication support contradicts the claim of poor protection; B is interpreted as nonprocedural querying rather than no application code ever.
Evidence in the complete study note
A database is a coherent collection of related facts with meaning for an application. A DBMS is software that defines, stores, retrieves, updates, protects and shares that data. The database is the content; the DBMS manages it. A database system also includes applications, people and infrastructure.FDB01
B/B+ trees support ordered and range search; hashing targets equality; bitmap indexes suit some analytic workloads.FDB25
Complexity, administration, storage and licensing can outweigh benefits for a tiny fixed application; lightweight embedded DBMSs also exist.FDB01
Choice by choice
A · correct — Contradicts described protection (PDF 53.)
B · incorrect — Nonprocedural retrieval is supported (PDF 53.)
C · incorrect — Matches security support (PDF 53.)
D · incorrect — Storage and indexes support retrieval (PDF 53.)
Question 48
Consider a directed line(->) from the relationship set advisor to both entity sets instructor
and student. This indicates cardinality
A. Many to one
B. Many to many
C. ¢.
One to one
D. One to many
Hint / Module Reference (Where to Read)
Read the relationship in both directions and check participation constraints.
Correct answer: C — One-to-one, under the common relationship-arrow convention.
FDB09 explicitly states the convention used for this example: arrows from advisor toward both Instructor and Student impose maximum-one at both ends. Each instructor can therefore be associated with at most one student and each student with at most one instructor. That is 1:1, without implying mandatory participation. A different notation would need an explicit legend.
Evidence in the complete study note
Read business rules in both directions. If each employee belongs to one department and each department has many employees, Employee→Department is N:1; Department→Employee is 1:N. Building→Location could also be N:1, so names alone cannot select a unique example. Under the common arrow convention, arrows from advisor to both Instructor and Student impose maximum-one at both ends, giving 1:1. Without a notation convention, arrows are unsafe evidence.FDB09
Choice by choice
A · incorrect — Many-to-one constrains only one end to maximum one, whereas both ends have arrows here.
B · incorrect — Many-to-many permits multiple associations at both ends, contrary to both maximum-one arrows.
C · correct — Maximum one at each end yields a one-to-one relationship under the stated common convention.
D · incorrect — One-to-many allows multiple associations at one end, which the second arrow rules out.
Question 49
What is the basic goal of Normalization?
A. To decrease data integrity
B. To reduce redundancy
C. ¢.
To maximize transitive dependency
D. To increase dependency
Hint / Module Reference (Where to Read)
Distinguish dependency-based design problems from the contents of one sample table.
Normalization reduces repeated data and resulting anomalies.
Evidence in the complete study note
Suppose Enrollment repeats StudentName for every course. An update anomaly requires changing every copy of the student's name; missed copies disagree. An insertion anomaly prevents recording a new student until some enrollment exists. A deletion anomaly loses the only record of a student's details when their last enrollment is deleted. Revoke anomaly is not a normalization anomaly.FDB19
Reduced redundancy can improve integrity and storage use, but extra keys and joins mean neither storage savings nor faster retrieval is guaranteed.FDB19
Adding a generated row ID may make insertion technically possible, but does not remove repeated facts or fix the dependency design.FDB19
Choice by choice
A · incorrect — Integrity is protected (PDF 16.)
B · correct — Redundancy reduction is explicit (PDF 16.)
C · incorrect — 3NF removes transitive dependence (PDF 16.)
D · incorrect — Increasing dependency is not the goal (PDF 16.)
Question 50
For a relation with attributes ID, CITY and NAME, ID is guaranteed unique and non-NULL; CITY and NAME are not unique. Which listed attribute sets are superkeys? Select all that apply.
A. NAME
B. ID
C. CITY
D. CITY, ID
Hint / Module Reference (Where to Read)
Test uniqueness and minimality separately when classifying keys.
Correct answers (select all): B, D — B. ID; D. CITY, ID
A superkey is any attribute set guaranteed to identify a tuple; it need not be minimal. ID has the stated uniqueness guarantee, and adding CITY cannot destroy it. Thus ID and (CITY, ID) are superkeys. CITY and NAME individually lack uniqueness guarantees. Only ID is a minimal candidate key among the two qualifying choices, but minimality is not required here.
Evidence in the complete study note
A superkey uniquely identifies a tuple. A candidate key is a minimal superkey: removing any component loses uniqueness. Minimal means no unnecessary attribute, not necessarily the fewest columns among all keys. A primary key is one selected candidate key; remaining candidates are alternate keys. A composite key contains multiple attributes and need not be the chosen primary key.FDB07
If ID is unique, both {ID} and {ID,City} are superkeys, but only {ID} is minimal.FDB07
Prefer stable, compact identifiers when practical, but Email, SSN or EmployeeID is a candidate only if business rules guarantee uniqueness.FDB07
Choice by choice
A · incorrect — Repeated names are permitted, so NAME is not a guaranteed identifier.
B · correct — ID is guaranteed unique and identifies each tuple.
C · incorrect — Repeated cities are permitted, so CITY alone is not a superkey.
D · correct — The set includes unique ID and therefore retains uniqueness.
Question 51
is one of the candidate keys chosen by the database designer to uniquely
identify the entity set.
A. Super key
B. Candidate key
C. Social security key
D. Primary key
Hint / Module Reference (Where to Read)
Test uniqueness and minimality separately when classifying keys.
A primary key is the candidate selected by the designer.
Evidence in the complete study note
A superkey uniquely identifies a tuple. A candidate key is a minimal superkey: removing any component loses uniqueness. Minimal means no unnecessary attribute, not necessarily the fewest columns among all keys. A primary key is one selected candidate key; remaining candidates are alternate keys. A composite key contains multiple attributes and need not be the chosen primary key.FDB07
Choice by choice
A · incorrect — Superkey need not be minimal (PDF 11.)
B · incorrect — Candidate need not be selected (PDF 11.)
C · incorrect — Example identifier is not this role (PDF 11.)
D · correct — Exact selected-key definition (PDF 11.)
Question 52
12.
is a collection of concepts thatcan be used to describe the
structure of a database.
A. a | Data Architecture
B. i? | Data Model
C. oO Data client
D. | All of the above
Hint / Module Reference (Where to Read)
Compare the data models by how they organize records and relationships.
The defined collection of concepts describing database structure is a data model.
Evidence in the complete study note
A data model supplies concepts for structure, relationships, constraints and operations. Conceptual ER models describe entities and relationships. Relational models use tables; hierarchical models organize records in a parent–child tree; classic network models connect records through pointer-based owner/member links, allowing multiple parents. Object models represent objects with identity, state and behavior. A physical model specifies record layouts and access paths. These are different representation choices, not stages of one query.FDB02
Choice by choice
A · incorrect — Architecture is not the defined term (PDF 7.)
B · correct — Exact definition (PDF 7.)
C · incorrect — Client is an application role (PDF 7.)
D · incorrect — Other choices do not match (PDF 7.)
Question 53
The description of a database is called
A. a | Database snapshot
B. i? | Database instances
C. @) Database schema
D. | All of the above
Hint / Module Reference (Where to Read)
Distinguish a relation's structure from its current contents and its descriptive metadata.
Schema describes the designed database; instance and snapshot describe current data.
Evidence in the complete study note
A schema describes names, structures and constraints; an instance, state or snapshot is the data at a particular time. Inserting a student changes the state, while adding a column changes the schema. Metadata is data describing data. The catalog or data dictionary records table definitions, types, constraints and often access privileges and optimizer statistics. A schema diagram draws tables and key references; labels such as database diagram can overlap.FDB03
Choice by choice
A · incorrect — Current contents (PDF 7.)
B · incorrect — Current occurrences (PDF 7.)
C · correct — Designed structure (PDF 7.)
D · incorrect — Other choices are different concepts (PDF 7.)
Question 54
Which listed terms denote database contents at one particular moment, rather than the database definition? Select all that apply.
A. Database instance
B. Database state / snapshot
C. Database schema
D. None of the above
Hint / Module Reference (Where to Read)
Distinguish a relation's structure from its current contents and its descriptive metadata.
Correct answers (select all): A, B — A. Database instance; B. Database state / snapshot
An instance, state or snapshot describes the actual data at a selected time. Those contents can change after an insertion or update without changing the database definition. A schema describes names, structures and constraints instead. Therefore both the instance option and the state/snapshot option fit, while none of the above cannot be selected because these matching terms are present.
Evidence in the complete study note
A schema describes names, structures and constraints; an instance, state or snapshot is the data at a particular time. Inserting a student changes the state, while adding a column changes the schema. Metadata is data describing data. The catalog or data dictionary records table definitions, types, constraints and often access privileges and optimizer statistics. A schema diagram draws tables and key references; labels such as database diagram can overlap.FDB03
Choice by choice
A · correct — A database instance denotes its data at a particular time.
B · correct — State and snapshot are synonyms for the same time-specific database contents.
C · incorrect — A schema describes structure and constraints rather than current data values.
D · incorrect — Both instance and state/snapshot are provided and correctly fit the definition.
Question 55
What is the capacity to change the conceptual schema while preserving affected external schemas and application interfaces?
A. Logical data independence
B. Physical data independence
C. Conceptual database
D. All of the above
Hint / Module Reference (Where to Read)
Trace which schema levels a proposed change affects.
Logical data independence allows changes to the conceptual schema while preserving the external views and application interfaces that depend on it. Physical data independence instead concerns changing internal storage without changing the conceptual schema. A conceptual database or schema describes the logical structure itself; naming that structure does not express the ability to preserve external interfaces during a change. Consequently A names the requested property and the all-of-the-above choice is false.
Evidence in the complete study note
The external level gives each user group a selected view. The conceptual level describes the whole logical database: entity types, relationships, data types and constraints.FDB04
Physical data independence permits changing internal storage without changing the conceptual schema, for example adding an index. Logical data independence permits conceptual changes while preserving affected external views and application interfaces, for example splitting a table while maintaining a compatible view.FDB04
Choice by choice
A · correct — Correct: logical data independence preserves external interfaces across conceptual-schema changes.
B · incorrect — Incorrect: physical data independence concerns internal-storage changes while preserving the conceptual schema.
C · incorrect — Incorrect: conceptual database names the logical structure, not the property preserving external interfaces when that structure changes.
D · incorrect — Incorrect: physical independence and the conceptual database are not the requested change-preservation property.
Question 56
is language used to define the database conceptual schema
A. a | Data Manipulation Language (DML) x
B. ? | Data Definition Langugae (DDL) v
C. /@| Transaction Query Language (TQL) x
D. | None of the above x
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
Correct answer: B — ? | Data Definition Langugae (DDL) v
Defining a schema is definition rather than manipulation. DDL commands define structure, while DML commands manipulate data.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
Choice by choice
A · incorrect — DML covers retrieval and updates, not schema definition. (PDF pp. 26.)
B · correct — DDL is the definition-language category. (PDF pp. 26.)
C · incorrect — TQL is not the module's schema language. (PDF pp. 26.)
D · incorrect — The definition-language choice is present. (PDF pp. 26.)
Question 57
Common functions applied to collections of numeric values: SUM,
AVERAGE, COUNT. This function belongs to
A. Ww Grouping x
B. i? | Aggregate functions 4
C. |@| All of the above x
D. | None of the above ><
Hint / Module Reference (Where to Read)
Apply grouping before filtering groups and computing aggregate output.
SUM, average and COUNT summarize collections into values, which is aggregation. GROUP BY instead partitions rows into groups for those summaries. SQL normally spells the average function AVG; the conceptual use of AVERAGE here still describes aggregation.
Evidence in the complete study note
COUNT(*) counts rows, COUNT(x) counts non-NULL x values; SUM and AVG summarize non-NULL numeric values. SQL's usual average function is AVG, not AVERAGE. GROUP BY creates one group per distinct grouping value; HAVING tests aggregate groups after WHERE has filtered individual rows.FDB32
Choice by choice
A · incorrect — Grouping partitions rows; it is not the function category containing SUM and COUNT.
B · correct — These functions summarize a collection of values into a count, total or average.
C · incorrect — Grouping and aggregate functions are distinct concepts, so all listed categories cannot be correct.
D · incorrect — Aggregate functions is present and correctly describes the functions in the stem.
Question 58
Entity types that do not have key attributes of their own are called
A. a | Entity
B. i? | Strong Entity
C. @) Weak Entity
D. | Foreign Entity
Hint / Module Reference (Where to Read)
Check how a dependent entity obtains its complete identity.
Weak entities lack their own key attributes, while strong entities have their own keys. Their partial keys identify them only within an owner.
Evidence in the complete study note
A weak entity lacks a complete independent key. Its owner is normally a strong entity, so owner entity set and strong entity set may both describe the associated set. Its partial key, also called discriminator, distinguishes weak entities of the same owner. Identification combines the owner key and partial key.FDB08
A weak entity is not merely any table with a foreign key.FDB08
Then (EmployeeID,DependentName) is the key, and EmployeeID is a foreign key.FDB08
Choice by choice
A · incorrect — Entity is the broader category and does not identify the missing-key subtype. (PDF pp. 10.)
B · incorrect — Strong entities have their own key, the opposite condition. (PDF pp. 10.)
C · correct — Weak entity matches the exact definition. (PDF pp. 10.)
D · incorrect — Foreign entity is not the defined subtype; foreign keys concern table references. (PDF pp. 10.)
Question 59
The partial key is sometimes called
A. a | Dominant Entity
B. i? | Child Entity Type
C. @) Subordinate Entity Type
D. | Discriminator
Hint / Module Reference (Where to Read)
Check how a dependent entity obtains its complete identity.
A partial key distinguishes weak entities belonging to the same owner; the owner key is still needed for global identification. FDB08 explicitly calls that partial key a discriminator. The other choices describe entity roles or types rather than the identifying attribute.
Evidence in the complete study note
A weak entity lacks a complete independent key. Its owner is normally a strong entity, so owner entity set and strong entity set may both describe the associated set. Its partial key, also called discriminator, distinguishes weak entities of the same owner. Identification combines the owner key and partial key.FDB08
The weak entity participates totally in its identifying relationship.FDB08
Chen notation uses a double rectangle, double identifying diamond and dashed underline for a partial key.FDB08
Choice by choice
A · incorrect — An entity role is not the name of the identifying partial-key attribute.
B · incorrect — A child entity type describes a type of entity rather than its partial identifier.
C · incorrect — A subordinate entity type is not a synonym for a partial key.
D · correct — Discriminator is the partial key that distinguishes weak entities within the same owner.
Question 60
A real-world physical or logical object is called:
A. Attributes
B. Relationship
C. Entity
D. All of the above
Hint / Module Reference (Where to Read)
Review entity, attribute and relationship notation before interpreting the model.
An entity may have physical existence or conceptual existence. Attributes describe an entity; relationships associate entity types. The stem asks for the object itself.
Evidence in the complete study note
An entity is a distinguishable real or conceptual thing, such as one student or one course. An entity type describes common properties; its current collection of entities is an entity set. A relationship associates entities. In Chen notation rectangles represent entity types, diamonds relationships and ellipses attributes; double ellipses denote multivalued attributes and dashed ellipses derived attributes. Always inspect the diagram legend.FDB06
Choice by choice
A · incorrect — An attribute is a property of the object. (PDF pp. 10, 11.)
B · incorrect — A relationship associates objects rather than naming the object. (PDF pp. 10, 11.)
C · correct — Entity covers both physical and conceptual objects. (PDF pp. 10, 11.)
D · incorrect — The other categories have different roles, so all is false. (PDF pp. 10, 11.)
Question 61
In the usual normalization sequence, which is the first normal form that excludes partial dependencies of nonprime attributes on any candidate key?
A. First Normal Form (1NF)
B. Third Normal Form (3NF)
C. Second Normal Form (2NF)
D. Boyce–Codd Normal Form (BCNF)
Hint / Module Reference (Where to Read)
Check atomicity, candidate keys and partial dependencies in that order.
Second normal form is the first stage requiring atomic relations to have no nonprime attribute partially dependent on any candidate key. First normal form addresses atomic values, so it does not impose that dependency rule. Third normal form and BCNF are stronger stages that inherit the absence of partial dependencies, but they are not the first stage introducing it. BCNF specifically requires every nontrivial functional-dependency determinant to be a superkey.
Evidence in the complete study note
1NF requires atomic values in the chosen relational domains and no repeating groups in cells.FDB20
2NF requires 1NF and no nonprime attribute partially dependent on any candidate key.FDB20
For every nontrivial FD X→A, 3NF permits it when X is a superkey or A is prime. BCNF requires X to be a superkey. The mnemonic whole key and nothing but the key describes the common single-key case; it cannot replace the full tests when keys overlap. Remove dependencies of nonprime facts on nonkey determinants.FDB21
Choice by choice
A · incorrect — Incorrect: 1NF requires atomic values but does not exclude partial dependencies.
B · incorrect — Incorrect: 3NF inherits 2NF and imposes its additional determinant/prime-attribute condition; it is not the first stage.
C · correct — Correct: 2NF is the first stage excluding nonprime partial dependencies on candidate keys.
D · incorrect — Incorrect: BCNF requires every nontrivial determinant to be a superkey; it is stronger than the first stage imposing the partial-dependency rule.
Question 62
In the usual normalization sequence, which is the first normal form that excludes transitive dependence of nonprime attributes on a key through a nonkey determinant?
A. Third Normal Form (3NF)
B. Fourth Normal Form (4NF)
C. First Normal Form (1NF)
D. All of the above
Hint / Module Reference (Where to Read)
Check atomicity, candidate keys and partial dependencies in that order.
In the stated single-key teaching case, 2NF excludes partial dependencies but can still allow a nonprime fact to depend on the key through a nonkey determinant. The next defining stage is 3NF, summarized by the whole-key-and-nothing-but-the-key mnemonic. BCNF strengthens the determinant rule, and 4NF addresses multivalued dependencies. Stronger forms inherit earlier conditions, so the explicit first-stage scope makes 3NF the unique selection rather than treating inherited properties as rival answers.
Evidence in the complete study note
1NF requires atomic values in the chosen relational domains and no repeating groups in cells.FDB20
2NF requires 1NF and no nonprime attribute partially dependent on any candidate key.FDB20
For every nontrivial FD X→A, 3NF permits it when X is a superkey or A is prime. BCNF requires X to be a superkey. The mnemonic whole key and nothing but the key describes the common single-key case; it cannot replace the full tests when keys overlap. Remove dependencies of nonprime facts on nonkey determinants.FDB21
A · correct — Correct: 3NF is the first stage imposing the specified nonprime transitive-dependency restriction in this teaching case.
B · incorrect — Incorrect: 4NF addresses multivalued dependencies and is a later stage; it is not the first stage imposing the 3NF condition.
C · incorrect — Incorrect: 1NF concerns atomic values and does not eliminate the specified transitive dependencies.
D · incorrect — Incorrect: Not all options identify the requested first stage: 1NF does not eliminate the specified transitive dependency and 4NF is later.
Question 63
A conceptual level refers to:
A. Describes the represents global view of the entire database
B. Describes the part of the database that a particular user group views of the
data environment
C. Describes how data is actually stored on disk
D. Describes the physical storage structure of the database
Hint / Module Reference (Where to Read)
Trace which schema levels a proposed change affects.
Correct answer: A — Describes the represents global view of the entire database
The conceptual schema describes the whole database while hiding storage details. External views show a particular group's part, and internal schema describes storage.
Evidence in the complete study note
The external level gives each user group a selected view. The conceptual level describes the whole logical database: entity types, relationships, data types and constraints. The internal/physical level describes files, record placement and access paths. These are abstraction levels, distinct from client/server deployment tiers.FDB04
Choice by choice
A · correct — Whole-database structure is conceptual. (PDF pp. 7, 8.)
B · incorrect — A user-group subset is external. (PDF pp. 7, 8.)
C · incorrect — Actual storage is internal. (PDF pp. 7, 8.)
D · incorrect — Physical storage structure is internal. (PDF pp. 7, 8.)
Question 64
Suppose you want to design a database for a certain company after you
know the need of all users of the database. What is the next step you are
expected to do?
A. Logical design
B. Normalization
C. Query Processing
D. Conceptual design
Hint / Module Reference (Where to Read)
Identify the design phase from its inputs and intended deliverable.
After collecting users' information requirements, conceptual design develops the ER/EER model that represents those needs. Logical design then maps that model to relations, keys and constraints and checks normalization. Normalization refines dependency structure rather than replacing the initial conceptual modeling step. Query processing evaluates database requests instead of defining the initial enterprise model. D is therefore the next step under the stated sequence, rather than a claim that design can never iterate.
Evidence in the complete study note
First collect business facts, required transactions, reports, volumes, privacy rules and quality targets. Conceptual design turns them into an ER/EER model. Logical design maps the conceptual model to relations, keys and constraints and checks normalization. Physical design chooses file organization, indexes and storage. Implementation creates and loads the actual objects; testing checks requirements.FDB05
Choice by choice
A · incorrect — Incorrect: logical design maps an already developed conceptual model into relations and constraints.
B · incorrect — Incorrect: normalization refines relation dependencies during logical design; the initial conceptual model must first be developed.
C · incorrect — Incorrect: query processing evaluates requests rather than creating the initial model from requirements.
D · correct — Correct: conceptual design follows the collection of requirements in the stated sequence.
Question 65
All are true about the Database except:
A. Program data independence.
B. Self-describing nature
C. Sharing of data and multi-user transaction processing
D. Support of single views of the data
Hint / Module Reference (Where to Read)
Review the database approach and match each claimed benefit or cost to its mechanism.
Correct answer: D — Support of single views of the data
The listed characteristics include insulation between data and programs, self-description, sharing, and multiple views. The exception is single views, interpreted as support restricted to one view; a DBMS can of course also display a single view.
Evidence in the complete study note
A database is a coherent collection of related facts with meaning for an application. A DBMS is software that defines, stores, retrieves, updates, protects and shares that data. The database is the content; the DBMS manages it. A database system also includes applications, people and infrastructure.FDB01
A DBMS centralizes metadata and supports multiple views, authorization, transactions, backup and recovery.FDB01
The external level gives each user group a selected view.FDB04
Choice by choice
A · incorrect — Program/data insulation is expressly a characteristic. (PDF pp. 5, 6.)
B · incorrect — Self-description is expressly listed. (PDF pp. 5, 6.)
C · incorrect — Sharing and multiuser transactions are expressly listed. (PDF pp. 5, 6.)
D · correct — The stated characteristic is multiple views, rather than restriction to a single view. (PDF pp. 5, 6.)
Question 66
Which one of the following provides the ability to query information from the database and to insert tuples into, delete tuples from, and modify tuples in the database?
A. DML(Data Manipulation Langauge)
B. DDL(Data Definition Langauge)
C. Query
D. Relational Schema
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
Correct answer: A — DML(Data Manipulation Langauge)
DML includes SELECT, INSERT, DELETE, UPDATE and therefore covers both retrieval and tuple changes. The second list is misprinted as Basic DDL commands but sits under the DML heading; the heading and separation resolve that typo.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
Choice by choice
A · correct — DML is the category covering all named operations. (PDF pp. 26.)
B · incorrect — DDL describes structural commands. (PDF pp. 26.)
C · incorrect — A query is a request, not the complete language category for all modifications. (PDF pp. 26.)
D · incorrect — A relational schema describes structure rather than performing operations. (PDF pp. 26.)
Question 67
Which command is used to modifies an existing database object, such as a table?
A. Create
B. Alter
C. Drop
D. Modify
Hint / Module Reference (Where to Read)
Distinguish table definition changes from changes to stored rows.
ALTER changes the definition of an existing database object. FDB30 demonstrates adding or removing columns and adding constraints with ALTER TABLE. CREATE establishes an object and DROP removes it. MODIFY can occur within some vendor-specific alteration syntax, but it is not the general top-level command requested here.
Evidence in the complete study note
ALTER TABLE Employee ADD COLUMN Email VARCHAR(120); adds a column in PostgreSQL-style syntax. ALTER TABLE Employee DROP COLUMN Email; removes it. ALTER TABLE Employee ADD CONSTRAINT uq_email UNIQUE (Email); adds a named constraint. Use ALTER rather than DALTER, and do not separate column_name and column_type with a comma; validate the resulting SQL before execution.FDB30
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications.FDB26
Some texts separate SELECT as DQL.FDB26
Choice by choice
A · incorrect — CREATE defines a new object rather than altering the definition of an existing one.
B · correct — ALTER is the command used to change an existing database object definition.
C · incorrect — DROP removes the object instead of modifying its existing definition.
D · incorrect — MODIFY is not the general standalone command; some dialects use it inside ALTER syntax.
Question 68
Which command is used to modifies records?
A. Insert
B. Update
C. Delete
D. Modify
Hint / Module Reference (Where to Read)
Track which rows each data-changing command affects.
The module puts UPDATE in DML (PDF p.26). Its UPDATE(Salary) privilege example explicitly permits the Finance user to change Salary values (PDF pp.54–55), grounding UPDATE as the command for modifying existing record values.
Evidence in the complete study note
INSERT INTO Hotel(HotelID,HotelName,SubCity) VALUES (1,'Hilton','Yeka'); pairs each named column with a compatible value. Counts and types must match; keys and other constraints still apply. Without a column list, values must follow the table's declared column order.FDB29
UPDATE without WHERE targets every row.FDB29
DELETE FROM r; removes every row but leaves r defined.FDB29
Choice by choice
A · incorrect — INSERT is a separate DML operation, illustrated as adding VALUES rows on PDF p.77.
B · correct — UPDATE is explicitly tied to changing Salary values on PDF p.55.
C · incorrect — DELETE is a separate operation illustrated with a row predicate on PDF p.77.
D · incorrect — MODIFY is not the command used for the demonstrated change-value privilege on PDF pp.54–55.
Question 69
In the SQLite command-line shell, which command lists the names and files of attached databases?
A. db
B. use
C. .databases
D. database
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
The command belongs to the SQLite shell rather than ordinary SQL statement syntax. Its documented spelling is .databases, including the leading dot and final s, and it lists attached database names and files. The corrected C has that spelling. The alternatives db, use and database do not form this SQLite attached-database listing command.
Evidence in the complete study note
DELETE removes rows and keeps the table; DROP TABLE removes the table object; ALTER changes its definition. TRUNCATE empties a table using implementation-specific mechanisms. PostgreSQL allows transactional rollback of TRUNCATE, so no rollback information is retained is not a universal SQL property. SQL Server permits DROP DATABASE Sales, suppliers; that comma-list syntax is vendor-specific. SQLite's .databases lists attached names/files in its command-line shell; .database is not the documented command.FDB26
Choice by choice
A · incorrect — db is not the SQLite shell listing command.
B · incorrect — use does not list SQLite attached names and files.
C · correct — The .databases command performs the requested listing.
D · incorrect — database lacks the required shell-command spelling.
Question 70
What is an RDBMS?
A. Database that stores data elements that are not linked
B. Database that accesses data elements that are not linked
C. Database that stores and allows access to data elements that are linked
D. None of the mentioned
Hint / Module Reference (Where to Read)
Compare the data models by how they organize records and relationships.
Correct answer: C — Database that stores and allows access to data elements that are linked
A relational DBMS manages a database represented by relations/tables, with references maintaining relationships between tuples. Among these broad descriptions C is closest, although linked data alone is not a complete definition distinguishing relational from other models.
Evidence in the complete study note
A data model supplies concepts for structure, relationships, constraints and operations. Conceptual ER models describe entities and relationships. Relational models use tables; hierarchical models organize records in a parent–child tree; classic network models connect records through pointer-based owner/member links, allowing multiple parents. Object models represent objects with identity, state and behavior. A physical model specifies record layouts and access paths. These are different representation choices, not stages of one query.FDB02
A relation is a set of tuples, represented as a table.FDB03
Classical relations have distinct tuples, atomic cells and domains for attributes; row order has no meaning.FDB03
Choice by choice
A · incorrect — A description excluding links omits the relationships modeled by references. (PDF pp. 5, 13, 14.)
B · incorrect — Accessing only unlinked elements also omits those relationships. (PDF pp. 5, 13, 14.)
C · correct — Storage and access to related elements agrees with tables and relational constraints. (PDF pp. 5, 13, 14.)
D · incorrect — C supplies the compatible broad description, though it is not a formal definition. (PDF pp. 5, 13, 14.)
Question 71
What is a relation in RDBMS?
A. Key
B. Table
C. Row
D. Data Types
Hint / Module Reference (Where to Read)
Distinguish a relation's structure from its current contents and its descriptive metadata.
The relational model represents relations in table format. A tuple is one row; a key identifies tuples rather than being the relation itself.
Evidence in the complete study note
A relation is a set of tuples, represented as a table. A tuple/row/record contains related attribute values; an attribute/column/field describes a property. Degree counts columns; cardinality counts rows. Classical relations have distinct tuples, atomic cells and domains for attributes; row order has no meaning. Column order has no logical meaning when attributes are identified by name. SQL tables can contain duplicate rows unless constrained.FDB03
A superkey uniquely identifies a tuple.FDB07
A primary key is one selected candidate key; remaining candidates are alternate keys.FDB07
Choice by choice
A · incorrect — A key is an identifying attribute set. (PDF pp. 13.)
B · correct — A relation is represented as a table. (PDF pp. 13.)
C · incorrect — A row is a tuple within the relation. (PDF pp. 13.)
D · incorrect — Data types constrain values rather than constituting a relation. (PDF pp. 13.)
Question 72
Relational Algebra is a __________ query language that takes two relations as input and produces another relation as an output of the query.
A. Relational
B. Structural
C. Procedural
D. Fundamental
Hint / Module Reference (Where to Read)
Distinguish a declarative condition from a prescribed evaluation procedure.
Relational algebra is procedural because it specifies a sequence of operations. The stem overgeneralizes arity: operations can take one or more input relations, not always two.
Evidence in the complete study note
Relational algebra specifies a sequence of operations and is procedural at the logical level. Relational calculus specifies a predicate describing desired results and is declarative/nonprocedural. A tuple variable ranges over tuples; a domain variable ranges over individual attribute values. Safe/range-restricted expressions produce results bounded by the database's active domain.FDB36
Rename ρ changes relation or attribute names.FDB35
Choice by choice
A · incorrect — Relational describes the model but misses the procedural/calculus distinction. (PDF pp. 24, 24.)
B · incorrect — Structural is not the stated classification. (PDF pp. 24, 24.)
C · correct — Procedural is expressly justified by the required sequence. (PDF pp. 24, 24.)
D · incorrect — Fundamental is not the procedural classification used in the definition. (PDF pp. 24, 24.)
Question 73
Which of the following is a fundamental operation in relational algebra?
A. Set intersection
B. Natural join
C. Assignment
D. None of the mentioned
Hint / Module Reference (Where to Read)
Use the operator convention stated in the question and distinguish membership from derivability.
Under the stated Basic operations list, intersection is basic. A smaller primitive basis may instead treat intersection as derived; the answer uses the stated list.
Evidence in the complete study note
A list of basic relational-algebra operators is a teaching convention, distinct from a claim that every listed operation is logically indispensable. The course module uses selection, projection, Cartesian product, set difference, union and intersection as its basic-operation list. Natural join and assignment are not members of that particular list.FDB-S01
A smaller primitive basis may therefore omit intersection even when it is introduced as a basic operation.FDB-S01
Under set semantics, union includes tuples from either compatible input, intersection retains tuples common to both, and difference keeps first-input tuples absent from the second.FDB-S01
Choice by choice
A · correct — Intersection is explicitly under Basic operations. (PDF pp. 24.)
B · incorrect — Natural join is absent from this basic list. (PDF pp. 24.)
C · incorrect — Assignment is absent from this basic list. (PDF pp. 24.)
D · incorrect — False because intersection is listed as basic. (PDF pp. 24.)
Question 74
Which of the following is used to denote the selection operation in relational algebra?
A. Pi (Greek)
B. Sigma (Greek)
C. Lambda (Greek)
D. Omega (Greek)
Hint / Module Reference (Where to Read)
Apply each algebra operator to the input relations step by step.
Selection filters tuples and is conventionally denoted by sigma, σ. Projection keeps attributes and is denoted by pi, π. Lambda and omega are different Greek symbols and therefore cannot identify the requested sigma operation under this notation. This is a question about the operator's conventional symbol, rather than whether another symbol could be assigned a new meaning in a custom notation. B is the unique selection symbol among the choices.
Evidence in the complete study note
Selection σ filters tuples; projection π retains attributes and removes duplicate tuples under set semantics.FDB35
Choice by choice
A · incorrect — Incorrect: pi, π, denotes projection, which retains attributes.
B · correct — Correct: sigma, σ, denotes selection, which filters tuples.
C · incorrect — Incorrect: lambda, λ, is a different symbol from the conventional selection symbol σ.
D · incorrect — Incorrect: omega, ω, is a different symbol from the conventional selection symbol σ.
Question 75
For union-compatible relations, which listed names denote the minus operation R−S, returning tuples in R but not S? Select all that apply.
A. Union
B. Set-difference
C. Difference
D. Intersection
Hint / Module Reference (Where to Read)
Use the operator convention stated in the question and distinguish membership from derivability.
Correct answers (select all): B, C — B. Set-difference; C. Difference
R−S tests membership asymmetrically: a tuple must occur in R and must not occur in S. Difference and set-difference are names for this same operator, so both should be selected. Union combines membership from either input, whereas intersection requires membership in both. Swapping the inputs to difference can change the result because the exclusion direction matters.
Evidence in the complete study note
Under set semantics, union includes tuples from either compatible input, intersection retains tuples common to both, and difference keeps first-input tuples absent from the second. Intersection can be expressed using difference, while a matching join can be expressed as selection over a Cartesian product. A smaller primitive basis may therefore omit intersection even when it is introduced as a basic operation.FDB-S01
Union, difference and intersection require union-compatible schemas.FDB35
Rename ρ changes relation or attribute names.FDB35
Choice by choice
A · incorrect — Union combines compatible input tuples rather than retaining only first-input tuples absent from the second.
B · correct — Set-difference returns tuples in the first relation that do not occur in the second.
C · correct — Difference is the same minus operator as set-difference.
D · incorrect — Intersection keeps tuples common to both relations, the opposite membership condition.
Question 76
Which one of the following is used to define the structure of the relation, deleting relations and relating schemas?
A. DML(Data Manipulation Langauge)
B. DDL(Data Definition Langauge)
C. Query
D. Relational Schema
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
Defining and deleting relation structures are schema operations in DDL. DML operates on their data. Relating schemas here is interpreted as defining schema structure rather than retrieving joins.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
Choice by choice
A · incorrect — DML is for data operations. (PDF pp. 26.)
B · correct — DDL is the schema-definition category. (PDF pp. 26.)
C · incorrect — A query request is not the schema language category. (PDF pp. 26.)
D · incorrect — A schema is a description, not a language for changing it. (PDF pp. 26.)
Question 77
CREATE TABLE employee (name VARCHAR(80), id INTEGER); What kind of SQL statement is this?
A. DML
B. DDL
C. View
D. Integrity constraint
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
CREATE TABLE defines a new table and its columns, so it is a data-definition-language statement. DML instead retrieves or changes stored rows. A view is a query-based logical object, while this statement creates a base table. Integrity constraints can be included inside a table definition, but this whole statement is not merely a constraint expression. VARCHAR has the stated length in this table definition.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
CREATE TABLE Employee (EmpID INTEGER PRIMARY KEY, Name VARCHAR(80)); declares two columns. The name and its type stay together; the comma separates whole column declarations.FDB30
Choice by choice
A · incorrect — Incorrect: DML acts on stored data, whereas this statement defines a table.
B · correct — Correct: creating a table and declaring its columns is DDL.
C · incorrect — Incorrect: CREATE TABLE defines a table rather than a view.
D · incorrect — Incorrect: the statement defines an entire table, not just an integrity-constraint expression.
Question 78
SELECT * FROM employee
What type of statement is this?
A. DML
B. DDL
C. View
D. Integrity constraint
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
SELECT is in the list under the DML heading. The line's repeated DDL word is a typographical error in that DML subsection, as shown by the preceding separate DDL list.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
CREATE VIEW CSStudent AS SELECT StudentID,Name FROM Student WHERE Major = 'CS'; stores a query definition and presents a virtual table.FDB33
An ordinary view does not necessarily store its result; a materialized view does.FDB33
Choice by choice
A · correct — SELECT is classified under DML. (PDF pp. 26.)
B · incorrect — SELECT is not in the preceding DDL list. (PDF pp. 26.)
C · incorrect — No view is defined by this standalone retrieval. (PDF pp. 26.)
D · incorrect — The retrieval is not an integrity constraint. (PDF pp. 26.)
Question 79
The basic data type char(n) is a _____ length character string and varchar(n) is _____ length character.
A. Fixed, equal
B. Equal, variable
C. Fixed, variable
D. Variable, equal
Hint / Module Reference (Where to Read)
Relate the value domain and access requirements to storage and type choices.
The attribute-type section directly defines CHAR as fixed-length and VARCHAR as variable-length. Neither blank means equal length.
Evidence in the complete study note
CHAR(n) is fixed length; VARCHAR(n) has variable length up to a limit. DATE represents a date and BLOB binary content such as images. INTEGER represents whole numbers; DECIMAL(p,s) represents exact decimal values, making it suitable for money when scale is specified. FLOAT/REAL/DOUBLE are approximate; names, widths and precision depend on the DBMS. Numeric type widths depend on the DBMS and type; do not assume one universal width.FDB23
Choice by choice
A · incorrect — Fixed is right for CHAR but equal is wrong for VARCHAR. (PDF pp. 19.)
B · incorrect — Variable is right for VARCHAR but equal is not the stated CHAR class. (PDF pp. 19.)
C · correct — Fixed followed by variable matches both definitions. (PDF pp. 19.)
D · incorrect — Both descriptions miss the given definitions. (PDF pp. 19.)
Question 80
To remove a relation from an SQL database, we use the ______ comman
d.
A. Delete
B. Purge
C. Remove
D. Drop table
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
Removing a relation means removing the table object, rather than just its tuples. FDB26 distinguishes DROP TABLE from DELETE: DELETE preserves the table definition while removing rows. DROP TABLE removes the object itself. PURGE and REMOVE are not the general SQL command specified here for this task.
Evidence in the complete study note
DELETE removes rows and keeps the table; DROP TABLE removes the table object; ALTER changes its definition. TRUNCATE empties a table using implementation-specific mechanisms. PostgreSQL allows transactional rollback of TRUNCATE, so no rollback information is retained is not a universal SQL property. SQL Server permits DROP DATABASE Sales, suppliers; that comma-list syntax is vendor-specific. SQLite's .databases lists attached names/files in its command-line shell; .database is not the documented command.FDB26
Choice by choice
A · incorrect — DELETE removes tuples while preserving the relation definition, so it does not remove the relation.
B · incorrect — PURGE is not the general table-removal command established here.
C · incorrect — REMOVE is not the SQL command used here to remove a relation object.
D · correct — DROP TABLE removes the table object, including its relation definition.
Question 81
Assume constraints permit the deletion. Which descriptions apply to executing DELETE FROM r; on an existing table r? Select all that apply.
A. Remove relation
B. Clear relation entries
C. Delete fields
D. Delete rows
Hint / Module Reference (Where to Read)
Track which rows each data-changing command affects.
Correct answers (select all): B, D — B. Clear relation entries; D. Delete rows
Without WHERE, DELETE targets every row in r. If the operation succeeds, the table's entries are cleared but its column definitions and table object remain. Clear relation entries and delete rows describe this same effect. Removing the relation itself requires a schema operation such as DROP TABLE, while deleting fields would change columns rather than row contents.
Evidence in the complete study note
INSERT INTO Hotel(HotelID,HotelName,SubCity) VALUES (1,'Hilton','Yeka'); pairs each named column with a compatible value. Counts and types must match; keys and other constraints still apply. Without a column list, values must follow the table's declared column order.FDB29
Both clear relation entries and delete rows describe that effect.FDB29
DELETE FROM r; removes every row but leaves r defined.FDB29
Choice by choice
A · incorrect — The table definition remains; DELETE does not remove the relation object.
B · correct — Without WHERE, every relation entry is removed while the table remains available.
C · incorrect — Fields are columns; DELETE removes rows rather than column definitions.
D · correct — Every row is targeted because the statement contains no filtering WHERE clause.
Question 82
INSERT INTO instructor VALUES (10211, 'Smith', 'Biology', 66000);
What type of statement is this?
A. Query
B. DML
C. Relational
D. DDL
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
INSERT belongs to the DML list. Classification follows from the operation name; it does not require assuming an instructor schema or proving that these particular values satisfy its constraints.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
Without a column list, values must follow the table's declared column order.FDB29
INSERT INTO Hotel(HotelID,HotelName,SubCity) VALUES (1,'Hilton','Yeka'); pairs each named column with a compatible value.FDB29
Choice by choice
A · incorrect — Query is a generic request rather than the displayed language category. (PDF pp. 26.)
B · correct — INSERT is expressly a DML command. (PDF pp. 26.)
C · incorrect — Relational names a model, not this statement class. (PDF pp. 26.)
D · incorrect — INSERT is not in the DDL list. (PDF pp. 26.)
Question 83
Which joins discard unmatched tuples from both inputs? Interpret Natural join as an ordinary natural inner join. Select all that apply.
A. Left outer join
B. Right outer join
C. Inner join
D. Natural join
Hint / Module Reference (Where to Read)
Compute matching pairs, then account for any preserved unmatched rows.
Correct answers (select all): C, D — C. Inner join; D. Natural join
An inner join returns only pairs that satisfy its matching condition, so unmatched tuples from either input disappear. An ordinary natural join is an inner equality join using all common column names and has the same preservation behavior. Left and right outer joins instead preserve unmatched tuples from their designated side by adding NULLs for the other side.
Evidence in the complete study note
An inner join returns matching pairs satisfying ON. An equijoin uses equality; a theta join permits another comparison. A natural join is ordinarily an inner equality join on every same-named column and merges those duplicate columns. JOIN … USING (DeptID) specifies shared join columns explicitly. CROSS JOIN forms every pair without a matching condition.FDB31
LEFT OUTER JOIN also preserves unmatched left rows, padding right columns with NULL; RIGHT preserves right rows and FULL preserves both.FDB31
Therefore inner and natural inner joins both discard unmatched rows.FDB31
Choice by choice
A · incorrect — A left outer join preserves unmatched left rows, filling right-side columns with NULL.
B · incorrect — A right outer join preserves unmatched right rows, filling left-side columns with NULL.
C · correct — An inner join returns matching pairs only, so unmatched tuples do not survive.
D · correct — An ordinary natural join is an inner equality join and discards unmatched tuples.
Question 84
What type of join is needed when you wish to include rows that do not have matching values?
A. Equi-join
B. Natural join
C. Outer join
D. All of the mentioned
Hint / Module Reference (Where to Read)
Compute matching pairs, then account for any preserved unmatched rows.
An outer join retains unmatched rows from one or both inputs and fills the missing side with NULL. FDB31 contrasts that behavior with ordinary matching-only joins. Equijoin describes an equality condition, and an ordinary natural join is an inner equality join; neither alone guarantees unmatched-row preservation. Therefore all of the mentioned is false.
Evidence in the complete study note
An inner join returns matching pairs satisfying ON. An equijoin uses equality; a theta join permits another comparison. A natural join is ordinarily an inner equality join on every same-named column and merges those duplicate columns. JOIN … USING (DeptID) specifies shared join columns explicitly. CROSS JOIN forms every pair without a matching condition.FDB31
Therefore inner and natural inner joins both discard unmatched rows.FDB31
LEFT OUTER JOIN also preserves unmatched left rows, padding right columns with NULL; RIGHT preserves right rows and FULL preserves both.FDB31
Choice by choice
A · incorrect — An equality condition alone does not retain rows that fail to match.
B · incorrect — An ordinary natural join uses matching shared-column values and discards unmatched rows.
C · correct — Outer joins preserve unmatched rows from the left, right or both inputs.
D · incorrect — The other named joins do not all guarantee preservation of unmatched rows.
Question 85
How many input relations does one ordinary binary join operator combine? Count operator inputs, not distinct base tables used by a complete query.
A. One
B. Two
C. Three
D. All of the mentioned
Hint / Module Reference (Where to Read)
Compute matching pairs, then account for any preserved unmatched rows.
One binary join operator combines two input relations. Either input may itself be the output of an earlier join, so the complete query can involve more base tables. A self-join can refer twice to one base table, but still supplies two relation occurrences to the operator. The question fixes the count at the operator level, making two the unique choice.
Evidence in the complete study note
An inner join returns matching pairs satisfying ON. An equijoin uses equality; a theta join permits another comparison. A natural join is ordinarily an inner equality join on every same-named column and merges those duplicate columns. JOIN … USING (DeptID) specifies shared join columns explicitly. CROSS JOIN forms every pair without a matching condition.FDB31
Each ordinary join operator combines two inputs, but its inputs can be earlier joins, so one query can join many tables.FDB31
Distinguish base-table count, table occurrences and binary operator arity.FDB31
Choice by choice
A · incorrect — One distinct base table can support a self-join, but the operator still has two inputs.
B · correct — A binary join operator has exactly two inputs.
C · incorrect — Three base tables require a chain of binary joins, not one operator.
D · incorrect — The input count of the specified binary operator is fixed, so all counts do not apply.
Question 86
Which option gives the complete set of valid SQL join forms among the listed alternatives? Choose the most complete single answer.
A. Cross join
B. Natural join
C. Join with USING clause
D. All of the mentioned
Hint / Module Reference (Where to Read)
Compute matching pairs, then account for any preserved unmatched rows.
CROSS JOIN forms every pair of input rows without a matching predicate. NATURAL JOIN matches all same-named columns and merges the duplicate join columns. JOIN USING explicitly names shared equality columns. All three are valid join forms, although they do not all express a join condition in the same way. The revised stem requests the complete single answer, so the aggregate D is selected; each individually true alternative is incomplete on its own.
Evidence in the complete study note
An inner join returns matching pairs satisfying ON. An equijoin uses equality; a theta join permits another comparison. A natural join is ordinarily an inner equality join on every same-named column and merges those duplicate columns. JOIN … USING (DeptID) specifies shared join columns explicitly. CROSS JOIN forms every pair without a matching condition.FDB31
Choice by choice
A · incorrect — Incorrect as the complete selection: CROSS JOIN is a valid join form without a matching condition, but B and C also qualify.
B · incorrect — Incorrect as the complete selection: NATURAL JOIN is valid and matches same-named columns, but A and C also qualify.
C · incorrect — Incorrect as the complete selection: JOIN USING is valid and names shared equality columns, but A and B also qualify.
D · correct — Correct: all three named constructs are valid SQL join forms, so the aggregate is the complete answer.
Question 87
The operation which is not considered a basic operation of relational algebra is
A. Join
B. Selection
C. Union
D. Cross product
Hint / Module Reference (Where to Read)
Use the operator convention stated in the question and distinguish membership from derivability.
The stated basic-operation list contains selection, union, cross-product, projection, difference, and intersection. Join is not included, even though it appears in later execution-plan examples.
Evidence in the complete study note
A list of basic relational-algebra operators is a teaching convention, distinct from a claim that every listed operation is logically indispensable. The course module uses selection, projection, Cartesian product, set difference, union and intersection as its basic-operation list. Natural join and assignment are not members of that particular list.FDB-S01
A smaller primitive basis may therefore omit intersection even when it is introduced as a basic operation.FDB-S01
Intersection can be expressed using difference, while a matching join can be expressed as selection over a Cartesian product.FDB-S01
Choice by choice
A · correct — Join is absent from the basic list and is the requested exception. (PDF pp. 24.)
B · incorrect — Selection is explicitly basic. (PDF pp. 24.)
C · incorrect — Union is explicitly basic. (PDF pp. 24.)
D · incorrect — Cross-product is explicitly basic. (PDF pp. 24.)
Question 88
A many to many relationship between two entities usually results in how many tables?
A. One
B. Two
C. Three
D. Four
Hint / Module Reference (Where to Read)
Identify relationship degree and cardinality before choosing a relational mapping.
Correct answer: C — Three tables: two entity tables and one association table.
Each of the two entity types normally has its own relation. Their many-to-many relationship requires an additional association relation holding references to both entities and any relationship attributes. FDB12 illustrates Student, Course and Enrollment. Thus the usual total is three, rather than only the one new association table.
Evidence in the complete study note
A binary M:N relationship needs an association table in addition to the two entity tables. Enrollment(StudentID,CourseID,Grade) references Student and Course; (StudentID,CourseID) is a key if one enrollment per pair is allowed. Repeated attempts require Term or AttemptNo in the key. This ordinarily produces three tables.FDB12
Choice by choice
A · incorrect — One counts only the association table and omits the two participating entity tables.
B · incorrect — Two entity tables alone do not record arbitrary many-to-many pairs.
C · correct — The two entity tables plus their association table produce the usual total of three.
D · incorrect — No fourth table is required by the ordinary binary many-to-many mapping.
Question 89
An ________ is a set of entities of the same type that share the same properties, or attributes.
A. Entity set
B. Attribute set
C. Relation set
D. Entity model
Hint / Module Reference (Where to Read)
Review entity, attribute and relationship notation before interpreting the model.
FDB06 distinguishes an individual entity from the type describing its properties and the set containing the current entities of that type. The stem asks for that collection of similarly described entities, which is an entity set. Attributes describe properties; a model describes representation rather than the current collection itself.
Evidence in the complete study note
An entity is a distinguishable real or conceptual thing, such as one student or one course. An entity type describes common properties; its current collection of entities is an entity set. A relationship associates entities. In Chen notation rectangles represent entity types, diamonds relationships and ellipses attributes; double ellipses denote multivalued attributes and dashed ellipses derived attributes. Always inspect the diagram legend.FDB06
A relation is a set of tuples, represented as a table.FDB03
A tuple/row/record contains related attribute values; an attribute/column/field describes a property.FDB03
Choice by choice
A · correct — An entity set is the collection of entities belonging to one common entity type.
B · incorrect — An attribute set groups properties, rather than the entities possessing those properties.
C · incorrect — Relation set is not the ER term for the stated collection of entities.
D · incorrect — An entity model describes structures, rather than naming the current set of entity instances.
Question 90
Entity is a _________
A. Object of relation
B. Present working model
C. Thing in real world
D. Model of relation
Hint / Module Reference (Where to Read)
Review entity, attribute and relationship notation before interpreting the model.
An entity is an object with physical or conceptual existence. It need not be a physical thing; the real-world thing option includes conceptual objects such as a course.
Evidence in the complete study note
An entity is a distinguishable real or conceptual thing, such as one student or one course. An entity type describes common properties; its current collection of entities is an entity set. A relationship associates entities. In Chen notation rectangles represent entity types, diamonds relationships and ellipses attributes; double ellipses denote multivalued attributes and dashed ellipses derived attributes. Always inspect the diagram legend.FDB06
Choice by choice
A · incorrect — Object of relation is not the module's definition. (PDF pp. 10.)
B · incorrect — A present working model is not an entity definition. (PDF pp. 10.)
C · correct — Thing in the real world matches physical or conceptual objects. (PDF pp. 10.)
D · incorrect — A model describes entities rather than being the object described. (PDF pp. 10.)
Question 91
E-R modeling technique is a :
A. Top-down approach
B. Bottom-up approach
C. Left-right approach
D. None of the above
Hint / Module Reference (Where to Read)
Identify the design phase from its inputs and intended deliverable.
Correct answer: A — Top-down is the usual conceptual ER approach.
FDB05 describes conceptual design as usually starting with an enterprise view and working downward into entity and relationship details. This is the usual top-down classification of ER modeling. Bottom-up refinement is associated here with normalization from dependencies. This is a useful conventional approach, rather than a claim that every ER modeling activity must follow one direction.
Evidence in the complete study note
First collect business facts, required transactions, reports, volumes, privacy rules and quality targets. Conceptual design turns them into an ER/EER model. Logical design maps the conceptual model to relations, keys and constraints and checks normalization. Physical design chooses file organization, indexes and storage. Implementation creates and loads the actual objects; testing checks requirements.FDB05
Conceptual design usually proceeds from a whole enterprise view downward; normalization can refine structures from dependencies upward.FDB05
These are useful approaches rather than mandatory directions for every modeling activity.FDB05
Choice by choice
A · correct — Conceptual ER modeling usually moves from an overall enterprise view toward detailed entities and relationships.
B · incorrect — Bottom-up dependency refinement describes normalization here, rather than the usual ER conceptual approach.
C · incorrect — Left-right is not a database design methodology identified here.
D · incorrect — Top-down is an available and appropriate description of the usual conceptual ER approach.
Question 92
The number of entities to which another entity can be associated via a relationship set is expressed as :
A. Entity
B. Cardinality
C. Schema
D. Attributes
Hint / Module Reference (Where to Read)
Read the relationship in both directions and check participation constraints.
Cardinality measures how often an entity participates and distinguishes one-to-one, one-to-many, and many-to-many. That is the number of possible associations asked about.
Evidence in the complete study note
Cardinality specifies maximum associated occurrences: 1:1, 1:N or M:N. Minimum cardinality specifies optional/partial participation (zero permitted) or mandatory/total participation (at least one). A maximum of one does not mean participation is compulsory. Relationship degree counts participating entity roles: unary recursive, binary or ternary.FDB09
Choice by choice
A · incorrect — Entity is a participating object. (PDF pp. 11, 12.)
B · correct — Cardinality is the association-count constraint. (PDF pp. 11, 12.)
C · incorrect — Schema describes structure rather than the count. (PDF pp. 11, 12.)
D · incorrect — Attributes are properties, not the association-count classification. (PDF pp. 11, 12.)
Question 93
Relations produced from an E-R model will always be in :
A. First normal form
B. Second normal form
C. Third normal form
D. Fourth normal form
Hint / Module Reference (Where to Read)
Check atomicity, candidate keys and partial dependencies in that order.
Correct answer: A — First normal form, assuming correct ER-to-relational mapping.
FDB20 states that properly splitting composite and multivalued ER structures produces ordinary 1NF relations. Nothing in an ER drawing alone proves absence of partial, transitive or multivalued dependencies needed for higher forms. Thus A is the guaranteed level under correct relational mapping; the word always must not be read as excusing an incorrect mapping.
Evidence in the complete study note
1NF requires atomic values in the chosen relational domains and no repeating groups in cells. It does not mean merely declaring a primary key. A mapped ER design that correctly splits composite and multivalued structures gives ordinary 1NF tables; ER drawing alone guarantees no higher form.FDB20
Higher forms inherit 2NF, so no partial dependencies establishes the minimum guaranteed stage only when 1NF is also assumed.FDB20
A relation whose every candidate key is single-attribute has no nonprime partial-key dependency.FDB20
Choice by choice
A · correct — Correctly mapped atomic relations are in 1NF; this is the guaranteed level described here.
B · incorrect — An ER model alone does not rule out partial dependencies on a composite candidate key.
C · incorrect — An ER model alone does not establish the full dependency conditions required for 3NF.
D · incorrect — An ER model alone does not rule out the nontrivial multivalued dependencies addressed by 4NF.
Question 94
In ER model the details of the entities are hidden from the user. This process is called :
A. generalization
B. specialization
C. abstraction
D. none of these above
Hint / Module Reference (Where to Read)
Trace which schema levels a proposed change affects.
Abstraction hides unwanted or irrelevant details. Generalization groups subclasses; specialization divides a superclass. Thus hiding entity details describes abstraction.
Evidence in the complete study note
The external level gives each user group a selected view. The conceptual level describes the whole logical database: entity types, relationships, data types and constraints. The internal/physical level describes files, record placement and access paths. These are abstraction levels, distinct from client/server deployment tiers.FDB04
Specialization divides Student into Undergraduate and Graduate; generalization combines common properties into Student.FDB13
EER extends ER with superclass/subclass structure.FDB13
Choice by choice
A · incorrect — Generalization combines common characteristics, rather than the general detail-hiding process. (PDF pp. 6, 13.)
B · incorrect — Specialization separates narrower classes. (PDF pp. 6, 13.)
C · correct — Abstraction is explicitly the process of hiding details. (PDF pp. 6, 13.)
D · incorrect — The required term is present. (PDF pp. 6, 13.)
Question 95
The attributes made up of more than one single attributes are called
A. Composite attribute
B. Derived attribute
C. Single value attribute
D. Multi value attribute
Hint / Module Reference (Where to Read)
Review entity, attribute and relationship notation before interpreting the model.
Composite attributes have smaller meaningful subparts. This differs from multiple values of one attribute and from calculating a value from another.
Evidence in the complete study note
Simple versus composite asks whether a value has meaningful components: Address may split into City and Street. Single-valued versus multivalued asks how many values one entity may have: a student may have several phone numbers. These dimensions differ: one address may be composite and single-valued. Age derived from birth date is derived, even if also single-valued. Attribute names alone do not establish multiplicity.FDB06
Choice by choice
A · correct — Composite is the stated divisible structure. (PDF pp. 10, 10.)
B · incorrect — Derived concerns computation from stored attributes. (PDF pp. 10, 10.)
C · incorrect — Single-valued concerns count of values, not internal composition. (PDF pp. 10, 10.)
D · incorrect — Multivalued concerns a set of values rather than components of one value. (PDF pp. 10, 10.)
Question 96
The attribute AGE is calculated from DATE_OF_BIRTH. The attribute AGE is
A. Single valued
B. Multi valued
C. Composite
D. Derived
Hint / Module Reference (Where to Read)
Review entity, attribute and relationship notation before interpreting the model.
The exact Age/Birth_date example identifies Age as derived. Age can also be single-valued, but the stem asks the classification indicated by calculating it from another attribute; D describes that dependency, while A describes a different axis.
Evidence in the complete study note
Simple versus composite asks whether a value has meaningful components: Address may split into City and Street. Single-valued versus multivalued asks how many values one entity may have: a student may have several phone numbers. These dimensions differ: one address may be composite and single-valued. Age derived from birth date is derived, even if also single-valued. Attribute names alone do not establish multiplicity.FDB06
Choice by choice
A · incorrect — Age can be single-valued, but that does not express the calculation relationship asked for. (PDF pp. 10, 10.)
B · incorrect — No set of ages per person is described. (PDF pp. 10, 10.)
C · incorrect — No meaningful component attributes of Age are specified. (PDF pp. 10, 10.)
D · correct — Derived directly matches the module's example and the calculation clause. (PDF pp. 10, 10.)
Question 97
A student has one official Name and one Date_of_birth, but may have several Phone_number values. Which listed attribute is multivalued for a student?
A. Phone_number
B. Name
C. Date_of_birth
D. All of the mentioned
Hint / Module Reference (Where to Read)
Review entity, attribute and relationship notation before interpreting the model.
Multiplicity asks how many values of one attribute may belong to the same entity. The student may have several phone numbers, making Phone_number multivalued. The stated rules allow one official name and one birth date, so those attributes are single-valued. These classifications follow the supplied requirements rather than the names or internal structure of the values.
Evidence in the complete study note
Simple versus composite asks whether a value has meaningful components: Address may split into City and Street. Single-valued versus multivalued asks how many values one entity may have: a student may have several phone numbers. These dimensions differ: one address may be composite and single-valued. Age derived from birth date is derived, even if also single-valued. Attribute names alone do not establish multiplicity.FDB06
Choice by choice
A · correct — Several phone values are permitted for one student.
B · incorrect — The model permits one official name per student.
C · incorrect — One birth date is permitted per student.
D · incorrect — Name and birth date are single-valued under the stated rules.
Question 98
For each student, Register_number has one value; Address records all residences and permits several values; SUBJECT_TAKEN permits several subjects; Reference permits several referees. Which listed attribute is single-valued?
A. Register_number
B. Address
C. SUBJECT_TAKEN
D. Reference
Hint / Module Reference (Where to Read)
Review entity, attribute and relationship notation before interpreting the model.
Single-valued means one value per entity, independently of whether that value can be broken into components. Register_number is single-valued under the stated student rule. Address, SUBJECT_TAKEN and Reference each explicitly permit multiple values for one student. An address can be composite without necessarily being multivalued, but this particular model permits multiple residences.
Evidence in the complete study note
Simple versus composite asks whether a value has meaningful components: Address may split into City and Street. Single-valued versus multivalued asks how many values one entity may have: a student may have several phone numbers. These dimensions differ: one address may be composite and single-valued. Age derived from birth date is derived, even if also single-valued. Attribute names alone do not establish multiplicity.FDB06
Choice by choice
A · correct — One register number is assigned per student.
B · incorrect — Several residence addresses are permitted.
C · incorrect — Several subjects can be taken by one student.
D · incorrect — Several referee references are permitted.
Question 99
An association of several entities in an Entity-Relation model is called :
A. Tuple
B. Record
C. Relationship
D. Field
Hint / Module Reference (Where to Read)
Review entity, attribute and relationship notation before interpreting the model.
A relationship represents an association between entity types. Tuples and records instead represent individual table rows.
Evidence in the complete study note
An entity is a distinguishable real or conceptual thing, such as one student or one course. An entity type describes common properties; its current collection of entities is an entity set. A relationship associates entities. In Chen notation rectangles represent entity types, diamonds relationships and ellipses attributes; double ellipses denote multivalued attributes and dashed ellipses derived attributes. Always inspect the diagram legend.FDB06
A tuple/row/record contains related attribute values; an attribute/column/field describes a property.FDB03
A relation is a set of tuples, represented as a table.FDB03
Choice by choice
A · incorrect — Tuple is a row. (PDF pp. 11, 13.)
B · incorrect — Record is row data, not the ER association category. (PDF pp. 11, 13.)
C · correct — Relationship is the explicit association term. (PDF pp. 11, 13.)
D · incorrect — Field is an attribute component, not the association. (PDF pp. 11, 13.)
Question 100
To include integrity constraint in an existing relation use:
A. Modify table
B. Drop table
C. Alter table
D. Create table
Hint / Module Reference (Where to Read)
Distinguish table definition changes from changes to stored rows.
Adding an integrity constraint to an existing table changes its definition, so ALTER TABLE is the appropriate command family. It can add a named constraint or a primary key, provided existing data satisfies the rule. DROP TABLE removes the object, and CREATE TABLE defines a new one. MODIFY TABLE does not follow the ALTER TABLE command form required for this structural change. The remaining constraint clause depends on the integrity rule to be enforced.
Evidence in the complete study note
ALTER TABLE Employee ADD COLUMN Email VARCHAR(120); adds a column in PostgreSQL-style syntax. ALTER TABLE Employee DROP COLUMN Email; removes it. ALTER TABLE Employee ADD CONSTRAINT uq_email UNIQUE (Email); adds a named constraint. Use ALTER rather than DALTER, and do not separate column_name and column_type with a comma; validate the resulting SQL before execution.FDB30
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications.FDB26
Choice by choice
A · incorrect — Incorrect: the required command family for this definition change is ALTER TABLE, not MODIFY TABLE.
B · incorrect — Incorrect: DROP TABLE removes the existing object instead of adding a constraint.
C · correct — Correct: ALTER TABLE can add a constraint to an existing table definition.
D · incorrect — Incorrect: CREATE TABLE defines a new table rather than changing this existing one.
Question 101
Which option is not a general SQL integrity-constraint keyword or clause? Distinguish a constraint clause from a business condition that could be expressed inside one.
A. Not null
B. Positive
C. Unique
D. Check predicate
Hint / Module Reference (Where to Read)
Read each SQL constraint's scope and enforcement rule.
NOT NULL forbids missing values, UNIQUE enforces uniqueness under the DBMS's NULL rules, and CHECK evaluates a declared predicate. POSITIVE by itself is not a general SQL constraint keyword. A positivity requirement is still a valid business rule and can be written as a CHECK predicate such as CHECK (Amount > 0). The question therefore distinguishes clause syntax from the meaning of a possible rule, making B the unique incorrect keyword rather than rejecting positivity as an integrity requirement.
Evidence in the complete study note
Example: CREATE TABLE Enrollment (StudentID INTEGER NOT NULL REFERENCES Student(StudentID), CourseID INTEGER NOT NULL REFERENCES Course(CourseID), Grade INTEGER CHECK (Grade BETWEEN 0 AND 100), PRIMARY KEY (StudentID,CourseID)); Each column declaration gives its name then type, with commas between declarations. CHECK is a constraint clause, not a standalone GRANT-like statement. POSITIVE is not a general SQL constraint keyword; CHECK (Amount > 0) expresses positivity.FDB16
PRIMARY KEY implies uniqueness and non-NULL. UNIQUE does not generally imply NOT NULL; NULL treatment differs by DBMS.FDB16
Choice by choice
A · incorrect — Incorrect selection: NOT NULL is a constraint that forbids NULL values.
B · correct — Correct selection: POSITIVE is not the standalone constraint keyword; a CHECK predicate can enforce positivity.
C · incorrect — Incorrect selection: UNIQUE is a constraint enforcing uniqueness.
D · incorrect — Incorrect selection: CHECK is the clause for enforcing a declared predicate.
Question 102
Foreign key is the one in which the ________ of one relation is referenced in another relation.
A. Foreign key
B. Primary key
C. References
D. Check constraint
Hint / Module Reference (Where to Read)
Match the validity requirement to the appropriate constraint definition.
A foreign key references a matching candidate, usually primary, key in the parent. B is the offered conventional primary-key case; the definition also permits a candidate key, so primary is not an exclusive universal requirement.
Evidence in the complete study note
Domain integrity restricts type and allowed values. Key integrity enforces uniqueness. Entity integrity requires every primary-key component to be non-NULL. Referential integrity requires a non-NULL foreign-key value to match an eligible referenced unique/candidate key; self-references are allowed. A parent need not have a child.FDB15
Choice by choice
A · incorrect — An arbitrary foreign key is not the required parent unique target. (PDF pp. 48.)
B · correct — Primary key is the listed usual referenced key. (PDF pp. 48.)
C · incorrect — References names a mechanism rather than the key category. (PDF pp. 48.)
D · incorrect — A CHECK predicate is not the referenced key. (PDF pp. 48.)
Question 103
Which are uses of data integrity constraints, rather than access authorization? Select all that apply.
A. Control who is allowed access to the data
B. Ensure that duplicate records are not entered into the table
C. Improve the quality of data entered for a specific property (i.e., table column)
D. Prevent users from changing the values stored in the table
Hint / Module Reference (Where to Read)
Match the validity requirement to the appropriate constraint definition.
Correct answers (select all): B, C — B. Ensure that duplicate records are not entered into the table; C. Improve the quality of data entered for a specific property (i.e., table column)
Integrity constraints test whether data is valid according to the schema's rules. Uniqueness constraints can reject duplicate keyed records, while domain constraints reject inappropriate values for a column. These support the two selected purposes. Authorization controls who may access the data. Integrity rejects invalid changes rather than prohibiting all changes, so it does not imply that every stored value is immutable.
Evidence in the complete study note
Domain integrity restricts type and allowed values. Key integrity enforces uniqueness. Entity integrity requires every primary-key component to be non-NULL. Referential integrity requires a non-NULL foreign-key value to match an eligible referenced unique/candidate key; self-references are allowed. A parent need not have a child.FDB15
Choice by choice
A · incorrect — Controlling who may access data is authorization rather than data integrity itself.
B · correct — Key or uniqueness constraints prevent duplicate keyed records.
C · correct — Domain constraints validate types and allowed values for a specific column.
D · incorrect — Integrity permits valid updates; it does not prohibit every change to stored values.
Question 104
The relationship between the two tables are created using __________
A. Candidate Key
B. Primary Key
C. Foreign Key
D. Check Constraint
Hint / Module Reference (Where to Read)
Match the validity requirement to the appropriate constraint definition.
A foreign key links child occurrences to the matching parent candidate key. Primary/candidate keys identify the target, but foreign-key referencing creates the intertable link.
Evidence in the complete study note
Domain integrity restricts type and allowed values. Key integrity enforces uniqueness. Entity integrity requires every primary-key component to be non-NULL. Referential integrity requires a non-NULL foreign-key value to match an eligible referenced unique/candidate key; self-references are allowed. A parent need not have a child.FDB15
A primary key is one selected candidate key; remaining candidates are alternate keys.FDB07
POSITIVE is not a general SQL constraint keyword; CHECK (Amount > 0) expresses positivity.FDB16
Choice by choice
A · incorrect — Candidate keys identify potential reference targets rather than expressing the link alone. (PDF pp. 48.)
B · incorrect — A primary key identifies a row; a foreign key expresses the reference. (PDF pp. 48.)
C · correct — The foreign key is explicitly the linking mechanism. (PDF pp. 48.)
D · incorrect — CHECK restricts values rather than defining this intertable reference. (PDF pp. 48.)
Question 105
Customer has candidate keys CustId, Email and ContactNo. CustId is an immutable compact generated identifier; Email and ContactNo may change. A policy prefers stable compact candidate keys. Which is the best primary key under that policy?
A. CustId
B. Email
C. ContactNo
D. CustId and ContactNo
Hint / Module Reference (Where to Read)
Test uniqueness and minimality separately when classifying keys.
Every candidate key is eligible for primary-key selection, but the policy gives a reason to prefer one. CustId is explicitly stable and compact, while the contact attributes may change. Choosing CustId follows that policy without denying the uniqueness of the other candidates. Combining CustId and ContactNo is nonminimal because CustId alone already identifies each customer.
Evidence in the complete study note
A superkey uniquely identifies a tuple. A candidate key is a minimal superkey: removing any component loses uniqueness. Minimal means no unnecessary attribute, not necessarily the fewest columns among all keys. A primary key is one selected candidate key; remaining candidates are alternate keys. A composite key contains multiple attributes and need not be the chosen primary key.FDB07
Prefer stable, compact identifiers when practical, but Email, SSN or EmployeeID is a candidate only if business rules guarantee uniqueness.FDB07
A table can have several candidates and foreign keys but at most one declared primary-key constraint, possibly containing several columns.FDB07
Choice by choice
A · correct — Its stated stability and compactness satisfy the preference policy.
B · incorrect — Email is eligible but may change, so it loses under this policy.
C · incorrect — ContactNo is eligible but may change, so it loses under this policy.
D · incorrect — Adding ContactNo to an already unique CustId is unnecessary and nonminimal.
Question 106
Which of the following statement(s) is/are FALSE about primary key?
A. There can be more than one primary key for a table
B. Columns with string data types cannot be made Primary Key because they are large
C. Both A and B
D. None of the above
Hint / Module Reference (Where to Read)
Test uniqueness and minimality separately when classifying keys.
The designer selects one candidate key as the primary key; a composite primary key is still one key. No numeric-only restriction appears: a key is defined by uniqueness, not by a prohibition on string type. Thus both A and B are false claims.
Evidence in the complete study note
A superkey uniquely identifies a tuple. A candidate key is a minimal superkey: removing any component loses uniqueness. Minimal means no unnecessary attribute, not necessarily the fewest columns among all keys. A primary key is one selected candidate key; remaining candidates are alternate keys. A composite key contains multiple attributes and need not be the chosen primary key.FDB07
A table can have several candidates and foreign keys but at most one declared primary-key constraint, possibly containing several columns.FDB07
PRIMARY KEY implies uniqueness and non-NULL.FDB16
Choice by choice
A · incorrect — False: several candidate keys do not mean several primary keys. (PDF pp. 11, 14.)
B · incorrect — False: the definition imposes uniqueness, not a ban on strings. (PDF pp. 11, 14.)
C · correct — Both preceding claims conflict with the key definition. (PDF pp. 11, 14.)
D · incorrect — There are false claims, so none is not correct. (PDF pp. 11, 14.)
Question 107
Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?
A. Data Definition Language(DDL)
B. Data Manipulation Language(DML)
C. DML and DDL
D. None of the Mentioned
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
Schema-structure manipulation falls under definition language, despite the everyday word manipulate. DML is the separate category for data manipulation.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
Choice by choice
A · correct — DDL contains the structural commands. (PDF pp. 26.)
B · incorrect — DML concerns data operations. (PDF pp. 26.)
C · incorrect — The asked subset for structures is DDL, not a combined category. (PDF pp. 26.)
D · incorrect — DDL is offered. (PDF pp. 26.)
Question 108
Using the course's textbook SQL command categories, with RENAME treated as DDL, which listed commands are outside DDL? Select all that apply.
A. RENAME
B. REVOKE
C. GRANT
D. UPDATE
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
Correct answers (select all): B, C, D — B. REVOKE; C. GRANT; D. UPDATE
DDL concerns structural definitions and includes RENAME in this course classification. GRANT and REVOKE manage privileges, placing them in DCL, while UPDATE changes existing data and belongs to DML. All three are consequently outside DDL. Classifying by each statement's effect distinguishes schema operations, access permissions and data changes even though all are expressed using SQL.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
Choice by choice
A · incorrect — RENAME is commonly classified as DDL in the note's textbook taxonomy.
B · correct — REVOKE removes privileges and is DCL rather than DDL.
C · correct — GRANT assigns privileges and is DCL rather than DDL.
D · correct — UPDATE changes data and is DML rather than DDL.
Question 109
Which command empties an existing table while retaining its definition, using DBMS-specific table-emptying mechanisms rather than asserting that rollback is impossible?
A. TRUNCATE command
B. Create command
C. Drop table command
D. Alter table command
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
TRUNCATE empties a table while retaining the table object. Its implementation, logging and transaction behavior depend on the DBMS, so the answer does not rely on a claim that rollback is impossible. CREATE defines an object, DROP TABLE removes it, and ALTER changes its definition. Only TRUNCATE has the requested table-emptying role among these choices.
Evidence in the complete study note
DELETE removes rows and keeps the table; DROP TABLE removes the table object; ALTER changes its definition. TRUNCATE empties a table using implementation-specific mechanisms. PostgreSQL allows transactional rollback of TRUNCATE, so no rollback information is retained is not a universal SQL property. SQL Server permits DROP DATABASE Sales, suppliers; that comma-list syntax is vendor-specific. SQLite's .databases lists attached names/files in its command-line shell; .database is not the documented command.FDB26
Choice by choice
A · correct — TRUNCATE empties the table and keeps its definition.
B · incorrect — CREATE defines objects rather than emptying an existing table.
C · incorrect — DROP TABLE removes the definition as well as the table.
D · incorrect — ALTER changes the table definition rather than serving as the table-emptying command.
Question 110
Which SQL command form removes a database object together with its contained database objects?
A. DROP DATABASE
B. DELETE DATABASE
C. REMOVE DATABASE
D. None of the mentioned
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
DROP removes a database object, whereas DELETE removes rows from an existing table. DROP DATABASE is consequently the correct command form for removing a database and its contained objects. DELETE DATABASE does not express that structural operation, and REMOVE DATABASE does not follow the taught DROP syntax. Since the correct DROP DATABASE form is one of the options, the none-of-the-mentioned alternative is also incorrect.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
SQL Server permits DROP DATABASE Sales, suppliers; that comma-list syntax is vendor-specific.FDB26
Choice by choice
A · correct — Correct: DROP DATABASE removes the database object and its contained objects.
B · incorrect — Incorrect: DELETE is row removal; deleting rows does not remove the database object.
C · incorrect — Incorrect: the database-removal command form uses DROP, not REMOVE.
D · incorrect — Incorrect: DROP DATABASE is present and performs the requested structural operation.
Question 111
In SQL Server, which statement drops the two databases named Sales and suppliers? Assume permissions and database state allow the operation.
A. DROP DATABASE Sales, NewSales
B. DROP DATABASE Sales, suppliers
C. DROP DATABASE Sales and suppliers
D. DROP DATABASE Sales-suppliers
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
Correct answer: B — B. DROP DATABASE Sales, suppliers
SQL Server permits a comma-separated list of database names after DROP DATABASE. B names exactly Sales and suppliers using that syntax. A names a different second database, while AND and a hyphen do not form the required two-name list. The answer is specific to SQL Server and does not assert that the same multi-database syntax works in every SQL dialect.
Evidence in the complete study note
DELETE removes rows and keeps the table; DROP TABLE removes the table object; ALTER changes its definition. TRUNCATE empties a table using implementation-specific mechanisms. PostgreSQL allows transactional rollback of TRUNCATE, so no rollback information is retained is not a universal SQL property. SQL Server permits DROP DATABASE Sales, suppliers; that comma-list syntax is vendor-specific. SQLite's .databases lists attached names/files in its command-line shell; .database is not the documented command.FDB26
Choice by choice
A · incorrect — The second target is NewSales rather than suppliers.
B · correct — The supported comma list names both required databases.
C · incorrect — AND is not the separator in this DROP DATABASE syntax.
D · incorrect — A hyphen does not separate these two database names.
Question 112
Command that is used to add attributes to an existing relation, is said to be
A. Alter
B. Modify
C. Tailor
D. Eliminate
Hint / Module Reference (Where to Read)
Distinguish table definition changes from changes to stored rows.
Adding attributes changes a relation schema. FDB30 demonstrates ALTER TABLE Employee ADD COLUMN Email VARCHAR(120), keeping the column name next to its type. ALTER is therefore the required command family. The remaining alternatives do not name the general SQL schema-change command shown by the complete note.
Evidence in the complete study note
ALTER TABLE Employee ADD COLUMN Email VARCHAR(120); adds a column in PostgreSQL-style syntax. ALTER TABLE Employee DROP COLUMN Email; removes it. ALTER TABLE Employee ADD CONSTRAINT uq_email UNIQUE (Email); adds a named constraint. Use ALTER rather than DALTER, and do not separate column_name and column_type with a comma; validate the resulting SQL before execution.FDB30
Choice by choice
A · correct — ALTER changes an existing relation definition and supports adding a column.
B · incorrect — MODIFY alone is not the general command for adding attributes to an existing relation.
C · incorrect — TAILOR is not an SQL schema-change command established here.
D · incorrect — ELIMINATE is not the SQL command for adding a relation attribute.
Question 113
Which keyword pair changes a table definition to remove a specified column, rather than changing or deleting row values? Treat the comma in an option as separating keyword names.
A. UPDATE TABLE
B. TRUNCATE COLUMN
C. ALTER, DROP
D. DELETE COLUMN
Hint / Module Reference (Where to Read)
Distinguish table definition changes from changes to stored rows.
Removing a column changes the table's definition. The appropriate form is ALTER TABLE followed by DROP COLUMN, so the keyword pair represented by C is correct. Its comma separates the two keyword names rather than belonging in executable SQL. UPDATE changes stored row values, DELETE removes rows, and TRUNCATE empties a table while retaining its definition; none of those operations removes the specified column definition.
Evidence in the complete study note
ALTER TABLE Employee ADD COLUMN Email VARCHAR(120); adds a column in PostgreSQL-style syntax. ALTER TABLE Employee DROP COLUMN Email; removes it. ALTER TABLE Employee ADD CONSTRAINT uq_email UNIQUE (Email); adds a named constraint. Use ALTER rather than DALTER, and do not separate column_name and column_type with a comma; validate the resulting SQL before execution.FDB30
DELETE removes rows and keeps the table; DROP TABLE removes the table object; ALTER changes its definition.FDB26
TRUNCATE empties a table using implementation-specific mechanisms.FDB26
UPDATE Book SET Price = 600 WHERE ID = 'B001'; changes matching rows. UPDATE without WHERE targets every row.FDB29
Choice by choice
A · incorrect — Incorrect: UPDATE changes row values rather than removing a column definition.
B · incorrect — Incorrect: TRUNCATE empties a table; TRUNCATE COLUMN is not the column-removal form.
C · correct — Correct: ALTER TABLE ... DROP COLUMN removes a column definition.
D · incorrect — Incorrect: DELETE removes rows, not a column from the schema.
Question 114
Which one of these is used with SELECT clause to fetch all columns from a table?
A. ALL
B. *
C. DISTINCT
D. AS
Hint / Module Reference (Where to Read)
Trace how SELECT forms its result columns and rows.
FDB27 explicitly says SELECT * selects all columns. ALL and DISTINCT instead govern duplicate result rows, so neither means every column. AS introduces an alias. A query still needs the appropriate FROM source for table columns; the question asks only which SELECT-list symbol requests them all.
Evidence in the complete study note
SELECT Name,Address FROM Customer; produces only those mailing-label fields. SELECT * selects all columns; DISTINCT removes duplicate result rows; AS supplies an alias; ALL permits duplicates and does not mean all columns. SQL normally preserves duplicates without DISTINCT. A query reading table columns needs an appropriate FROM source. A literal-only query such as SELECT 1 may legally omit FROM.FDB27
WHERE alone is a clause rather than a complete read-only statement, so it cannot be compared as an equivalent executable command.FDB27
Typical written clause order is SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY ….FDB27
Choice by choice
A · incorrect — ALL permits duplicate result rows and does not expand the result to every column.
B · correct — The wildcard * requests every column from the selected table source.
C · incorrect — DISTINCT removes duplicate result rows rather than selecting all available columns.
D · incorrect — AS supplies an alias for a result expression or source, not a wildcard column list.
Question 115
A parent table has a DATE column, a nullable data column and a primary-key column referenced by existing child rows with ON UPDATE RESTRICT. Which column's change to a different otherwise valid value is blocked specifically because the old key is still referenced? No other constraints or triggers block changes.
A. DATE type columns in the table
B. Columns which allows NULL values in the table
C. A primary key column which also serves as foreign key reference in another table
D. All of the above
Hint / Module Reference (Where to Read)
Read each SQL constraint's scope and enforcement rule.
Correct answer: C — C. A primary key column which also serves as foreign key reference in another table
The stated restrictive action prevents changing the parent key while existing children still refer to its old value. This is a consequence of the particular reference state and action, rather than inherent immutability of every primary key. DATE and nullable columns can accept otherwise valid changes. Therefore only the referenced primary-key column has the specified update block.
Evidence in the complete study note
PRIMARY KEY implies uniqueness and non-NULL. UNIQUE does not generally imply NOT NULL; NULL treatment differs by DBMS. A CHECK can pass when its result is unknown, so add NOT NULL if missing values must be forbidden. Parent updates/deletes may use NO ACTION/RESTRICT, CASCADE or SET NULL. A referenced key is not inherently unchangeable: legality depends on matching children and the declared action.FDB16
Choice by choice
A · incorrect — DATE values can be changed to other permitted dates.
B · incorrect — Allowing NULL does not itself block valid updates.
C · correct — Existing child references plus ON UPDATE RESTRICT block this key change.
D · incorrect — The date and nullable columns are not blocked under the given conditions.
Question 116
Using the course classification that includes SELECT in DML, which listed language terms can describe application requests to retrieve database data? Select all that apply.
A. DML
B. DDL
C. Query language
D. All of the Mentioned
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
Correct answers (select all): A, C — A. DML; C. Query language
A query language expresses requests for data. The course also includes SELECT retrieval within DML, alongside insertion, update and deletion. Both terms can therefore describe the application's retrieval request at different levels of terminology. DDL changes structural definitions instead of expressing the requested retrieval. Since DDL does not fit, the aggregate all-of-the-mentioned choice is also false.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
Choice by choice
A · correct — DML includes retrieval with SELECT in the note's classification.
B · incorrect — DDL changes schema structure rather than expressing ordinary data retrieval requests.
C · correct — A query language expresses requests for data, directly matching the stem.
D · incorrect — DDL does not fit the retrieval role, so the inclusive choice is false.
Question 117
Which of the following is not included in DML (Data Manipulation Language)?
A. Insert
B. Update
C. Delete
D. Alter
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
INSERT, UPDATE and DELETE are all in the DML subsection. ALTER is instead in the preceding DDL list.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
Choice by choice
A · incorrect — INSERT is listed as DML. (PDF pp. 26.)
B · incorrect — UPDATE is listed as DML. (PDF pp. 26.)
C · incorrect — DELETE is listed as DML. (PDF pp. 26.)
D · correct — ALTER is the DDL exception. (PDF pp. 26.)
Question 118
In the course's SQL command taxonomy, how is TRUNCATE classified?
A. DDL
B. DML
C. DCL
D. TCL
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
The course groups TRUNCATE with DDL commands. It empties a table through mechanisms that depend on the implementation, but that fact does not turn it into a privilege-management or transaction-boundary command. DML covers commands such as INSERT, UPDATE and DELETE; DCL covers GRANT and REVOKE; TCL covers COMMIT, ROLLBACK and SAVEPOINT. The requested classification is the course convention, not a claim that TRUNCATE has identical recovery behavior in every DBMS.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
Choice by choice
A · correct — Correct: the course's textbook classification includes TRUNCATE under DDL.
B · incorrect — Incorrect: DML includes row operations such as INSERT, UPDATE and DELETE; TRUNCATE is assigned to DDL in this taxonomy.
C · incorrect — Incorrect: DCL manages privileges through commands such as GRANT and REVOKE.
D · incorrect — Incorrect: TCL controls transaction boundaries or rollback points, unlike TRUNCATE.
Question 119
A type of query that is placed within a WHERE or HAVING clause of another query is called
A. Master query
B. Sub query
C. Super query
D. Multi-query
Hint / Module Reference (Where to Read)
Distinguish a stored query definition from stored data and trace subquery scope.
The optimization section identifies nested queries as separate query blocks, and the view example places a SELECT inside an outer WHERE. Subquery is the offered name matching this inner/nested query; WHERE or HAVING placement does not define every possible subquery location.
Evidence in the complete study note
A subquery is a SELECT nested inside another statement. It may appear in WHERE, HAVING or FROM, not only the first two. IN tests membership; EXISTS tests whether at least one qualifying row exists. Correlated subqueries refer to the outer row. A scalar subquery must produce an appropriate single value. Beware NOT IN when its subquery can return NULL; NOT EXISTS often expresses the intended absence more safely.FDB33
CREATE VIEW CSStudent AS SELECT StudentID,Name FROM Student WHERE Major = 'CS'; stores a query definition and presents a virtual table.FDB33
An ordinary view does not necessarily store its result; a materialized view does.FDB33
Choice by choice
A · incorrect — Master describes neither the inner query nor the module's nesting distinction. (PDF pp. 34, 58.)
B · correct — Subquery corresponds to the nested inner SELECT demonstrated. (PDF pp. 34, 58.)
C · incorrect — Super query does not name the inner block. (PDF pp. 34, 58.)
D · incorrect — Multi-query does not identify the nesting relationship. (PDF pp. 34, 58.)
Question 120
A data manipulation command the combines the records from one or more tables is called
A. SELECT
B. PROJECT
C. JOIN
D. PRODUCT
Hint / Module Reference (Where to Read)
Compute matching pairs, then account for any preserved unmatched rows.
The DML list contains JOIN, and the query-plan example describes joining Staff and Branch into a joined relation. This directly matches combining records rather than selecting columns alone.
Evidence in the complete study note
An inner join returns matching pairs satisfying ON. An equijoin uses equality; a theta join permits another comparison. A natural join is ordinarily an inner equality join on every same-named column and merges those duplicate columns. JOIN … USING (DeptID) specifies shared join columns explicitly. CROSS JOIN forms every pair without a matching condition.FDB31
Rename ρ changes relation or attribute names.FDB35
Identify the join variant before predicting its behavior.FDB35
Choice by choice
A · incorrect — SELECT can retrieve data, but JOIN names the combining operation illustrated. (PDF pp. 26, 28.)
B · incorrect — PROJECT selects columns rather than linking records by the illustrated condition. (PDF pp. 26, 28.)
C · correct — JOIN is explicitly used to combine the example relations. (PDF pp. 26, 28.)
D · incorrect — PRODUCT combines all pairs; it is the broader Cartesian operation rather than the illustrated join command. (PDF pp. 26, 28.)
Question 121
Data manipulation language (DML) includes statements that modify the _____ of a database.
A. Structure
B. Data
C. User
D. Size
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
Data-definition commands are distinct from data-manipulation commands such as INSERT, DELETE and UPDATE. Their category concerns the stored data, while SELECT is also included as a retrieval command that need not modify state.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
Choice by choice
A · incorrect — Structure belongs to definition language. (PDF pp. 26.)
B · correct — Data is the manipulated content. (PDF pp. 26.)
C · incorrect — User administration is not the target category here. (PDF pp. 26.)
D · incorrect — Size can change as a consequence, not the category manipulated. (PDF pp. 26.)
Question 122
Which listed term names the standard SQL expression form for converting a value to a requested data type, rather than vendor-specific conversion syntax?
A. Cast
B. Drop type
C. Alter type
D. Convert
Hint / Module Reference (Where to Read)
Check set membership, duplicates and compatible result types.
CAST is the standard expression form that requests conversion of a value to a target type. CONVERT can also perform conversions in supporting vendor dialects, but the question specifically excludes vendor-specific syntax. DROP TYPE and ALTER TYPE act on type definitions rather than converting one expression value. The target value must still be representable in the requested type.
Evidence in the complete study note
CAST('42' AS INTEGER) converts an expression to a requested type if the value permits it. CONVERT is vendor-specific and can also perform type conversion; specify the SQL dialect when comparing it with CAST. In SELECT xyz FROM table1 UNION xyz FROM table2, the second SELECT is missing. In the corrected query, xyz is a selected column/expression, not a table name.FDB34
Choice by choice
A · correct — CAST supplies the requested standard expression conversion.
B · incorrect — DROP TYPE removes a type definition.
C · incorrect — ALTER TYPE changes a type definition.
D · incorrect — CONVERT is vendor-specific in the supplied course distinction.
Question 123
Which SQL keyword combines the rows returned by two compatible SELECT statements into one result set, removing duplicates by default?
A. COMBINE
B. CONCAT
C. JOIN
D. UNION
Hint / Module Reference (Where to Read)
Check set membership, duplicates and compatible result types.
UNION combines the rows of compatible query results and removes duplicates by default. It is written between the SELECT statements. JOIN instead combines input rows through matching or product behavior within a query, rather than taking the set union of completed results. CONCAT concerns concatenation of values, and COMBINE does not form the required SQL set-operation syntax. D is therefore the unique correct selection for this result-set operation.
Evidence in the complete study note
SELECT ID FROM A UNION SELECT ID FROM B; combines compatible result columns and removes duplicate rows by default. UNION ALL retains duplicates. Explicit UNION DISTINCT is accepted in some dialects, including PostgreSQL; it is not a separate algebra rule. INTERSECT returns common rows; EXCEPT returns first-result rows absent from the second. Inputs need the same column count and compatible corresponding types.FDB34
Choice by choice
A · incorrect — Incorrect: COMBINE is not the UNION set-operation keyword required between these SELECT statements.
B · incorrect — Incorrect: concatenating values does not perform the requested union of query-result rows.
C · incorrect — Incorrect: JOIN forms combinations of input rows rather than this union of compatible SELECT results.
D · correct — Correct: UNION combines compatible query results and removes duplicates by default.
Question 124
What is xyz in the following SQL statement? SELECT xyz FROM table1 UNION xyz FROM table2;
A. row name
B. column name
C. table name
D. database name
Hint / Module Reference (Where to Read)
Check set membership, duplicates and compatible result types.
Correct answer: B — xyz denotes a selected column/expression; the second SELECT is missing.
The printed statement is malformed because the second branch says UNION xyz rather than UNION SELECT xyz. Nevertheless, the role of xyz in the first SELECT list is clear: it is a column or expression, not a row, table or database name. FDB34 explicitly identifies that role and the required repair, so the terminology question remains answerable.
Evidence in the complete study note
SELECT ID FROM A UNION SELECT ID FROM B; combines compatible result columns and removes duplicate rows by default. UNION ALL retains duplicates. Explicit UNION DISTINCT is accepted in some dialects, including PostgreSQL; it is not a separate algebra rule. INTERSECT returns common rows; EXCEPT returns first-result rows absent from the second. Inputs need the same column count and compatible corresponding types.FDB34
In the corrected query, xyz is a selected column/expression, not a table name.FDB34
In SELECT xyz FROM table1 UNION xyz FROM table2, the second SELECT is missing.FDB34
Choice by choice
A · incorrect — SQL does not select a row by placing its row name in this SELECT-list position.
B · correct — xyz occupies the selected-column position; the second branch needs its missing SELECT keyword.
C · incorrect — The table sources are table1 and table2, which follow FROM rather than SELECT.
D · incorrect — No database identifier occupies xyz's SELECT-list position in this statement.
Question 125
In a dialect that accepts explicit UNION DISTINCT, which listed modifier requests duplicate elimination when used with UNION?
A. ALL
B. NARROW
C. STRICT
D. DISTINCT
Hint / Module Reference (Where to Read)
Check set membership, duplicates and compatible result types.
UNION eliminates duplicate result rows by default. In the stated dialect, DISTINCT explicitly requests that same behavior, while ALL retains duplicates. NARROW and STRICT are not modifiers in the UNION [ALL | DISTINCT] form being tested. This classification does not imply that every DBMS accepts the explicit DISTINCT spelling: the question states that support, and bare UNION remains sufficient to request duplicate elimination.
Evidence in the complete study note
SELECT ID FROM A UNION SELECT ID FROM B; combines compatible result columns and removes duplicate rows by default. UNION ALL retains duplicates. Explicit UNION DISTINCT is accepted in some dialects, including PostgreSQL; it is not a separate algebra rule. INTERSECT returns common rows; EXCEPT returns first-result rows absent from the second. Inputs need the same column count and compatible corresponding types.FDB34
Choice by choice
A · incorrect — Incorrect: ALL retains duplicates, the opposite of the requested behavior.
B · incorrect — Incorrect: NARROW is not an ALL or DISTINCT modifier in the stated UNION form.
C · incorrect — Incorrect: STRICT is not an ALL or DISTINCT modifier in the stated UNION form.
D · correct — Correct: DISTINCT explicitly requests duplicate elimination in the stated dialect.
Question 126
The database design prevents some data from being stored due to _______.
A. Deletion anomalies
B. Insertion anomalies
C. Update anomalies
D. Selection anomalies
Hint / Module Reference (Where to Read)
Distinguish dependency-based design problems from the contents of one sample table.
Insertion anomaly is explicitly the inability to insert a new record properly when the whole primary key is unknown. The other anomalies concern losing information on deletion or inconsistent/multiple updates.
Evidence in the complete study note
Normalization separates facts according to their determinants, reducing avoidable repetition and associated anomalies. It does not require seeing bad current data: declared dependencies suffice. Adding a generated row ID may make insertion technically possible, but does not remove repeated facts or fix the dependency design. Reduced redundancy can improve integrity and storage use, but extra keys and joins mean neither storage savings nor faster retrieval is guaranteed.FDB19
Choice by choice
A · incorrect — Deletion anomaly loses existing related information. (PDF pp. 16.)
B · correct — Insertion anomaly prevents a proper new insertion. (PDF pp. 16.)
C · incorrect — Update anomaly concerns changing existing values. (PDF pp. 16.)
D · incorrect — Selection anomaly is not among the module's stated three types. (PDF pp. 16.)
Question 127
In the course terminology, which labels can name a drawing of relational tables and their primary-key/foreign-key references? Select all that apply. ER Diagram means a conceptual entity/relationship drawing; Relational Schema means a textual table definition here.
A. ER Diagram
B. Database Diagram
C. Schema Diagram
D. Relational Schema
Hint / Module Reference (Where to Read)
Distinguish a relation's structure from its current contents and its descriptive metadata.
Correct answers (select all): B, C — B. Database Diagram; C. Schema Diagram
A schema diagram draws relational tables and their key references. Database diagram is also used for that depiction in the course terminology, so both labels qualify. The question explicitly distinguishes a conceptual ER drawing and a textual relational schema from the requested table-reference drawing. This makes the two accepted names compatible without treating every representation of a database as the same artifact.
Evidence in the complete study note
A schema describes names, structures and constraints; an instance, state or snapshot is the data at a particular time. Inserting a student changes the state, while adding a column changes the schema. Metadata is data describing data. The catalog or data dictionary records table definitions, types, constraints and often access privileges and optimizer statistics. A schema diagram draws tables and key references; labels such as database diagram can overlap.FDB03
An entity is a distinguishable real or conceptual thing, such as one student or one course.FDB06
In Chen notation rectangles represent entity types, diamonds relationships and ellipses attributes; double ellipses denote multivalued attributes and dashed ellipses derived attributes.FDB06
Choice by choice
A · incorrect — A conceptual ER diagram represents entities and relationships rather than specifically depicting mapped table key references.
B · correct — Database diagram can denote the table-and-key depiction also called a schema diagram.
C · correct — A schema diagram explicitly draws tables and their primary/foreign-key references.
D · incorrect — A relational schema defines structures and constraints but need not itself be a diagram.
Question 128
R(A,B,C) has atomic attributes and exactly the dependencies implied by A→B and B→C. A is the sole candidate key; B is not a superkey and C is nonprime. Moving upward through 1NF, 2NF, 3NF and BCNF, which listed normal form is the first that R fails?
A. BCNF
B. 1NF
C. 2NF
D. 3NF
Hint / Module Reference (Where to Read)
Check atomicity, candidate keys and partial dependencies in that order.
The atomic relation satisfies 1NF. Its only candidate key is the single attribute A, so no nonprime dependency can involve a proper part of that key, and 2NF holds. However, B→C has a determinant that is not a superkey and a dependent that is nonprime. This violates the stated 3NF test, so 3NF is the listed form it fails.
Evidence in the complete study note
2NF requires 1NF and no nonprime attribute partially dependent on any candidate key. For Enrollment(SID,CID,SName,CTitle,Grade), assume key (SID,CID), SID→SName and CID→CTitle. Split into Student(SID,SName), Course(CID,CTitle) and Enrollment(SID,CID,Grade). Grade still depends on the whole pair. A relation whose every candidate key is single-attribute has no nonprime partial-key dependency. Higher forms inherit 2NF, so no partial dependencies establishes the minimum guaranteed stage only when 1NF is also assumed.FDB20
Full dependency requires all determinant attributes; partial dependency holds through a proper subset.FDB17
With atomic values the highest form is 1NF, even though a transitive dependency also exists.FDB21
Choice by choice
A · incorrect — BCNF also fails, but 3NF is the earlier failed level in the stated hierarchy.
B · incorrect — Atomic values satisfy 1NF.
C · incorrect — A single-attribute candidate key has no nonprime partial-key dependency.
D · correct — B→C violates 3NF because B is not a superkey and C is nonprime.
Question 129
Consider the relation Sale(Date, Customer, Product, Vendor, VendorCity, SalesRep){Date, Customer, Product} is the composite candidate key and the following functional dependencies are also given:Vendor => VendorCity, Product => VendorWhat is the highest normal form of the sale relation?
A. 0NF
B. 1NF
C. 2NF
D. 3NF
Hint / Module Reference (Where to Read)
Test determinants and prime attributes against the stated normal-form rules.
Assuming atomic values as customary for a relation, Product is a proper subset of the stated composite key and determines non-prime Vendor. Vendor→VendorCity adds another non-key dependency. Product→Vendor alone violates 2NF, so the highest supported form is 1NF. No stated dependency makes these non-prime attributes alternative keys.
Evidence in the complete study note
In Sale(Date,Customer,Product,Vendor,VendorCity,SalesRep), assume key (Date,Customer,Product), Product→Vendor and Vendor→VendorCity. Product is a proper subset of the key, so Vendor and VendorCity violate 2NF. With atomic values the highest form is 1NF, even though a transitive dependency also exists. 4NF addresses nontrivial multivalued dependencies with nonsuperkey determinants; 5NF addresses join dependencies. Do not confuse a multivalued dependency with a list stored inside a cell.FDB21
Choice by choice
A · incorrect — 0NF is not warranted when attributes are atomic. (PDF pp. 15, 17.)
B · correct — 1NF is the highest form under the explicit atomicity assumption. (PDF pp. 15, 17.)
C · incorrect — 2NF fails because Vendor depends on Product alone. (PDF pp. 15, 17.)
D · incorrect — 3NF requires 2NF, which already fails. (PDF pp. 15, 17.)
Question 130
Third normal form is based on the concept of _________
A. Closure Dependency
B. Transitive Dependency
C. Normal Dependency
D. Functional Dependency
Hint / Module Reference (Where to Read)
Test determinants and prime attributes against the stated normal-form rules.
The defining extra condition of 3NF over 2NF is non-transitive dependence of non-prime attributes on keys. Functional dependency is the broader framework, but transitive dependency is the particular concept distinguishing this stage.
Evidence in the complete study note
For every nontrivial FD X→A, 3NF permits it when X is a superkey or A is prime. BCNF requires X to be a superkey. The mnemonic whole key and nothing but the key describes the common single-key case; it cannot replace the full tests when keys overlap. Remove dependencies of nonprime facts on nonkey determinants.FDB21
With atomic values the highest form is 1NF, even though a transitive dependency also exists.FDB21
Do not confuse a multivalued dependency with a list stored inside a cell.FDB21
Choice by choice
A · incorrect — Closure dependency is not the stated criterion. (PDF pp. 18.)
B · correct — Transitive dependency is the specific concept addressed. (PDF pp. 18.)
C · incorrect — Normal dependency is not a defined dependency category. (PDF pp. 18.)
D · incorrect — Functional dependency is broader and does not identify the distinctive 3NF condition. (PDF pp. 18.)
Question 131
Which of the following statements does not change the state of the database?
A. Where
B. Select
C. Insert
D. Drop
Hint / Module Reference (Where to Read)
Trace how SELECT forms its result columns and rows.
The read-only transaction example uses SELECT to retrieve rows from two tables. SELECT is the complete retrieval statement among these choices; WHERE is only a clause and not a standalone SQL statement. The question is read as asking about valid statements.
Evidence in the complete study note
SELECT Name,Address FROM Customer; produces only those mailing-label fields. SELECT * selects all columns; DISTINCT removes duplicate result rows; AS supplies an alias; ALL permits duplicates and does not mean all columns. SQL normally preserves duplicates without DISTINCT. A query reading table columns needs an appropriate FROM source. A literal-only query such as SELECT 1 may legally omit FROM.FDB27
WHERE alone is a clause rather than a complete read-only statement, so it cannot be compared as an equivalent executable command.FDB27
Typical written clause order is SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY ….FDB27
Choice by choice
A · incorrect — WHERE alone is not a complete statement, so it cannot be the requested statement. (PDF pp. 52, 26.)
B · correct — SELECT is demonstrated in a read-only transaction. (PDF pp. 52, 26.)
C · incorrect — INSERT is a manipulation command, not the demonstrated read-only request. (PDF pp. 52, 26.)
D · incorrect — DROP is a structural DDL command, not the demonstrated retrieval. (PDF pp. 52, 26.)
Question 132
A relation is in 1NF and has no nonprime attribute partially dependent on any candidate key. What is the strongest normal form guaranteed by these facts alone?
A. First
B. BCNF
C. Third
D. Second
Hint / Module Reference (Where to Read)
Check atomicity, candidate keys and partial dependencies in that order.
The two stated conditions are exactly the requirements for 2NF: 1NF plus the absence of nonprime partial dependencies on candidate keys. First normal form is also true but is a weaker guarantee. Third normal form and BCNF need additional conditions on other functional dependencies. The relation might satisfy those higher forms, but the given facts alone establish only 2NF.
Evidence in the complete study note
1NF requires atomic values in the chosen relational domains and no repeating groups in cells. It does not mean merely declaring a primary key. A mapped ER design that correctly splits composite and multivalued structures gives ordinary 1NF tables; ER drawing alone guarantees no higher form.FDB20
2NF requires 1NF and no nonprime attribute partially dependent on any candidate key.FDB20
A relation whose every candidate key is single-attribute has no nonprime partial-key dependency.FDB20
Choice by choice
A · incorrect — 1NF is given, but 2NF is the stronger guaranteed form.
B · incorrect — BCNF additionally requires all nontrivial determinants to be superkeys.
C · incorrect — 3NF needs further checks on determinants and prime dependents.
D · correct — The conditions directly establish 2NF as the strongest guaranteed form.
Question 133
The minimal set of super key is called
A. Foreign key
B. None of the choices is correct
C. Candidate key
D. Primary key
Hint / Module Reference (Where to Read)
Test uniqueness and minimality separately when classifying keys.
A candidate key is a minimal superkey. Minimal means no removable attribute while retaining uniqueness, not necessarily the fewest attributes among all keys.
Evidence in the complete study note
A superkey uniquely identifies a tuple. A candidate key is a minimal superkey: removing any component loses uniqueness. Minimal means no unnecessary attribute, not necessarily the fewest columns among all keys. A primary key is one selected candidate key; remaining candidates are alternate keys. A composite key contains multiple attributes and need not be the chosen primary key.FDB07
Choice by choice
A · incorrect — Foreign key links relations, not the minimal-superkey definition. (PDF pp. 11.)
B · incorrect — A matching definition is offered. (PDF pp. 11.)
C · correct — Candidate key is the exact definition. (PDF pp. 11.)
D · incorrect — Primary key is the selected candidate key rather than the category of every minimal superkey. (PDF pp. 11.)
Question 134
Assume every foreign-key column is NOT NULL and references a declared primary key. Which statement expresses referential integrity under these assumptions?
A. Every primary-key value must match a primary-key value in an associated table
B. Every primary-key value must match a foreign-key value in an associated table
C. Every foreign-key value must match a primary-key value in an associated table
D. Every foreign-key value must match a foreign-key value in an associated table
Hint / Module Reference (Where to Read)
Match the validity requirement to the appropriate constraint definition.
Correct answer: C — C. Every foreign-key value must match a primary-key value in an associated table
A foreign key is a reference from a child row to an eligible key in the parent. Because the question makes every reference non-NULL and targets primary keys, each foreign-key value must match a primary-key value. A parent row need not have children, and primary keys do not themselves have to reference another table. Merely matching a foreign-key column is not sufficient.
Evidence in the complete study note
Domain integrity restricts type and allowed values. Key integrity enforces uniqueness. Entity integrity requires every primary-key component to be non-NULL. Referential integrity requires a non-NULL foreign-key value to match an eligible referenced unique/candidate key; self-references are allowed. A parent need not have a child.FDB15
Choice by choice
A · incorrect — A primary key can exist without referencing another table.
B · incorrect — A parent can have no child references.
C · correct — Every non-NULL child reference must match its targeted parent primary key.
D · incorrect — The target must be an eligible referenced key, not an arbitrary foreign-key column.
Question 135
Which of the following is generally used for performing tasks like creating the structure of the relations, deleting relation?
A. DML(Data Manipulation Language)
B. Query
C. Relational Schema
D. DDL(Data Definition Language)
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
Creating/deleting relation structure falls under DDL's structural command category. It is distinguished from data manipulation and from the schema description itself.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
Choice by choice
A · incorrect — DML is the data-operation category. (PDF pp. 26.)
B · incorrect — Query is not the schema-definition category. (PDF pp. 26.)
C · incorrect — A relational schema is a description, not a command language. (PDF pp. 26.)
D · correct — DDL is the named structural-language category. (PDF pp. 26.)
Question 136
Which of the following is used in the application programs to request data from the database management system?
A. Data Manipulation language
B. Data Definition Language
C. Data Control Language
D. All choices are correct
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
DML is the offered language category that includes retrieval requests such as SELECT. DDL handles definition, while authorization is separate from retrieval commands.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
Choice by choice
A · correct — DML includes retrieval. (PDF pp. 26, 24.)
B · incorrect — DDL is definition rather than ordinary data requests. (PDF pp. 26, 24.)
C · incorrect — Access-control language is not the category that retrieves the requested data. (PDF pp. 26, 24.)
D · incorrect — DDL and control are not all retrieval categories. (PDF pp. 26, 24.)
Question 137
Which single choice completely identifies the relational DBMS products in the list?
A. Oracle Database
B. Microsoft SQLServer
C. IBM Db2
D. All of the mentioned
Hint / Module Reference (Where to Read)
Compare the data models by how they organize records and relationships.
Oracle Database, Microsoft SQL Server and IBM Db2 are all relational DBMS products named in the note. Replacing the vendor-only IBM label with IBM Db2 makes every product entry well-defined. The question asks for the complete set, so All of the mentioned is the best answer. Each individual product is genuinely relational but identifies only part of the full set.
Evidence in the complete study note
PostgreSQL, MySQL, Oracle Database, Microsoft SQL Server and IBM Db2 are relational DBMS products. MongoDB is a document DBMS. Java is a programming language. IBM alone names a vendor, not a database product. Distinguish a vendor such as IBM from a specific database product.FDB02
Choice by choice
A · incorrect — Oracle Database is relational, but this alone is an incomplete set.
B · incorrect — SQL Server is relational, but this alone is an incomplete set.
C · incorrect — IBM Db2 is relational, but this alone is an incomplete set.
D · correct — The aggregate choice includes all three relational products.
Question 138
In ER diagram Diamond/Ellipses represent
A. Relationship sets/Attributes
B. Entity sets/Attributes
C. Attributes/Relationships sets
D. Relationship sets/Entity sets
Hint / Module Reference (Where to Read)
Review entity, attribute and relationship notation before interpreting the model.
FDB06 maps rectangles to entity types, diamonds to relationships and ellipses to attributes in Chen notation. The question asks for the diamond/ellipse pair in that order. A preserves that order and meaning; the other choices either substitute entity sets or reverse the symbol meanings. A diagram-specific legend would override the generic convention.
Evidence in the complete study note
An entity is a distinguishable real or conceptual thing, such as one student or one course. An entity type describes common properties; its current collection of entities is an entity set. A relationship associates entities. In Chen notation rectangles represent entity types, diamonds relationships and ellipses attributes; double ellipses denote multivalued attributes and dashed ellipses derived attributes. Always inspect the diagram legend.FDB06
Choice by choice
A · correct — The two shapes map to relationships and attributes respectively in Chen notation.
B · incorrect — Entity types use rectangles, so a diamond does not represent the first item listed.
C · incorrect — This reverses the requested meanings: diamonds indicate relationships, while ellipses indicate attributes.
D · incorrect — The first meaning fits, but ellipses represent attributes rather than entity sets.
Question 139
A relation is in 2NF if:
A. All the values of non-key attributes are dependent fully on the candidate key.
B. Any non-key attribute that are dependent on only part of the candidate key should be moved to another relation where the partial key is the actual full key.
C. It must be already in the 1NF.
D. All of the above.
Hint / Module Reference (Where to Read)
Check atomicity, candidate keys and partial dependencies in that order.
Second normal form requires 1NF and full dependence of each non-prime attribute on candidate keys; partial dependencies must be moved to another relation. A states the criterion, B the repair, and C the prerequisite; D collects all three. A alone is not a complete definition without C.
Evidence in the complete study note
2NF requires 1NF and no nonprime attribute partially dependent on any candidate key. For Enrollment(SID,CID,SName,CTitle,Grade), assume key (SID,CID), SID→SName and CID→CTitle. Split into Student(SID,SName), Course(CID,CTitle) and Enrollment(SID,CID,Grade). Grade still depends on the whole pair. A relation whose every candidate key is single-attribute has no nonprime partial-key dependency. Higher forms inherit 2NF, so no partial dependencies establishes the minimum guaranteed stage only when 1NF is also assumed.FDB20
Choice by choice
A · incorrect — Full dependency is a required 2NF property. (PDF pp. 17.)
B · incorrect — Moving a partially dependent attribute to the relation where its determinant is the whole key is the stated repair. (PDF pp. 17.)
C · incorrect — 1NF is expressly required. (PDF pp. 17.)
D · correct — All three agree with the module's definition and repair rule. (PDF pp. 17.)
Question 140
Which one of the following is NOT a single level ordered index?
A. Secondary index
B. Primary index
C. Clustering index
D. Multilevel index
Hint / Module Reference (Where to Read)
Compare index organization with its access benefits and maintenance costs.
In this single-level ordered-index classification, the listed types are primary, clustering and secondary indexes. A multilevel index is the exception to that listed single-level classification.
Evidence in the complete study note
In ordered-file indexing terminology, a primary ordered index is on the file’s ordering key and can have one entry per page, hence sparse. A clustering index is on a nonunique ordering field, often with an entry per distinct value. Secondary indexes provide other access paths and must represent all searchable values with record pointers or pointer lists. A dense index represents each search value; sparse indexes rely on ordered data between entries. Multilevel indexes index lower index levels. B/B+ trees support ordered and range search; hashing targets equality; bitmap indexes suit some analytic workloads. No one type is universally best.FDB25
Choice by choice
A · incorrect — Secondary is listed as single level. (PDF pp. 21.)
B · incorrect — Primary is listed as single level. (PDF pp. 21.)
C · incorrect — Clustering is listed as single level. (PDF pp. 21.)
D · correct — Multilevel is outside the specified single-level group. (PDF pp. 21.)
Question 141
Among the following, which one best describes a Database Management System (DBMS)?
A. It is responsible for creating and maintaining a database
B. It can help users to manage computer's activity
C. It can assist process images
D. It can assist you to process document
Hint / Module Reference (Where to Read)
Review the database approach and match each claimed benefit or cost to its mechanism.
Correct answer: A — It is responsible for creating and maintaining a database
A DBMS is software facilitating definition, construction, manipulation and sharing of databases. This directly matches creating and maintaining a database.
Evidence in the complete study note
A database is a coherent collection of related facts with meaning for an application. A DBMS is software that defines, stores, retrieves, updates, protects and shares that data. The database is the content; the DBMS manages it. A database system also includes applications, people and infrastructure.FDB01
Choice by choice
A · correct — Matches the module's DBMS definition. (PDF pp. 5.)
B · incorrect — Managing general computer activity describes a broader different role. (PDF pp. 5.)
C · incorrect — Image processing is not the database-management role. (PDF pp. 5.)
D · incorrect — Document processing is not the database-management definition. (PDF pp. 5.)
Question 142
Given the schemas: BOOK (Book_id, Title, Publisher_name), BOOK_AUTHOR (Book_id,
Author_name), PUBLISHER (Name, Address, Phone). Which one of the following SQL is correct to delete the
publisher named 'XWZ' from the PUBLISHER table?
A. DELETE FROM BOOK_AUTHOR WHERE Address = 'XWZ'
B. DELETE FROM PUBLISHER WHERE Name = 'XWZ'
C. DELETE FROM PUBLISHER WHERE Address = 'XWZ'
D. DELETE FROM BOOK WHERE Name = 'XWZ'
Hint / Module Reference (Where to Read)
Track which rows each data-changing command affects.
Correct answer: B — DELETE FROM PUBLISHER WHERE Name = 'XWZ'
The module illustrates DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 13 (PDF p.77). Substitute the requested relation PUBLISHER and its identifying Name predicate: DELETE FROM PUBLISHER WHERE Name = 'XWZ'. The stem's schemas distinguish Name from Address and omit both from BOOK_AUTHOR. This identifies the requested statement; successful execution may additionally depend on existing referencing rows and the deletion policy (PDF pp.48–49).
Evidence in the complete study note
INSERT INTO Hotel(HotelID,HotelName,SubCity) VALUES (1,'Hilton','Yeka'); pairs each named column with a compatible value. Counts and types must match; keys and other constraints still apply. Without a column list, values must follow the table's declared column order.FDB29
DELETE FROM Publisher WHERE Name = 'XWZ'; deletes matching publisher rows, subject to reference policies.FDB29
Both clear relation entries and delete rows describe that effect.FDB29
Choice by choice
A · incorrect — BOOK_AUTHOR is the wrong target and has no Address column in the supplied schema (compare the target/predicate form on PDF p.77).
B · correct — PUBLISHER is the target and Name is the requested name filter, matching the module's DELETE FROM ... WHERE example (PDF p.77).
C · incorrect — Filtering Address does not select by publisher name, despite a valid target table (PDF p.77's predicate pattern).
D · incorrect — BOOK is the wrong target and has no Name column in the supplied schema (PDF p.77's target/predicate pattern).
Question 143
What does program data independence in the database approach entail?
A. The application may be affected when data changes
B. Data and applications are defined separately
C. Programs can create redundant data independently
D. Both program and data are defined together
Hint / Module Reference (Where to Read)
Trace which schema levels a proposed change affects.
Correct answer: B — Data and applications are defined separately
Program/data insulation separates application definitions from data definitions, allowing schema changes without corresponding higher-level changes under the relevant independence conditions.
Evidence in the complete study note
Physical data independence permits changing internal storage without changing the conceptual schema, for example adding an index. Logical data independence permits conceptual changes while preserving affected external views and application interfaces, for example splitting a table while maintaining a compatible view. It is harder because applications depend on logical meaning. Data abstraction hides irrelevant details; program–data insulation separates data definitions from application code.FDB04
Choice by choice
A · incorrect — Says changes necessarily affect applications, contrary to insulation. (PDF pp. 5, 6, 8.)
B · correct — Separate definitions express program/data insulation. (PDF pp. 5, 6, 8.)
C · incorrect — Creating redundant data independently is not the defined independence. (PDF pp. 5, 6, 8.)
D · incorrect — Defining both together contradicts insulation. (PDF pp. 5, 6, 8.)
Question 144
Which statements are NOT correct descriptions of a fan trap or its general remedy in an ER diagram? Select all that apply. Treat B as a warning pattern, not a sufficient condition.
A. It occurs when there is no pathway between related entities
B. Highly likely when more than two one-to-many relationships fan out from an entity
C. When there is ambiguous pathway between some entity occurrences in related entity types
D. It can be resolved by readjusting the model so that no one-to-many relationships fan out from an entity
Hint / Module Reference (Where to Read)
Trace the actual relationship paths and check whether the requested association follows.
Correct answers (select all): A, D — A. It occurs when there is no pathway between related entities; D. It can be resolved by readjusting the model so that no one-to-many relationships fan out from an entity
A fan trap concerns an ambiguous association inferred through branches from a common entity. A missing or broken path is instead a chasm-type issue. Also, legitimate one-to-many branches need not all be eliminated: the model should record the actual missing association. Multiple branches can warn of a fan trap, and ambiguity is its defining issue, so B and C are not false.
Evidence in the complete study note
A fan trap occurs when two 1:N relationships fan from a common entity and a path suggests an association it cannot determine. Department→Employee and Department→Project do not reveal which employee works on which project; joining through Department pairs every employee with every project in that department. Record WorksOn explicitly when that is the fact needed.FDB14
A missing path describes a chasm, not the defining ambiguity of a fan.FDB14
Multiple 1:N relationships are legitimate; eliminating all fans is not a universal repair.FDB14
Choice by choice
A · correct — This is false about fan traps: a missing or broken path describes a chasm-type problem.
B · incorrect — Several 1:N branches from a common entity can create the ambiguous association characteristic of fan traps.
C · incorrect — Ambiguity about associations between entity occurrences is the defining fan-trap problem.
D · correct — This is false as a general remedy: valid 1:N fans may remain after explicitly modeling the required association.
Question 145
In which situation does using a DBMS become costly and not recommended?
A. In embedded systems having too small storage space
B. In situation where more than one user should access the database
C. To deny access to unauthorized users
D. To control redundancy
Hint / Module Reference (Where to Read)
Review the database approach and match each claimed benefit or cost to its mechanism.
Correct answer: A — Severely storage-constrained embedded systems can make a DBMS too costly.
FDB01 says storage, administration and complexity may outweigh DBMS benefits in a tiny fixed application, while also recognizing lightweight embedded DBMSs. A is therefore the listed situation where resource constraints can make a DBMS inappropriate. The other scenarios are central reasons for using a DBMS: sharing, authorization and redundancy control. Embedded use alone does not forbid a DBMS.
Evidence in the complete study note
A database is a coherent collection of related facts with meaning for an application. A DBMS is software that defines, stores, retrieves, updates, protects and shares that data. The database is the content; the DBMS manages it. A database system also includes applications, people and infrastructure.FDB01
Complexity, administration, storage and licensing can outweigh benefits for a tiny fixed application; lightweight embedded DBMSs also exist.FDB01
It controls redundancy rather than guaranteeing zero duplication.FDB01
Choice by choice
A · correct — Severely limited storage can make DBMS overhead unjustified, though lightweight embedded DBMSs may still fit.
B · incorrect — Controlled multiuser access is a benefit supplied by database management rather than a reason to avoid it.
C · incorrect — Authorization helps deny unauthorized access and is a reason to use DBMS protection.
D · incorrect — Controlling redundancy is a benefit of shared database management rather than a contraindication.
Question 146
Indexes in database tables speed up the _______ operations.
A. Read operations
B. Delete operations
C. Update operations
D. Write operations
Hint / Module Reference (Where to Read)
Compare index organization with its access benefits and maintenance costs.
Indexes are auxiliary access structures explicitly used to speed record retrieval for search conditions. This is a read-performance purpose; it does not claim that maintaining indexes makes every change operation faster.
Evidence in the complete study note
An index stores search keys with pointers or row identifiers. It often speeds selective retrieval but consumes space and must be maintained on writes. A full scan can win for a tiny table or a query returning most rows. Indexes do not themselves establish referential integrity.FDB25
Secondary indexes provide other access paths and must represent all searchable values with record pointers or pointer lists.FDB25
A dense index represents each search value; sparse indexes rely on ordered data between entries.FDB25
Choice by choice
A · correct — Read retrieval is the stated purpose. (PDF pp. 21.)
B · incorrect — Deletion may require maintenance; it is not the stated general benefit. (PDF pp. 21.)
C · incorrect — Updating data is not the stated general purpose. (PDF pp. 21.)
D · incorrect — Writing is not the stated retrieval purpose. (PDF pp. 21.)
Question 147
An e-commerce database already has a suitable normalized schema. Customer-order queries repeatedly select a small fraction of rows by a search key. Which technique directly adds a maintained search path for those lookups?
A. Normalization
B. Denormalization
C. Partitioning
D. Indexing
Hint / Module Reference (Where to Read)
Compare design techniques by the specific problem each solves.
Indexing stores search keys and pointers that support selective retrieval without scanning every row. That directly meets the requested search-path improvement. Normalization addresses dependency anomalies, denormalization stores redundant or precomputed facts, and partitioning divides a dataset. Those techniques can be useful elsewhere, but the specified task is to add an access path to the existing normalized schema.
Evidence in the complete study note
Normalization reduces dependency-driven anomalies. Indexing supplies search paths. Denormalization deliberately repeats or precomputes facts to reduce selected read costs, increasing maintenance work. Partitioning divides rows or columns; replication creates copies to improve locality or availability while complicating updates. These techniques can coexist. An e-commerce or gaming label alone does not select one.FDB38
An index stores search keys with pointers or row identifiers.FDB25
It often speeds selective retrieval but consumes space and must be maintained on writes.FDB25
Choice by choice
A · incorrect — Normalization organizes dependencies; it does not add the requested search path.
B · incorrect — Denormalization repeats or precomputes facts rather than directly adding an index path.
C · incorrect — Partitioning divides the dataset rather than adding the requested maintained search-key structure.
D · correct — Indexing directly supplies the selective lookup search path.
Question 148
Which SQL command is used to modify existing data in a table?
A. ALTER
B. UPDATE
C. MODIFY
D. CHANGE
Hint / Module Reference (Where to Read)
Track which rows each data-changing command affects.
UPDATE changes existing row values using a target table, SET assignments and an optional WHERE predicate. ALTER instead changes an object's definition. The bare words MODIFY and CHANGE do not form the UPDATE statement required for changing stored row values in the stated SQL forms. They should not be treated as synonyms for the command merely because they describe the task in everyday language. B names the actual row-update command.
Evidence in the complete study note
UPDATE Book SET Price = 600 WHERE ID = 'B001'; changes matching rows. UPDATE without WHERE targets every row.FDB29
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE.FDB26
Choice by choice
A · incorrect — Incorrect: ALTER changes schema definitions rather than stored row values.
B · correct — Correct: UPDATE changes existing rows using SET and, when needed, WHERE.
C · incorrect — Incorrect: MODIFY is not the UPDATE command required for the stated row-value operation.
D · incorrect — Incorrect: CHANGE is not the UPDATE command required for the stated row-value operation.
Question 149
Which is the first normal form in the usual normalization sequence that requires atomic values and no repeating groups?
A. Second Normal Form (2NF)
B. Third Normal Form (3NF)
C. Boyce–Codd Normal Form (BCNF)
D. First Normal Form (1NF)
Hint / Module Reference (Where to Read)
Check atomicity, candidate keys and partial dependencies in that order.
First normal form is the first stage requiring atomic values and no repeating groups in cells. It does not by itself remove partial or transitive functional dependencies. Second normal form adds the full-dependency condition, third normal form adds the determinant/prime-attribute rule, and BCNF requires every nontrivial determinant to be a superkey. These stronger forms inherit atomicity, but the question asks for the first stage defining it.
Evidence in the complete study note
1NF requires atomic values in the chosen relational domains and no repeating groups in cells.FDB20
2NF requires 1NF and no nonprime attribute partially dependent on any candidate key.FDB20
For every nontrivial FD X→A, 3NF permits it when X is a superkey or A is prime. BCNF requires X to be a superkey. The mnemonic whole key and nothing but the key describes the common single-key case; it cannot replace the full tests when keys overlap. Remove dependencies of nonprime facts on nonkey determinants.FDB21
Choice by choice
A · incorrect — Incorrect: 2NF inherits atomicity and additionally excludes nonprime partial dependencies; it is a later stage.
B · incorrect — Incorrect: 3NF inherits the earlier atomicity condition and adds a stronger dependency test.
C · incorrect — Incorrect: BCNF adds the superkey-determinant rule and is not the first stage requiring atomicity.
D · correct — Correct: 1NF is the first stage defining atomic values and no repeating groups.
Question 150
What is the purpose of normalization in database design?
A. To increase data redundancy
B. To decrease data integrity
C. To eliminate data duplication
D. To simplify data retrieval
Alternative version 1
A. To reduce redundancy and improve data integrity
B. To speed up query performance
C. To create complex relationships between tables
D. To encrypt sensitive data
Hint / Module Reference (Where to Read)
Distinguish dependency-based design problems from the contents of one sample table.
Normalization systematically decomposes relations to eliminate or reduce duplication and insertion/update/deletion anomalies. Eliminate is understood as the design goal, not a claim that every possible repeated value or necessary key reference disappears.
Evidence in the complete study note
Suppose Enrollment repeats StudentName for every course. An update anomaly requires changing every copy of the student's name; missed copies disagree. An insertion anomaly prevents recording a new student until some enrollment exists. A deletion anomaly loses the only record of a student's details when their last enrollment is deleted. Revoke anomaly is not a normalization anomaly.FDB19
Adding a generated row ID may make insertion technically possible, but does not remove repeated facts or fix the dependency design.FDB19
Reduced redundancy can improve integrity and storage use, but extra keys and joins mean neither storage savings nor faster retrieval is guaranteed.FDB19
Choice by choice
A · incorrect — Increasing redundancy reverses the goal. (PDF pp. 16.)
B · incorrect — Decreasing integrity reverses the anomaly-prevention goal. (PDF pp. 16.)
C · correct — Eliminating duplicated data is directly stated. (PDF pp. 16.)
D · incorrect — Simplifying retrieval is not the stated normalization purpose and does not follow automatically from decomposition. (PDF pp. 16.)
Alternative version 1
Correct answer: A — To reduce redundancy and improve data integrity
Normalization systematically decomposes relations to eliminate or reduce duplication and insertion/update/deletion anomalies. Eliminate is understood as the design goal, not a claim that every possible repeated value or necessary key reference disappears.
Suppose Enrollment repeats StudentName for every course. An update anomaly requires changing every copy of the student's name; missed copies disagree. An insertion anomaly prevents recording a new student until some enrollment exists. A deletion anomaly loses the only record of a student's details when their last enrollment is deleted. Revoke anomaly is not a normalization anomaly.FDB19
Reduced redundancy can improve integrity and storage use, but extra keys and joins mean neither storage savings nor faster retrieval is guaranteed.FDB19
Adding a generated row ID may make insertion technically possible, but does not remove repeated facts or fix the dependency design.FDB19
A · correct — Reducing redundancy and preventing anomalies is the normalization goal (PDF p.16).
B · incorrect — Query performance is not the stated normalization purpose; decomposition can require additional joins.
C · incorrect — Relationships support decomposition but creating complexity is not its goal.
D · incorrect — Encryption is a security mechanism, not normalization.
Question 151
Which of the following IS NOT a type of database management system (DBMS)?
A. PostgreSQL
B. MongoDB
C. Java
D. MySQL
Hint / Module Reference (Where to Read)
Compare the data models by how they organize records and relationships.
Correct answer: C — Java is a programming language, not a DBMS.
The question uses type loosely to list named systems. FDB02 identifies PostgreSQL and MySQL as relational DBMS products and MongoDB as a document DBMS. Java is explicitly a programming language. Its ability to connect to databases does not make it a database management system, so C alone fits the negative question.
Evidence in the complete study note
PostgreSQL, MySQL, Oracle Database, Microsoft SQL Server and IBM Db2 are relational DBMS products. MongoDB is a document DBMS. Java is a programming language. IBM alone names a vendor, not a database product. Distinguish a vendor such as IBM from a specific database product.FDB02
Choice by choice
A · incorrect — PostgreSQL is a relational DBMS product, so it does not satisfy the negative question.
B · incorrect — MongoDB is a document DBMS, even though it is not a relational system.
C · correct — Java is a programming language rather than a database management system.
D · incorrect — MySQL is a relational DBMS product and therefore is not the requested exception.
Question 152
The E-R data model is based on a perception of the real world that consists of a set of basic
objects called ______.
A. Objects
B. Classes
C. Entities
D. Attributes
Hint / Module Reference (Where to Read)
Review entity, attribute and relationship notation before interpreting the model.
The ER model uses entities, attributes and relationships; the actual physical/conceptual objects are entities. Objects is a generic word, while entities is the model-specific category requested.
Evidence in the complete study note
An entity is a distinguishable real or conceptual thing, such as one student or one course. An entity type describes common properties; its current collection of entities is an entity set. A relationship associates entities. In Chen notation rectangles represent entity types, diamonds relationships and ellipses attributes; double ellipses denote multivalued attributes and dashed ellipses derived attributes. Always inspect the diagram legend.FDB06
Choice by choice
A · incorrect — Objects is the generic description, not the requested ER term. (PDF pp. 9, 10.)
B · incorrect — Classes are not the basic-object term in this ER definition. (PDF pp. 9, 10.)
C · correct — Entities is the named category for those real-world objects. (PDF pp. 9, 10.)
D · incorrect — Attributes are properties of entities rather than the objects themselves. (PDF pp. 9, 10.)
Question 153
What is the primary key in a relational database?
A. A unique identifier for each row in a table
B. A foreign key from another table
C. A key used for encryption
D. An index on a non-unique column
Hint / Module Reference (Where to Read)
Test uniqueness and minimality separately when classifying keys.
Correct answer: A — A unique identifier for each row in a table
A primary key is the selected candidate key used to identify tuples uniquely; its values must be unique and non-null. The key may consist of more than one attribute.
Evidence in the complete study note
A superkey uniquely identifies a tuple. A candidate key is a minimal superkey: removing any component loses uniqueness. Minimal means no unnecessary attribute, not necessarily the fewest columns among all keys. A primary key is one selected candidate key; remaining candidates are alternate keys. A composite key contains multiple attributes and need not be the chosen primary key.FDB07
A table can have several candidates and foreign keys but at most one declared primary-key constraint, possibly containing several columns.FDB07
If ID is unique, both {ID} and {ID,City} are superkeys, but only {ID} is minimal.FDB07
Choice by choice
A · correct — Uniquely identifies each row, consistent with the key definition. (PDF pp. 11, 14.)
B · incorrect — A foreign key serves a reference role and need not be this table's primary key. (PDF pp. 11, 14.)
C · incorrect — Encryption keys have a different security role. (PDF pp. 11, 14.)
D · incorrect — A non-unique index does not meet key uniqueness. (PDF pp. 11, 14.)
Question 154
What is the role of a database administrator (DBA)?
A. Designing user interfaces for database applications
B. Developing front-end applications
C. Writing complex SQL queries
D. Managing and maintaining databases
Hint / Module Reference (Where to Read)
Review the database approach and match each claimed benefit or cost to its mechanism.
Correct answer: D — Managing and maintaining databases
The DBA administers the database and DBMS, authorizes access, coordinates/monitors use and acquires resources. Those responsibilities fit management and maintenance.
Evidence in the complete study note
The DBA manages access authorization, backup/recovery, availability, monitoring, tuning and routine maintenance, coordinating database use; designers define the schema and application developers build user-facing programs.FDB01
Choice by choice
A · incorrect — User-interface design is not the defining DBA responsibility described. (PDF pp. 6.)
B · incorrect — Front-end development is not the defining responsibility. (PDF pp. 6.)
C · incorrect — Writing a difficult query may occur, but is not the administration role defined. (PDF pp. 6.)
D · correct — Management and maintenance match the listed administration duties. (PDF pp. 6.)
Question 155
Which of the following IS NOT a type of database model?
A. Linear
B. Object-Oriented
C. Hierarchical
D. Relational
Hint / Module Reference (Where to Read)
Compare the data models by how they organize records and relationships.
The model taxonomy names object-oriented, hierarchical/network, and relational concepts. Linear is not among the stated database-model categories; this is a taxonomy question within that stated list, not a universal claim about every possible model name.
Evidence in the complete study note
A data model supplies concepts for structure, relationships, constraints and operations. Conceptual ER models describe entities and relationships. Relational models use tables; hierarchical models organize records in a parent–child tree; classic network models connect records through pointer-based owner/member links, allowing multiple parents. Object models represent objects with identity, state and behavior. A physical model specifies record layouts and access paths. These are different representation choices, not stages of one query.FDB02
Choice by choice
A · correct — Linear is outside the supplied taxonomy. (PDF pp. 7, 13.)
B · incorrect — Object-Oriented is explicitly a conceptual-model example. (PDF pp. 7, 13.)
C · incorrect — Hierarchical is named in the model discussion. (PDF pp. 7, 13.)
D · incorrect — Relational is explicitly defined as a database model. (PDF pp. 7, 13.)
Question 156
A relation has atomic values with no repeating groups, and no nonprime attribute partially depends on any candidate key. What is the strongest normal form guaranteed by these facts alone?
A. 4NF
B. 2NF
C. 1NF
D. 3NF
Hint / Module Reference (Where to Read)
Check atomicity, candidate keys and partial dependencies in that order.
Atomic values without repeating groups establish 1NF. Adding the absence of nonprime partial dependencies establishes 2NF. Higher normal forms have additional requirements involving nonkey determinants or multivalued dependencies, and none of those requirements follows from the stated facts. Thus 2NF is the strongest guaranteed level, even though the relation could also happen to satisfy higher forms.
Evidence in the complete study note
1NF requires atomic values in the chosen relational domains and no repeating groups in cells. It does not mean merely declaring a primary key. A mapped ER design that correctly splits composite and multivalued structures gives ordinary 1NF tables; ER drawing alone guarantees no higher form.FDB20
2NF requires 1NF and no nonprime attribute partially dependent on any candidate key.FDB20
Higher forms inherit 2NF, so no partial dependencies establishes the minimum guaranteed stage only when 1NF is also assumed.FDB20
Choice by choice
A · incorrect — No restriction on nontrivial multivalued dependencies has been established.
B · correct — 1NF plus the absence of nonprime partial dependencies guarantees 2NF.
C · incorrect — 1NF holds, but 2NF is the stronger guarantee.
D · incorrect — 3NF requires additional determinant/prime-attribute conditions.
Question 157
Which one of the following is not among the characterizing features of database approach?
A. Sharing of data
B. @-tTM Application-data dependency
C. j Oc. Self-describing
D. Data abstraction
Hint / Module Reference (Where to Read)
Review the database approach and match each claimed benefit or cost to its mechanism.
Correct answer: B — @-tTM Application-data dependency
Database systems support sharing, self-description and abstraction/insulation. Application-data dependency is the opposite of insulation and independence.
Evidence in the complete study note
A database is a coherent collection of related facts with meaning for an application. A DBMS is software that defines, stores, retrieves, updates, protects and shares that data. The database is the content; the DBMS manages it. A database system also includes applications, people and infrastructure.FDB01
Data abstraction hides irrelevant details; program–data insulation separates data definitions from application code.FDB04
Logical data independence permits conceptual changes while preserving affected external views and application interfaces, for example splitting a table while maintaining a compatible view.FDB04
Choice by choice
A · incorrect — Sharing is expressly a characteristic. (PDF pp. 5, 6.)
B · correct — Application-data dependency contradicts the stated insulation characteristic. (PDF pp. 5, 6.)
C · incorrect — Self-description is expressly a characteristic. (PDF pp. 5, 6.)
D · incorrect — Abstraction is expressly a characteristic. (PDF pp. 5, 6.)
Question 158
"Database is a logically coherent collection of data with some inherent meaning" describes that
A. the entity should be physical object
B. number of attributes should be limited
C. there should be one primary key attribute
D. all attributes of the entity should be related
Hint / Module Reference (Where to Read)
Review the database approach and match each claimed benefit or cost to its mechanism.
Correct answer: D — all attributes of the entity should be related
A database is a collection of related data with implicit meaning. D is the closest reading of coherence in the question: attributes should describe related subject matter, not arbitrary unrelated data. It does not mean every attribute functionally determines every other.
Evidence in the complete study note
A database is a coherent collection of related facts with meaning for an application. A DBMS is software that defines, stores, retrieves, updates, protects and shares that data. The database is the content; the DBMS manages it. A database system also includes applications, people and infrastructure.FDB01
A composite key contains multiple attributes and need not be the chosen primary key.FDB07
An entity type describes common properties; its current collection of entities is an entity set.FDB06
Choice by choice
A · incorrect — Entities may have conceptual existence too. (PDF pp. 5, 10, 11.)
B · incorrect — No bound on the number of attributes is implied. (PDF pp. 5, 10, 11.)
C · incorrect — A primary key may have multiple attributes; coherence does not require a single attribute. (PDF pp. 5, 10, 11.)
D · correct — Related attributes are the offered interpretation consistent with meaningful related data. (PDF pp. 5, 10, 11.)
Question 159
For HOTEL(Hotel_id INTEGER PRIMARY KEY, Hotel_Name STRING, Sub_city STRING), which statement correctly inserts one row? Assume Hotel_id = 1 is unused and no other constraints prevent the insert.
A. INSERT INTO HOTEL(Hotel_id,Hotel_Name,Sub_city) Values('Hilton', Yeka')
B. INSERT INTO HOTEL(Hotel_id,Hotel_Name,Sub_city) Values (1,'Hilton','Yeka')
C. INSERT INTO HOTEL(1,'Hilton','Yeka')
D. INSERT Values INTO HOTEL(Hotel_id,Hotel_Name,Sub_city) Values(1,'Hilton','Yeka')
Hint / Module Reference (Where to Read)
Track which rows each data-changing command affects.
Correct answer: B — INSERT INTO HOTEL(Hotel_id,Hotel_Name,Sub_city) Values (1,'Hilton','Yeka')
An INSERT must match each named column with a compatible value and follow INSERT INTO ... VALUES syntax. B supplies the integer identifier and the two text values in the named order; the stated unused-key assumption removes a possible primary-key conflict. A supplies too few values and has damaged string quoting. C puts values where column names belong and omits VALUES, while D places an extra Values token before INTO. These are incorrect statements, not unresolved alternatives.
Evidence in the complete study note
INSERT INTO Hotel(HotelID,HotelName,SubCity) VALUES (1,'Hilton','Yeka'); pairs each named column with a compatible value. Counts and types must match; keys and other constraints still apply. Without a column list, values must follow the table's declared column order.FDB29
Domain integrity restricts type and allowed values. Key integrity enforces uniqueness. Entity integrity requires every primary-key component to be non-NULL. Referential integrity requires a non-NULL foreign-key value to match an eligible referenced unique/candidate key; self-references are allowed. A parent need not have a child.FDB15
Choice by choice
A · incorrect — Incorrect: three named columns require three compatible values; this text supplies only two and also has malformed quoting.
B · correct — Correct: it supplies an unused integer key and two strings in the named column order with the required INSERT syntax.
C · incorrect — Incorrect: literal values occupy the column-list position and the VALUES clause is absent.
D · incorrect — Incorrect: INSERT Values INTO has an extra token before INTO and breaks the required INSERT structure.
Question 160
DORM(DormID, FloorNumber) has primary key DormID. STUDENT(IdNo, Name, Department, DormID) has primary key IdNo. Which table-constraint fragment correctly declares STUDENT.DormID as a reference to DORM? Capacity six is enforced separately; the surrounding CREATE TABLE syntax is supplied.
A. FOREIGN KEY (DormID) REFERENCED BY DORM(DormID)
B. DORM table is a referencing relation
C. FOREIGN KEY (DormID) REFERENCES DORM(DormID)
D. STUDENT table is a referenced relation
Hint / Module Reference (Where to Read)
Trace keys and cardinalities through the integrated schema.
Correct answer: C — C. FOREIGN KEY (DormID) REFERENCES DORM(DormID)
The student row stores the dorm identifier, making STUDENT the referencing child and DORM the referenced parent. The required clause names the child column and uses REFERENCES to identify Dorm's key. C has this syntax after the naming repair. The foreign key validates a dorm reference; it does not by itself enforce the six-student capacity rule, which the question handles separately.
Evidence in the complete study note
Use Department(DeptID,Name), Student(SID,Name,DeptID,DormID), Course(CID,Title), Enrollment(SID,CID,Grade) and Dorm(DormID,FloorNumber). Declare all entity IDs primary keys; Student.DeptID and Student.DormID reference their parents; Enrollment has composite key (SID,CID) and both references. Make DormID nullable until assignment. Separate StudentPhone(SID,Phone) for multiple contacts. Normalize repeated department/course facts into their owning tables. A simple foreign key does not enforce the six-student capacity; use a concurrency-safe trigger, allocation transaction or explicit slot design with six unique slots per dorm.FDB39
STUDENT is the referencing child; DORM is referenced.FDB39
Consistent names and an added Student.DormID column are required before execution.FDB39
Choice by choice
A · incorrect — REFERENCED BY is not the required child-to-parent clause.
B · incorrect — DORM is the referenced parent, not the referencing child.
C · correct — REFERENCES DORM(DormID) correctly targets the parent key.
D · incorrect — STUDENT is the referencing child.
Question 161
Which one of the following is an appropriate sequence of database design processes?
A. Logical database design, Enterprise data modeling, Physical database design, Database implementation
B. Enterprise data modeling, Logical database design, Database implementation, Physical database design
C. Logical database design, Enterprise data modeling, Physical database design, Database implementation
D. Enterprise data modeling, Logical database design, Physical database design, Database implementation
Hint / Module Reference (Where to Read)
Identify the design phase from its inputs and intended deliverable.
FDB05 starts with business requirements and conceptual ER modeling, then maps to relations and constraints in logical design, chooses storage and indexes in physical design, and creates/loads actual objects in implementation. D preserves that order. The other choices either begin logical design before enterprise modeling or place implementation before the storage decisions it must implement.
Evidence in the complete study note
First collect business facts, required transactions, reports, volumes, privacy rules and quality targets. Conceptual design turns them into an ER/EER model. Logical design maps the conceptual model to relations, keys and constraints and checks normalization. Physical design chooses file organization, indexes and storage. Implementation creates and loads the actual objects; testing checks requirements.FDB05
Choice by choice
A · incorrect — Logical design should use the conceptual enterprise model rather than precede its creation.
B · incorrect — Physical storage decisions precede implementation, not follow it in the stated design sequence.
C · incorrect — This duplicates A and again places logical design before enterprise modeling.
D · correct — It follows conceptual enterprise modeling, relational mapping, storage design and actual implementation.
Question 162
Which of the following is a primary key in a relational database?
A. A unique identifier for a row in a table
B. A foreign key from another table
C. A non-unique attribute in a table
D. A combination of attributes in a table
Hint / Module Reference (Where to Read)
Test uniqueness and minimality separately when classifying keys.
Correct answer: A — A unique identifier for a row in a table
A primary key is selected from candidate keys to uniquely identify a row and must be unique/non-null. Multiple attributes alone do not make a set a primary key unless those requirements and selection hold.
Evidence in the complete study note
A superkey uniquely identifies a tuple. A candidate key is a minimal superkey: removing any component loses uniqueness. Minimal means no unnecessary attribute, not necessarily the fewest columns among all keys. A primary key is one selected candidate key; remaining candidates are alternate keys. A composite key contains multiple attributes and need not be the chosen primary key.FDB07
A table can have several candidates and foreign keys but at most one declared primary-key constraint, possibly containing several columns.FDB07
PRIMARY KEY implies uniqueness and non-NULL.FDB16
Choice by choice
A · correct — Gives the essential identifying property. (PDF pp. 11, 14.)
B · incorrect — A foreign key is a reference and is not necessarily the selected primary identifier. (PDF pp. 11, 14.)
C · incorrect — Non-unique attributes cannot uniquely identify rows. (PDF pp. 11, 14.)
D · incorrect — A combination can be a composite primary key, but an arbitrary combination is not sufficient. (PDF pp. 11, 14.)
Question 163
What is the purpose of foreign keys in a relational database?
A. To establish relationships between tables
B. To store encrypted data
C. To enforce data types in columns
D. To improve query performance
Hint / Module Reference (Where to Read)
Match the validity requirement to the appropriate constraint definition.
Correct answer: A — To establish relationships between tables
Foreign keys link child records to matching parent keys and preserve consistency between related tables.
Evidence in the complete study note
Domain integrity restricts type and allowed values. Key integrity enforces uniqueness. Entity integrity requires every primary-key component to be non-NULL. Referential integrity requires a non-NULL foreign-key value to match an eligible referenced unique/candidate key; self-references are allowed. A parent need not have a child.FDB15
A primary key is one selected candidate key; remaining candidates are alternate keys.FDB07
Minimal means no unnecessary attribute, not necessarily the fewest columns among all keys.FDB07
Choice by choice
A · correct — Establishes the described reference relationship. (PDF pp. 48, 14.)
B · incorrect — Encryption is a security mechanism, not the FK purpose. (PDF pp. 48, 14.)
C · incorrect — Data types are controlled by domain constraints. (PDF pp. 48, 14.)
D · incorrect — Performance improvement is not the stated FK purpose. (PDF pp. 48, 14.)
Question 164
In the usual normalization sequence, which is the first normal form requiring every nonprime attribute to depend fully on every candidate key, rather than on a proper subset?
A. First normal form (1NF)
B. Second normal form (2NF)
C. Third normal form (3NF)
D. Boyce-Codd normal form (BCNF)
Hint / Module Reference (Where to Read)
Check atomicity, candidate keys and partial dependencies in that order.
Second normal form is the first stage requiring atomic relations to have no nonprime attribute partially dependent on any candidate key. First normal form addresses atomic values, so it does not impose that dependency rule. Third normal form and BCNF are stronger stages that inherit the absence of partial dependencies, but they are not the first stage introducing it. BCNF specifically requires every nontrivial functional-dependency determinant to be a superkey.
Evidence in the complete study note
1NF requires atomic values in the chosen relational domains and no repeating groups in cells.FDB20
2NF requires 1NF and no nonprime attribute partially dependent on any candidate key.FDB20
For every nontrivial FD X→A, 3NF permits it when X is a superkey or A is prime. BCNF requires X to be a superkey. The mnemonic whole key and nothing but the key describes the common single-key case; it cannot replace the full tests when keys overlap. Remove dependencies of nonprime facts on nonkey determinants.FDB21
Choice by choice
A · incorrect — Incorrect: 1NF requires atomic values but does not exclude partial dependencies.
B · correct — Correct: 2NF is the first stage excluding nonprime partial dependencies on candidate keys.
C · incorrect — Incorrect: 3NF inherits 2NF and imposes its additional determinant/prime-attribute condition; it is not the first stage.
D · incorrect — Incorrect: BCNF requires every nontrivial determinant to be a superkey; it is stronger than the first stage imposing the partial-dependency rule.
Question 165
What defines a composite key in database design?
A. A key made up of two or more attributes
B. A foreign key that references multiple tables
C. A unique identifier for a column in a table
D. A key used for data encryption
Hint / Module Reference (Where to Read)
Test uniqueness and minimality separately when classifying keys.
Correct answer: A — A. A key made up of two or more attributes
Composite describes the number of attributes in a key: it contains two or more attributes. It does not mean the key has necessarily been selected as the primary key, nor does it refer to the number of tables reached by a reference. A column name alone is not a multicolumn identification key. Encryption uses a different meaning of key.
Evidence in the complete study note
A superkey uniquely identifies a tuple. A candidate key is a minimal superkey: removing any component loses uniqueness. Minimal means no unnecessary attribute, not necessarily the fewest columns among all keys. A primary key is one selected candidate key; remaining candidates are alternate keys. A composite key contains multiple attributes and need not be the chosen primary key.FDB07
A composite-key example must include multiple columns; a single-column key is not composite.FDB07
A table can have several candidates and foreign keys but at most one declared primary-key constraint, possibly containing several columns.FDB07
Choice by choice
A · correct — Two or more key attributes are the defining feature.
B · incorrect — The number of referenced tables does not define composition.
C · incorrect — A column identifier does not describe a multicolumn key.
D · incorrect — An encryption key is a different concept.
Question 166
What is the purpose of an index in a database?
A. To store large binary objects
B. To enforce referential integrity
C. To improve data retrieval performance
D. To encrypt sensitive data
Hint / Module Reference (Where to Read)
Compare index organization with its access benefits and maintenance costs.
Correct answer: C — To improve data retrieval performance
Indexes are auxiliary structures used to speed retrieval for search conditions, rather than stored binary values or security mechanisms.
Evidence in the complete study note
An index stores search keys with pointers or row identifiers. It often speeds selective retrieval but consumes space and must be maintained on writes. A full scan can win for a tiny table or a query returning most rows. Indexes do not themselves establish referential integrity.FDB25
DATE represents a date and BLOB binary content such as images.FDB23
Domain integrity restricts type and allowed values.FDB15
Choice by choice
A · incorrect — BLOB is the named large-binary-object type. (PDF pp. 21, 19.)
B · incorrect — Foreign keys handle referential links; indexes are defined for retrieval. (PDF pp. 21, 19.)
C · correct — Exactly the stated retrieval-performance purpose. (PDF pp. 21, 19.)
D · incorrect — Encryption is a separate security mechanism. (PDF pp. 21, 19.)
Question 167
What is a data dictionary in database design?
A. A database table that stores user information
B. A collection of tables in a database
C. A file system used to store database files
D. A repository of metadata about the database structure
Hint / Module Reference (Where to Read)
Distinguish a relation's structure from its current contents and its descriptive metadata.
Correct answer: D — A repository of database metadata.
A data dictionary describes the database rather than serving as ordinary application data storage. FDB03 says it records definitions, types, constraints and often privileges and optimizer statistics. User information may appear in an application table, but that is not the dictionary definition. Nor is a dictionary simply all tables or the physical file system.
Evidence in the complete study note
A schema describes names, structures and constraints; an instance, state or snapshot is the data at a particular time. Inserting a student changes the state, while adding a column changes the schema. Metadata is data describing data. The catalog or data dictionary records table definitions, types, constraints and often access privileges and optimizer statistics. A schema diagram draws tables and key references; labels such as database diagram can overlap.FDB03
Choice by choice
A · incorrect — Ordinary user records are application data, not the defining purpose of a metadata repository.
B · incorrect — A collection of tables is not specifically the repository describing those tables and constraints.
C · incorrect — The physical file system stores files; a dictionary records descriptive database metadata.
D · correct — The dictionary is the repository describing database structure, data types and constraints.
Question 168
Which of the following is an example of a database constraint?
A. UNIQUE
B. SELECT
C. INSERT
D. DELETE
Hint / Module Reference (Where to Read)
Read each SQL constraint's scope and enforcement rule.
Uniqueness constrains distinct tuple identifiers, whereas SELECT, INSERT and DELETE are language operations in the DML list. UNIQUE is the offered constraint name matching that property.
Evidence in the complete study note
Example: CREATE TABLE Enrollment (StudentID INTEGER NOT NULL REFERENCES Student(StudentID), CourseID INTEGER NOT NULL REFERENCES Course(CourseID), Grade INTEGER CHECK (Grade BETWEEN 0 AND 100), PRIMARY KEY (StudentID,CourseID)); Each column declaration gives its name then type, with commas between declarations. CHECK is a constraint clause, not a standalone GRANT-like statement. POSITIVE is not a general SQL constraint keyword; CHECK (Amount > 0) expresses positivity.FDB16
DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE.FDB26
Some texts separate SELECT as DQL.FDB26
Choice by choice
A · correct — Uniqueness is explicitly an integrity condition. (PDF pp. 14, 26.)
B · incorrect — SELECT is a retrieval command. (PDF pp. 14, 26.)
C · incorrect — INSERT is a manipulation command. (PDF pp. 14, 26.)
D · incorrect — DELETE is a manipulation command. (PDF pp. 14, 26.)
Question 169
What is the purpose of referential integrity in a database?
A. To prevent unauthorized access to data
B. To ensure data consistency and integrity across related tables
C. To improve query performance
D. To encrypt sensitive data
Alternative version 1
A. To prevent unauthorized access to data
B. To ensure data consistency and integrity
C. To improve query performance
D. To encrypt sensitive data
Hint / Module Reference (Where to Read)
Match the validity requirement to the appropriate constraint definition.
Correct answer: B — To ensure data consistency and integrity across related tables
Referential integrity maintains consistency between related tuples by requiring non-null child references to resolve to existing parent keys. It controls valid relationships rather than authorization, performance, or encoding.
Evidence in the complete study note
Domain integrity restricts type and allowed values. Key integrity enforces uniqueness. Entity integrity requires every primary-key component to be non-NULL. Referential integrity requires a non-NULL foreign-key value to match an eligible referenced unique/candidate key; self-references are allowed. A parent need not have a child.FDB15
Both encryption and access control can protect patient data.FDB38
Authentication verifies identity; role-based authorization limits allowed records/actions; encryption protects data at rest or in transit; auditing records activity.FDB38
Choice by choice
A · incorrect — Unauthorized access is a security issue. (PDF pp. 14, 48.)
B · correct — Matches the module's consistency-between-relations definition. (PDF pp. 14, 48.)
C · incorrect — Query performance is not the referential validity rule. (PDF pp. 14, 48.)
D · incorrect — Encryption is a separate security mechanism. (PDF pp. 14, 48.)
Alternative version 1
Correct answer: B — To ensure data consistency and integrity
Referential integrity maintains consistency between related tuples by requiring non-null child references to resolve to existing parent keys. It controls valid relationships rather than authorization, performance, or encoding.
Domain integrity restricts type and allowed values. Key integrity enforces uniqueness. Entity integrity requires every primary-key component to be non-NULL. Referential integrity requires a non-NULL foreign-key value to match an eligible referenced unique/candidate key; self-references are allowed. A parent need not have a child.FDB15
Scalability, availability, fast retrieval, redundancy control and security are all reasonable design considerations.FDB38
Authentication verifies identity; role-based authorization limits allowed records/actions; encryption protects data at rest or in transit; auditing records activity.FDB38
Correct answer: B — Deliberately adding redundancy to improve selected retrieval workloads.
FDB38 defines denormalization as deliberately repeating or precomputing facts to reduce selected read costs, with extra maintenance work. It is a workload-driven trade-off rather than elimination of all duplication. Breaking tables apart typically describes normalization; changing storage format is a different concern. Thus B captures both the mechanism and its intended retrieval benefit.
Evidence in the complete study note
Normalization reduces dependency-driven anomalies. Indexing supplies search paths. Denormalization deliberately repeats or precomputes facts to reduce selected read costs, increasing maintenance work. Partitioning divides rows or columns; replication creates copies to improve locality or availability while complicating updates. These techniques can coexist. An e-commerce or gaming label alone does not select one.FDB38
Choice by choice
A · incorrect — Splitting tables according to dependencies is normalization, rather than the redundancy introduced by denormalization.
B · correct — Denormalization deliberately repeats or precomputes information to improve selected retrieval workloads.
C · incorrect — Eliminating duplication describes a different direction from deliberately introducing useful redundant facts.
D · incorrect — Changing storage format does not define the dependency and redundancy trade-off of denormalization.
Question 171
What is the purpose of a view in a database?
A. To define the structure of a table
B. To store complex queries for reuse
C. To encrypt sensitive data
D. To enforce data types in columns
Hint / Module Reference (Where to Read)
Distinguish a stored query definition from stored data and trace subquery scope.
Correct answer: B — Store a query definition for reuse as a virtual table.
FDB33 says CREATE VIEW stores a query definition and presents a virtual table. B describes reusing that query, although a view need not be complex. Ordinary views do not inherently store result rows. Table definitions and column types belong to schema DDL, and views do not encrypt sensitive content merely by hiding columns or rows.
Evidence in the complete study note
A subquery is a SELECT nested inside another statement. It may appear in WHERE, HAVING or FROM, not only the first two. IN tests membership; EXISTS tests whether at least one qualifying row exists. Correlated subqueries refer to the outer row. A scalar subquery must produce an appropriate single value. Beware NOT IN when its subquery can return NULL; NOT EXISTS often expresses the intended absence more safely.FDB33
CREATE VIEW CSStudent AS SELECT StudentID,Name FROM Student WHERE Major = 'CS'; stores a query definition and presents a virtual table.FDB33
An ordinary view does not necessarily store its result; a materialized view does.FDB33
Choice by choice
A · incorrect — A view defines a virtual query result rather than the base table's storage structure.
B · correct — A view saves a query definition that can be reused as a virtual table.
C · incorrect — Restricting exposed data through a view does not encrypt that data.
D · incorrect — Column data types are enforced by schema definitions and constraints rather than the view's primary purpose.
Question 172
Which database model represents data as a collection of objects?
A. Hierarchical model
B. Relational model
C. Object-oriented model
D. Network model
Hint / Module Reference (Where to Read)
Compare the data models by how they organize records and relationships.
FDB02 says object models represent objects with identity, state and behavior. That matches a collection of objects directly. Hierarchical models use a parent-child tree, relational models use tables, and classic network models link records through owner/member pointers. Those representations should not be conflated merely because programming languages can wrap their data in objects.
Evidence in the complete study note
A data model supplies concepts for structure, relationships, constraints and operations. Conceptual ER models describe entities and relationships. Relational models use tables; hierarchical models organize records in a parent–child tree; classic network models connect records through pointer-based owner/member links, allowing multiple parents. Object models represent objects with identity, state and behavior. A physical model specifies record layouts and access paths. These are different representation choices, not stages of one query.FDB02
Choice by choice
A · incorrect — The hierarchical model organizes records in a parent-child tree rather than primarily as objects.
B · incorrect — The relational model represents data in tables with tuples and attributes.
C · correct — The object model represents objects with identity, state and behavior.
D · incorrect — The network model represents linked records through owner/member connections rather than object behavior.
Question 173
What is the purpose of data normalization in database design?
A. To improve data storage efficiency
B. To simplify the database structure
C. To eliminate data redundancy
D. To optimize query performance
Hint / Module Reference (Where to Read)
Distinguish dependency-based design problems from the contents of one sample table.
Normalization organizes/decomposes tables to avoid redundancy and anomalies. Storage may benefit, but the explicit defining purpose among these choices is reducing/eliminating repeated data.
Evidence in the complete study note
Suppose Enrollment repeats StudentName for every course. An update anomaly requires changing every copy of the student's name; missed copies disagree. An insertion anomaly prevents recording a new student until some enrollment exists. A deletion anomaly loses the only record of a student's details when their last enrollment is deleted. Revoke anomaly is not a normalization anomaly.FDB19
Adding a generated row ID may make insertion technically possible, but does not remove repeated facts or fix the dependency design.FDB19
Normalization separates facts according to their determinants, reducing avoidable repetition and associated anomalies.FDB19
Choice by choice
A · incorrect — Storage efficiency can be a consequence, but the cited definition does not guarantee a storage result. (PDF pp. 16.)
B · incorrect — Decomposition does not necessarily simplify the whole schema. (PDF pp. 16.)
C · correct — Eliminating redundancy is explicitly stated. (PDF pp. 16.)
D · incorrect — Query speed is not the stated normalization goal. (PDF pp. 16.)
Question 174
In the customary single-key teaching case, which is the first normalization stage summarized as dependence on the key, the whole key, and nothing but the key?
A. First normal form (1NF)
B. Second normal form (2NF)
C. Third normal form (3NF)
D. Boyce-Codd normal form (BCNF)
Hint / Module Reference (Where to Read)
Check atomicity, candidate keys and partial dependencies in that order.
In the stated single-key teaching case, 2NF excludes partial dependencies but can still allow a nonprime fact to depend on the key through a nonkey determinant. The next defining stage is 3NF, summarized by the whole-key-and-nothing-but-the-key mnemonic. BCNF strengthens the determinant rule, and 4NF addresses multivalued dependencies. Stronger forms inherit earlier conditions, so the explicit first-stage scope makes 3NF the unique selection rather than treating inherited properties as rival answers.
Evidence in the complete study note
1NF requires atomic values in the chosen relational domains and no repeating groups in cells.FDB20
2NF requires 1NF and no nonprime attribute partially dependent on any candidate key.FDB20
For every nontrivial FD X→A, 3NF permits it when X is a superkey or A is prime. BCNF requires X to be a superkey. The mnemonic whole key and nothing but the key describes the common single-key case; it cannot replace the full tests when keys overlap. Remove dependencies of nonprime facts on nonkey determinants.FDB21
Choice by choice
A · incorrect — Incorrect: 1NF concerns atomic values and does not eliminate the specified transitive dependencies.
B · incorrect — Incorrect: 2NF excludes partial dependencies but can retain a nonprime transitive dependency through a nonkey determinant.
C · correct — Correct: 3NF is the first stage imposing the specified nonprime transitive-dependency restriction in this teaching case.
D · incorrect — Incorrect: BCNF strengthens 3NF by requiring every nontrivial determinant to be a superkey; it is not the first stage described.
Question 175
Which database model represents data as a collection of records linked through pointers?
A. Hierarchical model
B. Relational model
C. Object-oriented model
D. Network model
Hint / Module Reference (Where to Read)
Compare the data models by how they organize records and relationships.
FDB02 specifically describes the classic network model as records connected by pointer-based owner/member links, allowing multiple parents. That is the representation in the stem. Hierarchical data is restricted to a parent-child tree; relational data uses tables; object-oriented data emphasizes identity, state and behavior. Pointer-linked records therefore identify the network model in this comparison.
Evidence in the complete study note
A data model supplies concepts for structure, relationships, constraints and operations. Conceptual ER models describe entities and relationships. Relational models use tables; hierarchical models organize records in a parent–child tree; classic network models connect records through pointer-based owner/member links, allowing multiple parents. Object models represent objects with identity, state and behavior. A physical model specifies record layouts and access paths. These are different representation choices, not stages of one query.FDB02
Choice by choice
A · incorrect — A hierarchical model restricts record organization to a parent-child tree rather than general network links.
B · incorrect — A relational model uses tables and logical keys rather than the classic pointer-based network representation.
C · incorrect — Object modeling is defined by identity, state and behavior, not this classic linked-record formulation.
D · correct — Classic network databases connect records through pointer-based owner/member links.
Question 176
What is the purpose of database indexing?
A. To encrypt sensitive data
B. To enforce data integrity constraints
C. To improve data retrieval performance
D. To ensure data consistency
Hint / Module Reference (Where to Read)
Compare index organization with its access benefits and maintenance costs.
Correct answer: C — To improve data retrieval performance
Indexes provide additional access paths and explicitly speed retrieval in response to search conditions; the purpose is performance of reads.
Evidence in the complete study note
An index stores search keys with pointers or row identifiers. It often speeds selective retrieval but consumes space and must be maintained on writes. A full scan can win for a tiny table or a query returning most rows. Indexes do not themselves establish referential integrity.FDB25
Secondary indexes provide other access paths and must represent all searchable values with record pointers or pointer lists.FDB25
Indexing supplies search paths.FDB38
Choice by choice
A · incorrect — Encoding data is not the defined indexing purpose. (PDF pp. 21.)
B · incorrect — The cited purpose is access speed, not integrity enforcement. (PDF pp. 21.)
C · correct — Directly matches the retrieval definition. (PDF pp. 21.)
D · incorrect — Consistency is a constraint/transaction concern rather than the indexing purpose defined. (PDF pp. 21.)
Question 177
Which database model represents data as a collection of tables with rows and columns?
A. Hierarchical model
B. Relational model
C. Object-oriented model
D. Network model
Hint / Module Reference (Where to Read)
Compare the data models by how they organize records and relationships.
The relational model represents data using relations, conventionally shown as tables of tuples and attributes. The other choices have different defining structures: a hierarchical model uses a parent–child tree, a classic network model uses pointer-based owner/member links, and an object model uses objects with identity, state and behavior. Products may combine features, but the question asks for the model defined by rows and columns, which is relational.
Evidence in the complete study note
A data model supplies concepts for structure, relationships, constraints and operations. Conceptual ER models describe entities and relationships. Relational models use tables; hierarchical models organize records in a parent–child tree; classic network models connect records through pointer-based owner/member links, allowing multiple parents. Object models represent objects with identity, state and behavior. A physical model specifies record layouts and access paths. These are different representation choices, not stages of one query.FDB02
Choice by choice
A · incorrect — Incorrect: the hierarchical model organizes records in a parent–child tree.
B · correct — Correct: the relational model uses relations represented as tables of rows and columns.
C · incorrect — Incorrect: the object model represents objects with identity, state and behavior.
D · incorrect — Incorrect: the classic network model uses pointer-based owner/member links, allowing multiple parents.
Question 178
What is the purpose of a database schema?
A. To enforce data types in columns
B. To store database files
C. To define the structure and organization of a database
D. To ensure data integrity
Hint / Module Reference (Where to Read)
Distinguish a relation's structure from its current contents and its descriptive metadata.
Correct answer: C — To define the structure and organization of a database
A schema is the database description specified during design. It expresses structure, types, relationships and constraints; enforcing a particular constraint is narrower than the description's overall purpose.
Evidence in the complete study note
A schema describes names, structures and constraints; an instance, state or snapshot is the data at a particular time. Inserting a student changes the state, while adding a column changes the schema. Metadata is data describing data. The catalog or data dictionary records table definitions, types, constraints and often access privileges and optimizer statistics. A schema diagram draws tables and key references; labels such as database diagram can overlap.FDB03
Choice by choice
A · incorrect — Types are included, but enforcement alone is narrower than the schema description. (PDF pp. 7, 7.)
B · incorrect — Files hold data physically; the schema describes the database. (PDF pp. 7, 7.)
C · correct — Structure and organization match the schema definition. (PDF pp. 7, 7.)
D · incorrect — Integrity conditions can be specified in a schema, but integrity alone is not its overall descriptive purpose. (PDF pp. 7, 7.)
Question 179
What is the role of a database administrator (DBA) in database management?
A. To design and implement the database schema
B. To write complex SQL queries
C. To manage and maintain the database system
D. To develop database applications
Hint / Module Reference (Where to Read)
Review the database approach and match each claimed benefit or cost to its mechanism.
Correct answer: C — To manage and maintain the database system
The DBA administers database and DBMS resources, authorizes access and coordinates/monitors use. Management and maintenance summarize those system responsibilities.
Evidence in the complete study note
The DBA manages access authorization, backup/recovery, availability, monitoring, tuning and routine maintenance, coordinating database use; designers define the schema and application developers build user-facing programs.FDB01
Choice by choice
A · incorrect — Schema design may be a duty in practice but is not the defining administrator role stated. (PDF pp. 6.)
B · incorrect — Complex query writing is not the defining administrative responsibility. (PDF pp. 6.)
C · correct — Matches resource administration and monitoring. (PDF pp. 6.)
D · incorrect — Developing applications is a different role from the stated administration duties. (PDF pp. 6.)
Question 180
Which database model represents data as a hierarchical structure with parent-child
relationships?
A. Hierarchical model
B. Relational model
C. Object-oriented model
D. Network model
Hint / Module Reference (Where to Read)
Compare the data models by how they organize records and relationships.
The defining phrase is a hierarchical parent-child structure. FDB02 assigns that tree organization to the hierarchical model. The relational model uses tables; the object model represents identity, state and behavior; the network model permits more general links including multiple parents. A parent-child tree therefore selects A directly.
Evidence in the complete study note
A data model supplies concepts for structure, relationships, constraints and operations. Conceptual ER models describe entities and relationships. Relational models use tables; hierarchical models organize records in a parent–child tree; classic network models connect records through pointer-based owner/member links, allowing multiple parents. Object models represent objects with identity, state and behavior. A physical model specifies record layouts and access paths. These are different representation choices, not stages of one query.FDB02
Choice by choice
A · correct — The hierarchical model organizes records into a parent-child tree.
B · incorrect — The relational model represents data as tables rather than a prescribed parent-child tree.
C · incorrect — The object model represents object identity, state and behavior rather than requiring a tree.
D · incorrect — The network model permits more general connections and multiple parents rather than the restricted tree structure.
Question 181
What is the first step in the database design process?
A. Defining relationships between tables
B. Determining the database schema
C. Analyzing the information requirements
D. Creating database queries
Hint / Module Reference (Where to Read)
Identify the design phase from its inputs and intended deliverable.
Correct answer: C — Analyzing the information requirements
The initial design step is to understand the organization's information needs, required transactions, reports and constraints. Those requirements guide conceptual modeling, which is then mapped into a logical schema. Defining table relationships and determining the schema therefore depend on the earlier analysis. Queries express required operations against a model rather than replacing the initial work of identifying what information and behavior are needed. C is the first step in that sequence.
Evidence in the complete study note
First collect business facts, required transactions, reports, volumes, privacy rules and quality targets. Conceptual design turns them into an ER/EER model. Logical design maps the conceptual model to relations, keys and constraints and checks normalization. Physical design chooses file organization, indexes and storage. Implementation creates and loads the actual objects; testing checks requirements.FDB05
Choice by choice
A · incorrect — Incorrect: table relationships are designed to satisfy requirements already identified.
B · incorrect — Incorrect: schema choices follow the analysis of required information and operations.
C · correct — Correct: analyze information requirements before choosing the model and schema.
D · incorrect — Incorrect: query construction depends on understanding the required information and intended database structure.
Question 182
Read pairs left→right. Student↔Course and Product↔Supplier permit many occurrences in both directions. Each employee belongs to one department with many employees. Each order belongs to one customer, who may place many orders. Which pair is one-to-many?
A. Student and Course
B. Employee and Department
C. Customer and Order
D. Product and Supplier
Hint / Module Reference (Where to Read)
Read the relationship in both directions and check participation constraints.
Customer→Order is one-to-many because one customer can place several orders and each order belongs to one customer. Employee→Department runs in the opposite direction: many employees share one department, making it many-to-one. The student/course and product/supplier pairs explicitly permit many partners in both directions and are many-to-many. Cardinality follows these rules and the direction of the pair.
Evidence in the complete study note
Cardinality specifies maximum associated occurrences: 1:1, 1:N or M:N. Minimum cardinality specifies optional/partial participation (zero permitted) or mandatory/total participation (at least one). A maximum of one does not mean participation is compulsory. Relationship degree counts participating entity roles: unary recursive, binary or ternary.FDB09
If each employee belongs to one department and each department has many employees, Employee→Department is N:1; Department→Employee is 1:N.FDB09
Read business rules in both directions.FDB09
Choice by choice
A · incorrect — Student→Course is M:N under the stated enrollment rule.
B · incorrect — Employee→Department is N:1.
C · correct — Customer→Order is 1:N under the ownership rule.
D · incorrect — Product→Supplier is M:N under the stated supply rule.
Question 183
Which of the following is a benefit of using database indexes?
A. Increased data redundancy
B. Decreased data integrity
C. Improved data retrieval performance
D. Simplified data modeling
Hint / Module Reference (Where to Read)
Compare index organization with its access benefits and maintenance costs.
Correct answer: C — Improved data retrieval performance
The auxiliary index provides alternative access paths to speed retrieval under search conditions. This is the directly supported benefit.
Evidence in the complete study note
An index stores search keys with pointers or row identifiers. It often speeds selective retrieval but consumes space and must be maintained on writes. A full scan can win for a tiny table or a query returning most rows. Indexes do not themselves establish referential integrity.FDB25
Secondary indexes provide other access paths and must represent all searchable values with record pointers or pointer lists.FDB25
A dense index represents each search value; sparse indexes rely on ordered data between entries.FDB25
Choice by choice
A · incorrect — Redundancy is not the benefit identified by the index definition. (PDF pp. 21.)
B · incorrect — Reduced integrity is not a stated benefit. (PDF pp. 21.)
C · correct — Retrieval improvement is explicitly stated. (PDF pp. 21.)
D · incorrect — Simplification of the logical model is not the index's stated function. (PDF pp. 21.)
Question 184
Employee has required, unique SSN and Employee ID values. Names and salaries may repeat. Which listed individual attributes are candidate keys? Select all that apply.
A. Social Security Number (SSN)
B. Employee Name
C. Employee ID
D. Employee Salary
Hint / Module Reference (Where to Read)
Test uniqueness and minimality separately when classifying keys.
Correct answers (select all): A, C — A. Social Security Number (SSN); C. Employee ID
A candidate key is a minimal attribute set guaranteed to identify a tuple. Each required unique single-attribute identifier is already minimal, so SSN and Employee ID both qualify. Names and salaries may repeat and therefore cannot guarantee identification. The question asks for candidate keys rather than the one candidate later selected as the primary key, allowing both identifiers.
Evidence in the complete study note
A superkey uniquely identifies a tuple. A candidate key is a minimal superkey: removing any component loses uniqueness. Minimal means no unnecessary attribute, not necessarily the fewest columns among all keys. A primary key is one selected candidate key; remaining candidates are alternate keys. A composite key contains multiple attributes and need not be the chosen primary key.FDB07
If ID is unique, both {ID} and {ID,City} are superkeys, but only {ID} is minimal.FDB07
Prefer stable, compact identifiers when practical, but Email, SSN or EmployeeID is a candidate only if business rules guarantee uniqueness.FDB07
Choice by choice
A · correct — Required unique SSN identifies each employee and is minimal.
B · incorrect — Repeated names are permitted, so Name is not a candidate key.
C · correct — Required unique Employee ID independently identifies each employee.
D · incorrect — Repeated salaries are permitted, so Salary is not a candidate key.
Question 185
What is the purpose of a foreign key in database design?
A. To enforce data types in columns
B. To define primary key constraints
C. To establish relationships between tables
D. To optimize query performance
Hint / Module Reference (Where to Read)
Match the validity requirement to the appropriate constraint definition.
Correct answer: C — To establish relationships between tables
A foreign key links a child occurrence to the matching parent candidate/primary key. Its defining purpose is the intertable relationship.
Evidence in the complete study note
Domain integrity restricts type and allowed values. Key integrity enforces uniqueness. Entity integrity requires every primary-key component to be non-NULL. Referential integrity requires a non-NULL foreign-key value to match an eligible referenced unique/candidate key; self-references are allowed. A parent need not have a child.FDB15
A primary key is one selected candidate key; remaining candidates are alternate keys.FDB07
A composite key contains multiple attributes and need not be the chosen primary key.FDB07
Choice by choice
A · incorrect — Domain constraints specify legal types. (PDF pp. 48.)
B · incorrect — The parent key is an identifying constraint, while the child FK expresses the reference. (PDF pp. 48.)
C · correct — Matches the stated linking purpose. (PDF pp. 48.)
D · incorrect — Performance is not the defining reference function. (PDF pp. 48.)
Question 186
Which of the following database design principles promotes data independence?
A. Entity-Relationship (ER) modeling
B. Normalization
C. Database indexing
D. Data abstraction
Hint / Module Reference (Where to Read)
Trace which schema levels a proposed change affects.
Abstraction hides irrelevant details and helps achieve data independence. Three-schema separation then permits changes at one level without forcing higher-level schema changes.
Evidence in the complete study note
Physical data independence permits changing internal storage without changing the conceptual schema, for example adding an index. Logical data independence permits conceptual changes while preserving affected external views and application interfaces, for example splitting a table while maintaining a compatible view. It is harder because applications depend on logical meaning. Data abstraction hides irrelevant details; program–data insulation separates data definitions from application code.FDB04
Conceptual design turns them into an ER/EER model.FDB05
Logical design maps the conceptual model to relations, keys and constraints and checks normalization.FDB05
Choice by choice
A · incorrect — ER modeling describes conceptual structure but is not the explicit independence principle here. (PDF pp. 6, 8.)
B · incorrect — Normalization addresses redundancy/anomalies. (PDF pp. 6, 8.)
C · incorrect — Indexes address retrieval paths and performance. (PDF pp. 6, 8.)
D · correct — Abstraction is explicitly connected to independence. (PDF pp. 6, 8.)
Question 187
Which type of database relationship is represented by a join table?
A. One-to-one relationship
B. One-to-many relationship
C. Many-to-many relationship
D. Hierarchical relationship
Hint / Module Reference (Where to Read)
Identify relationship degree and cardinality before choosing a relational mapping.
A join table stores pairs of references to the two participating entity tables, optionally with relationship attributes. FDB12 uses Enrollment(StudentID,CourseID,Grade) for the many-to-many Student–Course relationship. One-to-one and one-to-many relationships normally use a foreign key in an entity table instead. A hierarchy is a structural pattern, not the relationship cardinality represented by this usual association mapping.
Evidence in the complete study note
A binary M:N relationship needs an association table in addition to the two entity tables. Enrollment(StudentID,CourseID,Grade) references Student and Course; (StudentID,CourseID) is a key if one enrollment per pair is allowed. Repeated attempts require Term or AttemptNo in the key. This ordinarily produces three tables.FDB12
For 1:N, place the one-side key as a foreign key in the many-side table; store relationship attributes there.FDB11
For 1:1, one side may hold the other key with a UNIQUE constraint.FDB11
Choice by choice
A · incorrect — A 1:1 relationship normally uses a UNIQUE foreign key rather than requiring a join table.
B · incorrect — A 1:N relationship normally stores the one-side key in the many-side entity table.
C · correct — A join or association table stores the pairs making up a many-to-many relationship.
D · incorrect — Hierarchical organization is not the standard cardinality category represented by this association-table mapping.
Question 188
Employee ID uniquely identifies an employee and their name, salary and department. Names may repeat; the same salary may occur in different departments, and a department may contain different salaries. Which listed functional dependency is guaranteed?
A. Employee ID -> Employee Name
B. Employee Name -> Employee ID
C. Employee Salary -> Employee Department
D. Employee Department -> Employee Salary
Hint / Module Reference (Where to Read)
Compute closures using the declared dependencies and test minimality.
Correct answer: A — A. Employee ID -> Employee Name
A functional dependency is guaranteed by business rules for every legal state. Unique Employee ID determines all employee attributes, including Name, so A follows. Repeated names can correspond to different IDs, equal salaries can occur in different departments, and one department may contain different salaries. Those permissions prevent the other proposed directions from being guaranteed dependencies.
Evidence in the complete study note
X→Y means every legal pair of tuples agreeing on X also agrees on Y. It is a business constraint, not a correlation observed in one sample. Full dependency requires all determinant attributes; partial dependency holds through a proper subset. A prime attribute belongs to some candidate key; a nonprime attribute belongs to none.FDB17
Remove attributes and recompute to test minimality.FDB17
Choice by choice
A · correct — The unique employee identifier determines the employee's name.
B · incorrect — Repeated names can belong to different IDs.
C · incorrect — The same salary may occur in different departments.
D · incorrect — One department may contain employees with different salaries.
Question 189
What is the purpose of a data dictionary in database design?
A. To store user credentials
B. To manage database backups
C. To define the structure and attributes of data elements
D. To enforce referential integrity constraints
Hint / Module Reference (Where to Read)
Distinguish a relation's structure from its current contents and its descriptive metadata.
Correct answer: C — To describe the structure and attributes of data elements.
The dictionary contains metadata explaining table definitions, attribute types and constraints. FDB03 therefore supports C as its purpose. It may record privileges, but storing user credentials is not its defining function. Backup management is an administrative operation, and enforcing a reference requires DBMS constraint machinery rather than merely the repository that describes the constraint.
Evidence in the complete study note
A schema describes names, structures and constraints; an instance, state or snapshot is the data at a particular time. Inserting a student changes the state, while adding a column changes the schema. Metadata is data describing data. The catalog or data dictionary records table definitions, types, constraints and often access privileges and optimizer statistics. A schema diagram draws tables and key references; labels such as database diagram can overlap.FDB03
Choice by choice
A · incorrect — Credential storage is not the defining purpose of the database structure metadata repository.
B · incorrect — Backup management is an administrative activity rather than the dictionary's structural-description purpose.
C · correct — The dictionary records definitions, attributes, data types and constraints describing database elements.
D · incorrect — It describes constraints; the DBMS enforcement machinery actually checks referential integrity during operations.
Question 190
Compared with scanning all pages of a large heap file, which advantage can an RDBMS with a suitable index provide for a query selecting a small fraction of rows? Assume index lookup costs less than the scan.
A. Easy integration with external systems
B. Lower storage requirements
C. No need for data normalization
D. Faster data retrieval
Hint / Module Reference (Where to Read)
Compare design techniques by the specific problem each solves.
The suitable index provides a search path for the selective query, reducing the work compared with scanning the large heap under the stated cost assumption. This supports faster retrieval for this workload. It does not remove normalization concerns, guarantee smaller storage or establish external-system compatibility. Index space and maintenance costs remain separate trade-offs from the query's retrieval advantage.
Evidence in the complete study note
Normalization reduces dependency-driven anomalies. Indexing supplies search paths. Denormalization deliberately repeats or precomputes facts to reduce selected read costs, increasing maintenance work. Partitioning divides rows or columns; replication creates copies to improve locality or availability while complicating updates. These techniques can coexist. An e-commerce or gaming label alone does not select one.FDB38
It often speeds selective retrieval but consumes space and must be maintained on writes.FDB25
A full scan can win for a tiny table or a query returning most rows.FDB25
Choice by choice
A · incorrect — External integration is outside the stated lookup comparison.
B · incorrect — Indexes consume additional storage rather than guaranteeing a reduction.
C · incorrect — An index does not remove the need for dependency-aware design.
D · correct — The stated cheaper indexed lookup supports faster retrieval for this query.
Question 191
OrderLine has one row per line number within an order. OrderID and LineNo together uniquely identify a row; neither alone does. Which listed attribute set is a composite key?
A. Employee ID
B. Product SKU
C. Customer Name
D. (OrderID, LineNo)
Hint / Module Reference (Where to Read)
Test uniqueness and minimality separately when classifying keys.
A composite key contains more than one attribute. OrderID identifies an order and LineNo identifies a line within it; the stated rules make their pair unique while neither component alone is sufficient. That establishes a minimal composite key. The other options each name only one attribute and therefore do not illustrate a composite key, regardless of any separate uniqueness policy.
Evidence in the complete study note
A superkey uniquely identifies a tuple. A candidate key is a minimal superkey: removing any component loses uniqueness. Minimal means no unnecessary attribute, not necessarily the fewest columns among all keys. A primary key is one selected candidate key; remaining candidates are alternate keys. A composite key contains multiple attributes and need not be the chosen primary key.FDB07
If ID is unique, both {ID} and {ID,City} are superkeys, but only {ID} is minimal.FDB07
A composite-key example must include multiple columns; a single-column key is not composite.FDB07
Choice by choice
A · incorrect — Employee ID is a single attribute.
B · incorrect — Product SKU is a single attribute.
C · incorrect — Customer Name is a single listed attribute.
D · correct — The pair is unique and requires both stated attributes.
Question 192
What is the purpose of data validation in database design?
A. To enforce referential integrity constraints
B. To improve query performance
C. To eliminate data redundancy
D. To ensure data integrity and quality
Hint / Module Reference (Where to Read)
Match the validity requirement to the appropriate constraint definition.
Correct answer: D — To ensure data integrity and quality
Validation checks that input values meet legal domains/criteria, protecting integrity and consistency. D is the broad purpose; referential validation is one narrower kind rather than the whole definition.
Evidence in the complete study note
Domain integrity restricts type and allowed values. Key integrity enforces uniqueness. Entity integrity requires every primary-key component to be non-NULL. Referential integrity requires a non-NULL foreign-key value to match an eligible referenced unique/candidate key; self-references are allowed. A parent need not have a child.FDB15
Reduced redundancy can improve integrity and storage use, but extra keys and joins mean neither storage savings nor faster retrieval is guaranteed.FDB19
Indexes do not themselves establish referential integrity.FDB25
Choice by choice
A · incorrect — Referential checks are one specific constraint, not the overall purpose of validation. (PDF pp. 47, 48, 49.)
B · incorrect — Performance is not the legal-input goal. (PDF pp. 47, 48, 49.)
C · incorrect — Redundancy reduction belongs to normalization. (PDF pp. 47, 48, 49.)
D · correct — Integrity and input quality match domain validation and CHECK criteria. (PDF pp. 47, 48, 49.)
Question 193
Which normal forms have defining restrictions on functional dependencies? Select all that apply. Treat 1NF as the atomic-value/no-repeating-groups requirement.
A. First Normal Form (1NF)
B. Second Normal Form (2NF)
C. Third Normal Form (3NF)
D. Boyce-Codd Normal Form (BCNF)
Hint / Module Reference (Where to Read)
Test determinants and prime attributes against the stated normal-form rules.
Correct answers (select all): B, C, D — B. Second Normal Form (2NF); C. Third Normal Form (3NF); D. Boyce-Codd Normal Form (BCNF)
Second normal form excludes nonprime partial functional dependencies on candidate keys. Third normal form checks each nontrivial dependency using the determinant's superkey status or the dependent's prime status. BCNF strengthens that test by requiring a superkey determinant. These three therefore use functional-dependency restrictions. First normal form instead establishes the atomic-value foundation and absence of repeating groups.
Evidence in the complete study note
For every nontrivial FD X→A, 3NF permits it when X is a superkey or A is prime. BCNF requires X to be a superkey. The mnemonic whole key and nothing but the key describes the common single-key case; it cannot replace the full tests when keys overlap. Remove dependencies of nonprime facts on nonkey determinants.FDB21
A relation whose every candidate key is single-attribute has no nonprime partial-key dependency.FDB20
2NF requires 1NF and no nonprime attribute partially dependent on any candidate key.FDB20
Choice by choice
A · incorrect — 1NF addresses atomic values and repeating groups rather than the stated dependency-based tests.
B · correct — 2NF is defined through absence of nonprime partial functional dependencies on candidate keys.
C · correct — 3NF tests nontrivial FDs using determinant superkeys and prime dependent attributes.
D · correct — BCNF requires every nontrivial FD determinant to be a superkey.
Question 194
Which are direct representational purposes of conceptual/logical data modeling, rather than DBMS enforcement or physical query tuning? Select all that apply.
A. To enforce data constraints
B. To optimize query performance
C. To define the structure and organization of a database
D. To represent real-world entities and their relationships
Hint / Module Reference (Where to Read)
Identify the design phase from its inputs and intended deliverable.
Correct answers (select all): C, D — C. To define the structure and organization of a database; D. To represent real-world entities and their relationships
Conceptual modeling represents domain entities and their relationships, while logical modeling defines database structures, keys and constraints. Both are direct representational purposes, so C and D qualify. Modeling can describe constraints, but the DBMS and application implementation enforce them during operations. Query performance can inform physical design, yet it is not the direct representational task specified in this question.
Evidence in the complete study note
First collect business facts, required transactions, reports, volumes, privacy rules and quality targets. Conceptual design turns them into an ER/EER model. Logical design maps the conceptual model to relations, keys and constraints and checks normalization. Physical design chooses file organization, indexes and storage. Implementation creates and loads the actual objects; testing checks requirements.FDB05
A data model supplies concepts for structure, relationships, constraints and operations.FDB02
Conceptual ER models describe entities and relationships.FDB02
Choice by choice
A · incorrect — A model describes constraints, while implementation and DBMS machinery enforce them on actual operations.
B · incorrect — Query performance can inform physical design, but it is not the general representational purpose asked here.
C · correct — Modeling specifies the database's structure and organization.
D · correct — Conceptual modeling represents real-world entities and their relationships.
Question 195
Which option states a measurable nonfunctional quality requirement for a database system?
A. The database service must achieve at least 99.9% uptime.
B. User authentication
C. Entity-Relationship (ER) diagram
D. Data normalization
Hint / Module Reference (Where to Read)
Compare design techniques by the specific problem each solves.
Correct answer: A — A. The database service must achieve at least 99.9% uptime.
A nonfunctional requirement states a quality target such as availability or response time. The uptime threshold specifies a measurable availability requirement and fits that category. User authentication names system behavior, while an ER diagram is a modeling artifact and normalization is a design technique. These may support requirements, but they do not themselves state the quantified quality requested here.
Evidence in the complete study note
Privacy requires a threat model. Authentication verifies identity; role-based authorization limits allowed records/actions; encryption protects data at rest or in transit; auditing records activity. Both encryption and access control can protect patient data. Scalability, availability, fast retrieval, redundancy control and security are all reasonable design considerations. Functional requirements describe behavior; nonfunctional requirements quantify qualities such as 99.9% uptime or response time. Authentication and encryption mechanisms alone do not uniquely establish that distinction.FDB38
Adding a generated row ID may make insertion technically possible, but does not remove repeated facts or fix the dependency design.FDB19
Conceptual design turns them into an ER/EER model.FDB05
Choice by choice
A · correct — The 99.9% uptime threshold is a measurable availability target.
B · incorrect — Authentication names behavior rather than a quantified quality target.
C · incorrect — An ER diagram is a design artifact.
D · incorrect — Normalization is a design technique.
Question 196
What is the purpose of data manipulation in database management?
A. To define the database schema
B. To create database backups
C. To insert, update, and delete data
D. To manage concurrent access to data
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
Correct answer: C — To insert, update, and delete data
The DML subsection includes INSERT, UPDATE and DELETE. SQL examples show insertion/deletion and an UPDATE privilege permits changing values, grounding the data-manipulation purpose. DML also includes retrieval, so C describes its modification part rather than all of DML.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
Choice by choice
A · incorrect — Schema definition is DDL. (PDF pp. 26, 55, 77.)
B · incorrect — Backup is not the DML purpose identified. (PDF pp. 26, 55, 77.)
C · correct — Insertion, updating and deletion are explicitly DML operations. (PDF pp. 26, 55, 77.)
D · incorrect — Concurrency control is a separate transaction-management responsibility. (PDF pp. 26, 55, 77.)
Question 197
During conceptual database design, which listed goal directly concerns representing the business domain before choosing storage layouts and access paths?
A. Efficient storage of data
B. Secure data transmission
C. Optimal query performance
D. Effective representation of real-world entities
Hint / Module Reference (Where to Read)
Identify the design phase from its inputs and intended deliverable.
Correct answer: D — D. Effective representation of real-world entities
Conceptual design turns business facts into an ER/EER representation of entities and relationships. Effective representation of the domain is therefore the direct goal in this phase. Storage efficiency and query access paths are later physical design concerns. Secure transmission can be an important requirement, but it is not the conceptual representational task singled out by the revised question.
Evidence in the complete study note
First collect business facts, required transactions, reports, volumes, privacy rules and quality targets. Conceptual design turns them into an ER/EER model. Logical design maps the conceptual model to relations, keys and constraints and checks normalization. Physical design chooses file organization, indexes and storage. Implementation creates and loads the actual objects; testing checks requirements.FDB05
Indexing supplies search paths.FDB38
Choice by choice
A · incorrect — Storage layout efficiency belongs to physical decisions.
B · incorrect — Transmission security is a requirement or mechanism, not the specified conceptual representation goal.
C · incorrect — Query performance is addressed through later physical design and tuning.
Which are legitimate considerations when designing a database for a large e-commerce site? Select all that apply. Minimal redundancy means controlling avoidable duplication while permitting deliberate replicas.
A. High availability and scalability
B. Minimal data redundancy
C. Fast data retrieval
D. Secure user authentication
Hint / Module Reference (Where to Read)
Compare design techniques by the specific problem each solves.
Correct answers (select all): A, B, C, D — A. High availability and scalability; B. Minimal data redundancy; C. Fast data retrieval; D. Secure user authentication
Large e-commerce databases need several complementary qualities. Availability and scalability support continued service and growth; retrieval speed supports interactive use; security protects accounts and records. Controlling avoidable redundancy reduces consistency and maintenance problems even where intentional replicas are useful. All listed considerations therefore belong in the design discussion, with their relative priority determined by the site's actual requirements.
Evidence in the complete study note
Privacy requires a threat model. Authentication verifies identity; role-based authorization limits allowed records/actions; encryption protects data at rest or in transit; auditing records activity. Both encryption and access control can protect patient data. Scalability, availability, fast retrieval, redundancy control and security are all reasonable design considerations. Functional requirements describe behavior; nonfunctional requirements quantify qualities such as 99.9% uptime or response time. Authentication and encryption mechanisms alone do not uniquely establish that distinction.FDB38
In a CMS, tags/categories describe meaning, while foreign keys preserve valid references between content and tags.FDB38
Choice by choice
A · correct — High availability and scalability support growth and continued service for a large site.
B · correct — Controlling unnecessary redundancy improves consistency and maintenance even when deliberate copies are retained.
C · correct — Fast retrieval is a legitimate quality target for browsing, orders and customer-facing operations.
D · correct — Secure authentication is a legitimate security consideration for customer and administrative access.
Question 199
Which listed mechanisms directly protect patient confidentiality, covering both protected stored/transmitted content and restrictions on authorized user actions? Select all that apply.
A. Database normalization
B. Data encryption
C. Role-based access control
D. Database indexing
Hint / Module Reference (Where to Read)
Compare design techniques by the specific problem each solves.
Correct answers (select all): B, C — B. Data encryption; C. Role-based access control
Encryption protects patient content at rest or in transit against the disclosure threats it covers. Role-based access control restricts which users can read or change patient records. These are complementary privacy protections and both qualify. Normalization reduces dependency anomalies, while indexing supplies retrieval paths; neither mechanism by itself decides who may see patient information or protects encoded content.
Evidence in the complete study note
Privacy requires a threat model. Authentication verifies identity; role-based authorization limits allowed records/actions; encryption protects data at rest or in transit; auditing records activity. Both encryption and access control can protect patient data. Scalability, availability, fast retrieval, redundancy control and security are all reasonable design considerations. Functional requirements describe behavior; nonfunctional requirements quantify qualities such as 99.9% uptime or response time. Authentication and encryption mechanisms alone do not uniquely establish that distinction.FDB38
Secondary indexes provide other access paths and must represent all searchable values with record pointers or pointer lists.FDB25
Adding a generated row ID may make insertion technically possible, but does not remove repeated facts or fix the dependency design.FDB19
Choice by choice
A · incorrect — Normalization reduces dependency anomalies but does not itself restrict unauthorized disclosure.
B · correct — Encryption protects patient content at rest or in transit against appropriate disclosure threats.
C · correct — Role-based access control limits which users may access patient records or perform actions.
D · incorrect — Indexes provide search paths and do not by themselves enforce patient confidentiality.
Question 200
Which techniques can improve query performance for some suitable workloads, without implying that they always help? Select all that apply.
A. Indexing
B. Data replication
C. Denormalization
D. Partitioning
Hint / Module Reference (Where to Read)
Compare design techniques by the specific problem each solves.
Correct answers (select all): A, B, C, D — A. Indexing; B. Data replication; C. Denormalization; D. Partitioning
Indexes add search paths for selective queries. Replicas can improve read locality, denormalization can reduce selected joins or computations, and partitioning can reduce relevant data access when aligned with queries. Each technique can therefore help some workloads. Their benefits are conditional and can involve extra storage or update costs, so selecting all four does not mean every database should use all of them.
Evidence in the complete study note
Normalization reduces dependency-driven anomalies. Indexing supplies search paths. Denormalization deliberately repeats or precomputes facts to reduce selected read costs, increasing maintenance work. Partitioning divides rows or columns; replication creates copies to improve locality or availability while complicating updates. These techniques can coexist. An e-commerce or gaming label alone does not select one.FDB38
B/B+ trees support ordered and range search; hashing targets equality; bitmap indexes suit some analytic workloads.FDB25
Choose organization from actual reads, writes and predicates.FDB24
Choice by choice
A · correct — Indexes can reduce reads for selective predicates when maintenance and lookup costs are justified.
B · correct — Replication can place read copies near users or spread suitable read workloads.
C · correct — Denormalization can avoid selected joins or repeated computation by storing redundant/precomputed facts.
D · correct — Partitioning can limit relevant data or improve locality when partitioning matches query predicates.
Question 201
Users may follow many other users, and each user may be followed by many users. Which option describes the cardinality of this recursive user relationship, rather than a key constraint or procedural enforcement mechanism?
A. Primary key constraints
B. Foreign key constraints
C. Many-to-many relationships
D. Triggers and stored procedures
Hint / Module Reference (Where to Read)
Identify relationship degree and cardinality before choosing a relational mapping.
The follow rules permit multiple related users in both directions, which is many-to-many cardinality. A recursive association table can store two user references to represent those links. Primary and foreign keys support identification and referential integrity, but they are constraint mechanisms rather than the requested multiplicity classification. Triggers and stored procedures are also not a cardinality category.
Evidence in the complete study note
A recursive M:N friendship uses UserLink(UserID,OtherUserID), two foreign keys to User; decide whether links are directed and whether self-links are allowed. A recursive 1:N supervision uses a nullable SupervisorID reference in Employee. For a ternary Supply(SupplierID,PartID,ProjectID,Quantity), preserve all participating references; binary replacements can lose which triple occurred. Choose the association key from actual cardinalities rather than automatically assuming every reference is necessary.FDB12
A composite key contains multiple attributes and need not be the chosen primary key.FDB07
Referential integrity requires a non-NULL foreign-key value to match an eligible referenced unique/candidate key; self-references are allowed.FDB15
Choice by choice
A · incorrect — Primary keys identify rows rather than name relationship multiplicity.
B · incorrect — Foreign keys validate references rather than describe the many-to-many cardinality.
C · correct — Both endpoints permit many partners, establishing M:N.
D · incorrect — Procedures are implementation mechanisms rather than a cardinality type.
Question 202
In the usual normalization sequence, which is the first normal form excluding transitive dependence of nonprime attributes on a key through a nonkey determinant?
A. First Normal Form (1NF)
B. Second Normal Form (2NF)
C. Third Normal Form (3NF)
D. Boyce-Codd Normal Form (BCNF)
Hint / Module Reference (Where to Read)
Check atomicity, candidate keys and partial dependencies in that order.
In the stated single-key teaching case, 2NF excludes partial dependencies but can still allow a nonprime fact to depend on the key through a nonkey determinant. The next defining stage is 3NF, summarized by the whole-key-and-nothing-but-the-key mnemonic. BCNF strengthens the determinant rule, and 4NF addresses multivalued dependencies. Stronger forms inherit earlier conditions, so the explicit first-stage scope makes 3NF the unique selection rather than treating inherited properties as rival answers.
Evidence in the complete study note
1NF requires atomic values in the chosen relational domains and no repeating groups in cells.FDB20
2NF requires 1NF and no nonprime attribute partially dependent on any candidate key.FDB20
For every nontrivial FD X→A, 3NF permits it when X is a superkey or A is prime. BCNF requires X to be a superkey. The mnemonic whole key and nothing but the key describes the common single-key case; it cannot replace the full tests when keys overlap. Remove dependencies of nonprime facts on nonkey determinants.FDB21
Choice by choice
A · incorrect — Incorrect: 1NF concerns atomic values and does not eliminate the specified transitive dependencies.
B · incorrect — Incorrect: 2NF excludes partial dependencies but can retain a nonprime transitive dependency through a nonkey determinant.
C · correct — Correct: 3NF is the first stage imposing the specified nonprime transitive-dependency restriction in this teaching case.
D · incorrect — Incorrect: BCNF strengthens 3NF by requiring every nontrivial determinant to be a superkey; it is not the first stage described.
Question 203
What is the purpose of denormalization in database design for real-world scenarios?
A. To eliminate data redundancy
B. To optimize query performance
C. To enforce referential integrity
D. To simplify the database schema
Hint / Module Reference (Where to Read)
Compare design techniques by the specific problem each solves.
Correct answer: B — To improve selected query performance.
Denormalization repeats or precomputes facts so that selected queries can perform less work, such as fewer joins. FDB38 also states the cost: more maintenance when underlying facts change. It does not aim to eliminate redundancy or enforce foreign-key references. A simpler query path may result, but a simpler schema is not the essential purpose.
Evidence in the complete study note
Normalization reduces dependency-driven anomalies. Indexing supplies search paths. Denormalization deliberately repeats or precomputes facts to reduce selected read costs, increasing maintenance work. Partitioning divides rows or columns; replication creates copies to improve locality or availability while complicating updates. These techniques can coexist. An e-commerce or gaming label alone does not select one.FDB38
Referential integrity requires a non-NULL foreign-key value to match an eligible referenced unique/candidate key; self-references are allowed.FDB15
Key integrity enforces uniqueness.FDB15
Choice by choice
A · incorrect — Denormalization introduces controlled redundancy instead of eliminating it.
B · correct — Its goal is to reduce costs of selected read queries using repeated or precomputed information.
C · incorrect — Foreign-key constraints enforce references; denormalization itself does not establish referential integrity.
D · incorrect — It may simplify selected reads, but schema simplicity is not the defining or guaranteed goal.
Question 204
Which listed modeling technique directly includes superclass/subclass inheritance? Interpret ER modeling in A as basic ER without the EER extension, relational modeling as ordinary tables/keys, and XML modeling as document structure.
A. Entity-Relationship (ER) modeling
B. Object-oriented data modeling
C. Relational data modeling
D. XML data modeling
Hint / Module Reference (Where to Read)
Review inheritance, specialization and category constraints.
Correct answer: B — B. Object-oriented data modeling
Object-oriented data modeling directly represents superclass/subclass inheritance. Enhanced ER also supports inheritance, but A is explicitly limited to basic ER in this question. Ordinary relational tables can encode subtype data through references without making inheritance a direct modeling construct, and document structure is not the object inheritance mechanism being tested. The scope distinction makes the selected technique definite.
Evidence in the complete study note
EER extends ER with superclass/subclass structure. A subclass inherits the superclass attributes and relationships. Specialization divides Student into Undergraduate and Graduate; generalization combines common properties into Student. Disjoint subclasses cannot overlap; overlapping subclasses can. Total specialization requires every superclass member in some subclass; partial specialization does not.FDB13
Object models also support inheritance; distinguish basic ER notation from EER when comparing models.FDB13
Structural EER inheritance alone does not define executable object methods.FDB13
Choice by choice
A · incorrect — Basic ER excludes the EER inheritance extension under the stated scope.
B · correct — Object models directly support superclass/subclass inheritance.
C · incorrect — Ordinary relational tables encode facts and references rather than directly declaring this inheritance construct.
D · incorrect — Document structure is not the direct object inheritance model asked for.
Question 205
Which CMS features directly support either semantic grouping of content or valid stored references between content and tags? Select all that apply.
A. Referential integrity constraints
B. Full-text search capabilities
C. Content categorization and tagging
D. Distributed database architecture
Hint / Module Reference (Where to Read)
Compare design techniques by the specific problem each solves.
Correct answers (select all): A, C — A. Referential integrity constraints; C. Content categorization and tagging
Categories and tags describe semantic associations among content items. Referential integrity constraints keep the stored links between content and tags valid. These two features address meaning and reference validity respectively, so both fit. Full-text search supports finding text, while a distributed architecture concerns data placement and coordination. Neither is the requested direct representation or validation of content-to-tag relationships.
Evidence in the complete study note
In a CMS, tags/categories describe meaning, while foreign keys preserve valid references between content and tags. A ContentTag(ContentID,TagID) association models multiple tags per item; these functions complement each other.FDB38
Referential integrity requires a non-NULL foreign-key value to match an eligible referenced unique/candidate key; self-references are allowed.FDB15
Key integrity enforces uniqueness.FDB15
Choice by choice
A · correct — Referential constraints preserve valid links between content and related rows, such as tags.
B · incorrect — Full-text search finds textual content rather than primarily defining or validating item relationships.
C · correct — Categories and tags describe semantic groupings and associations among content items.
D · incorrect — Distribution determines placement and coordination rather than directly representing the stated content relationships.
Question 206
Which database design technique is used to represent a hierarchical structure of data
elements?
A. Relational data modeling
B. Network data modeling
C. Hierarchical data modeling
D. Object-relational data modeling
Hint / Module Reference (Where to Read)
Compare the data models by how they organize records and relationships.
A hierarchical structure organizes elements into parent-child levels. FDB02 assigns this representation to the hierarchical model, so C directly matches the requested design concept. Relational modeling instead uses tables, network modeling permits more general record links, and richer object-relational types do not by themselves require a hierarchical organization.
Evidence in the complete study note
A data model supplies concepts for structure, relationships, constraints and operations. Conceptual ER models describe entities and relationships. Relational models use tables; hierarchical models organize records in a parent–child tree; classic network models connect records through pointer-based owner/member links, allowing multiple parents. Object models represent objects with identity, state and behavior. A physical model specifies record layouts and access paths. These are different representation choices, not stages of one query.FDB02
Richer relational types do not by themselves require all data to form a parent–child tree; that representation is the defining concern of hierarchical modeling.FDB-S02
An object-relational DBMS retains relational data management while supporting richer extensions such as user-defined data types, functions and operators.FDB-S02
Choice by choice
A · incorrect — Relational modeling represents facts in tables rather than requiring a hierarchical tree.
B · incorrect — Network modeling permits linked records with multiple parents rather than a restricted hierarchy.
C · correct — Hierarchical modeling explicitly represents the parent-child structure asked for in the stem.
D · incorrect — An object-relational approach adds richer types to relational storage rather than defining this tree organization.
Question 207
Which benefit directly follows from reducing dependency-driven insertion, deletion and update anomalies through normalization, without assuming reduced storage or simpler queries?
A. Simplified data retrieval
B. Increased data redundancy
C. Enhanced data integrity
D. Reduced storage requirements
Hint / Module Reference (Where to Read)
Distinguish dependency-based design problems from the contents of one sample table.
Normalization separates facts according to their determinants, reducing repeated facts and the insertion, deletion and update anomalies they cause. That directly supports enhanced data integrity. Storage savings and simpler retrieval are not guaranteed because decomposition can introduce additional keys and joins. Increased redundancy is opposite to the intended reduction of avoidable repetition, so it is not the benefit identified by this question.
Evidence in the complete study note
Suppose Enrollment repeats StudentName for every course. An update anomaly requires changing every copy of the student's name; missed copies disagree. An insertion anomaly prevents recording a new student until some enrollment exists. A deletion anomaly loses the only record of a student's details when their last enrollment is deleted. Revoke anomaly is not a normalization anomaly.FDB19
Normalization separates facts according to their determinants, reducing avoidable repetition and associated anomalies.FDB19
Reduced redundancy can improve integrity and storage use, but extra keys and joins mean neither storage savings nor faster retrieval is guaranteed.FDB19
Choice by choice
A · incorrect — Normalized retrieval may require more joins, so simplification does not follow.
B · incorrect — Normalization aims to reduce avoidable repetition.
C · correct — Reducing inconsistent updates and related anomalies improves integrity.
D · incorrect — Storage savings can be offset by extra keys and relation overhead.
Question 208
Which database design concept allows the division of a large database into smaller,
manageable units?
A. Data partitioning
B. Database replication
C. Data migration
D. Data archiving
Hint / Module Reference (Where to Read)
Distinguish logical records, disk pages and physical file organization.
FDB24 explicitly contrasts partitioning, replication, migration and archiving. Partitioning divides the dataset into smaller portions, matching the question. Replication makes additional copies, migration moves data, and archiving takes older data out of active service. Those operations can accompany partitioning, but none of them defines division into manageable units.
Evidence in the complete study note
A page/block is a unit of database I/O and holds several records. For fixed unspanned records of size r in usable page space B, the blocking factor is floor(B/r), and n records occupy ceil(n/blocking factor) pages. Page headers and slots reduce usable space.FDB24
Partitioning splits a large dataset; replication copies it; migration moves it; archiving removes older data from active service.FDB24
A heap has no search-key order and supports simple inserts, but searches may scan all pages.FDB24
Choice by choice
A · correct — Partitioning divides a large dataset into smaller portions or manageable units.
B · incorrect — Replication creates copies rather than dividing the original dataset into distinct units.
C · incorrect — Migration moves data to another location or system rather than defining a split.
D · incorrect — Archiving removes older data from active service rather than generally dividing the database.
Question 209
Which database design technique is used to capture the relationships between entities in a
graphical representation?
A. Relational data modeling
B. Entity-Relationship (ER) modeling
C. Object-oriented data modeling
D. Document data modeling
Hint / Module Reference (Where to Read)
Review entity, attribute and relationship notation before interpreting the model.
Correct answer: B — Entity-Relationship (ER) modeling
ERD is explicitly a graphical database-schema technique showing modeled entities and their important relationships. This directly establishes ER modeling for the graphical representation asked about.
Evidence in the complete study note
An entity is a distinguishable real or conceptual thing, such as one student or one course. An entity type describes common properties; its current collection of entities is an entity set. A relationship associates entities. In Chen notation rectangles represent entity types, diamonds relationships and ellipses attributes; double ellipses denote multivalued attributes and dashed ellipses derived attributes. Always inspect the diagram legend.FDB06
Conceptual ER models describe entities and relationships.FDB02
Relational models use tables; hierarchical models organize records in a parent–child tree; classic network models connect records through pointer-based owner/member links, allowing multiple parents.FDB02
Choice by choice
A · incorrect — Relational modeling describes tables; ERD is the named graphical technique. (PDF pp. 9.)
B · correct — ER modeling is the explicit graphical entities/relationships technique. (PDF pp. 9.)
C · incorrect — Object-oriented is named but not the described ERD technique. (PDF pp. 9.)
D · incorrect — Document modeling is not the technique defined in the module. (PDF pp. 9.)
Question 210
In contrary to the traditional file-based approach, in the database approach:
A. Programs define and access their own data
B. Store data items in multiple copies
C. Don't care about the definition of programs and data
D. Data and programs are defined independently
Hint / Module Reference (Where to Read)
Trace which schema levels a proposed change affects.
Correct answer: D — Data and programs are defined independently
The database approach insulates programs from data definitions and supports data independence. This is the relevant distinction from programs managing their own files.
Evidence in the complete study note
Physical data independence permits changing internal storage without changing the conceptual schema, for example adding an index. Logical data independence permits conceptual changes while preserving affected external views and application interfaces, for example splitting a table while maintaining a compatible view. It is harder because applications depend on logical meaning. Data abstraction hides irrelevant details; program–data insulation separates data definitions from application code.FDB04
Choice by choice
A · incorrect — Own program-specific definitions do not express the database approach's insulation. (PDF pp. 5, 6, 8.)
B · incorrect — Multiple uncontrolled copies are not the defining benefit and can cause inconsistency. (PDF pp. 5, 6, 8.)
C · incorrect — The approach explicitly maintains metadata and definitions, not indifference to them. (PDF pp. 5, 6, 8.)
D · correct — Independent definitions match program/data insulation and independence. (PDF pp. 5, 6, 8.)
Question 211
Assume you are a database designer and you are given a task to specify entities, relationships,
constraints on relations and data types for the data to be stored. At which level of the three-schema architecture
would this be addressed?
A. View level
B. Internal level
C. External level
D. Conceptual level
Hint / Module Reference (Where to Read)
Trace which schema levels a proposed change affects.
The conceptual level describes the whole database and explicitly concentrates on entities, data types, relationships, operations and constraints while hiding physical storage. The stem repeats those conceptual responsibilities.
Evidence in the complete study note
The external level gives each user group a selected view. The conceptual level describes the whole logical database: entity types, relationships, data types and constraints. The internal/physical level describes files, record placement and access paths. These are abstraction levels, distinct from client/server deployment tiers.FDB04
Choice by choice
A · incorrect — View level shows a user-group subset. (PDF pp. 7, 8.)
B · incorrect — Internal level describes physical storage and access paths. (PDF pp. 7, 8.)
C · incorrect — External is another name for user view, not the global description. (PDF pp. 7, 8.)
D · correct — Conceptual is the level explicitly listing the requested structural components. (PDF pp. 7, 8.)
Question 212
What is the purpose of normalization in database design?
A. To reduce redundancy and improve data integrity
B. To speed up query performance
C. To create complex relationships between tables
D. To encrypt sensitive data
Hint / Module Reference (Where to Read)
Distinguish dependency-based design problems from the contents of one sample table.
Correct answer: A — Reduce avoidable redundancy and associated integrity anomalies.
FDB19 defines normalization as separating facts according to their determinants, reducing avoidable repetition and insertion, deletion and update anomalies. That supports both parts of A. It does not promise universally faster queries, deliberately create complexity for its own sake or encrypt content. Performance and security need their own workload and threat-model choices.
Evidence in the complete study note
Suppose Enrollment repeats StudentName for every course. An update anomaly requires changing every copy of the student's name; missed copies disagree. An insertion anomaly prevents recording a new student until some enrollment exists. A deletion anomaly loses the only record of a student's details when their last enrollment is deleted. Revoke anomaly is not a normalization anomaly.FDB19
Normalization separates facts according to their determinants, reducing avoidable repetition and associated anomalies.FDB19
Adding a generated row ID may make insertion technically possible, but does not remove repeated facts or fix the dependency design.FDB19
Choice by choice
A · correct — Normalization reduces repeated facts and the anomalies that threaten data consistency.
B · incorrect — More joins may be needed, so faster query execution is not a universal normalization purpose.
C · incorrect — Relationships follow fact dependencies; creating complexity is not the goal.
D · incorrect — Normalization restructures facts and does not encrypt sensitive values.
Question 213
A weak entity uses its partial key together with the key of an independently keyed owner. Which terms describe that associated entity set? Select all that apply.
A. Identifying set
B. Strong entity set
C. Neighbor set
D. Owner set
Hint / Module Reference (Where to Read)
Check how a dependent entity obtains its complete identity.
Correct answers (select all): B, D — B. Strong entity set; D. Owner set
The weak entity lacks a complete independent identifier. Its owner's key combines with the partial key to identify it. Because the owner is independently keyed in this question, strong entity set and owner set both describe the associated set. Identifying describes the relationship connecting the sets, while neighbor does not state the necessary ownership and identification dependency.
Evidence in the complete study note
A weak entity lacks a complete independent key. Its owner is normally a strong entity, so owner entity set and strong entity set may both describe the associated set. Its partial key, also called discriminator, distinguishes weak entities of the same owner. Identification combines the owner key and partial key.FDB08
The weak entity participates totally in its identifying relationship.FDB08
Chen notation uses a double rectangle, double identifying diamond and dashed underline for a partial key.FDB08
Choice by choice
A · incorrect — Identifying is normally the relationship role rather than the defined name of the owner entity set.
B · correct — The owner that supplies an independent identifying key is normally a strong entity set.
C · incorrect — Neighbor does not express the necessary ownership and identification dependency.
D · correct — Owner set is the associated entity set whose key identifies the weak entity together with its partial key.
Question 214
A language that allows the DBA or user to describe and name the entities, attributes, and
relationships required for the application is__________
A. Transaction control language (TCL)
B. Data control language (DCL)
C. Data Manipulation Language (DML)
D. Data Definition Language (DDL)
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
Describing and naming application entities, attributes and relationships defines schema structure. FDB26 assigns structural changes to DDL through CREATE, ALTER and DROP. TCL manages transaction boundaries, DCL controls privileges and DML retrieves or changes data. These categories have different effects, so DDL uniquely matches the schema-description role in the stem.
Evidence in the complete study note
DDL changes structure: CREATE, ALTER, DROP, commonly RENAME and TRUNCATE in textbook classifications. DML retrieves or changes data: SELECT, INSERT, UPDATE, DELETE. Some texts separate SELECT as DQL. DCL manages privileges through GRANT and REVOKE. TCL includes COMMIT, ROLLBACK and SAVEPOINT. Classify a statement by its effect, not its length.FDB26
Choice by choice
A · incorrect — TCL manages commit, rollback and savepoints rather than defining schema elements.
B · incorrect — DCL manages privileges through GRANT and REVOKE rather than describing entities and attributes.
C · incorrect — DML retrieves or changes instance data rather than defining the application schema.
D · correct — DDL defines and changes schema structure, including tables, attributes and constraints.
Question 215
In relational-table terminology, which terms describe a unit of related field values treated together by an application? Select all that apply.
A. Rows
B. Field
C. Record
D. Database
Hint / Module Reference (Where to Read)
Distinguish a relation's structure from its current contents and its descriptive metadata.
Correct answers (select all): A, C — A. Rows; C. Record
A tuple holds a set of related attribute values as one table unit. Row and record are synonymous names for that unit, so both alternatives qualify. A field is only an individual property, and a database is the larger coherent collection of related data. Grammatical number in Rows does not create a different relational concept from Record.
Evidence in the complete study note
A relation is a set of tuples, represented as a table. A tuple/row/record contains related attribute values; an attribute/column/field describes a property. Degree counts columns; cardinality counts rows. Classical relations have distinct tuples, atomic cells and domains for attributes; row order has no meaning. Column order has no logical meaning when attributes are identified by name. SQL tables can contain duplicate rows unless constrained.FDB03
Choice by choice
A · correct — Rows contain related field values and are the relational counterpart of records.
B · incorrect — A field is an individual property rather than a collection of related fields.
C · correct — Record is the stated unit that groups related fields for application use.
D · incorrect — A database contains a larger coherent collection of data rather than just one record.
Question 216
Which template creates a table with one column? Treat table_name, column_name and column_type as placeholders for valid identifiers and a data type.
A. CREATE table_name (column_name, column type);
B. CREATE table_name (column_type, column name);
C. CREATE TABLE table_name (column_name column_type);
D. CREATE TABLE table_name (column_type, column name);
Hint / Module Reference (Where to Read)
Distinguish table definition changes from changes to stored rows.
Correct answer: C — C. CREATE TABLE table_name (column_name column_type);
CREATE TABLE introduces a new table definition. Within its parentheses, each column declaration places the column name before its data type, without a comma between those two parts. Commas separate whole declarations. Option C matches this pattern. A and B omit TABLE, while D reverses and separates the intended name/type pair and therefore fails the requested template.
Evidence in the complete study note
CREATE TABLE Employee (EmpID INTEGER PRIMARY KEY, Name VARCHAR(80)); declares two columns. The name and its type stay together; the comma separates whole column declarations. To add a primary key later: ALTER TABLE Employee ADD PRIMARY KEY (EmpID); existing data must be unique and non-NULL. A table cannot gain a second primary key without replacing the existing constraint.FDB30
Choice by choice
A · incorrect — TABLE is absent and the column declaration is split incorrectly.
B · incorrect — TABLE is absent and the name/type order is reversed.
C · correct — The name/type pair forms one complete column declaration.
D · incorrect — The type/name order and comma do not form the intended declaration.
Question 217
From the following alternatives which one is FALSE about database system?
A. It is very difficult to protect a file under the system
B. In database system the user is not required to write the procedures
C. In database system, DBMS provides a good protection mechanism.
D. It contains a wide variety of sophisticated techniques to store and retrieve the data.
Hint / Module Reference (Where to Read)
Review the database approach and match each claimed benefit or cost to its mechanism.
Correct answer: A — The claim that protection is very difficult under a database system is false.
FDB01 contrasts unmanaged file difficulties with DBMS authorization, shared integrity, transactions and recovery. A therefore reverses a stated database benefit. B is understood in the data-access sense: declarative requests need not prescribe retrieval procedures, as FDB36 explains; it does not mean applications never contain code. Protection and sophisticated storage/access facilities in C and D are consistent with the note.
Evidence in the complete study note
A database is a coherent collection of related facts with meaning for an application. A DBMS is software that defines, stores, retrieves, updates, protects and shares that data. The database is the content; the DBMS manages it. A database system also includes applications, people and infrastructure.FDB01
Traditional file applications define their own formats and access procedures.FDB01
A DBMS centralizes metadata and supports multiple views, authorization, transactions, backup and recovery.FDB01
Choice by choice
A · correct — This reverses the note's benefit: a DBMS provides coordinated protection rather than inherently making protection very difficult.
B · incorrect — Declarative data requests need not prescribe retrieval procedures; this does not exclude ordinary application code.
C · incorrect — Authorization and controlled data access are explicit DBMS protection mechanisms.
D · incorrect — DBMS storage and access facilities include indexes, buffers, query processing and other sophisticated mechanisms.
Question 218
Consider a directed line(->) from the relationship set advisor to both entity sets instructor
and student. This indicates _________ cardinality
A. Many to one
B. Many to many
C. One to one
D. One to many
Hint / Module Reference (Where to Read)
Read the relationship in both directions and check participation constraints.
Correct answer: C — One-to-one under the common ER arrow convention.
FDB09 explicitly discusses arrows from advisor to both Instructor and Student. Under its common convention, each arrow imposes a maximum of one on the corresponding end. Both ends are therefore maximum-one, producing 1:1. This does not imply total participation, and another diagram notation would require a legend, but the complete note supplies the convention for this example.
Evidence in the complete study note
Read business rules in both directions. If each employee belongs to one department and each department has many employees, Employee→Department is N:1; Department→Employee is 1:N. Building→Location could also be N:1, so names alone cannot select a unique example. Under the common arrow convention, arrows from advisor to both Instructor and Student impose maximum-one at both ends, giving 1:1. Without a notation convention, arrows are unsafe evidence.FDB09
Choice by choice
A · incorrect — Many-to-one permits many at one end, contrary to maximum-one arrows at both ends.
B · incorrect — Many-to-many imposes neither of the maximum-one constraints stated by the arrows.
C · correct — Maximum one at both ends produces a one-to-one relationship.
D · incorrect — One-to-many permits multiple associations at one end rather than maximum one at both.
Question 219
What is the basic goal of Normalization?
A. To decrease data integrity
B. To reduce redundancy
C. To maximize transitive dependency
D. To increase dependency
Hint / Module Reference (Where to Read)
Distinguish dependency-based design problems from the contents of one sample table.
Normalization separates facts by their determinants to reduce repeated storage and the anomalies that repetition can cause. FDB19 therefore supports reducing redundancy as the basic listed goal. Decreasing integrity is the opposite intended effect, and maximizing transitive or unspecified dependencies is not a normalization objective. Proper dependencies are analyzed rather than increased indiscriminately.
Evidence in the complete study note
Suppose Enrollment repeats StudentName for every course. An update anomaly requires changing every copy of the student's name; missed copies disagree. An insertion anomaly prevents recording a new student until some enrollment exists. A deletion anomaly loses the only record of a student's details when their last enrollment is deleted. Revoke anomaly is not a normalization anomaly.FDB19
Normalization separates facts according to their determinants, reducing avoidable repetition and associated anomalies.FDB19
Remove dependencies of nonprime facts on nonkey determinants.FDB21
Choice by choice
A · incorrect — Normalization aims to improve integrity by reducing anomalies, not decrease it.
B · correct — Reducing avoidable repeated facts is a basic normalization goal.
C · incorrect — Problematic nonkey transitive dependencies are removed or separated rather than maximized.
D · incorrect — Dependencies are analyzed to place facts correctly; increasing them indiscriminately is not the objective.
Question 220
For a relation with ID, CITY and NAME, ID is guaranteed unique and non-NULL while CITY and NAME may repeat. Which listed sets are superkeys? Select all that apply.
A. NAME
B. ID
C. CITY
D. CITY, ID
Hint / Module Reference (Where to Read)
Test uniqueness and minimality separately when classifying keys.
Correct answers (select all): B, D — B. ID; D. CITY, ID
A superkey is any set of attributes guaranteed to identify a tuple. ID has that guarantee, and the larger set containing CITY and ID keeps it. Both sets therefore qualify, even though the larger one is not minimal. CITY and NAME may repeat and cannot independently identify all tuples. Minimality would distinguish candidate keys, but it is not required for superkeys.
Evidence in the complete study note
A superkey uniquely identifies a tuple. A candidate key is a minimal superkey: removing any component loses uniqueness. Minimal means no unnecessary attribute, not necessarily the fewest columns among all keys. A primary key is one selected candidate key; remaining candidates are alternate keys. A composite key contains multiple attributes and need not be the chosen primary key.FDB07
If ID is unique, both {ID} and {ID,City} are superkeys, but only {ID} is minimal.FDB07
A table can have several candidates and foreign keys but at most one declared primary-key constraint, possibly containing several columns.FDB07
Choice by choice
A · incorrect — Repeated names are permitted, so NAME is not guaranteed unique.
B · correct — ID uniquely identifies every tuple.
C · incorrect — Repeated cities are permitted, so CITY is not guaranteed unique.
D · correct — Including unique ID preserves identification even with the extra CITY attribute.
Question 221
______________is one of the candidate keys chosen by the database designer to uniquely
identify the entity set.
A. Super key
B. Candidate key
C. Social security key
D. Primary key
Hint / Module Reference (Where to Read)
Test uniqueness and minimality separately when classifying keys.
FDB07 defines a primary key as the selected candidate key, with the remaining candidates called alternate keys. The stem asks specifically for the designer's selection rather than every unique attribute set. Superkey is broader and candidate key denotes eligibility before selection. A social-security identifier could be chosen, but is not the generic name of that role.
Evidence in the complete study note
A superkey uniquely identifies a tuple. A candidate key is a minimal superkey: removing any component loses uniqueness. Minimal means no unnecessary attribute, not necessarily the fewest columns among all keys. A primary key is one selected candidate key; remaining candidates are alternate keys. A composite key contains multiple attributes and need not be the chosen primary key.FDB07
Choice by choice
A · incorrect — A superkey is any unique identifier set and may include unnecessary attributes.
B · incorrect — Candidate keys are eligible minimal identifiers, not specifically the one selected for the primary role.
C · incorrect — A social-security identifier is a possible attribute choice, not the generic selected-key category.
D · correct — The selected candidate key is the primary key.
Question 222
What is the difference between a primary key and a foreign key?
Hint / Module Reference (Where to Read)
Test uniqueness and minimality separately when classifying keys.
Answer: A primary key is the selected unique non-NULL identifier; a foreign key references an eligible key.
FDB07 defines the primary key as a selected candidate key. FDB15 requires non-NULL foreign-key values to match an eligible referenced unique/candidate key; foreign keys need not be unique and may permit NULL.
Evidence in the complete study note
A superkey uniquely identifies a tuple. A candidate key is a minimal superkey: removing any component loses uniqueness. Minimal means no unnecessary attribute, not necessarily the fewest columns among all keys. A primary key is one selected candidate key; remaining candidates are alternate keys. A composite key contains multiple attributes and need not be the chosen primary key.FDB07
Referential integrity requires a non-NULL foreign-key value to match an eligible referenced unique/candidate key; self-references are allowed.FDB15
Entity integrity requires every primary-key component to be non-NULL.FDB15
Question 223
Why is database normalization important? What problems does it solve?
Hint / Module Reference (Where to Read)
Distinguish dependency-based design problems from the contents of one sample table.
Answer: Normalization reduces repeated facts and insertion, deletion and update anomalies.
Repeating a student name across enrollments risks inconsistent updates, prevents storing some facts without unrelated rows and can lose a fact when its last repeated row is deleted. Normalization separates facts by their determinants to reduce these anomalies.
Evidence in the complete study note
Suppose Enrollment repeats StudentName for every course. An update anomaly requires changing every copy of the student's name; missed copies disagree. An insertion anomaly prevents recording a new student until some enrollment exists. A deletion anomaly loses the only record of a student's details when their last enrollment is deleted. Revoke anomaly is not a normalization anomaly.FDB19
Normalization separates facts according to their determinants, reducing avoidable repetition and associated anomalies.FDB19
Adding a generated row ID may make insertion technically possible, but does not remove repeated facts or fix the dependency design.FDB19
Question 224
What happens if you UPDATE a row but forget to include a WHERE clause?
Hint / Module Reference (Where to Read)
Track which rows each data-changing command affects.
Answer: UPDATE without WHERE targets every row, subject to constraints and transaction outcome.
FDB29 states that an UPDATE without WHERE targets every row. The SET expressions are therefore applied to all rows rather than one intended row; constraints or errors can still prevent completion.
Evidence in the complete study note
INSERT INTO Hotel(HotelID,HotelName,SubCity) VALUES (1,'Hilton','Yeka'); pairs each named column with a compatible value. Counts and types must match; keys and other constraints still apply. Without a column list, values must follow the table's declared column order.FDB29
UPDATE without WHERE targets every row.FDB29
UPDATE Book SET Price = 600 WHERE ID = 'B001'; changes matching rows.FDB29
Question 225
Why would you use a ROLLBACK command in a transaction?
Hint / Module Reference (Where to Read)
Match each transaction guarantee to its precise failure or interference scenario.
Answer: ROLLBACK undoes uncommitted transaction work, for example after a failure or unwanted change.
FDB37 contrasts COMMIT, which completes transaction work, with ROLLBACK, which undoes uncommitted work. Rollback restores the prior transactional state after errors or a decision not to keep the changes; it does not erase other users' committed transactions.
Evidence in the complete study note
For a transfer, subtracting 100 from one balance and adding 100 to another must commit together; losing either half violates atomicity and may violate the total-balance invariant. BEGIN starts work, COMMIT completes it, ROLLBACK undoes uncommitted work and SAVEPOINT marks an intermediate rollback point. A failed transaction may roll back while other users' committed transactions remain. SQL transaction syntax and error-handling constructs differ by DBMS; @@ERROR/IF is SQL Server-specific.FDB37
Question 226
What is the difference between DELETE and DROP?
Hint / Module Reference (Where to Read)
Classify each SQL command by the kind of change it performs.
Answer: DELETE removes rows and retains the table; DROP TABLE removes the table object.
FDB26 distinguishes data deletion from structural removal. DELETE may target selected rows with WHERE, or all rows without it, while leaving the table defined. DROP TABLE removes that relation object.
Evidence in the complete study note
DELETE removes rows and keeps the table; DROP TABLE removes the table object; ALTER changes its definition. TRUNCATE empties a table using implementation-specific mechanisms. PostgreSQL allows transactional rollback of TRUNCATE, so no rollback information is retained is not a universal SQL property. SQL Server permits DROP DATABASE Sales, suppliers; that comma-list syntax is vendor-specific. SQLite's .databases lists attached names/files in its command-line shell; .database is not the documented command.FDB26
UPDATE without WHERE targets every row.FDB29
DELETE FROM r; removes every row but leaves r defined.FDB29