Western Governors University DATABASE 0047: relational_database_final_milestone . MILESTONE Score 19/25

Document Content and Description Below

MILESTONE Score 19/25 You passed this Milestone 19 questions were answered correctly. 6 questions were answered incorrectly. 1 In each milestone, you may want or need to use the database and que... ry 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 require no more than 2 backup sets? Differential and incremental backup Incremental and full backup Full backup and nightly backup Differential and full backup CONCEPT Backup Methods 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/ What is wrong with the following ERD meant to show the attributes of an entity? The symbols used are swapped. The attributes are not correctly linked to the entity. The primary key is not indicated correctly. There is nothing wrong with the ERD. CONCEPT Adding Attributes 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/ 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 2. Insert 3. Insert 4. Delete 5. Update ROLLBACK COMMIT 1 1, 2, 3, 4, 5 none 5 CONCEPT COMMIT and ROLLBACK to Manage Changes 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/ Using the IN operator, filter the invoice table to find the billing city being set to Paris, London, New York, or Brussels. Identify the billing postal code of the 4th record. 1000 SW1V 3EN 75009 75002 CONCEPT IN to Filter Data 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/ Given the tables provided, which of the following DROP TABLE series of statements would correctly remove the tables without causing an error? DROP TABLE customer; DROP TABLE invoice; DROP TABLE invoice_line; DROP TABLE genre; DROP TABLE album; DROP TABLE artist; DROP TABLE invoice_line; DROP TABLE playlist_track; DROP TABLE playlist; DROP TABLE genre; DROP TABLE album; DROP TABLE artist; CONCEPT DROP TABLE to Remove Tables 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 context of a database, what is knowledge? A body of information and facts about a specific topic A process that focuses on data collection, storage, and retrieval Raw facts of interest to the end user A computer structure that holds a collection of related data CONCEPT Database Purpose 7 Which of the following is a key part of physical design in an entity relationship model? This model uses column names instead of attributes. In this model, the characteristics such as location, path, and format are described. In this model, no primary keys are specified in the entities. This model resolves the many-to-many relationships to multiple one-to-many relationships. CONCEPT Physical Design 8 Which of the following is a key part of logical design in an entity relationship model? This model avoids any database model-specific details. This model has normalization that occurs. In this model, no attributes are specified. This model converts entities into tables. CONCEPT Logical Design 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/ Given the following view that has been created, how would you query the view to list the artist names in ascending order and album titles in desc order? CREATE VIEW album_artist_names AS SELECT album.title, artist.name FROM album INNER JOIN artist ON album.artist_id = artist.artist_id; SELECT * FROM album_artist_names ORDER BY name, title DESC; SELECT * FROM album_artist_names ORDER BY name ASC, title DESC; SELECT * FROM album_artist_names ORDER BY name DESC, title; SELECT * FROM album_artist_names ORDER BY name DESC, title ASC; CONCEPT VIEW & Complex Queries 10 What type of scenario would help protect against a security issue in relation to database migration challenges? Testing for data loss Converting schemas Normalizing data Encrypting the data CONCEPT Migrating Databases in an Organization 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/ In trying to delete from the playlist table, what is the cause of this error? "Query failed because of: error: update or delete on table "playlist" violates foreign key constraint "playlist_track_playlist_id_fkey" on table "playlist_track" The playlist_id doesn't exist in the playlist table. The playlist_track table has a reference to the playlist_id that is being deleted. The playlist_id doesn't exist in the playlist_track table. The track has to be deleted first before the playlist is deleted. CONCEPT Foreign Key Errors 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/ Given the following queries, which of these would be the most efficient? 1. SELECT * FROM invoice WHERE customer_id IN (SELECT customer_id FROM customer WHERE country like 'U%'); 2. SELECT invoice.* FROM invoice INNER JOIN customer ON customer.customer_id = invoice.customer_id WHERE country like 'U%'; Query #1 would be more efficient as it is based on primary and foreign keys. Query #2 would be more efficient as it is not using indexed columns. Query #2 would be more efficient as it is based on primary and foreign keys. Both would be the same as both use the same indices for the join and filter. CONCEPT Subquery Performance 13 What is a benefit of using a lookup table? They guarantee that there will be no data redundancies due to functional dependencies. They model pure relationships rather than entities. They resolve weak relationships. They provide fast answers to some questions. CONCEPT Reference Tables 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/ Identify the correctly constructed ALTER TABLE statement that removes the column phone from the employee table. ALTER TABLE employee DROP COLUMN phone; ALTER TABLE employee DROP COLUMN phone VARCHAR (100); ALTER TABLE employee DROP COLUMN home_phone; ALTER TABLE employee ADD phone; CONCEPT ALTER TABLE to Change Columns: Add/Drop 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/ Use the following data model for this question: Recipe recipe_id title Ingredient ingredient_id name Recipe_Ingredient recipe_ingredient_id recipe_id ingredient_id Which of the following is a situation where an OUTER JOIN could be useful? To view recipes that have ingredients in the ingredients table To view only ingredients that are being utilized for particular recipes To view all ingredients in the database even if they are not being used for a particular recipe To view recipes that have the word "banana" in their title CONCEPT Outer Joins 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 scenarios reflects the durability property? 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. Which 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 comes back up, Tiffany was able to verify that the address was updated. 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. CONCEPT Durability 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 statement(s) would successfully delete the playlist_id 8 from the playlist table? DELETE FROM playlist_id WHERE playlist_id_id = 8; DELETE FROM playlist_track WHERE playlist_id_id = 8; DELETE from playlist_track WHERE playlist_id = 8; DELETE from playlist WHERE playlist_id = 8; DELETE FROM playlist_id WHERE playlist_id_id = 8; DELETE FROM playlist_track WHERE playlist_id_id = 8; CONCEPT DELETE FROM to Remove Row 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/ What is wrong with the following ERD meant to show a relationship between two entities? There is nothing wrong with the ERD. One of the items is set up as an attribute. There is no relationship between the entities. Both of the objects are attributes. CONCEPT Tables as Entities 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/ What will be the result of the query based on the following criteria? <columnname> < ALL (<subquery>) Returns true if the value is less than or equal to the smallest value returned by the subquery. Returns true if the value is less than or equal to any of the values returned by the subquery. Returns true if the value is less than any of the values returned by the subquery. Returns true if the value is less than the smallest value returned by the subquery. CONCEPT ANY and ALL Operators 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/ Identify the line of code that would either generate a syntax, logical, or requirements error in the following CREATE TABLE statement. The statement should create a table called 'users' that consists of the user_id as the primary key that is auto-incremented, the username, and password. CREATE TABLE user( user_id serial PRIMARY KEY, username VARCHAR (50) password VARCHAR (50) ); 3 4 1 2 CONCEPT Primary Key and Auto-increment 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/ Using the WHERE clause, filter the employee table to include individuals that report to Andrew Adams, who has an employee_id of 1. Identify the title of the 2nd individual listed. Sales Support Agent IT Manager Sales Manager General Manager CONCEPT WHERE to Filter Data 22 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 backup the data, schema, or both at the same time. We can compress the backup script. We can back up a remote server. CONCEPT Backups: Command Line vs. GUI 23 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 these SELECT statements would successfully display exactly five columns of data from the customer table? SELECT * FROM customer; SELECT customer_id, city, state, phone, company FROM customer; SELECT customer_id, city_id, state_id, phone_id, company_id FROM customer; SELECT customer_id + city + state + phone + company FROM customer CONCEPT SELECT to Display Data 24 What would be introduced as a criterion of a table to be in second normal form? Each cell of a table should have a single value. There should be no repeating groups. All attributes in a table are dependent on the primary key. There should be no repeating groups. CONCEPT Second Normal Form 25 Which of the following is a key part of conceptual design in an entity relationship model? This model resolves the many-to-many relationships to multiple one-to-many relationships. This model does not depend on the database management software or the hardware used to implement the model. This model converts attributes into columns. This model adds all of the attributes for each entity. CONCEPT Conceptual Design About Contact Us Privacy Policy Terms of Use [Show More]

Last updated: 1 year ago

Preview 1 out of 19 pages