D426 Practice Test

A record consists of a ____.

A. Character
B. Collection of related characters
C. Set of one or more fields
D. Group of files

C. A set of connected fields in a table make up a row, and that complete row is referred to as a record.

A DBMS performs several important functions that guarantee the integrity and consistency of the data in the database. Which of the following is NOT one of those functions?

A. Data integrity management
B. Data storage management
C. Data reports
D. Security management

C. Data based reports from a database are generated using analytics tools rather than DBMS.

Which item is unstructured data?

A. A record representing one student
B. A video
C. A table that holds student data
D. A relational database that holds course registration data

B. A video itself is not an example of structured data, but various fields describing the video would be.

Data is/are ______.

A. Information
B. Raw facts
C. Processed information
D. A DBMS

B. Data by itself cannot tell us much, but data connected in a logical way can produce information which can be acted on.

Which classification is correct for the box marked “Registration” in the given E-R Diagram?

A. Intersection data
B. Modality data
C. Cardinality data
D. Student data

A. The term intersection data refers to data which can be used to connect tables which do not share common fields by containing fields that are common to both other tables.

Which type of relationship exists between Student and Course in the diagram?

A. One-to-many binary
B. Many-to-many binary
C. One-to-many unary
D. Many-to-many unary

B. Since the outer icon on both sides of the line representing the relationship is a “crow’s foot” and the relationship includes two entities, the relationship would be binary many-to-many.

What is the E-R Diagram about an online pet food vendor showing?

A. An Invoice Line can include many Items.
B. An item can only be included in one Invoice Line.
C. One Invoice Line must include a minimum and a maximum of one Item.
D. The relationship between Item and Invoice Line is many-to-many.

C. Since the line representing the relationship has 2 single | marks on the item side of the diagram, the item has both a minimum and maximum of one in the relationship.

Example: A pet owner can have many pets; a specific pet is linked to one pet owner.

A. One-to-one
B. One-to-many
C. Many-to-many
D. Associative

B. Since there can only be one owner per pet and a pet owner can have many pets, the relationship is one-to-many.

A Database Models is ____.

A. a conceptual framework for database systems
B. a leading relational database system sponsored by Oracle
C. s suitable for non-commercial applications such as education
D. is a specification of database objects such as tables

A.

What is the correct way to read the modality on the right side of the association?

A. A maximum of one activity
B. A maximum of zero activities
C. A minimum of zero activities
D. A maximum of many activities

C. Modality is a description of the minimum number of values on one side of a relationship.

What is the modality of Volunteer?

A. Two or more
B. Two
C. Dependent on registration date
D. At least one

D. Minimum and At Least are synonyms in questions 10 & 11.

What type of relationship is expressed with the phrase “A Painter paints one or more Paintings”?

A. 1:M
B. 1:1
C. M:1
D. M:N

A. If you think about a real artist, Da Vinci produced several works of art. The relationship between him and his paintings is 1:M (single artist, several works).

The entity integrity rule requires that ____.

A. All primary key entries are unique
B. A part of the key may be null
C. Foreign key values do not reference primary key values
D. Duplicate object values are allowed

A. Entity Integrity: This rule proclaims that each row in a table must contain some unique data.

What does the ‘refer’ in referential integrity mean?

A. The relationships between entities and attributes, also called referrals.
B. Reference points that databases place in each record during backups.
C. A foreign key in a table must refer to a valid primary key in another table.
D. You delete a row in one table whose primary key does not have a matching foreign key value in another table.

C. The reference between a primary and foreign key is how tables in a database are connected and can ensure that changes in one area of the database can be propagated to others.

A table is perceived as a ____.

A. flat structure
B. two-dimensional structure
C. linked structure
D. graph

B. The two-dimensions we are referring to in this context are the columns and rows (or X and Y axis if you are thinking mathematically).

Another word for the term “relation” is ____.

A. Datafile
B. Data index
C. Table Name
D. Data query

C.

DISTINCT filters the results to remove duplicates. ORDER BY ____.

A. does the same thing
B. alters the order of the rows in a table
C. modifies the presentation by changing the order of the result set
D. removes duplicates in the table

C. ORDER BY applies sorting to your statement’s output.

A primary key ____.

A. is a minimal superkey
B. is always the first field in each table
C. must be numeric
D. must be unique

D. Rules for a primary key are that they are Unique and Not Null.

A table can be logically connected to another table by defining a ____.

A. hyperlink
B. common attribute
C. primary key
D. logic key

B. Common attributes are properties or characteristics (i.e. fields) shared by two or more tables.

A relational operator that allows for the combination of information from two or more tables is known as the ____ operator.

A. SELECT
B. PROJECT
C. JOIN
D. DIFFERENCE

C. The Join clause facilitates the connection of two table through identification of a common attribute.

Which of the following statements concerning the primary key is true?

A. All primary key entries are unique.
B. The primary key may be null.
C. The primary key is not required for all tables.
D. The primary key data do not have to be unique.

A. Rules for a primary key are that they are Unique and Not Null.

We can describe a link by observing that ____.

A. a primary key of one table appears again as a primary key in a related table
B. a foreign key of one table appears again as a foreign key in a related table
C. a primary key of one table appears again as a foreign key in a related table
D. a foreign key of one table appears again as a surrogate key in a related table

C. The reference of a foreign key in one table to the primary key in another links those tables together.

When designing a new database, it is a good idea to ____.

A. minimize data redundancy
B. include redundant fields
C. include a common field in all tables
D. use composite keys

A. Data redundancy creates more opportunity to introduce errors into the database, increased storage needs, and reduces query performance.

An attribute (or combination of attributes) that uniquely identifies each entity in a table is called a ____.

A. superkey
B. foreign key
C. master key
D. secondary key

A.

A foreign key must ____.

A. be numeric
B. be unique
C. be defined in all tables within the database
D. match the value of a primary key in a related table

D. Foreign keys must include a reference to another table’s primary key.

The Entity Relationship Diagram (ERD) is used to graphically represent the ____ database model.

A. condensed
B. physical
C. logical
D. conceptual

D. The conceptual model is developed during the Analysis Phase of database design.

A derived attribute ____.

A. must be stored physically within the database
B. need not be physically stored within the database
C. has many values
D. must be based on the value of three or more attributes

B. Derived attributes are calculated using stored values rather than stored themselves.

A relationship is an association between ____.

A. objects
B. entities
C. databases
D. fields

B.

A ____ key is a key that consists of more than one attribute.

A. primary
B. foreign
C. composite
D. domain

C. In a relational model, attribute is the formal term for a column, and a composite consists of multiple columns.

A(n) ____ attribute is one that cannot be subdivided.

A. composite
B. atomic
C. binary-valued
D. multivalued

B. An Atomic Attribute is another term for Simple Attribute, which refers to a single value and cannot be broken down further.

If an entity can exist apart from one or more related entities, it is said to be ____-independent.

A. existence
B. relationship
C. business
D. weak

A.

A ____ relationship exists when three entities are associated.

A. unary
B. binary
C. ternary
D. weak

C. Unary means 1, Binary means 2, and Ternary means 3, which is also how many entities exist in each type of relationship.

The set of possible values for an attribute is a ____.

A. domain
B. range
C. set
D. key

A. A domain is a collection of rules that describe available values for a field type.

Which attribute(s) make up the primary key in the table definition: CLASS (CRS_CODE, CLASS_SECTION, CLASS_TIME, CLASS_ROOM, PROF_NUM)

A. CRS_CODE
B. CLASS_SECTION
C. CRS_CODE and CLASS_SECTION
D. There is no primary key

C. The underlined values represent the two columns in a Composite Primary Key for this table.

A table that has all key attributes defined, has no repeating groups, and all its attributes are dependent on the primary key, is said to be in ____.

A. 1NF
B. 2NF
C. 3NF
D. 4NF

A. 1NF has the fewest requirements and is the most basic level of normalization.

A table that is in 2NF and contains no transitive dependencies is said to be in ____.

A. 1NF
B. 2NF
C. 3NF
D. 4NF

C. 2NF is a more strict form of normalization than 1NF.

Data redundancy produces ____.

A. slower lookups
B. robust design
C. efficient storage use
D. data integrity problems

D. Data redundancy can result in the same data needing to be updated in multiple locations, introducing more opportunity to violate data integrity.

Normalization works through a series of normal ____.

A. schemas
B. entities
C. databases
D. forms

D. Normal forms are rules for designing tables with less redundancy.

Dependencies based on only a part of a composite primary key are called ____ dependencies.

A. primary
B. partial
C. incomplete
D. composite

B. Partial dependency in a relational database occurs when a non-prime attribute is functionally dependent on only part of a composite key rather than the whole key.

Which of the following is a partial dependency?

A. PROJ_NUM --> PROJ_NAME

B. PROJ_NAME --> HOURS

C. PROJ_NUM, EMP_NUM --> HOURS

D. PROJ_NUM, EMP_NUM --> PROJ_NAME

A. PROJ_NUM --> PROJ_NAME shows a non-key field dependent on only one part of a composite primary key, resulting in a partial dependency.

A relation is not in 1NF if _______.

A. it has multiple candidate keys
B. all the key attributes are defined
C. there are repeating groups in the table
D. all attributes are dependent on the primary key

C. 1NF requires each column to have a unique name and each cell to only contain a single value.

The SQL command that lets you insert data into a table, one row at a time, is ____.

A. INSERT
B. SELECT
C. COMMIT
D. UPDATE

A. INSERT INTO is the Data Manipulation Language (DML) clause to add data to a table.

The SQL command that enables you to make changes in the data is ____.

A. INSERT
B. SELECT
C. COMMIT
D. UPDATE

D. UPDATE is the Data Manipulation Language (DML) clause to modify data in a table.

To list all the contents of the PRODUCT table, you would use ____.

A. LIST * FROM PRODUCT;
B. SELECT * FROM PRODUCT;
C. DISPLAY * FROM PRODUCT;
D. SELECT ALL FROM PRODUCT;

B. Using the wildcard character * in a SELECT statement returns all values that satisfy the conditions of the statement.

Which command would you use when making corrections to the PRODUCT table?

A. CHANGE PRODUCT …
B. ROLLBACK PRODUCT …
C. EDIT PRODUCT …
D. UPDATE PRODUCT …

D.

Which command would be used to delete the table row where the P_Code = '2238/QPD'?

A. DELETE FROM PRODUCT

WHERE P_CODE = '2238/QPD';

B. REMOVE FROM PRODUCT

WHERE P_CODE = '2238/QPD';

C. ERASE FROM PRODUCT

WHERE P_CODE = '2238/QPD';

D. ROLLBACK FROM PRODUCT

WHERE P_CODE = '2238/QPD';

A.

Which command is used to select partial table contents?

A. SELECT <column(s)>

FROM <Table name>

WHERE <Item>;

B. LIST <column(s)>

FROM <Table name>

WHERE <Conditions>;

C. SELECT <column(s)>

FROM <Table name>

WHERE <Conditions>;

D. LIST<column(s)>

FROM <Table name>

WHERE <Item>;

C.

Which query will output the table contents when V_CODE <= 21344?

A. SELECT … WHERE V_CODE <> 21344;

B. SELECT … WHERE V_CODE <= 21344;

C. SELECT … WHERE V_CODE => 21344;

D. SELECT … WHERE V_CODE = 21344;

B. The proper syntax to select the specific values in this question is WHERE V_CODE <=21344

Which query will list all rows on or after January 20, 2006?

A. … >= '2006-01-20'
B. … >= #01/20/2004#
C. … >= '20-JAN-2004'
D. … >= {01-20-2004}

A. Single quotes are required on dates as the – symbol is a reserved operator for arithmetic functions.

Which query selects V_CODE = 21344 OR V_CODE = 24288?

A. SELECT …

WHERE V_CODE = 21344

OR V_CODE <= 24288

B. SELECT …

WHERE V_CODE = 21344

OR V_CODE => 24288

C. SELECT …

WHERE V_CODE = 21344

OR V_CODE > 24288

D. SELECT …

WHERE V_CODE = 21344

OR V_CODE = 24288

D. The proper syntax in this question is a combination of both conditions using an OR operator.

What is the command to join the P_DESCRIPT and P_PRICE fields from the PRODUCT table and the V_NAME, V_AREACODE, V_PHONE, and V_CONTACT fields from the VENDOR table where the value of V_CODE match?

A.SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE

FROM PRODUCT, VENDOR

WHERE PRODUCT.V_CODE <> VENDOR.V_CODE;

B.SELECT " "

FROM PRODUCT, VENDOR

WHERE PRODUCT.V_CODE = VENDOR.V_CODE;

C.SELECT " "

FROM PRODUCT, VENDOR

WHERE PRODUCT.V_CODE <= VENDOR.V_CODE;

D.SELECT " "

FROM PRODUCT, VENDOR

WHERE PRODUCT.V_CODE => VENDOR.V_CODE;

B. Retrieving matching fields requires an = operator.

The LIKE operator is used for ____.

A. BETWEEN
B. IS NULL
C. pattern matching
D. IN

C. The LIKE operator combined with wildcard characters can evaluate string values.

DROP is used to ____.

A. remove a table
B. insert data
C. update data
D. select data

A. DROP is the Data Definition Language statement to remove a table from the database.

UPDATE tablename

__________________

[WHERE conditionlist];

A. SET columnname = expression
B. columnname = expression
C. expression = columnname
D. LET columnname = expression

A. UPDATE is the Data Definition Language statement to change data in a table and requires a SET statement to identify new values.

A ____ join returns unmatched rows from both tables.

A. natural
B. cross
C. full outer
D. left outer

C. FULL JOIN is the only join which returns unmatched data from both tables.

ETL transformation step involves ____.

A. extraction
B. transformation
C. loading
D. denormalization

A. During the transformation step, a series of rules or functions is applied to the extracted data and can involve transformations such as data summations, encoding, merging, splitting, calculations, and creation of surrogate keys.

Key ETL loading issue is ____.

A. mapping keys
B. refresh volume/frequency
C. content determination
D. denormalization

B. The loading component of the ETL process is centered on moving transformed data into the data warehouse. Key issues include target dependencies and refresh volume/frequency.

Scenario: Each teacher is associated with exactly one campus. The address in the table is the address for the campus where the teacher teaches.

What change would you need to make to normalize the database to the third normal form (3NF)?

A. Combine the FirstName and LastName columns into a single column.

B. Combine the Campus, Address, City, State, Country, and PostalCode columns into a single column.

C. Create a separate table for campus address information. Use the Campus as the primary key. Add a TeacherID column to the table and relate it to the Teacher ID column in the Teachers table.

D. Create a separate table for campus address information. Use Campus as the primary key for the table. Create a foreign key in the Teachers table that relates teacher to the campus.

D. In the Teachers table, the address fields are related to the Campus field instead of the Primary Key TeacherID. Moving those fields to a new table using Campus as a primary key results in 3rd normal form.

Which statement will remove all rows from the Materials table that have a Status value of ‘Obsolete’ but do not have a value for the VendorID column?

A. DELETE Materials

WHERE Status = ‘Obsolete’ OR VendorID IS NULL

B. DELETE FROM Materials

WHERE Status = ‘Obsolete’ AND VendorID IS NULL

C. DELETE MaterialID, Description, Status, VendorID

FROM Materials

WHERE Status = ‘Obsolete’ AND VendorID IS NULL

D. DELETE FROM Materials

WHERE Status = ‘Obsolete’

WHERE VendorID IS NULL

B. When referring to cells that contain no data the proper operator is IS NULL.

This table has these characteristics:

• Each student has a unique StudentID that references the Students table.

• A specific test can be taken more than once.

• Each instructor has a unique InstructorID that references the Instructors table

• Each student may take multiple tests on the same date.

• Each student may take tests on multiple dates.

What column or columns should you use for the primary key?

A. StudentID, Date

B. StudentID, Test, Date, InstructorID

C. StudentID, Test, and Date

D. Test

C. Since StudentID and InstructorID are foreign keys and there are no other unique values, it is necessary to combine fields to form a Composite Primary Key.

Scenario: You are creating a relational database to store information about instructors and the courses that each instructor teaches. Each course is taught by a single instructor. You have created an Instructor table and a Course table as shown above. You need to create a relationship between the Instructor table and the Course table. You need to keep duplicate data to a minimum. How would you do this?

A. Create a new column in the Instructor table.

B. Create new columns in the Instructors table for each course taught.

C. Create a new table that includes two columns.

D. Create a new column in the Course table

D. To connect these tables, including the InstructorID would be a viable foreign key in the Course table.

Which of the following is structured data?

A. images
B. emails
C. database records
D. audio/video

C. Structured data is data that has a standardized format and can be processed into information.

Scenario: Agents at the NWREA may be assigned as a mentor to zero or more other agents. However, each agent being mentored is mentored by only one NWREA agent. Which kind of relationship does the diagram depict?

A. Unary one-to-many

B. Binary many-to-many

C. Unary many-to-many

D. Binary one-to-many

A. A single table makes this a Unary relationship, and the outer icons indicate a max of 1 on one side and many on the other.

Scenario: Agents at the NWREA are assigned as a mentor to one other agent. Each NWREA mentor can only work with one agent at a time. Further, each agent is only mentored by one PNREA agent. However, while being mentored is required, being a mentor is not. Which kind of relationship does the diagram depict?

A. Unary one-to-many

B. Binary many-to-many

C. Unary one-to-one

D. Binary one-to-many

C. Because each mentor can only have one agent and each agent only one mentor, the relationship is Unary one-to-one.

What is the modality of appointment?

A. At least one

B. Dependent on the quantity

C. Two

D. Two or more

A. Modality is a description of the minimum number of values on one side of a relationship.

What form is this table in?

A. Unnormalized

B. 1NF

C. 2NF

D. 3NF

A. Because this table contains multiple non-unique rows it does not satisfy the criteria for any normal forms.

What form is this table in?

A. Unnormalized

B. 1NF

C. 2NF

D. 3NF

A. This table has not been normalized because there are repeating groups and a multivalued field.

What form is this table in?

A. Unnormalized

B. 1NF

C. 2NF

D. 3NF

C. The table is in 2NF but has a transitive dependency on Coordinator ID that must be resolved for 3NF.

What form is this table in?

A. Unnormalized

B. 1NF

C. 2NF

D. 3NF

B. This table is still in 1NF because of partial dependencies. Item Name and Item Price depend only on part of the key.