DATABASE C192 OA Study Guide 1_2020 – Western Governors University | DATABASE C 192 OA Study Guide 1_2020

DATABASE C192 OA Study Guide 1_2020 – Western Governors University Answers labeled with @ Which functions are supported within a database management system (DBMS)? Choose 2 answers Requirements gathering Contr ...

DATABASE C192 OA Study Guide 1_2020 – Western Governors University Answers labeled with @ Which functions are supported within a database management system (DBMS)? Choose 2 answers Requirements gathering Controlled access to the database Data manipulation Logical data correctness verification Data business rules generation Match each model with its corresponding product Answer options may be used more than once or not at all Hierarchical IMS Network IDMS Relational oracle Which binary data type is used within a database management system (DBMS)? BLOB NVARCHAR2 ROWID FLOAT Which term can be described as the immunity of the external schemas to changes in the conceptual schema? Logical data independence Database management system (DMBS) independence Object independence Physical data independence Consider the input relation staff with attributes staff_number, first_name, last_name, and salary Which operations need to be used to produce a new relation containing those staff tuples with a salary greater than $8,000 and attributes first_name, last_name, and salary? Choose 2 answers Projection Selection Cartesian product Difference Join Union Which type of relationship is used to represent how data will be stored in an enhanced entity-relationship (EER) model? Aggregation Anomaly Security Consistency What is the first phase of database design called? Object-oriented database design Physical database design Logical database design Conceptual database design Which technique is used to test the correctness of the logical data model during the design phase? Composition Indexing Aggregation Normalization What is a characteristic of a logical database design? Logical database design specifies system triggers Logical database design is based upon the relational model Logical database design occurs before conceptual database design Logical database design is independent of a system’s physical requirements What is physical database design tailored to? The number of tables The hard disk manufacturer The query language The specific DBMS What is a valid data definition language (DDL) command? Alter Execute Update Grant CREATE TABLE Students( studentNo StudentNumber NOT NULL, firstName FirstName NOT NULL, lastName LastName NOT NULL, collegiateLevel CollegiateLevel NOT NULL DEFAULT ‘undergraduate’, enrollment Enrollment NOT NULL DEFAULT ‘fulltime’, tuitionRate NOT NULL DEFAULT 500, PRIMARY KEY (studentNo) ); Which value would be saved for a student if that student was inserted without a specified tuitionRate , based on this CREATE TABLE ? NULL ‘undergraduate’ ‘fulltime’ 500 What is an example of the correct syntax for creating a view? CREATE VIEW StateCity INTO (State, City) SELECT DISTINCT State, City FROM StateCityZip ADD VIEW StateCity (SELECT DISTINCT State, City FROM StateCityZip) ADD VIEW StateCity AS (SELECT DISTINCT State, City FROM StateCityZip) CREATE VIEW StateCity AS SELECT DISTINCT State, City FROM StateCityZip What is an example of the correct syntax for creating an index? ADD INDEX xStateCity ON TABLE StateCity (State, City) CREATE INDEX xStateCity ON TABLE StateCity (State Ascending, City) CREATE INDEX xStateCity ON StateCity (State, City) ADD INDEX xStateCity ON StateCity (State, City) Use the given DDL for a child table to answer the question below: CREATE TABLE invoice ( invoiceNumber INT NOT NULL, clientID INT NOT NULL DEFAULT 1000, PRIMARY KEY (invoiceNumber), FOREIGN KEY (clientID) REFERENCES clients (clientID) ON DELETE ________________ ) Which code snippet will complete the child table and allow the DDL to reject the delete operation from the parent table? SET DEFAULT CASCADE NO ACTION SET NULL Which qualifier on a column allows for insertions to be made without specifying a value? DEFAULT NOT NULL UNIQUE PRIMARY Use the given statement to answer the following question: UPDATE Employees SET pay = pay*105; What will this statement update? The last row All rows The first row No rows Which command will remove all rows from a plants table? DELETE FROM plants UPDATE plants SET COLUMNS = NULL SET plants = NULL REMOVE * FROM plants What is an example of the correct syntax for querying data from a table? SELECT city FROM StateCity WHERE state=‘Wisconsin’ QUERY TABLE StateCity WHERE state=‘Wisconsin’ QUERY StateCity (City) VALUES state=‘Wisconsin’ SELECT StateCity (City) WHERE state=‘Wisconsin’ Which SQL keyword will eliminate duplicates from the results set? DISTINCT ORDER BY RESTRICT CHECK UPDATE Which SQL statement is used to aggregate the average salary of employees by department? SELECT salary FROM employees GROUP BY department_name; SELECT AVG(salary) FROM employees GROUP BY department_name; SELECT department_name, AVG(salary) FROM employees GROUP BY department_name; SELECT salary FROM employees GROUP BY department_name, AVG(salary); Use the given query to answer the following question: SELECT branchNo, COUNT(staffNo) AS myCount, SUM(salary) AS mySum FROM Staff GROUP BY branchNo __________ COUNT(staffNo) > 1 ORDER BY branchNo Which code snippet will complete the above query? CONSTRAINT HAVING WHERE RESTRICT Use the given code to answer the following question: SELECT c*, v* FROM Client c, Viewing v WHERE cclient_number = vclient_number; What does this code do? It creates a left outer join of the client and viewing tables It creates a right outer join of the client and viewing tables It joins the client and viewing tables on the client_number field It updates records in the viewing table based on the client_number in the client table What is true about a stored procedure? The input and output parameters can be defined It contains a PL/SQL package The de facto language is XML The number of lines of code needs to be less than 100 Which description of a package is correct? A package defines input and output parameters for simple stored procedures A package specification is stored as a flat file outside the database A package is compiled and the compiled code is stored on a disk A package is a collection of procedures, functions, and variables grouped together What is an example of the correct syntax for creating a trigger? CREATE TRIGGER t1 AFTER INSERT ON Person REFERENCING NEW AS new FOR EACH ROW BEGIN INSERT INTO FriendsTable (name) VALUES (:newName) END ADD TRIGGER t1 AFTER INSERT ON TABLE Person REFERENCING NEW AS new FOR EACH ROW INSERT INTO FriendsTable (name) VALUES (:newName) CREATE TRIGGER t1 ON INSERT OF Person FOR EACH ROW BEGIN INSERT INTO FriendsTable (name) VALUES (:newName) END CREATE TRIGGER t1 BEFORE INSERT WITH Person FOR EACH ROW BEGIN INSERT INTO FriendsTable (name) VALUES (:newName) END What is a challenge associated with a data warehouse? Transaction processing within the data warehouse Creating a data model with more than two dimensions Limited performance with complex analytics within the data warehouse Underestimation of resources needed for data ETL What are reasons for creating a data mart instead of a data warehouse? Choose 2 answers A data mart allows access to enterprise-wide data to an entire organization A data mart allows access to real time data A data mart allows access to the data that is analyzed most often A data mart allows for the reduction of data volume Which types of transparencies are provided in a distributed database management system (DBMS)? Choose 2 answers Transaction Relational Manageability Hardware Performance What is a key component of business intelligence (BI) technologies associated with online analytical processing (OLAP)? Online transaction process Data warehousing Data normalization Transaction processing Which format is used to represent property sales revenue data of a real estate company with the dimensions time and location? A two-dimensional matrix A three-dimensional cube A two-field relational table A series of cubes A series of tables Which situation is appropriate for designing data storage using dimensionality modeling? For transaction processing To allow high-performance access To avoid redundancy For daily operations A denormalized dimension product, a denormalized customer table, and an order fact table are components of a schema Which schema should be used to represent this? Snowflake Fact constellation Hierarchical Star Use the given attached figure to answer the following question: Image Which dimensional model is being depicted? Snowflake schema Constellation schema Starflake schema Star schema A schema has a fact table with a multitude of normalized dimension tables Which design approach should be used in defining multi-dimensional data storage? Starflake schema Star schema Snowflake schema Relational schema Answer the following question by using the multidimensional model description below: Fact table in the center surrounded by normalized and denormalized dimension tables Which schema does this represent? Starflake Fact constellation Relational Snowflake Which technologies can be used to extract and present data from a database management system (DBMS) to a web interface? Choose 2 answers Scripting languages Data control languages Second-generation programming languages Electronic data interchange Common gateway interfaces What is an advantage of using an XML document when presenting data? It provides precise and specific data type specifications It provides low storage and transmission costs It allows web pages to display quicker without parsing It allows for the separation of content and presentation When should data mining be applied in an enterprise environment? In a data migration process To identify patterns In an ETL process To track user authentications Which strategy should be used to limit the data access when the specified users have the same privileges? Change the database authentication to LDAP authentication Give access to every user individually Create roles to control the users in a single group of access Create a script with all the GRANT ACCESS commands Use the given DCL statement to answer the following question: GRANT SELECT, UPDATE (salary) ON Staff TO Manager; Which privileges are granted when executing this statement? Grant user Manager, the privileges SELECT all columns and UPDATE on column salary of the Staff table Grant user Manager, the privileges SELECT only on column salary and UPDATE on column salary of the Staff table____________ Grant user Staff, the privileges SELECT all columns and UPDATE on column salary of the Manager table Grant user Staff, the privileges SELECT only on column salary and UPDATE on column salary of the Manager table A database staff schema consists of staff_number, first_name, last_name, position, salary, and branch_number Which DDL statement should be used to create a view to exclude data visibility to the salary and branch_number fields of the staff table for branch ‘007’? CREATE VIEW Staff3 AS SELECT staff_number, first_name, last_name, position, NVL(salary,’’) FROM Staff WHERE branch_number =‘007’; CREATE VIEW Staff3 AS SELECT * FROM staff WHERE branch_number =‘007’ AND salary IS NULL WITH CHECK OPTION; CREATE VIEW Staff3 AS SELECT staff_number, first_name, last_name, position FROM staff WHERE branch_number =‘007’ CREATE VIEW Staff3 AS SELECT staff_number, first_name, last_name, position, salary FROM Staff WHERE branch_number =‘007’ AND salary IS NULL; What are two components of encryption? Choose 2 answers An algorithm A view An index A key A trigger Which property should all transactions possess? Concurrency Visibility Latency Atomicity What describes the concept of serializability? To serially execute read operations of each transaction before write operations To prevent parallel execution of transactions within a schedule To have nonserial schedules execute concurrently without interfering with one another To serially execute non-constrained write operations of each transaction first When is an exclusive lock on a data item used? When writing to a data item that is locked by another operation When multiple transactions read a data item When a security measure is needed to block other transactions from accessing the data item When a write to the data item has to be performed What causes a deadlock to occur? Transactions ordered with timestamps Transactions timing out Transactions blocking each other Transactions executing one after another What would timestamping give priority to if a conflict occurred between transactions? The transaction that takes the longest time to run The transaction with the largest timestamp The transaction with the smaller timestamp The transaction that takes the shortest time to run Which phases are a part of the optimistic concurrency protocol? Choose 2 answers Shrinking phase Probing phase Read phase Growing phase Validation phase Which collective document is created once the needs of a database management system (DBMS) are identified and analyzed? Requirements specification Detail design Mission statement Mixed strategy What is the correct code to create a database called Sales with a creator name Doug, according to ISO standards? CREATE SCHEMA Sales AUTHORIZATION Doug; CREATE DATABASE Sales AUTHORIZATION Doug; CREATE DATABASE Sales USING Doug; CREATE SCHEMA Sales IDENTIFIED BY Doug; Which database component needs to be backed-up? Database log file Index file View output Database diagram A database has not been physically damaged but has become inconsistent What is the recovery technique that should be used? Restore using the differential backup Restore using the log files Restore using the incremental database copy Restore using the full database copy What is a replication strategy that results in a time delay before all sites are consistent? Symmetric Asynchronous Synchronous Parallel Use the given code to answer the following question: SELECT property_number FROM PropertyForRent WHERE rooms > 7 AND city = ‘London’; What is the correct index required to optimize this query? Index on the 'London' column Index on the property_number column Indexes on all columns of the PropertyForRent table Indexes on the rooms and city columns Which statement describes the optimistic concurrency control technique? Transactions proceed unsynchronized, and checks for conflicts are done when a commit occurs Transactions can read but not update a data item when being used by another transaction Transactions can read and update a data item, and access to other transactions is blocked Transactions involved in a conflict are rolled back and restarted The current backup plan of a company involves backing up the entire database at the beginning of each week Which backup strategy should be implemented to reduce impact of failure throughout the week with minimal disk space usage? Traditional Differential Incremental Full How can indexes improve query performance? Indexes can filter unauthorized access to data Indexes can reduce storage overhead A secondary index can be used primarily to help ordered tuples Indexes can provide accelerated access to the rows of a table Which dimension model schema should be used for a fact table that is surrounded by normalized and denormalized dimension tables? Snowflake schema Starflake schema Star schema Network schema Which property should a conceptual database design have? Inclusion of extraneous information Inclusion of specific database management system (DBMS) Independence of physical considerations Independence of the way the information is consumed Use the given transactions to answer the following question: Image Which concurrency techniques can be used to solve this lost update problem? Choose 2 answers Creating deadlock Performing rollback Applying timestamp Initializing lock