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

DATABASE C192 OA Study Guide 2_2020 – Western Governors University Answers labeled with @ What is a limitation of a database management system (DBMS)? Integrity Consistency Redundancy Complexity Which data ...

DATABASE C192 OA Study Guide 2_2020 – Western Governors University Answers labeled with @ What is a limitation of a database management system (DBMS)? Integrity Consistency Redundancy Complexity Which database management system (DBMS) type is an integrated data store (IDS) an example of? Relational DBMS oracle Network DBMS IDS Hierarchical DBMS IMS Object-oriented DBMS What is a valid ISO SQL data type? Prefix Tiny int Boolean Datestamp 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 What is the number of tuples contained in a relationship called? Cardinality Domain Intension Degree Which enhanced entity-relationship (EER) model implementation should be used to represent both managers and supervisors? Using one entity representing common attributes, one for unique manager attributes, and one for unique supervisor attributes Using one entity representing the union of supervisor attributes and manager attributes Using one entity representing the intersection of attributes common to both a manager and supervisor Using one entity representing the attributes of a manager and another for representing the attributes of a supervisor Which actions are appropriate to incorporate for the conceptual database design? Choose 2 answers Identifying relationship type Checking for future growth Designing base relations Determining entity type Checking integrity constraints Which feature of database design characterizes the logical phase? Specification of entities and relationships between entities Determination of the type of DBMS to be used Determination of the specific DBMS vendor product to use Specification of table definitions 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 should be considered for estimating disk space when developing a physical database design? The number of transactions The number of tuples The query execution plan The transaction usage map Which function can be performed using data manipulation language (DML)? Insert Revoke Create Drop Which statement will remove the table “Customers”? REMOVE TABLE Customers; DROP TABLE Customers; DELETE TABLE Customers; ALTER TABLE Customers; 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 What is an example of the correct syntax for inserting a record into a table? INSERT INTO TABLE StateCity State=‘Wisconsin’, City=‘Milwaukee’ INSERT INTO StateCity (‘Wisconsin’, ‘Milwaukee’) INSERT INTO StateCity FROM (State, City) SELECT ‘Wisconsin’, ‘Milwaukee’ INSERT INTO StateCity (State, City) VALUES (‘Wisconsin’, ‘Milwaukee’) What is an example of the correct syntax for updating a record in a table? UPDATE StateCity SET state=‘Wisconsin’ AND city=‘Milwaukee’ WHERE state=‘WI’ AND city=‘Milw’ UPDATE sc set state=‘Wisconsin’, city=‘Milwaukee’ FROM StateCity sc WHERE scstate=‘WI’ AND sccity=‘Milw’ UPDATE StateCity (State, City) SET state=‘Wisconsin’, city=‘Milwaukee’ SELECT state city FROM StateCity WHERE state=‘WI’ AND city=‘Milw’ UPDATE StateCity SET state=‘Wisconsin’, city=‘Milwaukee’ INTO state=‘WI’ AND city=‘Milw’ What is an example of the correct syntax for deleting a record in a table? DELETE FROM StateCity WHERE state=‘Wisconsin’ AND City=‘Milwaukee’ DELETE FROM StateCity (State, City) VALUES (‘Wisconsin’ ‘Milwaukee’) DELETE FROM TABLE StateCity WHERE state=‘Wisconsin’ AND city=‘Milwaukee’ DELETE FROM StateCity (State, City) SELECT ‘Wisconsin’, ‘Milwaukee’ Which DML statement should be used to obtain the data of table PrivateOwner of those persons with telf_num starting with “0141”? SELECT * FROM PrivateOwner WHERE telf_num=‘0141’; SELECT * FROM PrivateOwner WHERE telf_num LIKE ‘0141%’; SELECT * FROM PrivateOwner WHERE telf_num LIKE ‘%0141’; SELECT * FROM PrivateOwner WHERE telf_num IN (0141); Use the given SQL statement to answer the following question: SELECT DISTINCT property_number FROM Viewing; What will be returned from the SQL statement? A numerically ordered list of property_number All rows containing property_number A count of how many property_number exist A unique list of property_number Which SQL statement will return the number of values in the ID column? SELECT AVG(ID) FROM users SELECT MAX(ID) FROM users SELECT COUNT(ID) FROM users SELECT SUM(ID) FROM users Use the given SQL statement 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 statement do? It selects the branch number, staff number, and salary ordered by branch It selects the total salary by branch and orders the branch numbers It selects the branch number and total sum of staff salaries for all branches and ordered by branch It selects the branch number, total number of staff, and total salary for each branch and ordered by branch Use the given code to answer the following question: SELECT b*, p* FROM Branch b LEFT JOIN PropertyForRent p ON bcity = pcity; What does this code do? It creates a left outer join of the Branch and PropertyForRent tables on the city field It creates a left inner join of the Branch and PropertyForRent tables on the city field It updates values in the PropertyForRent table without matching records based on the city field It creates a right outer join of the Branch and PropertyForRent tables on the city field Which code structure is used to declare a stored procedure using PL/SQL? Image Image Image Image CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters] IS Declaration section BEGIN Execution section EXCEPTION Exception section END; Which statement will generate a PL/SQL package? GENERATE PACKAGE employeePackage AS procedure employeeProperties(staff_number); END employeePackage; ALTER PACKAGE employeePackage AS procedure employeeProperties(staff_number); END employeePackage; INSERT PACKAGE employeePackage AS procedure employeeProperties(staff_number); END employeePackage; CREATE PACKAGE employeePackage AS procedure employeeProperties(staff_number); END employeePackage; 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 Which challenge is associated with developing a data warehouse? Choose 2 answers Deletion of transactional data Complexity of integration High demand for resources Maintaining normalization of the data What is a benefit of a data mart? It is good for transaction processing It requires a subset of corporate data It is good for providing analytic capabilities across an entire business enterprise It gives users access to data they need to analyze infrequently Become a master of the Rubik's Cube with this tutorial Learn how to solve the cube with the beginner's method! Which characteristic describes a distributed database management system (DDBMS)? Data is processed under a single server Data is split into multiple fragments Data fragments may not replicate Data is stored in one central repository What is the fastest growing online analytical processing (OLAP) server category? Desktop OLAP Multi-dimensional OLAP Hybrid OLAP Relational OLAP What is a data representation concept associated with online analytical processing (OLAP)? The concept of “associate arrays” The concept of “lists” The concept of “dimensions” The concept of “hash tables” Match the model name to the statement that describes it Answer options may be used more than once or not at all Provides high-performance data retrieval Used to design an OLTP system Removes redundancy in data Used to design the database of a data warehouse A DBA needs to implement a data warehouse where fast data access on denormalized data is a priority What is the appropriate multidimensional data model that should be chosen for the database design? Hierarchical schema Snowflake schema Star schema Starflake schema 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 Which multidimensional data model has normalized hierarchical dimension tables and a fact table? Snowflake schema Starflake schema Relational schema Star schema Which dimensional data model is designed with one fact table surrounded by a mixture of normalized and denormalized tables? Star schema Starflake schema Relational schema Snowflake schema Which technology allows the creation of functions embedded within HTML code for data presentation? Common gateway interface HTTP cookies Java persistence API Scripting languages What is an advantage of using XML to present data stored in a database management system (DBMS)? It allows for the execution of stored procedures It allows for platform and vendor independence It provides for data redundancy It implements intrinsic data types 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 Match the strategy name to the appropriate description Answer options may be used more than once or not at all Database objects are assigned classification levels MCI Users are given appropriate access privileges on specific database objects DCL Subjects (users and programs) are given a designated clearance level MCI Users obtain privileges when they create objects and pass on privileges to others DCL 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 is a common component of encoding data when using a symmetric encryption technique? Public key cryptosystems Different encryption and decryption keys Shared key exchange Locking algorithm How is atomicity maintained throughout the life of a transaction? The transaction is transformed from one consistent state to another consistent state One transaction executes independently from other transactions If an error occurs during the transaction, it is rolled back Changes are permanently recorded in the database 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 What are the approaches that should be used to convert a non-serial schedule into serializable? Changing the order of conflicting read operations Changing the order of non-conflicting read operations Changing the order of conflicting write operations Changing the order of non-conflicting write operations What is a characteristic of the shared locking method? It allows a transaction to read a data item but not update it It allows a transaction to read and delete a data item It allows a transaction to both read and update a data item It allows a transaction to write a data item but not read it What causes a deadlock to occur? Transactions ordered with timestamps Transactions timing out Transactions blocking each other Transactions executing one after another What describes the implementation of timestamping? Assigning older transactions with higher priority Ordering new transactions with high priority Removing older transactions when deadlock occurs Running transactions at random increments What is the last phase of the optimistic concurrency control protocol? Write phase Read phase Validation phase Growing phase Which activities need to be completed in the implementation phase of database development? Choose 2 answers Creating user views Creating conceptual models Designing the user interface Collecting database requirements Executing DDL statements Try to figure out the solution of the Rubik's Cube with the online simulator See how far you can get Which DDL statement can be used for dropping the database? DROP DIRECTORY DROP DEFAULT DROP SCHEMA DROP TABLESPACE Which database management system (DBMS) component needs to be backed-up to be used in a recovery event? Database models Data dictionary Data audit logs Database physical files The database system has completely failed due to a disk head crash Which recovery step would be appropriate in this situation? Performing a rollback on the database Performing a full restore from the last backup Designing a new database system Creating a new backup from the system Match each description with the appropriate data transfer type Answer options may be used more than once or not at all Regaining database consistency may take from a few seconds to several days asyn Transactions will not complete if one or more sites are not available asyn Target data is modified after source data modification is complete syn Replicated data is updated immediately when the source data is updated as part of a single transaction syn 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 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 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 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 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 statement should be used to create a horizontal view? Create a view using difference in the subselect Create a view using projection in the subselect Create a view using cartesian product in the subselect Create a view using selection in the subselect