Western Governors University DATABASE 0047 relational_database_unit_3_milestones_3 UNIT 3 — MILESTONE 3 Score 15/21

Document Content and Description Below

UNIT 3 — MILESTONE 3 Score 15/21 You passed this Milestone 15 questions were answered correctly. 6 questions were answered incorrectly. 1 In each milestone, you may want or need to use the dat... abase and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ Which of the following statements would create a role named database_admin and gives them the ability to create databases? CREATE ROLE database_admin CREATEDB; CREATE ROLE database_admin NOINHERIT; CREATE ROLE database_admin CREATEROLE; CREATE ROLE database_admin INHERIT; Common mistakes when creating roles are using quotes around the role name, not setting the correct role privileges, or not using the right syntax. CONCEPT CREATE ROLE to Create Groups 2 In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ Which of the following criterion is specific to the isolation property? Once transaction changes are done and saved, they cannot be lost. When a transaction is completed, the database must be in a consistent state. Data used in one transaction cannot be used in another transaction until the first transaction is completed. All SQL requests of a transaction must be completed, if not, the transaction must be aborted. The isolation property ensures that the data that's used in a transaction cannot be used in another transaction until the original transaction is complete with it. This is important when you have multiple users accessing and updating data in the database at the same time. CONCEPT ACID Properties 3 In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ Which of the following cases would make the most sense to use a b-tree index? SELECT * FROM employee WHERE address LIKE '%i%'; SELECT * FROM album WHERE artist_id < 5; SELECT * FROM customer WHERE customer_id > 10 AND customer_id < 20; SELECT * FROM customer WHERE fax LIKE '+55%'; The b-tree index makes the most sense to use when the data is even and balanced. It works best on data types like text, numbers and timestamps when we compare ranges or content that start with a value. It does not do well with wild cards at the start of a comparison or ranges. CONCEPT B-Tree Index 4 In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ Which of the following DROP INDEX statements would give a notice if the index does not exist but not an error? DROP INDEX myindex RESTRICT; DROP INDEX CONCURRENTLY myindex; DROP INDEX myindex; DROP INDEX IF EXISTS myindex; Common mistakes when it comes to dropping an index include not including the correct index name, not using the right syntax, and not using the right parameters based on the requirements. Using IF EXISTS will not throw an error if the index does not exist. CONCURRENTLY will drop the index without locking concurrent select, insert, updates and deletes. CASCADE will automatically drop objects that depend on the index. RESTRICT will refuse to drop the index if any objects depend on it. This is set by default. CONCEPT DROP INDEX to Remove Indexes 5 In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ Which of the following scenarios reflect the atomicity property? 1. User 1 has $500 in their online account. 2. User 2 has $300 in their online account. 3. User 1 sends user 2 $100. 4. User 1 has $400 in their account. 5. User 2 has $300 in their account. 6. The transaction is reverted. 1. A fairly large update to the products table. 2. The update is saved to the logs. 3. The update starts to then save to the disk. 4. The system fails midway through. 5. When the system starts back up, the committed transactions that haven't been written to disk are written. 1. Transaction 1 reads a product's cost which is set at $100. 2. Transaction 1 updates the product to set the price to $90. 3. Transaction 2 reads the same product's cost and sees $100. 4. Transaction 1 commits the cost. 5. Transaction 3 reads the same product's cost and sees $90. 1. The product quantity starts at 200. 2. Transaction 1 runs to read the quantity of the product to be updated. 3. Transaction 2 runs in parallel to read the quantity of the product to be updated. 4. Transaction 1 updates the product quantity to reduce it by 100. 5. Transaction 2 updates the product quantity to reduce it by 5. 6. The product quantity ends at 195. Atomicity requires that all SQL requests in a transaction should be fully completed and if not, the entire transaction should be aborted. The transaction should be viewed as a single logical unit of work that is indivisible. CONCEPT Atomicity 6 In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ In the following scenario, which of the following statements would be saved to the database assuming these are all in a single transaction? 1. Update ROLLBACK 2. Insert 3. Insert 4. Delete ROLLBACK 5. Update COMMIT 1, 2, 3, 4, 5 2, 3, 4, 5 5 None In a transaction, if a ROLLBACK statement is run, all statements up to the prior COMMIT or ROLLBACK will be reverted. If a COMMIT statement is run, all statements up to the prior ROLLBACK or COMMIT will be saved. CONCEPT COMMIT and ROLLBACK to Manage Changes 7 In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ Which of the following scenarios reflects the durability property? 1. The product quantity starts at 200. 2. Transaction 1 runs to read the quantity of the product to be updated. 3. Transaction 2 runs in parallel to read the quantity of the product to be updated. 4. Transaction 1 updates the product quantity to reduce it by 100. 5. Transaction 2 updates the product quantity to reduce it by 5. 6. The product quantity ends at 195. 1. Transaction 1 reads a product's cost which is set at $100. 2. Transaction 1 updates the product to set the price to $90. 3. Transaction 2 reads the same product's cost and sees $100. 4. Transaction 1 commits the cost. 5. Transaction 3 reads the same product's cost and sees $90. 1. User 1 has $500 in their online account. 2. User 2 has $300 in their online account. 3. User 1 sends user 2 $100. 4. User 1 has $400 in their account. 5. User 2 has $300 in their account. 5. The transaction is reverted. 1. A fairly large update is made to the products table. 2. The update is saved to the logs. 3. The update starts to then save to the disk. 4. The system fails midway through. 5. When the system starts back up, the committed transactions that haven't been written to disk are written. The durability property ensures that once a transaction is done and committed, the changes cannot be undone or lost even if there is system failure. CONCEPT Durability 8 In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ Which of the following statements represents a correctly structured transaction? BEGIN UPDATE customer SET postal_code = '33433' WHERE customer_id = 22 UPDATE customer SET postal_code = '10789' WHERE city = 'Berlin' UPDATE customer SET company = 'Humor Inc.' WHERE city = 'Prague' ROLLBACK BEGIN; UPDATE customer SET postal_code = '33433' WHERE customer_id = 22; UPDATE customer SET postal_code = '10789' WHERE city = 'Berlin'; UPDATE customer SET company = 'Humor Inc.' WHERE city = 'Prague'; ROLLBACK; BEGIN; ROLLBACK; UPDATE customer SET postal_code = '33433' WHERE customer_id = 22; UPDATE customer SET postal_code = '10789' WHERE city = 'Berlin'; UPDATE customer SET company = 'Humor Inc.' WHERE city = 'Prague'; UPDATE customer SET postal_code = '33433' WHERE customer_id = 22; UPDATE customer SET postal_code = '10789' WHERE city = 'Berlin'; UPDATE customer SET company = 'Humor Inc.' WHERE city = 'Prague'; ROLLBACK; Common mistakes with a transaction include missing a ; after BEGIN as well as each statement including the COMMIT or ROLLBACK statement, not having the COMMIT or ROLLBACK at the end of the statement, and not including BEGIN at the start of the statement. CONCEPT Transactions 9 In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ Which of the following statements would restore just the table 'customer' from the mydatabase database from backup.sql? psql mydatabase customer < backup.sql psql -t customer mydatabase < backup.sql psql -a customer mydatabase < backup.sql psql -t customer < backup.sql Common mistakes when restoring a database using the command line include, using the wrong file redirect operator, using the incorrect syntax, not using the right order of statements, or not including the command line options. CONCEPT Restore from Backup 10 In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ Which of the following statements would clean the data in a data dump prior to outputting the commands to create them of the mydatabase to backup.sql? pg_dump mydatabase > backup.sql pg_dump -c mydatabase > backup.sql pg_dump -a mydatabase > backup.sql pg_dump -b mydatabase > backup.sql Common mistakes when backing up a database using the command line include, using the wrong file redirect operator, using the incorrect syntax, not using the right order of statements or not including the command line options. CONCEPT Create a Backup 11 In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ Which type of backup would result in the fastest restoration? Nightly backup Full backup Incremental backup Differential backup Full backups are a full copy of the entire data set. Although they are the best protection, they are timeconsuming and quite large to store. Incremental backups only back up the data that has changed since the previous backup even if it's another incremental backup. These file sizes are the smallest out of the lot. Differential backups are similar to incremental backups but it starts with a full backup and includes data since the prior full backup. At most, there are only 2 backups to restore from. CONCEPT Backup Methods 12 In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ Which of the following scenarios reflect the consistency property? 1. Transaction 1 reads a product's cost which is set at $100. 2. Transaction 1 updates the product to set the price to $90. 3. Transaction 2 reads the same product's cost and sees $100. 4. Transaction 1 commits the cost. 5. Transaction 3 reads the same product's cost and sees $90. 1. A fairly large update to the products table. 2. The update is saved to the logs. 3. The update starts to then save to the disk. 4. The system fails midway through. 5. When the system starts back up, the committed transactions that haven't been written to disk are written. 1. The product quantity starts at 200. 2. Transaction 1 runs to read the quantity of the product to be updated. 3. Transaction 2 runs in parallel to read the quantity of the product to be updated. 4. Transaction 1 updates the product quantity to reduce it by 100. 5. Transaction 2 updates the product quantity to reduce it by 5. 6. The product quantity ends at 95. 1. User 1 has $500 in their online account. 2. User 2 has $300 in their online account. 3. User 1 sends user 2 $100. 4. User 1 has $400 in their account. 5. User 2 has $300 in their account. 5. The transaction is reverted. The consistency property ensures that a transaction takes the database from one consistent state to another consistent state. If any transaction parts violates an integrity constraint, the entire transaction should be rolled back. CONCEPT Consistency 13 In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ Which of the following statements would create a user named testuser1 with the password set as testpassword1 that is valid until 2042-01-01? CREATE USER testuser1 VALID UNTIL '2042-01-01' WITH PASSWORD 'testpassword1' CREATE USER testuser1 WITH PASSWORD testpassword1 VALID UNTIL 2042-01-01 CREATE USER testuser1 WITH PASSWORD 'testpassword1' VALID UNTIL '2042-01-01' CREATE USER testuser1 WITH PASSWORD 'testpassword1' EXPIRES '2042-01-01' Common mistakes when creating a user include adding quotes to the username, forgetting to add quotes to the password, incorrectly setting the additional parameters, or using the incorrect syntax. CONCEPT CREATE USER/ROLE to Add Users 14 In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ Which is an advantage of both the command line and the GUI? We can encode the backup file to another format. We can use any parameters without limitation. We can restore a remote database. We can back up multiple databases at once. With the command line, you have more flexibility with all of the parameters to backup and restore the database. You're able to pipe together commands to be able to backup and restore data into different databases and have more control. With the GUI, you have more options to select from but all of them are limited to the functionality that has been implemented. Both types do allow us to continue to restore a database or stop if there's an error. We're also able to encode the backup files or backup the data, schema, or both using either option. CONCEPT Backups: Command Line vs. GUI 15 In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ Using this type of process between the user input and dynamically created statements can help reduce the number of SQL injection attacks. Filtering Posting Coding Construction By filtering the user input, we can ensure that we're checking for any special characters that should not be included. CONCEPT Application Security 16 In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ Which of the following cases would make the most sense to use a hash index? SELECT * FROM album WHERE artist_id < 5; SELECT * FROM track WHERE media_type_id = 1; SELECT * FROM customer WHERE customer_id > 10 AND customer_id < 20; SELECT * FROM employee WHERE address LIKE '%i%'; Hash indexes can only be used when we have equality comparisons. It does not do well with wild cards or any ranges. CONCEPT Hash Index 17 In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ Which of the following scenarios reflect the isolation property? 1. User 1 has $500 in their online account. 2. User 2 has $300 in their online account. 3. User 1 sends user 2 $100. 4. User 1 has $400 in their account. 5. User 2 has $300 in their account. 6. The transaction is reverted. 1. A fairly large update to the products table. 2. The update is saved to the logs. 3. The update starts to then save to the disk. 4. The system fails midway through. 5. When the system starts back up, the committed transactions that haven't been written to disk are written. 1. The product quantity starts at 200. 2. Transaction 1 runs to read the quantity of the product to be updated. 3. Transaction 2 runs in parallel to read the quantity of the product to be updated. 4. Transaction 1 updates the product quantity to reduce it by 100. 5. Transaction 2 updates the product quantity to reduce it by 5. 6. The product quantity ends at 195. 1. Transaction 1 reads a product's cost which is set at $100. 2. Transaction 1 updates the product to set the price to $90. 3. Transaction 2 reads the same product's cost and sees $100. 4. Transaction 1 commits the cost. 5. Transaction 3 reads the same product's cost and sees $90. The isolation property ensures that the data that's used in a transaction cannot be used in another transaction until the original transaction is complete with it. This is important when you have multiple users accessing and updating data in the database at the same time. CONCEPT Isolation 18 In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ Which of the following statements will grant insert permissions on the track table to all users? GRANT INSERT ON track TO public; GRANT INSERT ON track TO ALL; GRANT INSERT ON 'track' TO 'public'; GRANT INSERT ON public to track; Common mistakes when granting privileges to roles include using quotes around the table or role name, not using commas between privileges, including the incorrect privileges, or not using the right syntax. CONCEPT GRANT to Assign Privileges 19 In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ Which of the following sequence of statements would ensure that Jimmy has the privileges of the roles of health_department and manager? CREATE ROLE manager NOINHERIT; CREATE ROLE health_department INHERIT; GRANT health_department to jimmy; GRANT manager to health_department; CREATE ROLE manager NOINHERIT; CREATE ROLE health_department NOINHERIT; GRANT manager to jimmy; GRANT manager to health_department; CREATE ROLE manager NOINHERIT; CREATE ROLE health_department NOINHERIT; GRANT health_department to jimmy; GRANT manager to jimmy; CREATE ROLE manager NOINHERIT; CREATE ROLE health_department NOINHERIT; GRANT health_department to jimmy; GRANT manager to health_department; Common mistakes when assigning roles include not identifying which roles have INHERIT or NOINHERIT privileges, or not ensuring that the user has all of the roles allocated to the user. CONCEPT GRANT to Assign Users 20 In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ Which of the following operators will attempt to use a hash or b-tree index? = @> && >> By default, the b-tree index will be used if one of the following operators are used: <, <=, =, >= or >. The hash index will only be used if it is involved in a = operator. GiST indexes are focused on multiple indexing strategies being used and would be used in other operators. CONCEPT Index Overview 21 In each milestone, you may want or need to use the database and query tool to answer some of the questions. We suggest you open the tool in another browser tab while you are working on this assessment. https://postgres.sophia.org/ Which of the following permissions are automatically granted to a user? Any piece that touches the underlying system Change items in the database Enabling extensions All permissions need to be granted to the user or role All permissions need to be granted to the user explicitly for the user to have access. Only superusers have all permissions directly available to their account or role. CONCEPT Superusers About Contact Us Privacy Policy Terms of Use [Show More]

Last updated: 1 year ago

Preview 1 out of 18 pages