CIW DatabaseSpecialistDesign - Nicole Nguyen | Library

1

Consider the relations shown in the exhibit. Due to restructuring, the sales department has been eliminated and the employees working in that department have been dismissed. All ID information is stored as integers. Which SQL statement would be used to return a relation with all information for the employees who have been dismissed?

SELECT * FROM Employee;

SELECT * FROM Employee WHERE Dept_ID = 022;

SELECT ID, Last_Name FROM Employee; WHERE ID = 0004;

1

Consider the following relation definition: STUDENT( Student_Number: integer NOT NULL Name: variable length character string length 20 NOT NULL) Primary Key Student_Number - HOUSING( Housing_ID: integer NOT NULL - Student_Number: integer NOT NULL Building: variable length character string length 25 NOT NULL) Primary Key Housing_ID - Foreign Key Student_Number References STUDENT(Student_Number) ON DELETE NO CHECK - ON UPDATE - Which integrity constraint is violated in this relation definition?

Entity integrity

Enterprise constraint

Domain constraint

Referential integrity

1

Which term describes the rejoining of relations that have been decomposed?

Denormalization

Normalization

Domain constraints

Referential integrity

1

What is the highest normal form of the relation(s) shown in the exhibit?

Boyce-Codd normal form

Second normal form

First normal form

Third normal form

1

Consider the relation shown in the exhibit. Which of the following SQL statements would properly add information for a new employee?

INSERT INTO Employee(Emp_ID, First_Name, Last_Name, Birth_Date) VALUES(0004, Tim, Bogart, 03-15-77);

INSERT INTO Employee VALUES(0005, Tim, Bogart, 03-15-77);

1

The exhibit shows a table called Student Relation that tracks all information related to a students courses, professors and sites. What would be the consequence of removing all records for a student with the ID 1311?

Only an update anomaly would occur.

An insertion anomaly would occur.

An update anomaly and a deletion anomaly would occur.

A deletion anomaly would occur.

1

Consider the following SQL statement and the Orders relation shown in the exhibit: What is the output of this SQL statement? SELECT * FROM Orders - WHERE NOT (Amount < 1000 - AND Sales_Rep_No = 210);

Option D

Option C

Option B

Option A

1

Consider the relation shown in the exhibit. Which of the following SQL statements would return a relation that excludes all customers with a Satisfaction_Rate of less than or equal to 80 unless the Sales_Office is located in Atlanta?

SELECT * FROM Customers WHERE Satisfaction_Rate >= 80 AND NOT Sales Office = Atlanta

SELECT * FROM Customers WHERE Satisfaction_Rate <= 80 AND Sales_Office = Atlanta

SELECT * FROM Customers WHERE Satisfaction_Rate >= 80;

SELECT * FROM Customers WHERE Satisfaction_Rate > 80 OR Sales_Office = Atlanta

1

Which of the following best describes the information contained in the data dictionary (or system catalog)?

Table data

Data model

Metafile

Metadata

1

With regard to databases, what is normalization?

The process of reducing the cardinality of a relation

The process of organizing and refining relations

The process of duplicating data to reduce the number of tables

The process of limiting data stored in a table to a specific range of values

1

Which term describes one or more database operations that are executed as a single unit?

Operational group

Encapsulation

Update

Transaction

1

Which statement is used to define a named group of related tables, views, domains and other database objects?

CREATE TABLE

CREATE DOMAIN

CREATE SCHEMA

CREATE

1

What is a domain?

A combination of attributes for a relation

A definition of permissible values for one or more attributes

A normalized set of data applicable to a particular relation

A set of permissible values for one or more relations

1

What improvement can be made to file-based databases to overcome their limitations?

Implement a tabular structure.

Gather files in a distributed repository.

Tightly couple database structure to database application programs.

Use a hierarchical database file system.

1

Which subset of Structured Query Language (SQL) is used to perform operations such as data retrieval or deletion on the data contained in a database?

Data Control Language

Data Manipulation Language

Data Definition Language

Data Formatting Language

1

Your enterprise is creating a relation (shown in the exhibit) that tracks parts and suppliers. Which situation would occur if new supplier information were entered in the relation before any information about specific parts?

An insertion anomaly would occur.

An update anomaly and an insertion anomaly would occur.

A deletion anomaly would occur.

A deletion anomaly and an update anomaly would occur.

1

Consider the Information Engineering diagram shown in the exhibit. Building_ID, R_ID, Room_Count and Room_Num are integer numbers, whereas Bldg_Name and Res_Name are represented by variable-length strings with a maximum of 20 characters. Location can be up to 50 characters long, and no building has more than 600 rooms. Which SQL statement best implements the BUILDING relation shown in this diagram?

CREATE TABLE BUILDING ( Building_ID NOT NULL PRIMARY KEY, Bldg_Name, Location, Room_Count);

CREATE TABLE BUILDING (Building_ID INTEGER NOT NULL PRIMARY KEY, Bldg_Name VARCHAR (20), Location VARCHAR (50), Room_Count INTEGER CHECK (Room_Count > Room_Count INTEGER CHECK ( -1 And Room_Count < 601)); Room_Count > -1 Or Room_Count < 601));

CREATE TABLE BUILDING ( Building_ID NOT NULL PRIMARY KEY, Bldg_Name, Location, Room_Count, FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID))

CREATE TABLE BUILDING (Building_ID INTEGER NOT NULL PRIMARY KEY, Bldg_Name VARCHAR (20), Location VARCHAR (50),

1

What is the highest normal form of the relation(s) shown in the exhibit?

Second normal form

No normal form

Third normal form

First normal form

1

Consider the following database information: domain s_id: integer domain grd: fixed length character string length 1 STUDENT_GRADE( Student_Number: s_id NOT NULL - Grade: grd ) Primary Key - Student_Number - During which phase of the database design process would this information be developed?

Logical

Conceptual

Implementation

Physical

1

The exhibit shows a table called Activity Relation that relates a unique student identification number with a sports activity and a fee for participating in that activity. A student can participate in only one activity. The key for the relation is Student_ID. What consequence would occur if the tuple for Student_ID 1001 were removed?

A deletion anomaly would occur.

Both an insertion anomaly and a deletion anomaly would occur.

An insertion anomaly would occur.

An update anomaly would occur.

1

Which three pieces of information did E.F. Codd describe as necessary to retrieve a data value from a relational database?

Attribute, relation name, and primary key

The process of limiting data stored in a table to a specific range of values

The process of duplicating data to reduce the number of tables

The process of reducing the cardinality of a relation

1

What is a virtual table?

A virtual table is a relation created as the result of data manipulation; it exists only in computer memory, and is not a permanent part of the database.

A virtual table is a relation that consists of primary and foreign keys for a particular set of relations in a database.

A virtual table is a relation derived from the database data dictionary; it contains metadata about a base relation. A virtual table is a relation derived from the database? data dictionary; it contains metadata about a base relation.

A virtual table is a relation stored in the database; it is used when multiple users access the same relation in a database.

1

Consider the following DBDL description of an entity: Teachers (teach_num: variable length character string length 10 NOT NULL teach_name: variable length character string length 10 NOT NULL) Primary Key: teach_num which integrity constraint is satisfied?

Necessary data

Referential foreign integrity

Referential integrity

Entity integrity

1

Consider the relation shown in the exhibit. Which of the following SQL statements would properly remove all tuples for New York customers?

DELETE FROM Customers WHERE Sales_Office = New York;

DELETE * FROM Customer WHERE Sales_Office <> New York;

DELETE FROM Customers WHERE Sales_Office NOT LIKE New York;

DELETE * FROM Customers WHERE Sales_Office = New York;

1

A relation for a construction company is shown in the exhibit. Which of the following best defines the relationship between Cust_ID and Cust_Name?

Cust_Name is functionally dependent on Cust_ID.

Cust_Name is transitively dependent on Cust_ID.

Cust_Name is the determinant.

Cust_ID is transitively dependent on Cust_Name.

1

Consider the Dept1_Parts and Dept2_Parts relations shown in the exhibit. Which of the following SQL statements would create a set difference of the two relations with the widest variety of Structured Query Language dialects?

SELECT * FROM Dept1_Parts MINUS (SELECT Part_ID FROM Dept2_Parts);

SELECT * FROM Dept1_Parts DIFFERENCE (SELECT Part_ID FROM Dept2_Parts);

SELECT * FROM Dept1_Parts EXCEPT (SELECT Part_ID FROM Dept2_Parts);

1

Consider the following four database design activities: 1 - Design user views. 2 - Select a DBMS. 3 - Apply normalization. 4 - Determine entities. Which choice shows the correct ordering of these activities, from first to last, by assigned numbers?

1,2,3,4

3,4,1,2

4,2,3,1

4,3,1,2

1

Consider the following relation definitions: STUDENT( Student_Number: integer NOT NULL Name: variable length character string length 20) Primary Key Student_Number - HOUSING( Housing_ID: integer NOT NULL - Student_Number: integer NOT NULL Building: variable length character string length 25) Primary Key Housing_ID - Foreign Key Student_Number References STUDENT(Student_Number) ON DELETE NO ACTION - ON UPDATE CASCADE - What are the referential constraints for the relations defined in these relation definitions?

Modifications to HOUSING(Student_Number) are automatically reflected in changes to STUDENT(Student_Number), but deletions are not permitted.

When STUDENT(Student_Number) is changed or deleted, this modification or deletion will automatically be reflected in HOUSING(Student_Number).

Modifications to STUDENT(Student_Number) are automatically reflected in changes to HOUSING(Student_Number). For a deletion to occur from STUDENT (Student_Number), it must first occur in HOUSING(Student_Number).

There is no relationship between changes in STUDENT(Student_Number) and HOUSING(Student_Number).

1

Consider the Recreation relation in the exhibit. A data operation that changes one of the tuples for Student_ID 1003 must be performed. It is necessary to change one of the activities from swimming to tennis. The Student_ID and Activity attributes make up the primary key for the Recreation relation. All related information must be altered, as well. Which SQL statement or statements would best accomplish this?

UPDATE Recreation SET Activity, Activity_Fee (Tennis,100) WHERE Student_ID = 1003;

. UPDATE Recreation SET Activity = Tennis', Activity_Fee = 100 WHERE Student_ID = 1003 AND Activity = 'Swimming';

UPDATE TABLE Recreation ALTER COLUMN ACTIVITY SET ACTIVITY = Tennis', Activity_Fee = 100 WHERE Student__ID = 1003 AND Activity = wimming?AND Activity = ?wimming?

DELETE Activity FROM Recreation WHERE Student_ID = 1003; INSERT INTO Recreation VALUES (1003, Tennis', 100);

1

For the Employee relation shown in the exhibit, which set of column value holds the complete tuple for the employee named James Smith?

Emp_ID, 0002, First_Name, James, Last_Name, Smith

First_Name, James, Last_Name, Smith

0002, James, Smith

0002, James, Smith, 10-25-76

1

What is the highest normal form of the relation(s) shown in the exhibit?

Third normal form

STUDENT( Student_Number: integer NOT NULL Name: variable length character string length 20 NOT NULL) Primary Key StudenMMumber STU_CLASS( Student_Number: integer NOT NULL Class_Num: integer NOT NULL) Primary Key Student_Number CLASS( Class_Num: integer NOT NULL Class_Name: integer NOT NULL) Primary Key Class_Num

Second normal form

First normal form

Boyce-Codd normal form

STUDENT( Student_Number: integer NOT NULL Name: variable length character string length 20 NOT NULL) Primary Key Student_Number CLASS( Class_Num: integer NOT NULL Class_Name: integer NOT NULL) Primary Key Class_Num

STUDENT( Student_Number: integer NOT NULL Name: variable length character string length 20 NOT NULL) Primary Key Student_Number CLASS( Class_Num: integer NOT NULL Class_Name: integer NOT NULL) Primary Key Class_Num Foreign Key Class_Num References STUDENT

1

Using the Customer and Sales_Rep relations shown in the exhibit, you must determine a relational algebraic expression that will result in the following relation: Which of the following relational algebraic expressions would result in this relation? Using the Customer and Sales_Rep relations shown in the exhibit, you must determine a relational algebraic expression that will result in the following relation: Which of the following relational algebraic expressions would result in this relation?

(Customer X Sales_Rep) л Sales_Rep.Sales_Rep_No = Customer.Sales_Rep_No

Customer.Sales_Rep_No = Sales.sales_Rep_No (Customer X Sales_Rep)

(Sales_Rep.Sales_Rep_No = Customer.Sales_Rep_No (Sales_Rep X Customer))

Customer X Sales_Rep

1

Your enterprise is involved in planning a database project. The exhibit shows the result of one phase of the database design life cycle. Which term best describes the diagram shown in the exhibit?

Corporate data model

ERD model

Database requirements model

Information Engineering (IE) data model

1

Your enterprise must decide whether to use a database management system. Which of the following best describes the functionality of a DBMS?

A DBMS provides the ability to define, create and maintain databases while providing controlled access to databases.

A DBMS provides the ability to control data access and limit the number of users at one time.

A DBMS provides the ability to maintain databases while providing increased security for the database.

A DBMS allows users to access the database while allowing the database administrator to define views particular to individual sets of users.

1

FROM Registration WHERE - Consider the following relational algebraic expression: Which of the following SQL Course_Code = 'A4343'; statements is equivalent to this relational algebraic expression?

SELECT Sales_Rep_No(108) FROM Orders;

SELECT'FROM Orders WHERE Sales_Rep_No = ?08?

INSERT INTO Orders VALUES(Sales_Rep_No = 108) WHERE Sales_Rep_No = NULL;

SELECT'FROM Orders WHERE Sales_Rep_No = 108;

1

Consider the Registration relation shown in the exhibit. Which of the following SQL statements would return all tuples that have course codes beginning with the letter M?

SELECT * FROM Registration WHERE Course_Code LIKE _

SELECT * FROM Registration WHERE Course_Code LIKE %

SELECT * FROM Registration WHERE Course_Code = #

SELECT * FROM Registration WHERE Course Code = %

1

What is the highest normal form of the relation(s) shown in the exhibit?

First normal form

No normal form

Second normal form

First normal form

1

Which of the following definitions best describes an entity?

Data about data

Data stored in a table column

A relation

An item about which information is stored

1

A large enterprise uses a two-tier database architecture and runs complex database applications. Which term best describes the client in this system?

Thin client

Enterprise client

Terminal client

Fat client

1

Your database administrator has disallowed a group of users from making alterations to the Employees table in your corporate database. The users, known as Group_2, previously had full privileges with the Employees table. Which of the following SQL statements properly removes any alteration privileges from Group_2?

REVOKE UPDATE FOR Employees FROM Group_2;

REVOKE INSERT, UPDATE, DELETE ON Employees FROM Group_2;

REVOKE UPDATE, DELETE FOR Employees FROM Group_2;

REVOKE INSERT, UPDATE, DELETE ON Employees FOR Group_2;

1

Your enterprise has reached the conceptual design phase for a database project. What is the desired goal at the end of this design phase?

An entity-relationship (ER) model with no redundant data

A set of denormalized relations

A reviewed entity-relationship (ER) model

A set of normalized relations

1

What is a data dictionary?

A system catalog containing user data

An area of the database that is directly accessible by the user

Data that is stored in tables and is only accessible by the DBMS

Metadata that is stored in tables and is only accessible by the DBMS

1

Consider the Registration relation shown in the exhibit. Which of the following SQL statements would return the Registration2 relation from the Registration relation?

SELECT * FROM Registration WHERE Course_Code = A4343';

SELECT Registration_ID, Student_ID, First_Name, Last_Name

SELECT Course_Code FROM Registration;

SELECT * FROM Registration WHERE Registration_ID = 1003 AND Registration_ID = 1005;

1

Which subset of Structured Query Language (SQL) is used to create and name database entities?

Database Entity Language

Data Manipulation Language

Data Definition Language

Data Query Language

1

The creation of intermediate entities occurs during the logical database design phase for an enterprise. It is used to resolve which types of relationships?

One-to-many and one-to-one

Redundant, recursive, and one-to-many

One-to-many and recursive

Complex, recursive, and many-to-many

1

In which phase of database design do you identify entities, attribute domains, and relationships?

Conceptual

Physical

Application

Logical

1

Which statement best describes a candidate key?

One or more keys may be used to form a primary key.

One or more keys are joined together to form a composite key.

It is the primary key for an entity.

It uniquely identifies every instance of an entity.

1

Consider the entity-relationship (ER) diagram shown in the exhibit. Which type of relationship between the two entities is shown?

A one-to-one relationship

A recursive relationship

A many-to-many relationship

A one-to-many relationship

1

Which type of dependency occurs when one attribute of a composite key is removed and the dependency still exists?

Transitive

Functional

1:1

Partial functional

1

Which of the following best describes the ON DELETE NO ACTION referential integrity constraint?

If any child key references a parent key, the record containing the parent key cannot be deleted.

If a parent key is deleted, any child keys referenced by the parent key are automatically deleted.

If a parent key is deleted, no test is made for referential integrity.

If a parent key is deleted, all child keys are automatically set to a specified value. C

1

Consider the Registration the exhibit. Which of the following SQL statements would return all tuples that have course codes beginning with the letter M?

SELECT * FROM Registration WHERE Course_Code = #

SELECT* FROM Registration WHERE Course_Code LIKE %

SELECT * FROM Registration WHERE Course_Code LIKE _

SELECT * FROM Registration WHERE Course Code = %

1

Which process is used to prevent the current database operation from reading or writing a data item while that data item is being accessed by another operation?

Transaction

Lock

Deadlock

Time stamp

1

Consider the Orders relation shown in the exhibit. Which of the following SQL statements would return all complete tuples for order dates in 2002, arranged by amount from lowest to highest?

SELECT * FROM Orders WHERE Order_Date LIKE %02 ORDER BY Amount;

SELECT (Order_Date, Amount) FROM Orders WHERE Order_Date LIKE %02 ORDER BY Amount;

SELECT * FROM Orders WHERE Order_Date LIKE _02 ORDER BY Order_No;

SELECT * FROM Orders WHERE Order_Date LIKE _02 ORDER BY Amount;

1

Which statement best describes a procedural data manipulation language command?

It requires that the user know how the underlying data structures are implemented.

The user is not required to know how the underlying data structures are implemented.

It can be used only to manipulate data through a SQL interface.

It contains a query language for retrieving data.

1

Consider the following relations shown in the exhibit. Which of the following SQL statements would return the Customers2 relation from the Customers relation?

SELECT * FROM Customers WHERE Satisfaction_Rate BETWEEN (80, 90);

SELECT * FROM Customers WHERE Satisfaction_Rate IN (80 AND 90);

C. SELECT *FROM Customers WHERE Satisfaction_Rate >= 80 AND Satisfaction_Rate <= 89;

SELECT * FROM Customers WHERE Satisfaction_Rate <= 80 OR Satisfaction_Rate >= 90;

1

Consider the Information Engineering diagram shown in the exhibit for a building management company. Referential integrity must be maintained such that a building cannot be deleted when it has residents. Building_ID, R_ID, Room_Count and Room_Num are integer numbers, whereas Bldg_Name, Location and Res_Name are all represented by variable-length strings with a maximum of 20 characters. Which SQL statement best implements the relations shown in this diagram?

CREATE TABLE BUILDING ( Building_ID INTEGER NOT NULL PRIMARY KEY, Bldg_Name VARCHAR (20), Location VARCHAR (20), Room_Count INTEGER ); CREATE TABLE RESIDENT ( R_ID NOT NULL PRIMARY KEY, Room_Num INTEGER, Res_Name VARCHAR (20), Building_ID INTEGER NOT NULL, FOREIGN KEY Building_ID REFERENCES RESIDENT (Building_ID) ON DELETE NO CHECK);

CREATE TABLE BUILDING ( Building_ID INTEGER NOT NULL PRIMARY KEY, Bldg_Name VARCHAR (20), Location VARCHAR (20), Room_Count INTEGER ); CREATE TABLE RESIDENT ( R_ID NOT NULL PRIMARY KEY, Room_Num INTEGER, Res_Name VARCHAR (20), Building_ID INTEGER NOT NULL, FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID) ON DELETE NO CHECK ON UPDATE CASCADE);

1

Your company must choose which type of database to use for a new project. Which of the following lists three characteristics of file-based database systems?

Separation of data, repetition of data, and data independence

Application program inflexibility, data dependence, and separation of data

Repetition of data, application program flexibility, and data centralization

Incompatibility of files, tabular data structures, and data dependence

1

Consider the Employee relation shown in the exhibit. A database manager wants to set up a view called Emp_Dept that allows users to find employees and their department ID numbers. Which SQL statement will accomplish this?

CREATE VIEW Emp_Dept AS SELECT Last_Name, First_Name, Dept_ID FROM Employee;

CREATE VIEW Emp_Dept AS SELECT * FROM Employee WHERE ID = 0001 AND ID = 0002 AND ID = 0003 AND ID = 0004;

UPDATE VIEW Emp_Dept AS SELECT * FROM Employee;

UPDATE VIEW Emp_Dept AS SELECT Last_Name, First_Name, Dept_ID FROM Employee;

1

Which of the following best describes the two-tier database architecture?

The user accesses a database server using a terminal.

The user interface, data-processing logic, database access and data validation functions are performed on a mainframe server.

The user interface and data-processing logic are performed by the client whereas the server handles database access and data validation functions.

The user interface and data validation functions are performed by the client whereas the data-processing logic is performed on a server.

1

Consider the Dept1_Parts and Dept2_Parts relations shown in the exhibit. Which of the following SQL statements would create a set difference of the two relations with the widest variety of Structured Query Language dialects?

SELECT * FROM Dept1_Parts WHERE Part_ID NOT IN (SELECT Part_ID FROM Dept2_Parts);

SELECT * FROM Dept1_Parts DIFFERENCE (SELECT Part_ID FROM Dept2_Parts);

SELECT * FROM Dept1_Parts MINUS (SELECT Part_ID FROM Dept2_Parts);

SELECT * FROM Dept1_Parts EXCEPT(SELECT PartJD FROM Dept2_Parts);

1

Which type of entity must reference another entity for its data to be meaningful?

Weak

Foreign

Strong

Primary

1

Which area of database security involves maintaining access to enterprise data?

Confidentiality

Privacy

Theft

Availability

1

Which characteristic is an advantage of a database management system?

Data is decentralized.

Data files are owned and maintained by the users.

Database administration is simplified.

A standard method can be used to access the database.

1

The exhibit shows a table called Recreation Relation that relates a unique student identification number and a sports activity with a fee for participating in that activity. The Student_ID and Activity columns in the table are used together as a composite key. Which statement about the relation is correct?

Activity_Fee is partially dependent on the key.

Activity_Fee is a determinant of Activity.

The table contains a transitive dependency.

Activity_Fee is a determinant of Activity and Student_ID.

1

Which subset of Structured Query Language (SQL) is used to limit access to a database or its data?

Data Manipulation Language

Data Control Language

Data Markup Language

Data Formatting Language

1

Consider the Project relation shown in the exhibit as well as the following SQL statement: DELETE FROM Project - WHERE Cust_Name = Acme; Which of the following tables shows the Project relation after execution of this SQL statement?

Option C

Option A

Option B

Option D

1

Which of the following is a characteristic of the three-tier database architecture?

A thick client is used to perform business application logic functions locally.

Database application logic and database functionality are integrated and reside on a common server.

The application logic is centralized on a dedicated server.

A Web browser is used as the application server.

1

Consider the relations shown in the exhibit. Which of the following SQL statements would enter data from the Customers relation into the Atlanta_Customers relation?

INSERT INTO Atlanta_Customers VALUES( SELECT * FROM Customer s WHERE Sales_Office = Atlanta

INSERT INTO Atlanta_Customers SELECT * FROM Customers WHERE Sales_Office = Atlanta

INSERT INTO Atlanta_Customers SELECT Cust_No, Cust_Name, Sales_Office, Sales_Rep_No FROM Customers WHERE Sales_Office = Atlanta

INSERT INTO Atlanta_Customers SELECT Cust_No, Cust_Name, Satisfaction_Rate, Sales_Rep_No FROM Customers WHERE Sales_Office = Atlanta

1

Your enterprise is developing a database system that will contain highly sensitive data. Security of the data will take priority over database processing speed. Which database protection technique should be employed?

Backups

User views

Integrity controls

Encryption

1

Consider the Stu_Act and Act_Fee tables shown in the exhibit. Which relational algebraic operation would yield the Activity Relation table in the exhibit?

Intersection

Union

Natural join

Cartesian product

1

Consider the Information Engineering diagram shown in the exhibit. Building_ID, R_ID, Room_Count and Room_Num are integer numbers, whereas Bldg_Name, Location and Res_Name are all represented by variable-length strings with a maximum of 20 characters. Which SQL statement best implements the RESIDENT relation shown in this diagram?

CREATE TABLE RESIDENT ( R_ID INTEGER NOT NULL PRIMARY KEY, Room_Num INTEGER, Res_Name VARCHAR (20), Building_ID INTEGER NOT NULL, FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID));

CREATE TABLE RESIDENT ( R_ID INTEGER NOT NULL PRIMARY KEY, Room_Num BINARY, Res_Name VARCHAR (20), Building_ID INTEGER NOT NULL, FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID));

CREATE TABLE RESIDENT ( R_ID INTEGER NOT NULL PRIMARY KEY, Room_Num INTEGER, Res_Name VARCHAR (20), Building_ID INTEGER NOT NULL);

CREATE TABLE RESIDENT ( R_ID INTEGER NULL PRIMARY KEY, Room_Num FLOAT, Res_Name VARCHAR, Building_ID INTEGER NULL, FOREIGN KEY Building_ID REFERENCES BUILDING (Building_ID));

1

Your enterprise has created a database and database application. The testing phase for the project has started. Which of the following best describes white-box testing of the projects software?

The database designer tests the software because he or she is able to make necessary changes to the underlying code for the software.

Someone other than the database designer tests the software. This person has no access to the underlying code and attempts to use the software only in ways not considered by the software designers.

A user who has no knowledge of the softwares underlying code tests the software.

A person tests the software and submits suggestions to the software's underlying code. This person is someone other than the database designer, but has access to the softwares underlying code.

1

Consider the entity-relation (ER) diagram shown in the exhibit. When the logical database design phase is completed, which of the following is a valid DBDL description of the base relations for the ER diagram?

STUDENT( Student_Number: integer NOT NULL Name: variable length character string length 20 NOT NULL) Primary Key Student_Number STU_CLASS( Student_Number: integer NOT NULL Class_Num: integer NOT NULL) Primary Key Student_Number, Class_Num CLASS( Class_Num: integer NOT NULL Class_Name: integer NOT NULL) Primary Key Class_Num

STUDENT( Student_Number: integer NOT NULL Name: variable length character string length 20 NOT NULL) Primary Key Student_Number CLASS( Class_Num: integer NOT NULL Class_Name: integer NOT NULL) Primary Key Class_Num

STUDENT( Student_Number: integer NOT NULL Name: variable length character string length 20 NOT NULL) Primary Key Student_Number STU_CLASS( Student_Number: integer NOT NULL Class_Num: integer NOT NULL) Primary Key Student_Number CLASS( Class_Num: integer NOT NULL Class_Name: integer NOT NULL) Primary Key Class_Num

STUDENT( Student_Number: integer NOT NULL Name: variable length character string length 20 NOT NULL) Primary Key Student_Number CLASS( Class_Num: integer NOT NULL Class_Name: integer NOT NULL) Primary Key Class_Num Foreign Key Class_Num References STUDENT

1

Consider the Recreation relation shown in the exhibit. You need to apply a SQL statement to the Recreation relation that will return the following data: Which SQL statement applied to the Recreation relation will return this data?

SELECT DISTINCT Activity FROM Recreation;

SELECT Activity FROM Recreation WHERE DISTINCT Activity;

SELECT Activity FROM Recreation WHERE NOT LIKE Activity;

SELECT Activity FROM Recreation;

1

Which concurrency control method should be used only when conflicts between transactions rarely occur?

Serialization

Optimistic

Locking

Time stamps

1

A foreign key maps to a:

Indirect key

Composite key

Prime key

Parent key

1

Consider the table shown in the exhibit. Which relational algebraic operation would return Row 3?

Union

Difference

Projection

Selection

1

The exhibit shows a table called Housing Relation that relates a unique student identification number with a dormitory building and a room fee for that building. Each building charges only one fee and a student can live in only one building. The key for the Housing Relation is Student_ID. This table is in which normal form?

1NF and 2NF

1NF, 2NF, 3NF and BCNF

1NF

1NF, 2NF and 3NF

1

Which component in the three-tier database architecture handles the data-processing and business logic?

Thin client

Application server

Database server

Fat client

1

Which type of relational integrity is violated if a primary key in a database has a null value?

Domain constraints

Entity integrity

Referential integrity

Domain integrity

1

Consider the following table as well as the Dept1_Parts and Dept2_Parts relations shown in the exhibit: Which of the following relational algebraic expressions would result in the given table?

Option C

Option B

Option A

Option D

1

Which security technique limits access by unauthorized users to parts of an enterprise database?

Integrity controls

Views

Concurrency

Locking

1

Which term best defines a database system in which data records are stored in one or more files with no structured relationship?

Object-oriented database

Distributed database

Relational database

Flat-file database

1

Which mechanism provides database users with controlled access to the database through the use of virtual tables?

Data dictionary

Database management system

Database control language

View

1

Which of the following describes two desirable characteristics of a primary key?

A primary key should consist of meaningful data and a value that can be changed if needed.

A primary key should not consist of meaningful data and a value that can be changed if needed.

A primary key should be a value that is not null and will never change.

A primary key should be a value that may be null and may change over time.

1

In a relational database, which term describes a single table consisting of rows and columns?

Relation

Entity

Data dictionary

Matrix

1

What is the most important service provided by a database management system?

Provides support for a data manipulation language

Allows users to store data in a distributed data repository

Provides support for data formatting language commands

Translates procedural commands into non-procedural commands

1

Which term describes the management of simultaneous transactions to prevent conflicts?

Database control

Serialization

Parallelism

Concurrency control

1

Consider the following SQL statement and the Orders relation shown in the exhibit: How many records should be returned?

Four records

Two records

Three records

Five records

1

The exhibit shows a relation for a company projects. Which candidate key(s) would best serve as the primary key for this relation?

Proj_ID and Item_Num

S_Date and E_Date

ProjJD

1

Consider the following relational algebraic expression: Which of the following SQL statements is equivalent to this relational algebraic expression?

SELECT * FROM Customers, Employees WHERE Sales_Rep_No = Cust_No;

SELECT Cust_No, Cust_Name, Emp_Name, Emp_Loc FROM Customers, Employees WHERE Customers.Sales_Rep_No = Employees.Sales_Rep_No;

SELECT * FROM Customers, Employees WHERE Customers.Sales_Rep_No = Employees.Sales_Rep_No;

SELECT Cust_No, Cust_Name, Emp_Name, Emp_Loc FROM Customers, Employees WHERE Employees.Sales_Rep_No = Customers.Sales_Rep_No;

1

Consider the following SQL statement and the Orders relation shown in the exhibit:
What is the output of this SQL statement?

Option A

Option B

Option C

Option D

1

Which of the following ACID properties requires that a transaction be executed in its entirety or not all?

Atomicity

Consistency

Isolation

Durability

1

Which relational algebraic operation is used to select specific columns (attributes) from a relation?

Difference

Union

Projection

Intersection

1

Which pair of relational algebraic operations requires union compatibility?

Intersection and difference

Union and join

Selection and projection

Cartesian product and intersection

1

To create a view, what are the minimal privileges that a user must have for the relations used to make the view?

SELECT

GRANT

CREATE VIEW

REVOKE

1

The database manager wants to give Rubio and Doe the ability to modify the Project Relation shown in the exhibit. A temporary employee named Temp needs to access the data in the database to generate reports. Which group of SQL statements will perform this task?

GRANT UPDATE ON Project WHERE Manager = 'Rubio'; GRANT UPDATE ON Project WHERE Manager = 'Doe'; GRANT SELECT ON Project TO Temp;

GRANT UPDATE ON Project TO Rubio, Doe; GRANT SELECT ON Project TO Temp;

GRANT SELECT ON Project WHERE Manager = 'Rubio'; GRANT SELECT ON Project WHERE Manager = 'Doe';

GRANT ALL PRIVILEGES ON Project TO Rubio, Doe; GRANT UPDATE ON Project TO Temp;

1

Consider the relational database shown in the exhibit. What is the foreign key in this database?

Dept_Name

Dept_Mngr

Department. Dept_ID

Employee. Dept_ID

1

Consider the symbols shown in the exhibit. Which of the following correctly identifies these symbols when used in an entity-relationship (ER) diagram?

1 = relationship, 2 = entity, 3 = attribute

1 = attribute, 2 = entity, 3 = relationship

1 = entity, 2 = relationship, 3 = attribute

1 = relationship, 2 = attribute, 3 = entity

1

Consider the table for an employee database shown in the exhibit. What is the cardinality of the table?

20

4

25

6

1

Consider the Orders relation shown in the exhibit. Which of the following SQL statements would replace the value in the Sales_Rep_No column with 110 everywhere that Sales_Rep_No 108 is listed?

UPDATE Sales_Rep_No IN Orders SET(Sales_Rep_No = 110 WHERE Sales_Rep_No = 108);

UPDATE Orders SET Sales_Rep_No = 110 WHERE Sales_Rep_No = 108;

UPDATE Orders SET Sales_Rep_No = 110;

UPDATE Orders WHERE Sales_Rep_No = 108 SET Sales_Rep_No = 110;

1

Which of the following best describes a composite key?

A composite key is a primary key that consists of the first two attributes of a relation.

A composite key is a foreign key that consists of the same attributes as the primary key from a related table.

A composite key is a primary or foreign key defined by its parent keys.

A composite key is a primary or foreign key that consists of two or more attributes of a relation.

1

A theta-join can be viewed as:

A Cartesian product of two relations

A restricted Cartesian product of two relations

The Cartesian product of two union-compatible relations

The intersection of two relations

1

Consider the following relational algebraic expression as well as the Employee and Department relations shown in the exhibit: Which of the following relations would result from the given relational algebraic expression?

Option D

Option A

Option B

Option C

1

What is a relational database domain?

A group of attributes

A set of permissible tuple values

A set of permissible attribute values

A collection of related data items

1

In which situation would the DBMS use a serial schedule to execute the transactions?

Concurrent transactions read or write from different data structures.

Concurrent transactions read or write the same data structure.

Concurrent transactions write data to different data structures.

Concurrent transactions read data from the same data structure.

1

Which of the following definitions applies to all types of databases?

Data that is stored as tables

Data that is stored in a structured manner

Software that manipulates data

Data records that are stored sequentially in a file

1

NULL) Primary Key Class_Num - Consider the Information Engineering diagram shown in the exhibit. Which DBDL definition best describes this diagram?

BUILDING(Building_ID, Bldg_Name, Location, Room_Count) Primary Key BUILDING Foreign Key BUILDING(Building_ID) references RESIDENT (Building_ID) RESIDENT(R_ID, Room_Num, Res_Name, Building_ID) Primary Key RESIDENT

BUILDING(Building_ID, Bldg_Name, Location, Room_Count) Primary Key Building_ID RESIDENT(R_ID, Room_Num, Res_Name, Building_ID) Primary Key R_ID

BUILDING(Building_ID, Bldg_Name, Location, Room_Count) Primary Key BUILDING RESIDENT(R_ID, Room_Num, Res_Name, Building_ID) Primary Key RESIDENT

BUILDING(Building_ID, Bldg_Name, Location, Room_Count) Primary Key Building_ID RESIDENT(R_ID, Room_Num, Res_Name, Building_ID) Primary Key R_ID Foreign Key Building_ID references BUILDING(Building_ID)

1

Consider the Information Engineering diagram in the exhibit showing a conceptual data model of the relations BUILDING and RESIDENT. What is the next step in refining the data model?

Identify and resolve complex relationships.

Resolve many-to-many relationships.

Create a logical data model.

Create intermediate entities.

1

Consider the Project relation shown in the exhibit as well as the following SQL statement: Which of the following tables shows the Project relation after execution of this SQL statement?

Option C

Option B

Option A

Option D

1

Consider the Information Engineering diagram in the exhibit showing the relations BUILDING and RESIDENT. What is the relationship between BUILDING and RESIDENT?

1:1

1:N

N:1

M:N

1

Which term describes an attribute or combination of attributes that uniquely identifies a row in a relation?

Primary key

Entity

Domain

Attribute group

1

Several SQL operations are performed by User 1 to access the Fee information for Bowling in the Act_Fee relation (shown in the exhibit). The first access returns a fee of 50. An unrelated SQL operation by another user updates the Bowling fee to 60. The second access by User 1 returns a fee of 60. What problem has occurred?

Dirty read

Deadlock

Rollback

No problem has occurred.

1

Which database architecture is best suited to implementation in the World Wide Web environment?

Three-tier using thin client

Three-tier using fat client

Centralized mainframe with terminal client

1

Consider the table for an employee database shown in the exhibit. What is the degree of the table?

4

25

5

20

1

You enterprise must decide whether to use a database management system. Which of the following lists four advantages of using a DBMS?

Increased data access, increased data backup and recovery, data sharing, and consistency of data.

Consistency of data, adherence to standards, managed concurrency, and increased software complexity.

Management of data redundancy, increased data integrity, increased data dependence, and increased application program flexibility.

Increased data security, increased data integrity, increased data independence, and decreased data separation.

1

Which statement accurately describes a characteristic of attributes?

An attribute contains values from multiple domains.

The ordering of attributes in a table is significant.

An attribute name can be used only once per table.

An attribute name can be used only once in a relational database system with multiple tables.

1

Consider the entity-relationship (ER) diagram shown in the exhibit. What do the characters at the ends of the connecting line indicate?

Determinant of a relation

Primary key of a relation

Degree of a relation

Cardinality of a relation

1

Which of the following occurs in a relation when records are added or removed?

The degree of the relation is fixed but the cardinality varies.

The cardinality of the relation is fixed but the degree varies.

The attributes in the domain change.

The number of domains changes.

1

Consider the following relational algebraic expression as well as the Dept1_Parta and Dept2_Parts relations shown in the exhibit: Which of the following relations would result form the given relational algebraic expression?

Option D

Option B

Option C

Option A

1

Which of the following best describes the ON DELETE CASCADE referential integrity constraint?

If any child key references a parent key, the record containing the parent key cannot be deleted.

If a parent key is deleted, all child keys are automatically set to a specified value.

If a parent key is deleted, any child keys referenced by the parent key are automatically deleted.

If a parent key is deleted, no test is made for referential integrity.

1

Which database security technique prevents invalid data from being entered into the database?

File locking

Integrity controls

User authorization

Parity checks

1

Consider the Dept1_Parts and Dept2_Parts relations shown in the exhibit. Which of the following SQL statements would create an intersection of the two relations with the widest variety of Structured Query Language dialects?

SELECT * FROM Dept1_Parts WHERE Dept1_Parts.Part_ID = Dept2_Parts.Part_ID;

SELECT * FROM Dept1_Parts INTERSECTION (SELECT * FROM Dept2_Parts);

SELECT * FROM Dept1_Parts AND (SELECT * FROM Dept2_Parts);

1

Consider the entity-relationship (ER) diagram shown in the exhibit. Which type of relationship between the two entities is shown?

m:n

m:m

1:n

1:1