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?

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?

1

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

1

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

1

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

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?

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);

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?

1

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

1

With regard to databases, what is normalization?

1

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

1

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

1

What is a domain?

1

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

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?

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?

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?

1

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

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?

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?

1

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

1

What is a virtual table?

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?

1

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

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?

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?

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

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?

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?

1

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

1

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

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?

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?

1

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

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?

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?

1

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

1

Which of the following definitions best describes an entity?

1

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

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?

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?

1

What is a data dictionary?

1

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

1

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

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?

1

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

1

Which statement best describes a candidate key?

1

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

1

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

1

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

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?

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?

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?

1

Which statement best describes a procedural data manipulation language command?

1

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

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?

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?

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?

1

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

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?

1

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

1

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

1

Which characteristic is an advantage of a database management system?

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?

1

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

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?

1

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

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?

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?

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?

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?

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?

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?

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?

1

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

1

A foreign key maps to a:

1

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

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?

1

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

1

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

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?

1

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

1

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

1

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

1

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

1

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

1

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

1

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

1

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

1

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

1

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

1

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

1

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

1

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

1

Which pair of relational algebraic operations requires union compatibility?

1

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

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?

1

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

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

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

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?

1

Which of the following best describes a composite key?

1

A theta-join can be viewed as:

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?

1

What is a relational database domain?

1

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

1

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

1

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

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?

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?

1

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

1

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

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?

1

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

1

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

1

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

1

Which statement accurately describes a characteristic of attributes?

1

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

1

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

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?

1

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

1

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

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?

1