Sophia __ Welcome milestone 3 test
pdf
keyboard_arrow_up
School
Southern New Hampshire University *
*We aren’t endorsed by this school
Course
CS-210
Subject
Information Systems
Date
Feb 20, 2024
Type
Pages
29
Uploaded by Matthewdbills
1
16/21
that's 76%
RETAKE
16 questions were answered correctly
.
5 questions were answered incorrectly
.
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/
Consider the following scenario:
1. Update
ROLLBACK
2. Insert
3. Insert
COMMIT
4. Delete
5. Update
COMMIT
In this scenario, which statements would be saved to the
database, assuming these are all in a single transaction?
●
●
1, 2, 3, 4, 5
●
4, 5
UNIT 3 — MILESTONE 3
16/21
CONCEPT
→
COMMIT and ROLLBACK to Manage Changes
2
RATIONALE
In a transaction, if a ROLLBACK statement is run, all statements
up to the prior COMMIT or ROLLBACK statement will be
reverted. If a COMMIT statement is run, all statements up to the
prior ROLLBACK or COMMIT statement will be saved.
Report an issue with this question
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 atomicity property?
●
2, 3, 4, 5
●
2, 3
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.
UNIT 3 — MILESTONE 3
16/21
●
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. 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.
UNIT 3 — MILESTONE 3
16/21
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
Atomicity
3
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.
Report an issue with this question
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 role named
database_admin which gives them the ability to create
databases?
●
●
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.
●
CREATE ROLE database_admin
CREATEROLE;
UNIT 3 — MILESTONE 3
16/21
CONCEPT
→
CREATE ROLE to Create Groups
4
RATIONALE
Common mistakes when creating roles are using quotes
around the role name, not setting the correct role privileges,
and using the incorrect syntax.
Report an issue with this question
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 unique about a superuser compared to a user?
●
●
CREATE ROLE database_admin
NOINHERIT;
●
CREATE ROLE database_admin
INHERIT;
CREATE ROLE database_admin
CREATEDB;
UNIT 3 — MILESTONE 3
16/21
CONCEPT
→
Superusers
5
RATIONALE
A superuser role is one that is able to bypass all permission
checks so they are not even verified.
Report an issue with this question
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/
●
●
A superuser has their
permissions checked but passes
all tests.
A superuser has administrative
privileges that users can't have.
●
A superuser can bypass all
permission checks.
●
A superuser is the same as a
regular user but with all
permissions granted.
UNIT 3 — MILESTONE 3
16/21
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
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'
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';
COMMIT;
UNIT 3 — MILESTONE 3
16/21
CONCEPT
→
Transactions
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.
Report an issue with this question
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';
UNIT 3 — MILESTONE 3
16/21
6
CONCEPT
→
DROP INDEX to Remove Indexes
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 refuse to
drop the index if any objects depend on it?
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.
●
●
DROP INDEX IF EXISTS myindex;
DROP INDEX myindex RESTRICT;
●
DROP INDEX CONCURRENTLY
myindex;
●
DROP INDEX myindex CASCADE;
UNIT 3 — MILESTONE 3
16/21
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
7
CONCEPT
→
Application Security
Report an issue with this question
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 which type of process between the user input and
dynamically created statements can help reduce the number of
SQL injection attacks?
RATIONALE
By filtering the user input, we can ensure that we're checking
for any special characters that should not be included.
Report an issue with this question
●
●
Coding
Filtering
●
Posting
●
Construction
UNIT 3 — MILESTONE 3
16/21
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 is an advantage of both the command line and the GUI?
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.
●
●
We can restore a remote
database.
●
We can use any parameters
without limitation.
●
We can encode the backup file to
another format.
We can backup multiple
databases at once.
UNIT 3 — MILESTONE 3
16/21
CONCEPT
→
Backups: Command Line vs. GUI
9
Report an issue with this question
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 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.
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. Jeff checks his user profile and
sees his email address is set to
jeff@gmail.com.
2. Jeff changes his email to set it
to jeff@outlook.com but does not
click on save yet.
3. A customer rep checks into
UNIT 3 — MILESTONE 3
16/21
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
RATIONALE
The consistency property ensures that a transaction takes the
database from one consistent state to another consistent state.
If any transaction part violates an integrity constraint, the entire
transaction should be rolled back.
Jeff's account and sees
jeff@gmail.com.
4. Jeff clicks on save.
5. Another customer rep checks
Jeff's account and sees
jeff@outlook.com.
●
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 0 orders
purchased.
5. The transaction is saved.
●
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.
UNIT 3 — MILESTONE 3
16/21
CONCEPT
→
Consistency
10
Report an issue with this question
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 dump ONLY the table
customer from the mydatabase database to backup.sql?
RATIONALE
Common mistakes when backing up a database using the
command line include using the wrong file redirect operator,
●
●
pg_dump -a customer
mydatabase > backup.sql
●
pg_dump mydatabase customer
> backup.sql
●
pg_dump -t customer >
backup.sql
pg_dump -t customer
mydatabase > backup.sql
UNIT 3 — MILESTONE 3
16/21
CONCEPT
→
Create a Backup
11
using the incorrect syntax, not using the right order of
statements, and not including the command line options.
Report an issue with this question
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?
●
●
All SQL requests of a transaction
must be completed; if not, the
transaction must be aborted.
●
Data used in one transaction
cannot be used in another
transaction until the first
transaction is completed.
Once transaction changes are
done and saved, they cannot be
lost.
●
When a transaction is completed,
the database must be in a
UNIT 3 — MILESTONE 3
16/21
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
ACID Properties
12
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.
Report an issue with this question
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?
●
consistent state.
●
&&
●
>>
●
@>
=
UNIT 3 — MILESTONE 3
16/21
CONCEPT
→
Index Overview
13
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.
Report an issue with this question
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 sequences of statements would ensure
that Jerry has the privileges of the roles of sales and associate?
●
●
CREATE ROLE associate INHERIT;
CREATE ROLE sales INHERIT;
GRANT sales to jerry;
GRANT associate to jerry;
●
CREATE ROLE associate
NOINHERIT;
CREATE ROLE sales NOINHERIT;
GRANT associate to jerry;
GRANT associate to sales;
UNIT 3 — MILESTONE 3
16/21
CONCEPT
→
GRANT to Assign Users
14
RATIONALE
Common mistakes when assigning roles include not identifying
which roles have INHERIT or NOINHERIT privileges, and not
ensuring that the user has all of the roles allocated to the user.
Report an issue with this question
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 which of the following cases would it make the most sense to
use a hash index?
●
CREATE ROLE associate
NOINHERIT;
CREATE ROLE sales INHERIT;
GRANT sales to jerry;
GRANT associate to sales;
●
CREATE ROLE associate
NOINHERIT;
CREATE ROLE sales NOINHERIT;
GRANT sales to jerry;
GRANT associate to sales;
UNIT 3 — MILESTONE 3
16/21
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
Hash Index
15
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
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.
●
●
SELECT *
FROM customer
WHERE customer_id > 10 AND
customer_id < 20;
SELECT *
FROM track
WHERE media_type_id = 1;
●
SELECT *
FROM album
WHERE artist_id < 5;
●
SELECT *
FROM employee
WHERE address LIKE '%i%';
UNIT 3 — MILESTONE 3
16/21
https://postgres.sophia.org/
Which of the following scenarios reflects the durability
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. 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
UNIT 3 — MILESTONE 3
16/21
CONCEPT
→
Durability
16
RATIONALE
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.
Report an issue with this question
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/
●
identifies that the total amounts
before and after the transactions
are not maintained.
5. The transaction is reverted.
●
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.
UNIT 3 — MILESTONE 3
16/21
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
Restore from Backup
17
Which of the following statements would only restore the
schema, but not the data, of the database mydatabase from
backup.sql?
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.
Report an issue with this question
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
●
pg_restore -s mydatabase <
backup.sql
●
pg_restore -a mydatabase <
backup.sql
●
pg_restore -d mydatabase <
backup.sql
●
pg_restore mydatabase <
backup.sql
UNIT 3 — MILESTONE 3
16/21
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
B-Tree Index
this assessment.
https://postgres.sophia.org/
In which of the following cases would it make the most sense to
use a b-tree index?
RATIONALE
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.
●
SELECT *
FROM employee
WHERE title LIKE '%IT%';
SELECT *
FROM invoice
WHERE invoice_id > 10 AND
invoice_id < 200;
●
SELECT *
FROM customer
WHERE email LIKE '%gmail%';
●
SELECT *
FROM customer
WHERE fax LIKE '%7070';
UNIT 3 — MILESTONE 3
16/21
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
18
Report an issue with this question
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 require no more than 2 backup
sets?
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
●
Differential and incremental
backup
●
Differential and full backup
●
Incremental and full backup
●
Full backup and nightly backup
UNIT 3 — MILESTONE 3
16/21
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
Backup Methods
19
since the prior full backup. At most, there are only 2 backups to
restore from.
Report an issue with this question
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 update permissions
on the customer table to all users?
RATIONALE
●
●
GRANT UPDATE ON customer
TO ALL;
GRANT UPDATE ON customer
TO public;
●
GRANT UPDATE ON public to
customer;
●
GRANT UPDATE ON 'customer'
TO 'public';
UNIT 3 — MILESTONE 3
16/21
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
GRANT to Assign Privileges
20
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.
Report an issue with this question
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 login
role named temporary_user with the password set as
changeme that is valid until 2025-05-01?
●
CREATE ROLE temporary_user
LOGIN
PASSWORD 'changeme'
VALID UNTIL '2025-05-01'
●
CREATE ROLE temporary_user
LOGIN
PASSWORD 'changeme'
EXPIRES '2025-05-01'
UNIT 3 — MILESTONE 3
16/21
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
CREATE USER/ROLE to Add Users
21
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.
Report an issue with this question
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 isolation property?
●
●
CREATE ROLE temporary_user
LOGIN
PASSWORD changeme
VALID UNTIL 2025-05-01
●
CREATE ROLE temporary_user
LOGIN
VALID UNTIL '2025-05-01'
PASSWORD 'changeme'
1. A vendor has 5 orders
available.
UNIT 3 — MILESTONE 3
16/21
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
●
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.
●
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 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.
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. Jeff checks his user profile and
sees his email address is set to
UNIT 3 — MILESTONE 3
16/21
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
CONCEPT
→
Isolation
RATIONALE
The isolation property ensures that the data 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.
Report an issue with this question
jeff@gmail.com.
2. Jeff changes his email to set it
to jeff@outlook.com but does not
click on save yet.
3. A customer rep checks into
Jeff's account and sees
jeff@gmail.com.
4. Jeff clicks on save.
5. Another customer rep checks
Jeff's account and sees
jeff@outlook.com.
About
Contact Us
Privacy Policy
Cookie Policy
Terms of Use
Your Privacy Choices
© 2024 SOPHIA Learning, LLC. SOPHIA is a registered trademark of SOPHIA Learning, LLC.
UNIT 3 — MILESTONE 3
16/21
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help