Deck 3: CIW v5 Database Design Specialist

Question 1

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

A) Implement a tabular structure.
B) Gather files in a distributed repository.
C) Use a hierarchical database file system.
D) Tightly couple database structure to database application programs.

Answer 1

A

Question 2

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

A) 6
B) 20
C) 4
D) 25

Answer 2

C

Question 3

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

A) 25
B) 5
C) 4
D) 20

Answer 3

B

Question 4

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

A) Provides support for a data manipulation language
B) Allows users to store data in a distributed data repository
C) Provides support for data formatting language commands
D) Translates procedural commands into non-procedural commands

Question 5

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

A) Management of data redundancy, increased data integrity, increased data dependence, and increased application program flexibility.
B) Consistency of data, adherence to standards, managed concurrency, and increased software complexity.
C) Increased data access, increased data backup and recovery, data sharing, and consistency of data.
D) Increased data security, increased data integrity, increased data independence, and decreased data separation.

Question 6

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?

A) Logical
B) Physical
C) Conceptual
D) Implementation

Question 7

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

A) 1:1
B) 1:N
C) N:1
D) M:N

Question 8

Which pair of relational algebraic operations requires union compatibility?

A) Union and join
B) Selection and projection
C) Intersection and difference
D) Cartesian product and intersection

Question 9

Which of the following best describes a composite key?

A) A composite key is a primary key that consists of the first two attributes of a relation.
B) A composite key is a primary or foreign key defined by its parent keys.
C) A composite key is a foreign key that consists of the same attributes as the primary key from a related table.
D) A composite key is a primary or foreign key that consists of two or more attributes of a relation.

Question 10

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

A) One-to-many and recursive
B) Complex, recursive, and many-to-many
C) Redundant, recursive, and one-to-many
D) One-to-many and one-to-one

Question 11

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?

A) UPDATE Recreation SET Activity, Activity_Fee ('Tennis',100) WHERE Student_ID = 1003;
B) UPDATE TABLE Recreation ALTER COLUMN ACTIVITY SET ACTIVITY = 'Tennis', Activity_Fee = 100 WHERE Student__ID = 1003 AND Activity = wimming?AND Activity = ?wimming?
C) UPDATE Recreation SET Activity = 'Tennis', Activity_Fee = 100 WHERE Student_ID = 1003 AND Activity = 'Swimming';
D) DELETE Activity FROM Recreation WHERE Student_ID = 1003; INSERT INTO Recreation VALUES (1003, 'Tennis', 100);

Question 12

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

A) 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
B) Primary Key BUILDING RESIDENT(R_ID, Room_Num, Res_Name, Building_ID) Primary Key RESIDENT
C) 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
D) Res_Name, Building_ID) Primary Key R_ID Foreign Key Building_ID references BUILDING(Building_ID)

Question 13

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

A) The number of domains changes.
B) The attributes in the domain change.
C) The cardinality of the relation is fixed but the degree varies.
D) The degree of the relation is fixed but the cardinality varies.

Question 14

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?

A) Backups
B) User views
C) Encryption
D) Integrity controls

Question 15

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?

A) 1NF
B) 1NF and 2NF
C) 1NF, 2NF and 3NF
D) 1NF, 2NF, 3NF and BCNF

Question 16

What is a relational database domain?

A) A group of attributes
B) A set of permissible tuple values
C) A collection of related data items
D) A set of permissible attribute values

Question 17

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?

A) An update anomaly and an insertion anomaly would occur.
B) An insertion anomaly would occur.
C) A deletion anomaly would occur.
D) A deletion anomaly and an update anomaly would occur.

Question 18

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?

A) Option A
B) Option B
C) Option C
D) Option D

Question 19

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?

A) Lock
B) Deadlock
C) Time stamp
D) Transaction

Question 20

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

A) S_Date and E_Date
B) ProjJD
C) ltem_Num and E_Date
D) Proj_ID and Item_Num

Question 21

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?

A) SELECT Sales_Rep_No(108) FROM Orders;
B) INSERT INTO Orders VALUES(Sales_Rep_No = 108) WHERE Sales_Rep_No = NULL;
C) SELECT'FROM Orders WHERE Sales_Rep_No = 108;
D) WHERE Sales_Rep_No = ?08?

Question 22

Which statement best describes a procedural data manipulation language command?

A) It contains a query language for retrieving data.
B) It can be used only to manipulate data through a SQL interface.
C) The user is not required to know how the underlying data structures are implemented.
D) It requires that the user know how the underlying data structures are implemented.

Question 23

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?

A) 1,2,3,4
B) 3,4,1,2
C) 4,3,1,2
D) 4,2,3,1

Question 24

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

A) Union
B) Selection
C) Projection
D) Difference

Question 25

Which of the following definitions best describes an entity?

A) A relation
B) Data about data
C) Data stored in a table column
D) An item about which information is stored

Question 26

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

A) Data Markup Language
B) Data Control Language
C) Data Formatting Language
D) Data Manipulation Language

Question 27

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?

A) CREATE VIEW Emp_Dept AS SELECT Last_Name, First_Name, Dept_ID FROM Employee;
B) UPDATE VIEW Emp_Dept AS SELECT * FROM
C) UPDATE VIEW Emp_Dept AS SELECT
D) CREATE VIEW Emp_Dept Employee WHERE ID = 0001 AND ID = 0002 AND ID = 0003 AND ID = 0004;

Question 28

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?

A) Entity integrity
B) Necessary data
C) Referential integrity
D) Referential foreign integrity

Question 29

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

A) Entity integrity
B) Domain integrity
C) Domain constraints
D) Referential integrity

Question 30

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

A) SELECT Course_Code FROM Registration;
B) SELECT * FROM Registration WHERE Registration_ID = 1003 AND Registration_ID = 1005;
C) SELECT * FROM Registration WHERE Course_Code = 'A4343';
D) SELECT Registration_ID, Student_ID, First_Name, Last_Name

Question 31

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

A) Degree of a relation
B) Cardinality of a relation
C) Primary key of a relation
D) Determinant of a relation

Question 32

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) An update anomaly would occur.
B) An insertion anomaly would occur.
C) A deletion anomaly would occur.
D) Both an insertion anomaly and a deletion anomaly would occur.

Question 33

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

A) Fat client
B) Enterprise client
C) Thin client
D) Terminal client

Question 34

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

A) Theft
B) Privacy
C) Availability
D) Confidentiality

Question 35

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

A) DELETE * FROM Customers WHERE Sales_Office = New York;
B) DELETE FROM Customers WHERE Sales_Office = ew York?WHERE Sales_Office = ?ew York?
C) DELETE * FROM Customers WHERE Sales_Office = ew
D) DELETE FROM Customers WHERE Sales_Office NOT LIKE ew York? WHERESales_Office NOT LIKE ?ew York?

Question 36

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?

A) Rollback
B) Deadlock
C) Dirty read
D) No problem has occurred.

Question 37

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?

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

Question 38

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?

A) Repetition of data, application program flexibility, and data centralization
B) Incompatibility of files, tabular data structures, and data dependence
C) Separation of data, repetition of data, and data independence
D) Application program inflexibility, data dependence, and separation of data

Question 39

What is a data dictionary?

A) A system catalog containing user data
B) An area of the database that is directly accessible by the user
C) Data that is stored in tables and is only accessible by the DBMS
D) Metadata that is stored in tables and is only accessible by the DBMS

Question 40

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

A) Option A
B) Option B
C) Option C
D) Option D

Question 41

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?

A) SELECT * FROM Registration WHERE Course_Code = #
B) Course_Code LIKE _
C) Course_Code LIKE %
D) Course Code = %

Question 42

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?

A) Option A
B) Option B
C) Option C
D) Option D

Question 43

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

A) The user accesses a database server using a terminal.
B) The user interface, data-processing logic, database access and data validation functions are performed on a mainframe server.
C) The user interface and data validation functions are performed by the client whereas the data-processing logic is performed on a server.
D) The user interface and data-processing logic are performed by the client whereas the server handles database access and data validation functions.

Question 44

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

A) Second normal form
B) First normal form
C) Boyce-Codd normal form
D) Third normal form
E) 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
F) STUDENT( Student_Number: integer NOT NULL Name: variable length character string length 20 NOT NULL) Class_Num Foreign Key Class_Num References STUDENT
G) 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

Question 45

Which statement best describes a candidate key?

A) It is the primary key for an entity.
B) It uniquely identifies every instance of an entity.
C) One or more keys are joined together to form a composite key.
D) One or more keys may be used to form a primary key.

Question 46

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?

A) Customer X Sales_Rep
B) (Sales_Rep.Sales_Rep_No = Customer.Sales_Rep_No (Sales_Rep X Customer))
C) (Customer X Sales_Rep) л Sales_Rep.Sales_Rep_No = Customer.Sales_Rep_No
D) Customer.Sales_Rep_No = Sales.sales_Rep_No (Customer X Sales_Rep)

Question 47

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

A) Entity
B) Domain
C) Primary key
D) Attribute group

Question 48

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?

A) SELECT * FROM Registration WHERE Course_Code = #
B) Course_Code LIKE _
C) SELECT* FROM Course_Code LIKE %
D) Course Code = %

Question 49

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

A) Employee. Dept_ID
B) Dept_Mngr
C) Dept_Name
D) Department. Dept_ID

Question 50

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?

A) Option A
B) Option B
C) Option C
D) Option D

Question 51

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

A) A recursive relationship
B) A many-to-many relationship
C) A one-to-one relationship
D) A one-to-many relationship

Question 52

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

A) Third normal form
B) Second normal form
C) No normal form
D) First normal form

Question 53

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

A) A Web browser is used as the application server.
B) The application logic is centralized on a dedicated server.
C) A thick client is used to perform business application logic functions locally.
D) Database application logic and database functionality are integrated and reside on a common server.

Question 54

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

A) Two-tier using thin client
B) Three-tier using fat client
C) Three-tier using thin client
D) Centralized mainframe with terminal client

Question 55

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

A) 1:1
B) Transitive
C) Functional
D) Partial functional

Question 56

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?

A) INSERT INTO Atlanta_Customers VALUES( SELECT * FROM Customer s WHERE Sales_Office = Atlanta
B) SELECT * FROM Customers WHERE Sales_Office = Atlanta
C) INSERT INTO Atlanta_Customers SELECT Cust_No, Cust_Name, Satisfaction_Rate, Sales_Rep_No FROM Customers
D) Cust_Name, Sales_Office, Sales_Rep_No FROM Customers

Question 57

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?

A) REVOKE INSERT, UPDATE, DELETE ON Employees FROM Group_2;
B) REVOKE UPDATE FOR Employees FROM Group_2;
C) Employees FOR Group_2;
D) UPDATE, DELETE FOR Employees

Question 58

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

A) View
B) Data dictionary
C) Database control language
D) Database management system

Question 59

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?

A) SELECT * FROM Customers WHERE Satisfaction_Rate > 80 OR Sales_Office = Atlanta
B) WHERE Satisfaction_Rate <= 80 AND Sales_Office = Atlanta
C) WHERE Satisfaction_Rate >= 80;
D) WHERE Satisfaction_Rate >= 80 AND NOT Sales Office = Atlanta

Question 60

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

A) Locking
B) Time stamps
C) Optimistic
D) Serialization

Question 61

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?

A) SELECT Activity FROM Recreation;
B) SELECT DISTINCT Activity FROM Recreation;
C) SELECT Activity FROM Recreation WHERE NOT LIKE Activity;
D) WHERE DISTINCT Activity;

Question 62

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

A) A primary key should be a value that may be null and may change over time.
B) A primary key should be a value that is not null and will never change.
C) A primary key should consist of meaningful data and a value that can be changed if needed.
D) A primary key should not consist of meaningful data and a value that can be changed if needed.

Question 63

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

A) Parallelism
B) Serialization
C) Database control
D) Concurrency control

Question 64

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?

A) Activity_Fee is a determinant of Activity.
B) Activity_Fee is partially dependent on the key.
C) The table contains a transitive dependency.
D) Activity_Fee is a determinant of Activity and Student_ID.

Question 65

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

A) 1 = attribute, 2 = entity, 3 = relationship
B) 1 = entity, 2 = relationship, 3 = attribute
C) 1 = relationship, 2 = entity, 3 = attribute
D) 1 = relationship, 2 = attribute, 3 = entity

Question 66

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

A) No normal form
B) Second normal form
C) First normal form
D) Third normal form

Question 67

A theta-join can be viewed as:

A) The intersection of two relations
B) A Cartesian product of two relations
C) A restricted Cartesian product of two relations
D) The Cartesian product of two union-compatible relations

Question 68

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

A) Concurrent transactions read data from the same data structure.
B) Concurrent transactions write data to different data structures.
C) Concurrent transactions read or write the same data structure.
D) Concurrent transactions read or write from different data structures.

Question 69

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?

A) 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
B) Foreign Key Class_Num References STUDENT
C) STU_CLASS( Class_Num: integer NOT NULL)
D) Primary Key Student_Number, Class_Num

Question 70

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

A) Entity
B) Matrix
C) Relation
D) Data dictionary

Question 71

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

A) Flat-file database
B) Relational database
C) Distributed database
D) Object-oriented database

Question 72

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

A) Two records
B) Three records
C) Four records
D) Five records

Question 73

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?

A) GRANT UPDATE ON Project TO Rubio, Doe; GRANT SELECT ON Project TO Temp;
B) GRANT ALL PRIVILEGES ON Project TO Rubio, Doe; GRANT UPDATE ON Project TO Temp;
C) GRANT SELECT ON Project WHERE Manager = 'Rubio'; WHERE Manager = 'Doe';
D) GRANT UPDATE ON Project

Question 74

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?

A) UPDATE Sales_Rep_No IN Orders SET(Sales_Rep_No = 110 WHERE Sales_Rep_No = 108);
B) UPDATE Orders SET Sales_Rep_No = 110 WHERE Sales_Rep_No = 108;
C) SET Sales_Rep_No = 110;
D) WHERE Sales_Rep_No = 108

Question 75

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

A) Data that is stored as tables
B) Software that manipulates data
C) Data that is stored in a structured manner
D) Data records that are stored sequentially in a file