Western Governors University DATABASE 0047 Sophia Database Milestone 3 UNIT 3 — MILESTONE 3 Score 14/21

UNIT 3 — MILESTONE 3 Score 14/21 You passed this Milestone 14 questions were answered correctly. 7 questions were answered incorrectly. 1 In each milestone, you may want or need to use the database and query tool t ...

UNIT 3 — MILESTONE 3 Score 14/21 You passed this Milestone 14 questions were answered correctly. 7 questions were answered incorrectly. 1 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 None 5 2, 3, 4, 5 1, 2, 3, 4, 5 CONCEPT COMMIT and ROLLBACK to Manage Changes 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 statements would clean the data prior to restoring the database mydatabase from backup.sql? psql -a mydatabase < backup.sql psql -c mydatabase < backup.sql psql mydatabase < backup.sql psql -d mydatabase < backup.sql CONCEPT Restore from Backup 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 scenarios reflect the atomicity property? 1. In the library database, there are 50 books available. 2. Billy has checked and there are 50 books. 3. Sam has checked and there are 50 books. 4. Billy has taken out 5 books. 5. The library system informs Sam of the update and Sam now checks that there are 45 books. 6. Sam checks out 10 books. 7. There are now 35 books in the library database. 1. In the flower database, there are 50 flowers available. 2. Reese has checked and there are 50 flowers. 3. Reese has attempted to take out 5 flowers. 4. While trying to take them out, there was an error in trying to dispense. 5. While checking, there are still 50 flowers available in the system. 1. A user attempts to do a product transfer between companies. 2. The quantity of the product is moved from the first company. 3. Only once the product is verified to have been deducted, the quantity is moved to the second company. 4. Verification is done and identifies that the total amounts before and after the transactions are not maintained. 5. The transaction is reverted. 1. Tiffany has updated a customer's address while on the phone with them. 2. The server restarted after Tiffany clicked on save. 3. When the server came back up, Tiffany was able to verify that the address was updated. CONCEPT Atomicity 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/ Most SQL injections are performed through this type of application that acts as an interface to the database. Web Text Image System CONCEPT Application Security 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 statements represents a correctly structured transaction? BEGIN; ROLLBACK; UPDATE track set media_type_id = 1 where album_id = 3; DELETE FROM playlist WHERE playlist_id = 1; INSERT INTO genre (genre_id, name) VALUES (30, 'Funk' ),(31,'Classical'),(32,'Pop Rock'); BEGIN UPDATE track set media_type_id = 1 where album_id = 3 DELETE FROM playlist WHERE playlist_id = 1 INSERT INTO genre (genre_id, name) 1/1/22, 3:42 AM Sophia :: Welcome https://app.sophia.org/spcc/introduction-to-relational-databases/milestone_take_feedbacks/12141789 5/17 VALUES (30, 'Funk' ),(31,'Classical'),(32,'Pop Rock') ROLLBACK BEGIN; UPDATE track set media_type_id = 1 where album_id = 3; DELETE FROM playlist WHERE playlist_id = 1; INSERT INTO genre (genre_id, name) VALUES (30, 'Funk' ),(31,'Classical'),(32,'Pop Rock'); ROLLBACK; UPDATE track set media_type_id = 1 where album_id = 3; DELETE FROM playlist WHERE playlist_id = 1; INSERT INTO genre (genre_id, name) VALUES (30, 'Funk' ),(31,'Classical'),(32,'Pop Rock'); ROLLBACK; CONCEPT Transactions 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/ Which of the following scenarios reflect the consistency property? 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. 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. CONCEPT Consistency 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 statements will grant insert permissions on the track table to all users? GRANT INSERT ON track TO ALL; GRANT INSERT ON track TO public; GRANT INSERT ON public to track; GRANT INSERT ON 'track' TO 'public'; CONCEPT GRANT to Assign Privileges 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 would create a user named temporary_user with the password set as changeme that is valid until 2025-05-01? CREATE USER temporary_user VALID UNTIL '2025-05-01' WITH PASSWORD 'changeme' CREATE USER temporary_user WITH PASSWORD 'changeme' EXPIRES '2025-05-01' CREATE USER temporary_user WITH PASSWORD changeme VALID UNTIL 2025-05-01 CREATE USER temporary_user WITH PASSWORD 'changeme' VALID UNTIL '2025-05-01' CONCEPT 1/1/22, 3:42 AM Sophia :: Welcome CREATE USER/ROLE to Add Users 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 permissions are automatically granted to a user? Change items in the database Enabling extensions All permissions need to be granted to the user or role Any piece that touches the underlying system CONCEPT Superusers 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 scenarios reflects the durability property? 1. A fairly large transaction is run to import new customers. 2. The import is saved to the logs. 3. The import starts to then save to the disk. 4. The disk fails midway through the update before being committed. 5. A new disk replaces the failed disk. 6. The backup is applied and the saving of the data is run from the logs. 1/1/22, 3:42 AM Sophia :: Welcome 1. Jeff checks his user profile and sees his email address is set to [email protected]. 2. Jeff changes his email to set it to [email protected] but does not click on save yet. 3. A customer rep checks into Jeff's account and sees [email protected]. 4. Jeff clicks on save. 5. Another customer rep checks Jeff's account and sees [email protected]. 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 vendor has 5 orders available. 2. A customer attempts to purchase all 5. 3. The system deducts it from the vendor and adds it to the customer. 4. The vendor now has 0 orders available. 5. The customer has 5 orders purchased. 6. The transaction is saved. CONCEPT Durability 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 of the following properties are specific to the durability property? Data used in one transaction cannot be used in another transaction until the first transaction is completed. If any of the transaction parts violates an integrity constraint, the entire transaction must be aborted. A transaction should be treated as a single logical unit of work that is indivisible. In the event of system failure, no transactions that were done should be undone. CONCEPT ACID Properties 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 type of backup would result in the largest storage space? Nightly backup Differential backup Incremental backup Full backup CONCEPT Backup Methods 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. 1/1/22, 3:42 AM Sophia :: Welcome https://app.sophia.org/spcc/introduction-to-relational-databases/milestone_take_feedbacks/12141789 11/17 https://postgres.sophia.org/ Which of the following statements would only dump the data definitions and not the data of the mydatabase to backup.sql? pg_dump -d mydatabase > backup.sql pg_dump mydatabase > backup.sql pg_dump -a mydatabase > backup.sql pg_dump -s mydatabase > backup.sql CONCEPT Create a Backup 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 of the following cases would make the most sense to use a hash index? SELECT * FROM customer WHERE customer_id > 10 AND customer_id < 20; SELECT * FROM album WHERE artist_id < 5; 1/1/22, 3:42 AM Sophia :: Welcome https://app.sophia.org/spcc/introduction-to-relational-databases/milestone_take_feedbacks/12141789 12/17 SELECT * FROM employee WHERE address LIKE '%i%'; SELECT * FROM track WHERE media_type_id = 1; CONCEPT Hash Index 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/ 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 NOINHERIT; GRANT health_department to jimmy; GRANT manager to jimmy; 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 health_department; CREATE ROLE manager NOINHERIT; CREATE ROLE health_department INHERIT; 1/1/22, 3:42 AM Sophia :: Welcome GRANT health_department to jimmy; GRANT manager to health_department; CONCEPT GRANT to Assign Users 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 operators will attempt to use a hash or b-tree index? = >> && @> CONCEPT Index Overview 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. Which of the following statements would create a role named database_admin and gives them the ability to create databases? CREATE ROLE database_admin CREATEROLE; CREATE ROLE database_admin NOINHERIT; CREATE ROLE database_admin INHERIT; CREATE ROLE database_admin CREATEDB; CONCEPT CREATE ROLE to Create Groups 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 is an advantage of both the command line and the GUI? We can use any parameters without limitation. We can restore a remote database. We can back up multiple databases at once. We can encode the backup file to another format. CONCEPT Backups: Command Line vs. GUI 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 cases would make the most sense to use a b-tree index? 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%'; SELECT * FROM employee WHERE address LIKE '%i%'; CONCEPT B-Tree Index 20 1/1/22, 3:42 AM Sophia :: Welcome https://app.sophia.org/spcc/introduction-to-relational-databases/milestone_take_feedbacks/12141789 16/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. 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. 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. 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. CONCEPT Isolation 21 1/1/22, 3:42 AM Sophia :: Welcome https://app.sophia.org/spcc/introduction-to-relational-databases/milestone_take_feedbacks/12141789 17/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 DROP INDEX statements parameters' are set by default? DROP INDEX myindex CASCADE; DROP INDEX IF EXISTS myindex; DROP INDEX CONCURRENTLY myindex; DROP INDEX myindex RESTRICT; CONCEPT DROP INDEX to Remove Indexes About Contact Us Privacy Policy Terms of Use