1D0-541 by CIW Valid Free Exam Practice Test

Question 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?

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

Correct Answer: C

Question 2

What is a domain?

A. A set of permissible values for one or more relations B. A normalized set of data applicable to a particular relation C. A definition of permissible values for one or more attributes D. A combination of attributes for a relation

Correct Answer: C

Question 3

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 BUILDING (Building_ID)
ON DELETE NO CHECK
ON UPDATE CASCADE); 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 CHECK
ON 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 CHECK
ON 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 RESIDENT (Building_ID)
ON DELETE NO CHECK);

Correct Answer: A

Question 4

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

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

Correct Answer: C

Question 5

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.

Correct Answer: B

Question 6

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

A. Logical B. Physical C. Conceptual D. Application

Correct Answer: C

Question 7

Which statement best describes a candidate key?

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

Correct Answer: C

Question 8

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

A. CREATE TABLE B. CREATE SCHEMA C. CREATE D. CREATE DOMAIN

Correct Answer: B

Question 9

Which of the following definitions best describes an entity?

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

Correct Answer: A

Question 10

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

A. SELECT * FROM Customers, Employees WHERE Sales_Rep_No = Cust_No; B. SELECTCust_No, Cust_Name, Emp_Name, Emp_Loc FROM Customers, Employees WHERE Customers.Sales_Rep_No = Employees.Sales_Rep_No; C. SELECT * FROM Customers, Employees WHERE Customers.Sales_Rep_No = Employees.Sales_Rep_No; D. SELECTCust_No, Cust_Name, Emp_Name, Emp_Loc FROM Customers, Employees WHERE Employees.Sales_Rep_No = Customers.Sales_Rep_No;

Correct Answer: B

CIW v5 Database Design Specialist - 1D0-541 Exam Practice Test