CIW v5 Database Design Specialist
- Exam Number/Code : 1D0-541
- Exam Name : CIW v5 Database Design Specialist
- Questions and Answers : 91 Q&As
Download Pass4sure 1D0-541 Dumps from the link below
Download CertifyMe 1D0-541 Dumps from the link below
Download Testking 1D0-541 Dumps from the link below
PrepKing.com CIW 1D0-541 Sample Questions
Exam : CIW 1D0-541
Title : CIW v5 Database Design Specialist
1. Which three pieces of information did E.F. Codd describe as necessary to retrieve a data value from a relational database?
A. Attribute, domain, and tuple
B. Entity, relation name, and domain
C. Table name, primary key, and entity
D. Attribute, relation name, and primary key
Answer: D
2. 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
Answer: A
3. 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. 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
Foreign Key Class_Num References STUDENT
C. STUDENT(
Student_Number: integer NOT NULL
Name: variable length character string length 20 NOT NULL)
Primary Key Student_Number
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
D. STUDENT(
Student_Number: integer NOT NULL
Name: variable length character string length 20 NOT NULL)
Primary Key Student_Number
STU_CLASS(
Student_Number: integer NOT NULL
Class_Num: integer NOT NULL)
Primary Key Student_Number, Class_Num
CLASS(
Class_Num: integer NOT NULL
Class_Name: integer NOT NULL)
Primary Key Class_Num
Answer: D
4. 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 = tlanta
B. INSERT INTO Atlanta_Customers
SELECT *
FROM Customers
WHERE Sales_Office = tlanta
C. INSERT INTO Atlanta_Customers
SELECT Cust_No, Cust_Name, Satisfaction_Rate, Sales_Rep_No
FROM Customers
WHERE Sales_Office = tlanta
D. INSERT INTO Atlanta_Customers
SELECT Cust_No, Cust_Name, Sales_Office, Sales_Rep_No
FROM Customers
WHERE Sales_Office = tlanta
Answer: C
5. 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 York?WHERE Sales_Office = ?ew York?
D. DELETE
FROM Customers
WHERE Sales_Office NOT LIKE ew York? WHERE Sales_Office NOT LIKE ?ew York?
Answer: B
6. 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. BUILDING(Building_ID, Bldg_Name, Location, Room_Count)
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. 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
Foreign Key Building_ID references BUILDING(Building_ID)
Answer: D
7. 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.
Answer: D
8. 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 Employee;
C. UPDATE VIEW Emp_Dept
AS SELECT Last_Name, First_Name, Dept_ID
FROM Employee;
D. CREATE VIEW Emp_Dept
AS SELECT *
FROM Employee
WHERE ID = 0001
AND ID = 0002
AND ID = 0003
AND ID = 0004;
Answer: A
