C192 Pre-Assessment WGU

Question 1 of 59

1

Which factor must be considered during physical database design?

Select one of the following:

Determination of the specific DBMS vendor product to use
Specification of table definitions
Specification of entities and relationships between entities
Determination of the type of DBMS to be used

Explanation

Question 2 of 59

1

Which technique is used to test the correctness of a logical database design model?

Select one of the following:

Prototyping
Normalization
Logical Grouping
Extensibility

Explanation

Question 3 of 59

1

Which database design phase uses normalization techniques to eliminate anomalies in the data model?

Select one of the following:

XML Database Design
Physical Database Design
Logical Database Design
Conceptual Database Design

Explanation

Question 4 of 59

1

Which step would be seen in the conceptual phase of data model design?

Select one of the following:

Specification of entities and relationships
Specification of trigger definitions
Specification of storage size
Specification of programming language to use

Explanation

Question 5 of 59

1

What describes a specialized grouping of entity type occurrences that must be represented in a data model?

Select one of the following:

Superclass
Baseclass
Relationship
Subclass

Explanation

Question 6 of 59

1

Which concept does the SQL statement "SELECT * FROM Contacts, Employer" demonstrate?

Select one of the following:

Difference
Cartesian product
Selection
Intersection

Explanation

Question 7 of 59

1

What is the difference between a conceptual model and a logical model?

Select one of the following:

A conceptual model is refined based on the network data model, whereas the logical model is based on a multidimensional model.
A conceptual model assumes details of the underlying DBMS, whereas the logical model assumes details based on an abstraction.
A conceptual model is independent of all implementation details, whereas the logical model is based on a specific database model.
A conceptual model is dependent upon the programming language used to manage the data, whereas the logical model is dependent on implementation details.

Explanation

Question 8 of 59

1

Which data type is considered a non-interpreted byte stream for a database management system (DBMS)?

Select one of the following:

Float
Character
Bit
BLOB
XML

Explanation

Question 9 of 59

1

Which database management systems (DBMS) architecture emerged in the third generation of DBMSs?

Choose 2 answers

Select one or more of the following:

Object structured
Object oriented
Object stacking
Object relational

Explanation

Question 10 of 59

1

What are strengths of database management systems (DBMS)?

Choose 2 answers

Select one or more of the following:

Size of software
Cost of DBMS
Control of data redundancy
Increased performance
Improved data integrity

Explanation

Question 11 of 59

1

Which DDL statement should be used to enforce referential integrity?

Select one of the following:

CREATE TRIGGER AFTER GRANT
CREATE TRIGGER BEFORE UPDATE
CREATE TRIGGER AFTER SELECT
CREATE TRIGGER BEFORE DROP

Explanation

Question 12 of 59

1

What describes a package in PL/SQL?

Select one of the following:

Organized SQL statements, DMBS encryption algorithms, and keys
The culmination of database schema, design, and log files
A collection of procedures, functions, variables, and SQL statements
A grouping of indexes, truples, and attributes

Explanation

Question 13 of 59

1

Which statement produces a PL/SQL stored procedure?

Select one of the following:

ADD OR UPDATE PROCEDURE dataAbstraction AS BEGIN SELECT * FROM employees END
CREATE OR REPLACE PROCEDURE dataAbstraction AS BEGIN SELECT * FROM employees END
INSERT OR UPDATE PROCEDURE dataAbstraction AS BEGIN SELECT * FROM employees END
INSERT OR SET PROCEDURE dataAbstraction AS BEGIN SELECT * FROM employees END

Explanation

Question 14 of 59

1

Which SQL statement will select all the data from the branch table, whether or not there are entries in the employee table?

Select one of the following:

SELECT * FROM branch LEFT JOIN employee ON branch.location = employee.location
SELECT * FROM employee LEFT JOIN branch ON employee.location = branch.location
SELECT * FROM branch INNER JOIN employee ON branch.location = employee.location
SELECT * FROM employee INNER JOIN branch ON employee.location = branch.location

Explanation

Question 15 of 59

1

Use the given code to answer the following question:

SELECT branchNo, COUNT(staffNo) AS myCount, SUM(salary) AS mySum
FROM staff
GROUP BY branchNo
ORDER BY branchNo;

What does this code do?

Select one of the following:

Finds the total number of branches
Orders the staff table by the ascending order of branchNo
Finds the number of staff and the sum of their salaries in each branch
Orders the staff by ascending order of salary

Explanation

Question 16 of 59

1

Which aggregate functions can be applied to both numeric and non-numeric fields?

Choose 2 answers

Select one or more of the following:

AVG
MAX
SUM
FLOOR
COUNT

Explanation

Question 17 of 59

1

Use the given query results from the following table to answer the question below:

staffNo fName lName salary

SL21 John White 30000.00
SG5 Susan Brand 24000.00
SG14 David Ford 18000.00
SG37 Ann Beech 12000.00
SA9 Mary Howe 9000.00
SL41 Julie Lee 9000.00

Which query should be executed to produce the results as displayed in this table?

Select one of the following:

SELECT staffNo, fName, lName, salary FROM Staff ORDER BY 4 ASC
SELECT staffNo, fName, lName, salary FROM Staff ORDER BY fName DESC
SELECT staffNo, fName, lName, salary FROM Staff ORDER BY salary DESC
SELECT staffNo, fName, lName, salary FROM Staff ORDER BY salary ASC

Explanation

Question 18 of 59

1

Use the given SQL to answer the following question:

SELECT staff_id, first_name, last_name, position, salary
FROM Staff
WHERE salary > 40000
AND position = 'Supervisor'

What are the search conditions used by this statement?

Choose 2 answers

Select one or more of the following:

Range
Comparison
Pattern match
Set membership

Explanation

Question 19 of 59

1

Which statement will delete the staff member who is a manager with the staff ID "SG15"?

Select one of the following:

DELETE FROM staff WHERE position='Manager', staffNo='SG15'
DELETE WHERE position=Manager AND staffNo=SG15
DELETE FROM staff WHERE position='Manager' AND staffNo='SG15'
DELETE WHERE position='Manager' AND staffNo='SG15'

Explanation

Question 20 of 59

1

Which statement must be used to increment staff salary by 4%?

Select one of the following:

UPDATE staff SET salary = salary * 1.04
UPDATE staff SET salary = salary * 4%
UPDATE staff SET salary = salary * 0.04
UPDATE staff SET salary = salary * 104

Explanation

Question 21 of 59

1

Use the given statement to answer the following question:

INSERT INTO Production
VALUES ('Item123', 'Round');

Which function is performed in this statement?

Select one of the following:

Inserting a table into the database
Inserting a single row of data
Inserting data into random columns
Inserting multiple rows of data

Explanation

Question 22 of 59

1

Use the given DDL statement to answer the following question:

CREATE ASSERTION StaffNotHandlingTooMuch
CHECK(NOT EXISTS (SELECT staffNo
FROM PropertyForRent
GROUP BY staffNo
HAVING COUNT(*) > 100))

Which action is this statement performing?

Select one of the following:

Creating a trigger
Creating a stored procedure
Creating a constraint
Creating an index

Explanation

Question 23 of 59

1

Which DDL statement creates an index that could be used as a candidate primary key?

Select one of the following:

CREATE VIEW INDEX
CREATE UNIQUE INDEX
CREATE PRIMARY INDEX
CREATE DROP INDEX

Explanation

Question 24 of 59

1

Use the given DDL to answer the following question:

CREATE VIEW Manager3Staff
AS SELECT *
FROM Staff
WHERE branch_number = 3
WITH CHECK OPTION;

What does this code do?

Select one of the following:

It provides access to the entire Staff table.
It restricts the visible columns in the Staff table.
It creates a view that prohibits migrating rows.
It prevents insertions into Staff with branch_number = 3.

Explanation

Question 25 of 59

1

What is an example of the correct syntax for creating a new column in an existing table?

Select one of the following:

ALTER TABLE person ADD email VARCHAR(200) NULL
MODIFY TABLE person CREATE COLUMN email VARCHAR(200) NULL
MODIFY TABLE person ADD email VARCHAR(200) NULL
EDIT TABLE person ADD COLUMN email VARCHAR(200) NULL

Explanation

Question 26 of 59

1

What is a benefit of using SQL?

Select one of the following:

SQL is a standard used to define user interfaces.
SQL is a standard used for data interchange between enterprise systems.
SQL is recognized by all operating systems.
SQL is the strategic choice of many large and influential organizations.

Explanation

Question 27 of 59

1

A DBA needs to implement a data warehouse where avoiding redundant data is preferred over query performance.

What is the appropriate multidimensional data model that should be chosen for the database design?

Select one of the following:

Starflake schema
Network schema
Star schema
Snowflake schema

Explanation

Question 28 of 59

1

Which DM schema implements the given data model?

Select one of the following:

Network schema
Star schema
Relational schema
Constellation schema

Explanation

Question 29 of 59

1

A database architect is tasked with designing and implementing a data warehouse using a star schema.

Which design approach should the architect follow?

Select one of the following:

One fact table surrounded by normalized dimension tables
One dimension table surrounded by normalized fact tables
One dimension table surrounded by normalized and denormalized fact tables
One fact table surrounded by denormalized dimension tables

Explanation

Question 30 of 59

1

How does dimensional modeling differ from entity-relationship modeling?

Select one of the following:

Dimensional modeling is used to identify relationships among entities
Dimensional modeling's major goal is to remove redundancy in data
Dimensional modeling cannot efficiently and easily support ad-hoc queries
Dimensional modeling can model common business situations

Explanation

Question 31 of 59

1

Which data representation concept is associated with online analytical processing (OLAP)?

Select one of the following:

The concept of "slice and dice"
The concept of "associate arrays"
The concept of "hash tables"
The concept of "facts"

Explanation

Question 32 of 59

1

Which online analytical processing (OLAP) operations can be used for driving business decisions on data cubes?

Choose 2 answers

Select one or more of the following:

Slicing
Equijoining
Pivoting
Cascading
Validating

Explanation

Question 33 of 59

1

What is a benefit of distributed database management systems (DDBMS)?

Select one of the following:

It reduces the skills required of the database administrator.
It removes the requirement for global applications.
It stops fragments from replicating.
It helps resolve the islands of information problem.

Explanation

Question 34 of 59

1

What is a reason for creating a data mart versus a data warehouse?

Select one of the following:

A data mart provides subject-oriented and time-variant data.
A data mart improves end-user response time.
A data mart stores infrequently analyzed data.
A data mart provides a superset of data.

Explanation

Question 35 of 59

1

What is the process of loading data extracted from different OLTP data sources into an enterprise data warehouse?

Select one of the following:

EER (Enhanced Entity-Relationship)
Fragmentation
Replication
ETL (Extract/Transform/Load)

Explanation

Question 36 of 59

1

Which type of data will be found by utilizing data mining?

Select one of the following:

Customer sales reports
Customer refund reports
Customer address lists
Customer loyalty identification

Explanation

Question 37 of 59

1

What is an advantage of using XML when presenting database management system (DBMS) data?

Select one of the following:

It provides data redundancy.
It implements intrinsic data types.
It supports data integration.
It combines content and presentation.

Explanation

Question 38 of 59

1

Which MIME type is used to transfer a regular ASCII file hosted in a database management system (DBMS) to a web client?

Select one of the following:

text/plain
application/pdf
text/html
image/gif

Explanation

Question 39 of 59

1

What is a common component of encoding data using asymmetric encryption?

Select one of the following:

Same encryption and decryption keys
Uni-directional encryption
Different encryption and decryption keys
Identical encryption and decryption algorithms

Explanation

Question 40 of 59

1

Use the given statement to answer the following question:

CREATE VIEW CarSales(dealerNo, salesNo, sales)
AS SELECT s.dealerID, s.salesID, COUNT(*)
FROM salesman s, monthlySales m
WHERE s.salesID = m.salesID
GROUP BY s.dealerID, s.salesID

Which queries will properly run?

Choose 2 answers

Select one or more of the following:

SELECT * FROM CarSales WHERE dealerNo > 10
SELECT * FROM CarSales GROUP BY sales
SELECT * FROM CarSales WHERE COUNT(sales) > 5
SELECT * FROM CarSales WHERE salesNo > 3
SELECT COUNT(sales), dealerNo FROM CarSales

Explanation

Question 41 of 59

1

Which DCL command gives Director the privilege to SELECT on column Salary of the table Staff?

Select one of the following:

ALLOW Director SELECT Salary FROM Staff;
GRANT SELECT(Salary) ON Staff TO Director;
GRANT SELECT(Staff) ON Salary TO Director;
PERMIT Director SELECT ON Staff(Salary);

Explanation

Question 42 of 59

1

Which security strategy should be used for creating roles in a database?

Select one of the following:

Creating groupings of users with different data access privileges
Creating groupings of privileges for a specific user in the database
Creating groupings of users with the same data access privileges
Creating groupings of privileges by database transactions

Explanation

Question 43 of 59

1

Which phase is part of optimistic concurrency control protocol for read-only transactions?

Select one of the following:

Shrinking
Write
Growing
Validation

Explanation

Question 44 of 59

1

Which statement describes timestamping?

Select one of the following:

Transactions with newer dates get priority over older dates.
Locking is used to achieve serialization and to prevent conflicts.
Timeouts used on lock requests wait as a method of deadlock prevention.
Transactions involved in a conflict can be rolled back and restarted.

Explanation

Question 45 of 59

1

Which algorithms proposed by Rosenkrantz et al. are used for deadlock prevention?

Choose 2 answers

Select one or more of the following:

Wound-wait
Wait-die
Conservative 2PL
Indefinite wait
Wait-for graph

Explanation

Question 46 of 59

1

What is the difference between a transaction with a shared lock and a transaction with an exclusive lock?

Select one of the following:

A shared lock cannot access the item.
A shared lock can read the item.
A shared lock can delete the item.
A shared lock can update the item.

Explanation

Question 47 of 59

1

What is the term used to describe operations in transactions performed in order, without interleaved operations from other transactions?

Select one of the following:

Non-serial schedule
Exclusive schedule
Serial schedule
Sanitized schedule

Explanation

Question 48 of 59

1

What is the concurrency control technique of locking?

Select one of the following:

Validation
Pessimistic
Optimistic
Reading

Explanation

Question 49 of 59

1

A transaction must transform the database from one consistent state to another consistent state.

Select one of the following:

Isolation
Atomicity
Consistency
Durability

Explanation

Question 50 of 59

1

Transactions execute independently of one another.

Select one of the following:

Isolation
Atomicity
Consistency
Durability

Explanation

Question 51 of 59

1

A transaction is an indivisible unit that is either performed in its entirety or is not performed at all.

Select one of the following:

Isolation
Atomicity
Consistency
Durability

Explanation

Question 52 of 59

1

The effects of a successfully completed transaction are permanently recorded in the database.

Select one of the following:

Isolation
Atomicity
Consistency
Durability

Explanation

Question 53 of 59

1

Which characteristic does static optimization have when compared with dynamic optimization?

Select one of the following:

Static optimization is done at run time, whereas dynamic optimization is done prior to run time.
Static optimization requires more runtime overhead, whereas dynamic optimization has less overhead.
Static optimization is optimized prior to execution, whereas dynamic optimization is optimized during run time.
Static optimization is continually up to date, whereas dynamic optimization is updated once.

Explanation

Question 54 of 59

1

What are the methods for replicating data to mobile environments?

Choose 2 answers

Select one or more of the following:

DBMS installed in the mobile device
Store a subset of data in the mobile device
Store logs to dynamically generate data on a mobile device
Mobile device using a wired connection
Mobile device acting as a master replica

Explanation

Question 55 of 59

1

A transaction is committed before a failure occurs that prevents buffers from writing to secondary storage.

Which action should be taken to ensure durability of this transaction?

Select one of the following:

Roll forward
Roll back
Partial undo
Flush

Explanation

Question 56 of 59

1

Which factors should be considered in designing a backup plan?

Choose 2 answers

Select one or more of the following:

How often to test by restoring backup data
How to process application logic
Where to store backup data
Where to run query optimization
How to combine relations during backup to save space

Explanation

Question 57 of 59

1

When this SQL code runs, all associated objectives will also be removed.
DROP SCHEMA testingHouse CASCADE

What happens if any of these sub drops fail?

Select one of the following:

The DROP SCHEMA fails.
The DROP SCHEMA reruns failed drops.
The DROP SCHEMA skips failed drops.
The DROP SCHEMA deadlocks.

Explanation

Question 58 of 59

1

	Created by Craig Willey over 4 years ago

College C192 Databases Quiz on C192 Pre-Assessment WGU, created by Craig Willey on 23/03/2020.

C192 Pre-Assessment WGU

Question 1 of 59

Which factor must be considered during physical database design?

Select one of the following:

Explanation

Question 2 of 59

Which technique is used to test the correctness of a logical database design model?

Select one of the following:

Explanation

Question 3 of 59

Which database design phase uses normalization techniques to eliminate anomalies in the data model?

Select one of the following:

Explanation

Question 4 of 59

Which step would be seen in the conceptual phase of data model design?

Select one of the following:

Explanation

Question 5 of 59

What describes a specialized grouping of entity type occurrences that must be represented in a data model?

Select one of the following:

Explanation

Question 6 of 59

Which concept does the SQL statement "SELECT * FROM Contacts, Employer" demonstrate?

Select one of the following:

Explanation

Question 7 of 59

What is the difference between a conceptual model and a logical model?

Select one of the following:

Explanation

Question 8 of 59

Which data type is considered a non-interpreted byte stream for a database management system (DBMS)?

Select one of the following:

Explanation

Question 9 of 59

Which database management systems (DBMS) architecture emerged in the third generation of DBMSs? Choose 2 answers

Select one or more of the following:

Explanation

Question 10 of 59

What are strengths of database management systems (DBMS)? Choose 2 answers

Select one or more of the following:

Explanation

Question 11 of 59

Which DDL statement should be used to enforce referential integrity?

Select one of the following:

Explanation

Question 12 of 59

What describes a package in PL/SQL?

Select one of the following:

Explanation

Question 13 of 59

Which statement produces a PL/SQL stored procedure?

Select one of the following:

Explanation

Question 14 of 59

Which SQL statement will select all the data from the branch table, whether or not there are entries in the employee table?

Select one of the following:

Explanation

Question 15 of 59

Use the given code to answer the following question: SELECT branchNo, COUNT(staffNo) AS myCount, SUM(salary) AS mySum FROM staff GROUP BY branchNo ORDER BY branchNo; What does this code do?

Select one of the following:

Explanation

Question 16 of 59

Which aggregate functions can be applied to both numeric and non-numeric fields? Choose 2 answers

Select one or more of the following:

Explanation

Question 17 of 59

Select one of the following:

Explanation

Question 18 of 59

Use the given SQL to answer the following question: SELECT staff_id, first_name, last_name, position, salary FROM Staff WHERE salary > 40000 AND position = 'Supervisor' What are the search conditions used by this statement? Choose 2 answers

Select one or more of the following:

Explanation

Question 19 of 59

Which statement will delete the staff member who is a manager with the staff ID "SG15"?

Select one of the following:

Explanation

Question 20 of 59

Which statement must be used to increment staff salary by 4%?

Select one of the following:

Which database management systems (DBMS) architecture emerged in the third generation of DBMSs?

Choose 2 answers

What are strengths of database management systems (DBMS)?

Choose 2 answers

Use the given code to answer the following question:

SELECT branchNo, COUNT(staffNo) AS myCount, SUM(salary) AS mySum
FROM staff
GROUP BY branchNo
ORDER BY branchNo;

What does this code do?

Which aggregate functions can be applied to both numeric and non-numeric fields?

Choose 2 answers

Use the given SQL to answer the following question:

SELECT staff_id, first_name, last_name, position, salary
FROM Staff
WHERE salary > 40000
AND position = 'Supervisor'

What are the search conditions used by this statement?

Choose 2 answers

Use the given statement to answer the following question:

INSERT INTO Production
VALUES ('Item123', 'Round');

Which function is performed in this statement?

Use the given DDL statement to answer the following question:

CREATE ASSERTION StaffNotHandlingTooMuch
CHECK(NOT EXISTS (SELECT staffNo
FROM PropertyForRent
GROUP BY staffNo
HAVING COUNT(*) > 100))

Which action is this statement performing?

Use the given DDL to answer the following question:

CREATE VIEW Manager3Staff
AS SELECT *
FROM Staff
WHERE branch_number = 3
WITH CHECK OPTION;

What does this code do?

A DBA needs to implement a data warehouse where avoiding redundant data is preferred over query performance.

What is the appropriate multidimensional data model that should be chosen for the database design?

A database architect is tasked with designing and implementing a data warehouse using a star schema.

Which design approach should the architect follow?

Which online analytical processing (OLAP) operations can be used for driving business decisions on data cubes?

Choose 2 answers