C192 Pre-Assessment WGU

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

Prototyping

Normalization

Logical Grouping

Extensibility

XML Database Design

Physical Database Design

Logical Database Design

Conceptual Database Design

Specification of entities and relationships

Specification of trigger definitions

Specification of storage size

Specification of programming language to use

Superclass

Baseclass

Relationship

Subclass

Difference

Cartesian product

Selection

Intersection

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.

Float

Character

Bit

BLOB

XML

CREATE TRIGGER AFTER GRANT

CREATE TRIGGER BEFORE UPDATE

CREATE TRIGGER AFTER SELECT

CREATE TRIGGER BEFORE DROP

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

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

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

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

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

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'

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

Inserting a table into the database

Inserting a single row of data

Inserting data into random columns

Inserting multiple rows of data

Creating a trigger

Creating a stored procedure

Creating a constraint

Creating an index

CREATE VIEW INDEX

CREATE UNIQUE INDEX

CREATE PRIMARY INDEX

CREATE DROP INDEX

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.

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

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.

Starflake schema

Network schema

Star schema

Snowflake schema

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

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

The concept of "slice and dice"

The concept of "associate arrays"

The concept of "hash tables"

The concept of "facts"

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.

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.

EER (Enhanced Entity-Relationship)

Fragmentation

Replication

ETL (Extract/Transform/Load)

Customer sales reports

Customer refund reports

Customer address lists

Customer loyalty identification

It provides data redundancy.

It implements intrinsic data types.

It supports data integration.

It combines content and presentation.

text/plain

application/pdf

text/html

image/gif

Same encryption and decryption keys

Uni-directional encryption

Different encryption and decryption keys

Identical encryption and decryption algorithms

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);

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

Shrinking

Write

Growing

Validation

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.

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.

Non-serial schedule

Exclusive schedule

Serial schedule

Sanitized schedule

Validation

Pessimistic

Optimistic

Reading

Isolation

Atomicity

Consistency

Durability

Isolation

Atomicity

Consistency

Durability

Isolation

Atomicity

Consistency

Durability

Isolation

Atomicity

Consistency

Durability

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.

Roll forward

Roll back

Partial undo

Flush

The DROP SCHEMA fails.

The DROP SCHEMA reruns failed drops.

The DROP SCHEMA skips failed drops.

The DROP SCHEMA deadlocks.

Administration

Robustness

Redundancy

Concurrency

A starflake schema has multiple fact tables.

A starflake schema must use denormalized dimension tables and natural keys.

A starflake schema uses a network of hierarchical fact tables.

A starflake schema uses both normalized and denormalized dimension tables.