Database Final Exam

What type of functional dependency does NOT exist in the table ENROLLMENT? Question options: Trivial Full key Partial Transitive

Full key

Which of the following is NOT true? In a relational table: Question options: Two rows can have the same values Two columns can have the same values All values in a column are from the same domain All column names are different

Two rows can have the same values

Normalizing table ENROLLMENT to 3NF will result in: Question options: No changes (table ENROLLMENT remains as is, no additional tables) Two separate tables Three separate tables Four separate tables

Two separate tables

Normalizing table FLIGHT to 3NF will result in: Question 9 options: No changes (table FLIGHT remains as is, no additional tables) Two separate tables Three separate tables Four separate tables

Two separate tables

The ________ slowly changing dimension approach changes the old value in the dimension's record with the new value. Question options: Type 1 Type 2 Type 3 Type 0

Type 1

Which of the following designs allows reusing (instead of duplication) of dimensions? Question options: Time column Snowflaking Galaxy of stars Degenerate dimension

Galaxy of stars

Which of the following is an example of analytical information? Question options: Information about the amount of money in a checking account Information showing which airline routes in the United States have the most sales Information about the cost of a product Information showing the destination and origin of a particular airline route in the United States

Information showing which airline routs in the United States have the most sales

What type of functional dependency does NOT exist in the table FLIGHT? Question 7 options: Trivial Full key Partial Transitive

Partial

The following attribute can contain more than one value for each entity instance: Question options: Unique attribute Composite attribute Multivalued attribute Derived attribute

Multivalued attribute

Information can be used for ____________. Question options: Operational purposes only Analytical purposes only Operational and analytical purposes Neither for operational nor for analytical purposes

Operational and analytical purposes

The following attribute represents a collection of attributes: Question options: Unique attribute Composite attribute Multivalued attribute Derived attribute

Composite attribute

The dimensional schema is often referred to as the: Question options: Fact tables Dimension tables Star schema Transaction schema

Star schema

Which of the following is an invalid SQL keyword? Question options: Select SE_LECT selectT SeLeCt

SE_LECT

How many entities are involved in a unary relationship? Question options: 1 2 More than 2 Between 1 and 2

1

How many columns will a relation resulting from mapping the Entity Y have? Question options: 2 3 4 5

3

How many entities are depicted by the following requirements? (School XYZ keeps track of its 100 student, 10 teachers and 5 classrooms) Question options: 3 4 115 116

3

An ER diagram that contains two entities involved in two separate M:N relationships will be mapped as how many relations? Question options: 1 2 3 4

4

Which of the following is true according to the ABC Retailer ER diagram? Question options: A brand can have many products but it does not have to have any products A brand can have many products and it has to have at least one product Each brand is associated with exactly one product A brand is not associated with any products

A brand can have many products and it has to have at least one product

Which of the following is NOT true? Mapping an M:N relationship that has no attributes will result in: Question options: A new relation Two foreign keys A composite primary key A foreign key in both of the relations mapped from the involved entities

A composite primary key

Which of the following is true according to the ABC Retailer ER diagram? Question options: A promotion can be associated with many products and it has to be associated with at least one product A promotion can be associated with many products but it does not have to be associated with any products Each promotion is associated with exactly one product A promotion is not associated with any products

A promotion can be associated with many products and it has to be associated with at least one product

Which of the following is a part of a DDL statement? Question options: CREATE INSERT DELETE SELECT

CREATE

Which of the following is a part of the database requirements step? Question options: Database deployment Logical modeling Conceptual modeling Database implementation

Conceptual Modeling

Which of the following processes is typically the most time- and resource-consuming? Question options: Creating the data warehouse tables using the DBMS functionalities Creating the OLAP query Creating the ETL infrastructure Creating the data warehouse model

Creating the ETL infrastructure

Database implementation involves using ________ to implement the database model as an actual database. Question options: Front-end applications DBMS Database system Indirect interaction

DBMS

Joe is an example of ____________. Question options: Data Metadata Data Type DBMS

Data

Which of the following is typically found in the database metadata? Question options: Employee names Data Types Student ages Product prices

Data Types

________ are in charge of implementing the database model as a functioning database using the DBMS software. Question options: Database analysts Database architects Database developers DBAs

Database developers

Which of the following (regarding the relation ENROLLMENT) represents the deletion anomaly? Question options: Deleting a student ID may delete a student name Deleting a class ID may delete the class name Deleting a class may delete all information about a student Deleting a student may delete all information about a class

Deleting a student may delete all information about a class

Which of the following is true according to the ABC Retailer ER diagram? Question options: A product can belong to multiple brands but it must belong to at least one brand Each product belongs to multiple brands Each product belongs to exactly one brand Each product belongs to either one brand or no brand at all

Each product belongs to exactly one brand

Which of the following is true according to the ABC Retailer ER diagram? Question options: Each promotion has a unique promotion name Each brand has a unique brand name Each product has a unique product ID All of the above

Each product has a unique product ID

________ contain sum measures related to the subject of analysis. Question options: Fact tables Dimension tables Both dimension and fact tables None of the above

Fact tables

If applicable, the archived data from operational sources is loaded in the data warehouse during the: Question options: Refresh load First load Final load Archived data can never be loaded in the data warehouse

First load

Every relation contains: Question 2 options: Full key functional dependency Partial functional dependency Transitive functional dependency All of the above

Full key functional dependency

An associative entity is used as an alternative way of depicting: Question options: 1:M relationships 1:1 relationships M:N relationships Multivated attributes

M:N relationships

The description of the structure and the properties of the data fits into which category? Question options: Information Data Metadata DBMS

Metadata

In the case of so-called active data warehouses, the loads occur in ________ that happen continuously, ensuring that the data in the data warehouse is updated close to real time (enabling analysis of the latest data). Question options: Dimensional modeling actions OLAP actions OLTP actions Micro batches

Micro batches

OLAP tools are: Question options: Write-only tools Read-only tools Read and write tools Read, write, and delete tools

Read-only tools

Tuple is a synonym for Question options: Column Field Row Table

Row

Relation is a synonym for Question options: Column Field Row Table

Table

The ETL infrastructure is essentially predetermined by the results of the requirements collection and data warehouse modeling processes that specify: Question options: The OLAP/BI tools The alpha release The sources and the target The refresh cycle

The alpha release

Data cleansing is a part of: Question options: OLAP Extraction Transformation Load

Transformation

The ________ slowly changing dimension approach involves creating a previous and current column in the dimension table for each column where changes are anticipated. Question options: Type 1 Type 2 Type 3 Type 0

Type 3

The following attribute represents a collection of attributes: Question options: Composite attribute Multivalued attribute Derived attribute Unique composite attribute

Composite attribute

The set of processes that is used to populate the data warehouse tables with the appropriate relevant data retrieved from the operational databases is known as: Question options: BI ETL OLAP OLTP

ETL

Which of the following functional dependencies in the table FLIGHT is an augmented functional dependency? Question 6 options: FlightID, Date --> NoOfPassengers FlightID --> Origin FlightID --> Destination FlightID, Date --> Origin

FlightID, Date --> Origin

Table ENROLLMENT is: Question options: Not in 1NF In 1NF but not in 2NF In 2NF but not in 3NF In 3NF

In 2NF but not in 3NF

Table FLIGHT is: Question 8 options: Not in 1NF In 1NF but not in 2NF In 2NF but not in 3NF In 3NF

In 2NF but not in 3NF

Relationship attributes may be necessary in a relationship of what type? Question options: One-to-one One-to-many Zero-to-many Many-to-many

One-to-many

Which of the following is a possible type of relationship (maximum cardinality-wise)? Question options: One-to-one Zero-to-zero Zero-to-one Zero-to-many

One-to-one

In a typical, properly designed star schema, the number of records (rows) in any of the dimension tables is: Question options: Larger than in the fact table Same as in the fact table Smaller than in the fact table No typical rule — on average, there is an even number of dimension tables with larger, smaller and same number of records as in the fact table

Smaller than in the fact table

What will be the result of the following query on table STUDENT? SELECT COUNT(*) FROM student GROUP BY class; Question options: Number 5 Number 2 Numbers 2 and 3 Numbers 1 and 1

Numbers 2 and 3

Querying and presenting data from data warehouses and/or data marts for analytical purposes is known as: Question options: ETL OLAP OLTP Micro batches

OLAP

What will be the result of the following query on table STUDENT? SELECT * FROM student; Question options: The entire relation STUDENT will be displayed StudentID column of the relation STUDENT will be displayed First record of the relation STUDENT will be displayed Last record of the relation STUDENT will be displayed

The entire relation STUDENT will be displayed

In cases where multiple subjects of analysis share dimensions, the following occurs: Question options: A star schema is composed strictly of fact tables A dimensional model contains more than one fact table Some dimensions are eliminated All dimensions are reduced

A dimensional model contains more than one fact table

If a DBMS enforces a DELETE RESTRICT option on the referential integrity constraint between CLIENT and AGENT in the HAPPY INSURANCE database, what will be the outcome after a user tries to delete the last record (A4, James, 3, 90, 2010, A3) from AGENT? Question options: CLIENT will have 7 records, AGENT will have 4 records CLIENT will have 6 records, AGENT will have 3 records CLIENT will have 7 records, AGENT will have 3 records CLIENT will have 6 records, AGENT will have 4 records

CLIENT will have 7 records, AGENT will have 4 records

Which of the following actions will result in a modification anomaly in the table ENROLLMENT? Question options: Changing the name of Joe to Joseph Changing the name of English1 to English101 Changing the name of History1 to History101 Changing the name of Bob to Robert

Changing the name of English1 to English101

Which of the following (regarding the relation ENROLLMENT) represents the modification anomaly? Question options: If a class name changes, it may have to be changed in more than one record If a student ID changes, we may have to change the class ID as well If a class ID changes, we may have to change the student ID as well If a student decides to use a longer version of his or her name, we cannot accommodate that modification

If a class name changes, it may have to be changed in more than one record

What will be the result of the following query on table STUDENT? SELECT COUNT(*) FROM student GROUP BY class HAVING COUNT(*) <3; Question options: Number 5 Number 2 Numbers 2 and 3 Numbers 1 and 1

Numbers 2 and 3

Which of the following queries on table STUDENT will return three values as a result? Question options: SELECT name FROM student WHERE class != 'Sophomore'; SELECT name FROM student; SELECT DISTINCT name FROM student SELECT class FROM student;

SELECT DISTINCT name FROM student

Which of the following functional dependencies does NOT exist in the table ENROLLMENT? Question options: StudentID --> StudentName StudentName --> StudentID StudentID --> ClassID ClassID --> ClassName

StudentName --> StudentID

If a 1:M relationship is mandatory on the M side and optional on the 1 side, and if the table resulting from the 1 side has 3 records, then the table resulting from the M side cannot have less than _____ records. Question options: 0 1 2 3

3

How many columns will a relation resulting from mapping the Entity X have? Question options: 2 3 4 5

4

How many records will the following query on table STUDENT return? SELECT * FROM student WHERE name LIKE '%or%'; Question options: 0 2 3 4

4

Which one of the following is true according to the ABC retailer ER diagram? Question options: A product can be associated with many promotions and it has to be associated with at least one promotion A product can be associated with many promotions but it does not have to be associated with any promotions Each product is associated with exactly one promotions A product is not associted with any promotions

A product can be associated with many promotions but it does not have to be associated with any promotions

OLAP/BI tools can be used for: Question options: Ad-hoc direct analysis only Creation of front-end (BI) applications only Ad-hoc direct analysis and creation of front-end (BI) applications Neither ad-hoc direct analysis nor creation of front-end (BI) applications

Ad-hoc direct analysis and creation of front-end (BI) applications

DBMS is software used for the following purpose ___________________. Question options: Creation of databases Insertion, storage, retrieval, update, and deletion of the data in the database Maintenance of databases All of the above

All of the above

If a DBMS enforces a DELETE CASCADE option on the referential integrity constraint between CLIENT and AGENT in the HAPPY INSURANCE database, what will be the outcome after a user tries to delete the last record (A4, James, 3, 90, 2010, A3) from AGENT? Question options: CLIENT will have 7 records, AGENT will have 4 records CLIENT will have 6 records, AGENT will have 3 records CLIENT will have 7 records, AGENT will have 3 records CLIENT will have 6 records, AGENT will have 4 records

CLIENT will have 6 records, AGENT will have 3 records

If a DBMS enforces a DELETE RESTRICT option on the referential integrity constraint between CLIENT and AGENT in the HAPPY INSURANCE database, what will be the outcome after a user tries to delete the last record (C777, Christina, A4, Mike) from CLIENT? Question options: CLIENT will have 7 records, AGENT will have 4 records CLIENT will have 6 records, AGENT will have 3 records CLIENT will have 7 records, AGENT will have 3 records CLIENT will have 6 records, AGENT will have 4 records

CLIENT will have 7 records, AGENT will have 3 records

Which of the following is a modify operation? Question 1 options: Entering new data in the relation Retrieving data from the relation Removing data from the relation Changing the existing data in the relation

Changing the existing data in the relation

Which of the following is a modify operation? Question options: Entering new data in the relation Retrieving data from the relation Removing data from the relation Changing the existing data in the relation

Changing the existing data in the relation

Which of the following is in danger of being affected by the deletion anomaly in the table ENROLLMENT? Question options: Student 111, Joe Student 222, Bob Class H1, History1 Class E1, English1

Class H1, History1

Which of the following is NOT true? Front-end (BI) applications are used to retrieve the data from: Question options: Dependent data marts ETL infrastructure Independent data marts Data warehouse

ETL infrastructure

Which of the following is true according to the ABC Retailer ER diagram? Question options: We cannot keep track of products that are not associated with multiple promotions We must keep track of products that are not associated with any promotions We can keep track of products that are not associated with any promotions We cannot keep track of products that are not associated with any promotions

We can keep track of products that are not associated with any promotion

Which of the following is true according to the ABC Retailer ER diagram? Question options: We cannot keep track of brands that do now have multiple products We must keep track of brands that do not have any products We can keep track of brands that do not have any products We cannot keep track of brands that do not have any products

We cannot keep track of brands that do not have any products

Which of the following is true according to the ABC Retailer ER diagram? Question options: We cannot keep track of products that belong to any brand We must keep track of products that do not belong to any brand We can keep track of products that do now belong to any brand We cannot keep track of products that do not belong to any brand

We cannot keep track of products that do not belong to any brand

Which of the following is true according to the ABC Retailer ER diagram? Question options: We cannot keep track of promotions that are not associated with multiple products We must keep track of promotions that are not associated with any products We can keep track of promotinos that are not associated with any products We cannot keep track of promotions that are not associated with any products

We cannot keep track of promotions that are not associated with any products

If A --> B and B --> A are existing functional dependencies, which of the following are NOT equivalent functional dependencies? Question 3 options: A --> B and B --> A A --> B, X and B --> A, X X --> A and A --> B X, A --> B, X and X, B --> A, X

X --> A and A --> B

Which of the following is an example of a daily operational procedure or task supported by an operational database? Question options: Deducting the correct amount of money from a customer's checking account upon an ATM withdrawal Issuing a correct bill to a customer who purchased an airline ticket Both A and B None of the above

Both A and B

________ contain analytically useful information. Question options: Fact tables Dimension tables Both dimension and fact tables None of the above

Both dimension and fact tables

Typically, in a star schema all ________ are given a simple, non-composite system-generated key, also called a surrogate key. Question options: Fact tables Dimension tables Time columns Transaction identifiers

Dimension tables

________ contain descriptions of some aspect of the business, organization, or enterprise useful to analysis. Question options: Fact tables Dimension tables Both dimension and fact tables None of the above

Dimension tables

Which of the following statements regarding the table below is true? Question options: Every value in Column A is unique Every value in Column B is unique Every combination of values in Column A and Column B within the same row is unique All of the above

Every combination of values in Column A and Column B within the same row is unique

Which of the following functional dependencies does not exist in the table FLIGHT? Question 5 options: FlightID --> NoOfPassengers FlightID --> Origin FlightID --> Destination FlightID, Date --> NoOfPassengers

FlightID --> NoOfPassengers

Which of the following queries on table STUDENT will return number 2 as a result? Question options: SELECT COUNT(*) FROM student; SELECT COUNT(name) FROM student; SELECT COUNT(class) FROM student; SELECT COUNT(DISTINCT class) FROM student

SELECT COUNT(DISTINCT class) FROM student

Which of the following describes the uniqueness data quality characteristic? Question options: The degree to which all the required data is present in the data collection The characteristic that requires each real-world instance to be represented only once in the data collection The extent to which data correctly reflects the real-world instances it is supposed to depict The extent to which the data properly conforms to and matches up with the other data

The characteristic that requires each real-world instance to be represented only once in the data collection

Which of the following describes the completeness data quality characteristic? Question options: The degree to which all the required data is present in the data collection The characteristic that requires each real-world instance to be represented only once in the data collection The extent to which the data properly conforms to and matches up with the other data The degree to which the data is aligned with the proper time window in its representation of the real world

The degree to which all the required data is present in the data collection

Which of the following describes the timeliness data quality characteristic? Question options: The degree to which all the required data is present in the data collection The characteristic that requires each real-world instance to be represented only once in the data collection The extent to which data correctly reflects the real world instances it is supposed to depict The degree to which the data is aligned with the proper time window in its representation of the real world

The degree to which the data is aligned with the proper time window in its representation of the real world

If a 1:M relationship is optional on both sides, which of the following is true? Question options: The resulting foreign key can have null (empty) values The resulting foreign key cannot have null (empty) values The resulting foreign key must have null (empty) values No foreign key is created as a result of mapping such a relationship

The resulting foreign key can have null (empty) values

What is the result of the following SQL statement? UPDATE employee SET salary = 50000 WHERE salary < 50000; Question options: The statement sets the salary for each employee to below 50,000 The statement sets the salary of each employee to 50,000 and ensures that the salary cannot drop below 50,000 The statement increases salary to 50,000 for each employee whose salary is below 50,000 The statement sets the salary of each employee to 50,000

The statement increases salary to 50,000 for each employee whose salary is below 50,000

Which of the following is NOT true? In a relational database: Question options: Two tables can be a part of the same database Two tables can have the same number of columns Two tables can have the same names Two tables can have the same number of rows

Two tables can have the same names

The ________ slowly changing dimension approach creates a new additional dimension record using a new value for the surrogate key every time a value in a dimension record changes. Question options: Type 0 Type 1 Type 2 Type 3

Type 2

A regular entity must have at least one of the following attributes: Question options: Unique attribute Composite attribute Multivalued attribute Derived attribute

Unique attribute

Which of the following describes the accuracy data quality characteristic? Question options: The degree to which all the required data is present in the data collection The extent to which the data conforms to its specified format The extent to which data correctly reflects the real-world instances it is supposed to depict The extent to which the data properly conforms to and matches up with the other data

The extent to which data correctly reflects the real-world instances it is supposed to depict

What is the result of the following SQL query? SELECT ename FROM employee WHERE esalary = (SELECT MAX(salary) FROM employee); Question options: The query displays the number that represents how many employees share the highest salary The query displays the salary of the employee (or employees) with the highest salary The query displays the name and the salary of the employee (or employees) with the highest salary The query displays the name of the employee (or employees) with the highest salary

The query displays the name of the employee (or employees) with the highest salary

What is the result of the following SQL query? SELECT ename, esalary * 1.1 FROM employee; Question options: For each employee, the query will display the name, the salary, and all other columns from the table employee For each employee, the query will display the name, the salary, all other columns from the table employee, and the number 1.1 For each employee, the query will display the name and the salary increased by 10% For each employee, the query will display the name and 10% of the salary

For each employee, the query will display the name and the salary increased by 10%

Which of the following actions in the table ENROLLMENT will be prevented by an insertion anomaly? Question options: Entering a new student 444, Sue Entering a new student 555, Lisa Entering a new record 666, Bob, E1, English1 Entering a new class M1, Math1

Entering a new class M1, Math1

Every relation contains: Question options: Full key functional dependency Partial functional dependency Transitive functional dependency All of the above

Full key functional dependency

Which of the following (regarding the relation FLIGHT) represents the insertion anomaly? Question 4 options: Inability to insert a flight without its origin Inability to insert an origin without a destination Inability to insert a destination without an origin Inability to insert a number of passengers without a flight ID and date

Inability to insert a number of passengers without a flight ID and date

Updating, querying, and presenting data from databases for operational purposes is known as: Question options: ETL OLAP OLTP Micro batches

OLTP

Which of the following describes the conformity data quality characteristic? Question options: The degree to which all the required data is present in the data collection The extent to which the data properly conforms to and matches up with the other data The extent to which data correctly reflects the real world instances it is supposed to depict The extent to which the data conforms to its specified format

The extent to which the data conforms to its specified format

Which of the following describes the consistency data quality characteristic? Question options: The degree to which all the required data is present in the data collection The extent to which the data properly conforms to and matches up with the other data The extent to which data correctly reflects the real-world instances it is supposed to depict The extent to which the data conforms to its specified format

The extent to which the data properly conforms to and matches up with the other data