DBMS FINAL QUIZ QUESTIONS

What will be returned when the following SQL query is executed? Select driver_no, count(*) as num_deliveries from deliveries group by driver_no having count(*) > 2; 1 a listing of all drivers who made more than 2 deliveries as well as a count of the number of deliveries 2 A listing of all drivers 3 A listing of the number of deliveries greater than 2 4 A listing of all drivers who made more than 2 deliveries

1

A candidate key must satisfy all of the following conditions EXCEPT: 1 the key must uniquely identify the row. 2 the key must indicate the row's position in the table. 3 the key must be nonredundant. 4 each nonkey attribute is functionally dependent upon it.

2

Because applications are often developed independently in file processing systems: 1 the data is always non-redundant 2 unplanned duplicate data files are the rule rather than the exception. 3 data can always be shared with others 4 there is a large volume of file I/O.

2

Data modeling may be the most important part of the systems development process because: 1 the data in a system are generally less complex than the processes and play a central role in development. 2 data characteristics are important in the design of programs and other system components. 3 it is the easiest. 4 limited program maintenance

2

The logical representation of an organizations's data is called a(n): 1 database model 2 entity-relationship model 3 relationship systems design 4 database entity diagram

2

What will be returned when the following SQL statement is executed? Select driver_no, count(*) as num_deliveries from deliveries where state = 'MA' group by driver_no; 1 A listing of all drivers who made deliveries to state = 'MA', sorted by driver number 2 A listing of each driver who made deliveries to state = 'MA' as well as the number of deliveries that each driver has made to that state 3 A count of all of the deliveries made to state = 'MA' by all drivers 4 A listing of driver numbers

2

The following code would include: SELECT Customer_T.CustomerID, Order_T.CustomerID, CustomerName, OrderID FROM Customer_T, Order_T WHERE Customer_T.CustomerID = Order_T. CustomerID; 1 all rows of the Order_T Table regardless of matches with the Customer_T Table. 2 all rows of the Customer_T Table regardless of matches with the Order_T Table. 3 only rows that match both Customer_T and Order_T Tables. 4 only rows that don't match both Customer_T and Order_T Tables.

3

Which of the following finds all groups meeting stated conditions? 1 SELECT 2 WHERE 3 HAVING 4 FIND

3

A business analyst has the privilege to view and update a particular schema. Which of the following SQL statements will change privileges such that he/she cannot update anymore? 1 Drop user businessanalyst@localhost 2 Delete update privilege on schema 3 Withdraw update privilege from businessanalyst@localhost 4 Revoke update on schema from businessanalyst@localhost

4

The last part of an SQL query to be read is the _____________statement. 1 SELECT 2 FROM 3 WHERE 4 ORDER BY

4

Which of the following are goals of the normalization process? 1 Minimize data redundancy 2 Complicate the referential integrity constraints 3 Improve efficiency of update, insert and delete functions 4 Only A and C

4

A device to measure or detect fingerprints or signatures is called a(n) ________ device.

Biometric

The actions that must be taken to ensure data integrity is maintained during multiple simultaneous transactions are called ________ actions.

Concurrency Control

high-level organizational groups and processes that oversee data stewardship.

Data Governance

Converting data from the format of its source to the format of its destination is called:

Data transformation

Which of the following functions develop integrity controls?

Database Design

________ is a technical function responsible for database design, security, and disaster recovery.

Database administration

Getting poor data from a supplier is a(n) ________ reason for deteriorated data quality.

External Data source

A member of a subtype does NOT necessarily have to be a member of the supertype.

F

An enterprise data model describes the scope of data for only one information system. (T/F)

F

In MySQL workbench, Views are generally stored under tables.

F

The following code is an example of a Subquery. SELECT Customer_T.CustomerID, Order_T.CustomerID, CustomerName, OrderID FROM Customer_T, Order_T WHERE Customer_T.CustomerID = Order_T. CustomerID;

F

The following two SQL statements will produce different results. Select last_name, first_name from customer where state = 'MA' or state = 'NY' or state = 'NJ' or state = 'NH' or state = 'CT'; Select last_name, first_name from customer where state in ('MA','NY','NJ','NH','CT');

F

The physical structure and storage organization of the database is decided upon during the implementation phase of the systems development life cycle. (T/F)

F

There can be multivalued attributes in a relation.

F

When transforming a one-to-one relationship, a new relation is always created

F

Data that describes the properties of other data are:

Metadata

________ are anomalies that can be caused by editing data in tables.

Modification

a free source-code RBMS that provides the functionality of an SQL-compliant DBMS.

Open-source DBMS

_____________ are established between entities in a well-structured database model so that the desired information can be retrieved.

Relationships

Which of the following MySQL commands is used to recover a transaction?

Rollback

A database table is defined using the data definition language (DDL).

T

A deadlock results when two or more transactions need a common resource, but neither can proceed until the other releases the resource it wants.

T

A person is an example of an entity. (T/F)

T

A procedure is run by calling it by its name.

T

A single occurrence of an entity is called an entity instance. (T/F)

T

A well-structured relation contains minimal redundancy and allows users to manipulate the relation without errors or inconsistencies.

T

Data modeling is about documenting rules and policies of an organization that govern data. (T/F)

T

Figuring out what attributes you want in your query before you write the query will help with query writing.

T

In order to find out what customers have not placed an order for a particular item, one might use the NOT qualifier along with EXISTS.

T

Once executed, procedures and functions will be stored in the schema, which eliminates the need to execute them again each time a query is executed.

T

One property of a relation is that each attribute within a relation has a unique name.

T

Target and Sony Playstation experienced data breaches due to poor database administration.

T

The default primary key for an associative relation consists of the two primary key attributes from the other two relations.

T

The default value of autocommit in MySQL is 1

T

The user interface includes languages, menus, and other facilities by which users interact with various system components. (T/F)

T

To write a query involving a unary relationship, it is a good practice to create two tables based on the original table

T

Views can be used to improve database security.

T

When creating tables, it's important to decide which columns will allow null values before the table is created.

T

Entity clustering is a methodology for grouping one or more entity types and associated relationships into a single abstract entity type.

T`

A property or characteristic of an entity type that is of interest to the organization is call a(n):

attribute

The property by which subtype entities possess the values of all attributes of a supertype is called:

attribute inheritance

In the SQL language, the ________ statement is used to make table definitions.

create table

Including data capture controls (i.e., dropdown lists) helps reduce ________ deteriorated data problems.

data entry

________ is a component of the relational data model included to specify business rules to maintain the integrity of data when they are manipulated

data integrity

The coding or scrambling of data so that humans cannot read them is called:

encryption

An attribute in a relation of a database that serves as the primary key of another relation in the same database is called a:

foreign key

A constraint between two attributes is called a(n):

functional dependency

_______ causes two tables with a common domain to be combined into a single table or view.

join operation

The entity integrity rule states that:

no primary key attribute can be null

The process of defining one or more subtypes of a supertype and forming relationships is called:

specialization