Information
AI Chat

JJ 3rd Milestone

3rd Milestone of Relational Database off Sophia. Know there are other...

Course

Advanced Database (CS 204)

5 Documents

Students shared 5 documents in this course

University

Pennsylvania Highlands Community College

Academic year: 2021/2022

Uploaded by:

Anonymous Student

This document has been uploaded by a student, just like you, who decided to remain anonymous.

Pennsylvania Highlands Community College

Comments

Please sign in or register to post comments.

Related Studylists

introduction to relational databases Relational Database IRD – Soph

Preview text

UNIT 3 — MILESTONE 3

Score 20/

20/21 that's 95%

Well done! If you keep getting Milestone scores like this you'll pass the course.

20 questions were answered correctly. 1 question was answered incorrectly.

Milestone Tips

Remember that Milestones are open-book. Take the Practice Milestone before taking the Milestone. Take the Milestone at a time and in a place where you can be focused and undisturbed for the entire Milestone time limit.

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.

postgres/

Which of the following permissions are automatically granted to a user?

Access to enabling extensions

All permissions need to be granted to the user or role.

Permission to change items in the database

Access to any piece that touches the underlying system

RATIONALE

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 Report an issue with this question

Reported. Thanks for your feedback.

postgres/

Which of the following statements will grant update permissions on the customer table to all users?

GRANT UPDATE ON public to customer;

GRANT UPDATE ON customer TO ALL;

GRANT UPDATE ON 'customer' TO 'public';

GRANT UPDATE ON customer TO public;

RATIONALE

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, and using the incorrect syntax.

CONCEPT

GRANT to Assign Privileges Report an issue with this question

Reported. Thanks for your feedback.

postgres/

In the event of system failure, no transactions that were done can be undone.

RATIONALE

The durability property ensures that once a transaction is done and committed, the changes cannot be undone or lost, even if there is a system failure.

CONCEPT

ACID Properties Report an issue with this question

Reported. Thanks for your feedback.

postgres/

Which of the following scenarios reflects the atomicity property?

A vendor has 5 orders available.
A customer attempts to purchase all 5.
The system deducts it from the vendor and adds it to the customer.
The vendor now has 0 orders available.
The customer has 5 orders purchased.
The transaction is saved.
User 1 has $800 in their online account.
User 2 has $600 in their online account.
User 1 sends user 2 $100.
User 1 has $700 in their account.
User 2 has $600 in their account.
The transaction is saved.
Jeff checks his user profile and sees his email address is set to jeff@gmail.
Jeff changes his email to set it to jeff@outlook but does not click on save yet.
A customer rep checks into Jeff's account and sees jeff@gmail.
Jeff clicks on save.
Another customer rep checks Jeff's account and sees jeff@outlook.
A fairly large transaction is run to import new customers.
The import is saved to the logs.
The import starts to then save to the disk.
The disk fails midway through the update.
A new disk replaces the failed disk.
The backup is applied and the saving of the data is run from the logs.

RATIONALE

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 Report an issue with this question

Reported. Thanks for your feedback.

postgres/

Which of the following scenarios reflects the isolation property?

The product quantity starts at 200.
Transaction 1 runs to read the quantity of the product to be updated.
Transaction 2 runs in parallel to read the quantity of the product to be updated.
Transaction 1 updates the product quantity to reduce it by 100.
Transaction 2 updates the product quantity to reduce it by 5.
The product quantity ends at 195.
A fairly large update to the products table.
The update is saved to the logs.
The update starts to then save to the disk.
The system fails midway through.
When the system starts back up, the committed transactions that haven't been written to disk are written.
User 1 has $500 in their online account.
User 2 has $300 in their online account.
User 1 sends user 2 $100.
User 1 has $400 in their account.
User 2 has $300 in their account.
The transaction is reverted.

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 work well with wild cards at the start of a comparison or ranges.

CONCEPT

B-Tree Index Report an issue with this question

Reported. Thanks for your feedback.

postgres/

Which of the following statements represents a correctly structured transaction?

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'; COMMIT;

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' COMMIT

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'; COMMIT;

BEGIN;

COMMIT;

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';

RATIONALE

Common mistakes with a transaction include missing a semicolon (;) after BEGIN or after the COMMIT at the end of the statement, not having the COMMIT at the end of the statement, and not including BEGIN at the start of the statement.

CONCEPT

Transactions Report an issue with this question

Reported. Thanks for your feedback.

postgres/

Which of the following scenarios reflects the durability property?

The product quantity starts at 200.
Transaction 1 runs to read the quantity of the product to be updated.
Transaction 2 runs in parallel to read the quantity of the product to be updated.
Transaction 1 updates the product quantity to reduce it by 100.
Transaction 2 updates the product quantity to reduce it by 5.
The product quantity ends at 195.

>>

=

RATIONALE

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 Report an issue with this question

Reported. Thanks for your feedback.

postgres/

Which of the following statements would dump ONLY the table customer from the mydatabase database to backup?

pg_dump -t customer > backup

pg_dump mydatabase customer > backup

pg_dump -a customer mydatabase > backup

pg_dump -t customer mydatabase > backup

RATIONALE

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, and not including the command line options.

CONCEPT

Create a Backup Report an issue with this question

Reported. Thanks for your feedback.

postgres/

Which is an advantage of both the command line and the GUI?

We can compress the backup script.

We can back up a remote server.

We can backup the data, schema, or both at the same time.

We can use any parameters without limitation.

RATIONALE

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 Report an issue with this question

Reported. Thanks for your feedback.

postgres/

Which of the following scenarios reflect the consistency property?

Which of the following statements would create a role named database_test which gives them the ability login as a user?

CREATE ROLE database_test CREATEROLE;

CREATE ROLE database_test INHERIT;

CREATE ROLE database_test LOGIN;

CREATE ROLE database_test NOINHERIT;

RATIONALE

Common mistakes when creating roles are using quotes around the role name, not setting the correct role privileges, and using the incorrect syntax.

CONCEPT

CREATE ROLE to Create Groups Report an issue with this question

Reported. Thanks for your feedback.

postgres/

Which of the following statements would restore only the table 'customer' from the mydatabase database from backup?

psql -t customer mydatabase < backup

psql -a customer mydatabase < backup

psql mydatabase customer < backup

psql -t customer < backup

RATIONALE

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, and not including the command line options.

CONCEPT

Restore from Backup Report an issue with this question

Reported. Thanks for your feedback.

postgres/

In which of the following cases would it make the most sense to use a hash index?

SELECT *

FROM track WHERE track_id > 1 AND track_id < 15;

SELECT *

FROM album WHERE email LIKE '%t%';

SELECT *

FROM playlist WHERE playlist_id = 4;

SELECT *

FROM artist WHERE artist_id < 10;

RATIONALE

Hash indexes can only be used when we have equality comparisons. It does not work well with wild cards or any ranges.

Report an issue with this question

Reported. Thanks for your feedback.

postgres/

Which of the following statements would create a user named master_user with the password set as k489dJs9l that is valid until 2032-12-31?

CREATE USER master_user VALID UNTIL '2032-12-31' WITH PASSWORD 'k489dJs9l'

CREATE USER master_user WITH PASSWORD 'k489dJs9l' EXPIRES '2032-12-31'

CREATE USER master_user WITH PASSWORD 'k489dJs9l' VALID UNTIL '2032-12-31'

CREATE USER master_user WITH PASSWORD k489dJs9l VALID UNTIL 2032-12-

RATIONALE

Common mistakes when creating a user include adding quotes to the username, forgetting to add quotes to the password, incorrectly setting the additional parameters, and using the incorrect syntax.

CONCEPT

CREATE USER/ROLE to Add Users Report an issue with this question

Reported. Thanks for your feedback.

postgres/

Which of the following DROP INDEX statements would refuse to drop the index if any objects depend on it?

DROP INDEX CONCURRENTLY myindex;

DROP INDEX myindex RESTRICT;

DROP INDEX IF EXISTS myindex;

DROP INDEX myindex CASCADE;

RATIONALE

Common mistakes when dropping an index include not including the correct index name, using the incorrect 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 Report an issue with this question

Reported. Thanks for your feedback.

postgres/

Consider the following scenario:

Update ROLLBACK
Insert
Insert
Delete ROLLBACK
Update COMMIT

Differential backup

RATIONALE

Full backups are a full copy of the entire data set. Although they are the best protection, they are time- consuming 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 they start with a full backup and include data since the prior full backup. At most, there are only 2 backups to restore from.

CONCEPT

Backup Methods Report an issue with this question

Reported. Thanks for your feedback.

About Contact Us Privacy Policy Terms of Use

Was this document helpful?

JJ 3rd Milestone

Course: Advanced Database (CS 204)

5 Documents

Students shared 5 documents in this course

University: Pennsylvania Highlands Community College

Was this document helpful?

4/18/22, 9:33 PM

Sophia :: Welcome

https://app.sophia.org/spcc/introduction-to-relational-databases/milestone_take_feedbacks/13776201

1/19

UNIT 3 — MILESTONE 3

Score 20/21

20/21 that's 95%

Well done! If you keep getting Milestone scores like this you'll pass the course.

20 questions were answered correctly.

1 question was answered incorrectly.

Milestone Tips

Remember that Milestones are open-book.

Take the Practice Milestone before taking the Milestone.

Take the Milestone at a time and in a place where you can be focused and undisturbed for the entire

Milestone time limit.

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?

Access to enabling extensions

All permissions need to be granted to the user or role.

Permission to change items in the database

Access to any piece that touches the underlying system

RATIONALE

JJ 3rd Milestone

Advanced Database (CS 204)

Pennsylvania Highlands Community College

Comments

Students also viewed

Related documents

Related Studylists

Preview text

UNIT 3 — MILESTONE 3

Milestone Tips

RATIONALE

CONCEPT

RATIONALE

CONCEPT

RATIONALE

CONCEPT

RATIONALE

CONCEPT

CONCEPT

BEGIN

BEGIN;

BEGIN;

COMMIT;

RATIONALE

CONCEPT

>>

=

RATIONALE

CONCEPT

RATIONALE

CONCEPT

RATIONALE

CONCEPT

RATIONALE

CONCEPT

RATIONALE

CONCEPT

SELECT *

SELECT *

SELECT *

SELECT *

RATIONALE

RATIONALE

CONCEPT

RATIONALE

CONCEPT

RATIONALE

CONCEPT

JJ 3rd Milestone

Course: Advanced Database (CS 204)

University: Pennsylvania Highlands Community College

Students also viewed

Related documents