Fundamentals of Database Systems

Chapter 1: Database Systems (Questions 1-5)

Question 1:

Which of the following is NOT a disadvantage of file-processing systems?

Data redundancy

Data inconsistency

Program-data dependence

Efficient centralized data sharing

Question 2:

What does DBMS stand for?

Database Management System

Data Backup Management Software

Distributed Binary Management System

Database Monitoring Service

Question 3:

What is data redundancy in the context of database systems?

Unnecessarily storing the same data in multiple locations.

The process of removing duplicate records from a database.

Compressing data to save storage space.

Encrypting data for security purposes.

Question 4:

What is the fundamental difference between data and metadata?

Data is processed information, whereas metadata represents raw, unorganized facts.

Metadata refers strictly to historical transaction backups stored offline.

Metadata is "data about data," describing the data characteristics, data types, and relationships of end-user data.

Metadata is unstructured text generated exclusively during ad-hoc reporting.

Question 5:

Which of the following correctly identifies the five major components of a complete database system environment?

Hardware, Software, People, Procedures, Data

Input, Processing, Storage, Control, Output

Tables, Tuples, Attributes, Domains, Schemas

DDL, DML, TCL, DCL, Relational Algebra

Chapter 2: Data Models (Questions 6-10)

Question 6:

What is the primary characteristic of the relational data model?

Data is stored in a hierarchical tree structure.

Data is perceived as tables with rows and columns.

Data is stored in network graph structures.

Data is stored in key-value pairs only.

Question 7:

Which data model is best suited for handling unstructured and semi-structured big data?

Hierarchical model

Network model

Relational model

Object-oriented and NoSQL models

Question 8:

What are the four basic building blocks of all data models?

Hardware, software, people, and procedures

Tables, rows, columns, and foreign keys

1NF, 2NF, 3NF, and BCNF

Entities, attributes, relationships, and constraints

Question 9:

When translating organizational business rules into a data model, which standard grammatical convention is applied?

Adjectives translate to entities, and adverbs translate to relationships.

Verbs translate to primary keys, and conjunctions translate to foreign keys.

Nouns translate to entities or attributes, while active verbs translate to relationships between entities.

Nouns translate to constraints, and prepositions translate to tables.

Question 10:

How is data structurally arranged in the traditional hierarchical data model?

In an upside-down tree structure of 1:M parent-child segments where each child can have only one parent.

In two-dimensional tables linked by value-based foreign keys.

In graph structures where every record can have multiple parents via set types.

In document collections indexed by object identifiers.

Chapter 3: The Relational Database Model (Questions 11-15)

Question 11:

What is referential integrity?

A condition where every foreign key value must either be null or match an existing primary key value.

A condition where all rows must be unique.

A condition where all columns must have the same data type.

A condition where every table must have an index.

Question 12:

What is a primary key?

An attribute that can have null values.

An attribute or combination of attributes that uniquely identifies each row in a table.

An attribute that references another table's primary key.

An attribute used only for sorting purposes.

Question 13:

What operational condition is required to maintain Entity Integrity?

A foreign key must match an existing primary key or be null.

Every primary key value must be unique, and no part of a primary key may contain a null value.

Every table must contain at least one foreign key.

Candidate keys must consist exclusively of integer data types.

Question 14:

What does the term 'degree' refer to in a relational table?

The number of rows in a table.

The number of attributes (columns) in a table.

The number of foreign keys in a table.

The number of indexes on a table.

Question 15:

Which relational algebra operator produces a horizontal subset of rows from a table based on a specified condition?

PROJECT

UNION

SELECT (RESTRICT)

PRODUCT

Chapter 4: Entity Relationship (ER) Modeling (Questions 16-20)

Question 16:

What is a composite attribute?

An attribute that can have multiple values.

An attribute that can be divided into smaller sub-parts with independent meanings.

An attribute whose value is calculated from other attributes.

An attribute that uniquely identifies an entity.

Question 17:

What symbol is used to represent a weak entity in an ER diagram?

A single rectangle

A double rectangle

A diamond

An ellipse

Question 18:

When is a relationship between a parent and child entity classified as strong (identifying)?

When the foreign key in the child entity allows null values.

When the relationship between the two entities is Many-to-Many.

When the primary key of the child entity contains the primary key of the parent entity.

When neither entity depends on the other for existence.

Question 19:

If a course can exist in a university catalog before any class sections are scheduled for it, what is the participation of CLASS relative to COURSE?

Mandatory participation

Optional participation

Recursive participation

Associative participation

Question 20:

An attribute such as EMP_AGE that is calculated using EMP_DOB and the current system date is classified as a:

Derived attribute

Multivalued attribute

Composite attribute

Simple identifier

Chapter 6: Normalization of Database Tables (Questions 21-25)

Question 21:

Why might a database designer deliberately choose to denormalize a database design?

To eliminate all data redundancy and prevent data anomalies.

To ensure compliance with Boyce-Codd Normal Form.

To enforce referential integrity without index structures.

To improve query execution speed and processing performance by reducing complex table joins.

Question 22:

If EMP_NUM → DEPT_CODE and DEPT_CODE → DEPT_NAME, the relationship EMP_NUM → DEPT_NAME represents which type of dependency?

Transitive dependency

Partial dependency

Multivalued dependency

Trivial dependency

Question 23:

Under which structural condition is a table in 1NF automatically in Second Normal Form (2NF)?

When the table contains no foreign keys.

When all nonprime attributes determine each other.

When the table's primary key consists of a single attribute (non-composite).

When the table contains at least three candidate keys.

Question 24:

Which normal form deals with removing partial dependencies?

1NF

2NF

3NF

BCNF

Question 25:

What condition must be met for a relational table to satisfy Boyce-Codd Normal Form (BCNF)?

The table must be in 2NF and have no candidate keys.

Every determinant in the table must be a candidate key.

The table must contain no foreign keys.

Every nonprime attribute must functionally determine the primary key.

Chapter 7: Structured Query Language (SQL) (Questions 26-30)

Question 26:

What is the standard logical operator order of evaluation in an SQL WHERE clause when parentheses are omitted?

NOT first, then AND, then OR

OR first, then AND, then NOT

AND first, then OR, then NOT

Left-to-right strictly, regardless of operator

Question 27:

What is the purpose of the ORDER BY clause in SQL?

To filter rows based on a condition.

To sort the result set in ascending or descending order.

To group rows with the same values.

To join two tables together.

Question 28:

Which SQL operator and wildcard combination searches for customers whose last names begin with the letter 'J' followed by any number of characters?

WHERE CUS_LNAME = 'J*'

WHERE CUS_LNAME LIKE 'J%'

WHERE CUS_LNAME IN ('J_')

WHERE CUS_LNAME IS 'J%'

Question 29:

Which SQL clause is used to filter aggregated group results rather than individual rows?

WHERE

ORDER BY

HAVING

FROM

Question 30:

SQL commands such as CREATE TABLE, ALTER TABLE, and DROP TABLE belong to which category?

Data Manipulation Language (DML)

Transaction Control Language (TCL)

Data Definition Language (DDL)

Data Control Language (DCL)

Part II: Short Answer Questions — SQL Queries (Questions 31-35)

Question 31:

Question 35: Multi-Table Join with Aggregation (CUSTOMER, INVOICE, LINE) Write an SQL query to determine the total purchase spending for each customer who has made at least one purchase. - Connect the CUSTOMER, INVOICE, and LINE tables. - Display the customer code (CUS_CODE), customer last name (CUS_LNAME), and their total cumulative spending labeled as TOTAL_SPENT (sum of LINE_UNITS * LINE_PRICE). - Group the results appropriately by customer code and last name. - Sort the results alphabetically by last name (CUS_LNAME).

Question 32:

Question 34: Uncorrelated Subquery in Filtering (PRODUCT Table) Management needs to review premium catalog items. Write an SQL query to list the product code (P_CODE), description (P_DESCRIPT), and price (P_PRICE) of all products whose price is strictly greater than the average price of all products in the database. Note: You must use a subquery to calculate the average price.

Question 33:

Question 31: Inventory Stock & Computed Values (PRODUCT Table) SaleCo warehouse managers need to identify products that need reordering. Write an ANSI SQL query on the PRODUCT table that displays: - The product code (P_CODE), description (P_DESCRIPT), and unit price (P_PRICE). - A computed column named INVENTORY_VALUE representing total inventory value on hand (P_QOH * P_PRICE). - Condition: Include only products where the quantity on hand (P_QOH) is less than or equal to the minimum stock level (P_MIN). - Sorting: Order the results by INVENTORY_VALUE from highest to lowest.

Question 34:

Question 33: Aggregation and Group Restriction (LINE Table) Write an SQL query using the LINE table to calculate the total monetary value of each invoice. - Display the invoice number (INV_NUMBER) and the computed total cost of the invoice labeled as INVOICE_TOTAL (calculated as the sum of LINE_UNITS * LINE_PRICE). - Condition: Restrict output to only include invoices where the calculated invoice total exceeds $100.00. - Sorting: Order the results from highest invoice total to lowest.

Question 35:

Question 32: Two-Table Relational Join (PRODUCT & VENDOR) Write an ANSI SQL query to display the vendor name (V_NAME), vendor contact person (V_CONTACT), product description (P_DESCRIPT), and unit price (P_PRICE) for all products supplied by vendors located in Florida ('FL') or Tennessee ('TN') that have a unit price greater than $50.00. Note: You must use explicit ANSI JOIN ... ON syntax.

Fundamentals of Database Systems — Examination