CIW Database Design Specialist (1D0-541) Certification Sample Questions
The purpose of this Sample Question Set is to provide you with information about the CIW Database Design Specialist exam. These sample questions will make you very familiar with both the type and the difficulty level of the questions on the 1D0-541 certification test. To get familiar with real exam environment, we suggest you try our Sample CIW Database Design Specialist Certification Practice Exam. This sample practice exam gives you the feeling of reality and is a clue to the questions asked in the actual CIW Database Design Specialist certification exam.
These sample questions are simple and basic questions that represent likeness to the real CIW 1D0-541 exam questions. To assess your readiness and performance with real time scenario based questions, we suggest you prepare with our Premium CIW Database Design Specialist Certification Practice Exam. When you solve real time scenario based questions practically, you come across many difficulties that give you an opportunity to improve.
CIW 1D0-541 Sample Questions:
01. A gym database holds these relations:
CURRENT_MEMBERS (Member_ID: integer, Name: string)
FORMER_MEMBERS (Member_ID: integer, Name: string)
STAFF (Staff_ID: integer, Name: string, Role: string)
CLASSES (Class_Name: string, Capacity: integer)
Which pairs are union-compatible?
(Choose two.)
a) CURRENT_MEMBERS and FORMER_MEMBERS
b) CURRENT_MEMBERS and STAFF
c) FORMER_MEMBERS and CLASSES
d) CURRENT_MEMBERS and π Staff_ID, Name (STAFF)
02. EMPLOYEE (Emp_ID, Name, Manager_ID) records each employee's manager, where Manager_ID holds the Emp_ID of another employee.
Which statement lists each employee's name in the first column and the name of that employee's manager in the second column?
a) SELECT E.Name, M.Name FROM Employee E, Employee M;
b) SELECT E.Name, M.Name FROM Employee E, Employee M WHERE E.Manager_ID = M.Emp_ID;
c) SELECT E.Name, M.Name FROM Employee E, Employee M WHERE E.Emp_ID = M.Emp_ID;
d) SELECT E.Name, M.Name FROM Employee E, Employee M WHERE E.Emp_ID = M.Manager_ID;
03. Which statement lists the bookings whose fare is above the average fare of all bookings?
a) SELECT Booking_ID FROM Bookings WHERE Fare > (SELECT Fare FROM Bookings);
b) SELECT Booking_ID FROM Bookings WHERE Fare > AVG(Fare);
c) SELECT Booking_ID FROM Bookings HAVING Fare > AVG(Fare);
d) SELECT Booking_ID FROM Bookings WHERE Fare > (SELECT AVG(Fare) FROM Bookings);
04. A team is converting a validated logical data model into a physical design for the DBMS the organization has selected.
Which decisions belong to physical design rather than to logical design?
(Choose two.)
a) Selecting secondary indexes for the required transactions
b) Resolving each many-to-many relationship into a junction relation
c) Choosing each column's data type in the selected DBMS
d) Removing transitive dependencies from the relations by decomposition
05. When an airline deletes a flight from FLIGHT, every booking for that flight in BOOKING must be removed automatically.
Which clause in the definition of BOOKING provides this?
a) FOREIGN KEY (Flight_No) REFERENCES Flight(Flight_No) ON DELETE NO ACTION
b) FOREIGN KEY (Flight_No) REFERENCES Flight(Flight_No) ON DELETE CASCADE
c) FOREIGN KEY (Flight_No) REFERENCES Flight(Flight_No) ON DELETE SET NULL
d) FOREIGN KEY (Flight_No) REFERENCES Flight(Flight_No) ON UPDATE CASCADE
06. An online retailer stores customers' card numbers in its Payments table. It wants to reduce the risk of those numbers being disclosed to people who are not entitled to see them.
Which countermeasures address this threat?
(Choose two.)
a) Writing checkpoints to the transaction log more often
b) A view without the card column for staff
c) Encrypting the card-number column
d) Mirroring the database disks with RAID
07. The LESSONS relation stores Lesson_ID, Instructor and Fee. The school wants the instructors whose average lesson fee is greater than 60.
Which statement returns them?
a) SELECT Instructor FROM Lessons WHERE AVG(Fee) > 60 GROUP BY Instructor;
b) SELECT Instructor FROM Lessons WHERE Fee > 60;
c) SELECT Instructor, AVG(Fee) FROM Lessons ORDER BY AVG(Fee) DESC;
d) SELECT Instructor FROM Lessons GROUP BY Instructor HAVING AVG(Fee) > 60;
08. In a gym's conceptual model, each Member is assigned exactly one Locker, and each Locker is assigned to exactly one Member. Participation is mandatory on both sides.
Under the standard rules for deriving relations in logical design, how is this relationship represented?
a) Combine Member and Locker into one relation
b) Post Member's primary key into LOCKER as a foreign key
c) Create a junction relation, MEMBER_LOCKER
d) Post Locker's primary key into MEMBER as a foreign key
09. Two relations are joined with a natural join on Club:
STU_CLUB (Student, Club): S1, Chess; S2, Drama; S3, Chess
CLUB_ROOM (Club, Room): Chess, R5; Drama, R6; Film, R7
Which statement describes STU_CLUB ⋈ CLUB_ROOM?
a) Four tuples, with Film paired with a null student
b) Three tuples; Film does not appear
c) Nine tuples, one for every combination of the two relations
d) Two tuples, one for each club that has students
10. LOANS (Loan_ID, Member_ID, Due_Date) and MEMBERS (Member_ID, Name) are related by Member_ID.
Which statement lists each loan together with the name of the member who borrowed it?
a) SELECT Loans.Loan_ID, Members.Name FROM Loans, Members WHERE Loans.Loan_ID = Members.Member_ID;
b) SELECT Loan_ID, Name FROM Loans WHERE Member_ID IN (SELECT Member_ID FROM Members);
c) SELECT Loans.Loan_ID, Members.Name FROM Loans, Members WHERE Loans.Member_ID = Members.Member_ID;
d) SELECT Loans.Loan_ID, Members.Name FROM Loans, Members;
Answers:
|
Question: 01 Answer: a, d |
Question: 02 Answer: b |
Question: 03 Answer: d |
Question: 04 Answer: a, c |
Question: 05 Answer: b |
|
Question: 06 Answer: b, c |
Question: 07 Answer: d |
Question: 08 Answer: a |
Question: 09 Answer: b |
Question: 10 Answer: c |
Note: For any error in CIW Database Design Specialist (1D0-541) certification exam sample questions, please update us by writing an email on feedback@edusum.com.
