Database Management Final Exam

TEXT can be used for a variable number of alphanumeric characters

True

How many bytes are used to store the string "Sherlock" if the data type is TEXT?

8

Denormalization almost always leads to more storage space for raw data.

True

A factor to consider when choosing a file organization is:

1. fast Data retrieval

2. security

3. efficient storage

4. all of the above

All of the above

An index on columns from two or more tables that come from the same domain of values is called a:

Join Index

A rule of thumb for choosing indexes is to

1. Be careful indexing attributes that may be null.

2. Index each primary key of each table.

3. Use an index when there is variety in attribute values.

4. All of the above

All of the above

How SQLite stores dates?

1. TEXT

2. REAL

3. INTEGER

4. All of the above

All of the above

Which of the following data types can be used to store a video file?

BLOB

What is one potential way to handle missing data?

substitute an estimate for the missing value.

Which of the following is a purpose of the SQL standard?

1. To specify syntax and semantics of SQL data definition and manipulation

2. To specify minimal and complete standards, which permit different degrees of adoption in products

3. To define the data structures and basic operations for SQL
   databases

4. All of the above

all of the above

The benefits of a standardized relational language include:

1. Application longevity

2. Reduced training costs

3. Cross-system communication

4. All of the above

All of the above

The ________ is the structure that contains descriptions of objects such as tables and views created by users.

Schema

__ is a set of commands used to control a database, which includes security.

DCL (data classification system)

The date of birth for the customer wilbur smith is in the wrong format and needs to be charged to ‘1933-01-09’. Write a SQL script that makes the correction.

Hint: UPDATE table_name SET column_name = value WHERE condition;

UPDATE Customer_T

SET customerDoB = '1933-01-09'

WHERE customerID = 1006;

WHERE is a mandatory clause.

False

ORDER BY is executed before SELECT

False

AND is processed before OR.

True

Each SQL command should end with a period.

False

In relational databases, the relationship between multiple tables is typically established by including a common column in each table.

True

A correlated subquery is a type of subquery where the inner query depends on values from the outer query for its execution

True

Which SQL operation is used to combine the result sets of two or more SELECT queries into a single result set, with each result appearing as separate rows in the output?

UNION

What is Cartesian Join?

All possible combinations of the rows from the two tables.

What is the difference between an Inner Join and an Outer Join in SQL?

• Inner Join: An Inner Join returns only the rows where there is a match in both tables. If a row in either table does not have a corresponding match in the other table, it will be excluded from the result. Inner joins are useful when you only want to retrieve records that have complete information across the tables being joined.

• Outer Join: An Outer Join, on the other hand, returns all rows from one or both tables, including those that do not have a matching row in the other table.

A trigger in SQL requires an explicit call to execute.

False

Which of the following is NOT a benefit of using user-defined transactions in a database?

They require no knowledge of transaction integrity or recovery commands by application programmers.

Write an SQL query to retrieve the OrderID and CustomerName for all orders, including any orders that have not yet been assigned to a customer. Use a RIGHT OUTER JOIN to combine the Order_T and Customer_T tables, with CustomerID as the common column

SELECT Order_T.OrderID, Customer_T.CustomerName

FROM Customer_T

RIGHT OUTER JOIN Order_T ON Customer_T.CustomerID =

Order_T.CustomerID;

In a client/server architecture, which of the following statements about the Processing Logic layer is correct?

It manages tasks such as data validation and applying business rules.

In a thin client two-tier systems, where is the processing logic primarily located?

On the server

The client workstation in a client/server architecture is primarily focused on back-end functions.

False

Reorganize the steps for database access in the correct order:

1. Process the results of the query.

2. Open a connection to a database.

3. Identify and register a database driver.

4. Close the connection to the database.

5. Execute a query against the database.

6. Repeat steps 3-4 as necessary.

1. Identify and register a database driver.

2. Open a connection to a database.

3. Execute a query against the database.

4. Process the results of the query.

5. Repeat steps 3-4 as necessary.

6. Close the connection to the database.

Which of the following measures are used to ensure data security or manage external access in a web-based application?

1. Firewalls to limit access and data movement.

2. Proxy servers to cache frequently requested pages and manage communication.

3. Database administrators setting internal access controls

through the DBMS.

4. All of the above.

All of the above

When a client requests static content, such as an HTML file, the web server forwards the request to the application server to retrieve data from the database.

false

Which cloud service model provides full applications hosted on the cloud, accessible via the internet?

Software-as-a-Service (SaaS)

Which of the following is NOT a key advantage of XML?

High-speed performance compared to binary formats.

Which of the following characteristics is NOT typically associated with a data warehouse?

Time-Invariant

Which of the following is NOT a reason for the significant advancement of data warehousing?

Increase in Manual Data Processing (e.g., increased reliance on spreadsheets)

What are the two primary needs that drive the development of data warehousing?

1. A business requires an integrated, company-wide view of high-
   quality information.

2. The information systems department must separate informational from operational systems to improve performance dramatically in managing company

Operational systems primarily focus on historical snapshots and predictive data.

False

A retail company is planning to improve its ability to track and respond to customer purchases in real time. The company wants to integrate data from various sources, such as point-of-sale systems, online transactions, and customer feedback, to offer personalized recommendations instantly as customers interact with the website and mobile app. They need a system that can process data in real-time, ensuring that sales representatives, marketing teams, and customer support can make decisions based on up-to-the-minute data.

Logical Data Mart and Real-Time Data Warehouse Architecture

In periodic data, each update overwrites the previous record, leaving no trace of history.

False

The Star Schema is commonly used in data warehouses.

True

Which of the following is NOT an indicator of high-quality data?

Subjectivity

Type 1: Overwriting the current value is a suitable way to handle Slowly Changing Dimensions (SCD) when tracking changes is important

False