CIW 1D0-541 Free Practice Questions

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

• A. SELECT *FROM CustomersWHERE Satisfaction_Rate <= 80 OR Satisfaction_Rate >= 90;
• B. SELECT *FROM CustomersWHERE Satisfaction_Rate IN (80 AND 90);
• C. SELECT *FROM CustomersWHERE Satisfaction_Rate >= 80 AND Satisfaction_Rate <= 89;
• D. SELECT *FROM Customers WHERE Satisfaction_Rate BETWEEN (80, 90);

Answer: C

NEW QUESTION 2
Which pair of relational algebraic operations requires union compatibility?

• A. Projection and Cartesian product
• B. Selection and projection
• C. Intersection and difference
• D. Cartesian product and intersection

Answer: C

NEW QUESTION 3
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

Answer: A

NEW QUESTION 4
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. Timestamp
• D. Batch

Answer: A

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

• A. Data Definition Language
• B. Data Control Language
• C. Data Entity Language
• D. Data Manipulation Language

Answer: B

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

• A. Durability
• B. Consistency
• C. Isolation
• D. Atomicity

Answer: D

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

• A. Normalization
• B. Denormalization
• C. Referential integrity
• D. Domain constraints

Answer: B

NEW QUESTION 8
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:m
• D. m:n

Answer: B

NEW QUESTION 9
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?

• A. 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));
• B. 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));
• C. CREATE TABLE RESIDENT (R_ID INTEGER NOT NULL PRIMARY KEY,Room_Num INTEGER, Res_Name VARCHAR (20),Building_ID INTEGER NOT NULL);
• D. 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));

Answer: D

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

• A. If a parent key is deleted, any child keys referenced by the parent key are automatically deleted.
• B. If a parent key is deleted, no test is made for referential integrity.
• C. If any child key references a parent key, the record containing the parent key cannot be deleted.
• D. If a parent key is deleted, all child keys are automatically set to a specified value.

Answer: C

NEW QUESTION 11
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?

• A. 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);
• B. 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 CHECKON UPDATE CASCADE);
• C. 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 CHECKON UPDATE CASCADE);
• D. 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 CHECKON UPDATE CASCADE);

Answer: C

NEW QUESTION 12
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

Answer: A

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

• A. 1:1
• B. Transitive
• C. Functional
• D. Partial functional

Answer: D

Explanation:

NEW QUESTION 14
Consider the following relational algebraic expression as well as the Dept1_Parts and Dept2_Parts relations shown in the exhibit:


Which of the following relations would result from the given relational algebraic expression?

• A.
• B.
• C.
• D.

Answer: C

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

• A. Update
• B. Transaction
• C. Procedure
• D. Singleton

Answer: B

NEW QUESTION 16
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 NULLName: variable length character string length 20 NOT NULL) Primary Key Student_NumberCLASS(Class_Num: integer NOT NULL Class_Name: integer NOT NULL) Primary Key Class_Num
• B. STUDENT(Student_Number: integer NOT NULLName: variable length character string length 20 NOT NULL) Primary Key Student_NumberCLASS(Class_Num: integer NOT NULL Class_Name: integer NOT NULL) Primary Key Class_NumForeign Key Class_Num References STUDENT
• C. STUDENT(Student_Number: integer NOT NULLName: variable length character string length 20 NOT NULL) Primary Key Student_NumberSTU_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
• D. STUDENT(Student_Number: integer NOT NULLName: variable length character string length 20 NOT NULL) Primary Key Student_NumberSTU_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

Answer: D

NEW QUESTION 17
......