Which of the following is a benefit of using a DBMS?
They provide full security to data using private/public key encryption
They create automatic backups
They help create an environment for end users to have access to more data
They provide seamless Internet access to database data
The hierarchical database model is based on a ____.
tree structure
lack of a parent segment
lack of a child segment
matrix
Because an RDBMS hides the system's complexity from the user/designer, ____.
it does not exhibit data independence
it does not exhibit structural independence
data management is easier
data management is very difficult
The ERD is used to graphically represent the ____ database model.
condensed
physical
logical
conceptual
The property of ____ enables an entity subtype to inherit the attributes and relationships of the supertype.
subtype discriminator
inheritance
specialization hierarchy
entity supertype
At the implementation level, the supertype and its subtype(s) depicted in the specialization hierarchy maintain a ____ relationship.
none
1:1
1:M
M:N
Which command would be used to delete the table row where the P_Code = '2238/QPD'?
DELETE FROM PRODUCT WHERE P_CODE = '2238/QPD';
REMOVE FROM PRODUCT WHERE P_CODE = '2238/QPD';
ERASE FROM PRODUCT WHERE P_CODE = '2238/QPD';
ROLLBACK FROM PRODUCT WHERE P_CODE = '2238/QPD';
Which command is used to select partial table contents?
SELECT <column(s)> FROM <Table name> WHERE <Item>;
LIST <column(s)> FROM <Table name> WHERE <Conditions>;
SELECT <column(s)> FROM <Table name> WHERE <Conditions>;
LIST<column(s)> FROM <Table name> WHERE <Item>;
Which query will output the table contents when the value of V_CODE is equal to 21344?
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <> 21344;
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE <= 21344;
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE = 21344;
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT WHERE V_CODE => 21344;
What is the command used to list the P_CODE, P_DESCRIPT, P_INDATE, and P_PRICE fields from the Product table in ascending order by P_PRICE?
SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT SEQUENCE BY P_PRICE;
SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT LIST BY P_PRICE;
SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ORDER BY P_PRICE;
SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE FROM PRODUCT ASCENDING BY P_PRICE;
The basic SQL aggregate function that gives the total of all values for a selected attribute in a given column is ____.
COUNT
MIN
MAX
SUM
____ is used to tally the number of specific "values" of an attribute.
TOTAL
ADD
UPDATE tablename ***** [WHERE conditionlist]; What command replaces the ***** in the above statement?
SET columnname = expression
columnname = expression
expression = columnname
LET columnname = expression
When using a(n) ____ join, only rows that meet the given criteria are returned.
full
inner
outer
set
What is the syntax for a left outer join?
SELECT column-list FROM table1 OUTER JOIN table2 LEFT WHERE join-condition
SELECT column-list FROM table1 LEFT [OUTER] JOIN table2 ON join-condition
SELECT column-list WHERE LEFT table1 = table 2
SELECT column-list FROM table1 LEFT table2 [JOIN] WHERE join-condition
In subquery terminology, the first query in the SQL statement is known as the ____ query.
left
base
In a subquery, which query is executed first?
Leftmost
Rightmost
Innermost
Outermost
Creating the conceptual design, selecting DBMS software, creating the logical design, and creating the physical design are part of the ____ phase.
database initial study
database design
implementation and loading
testing and evaluation
Which of the conceptual design steps defines entities, attributes, and relationships?
Database analysis and requirements
Entity relationship modeling and normalization
Data model verification
Distributed database design
What is the primary objective of database design?
To create the most efficient database possible
To create complete, normalized, non-redundant, and fully integrated conceptual, logical, and physical database models
To create the fastest queries
To be able to add data quickly
The most common algorithms for concurrency control are locks, ____ stamping, and optimistic methods.
date
time
hour
minute
____ requires that all operations of a transaction be completed.
Specificity
Atomicity
Durability
Time stamping
The ____ establishes the order in which the operations within concurrent transactions are executed.
transaction log
timer
lock manager
scheduler
When is the implicit beginning of a transaction?
When the database is started
When a table is accessed for the first time
When the first SQL statement is encountered
When the COMMIT command is issued
A(n) ____ phase in a two-phase lock is when a transaction releases all locks and cannot obtain any new lock.
growing
shrinking
locking
unlocking
What is a consistent database?
One in which all tables have foreign keys
One in which all data integrity constraints are satisfied
One in which all tables are normalized
One in which all SQL statements only update one table at a time
A transaction acquires a ____ prior to data access.
grain
timestamp
lock
key
Changes are permanently applied to the database during the ____ phase of a transaction.
commit
write
input
output
The phenomenon of ____ occurs when two transactions, T1 and T2, are executed concurrently and the first transaction (T1) is rolled back after the second transaction (T2) has already accessed the uncommitted data—thus violating the isolation property of transactions.
lost updates
uncommitted data
transaction failure
inconsistent retrieval
____ occur when a transaction calculates some summary (aggregate) functions over a set of data while other transactions are updating the data.
Lost updates
Uncommitted data
Transaction failures
Inconsistent retrievals
The scheduler facilitates data ____ to ensure that two transactions do not update the same data element at the same time.
durability
isolation
atomicity
serializability
Which is not a DML statement?
CREATE TABLE
SELECT
INSERT
DELETE
What feature is a DDBMS disadvantage?
Data is located near the “greatest demand” site
Growth facilitation
Reduced operating cost
Lack of standards
What feature is a DDBMS advantage?
Greater difficulty in managing the data environment
Less danger of a single-point failure
Increased training cost
Complexity of management and control
____ distributed database systems integrate different types of centralized DBMSs over a network.
Homogeneous
Heterogeneous
Fully homogeneous
Fully heterogeneous
____ transparency allows a transaction to update data at several network sites.
Transaction
Distribution
Failure
Performance
____ transparency ensures that the system will continue to operate in the event of a node failure.
The ____ contains the description of the entire database as seen by the database administrator.
distributed global dictionary
distributed data dictionary
distributed global schema
distributed data schema
____ query optimization means that the DDBMS finds the most cost-effective access path without user intervention.
Static
Dynamic
Automatic
Commit
____ query optimization takes place at execution time.
Manual
A ____ based query optimization algorithm uses statistical information about the database.
statistically
dynamic
rule
manual
Data ____ refers to the storage of data copies at multiple sites served by a computer network.
replication
fragmentation
persistence
optimization
To provide better performance, some OLAP systems merge the data warehouse and data mart approaches by storing small extracts of the data warehouse at end user ____.
workstations
queries
schemas
servers
____ makes a copy of a table and places it in a different location, to improve access time.
Periodicity
Aggregation
Partitioning
Replication
The ____ schema creates the near equivalent of a multidimensional database schema from the existing relational database.
star
square
OLAP
ROLAP
Using multidimensional jargon, the ability to focus on slices of the cube to perform a more detailed analysis is known as ____.
star cuts
slice and dice
microscoping
TPL processing
A ____ is a uniquely identifiable object that allows a given person to log on to the database.
user
role
profile
manager
Which DDL statement should be used to enforce referential integrity?
CREATE TRIGGER AFTER GRANT
CREATE TRIGGER BEFORE UPDATE
CREATE TRIGGER BEFORE DROP
CREATE TRIGGER AFTER SELECT
Use the given code to answer the following question:
SELECT branchNo, COUNT(staffNo) AS myCount, SUM (salary) AS mySum FROM staff, GROUP BY branchNo ORDER By branchNo;
What does this code do?
Orders the staff table by the ascending order of branchNo
Orders the staff by ascending order of salary
Finds the number of staff and the sum of their salaries in each branch
Finds the total number of branches
Use the give DDL statement to answer the following question: CREATE ASSERTION PhysicianNotOverbooked CHECK (NOT EXISTS (SELECT physician_number FROM scheduled_exams GROUP By physician_number HAVING COUNT (*) > 20));
Which action is this statement performing?
Creating a trigger
Creating a stored procedure
Creating an index
Creating a constraint
Which DCL Command gives Manager the privilege to SELECT on column AccountBalance of the table Customer?
GRANT SELECT (Customer) ON AccountBalance TO Manager;
GRANT SELECT (AccountBalance) ON Customer TO Manager;
PERMIT Manager SELECT ON Customer (AccountBalance);
ALLOW Manager SELECT AccountBalance FROM Customer;
Which statement produces a PL/SQL stored procedure?
INSERT OR SET PROCEDURE ProcessReport AS BEGIN SELECT * FROM sales END
CREATE OR REPLACE PROCEDURE ProcessReport AS BEGIN SELECT * FROM sales END
ADD OR UPDATE PROCEDURE ProcessReport AS BEGIN SELECT * FROM sales END
INSERT OR UPDATE PROCEDURE ProcessReport AS BEGIN SELECT * FROM sales END