CIW Database Design Specialist (1D0-541) Exam Practice Test 2 | Practice Test / Quiz / MCQs

Home > Certified Internet Web Professional > Quizzes > CIW Database Design Specialist (1D0-541) Exam Practice Test 2

CIW Database Design Specialist (1D0-541) Exam Practice Test 2

Fast practice, instant feedback. Timer auto-submits when time’s up.

Avg score: 24% • Most missed: “(NULL) Primary Key Class_Num Consider the Information Engineering diagram shown …”

CIW Database Design Specialist (1D0-541) Exam Practice Test 2

Time left 00:00

1. Which statement accurately describes a characteristic of attributes?

The ordering of attributes in a table is significant.An attribute name can be used only once per table.An attribute contains values from multiple domains.An attribute name can be used only once in a relational database system with multiple tables.

2. Consider the table for an employee database shown in the exhibit. What is the degree of the table?

5420 25

3. Which subset of Structured Query Language (SQL) is used to perform operations such as data retrieval or deletion on the data contained in a database?

Data Formatting LanguageData Control LanguageData Manipulation LanguageData Definition Language

4. A foreign key maps to a:

parent key.prime key.indirect key.composite key.

5. Which database architecture is best suited to implementation in the World Wide Web environment?

Two-tier using thin clientThree-tier using thin clientThree-tier using fat clientCentralized mainframe with terminal client

6. Which three pieces of information did E.F. Codd describe as necessary to retrieve a data value from a relational database?

Attribute, relation name, and primary keyAttribute, domain, andtupleTable name, primary key, and entityEntity, relation name, and domain

7. Consider the Recreation relation shown in the exhibit. You need to apply a SQL statement to the Recreation relation that will return the following data: Which SQL statement applied to the Recreation relation will return this data?

SELECT Activity FROM Recreation;SELECT DISTINCT Activity FROM Recreation;SELECT Activity FROM Recreation WHERE DISTINCT Activity;SELECT Activity FROM Recreation WHERE NOT LIKE Activity;

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

Data Control LanguageData Markup LanguageData Formatting LanguageData Manipulation Language

9. 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?

SELECT * FROM Dept1_Parts EXCEPT (SELECT Part_ID FROM Dept2_Parts);SELECT * FROM Dept1_Parts DIFFERENCE (SELECT Part_ID FROM Dept2_Parts);SELECT * FROM Dept1_Parts MINUS (SELECT Part_ID FROM Dept2_Parts);SELECT * FROM Dept1_Parts DIFFERENCE (SELECT Part_ID FROM Dept2_Parts);

10. Consider the Registration relation shown in the exhibit. Which of the following SQL statements would return all tuples that have course codes beginning with the letter M?

SELECT * FROM Registration WHERE Course Code = %SELECT* FROM Registration WHERE Course_Code LIKE %SELECT * FROM Registration WHERE Course_Code LIKE _ SELECT * FROM Registration WHERE Course_Code = #

11. Which mechanism provides database users with controlled access to the database through the use of virtual tables?

Database management systemData dictionaryDatabase control languageView

12. Which type of dependency occurs when one attribute of a composite key is removed and the dependency still exists?

Functional1:1 TransitivePartial functional

13. Which relational algebraic operation is used to select specific columns (attributes) from a relation?

UnionProjectionDifferenceIntersection

14. A relation for a construction company is shown in the exhibit. Which of the following best defines the relationship between Cust_ID and Cust_Name?

Cust_Name is functionally dependent on Cust_ID.Cust_Name is transitively dependent on Cust_ID.Cust_Name is the determinant.Cust_ID is transitively dependent on Cust_Name.

15. Consider the following SQL statement and the Orders relation shown in the exhibit: What is the output of this SQL statement? SELECT * FROM Orders WHERE NOT (Amount < 1000 AND Sales_Rep_No = 210);

BACD

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

INSERT INTO Employee VALUES(0005, Tim, Bogart, 03-15-77);INSERT INTOEmployee(Emp_ID, First_Name, Last_Name, Birth_Date) VALUES(0005, Tim, Bogart, 03-05-77);INSERT INTOEmployee(Emp_ID, First_Name, Last_Name, Birth_Date) VALUES(0004, Tim, Bogart, 03-15-77);

17. 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?

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: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 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( 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

18. Consider the following DBDL description of an entity: Teachers (teach_num: variable length character string length 10 NOT NULL teach_name: variable length character string length 10 NOT NULL) Primary Key: teach_num which integrity constraint is satisfied?

Referential foreign integrityNecessary dataEntity integrityReferential integrity

19. Consider the Recreation relation in the exhibit. A data operation that changes one of the tuples for Student_ID 1003 must be performed. It is necessary to change one of the activities from swimming to tennis. The Student_ID and Activity attributes make up the primary key for the Recreation relation. All related information must be altered, as well. Which SQL statement or statements would best accomplish this?

UPDATE Recreation SET Activity = 'Tennis', Activity_Fee = 100 WHERE Student_ID = 1003 AND Activity = 'Swimming'; 'Swimming'; UPDATE TABLE Recreation ALTER COLUMN ACTIVITY SET ACTIVITY = 'Tennis', Activity_Fee = 100 WHERE Student__ID = 1003 AND Activity = wimming?AND Activity = ?wimming?UPDATE Recreation SET Activity, Activity_Fee ('Tennis',100) WHERE Student_ID = 1003;DELETE Activity FROM Recreation WHERE Student_ID = 1003; INSERT INTO Recreation VALUES (1003, 'Tennis', 100);

20. Which of the following occurs in a relation when records are added or removed?

The cardinality of the relation is fixed but the degree varies.The attributes in the domain change.The degree of the relation is fixed but the cardinality varies.The number of domains changes.

21. What is the highest normal form of the relation(s) shown in the exhibit?

First normal formSecond normal formThird normal formNo normal form

22. Consider the table for an employee database shown in the exhibit. What is the cardinality of the table?

2025 46

23. The database manager wants to give Rubio and Doe the ability to modify the Project Relation shown in the exhibit. A temporary employee named Temp needs to access the data in the database to generate reports. Which group of SQL statements will perform this task?

GRANT UPDATE ON Project TO Rubio, Doe; GRANT SELECT ON Project TO Temp;GRANT SELECT ON Project WHERE Manager = 'Rubio'; GRANT SELECT ON Project WHERE Manager = 'Doe';GRANT UPDATE ON Project WHERE Manager = 'Rubio'; GRANT UPDATE ON Project WHERE Manager = 'Doe'; GRANT SELECT ON Project TO Temp;GRANT ALL PRIVILEGES ON Project TO Rubio, Doe; GRANT UPDATE ON Project TO Temp;

24. 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?

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);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 INTEGECREATE 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 ICREATE 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 INTEG

25. Consider the following table as well as the Dept1_Parts and Dept2_Parts relations shown in the exhibit: Which of the following relational algebraic expressions would result in the given table?

ADBC

Welcome to Fatskills

Join 4M+ learners. Unlock unlimited quizzes, wrong-answer tracking, flashcards + reminders, study guides, and 1-on-1 challenges.

Create a free account Sign in

900+ exams • Millions of questions • No spam

❤ If you liked Fatskills, consider supporting us by checking out The Life Manuals You Never Got.

🌈 Our mission is to help improve your scores in any subject and exam withonline quizzes, practice tests, study guides, & advice for students. Since 2018.

About | Explore | User Guide | Topics | Subjects | Doubt Solver | Career Aptitude Test | Answers | Free Tools | OSHA Basics Quiz | What Should We Know?
Privacy | Terms |

All trademarks, logos and brand names are the property of their respective owners. All company, product and service names used in this website are for identification purposes only. Use of these names, trademarks and brands does not imply endorsement.

25 Questions

❤ If you liked Fatskills, consider supporting us by checking out The Life Manuals You Never Got.

About | Explore | User Guide | Topics | Subjects | Doubt Solver | Career Aptitude Test | Answers | Free Tools | OSHA Basics Quiz | What Should We Know?
Privacy | Terms |

Without work one finishes nothing. - Ralph Waldo Emerson
© 2026 Fatskills.com

All trademarks, logos and brand names are the property of their respective owners. All company, product and service names used in this website are for identification purposes only. Use of these names, trademarks and brands does not imply endorsement.

25 Questions

❤ If you liked Fatskills, consider supporting us by checking out The Life Manuals You Never Got.

About | Explore | User Guide | Topics | Subjects | Doubt Solver | Career Aptitude Test | Answers | Free Tools | OSHA Basics Quiz | What Should We Know? Privacy | Terms |

Without work one finishes nothing. - Ralph Waldo Emerson© 2026 Fatskills.com

All trademarks, logos and brand names are the property of their respective owners. All company, product and service names used in this website are for identification purposes only. Use of these names, trademarks and brands does not imply endorsement.

About | Explore | User Guide | Topics | Subjects | Doubt Solver | Career Aptitude Test | Answers | Free Tools | OSHA Basics Quiz | What Should We Know?
Privacy | Terms |

Without work one finishes nothing. - Ralph Waldo Emerson
© 2026 Fatskills.com