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
