introduction-to-relational-databases DATABASE 0047 Sophia Database FINAL MILESTONE Score 20/25 | Western Governors University

1/1/22, 4:27 AM Sophia :: Welcome 1/20 MILESTONE Score 20/25 You passed this Milestone 20 questions were answered correctly. 5 questions were answered incorrectly. 1 What is NOT another name for an associative e ...

1/1/22, 4:27 AM Sophia :: Welcome 1/20 MILESTONE Score 20/25 You passed this Milestone 20 questions were answered correctly. 5 questions were answered incorrectly. 1 What is NOT another name for an associative entity? Composite entity Strong entity Linking table Bridge entity Associative entities can have many different names, such as composite entity, bridge entity, or linking tables. They all refer to the same thing. CONCEPT Associative Entities 2 Is this dataset in 3NF? If not, why not? 1/1/22, 4:27 AM Sophia :: Welcome 2/20 No, there is transitive functional dependency with the zip, city, and state. Yes, it is in 3NF. No, it is not yet in 2NF. No, there are repeating groups. The first normal form indicates that each cell of a table should contain exactly one value with no repeating groups. The second normal form indicates that the table should already be in first normal form and the primary 1/1/22, 4:27 AM Sophia :: Welcome 3/20 key of a table should be composed of a single column. No non-prime attribute is dependent on the proper subset of any candidate key of a table. The third normal form indicates that the table should be in second normal form and that there shouldn't be any functional dependency. In 3NF, all transitive functional dependency of a nonprime attribute on any super key should be removed. CONCEPT Third Normal Form 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/ USING the SELECT statement, query the track table ordered by the bytes. Set the LIMIT to 5 and OFFSET to 5. What is the name of the last row returned? Deixa Entrar Bossa Evil Walks Snowballed Common mistakes with the LIMIT and OFFSET clauses include mixing up the two, not using the ORDER BY clause to enforce a predictable result set, and not using the rows skipped using OFFSET. CONCEPT LIMIT and OFFSET to Cap Results 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? 1/1/22, 4:27 AM Sophia :: Welcome 4/20 DROP INDEX CONCURRENTLY myindex; DROP INDEX myindex; DROP INDEX IF EXISTS myindex; DROP INDEX myindex RESTRICT; 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/ Given the initial tables in our example database, the organization would like to remove a playlist. What order should the table data be deleted from? playlist playlist_track playlist playlist_track track track playlist_track playlist 1/1/22, 4:27 AM Sophia :: Welcome 5/20 playlist_track playlist When deleting data from tables that have foreign keys, it is important to consider the order of the tables as data must be deleted in the order that they are referenced starting with the table with the most references going down to the one with the least. This is the case as long as no other data that references them still exists. As it is only a playlist that should be removed, the playlist_tracks need to be removed first and then the playlist. CONCEPT Foreign Keys & Referential Data 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 isolation property? 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. 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. 1/1/22, 4:27 AM Sophia :: Welcome 6/20 4. Verification is done and identifies that the total amounts before and after the transactions are not maintained. 5. The transaction is reverted. 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 7 Which of the following is a key part of physical design in an entity relationship model? 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. This model uses column names instead of attributes. In the physical design, the entities are translated into tables and attributes and translated into columns. This model is completely dependent on the hardware and database management software and should include all table structures including the column name, data types, column constraints, primary keys, foreign keys, and relationships. CONCEPT Physical Design 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/ Given the invoice table and the data that it contains, assuming that you have the rights to modify the table, which of the following ALTER TABLE statements would create an error. 1/1/22, 4:27 AM Sophia :: Welcome 7/20 ALTER TABLE invoice ALTER COLUMN billing_state TYPE VARCHAR (100); ALTER TABLE invoice ALTER COLUMN billing_city TYPE VARCHAR (100); ALTER TABLE invoice ALTER COLUMN total TYPE VARCHAR (100); ALTER TABLE invoice ALTER COLUMN invoice_id TYPE VARCHAR (50); Common mistakes when modifying the data type of a column include not casting the variables if needed, not including the table and column names to change, the column being a primary or foreign key, considering the error messages that may exist when converting the data that already exists in the table. CONCEPT ALTER TABLE to Change Columns: Data Characteristics 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 cases would make the most sense to use a hash index? SELECT * FROM artist WHERE artist_id < 10; SELECT * FROM album WHERE email LIKE '%t%'; SELECT * FROM playlist WHERE playlist_id = 4; 1/1/22, 4:27 AM Sophia :: Welcome 8/20 SELECT * FROM track WHERE track_id > 1 AND track_id < 15; 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 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/ What type of cardinality is demonstrated by the following ERD? A department has multiple professors. A professor is part of a department. A department has a professor. A professor is part of multiple departments. A department has a professor. A professor is part of a department. A department has multiple professors. A professor is part of multiple departments. CONCEPT 1/1/22, 4:27 AM Sophia :: Welcome 9/20 Relationships and Cardinality 11 What could be a reason to denormalize a database? We want to increase performance for inserts, updates, and deletes. We want to eliminate repeating groups. The database may require us to value performance over storage and redundancy issues. We want to increase the number of joins between tables to optimize performance. CONCEPT Denormalization 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/ Using the WHERE clause, filter the employee table to include individuals that live in Edmonton. Identify the title of the individual listed. General Manager IT Sales Manager Andrew 1/1/22, 4:27 AM Sophia :: Welcome 10/20 Sales Support Agent CONCEPT WHERE to Filter Data 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/ Using the SELECT statement, query the track table to find the total price for all tracks that has the genre_id equal to 2. 128.7 0.99 3552.27 130 CONCEPT SUM to Add Values 14 Which of the following is a key feature of a relational database? 1/1/22, 4:27 AM Sophia :: Welcome 11/20 It ensures strong consistency such that applications should be able to read what has been written to the database immediately. It has flexible data models that allow easy storage to combine data of any structure and dynamic changes to the schema. It's designed for highly available systems that should provide a consistent, high-quality experience for all users globally. It has a broad umbrella of a variety of approaches with data storage and manipulation. CONCEPT Non-relational Databases 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 statements would query the invoice_line table to sum up the totals by multiplying the unit_price with the quantity grouped by invoice_id? SELECT track_id, SUM(quantity * unit_price) AS total FROM invoice_line ORDER BY total DESC; SELECT track_id, SUM(quantity * unit_price) AS total FROM invoice_line GROUP BY track_id ORDER BY total DESC; SELECT track_id, (quantity / unit_price) AS total FROM invoice_line 1/1/22, 4:27 AM Sophia :: Welcome 12/20 GROUP BY track_id ORDER BY total DESC; SELECT track_id, (quantity * unit_price) AS total FROM invoice_line GROUP BY track_id ORDER BY total DESC; CONCEPT Calculations in SELECT Statements 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 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; 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; 1/1/22, 4:27 AM Sophia :: Welcome 13/20 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'; 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 CONCEPT Transactions 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 statements would be valid DROP VIEW statements? DROP VIEW invoice_verification CASCADE; DROP VIEW IF EXISTS invoice_verification; 1/1/22, 4:27 AM Sophia :: Welcome 14/20 DROP VIEW CASCADE invoice_verification; DROP VIEW invoice_verification; CONCEPT DROP VIEW to Remove Views 18 Which of the following ALTER TABLE features are available in SQLite? ALTER COLUMN DROP COLUMN ADD CONSTRAINT ADD COLUMN CONCEPT Using SQLite 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/ 1/1/22, 4:27 AM Sophia :: Welcome 15/20 Identify the line of code that would generate an error in the following CREATE TABLE statement. This statement creates a table called artist that consists of the artist_id as the primary key, the first_name, and last_name. CREATE TABLE artist( artist_id INTPRIMARYKEY, first_name VARCHAR (50), last_name VARCHAR (50) ); 2 1 3 4 CONCEPT CREATE TABLE Syntax 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 correctly constructed ALTER TABLE statement to add a UNIQUE constraint to the column driver_license with the constraint name driver_license_unique on the table identification. ALTER TABLE identification ADD CONSTRAINT driver_license UNIQUE (driver_license); ALTER TABLE identification ADD CONSTRAINT driver_license UNIQUE (driver_license_unique); 1/1/22, 4:27 AM Sophia :: Welcome 16/20 ALTER TABLE identification ADD CONSTRAINT driver_license_unique UNIQUE (driver_license); ALTER TABLE identification ADD UNIQUE driver_license CONSTRAINT (driver_license_unique); CONCEPT UNIQUE to Validate Data 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/ Outfit ID: 1, Name: Rainy day outfit ID: 2, Name: Important meeting outfit ID: 3, Name: Fancy event outfit ID: 4, Name: Beach outfit Piece ID: 1, Name: Gray button-up shirt ID: 2, Name: Rainboots ID: 3, Name: Velvet pants Given the above data for an outfit generator, how many records would be included in the result set for the following query? SELECT Outfit.name, Piece.name FROM Outfit CROSS JOIN Piece; 4 9 1/1/22, 4:27 AM Sophia :: Welcome 17/20 12 7 CONCEPT Cross Joins 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/ What type of situation would you need to create or replace a view? The data in the tables have changed. On a daily basis so that the data is refreshed. The view's underlying data has to be changed to other tables. Data has been imported from other databases.. CONCEPT 1/1/22, 4:27 AM Sophia :: Welcome 18/20 CREATE OR REPLACE VIEW to Update Views 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/ Select the correctly constructed CHECK constraint to validate the signup_date column of type data to ensure that values placed into it are between 2020-01-01 and 2025-01-01. CHECK (signup_date BETWEEN '2020-01-01' OR '2025-01-01') CHECK (signup_date BETWEEN '2020-01-01' AND '2025-01-01') CHECK (signup_date BETWEEN 2020-01-01 AND 2025-01-01) CHECK (signup_date IN '2020-01-01' AND '2025-01-01') CONCEPT CHECK to Validate Data 24 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 the best example of a scenario in which a new database should be created to support the business? An organization would like to build a support ticketing system. An open-source solution has the majority of the features that the organization needs. There are some improvements that still need to be made. An organization would like to build a support ticketing system for its custom ticketing application. None of the existing ticketing applications fits the workflow of the organization. 1/1/22, 4:27 AM Sophia :: Welcome 19/20 An organization would like to create a support ticketing tracking tool that anyone can access and change. There does not need to be any versioning or tracking of who changed items as long as they are being addressed. An organization has found a support ticketing tool that they could use. It is missing some features that they require in which the third party can build and add on. CONCEPT Databases in the Real World 25 What happens if we run the following command in MySQL? UPDATE product SET price = price --10; An error is thrown due to the --. The prices in the product table for all records are incremented by 10. The product table has all of the prices set to the same value as it previously had. The prices in the product table for all records are decremented by 10. CONCEPT 1/1/22, 4:27 AM Sophia :: Welcome 20/20 Using MySQL and MariaDB About Contact Us Privacy Policy Terms of Use