CIW CIW Certifications 1D0-541 Questions & Answers

• Question 91:

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

  Correct Answer: D

• Question 92:

  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

  Correct Answer: D

• Question 93:

  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?

  

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

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

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

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

  Correct Answer: D

• Question 94:

  Which statement accurately describes a characteristic of attributes?

  A. The ordering of attributes in a table is significant.

  B. An attribute contains values from multiple domains.

  C. An attribute name can be used only once per table.

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

  Correct Answer: C

• Question 95:

  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.

  Correct Answer: B

• Question 96:

  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?

  

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

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

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

  D. SELECT * FROM Dept1_Parts DIFFERENCE (SELECT Part_ID

  FROM Dept2_Parts);

  Correct Answer: D

• Question 97:

  A. A set of normalized relations

  B. A reviewed entity-relationship (ER) model

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

  D. A set of denormalized relations

  Correct Answer: B

• Question 98:

  What is a virtual table?

  A. A virtual table is a relation created as the result of data manipulation, and is not a permanent part of the database.

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

  C. A virtual table is a relation created from a defined base table; it contains metadata about the base relation.

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

  Correct Answer: A

• Question 99:

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

  

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

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

  C. INSERT INTO Employee(Emp_ID, First_Name, Last_Name, Birth_Date) VALUES(0005, Tim, Bogart, 03-05-77);

  D. INSERT INTO Employee(Emp_ID, First_Name, Last_Name, Birth_Date) VALUES(0005, Tim, Bogart, 03-05-77);

  Correct Answer: D

• Question 100:

  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 FROM Registration WHERE Course_Code = 'A4343';

  Correct Answer: C