CIW 1D0-541 Practice Questions with Explanations
Free CIW 1D0-541 practice questions. 21 of them, each with the correct answer, a full explanation, and the reason every other option is wrong.
1D0-541 practice questions
Question 1
With regard to databases, what is normalization?
- A. The process of reducing the cardinality of a relation
- B. The process of organizing and refining relations
- C. The process of duplicating data to reduce the number of tables
- D. The process of limiting data stored in a table to a specific range of values
Show answer and explanation ▾
Correct answer: B
Normalization organizes data into well-structured relations to cut redundancy and avoid update anomalies, which is refining the relations.
Why the other options are wrong:
- A. Normalization is not about reducing the cardinality of a relation.
- C. Normalization splits data to remove duplication, so it usually adds tables rather than reducing them.
- D. Limiting values to a range is a domain constraint, not normalization.
Question 2
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
Show answer and explanation ▾
Correct answer: D
Codd described a value as located by naming the relation (table), the primary key that picks the row, and the attribute (column) wanted.
Why the other options are wrong:
- A. A domain and a tuple do not on their own point to one stored value.
- B. An entity and a domain are not the addressing items Codd named.
- C. Table name and primary key are right, but entity is not the third item; the attribute is.
Question 3
Your enterprise has reached the conceptual design phase for a database project.
What is the desired goal at the end of this design phase?
- 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
Show answer and explanation ▾
Correct answer: B
The conceptual phase ends with a reviewed entity-relationship model that captures the business data needs. Normalizing or denormalizing relations belongs to later phases.
Why the other options are wrong:
- A. Normalized relations are produced in the logical design phase.
- C. Removing redundancy is a later normalization step, not the aim of conceptual design.
- D. Denormalization is a performance choice made after the logical design.
Question 4
In which phase of database design do you identify entities, attribute domains, and relationships?
- A. Logical
- B. Physical
- C. Application
- D. Conceptual
Show answer and explanation ▾
Correct answer: D
Entities, attribute domains and relationships are identified in the conceptual phase, before the logical and physical phases.
Why the other options are wrong:
- A. The logical phase turns the conceptual model into relations.
- B. The physical phase deals with storage and implementation details.
- C. Application is not a database design phase.
Question 5
Which term describes one or more database operations that are executed as a single unit?
- A. Update
- B. Transaction
- C. Encapsulation
- D. Operational group
Show answer and explanation ▾
Correct answer: B
A transaction is one or more database operations treated as a single logical unit of work. Either all of its operations are committed or none of them are.
Why the other options are wrong:
- A. An update is a single data modification operation, not a group handled as one unit.
- C. Encapsulation is an object-oriented concept that hides internal details of an object.
- D. Operational group is not a database term for a unit of work.
Question 6
Which statement is used to define a named group of related tables, views, domains and other database objects?
- A. CREATE
- B. CREATE TABLE
- C. CREATE DOMAIN
- D. CREATE SCHEMA
Show answer and explanation ▾
Correct answer: D
CREATE SCHEMA defines a schema, which is a named collection of related database objects such as tables, views and domains that belong together.
Why the other options are wrong:
- A. CREATE alone is not a complete statement, because it does not say what object to create.
- B. CREATE TABLE defines a single table.
- C. CREATE DOMAIN defines a set of permissible values for attributes, not a group of objects.
Question 7
What is a domain?
- A. A normalized set of data applicable to a particular relation
- B. A combination of attributes for a relation
- C. A definition of permissible values for one or more attributes
- D. A set of permissible values for one or more relations
Show answer and explanation ▾
Correct answer: C
A domain defines the set of permissible values that one or more attributes may take, such as a data type, length and allowed range.
Why the other options are wrong:
- A. A domain is not a normalized set of data for a relation.
- B. A combination of attributes describes the heading of a relation, not a domain.
- D. Domains apply to attributes, not to whole relations.
Question 8
Which pair of relational algebraic operations requires union compatibility?
- A. Union and join
- B. Selection and projection
- C. Intersection and difference
- D. Cartesian product and intersection
Show answer and explanation ▾
Correct answer: C
Intersection and difference, like union, compare whole tuples from two relations, so both relations must have the same number of attributes drawn from matching domains.
Why the other options are wrong:
- A. A join does not require union compatibility, only a common attribute to compare.
- B. Selection and projection each work on a single relation.
- D. A Cartesian product combines any two relations and does not require union compatibility.
Question 9
What is the most important service provided by a database management system?
- A. Provides support for a data manipulation language
- B. Allows users to store data in a distributed data repository
- C. Provides support for data formatting language commands
- D. Translates procedural commands into non-procedural commands
Show answer and explanation ▾
Correct answer: A
A database management system exists so that users and applications can store, retrieve and change data. It does this by supporting a data manipulation language for querying and updating the database.
Why the other options are wrong:
- B. Distributed storage is an optional architecture, not the core service of a DBMS.
- C. Formatting commands are not a core service of a DBMS.
- D. Translating procedural commands into non-procedural ones is not a service a DBMS provides.
Question 10
What improvement can be made to file-based databases to overcome their limitations?
- A. Implement a tabular structure.
- B. Gather files in a distributed repository.
- C. Use a hierarchical database file system.
- D. Tightly couple database structure to database application programs.
Show answer and explanation ▾
Correct answer: A
Organizing data into tables, as the relational model does, lets data be defined once, related through common columns and queried independently of any one program, which removes the main limits of file-based systems.
Why the other options are wrong:
- B. Gathering files in a distributed repository keeps the same separate, program-dependent files.
- C. A hierarchical file system does not remove the dependence of programs on file structure.
- D. Tight coupling of structure and programs is a limitation of file-based systems, not a fix.
Question 11
Which of the following occurs in a relation when records are added or removed?
- A. The number of domains changes.
- B. The attributes in the domain change.
- C. The cardinality of the relation is fixed but the degree varies.
- D. The degree of the relation is fixed but the cardinality varies.
Show answer and explanation ▾
Correct answer: D
Adding or removing records changes the number of tuples, which is the cardinality. The number of attributes, which is the degree, stays the same because the structure of the relation does not change.
Why the other options are wrong:
- A. Adding or removing rows does not change the number of domains.
- B. Row changes do not alter the attributes or their domains.
- C. This reverses the two terms: the degree stays fixed and the cardinality varies.
Question 12
The creation of intermediate entities occurs during the logical database design phase for an enterprise. It is used to resolve which types of relationships?
- A. One-to-many and recursive
- B. Complex, recursive, and many-to-many
- C. Redundant, recursive, and one-to-many
- D. One-to-many and one-to-one
Show answer and explanation ▾
Correct answer: B
During logical design, intermediate entities are created to break down relationships that the relational model cannot represent directly: many-to-many relationships, complex relationships among three or more entities, and recursive relationships.
Why the other options are wrong:
- A. One-to-many relationships are represented directly with a foreign key and need no intermediate entity.
- C. Redundant relationships are removed, and one-to-many relationships need no intermediate entity.
- D. One-to-many and one-to-one relationships are represented directly with foreign keys.
Question 13
Your enterprise is developing a database system that will contain highly sensitive data. Security of the data will take priority over database processing speed.
Which database protection technique should be employed?
- A. Backups
- B. User views
- C. Encryption
- D. Integrity controls
Show answer and explanation ▾
Correct answer: C
Encryption encodes the stored data so it is unreadable without the decryption key. It gives the strongest protection for highly sensitive data, at the cost of extra processing to encrypt and decrypt.
Why the other options are wrong:
- A. Backups protect against loss of data, not against unauthorized reading of it.
- B. User views restrict what each user sees but leave the stored data readable.
- D. Integrity controls keep data valid and consistent, not confidential.
Question 14
Which of the following best describes a composite key?
- A. A composite key is a primary key that consists of the first two attributes of a relation.
- B. A composite key is a primary or foreign key defined by its parent keys.
- C. A composite key is a foreign key that consists of the same attributes as the primary key from a related table.
- D. A composite key is a primary or foreign key that consists of two or more attributes of a relation.
Show answer and explanation ▾
Correct answer: D
A composite key is a key made up of two or more attributes of a relation that are used together. Both primary keys and foreign keys can be composite.
Why the other options are wrong:
- A. A composite key is not tied to the first two attributes of a relation.
- B. Parent keys do not define what makes a key composite.
- C. A foreign key that matches a primary key in another table is not composite unless it has two or more attributes.
Question 15
Consider the following database information: domain s_id: integer domain grd: fixed length character string length 1 STUDENT_GRADE( Student_Number: s_id NOT NULL Grade: grd ) Primary Key Student_Number During which phase of the database design process would this information be developed?
- A. Logical
- B. Physical
- C. Conceptual
- D. Implementation
Show answer and explanation ▾
Correct answer: A
Describing relations with attributes, domains, null rules and primary keys in a database definition language is the output of logical database design, which is independent of any particular DBMS product.
Why the other options are wrong:
- B. Physical design turns the logical description into storage structures and statements for a specific DBMS.
- C. Conceptual design identifies entities and relationships before domains and keys are written out in this form.
- D. Implementation builds the database from a finished design.
Question 16
Which process is used to prevent the current database operation from reading or writing a data item while that data item is being accessed by another operation?
- A. Lock
- B. Deadlock
- C. Time stamp
- D. Transaction
Show answer and explanation ▾
Correct answer: A
A lock marks a data item as in use by one operation, so other operations are denied read or write access to that item until the lock is released.
Why the other options are wrong:
- B. A deadlock is a stalemate in which transactions wait on each other's locks.
- C. A time stamp orders transactions by time instead of blocking access to an item.
- D. A transaction is a unit of work, not the mechanism that blocks access.
Question 17
Your company must choose which type of database to use for a new project.
Which of the following lists three characteristics of file-based database systems?
- A. Repetition of data, application program flexibility, and data centralization
- B. Incompatibility of files, tabular data structures, and data dependence
- C. Separation of data, repetition of data, and data independence
- D. Application program inflexibility, data dependence, and separation of data
Show answer and explanation ▾
Correct answer: D
In file-based systems each program defines and owns its own files. Data is therefore separated and isolated, file structure is written into the programs, and any change to that structure forces program changes.
Why the other options are wrong:
- A. Application program flexibility and data centralization are traits of a DBMS.
- B. Tabular data structures are a feature of relational databases.
- C. Data independence is a DBMS trait, since file-based systems have data dependence.
Question 18
Consider the following four database design activities:
1 - Design user views.
2 -
Select a DBMS.
3 - Apply normalization.
4 -
Determine entities.
Which choice shows the correct ordering of these activities, from first to last, by assigned numbers?
- A. 1,2,3,4
- B. 3,4,1,2
- C. 4,3,1,2
- D. 4,2,3,1
Show answer and explanation ▾
Correct answer: D
Entities are determined first, during conceptual design. A DBMS is then selected, normalization is applied during logical design, and user views are designed last, during physical design, as part of the security mechanisms.
Why the other options are wrong:
- A. User views cannot be designed before the entities are known.
- B. Normalization cannot be applied before the entities are determined.
- C. This order places the design of user views before the DBMS is selected.
Question 19
A large enterprise uses a two-tier database architecture and runs complex database applications.
Which term best describes the client in this system?
- A. Fat client
- B. Enterprise client
- C. Thin client
- D. Terminal client
Show answer and explanation ▾
Correct answer: A
In a two-tier architecture the client holds the presentation and the application logic while the server manages the data. A client that carries this much processing is called a fat client.
Why the other options are wrong:
- B. Enterprise client is not a standard term for a client tier.
- C. A thin client handles little beyond presentation and relies on a middle tier for application logic.
- D. Terminal client is not the term for a client that runs the application logic.
Question 20
Which of the following definitions best describes an entity?
- A. A relation
- B. Data about data
- C. Data stored in a table column
- D. An item about which information is stored
Show answer and explanation ▾
Correct answer: D
An entity is a person, place, thing, event or concept about which the enterprise stores information, such as a customer or an order.
Why the other options are wrong:
- A. A relation is the table structure that holds data, not the item being described.
- B. Data about data is metadata.
- C. Data stored in a table column is an attribute value.
Question 21
Which subset of Structured Query Language (SQL) is used to limit access to a database or its data?
- A. Data Markup Language
- B. Data Control Language
- C. Data Formatting Language
- D. Data Manipulation Language
Show answer and explanation ▾
Correct answer: B
Data Control Language holds the SQL statements that control access to the database and its data, such as GRANT and REVOKE.
Why the other options are wrong:
- A. Data Markup Language is not a subset of SQL.
- C. Data Formatting Language is not a subset of SQL.
- D. Data Manipulation Language retrieves and changes data and does not control access.
That was 21 of 124.
The full CIW 1D0-541 pack has all 124 questions, each with the answer, the explanation and why the other options are wrong, plus a questions-only copy for timed runs. $39.00 USD, paid once, with free monthly updates and a pass-or-your-money-back guarantee.
Get the full packNot ready yet? Get the first 36 free as one PDF by email. Get the Half Pack →
About the 1D0-541 exam
These 21 CIW 1D0-541 questions are free here. The free PDF has 36, free by email.
They are drawn from the same bank as the full 1D0-541 pack, which has 124 questions in total.