NEW
Font size
WorksheetsMTA 98-364 RESUME
Total questions: 50
Worksheet time: 48mins
John works as a Database Administrator for Bluewell Inc. The company has a SQL Server database. A table in the database has a candidate key and an attribute that is not a constituent of the candidate key. The non-key attribute depends upon the whole of the candidate key rather than just a part of it. Which of the following normal forms is represented in the scenario?
4 NF
3 NF
2 NF
1 NF
You work as a Database Designer for DataOneWorld Inc. The company has a SQL Server database. You are assigned the task of creating a data model of an enterprise based on a specific data model. The model to be created should be independent of a particular DBMS. Which of the following database designs will help you accomplish the task?
Logical database design
Conceptual database design
Physical database design
Application design
Consider the case of a fruit juice company. The company manufactures fruit juices and supplies them to wholesalers. The Database Designer creates a table named Production. The code is given below:
1.CREATE Table Production
2.(Fruit_type VarChar,
3.Fruit_name Char(20),
4.Quantity Int(3))
Which of the above-mentioned lines has an error?
Line 1
Line 2
Line 3
Line 4
John works as a Database Administrator for DataOneWorld Inc. The company has a SQL Server database. John wants to insert records in a table where the database is structured in a fixed format. Which of the following data models will he use to accomplish the task?
Object relational data model
Entity-Relationship Model
Network data model
Relational model
You manage a database named Customers, which includes a table named Orders. The Orders table is frequently queried, but only orders with a sales total of more than $1000.00 are required in the query. You want to create an index to speed up these types of queries at the same time, ensuring the index is as small as possible. What type of index should you use?
Non-clustered
Filtered
clustered
XML
Your Company is designing and developing a number of databases for a stock exchange. One of the databases will contain highly sensitive data for which high level of security will be required. Although high processing speed is one of the prime requirements of the customer, for this database, security of the data will take priority over the processing speed. It needs to be ensured that even if unauthorized access to the database is obtained, the rogue user is unable to read the data. Which of the following protection techniques will you suggest for this database?
Authentication
Encryption
Native auditing
Integrity controls
Raj has the cycle database setup for his dad’s cycle shop in India. The next step is to work with his dad to learn what operations he will need to perform with the database. These are called user requirements.
His dad needs the new application to perform these initial tasks (other requirements will be defi ned later):
• Create various inventory reports
• Produce sales reports (by cycle model, price, and so on)
• Add new inventory to the system when it arrives
• Change the cost of cycles and parts as necessary
• Remove cycles from the database when they are sold
What DML (data manipulation language) command must be used to indicate that a cycle was sold and should be removed from the Cycle table?
DELETE FROM Cycle WHERE cycle _ id = T1234
REMOVE FROM Cycle WHERE cycle _ id = T1234
ERASE FROM Cycle WHERE cycle _ id = T1234
SCENARIO: Raj has the cycle database setup for his dad’s cycle shop in India. The next step is to work with his dad to learn what operations he will need to perform with the database. These are called user requirements.
His dad needs the new application to perform these initial tasks (other requirements will be defi ned later):
• Create various inventory reports
• Produce sales reports (by cycle model, price, and so on)
• Add new inventory to the system when it arrives
• Change the cost of cycles and parts as necessary
• Remove cycles from the database when they are sold
What command is used to report on the current on-hand quantities of red cycles?
SELECT cycle _ model WHERE cycle _ color = ‘red’
SELECT * FROM Cycle WHERE cycle _ color = ‘red’
FIND * FROM Cycle WHERE cycle _ color = ‘red’
SCENARIO: Raj has the cycle database setup for his dad’s cycle shop in India. The next step is to work with his dad to learn what operations he will need to perform with the database. These are called user requirements.
His dad needs the new application to perform these initial tasks (other requirements will be defi ned later):
• Create various inventory reports
• Produce sales reports (by cycle model, price, and so on)
• Add new inventory to the system when it arrives
• Change the cost of cycles and parts as necessary
• Remove cycles from the database when they are sold
How is a new cycle added to the Cycle table in the database?
INSERT INTO Cycle (C3425, ‘Rockrider, ‘red’, 9999.00)
ADD INTO Cycle VALUES (C3425, ‘Rockrider’, ‘red’, 9999.00)
INSERT INTO Cycle VALUES (C3425, ‘Rockrider’, ‘red’, 9999.00)
SCENARIO: Raj is making excellent progress automating his dad’s cycle shop. He has reduced the amount of paperwork and provided more accurate information for his dad to maintain the current inventory. As a result of the automation, his dad has decided that he wants to use a website to advertise and sell cycles. Raj is in charge of the next phase of this exciting change to the cycle shop business. Raj immediately realizes that the current database schema needs to be changed. If they are going to allow users to buy from the internet, he needs to have photos of all their products, including cycles, accessories, and parts.
Which DDL command can Raj use to add a new field to the Cycle table to store the filename of the photo?
ALTER TABLE Cycle ADD photo _ file _ name CHAR (30) NULL
ADD photo _ file _ name TO TABLE Cycle
ALTER Cycle TABLE USING photo _ file _ name CHAR(30) NULL
SCENARIO: Raj is making excellent progress automating his dad’s cycle shop. He has reduced the amount of paperwork and provided more accurate information for his dad to maintain the current inventory. As a result of the automation, his dad has decided that he wants to use a website to advertise and sell cycles. Raj is in charge of the next phase of this exciting change to the cycle shop business. Raj immediately realizes that the current database schema needs to be changed. If they are going to allow users to buy from the internet, he needs to have photos of all their products, including cycles, accessories, and parts.
In the example in the previous question, what effect does the word NULL have when adding a new cycle to the table?
requires the user to enter a filename for the photo
does not require the user to enter a filename for the photo
automatically enters a filename for the photo
SCENARIO: Raj is making excellent progress automating his dad’s cycle shop. He has reduced the amount of paperwork and provided more accurate information for his dad to maintain the current inventory. As a result of the automation, his dad has decided that he wants to use a website to advertise and sell cycles. Raj is in charge of the next phase of this exciting change to the cycle shop business. Raj immediately realizes that the current database schema needs to be changed. If they are going to allow users to buy from the internet, he needs to have photos of all their products, including cycles, accessories, and parts.
What is the main difference between the DML command DELETE and the DDL command DROP?
They accomplish the same task; therefore, there is no difference
DELETE removes all (or a subset of) records from the table only; it does not remove the table
DROP removes all records from the table only; it does not remove the table
SCENARIO: The next step for Raj in the design of the database to streamline his father’s cycle business is to
determine the most useful relationships between the tables. He knows that he needs to create a relational
database because it will be important to conduct searches by using data in one table to fi nd additional data
in another table. Figuring out a design that best meets the needs of the cycle shop is a critical step. While
reviewing his current business requirements, he realizes that he needs to add another table to include the
suppliers of the parts for the cycles. This new table requires Raj to update the parts table to include
a foreign key to the supplier table.
What is the relationship between the supplier table and the parts table?
one-to-one
one-to-many
many-to-many
SCENARIO: The next step for Raj in the design of the database to streamline his father’s cycle business is to
determine the most useful relationships between the tables. He knows that he needs to create a relational
database because it will be important to conduct searches by using data in one table to fi nd additional data
in another table. Figuring out a design that best meets the needs of the cycle shop is a critical step. While
reviewing his current business requirements, he realizes that he needs to add another table to include the
suppliers of the parts for the cycles. This new table requires Raj to update the parts table to include
a foreign key to the supplier table.
What is a good foreign key for the relationship between the cycle table and the parts table?
part number
cycle model number
part name
SCENARIO: The next step for Raj in the design of the database to streamline his father’s cycle business is to
determine the most useful relationships between the tables. He knows that he needs to create a relational
database because it will be important to conduct searches by using data in one table to fi nd additional data
in another table. Figuring out a design that best meets the needs of the cycle shop is a critical step. While
reviewing his current business requirements, he realizes that he needs to add another table to include the
suppliers of the parts for the cycles. This new table requires Raj to update the parts table to include
a foreign key to the supplier table.
What is a suggested primary key for the accessory table?
accessory number
accessory name
accessory model
SCENARIO: High-school student Rajesh M. Patel works part-time in his dad’s cycle shop in Pune, India. Pune was the fi rst city in India to have dedicated lanes for cycles.
Raj’s dad currently keeps track of his inventory using pencil and paper. Ordering parts and accessories or
counting inventory takes several days. Raj is taking a database management class in school and he realizes that his dad’s business could benefi t greatly by implementing a database management system.
Raj needs to review a few basic concepts before he starts, and has made of list of topics he knows will be
important in the fi rst steps of the project.
Identify tables that Raj might create for the database.
a parts table, a cycle table, and an accessories table
one table for all parts, cycles, and accessories
a table for each part, each cycle, and each type of accessory (a helmet table, a biking glove
table, and so on)
SCENARIO: High-school student Rajesh M. Patel works part-time in his dad’s cycle shop in Pune, India. Pune was the fi rst city in India to have dedicated lanes for cycles.
Raj’s dad currently keeps track of his inventory using pencil and paper. Ordering parts and accessories or
counting inventory takes several days. Raj is taking a database management class in school and he realizes that his dad’s business could benefi t greatly by implementing a database management system.
Raj needs to review a few basic concepts before he starts, and has made of list of topics he knows will be
important in the fi rst steps of the project.
Identify the fields that Raj should use for column headings of the parts table.
part number, part name, cycle number, and cycle name
part number and quantity sold
part number, part name, quantity, color, and cycle identifier
SCENARIO: High-school student Rajesh M. Patel works part-time in his dad’s cycle shop in Pune, India. Pune was the fi rst city in India to have dedicated lanes for cycles.
Raj’s dad currently keeps track of his inventory using pencil and paper. Ordering parts and accessories or
counting inventory takes several days. Raj is taking a database management class in school and he realizes that his dad’s business could benefi t greatly by implementing a database management system.
Raj needs to review a few basic concepts before he starts, and has made of list of topics he knows will be
important in the first steps of the project.
What is the name of the data located at the intersection of a row and column?
field
record
variable
SCENARIO: Yuhong Li’s son, Yan, is taking a programming course in school and he asked her advice on selecting
a project for class. Yuhong suggests that Yan create a program to help organize her CD collection that she has
accumulated over the years. Yan likes the idea. He can create a database table to store all of data related to his mother’s collection and use C# or Visual Basic to create a user interface to search, add, and delete information from the table. Yan knows that defining the table fields is very mportant and selecting the right data type for each field is critical.
What data type should Yan use to store the CD label name?
char or variable char
name
integer
SCENARIO: Yuhong Li’s son, Yan, is taking a programming course in school and he asked her advice on selecting
a project for class. Yuhong suggests that Yan create a program to help organize her CD collection that she has
accumulated over the years. Yan likes the idea. He can create a database table to store all of data related to his mother’s collection and use C# or Visual Basic to create a user interface to search, add, and delete information from the table. Yan knows that defining the table fields is very mportant and selecting the right data type for each field is critical.
What data type should he use to store the number of tracks on each CD?
real number
integer
char
SCENARIO: Yuhong Li’s son, Yan, is taking a programming course in school and he asked her advice on selecting a project for class. Yuhong suggests that Yan create a program to help organize her CD collection that she has
accumulated over the years. Yan likes the idea. He can create a database table to store all of data related to his mother’s collection and use C# or Visual Basic to create a user interface to search, add, and delete information from the table. Yan knows that defining the table fields is very mportant and selecting the right data type for each field is critical.
What data type should Yan use to store the duration of the song in total seconds?
byte
integer
Boolean
SCENARIO: Yan has gathered together all of the CDs in his mother’s collection – she has more than he imagined! He has identifi ed the data fi elds he needs in his database table and the best data type for each piece of data, so he is ready to create the database tables. Yan plans to set up the table using proper ANSI SQL syntax and wants to brush up on the details before he begins.
Proper ANSI SQL syntax refers to a set of rules that:
decide if a field can contain a blank value or not
determine the size of all the data fields
govern the structure and content of statements
SCENARIO: Yan has gathered together all of the CDs in his mother’s collection – she has more than he imagined! He has identifi ed the data fi elds he needs in his database table and the best data type for each piece of data, so he is ready to create the database tables. Yan plans to set up the table using proper ANSI SQL syntax and wants to brush up on the details before he begins.
Which SQL command is used to add a new table?
CREATE TABLE table_name (column_name data type null/not null, column_name data type null/not null, and so on)
ADD TABLE table_name (column_name data type null/not null, column_name data type null/not null, and so on)
INSERT TABLE table_name (column_name data type null/not null, column_name data type null/not null, and so on)
SCENARIO: Yan has gathered together all of the CDs in his mother’s collection – she has more than he imagined! He has identifi ed the data fi elds he needs in his database table and the best data type for each piece of data, so he is ready to create the database tables. Yan plans to set up the table using proper ANSI SQL syntax and wants to brush up on the details before he begins.
Based on the information Yan has gathered, what data field can he use for a unique key to access the data in the table?
artist name
CD label name
track name
SCENARIO: Yan is learning about views in his database management class. His instructor explained that there are two approaches to creating views: using a command line language T-SQL (Transact-SQL) or by using a graphical designer. Yan wants to apply his new learning to the database project he is creating for his mother’s CD collection. He has identifi ed the following requirements for his application and will use T-SQL to create views:
• View all CDs sorted in alphabetical order
• Report on all CDs with a certain number of tracks
• Create a list of CDs by artist
• Calculate a count of all CDs
What is the best application of views and T-SQL for this project?
create separate views with the information for each request
create a new table for each of requirements in the list
add data to the existing tables when more CDs are purchased
SCENARIO: Yan is learning about views in his database management class. His instructor explained that there are two approaches to creating views: using a command line language T-SQL (Transact-SQL) or by using a graphical designer. Yan wants to apply his new learning to the database project he is creating for his mother’s CD collection. He has identifi ed the following requirements for his application and will use T-SQL to create views:
• View all CDs sorted in alphabetical order
• Report on all CDs with a certain number of tracks
• Create a list of CDs by artist
• Calculate a count of all CDs
Which of the following is an important benefit of using views?
allow the user to access the data in the tables directly
reduce the storage requirements for the application and database
use to represent summation data
SCENARIO: Yan is learning about views in his database management class. His instructor explained that there are two approaches to creating views: using a command line language T-SQL (Transact-SQL) or by using a graphical designer. Yan wants to apply his new learning to the database project he is creating for his mother’s CD collection. He has identifi ed the following requirements for his application and will use T-SQL to create views:
• View all CDs sorted in alphabetical order
• Report on all CDs with a certain number of tracks
• Create a list of CDs by artist
• Calculate a count of all CDs
Which code segment creates a view containing the title, artist, and year for all CDs with more than 10 tracks?
CREATE VIEW CD _ More _ than _ 10 AS SELECT CD _ Title, CD _ Art- ist, CD _ Year FROM CD _ Collection WHERE Tracks > 10
CREATE TABLE CD _ More _ than _ 10 AS SELECT CD _ Title, CD _ Artist, CD _ Year FROM CD _ Collection WHERE Tracks > 10
CREATE VIEW AS CD _ More _ than _ 10 FROM CD _ Collection WHERE Tracks > 10
SCENARIO: The views that Yan created using T-SQL were very useful for his mother. She can easily identify CDs that are in her collection using lists that can be sorted in a variety of ways. This is so much easier than creating piles of CDs on the living room floor!
Now that Yan has practice in creating views using T-SQL, he wants to create the same queries using a graphical designer. Practice with both approaches will certainly help him in his certifi cation exam.
How is a graphical designer such as JetSQL different from T-SQL?
Graphical designers use a command-line interface
Graphical designers are object-oriented
Graphical designers are only used for SQL Server databases
SCENARIO: The views that Yan created using T-SQL were very useful for his mother. She can easily identify CDs that are in her collection using lists that can be sorted in a variety of ways. This is so much easier than creating piles of CDs on the living room floor!
Now that Yan has practice in creating views using T-SQL, he wants to create the same queries using a graphical designer. Practice with both approaches will certainly help him in his certifi cation exam.
Create a query, identify source table(s) and/or query(s), select fields, set criteria, run, and display
Create a query, identify data fields, select table(s), set criteria, run, and display
Create a query, identify criteria, select table(s), select fields, run, and display
SCENARIO: The views that Yan created using T-SQL were very useful for his mother. She can easily identify CDs that are in her collection using lists that can be sorted in a variety of ways. This is so much easier than creating piles of CDs on the living room floor!
Now that Yan has practice in creating views using T-SQL, he wants to create the same queries using a graphical designer. Practice with both approaches will certainly help him in his certifi cation exam.
What additional criteriona must be specified to retrieve all CDs in alphabetical
order?
ORDER BY
WHERE
SELECT
SCENARIO: After having gone through the process of creating the CD collection database for his mother, Yan realizes that this type of structure can be used for many other inventory databases. He has discovered that some common functionality is available in predefi ned SQL functions. By utilizing these built-in, ready-to-use functions, he can increase his productivity and spend his time creating any other necessary user-defi ned functions. Yan has also learned to distinguish between aggregate and scalar functions.
What aggregate function can Yan use to calculate the total number of CDs in the CD collection database?
SUM(column name)
COUNT(column name)
AVG(column name)
SCENARIO: After having gone through the process of creating the CD collection database for his mother, Yan realizes that this type of structure can be used for many other inventory databases. He has discovered that some common functionality is available in predefi ned SQL functions. By utilizing these built-in, ready-to-use functions, he can increase his productivity and spend his time creating any other necessary user-defi ned functions. Yan has also learned to distinguish between aggregate and scalar functions.
Yan is not quite sure how scalar functions work. Which of the following is a scalar function?
FIRST(column name) returns the first field in the specified column
SUM(column name) returns the total of all values in the column
UCASE(column name) returns the value of the field in all uppercase letters
SCENARIO: After having gone through the process of creating the CD collection database for his mother, Yan realizes that this type of structure can be used for many other inventory databases. He has discovered that some common functionality is available in predefi ned SQL functions. By utilizing these built-in, ready-to-use functions, he can increase his productivity and spend his time creating any other necessary user-defi ned functions. Yan has also learned to distinguish between aggregate and scalar functions.
How is a stored procedure invoked?
RUN (procedure name, input values)
EXECUTE (procedure name, input values)
PERFORM (procedure name, input values)
SCENARIO: Katarina Larsson has always been very athletic; she enjoys kayaking, hiking, swimming, cycling, and more. So, Katarina was thrilled when she was hired as an intern in the information systems division of Adventure Works. Katarina is studying database management in college, so this internship is a valuable experience with the benefi ts of experiencing athletic adventures in Nova Scotia, Canada. Part of her new position includes writing SQL queries to analyze data about current trends in the recreational activities on the island enjoyed by residents and tourists. Katarina is ready to start creating reports on the following activities:
• hiking
• whale watching
• kayaking
• camping
• golfing
Which command would ensure that return visitors are only counted once when running a query against the reservation system?
SELECT ONLY
SELECT DISTINCT
SELECT UNIQUE
SCENARIO: Katarina Larsson has always been very athletic; she enjoys kayaking, hiking, swimming, cycling, and more. So, Katarina was thrilled when she was hired as an intern in the information systems division of Adventure Works. Katarina is studying database management in college, so this internship is a valuable experience with the benefi ts of experiencing athletic adventures in Nova Scotia, Canada. Part of her new position includes writing SQL queries to analyze data about current trends in the recreational activities on the island enjoyed by residents and tourists. Katarina is ready to start creating reports on the following activities:
• hiking
• whale watching
• kayaking
• camping
• golfing
For some of the reports it will be useful to see the results in alphabetical order. Which command will produce an alphabetical list?
ORDER BY column _ name (either ASC or DESC)
SORT BY column _ name (either ASC or DESC)
ARRANGE BY column _ name (either ASC or DESC)
SCENARIO: Katarina Larsson has always been very athletic; she enjoys kayaking, hiking, swimming, cycling, and more. So, Katarina was thrilled when she was hired as an intern in the information systems division of Adventure Works. Katarina is studying database management in college, so this internship is a valuable experience with the benefi ts of experiencing athletic adventures in Nova Scotia, Canada. Part of her new position includes writing SQL queries to analyze data about current trends in the recreational activities on the island enjoyed by residents and tourists. Katarina is ready to start creating reports on the following activities:
• hiking
• whale watching
• kayaking
• camping
• golfing
A truth table is helpful in visualizing the results of logical operators. When comparing two fields, which condition always results in TRUE?
the AND operator and only one field = TRUE
the OR operator and at least one field = TRUE
the AND operator and at least one field = FALSE
SCENARIO: Nova Scotia is a beautiful location and Katrina is enjoying a variety of outdoor activities in her free time. She loves her new job at Adventure Works because it is challenging and she is learning a great deal! She is three weeks into her internship and realizes that by applying some complex queries to the recreational trends database she will be able to produce some very enlightening reports. She decides to take some time to review the concepts for subqueries (predicate, scalar, and table), UNIONS, JOINS, and INTERSECTS.
Which statement best defines a predicate subquery?
returns a single value; can be used in CASE expressions, WHERE clauses, ORDER BY, and SELECT
returns a table based on the queries nested in the FROM clause
uses extended logical constructs in the WHERE clause using AND, OR, LIKE, BETWEEN, AS, and TOP
SCENARIO: Nova Scotia is a beautiful location and Katrina is enjoying a variety of outdoor activities in her free time. She loves her new job at Adventure Works because it is challenging and she is learning a great deal! She is three weeks into her internship and realizes that by applying some complex queries to the recreational trends database she will be able to produce some very enlightening reports. She decides to take some time to review the concepts for subqueries (predicate, scalar, and table), UNIONS, JOINS, and INTERSECTS.
What is the difference between UNION and JOIN?
UNION combines the results of two SQL queries when there is the same number of columns and data types; JOIN returns rows when there is at least one column match.
UNION combines the results of two SQL queries when there is at least one column match; JOIN returns rows when there is the same number of columns and data types.
UNION only returns rows that appear in both tables; JOIN returns rows when there is at least one column match.
SCENARIO: Nova Scotia is a beautiful location and Katrina is enjoying a variety of outdoor activities in her free time. She loves her new job at Adventure Works because it is challenging and she is learning a great deal! She is three weeks into her internship and realizes that by applying some complex queries to the recreational trends database she will be able to produce some very enlightening reports. She decides to take some time to review the concepts for subqueries (predicate, scalar, and table), UNIONS, JOINS, and INTERSECTS.
When should the INTERSECT query be used?
to find all rows from the left table even if there are no matches
to return all rows that appear in both tables, similar to a Boolean OR
to return only the rows that appear in both tables, similar to a Boolean AND
SCENARIO: Katarina gained a better understanding of the database layout through her reporting project at Adventure Works. The database manager for the team has given her a new assignment. She will be responsible for writing the SQL stored procedures that can be used by the programmers to insert, update, and delete data from the database. Adventure Works has gathered lots of new data from tourists about their preferences for recreational activities in Nova Scotia. Katarina’s fi rst task is to insert new rows into the database to store this data. Katarina wants to refresh her memory on how to effi ciently and correctly insert data into a database before she attempts this very important task.
When designing the INSERT SQL, what happens if data is missing for a particular column?
The INSERT statement returns a syntax error
The INSERT statement uses the default value for the column.
The INSERT statement inserts a NULL value in the column.
SCENARIO: Katarina gained a better understanding of the database layout through her reporting project at Adventure Works. The database manager for the team has given her a new assignment. She will be responsible for writing the SQL stored procedures that can be used by the programmers to insert, update, and delete data from the database. Adventure Works has gathered lots of new data from tourists about their preferences for recreational activities in Nova Scotia. Katarina’s fi rst task is to insert new rows into the database to store this data. Katarina wants to refresh her memory on how to effi ciently and correctly insert data into a database before she attempts this very important task.
How can Katarina set up the INSERT to allow the programmer to copy rows from other tables?
She can use the INSERT INTO with a SELECT . . . FROM clause.
This cannot be done with an INSERT command; she must use a JOIN.
She can use row value constructors.
What is the correct syntax for inserting multiple rows at one time?
INSERT INTO table (column1, val1a), (column2, val2a);
INSERT (column1, column2) VALUES (val1a, val1b), (val2a, val2b)INTO table;
INSERT INTO table (column1, column2) VALUES (val1a, val1b), (val2a, val2b);
SCENARIO: Because of her extensive work in adding data and creating reports from the Adventure Works database on recreational activities, Katarina has a better understanding of the company’s database organization. The database manager for the team is pleased with her progress and has given her a new assignment. She will be responsible for writing the SQL stored procedures that will be used by the developers to insert, update, and delete data from the database.
The developer wants to update all records in a database to reflect an increase in the provincial value-added tax from 8 percent to 10 percent. Which of the following is the correct syntax?
UPDATE RENTALS SET value _ added _ tax = .10;
SET sales _ tax _ rate = .10 IN RENTALS;
UPDATE sales _ tax _ rate = .10 IN RENTALS;
SCENARIO: Because of her extensive work in adding data and creating reports from the Adventure Works
database on recreational activities, Katarina has a better understanding of the company’s database organization.
The database manager for the team is pleased with her progress and has given her a new assignment. She will
be responsible for writing the SQL stored procedures that will be used by the developers to insert, update, and
delete data from the database.
One of the developers mentioned that sometimes he needs to update data in one table from another table. Which of the following is the correct syntax for this type of update?
UPDATE SET kayak = RENTALS.kayak + EQUIPMENT.kayak
FROM RENTALS, EQUIPMENT;
UPDATE RENTALS SET RENTALS.kayak + EQUIPMENT.kayak;
UPDATE RENTALS SET kayak = RENTALS.kayak + EQUIPMENT.kayak FROM RENTALS, EQUIPMENT;
SCENARIO: Katarina learned a great deal about the Adventure Works database in her last few assignments and is ready to fi nish her assignment of writing the SQL stored procedures that can be used by the programmers to insert, update, and delete data from the database.
The last task in this assignment is for Katarina to use SQL to delete existing data in the database. The procedures must allow for the deletion of data from single or multiple tables and these transactions must ensure data and referential integrity.
What happens if a WHERE statement is not included with the DELETE statement?
The entire table is deleted
The data from the table is deleted
The user receives a syntax error
SCENARIO: Katarina learned a great deal about the Adventure Works database in her last few assignments and is ready to fi nish her assignment of writing the SQL stored procedures that can be used by the programmers to insert, update, and delete data from the database.
The last task in this assignment is for Katarina to use SQL to delete existing data in the database. The procedures must allow for the deletion of data from single or multiple tables and these transactions must ensure data and referential integrity.
How are transactions useful when updating/deleting/adding records to a database?
They allows multiple statements to be grouped together to avoid data integrity errors
They count the number of changes to records in the table
They restrict updates to only allow one statement to execute at a time
SCENARIO: The database of Epsilon Pi Tau alumni is progressing nicely! Natasha has applied the concepts of normalization to reduce storage requirements and simplify query and update procedures. The result of putting the database into Third Normal Form was four separate tables: demographic, zip code, degree, and donation. The next step is to create primary, foreign, and composite keys for each table. This step will ensure a high level of data integrity and that the information is consistent and usable.
Identify the best primary key for the demographic table:
last name
alumni ID (auto-generated by the system)
last name plus first name
SCENARIO: The database of Epsilon Pi Tau alumni is progressing nicely! Natasha has applied the concepts of normalization to reduce storage requirements and simplify query and update procedures. The result of putting the database into Third Normal Form was four separate tables: demographic, zip code, degree, and donation. The next step is to create primary, foreign, and composite keys for each table. This step will ensure a high level of data integrity and that the information is consistent and usable.
Which of the following is an example of a foreign key?
zip code
year of graduation
last name
SCENARIO: Recently there has been a great deal of media coverage about companies losing the records of customers and clients through a variety of security breaches. The Humongous Insurance Company does not want to see its name in the headlines, so offi cers decided to complete an internal audit to identify any possible security risks in their information systems. The team of analysts is very familiar with possible areas of risk such as physical security, internal security, and external security. According to the auditors, the company has a sound security plan to ensure data integrity by identifying users and the actions they can perform, and to protect data from hackers. But other aspects need to be considered in a comprehensive security plan.
What should be included in the physical security plan?
location of the stored user accounts and passwords
location of the database administrators
location of servers with restricted access
SCENARIO: Recently there has been a great deal of media coverage about companies losing the records of customers and clients through a variety of security breaches. The Humongous Insurance Company does not want to see its name in the headlines, so offi cers decided to complete an internal audit to identify any possible security risks in their information systems. The team of analysts is very familiar with possible areas of risk such as physical security, internal security, and external security. According to the auditors, the company has a sound security plan to ensure data integrity by identifying users and the actions they can perform, and to protect data from hackers. But other aspects need to be considered in a comprehensive security plan.
Which of the following is not an example of a security attack?
applying roles to grant access
privilege escalation
SQL injection
