C170 - Practice Test 2
If one department chair—a professor—can chair only one department, and one department can have only one department chair. The entities PROFESSOR and DEPARTMENT exhibit a(n) _____ relationship.
1:1
A table that is in 2NF and contains no transitive dependencies is said to be in _____. A) 1NF B) 2NF C) 3NF D) 4NF
3NF
Please describe the relationship:
A Painter paints at least one painting and could paint many paintings. A painting is painted by one and only one painter.
Please describe the relationship:
A professor teaches zero to many sections. A section is taught by one and only one professor.
In the relationship depicted above, which of the following is true? A) An instructor can teach a maximum of one course. B) A course can be taught be many instructors. C) A specific course is taught by one and only one instructor. D) A specific course can have multiple Start Dates.
A specific course is taught by one and only one instructor.
All changes to a table structure are made using the _____ command, followed by a keyword that produces the specific changes a user wants to make. A) ALTER TABLE B) UPDATE TABLE C) COMMIT TABLE D) ROLLBACK TABLE
ALTER TABLE
What is a characteristic of a database that is normalized to the third normal form? (Choose 2) A) All redundancy has been removed. B) There are no values that are dependent on only part of a composite primary key. C) There are no transitive dependencies. D) All data is stored in a single table.
All redundancy has been removed. There are no transitive dependencies.
Please describe the relationship:
An employee learns a minimum of one and a maximum of many skills. A skill is learned by one to many employees.
Please describe the relationship:
An employee manages one and only one store and a store is manages by one and only one employee.
A primary key is a(n) _____ key chosen to be the primary means by which rows of a table are uniquely identified.
Candidate
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. What should you do? 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.
Create a new column in the Course table.
The _____ constraint assigns a value to an attribute when a new row is added to a table. A) CHECK B) UNIQUE C) NOT NULL D) DEFAULT
DEFAULT
The _____ command would be used to delete the table row where the P_CODE is 'BRT-345'. A) DELETE FROM PRODUCT WHERE P_CODE = 'BRT-345'; B) REMOVE FROM PRODUCT WHERE P_CODE = 'BRT-345'; C) ERASE FROM PRODUCT WHERE P_CODE = 'BRT-345'; D) ROLLBACK FROM PRODUCT WHERE P_CODE = 'BRT-345';
DELETE FROM PRODUCT WHERE P_CODE = 'BRT-345';
A table can be deleted from the database by using the ___ command. A) DROP TABLE B) DELETE TABLE C) MODIFY TABLE D) ERASE TABLE
DROP TABLE
Please describe the relationship:
Each instructor teaches one or more courses
A(n) _____ links tables on the basis of an equality condition that compares specified columns of each table.
Equijoin
Dependencies that are based on only a part of a composite primary key are called transitive dependencies. A) True B) False
False
SQL requires the use of the ADD command to enter data into a table. A) True B) False
False
When joining three or more tables, you need to specify a join condition for one pair of tables. A) True B) False
False
The _____ clause of the GROUP BY statement operates very much like the WHERE clause in the SELECT statement.
HAVING
_____ relationships can be implemented by creating a new entity in 1:M relationships with the original entities. A) 1:N B) M:1 C) M:N D) 1:1
M:N
The _____ constraint can be placed on a column to ensure that every row in the table has a value for that column. A) UNIQUE B) NOT NULL C) VALUE D) EMPTY
NOT NULL
Which rule would you use in your CREATE TABLE statement for your Category table if you would like to have all products related to a specific category deleted if they are linked to a category that is deleted? A) ON DELETE CASCADE B) ON DELETE SET TO NULL C) ON DELETE RESTRICT D) ON UPDATE CASCADE
ON DELETE CASCADE
Which rule would you use in your CREATE TABLE statement for your Category table if you needed to prevent deletion of a category if there were associated products in the Product table? A) ON DELETE CASCADE B) ON DELETE SET TO NULL C) ON DELETE RESTRICT D) ON UPDATE CASCADE
ON DELETE RESTRICT
You are creating a database which will have the 2 tables shown above. You need to ensure that if a category is removed from the Category table, all related product rows would have their CategoryID set to a null value. What should you use? A) ON DELETE CASCADE B) ON DELETE SET TO NULL C) ON DELETE RESTRICT D) ON UPDATE CASCADE
ON DELETE SET TO NULL
Which of the following queries is used to list a unique value for V_CODE, where the list will produce only a list of those values that are different from one another? A) SELECT ONLY V_CODE FROM PRODUCT; B) SELECT UNIQUE V_CODE FROM PRODUCT; C) SELECT DIFFERENT V_CODE FROM PRODUCT; D) SELECT DISTINCT V_CODE FROM PRODUCT;
SELECT DISTINCT V_CODE FROM PRODUCT;
The SQL query to output the contents of the EMPLOYEE table sorted by last name, first name, and initial is _____. A) SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE LIST BY EMP_LNAME, EMP_FNAME, EMP_INITIAL; B) SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE ORDER BY EMP_LNAME, EMP_FNAME, EMP_INITIAL; C) SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE DISPLAY BY EMP_LNAME, EMP_FNAME, EMP_INITIAL; D) SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE SEQUENCE BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;
SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE FROM EMPLOYEE ORDER BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;
Entity integrity is enforced automatically when the primary key is specified in the CREATE TABLE command sequence. a) True b) False
True
If the attribute (B) is functionally dependent on a composite key (A) but not on any subset of that composite key, the attribute (B) is fully functionally dependent on (A). A) True B) False
True
Since a partial dependency can exist only if a table's primary key is composed of two or more attributes, if a table in 1NF has a single-attribute primary key, then the table is automatically in 2NF. A) True B) False
True
When a user issues the DELETE FROM tablename command without specifying a WHERE condition, _____. A) no rows will be deleted B) the first row will be deleted C) the last row will be deleted D) all rows will be deleted
all rows will be deleted
A _____ key can be described as a minimal superkey, a superkey without any unnecessary attributes. A) secondary B) candidate C) primary D) foreign
candidate
The CUSTOMER table's primary key is CUS_CODE. The CUSTOMER primary key column has no null entries, and all entries are unique. This is an example of _____ integrity. A) entity B) referential C) relational D) null
entity
A _____ is the primary key of one table that has been placed into another table to create a common attribute and establish a relationship. A) superkey B) composite primary key C) candidate key D) foreign key
foreign key
A(n) _____ only returns matched records from the tables that are being joined. A) outer join B) inner join C) equijoin D) theta join
inner join
Referential _____ dictates that the foreign key must contain values that match the primary key in the related table, or must contain null. A) integrity B) uniqueness C) model D) attribute
integrity
A(n) _____ join links tables by selecting only the rows with common values in their common attribute(s). A) attribute B) unique C) foreign D) natural
natural
Dependencies based on only a part of a composite primary key are known as _____ dependencies. A) primary B) partial C) incomplete D) composite
partial
The SQL data manipulation command HAVING: A) restricts the selection of rows based on a conditional expression. B) restricts the selection of grouped rows based on a condition. C) modifies an attribute's values in one or more table's rows. D) groups the selected rows based on one or more attributes.
restricts the selection of grouped rows based on a condition.
A(n) _____ is a query that is embedded (or nested) inside another query. A) alias B) operator C) subquery D) view
subquery
A _____ is any key that uniquely identifies each row. A) superkey B) special key C) foreign key D) partial key
superkey
A(n) _____ exists when there are functional dependencies such that Y is functionally dependent on X, Z is functionally dependent on Y, and X is the primary key. A) partial dependency B) repeating group C) atomic attribute D) transitive dependency
transitive dependency
When you define a table's primary key, the DBMS automatically creates a(n) _____ index on the primary key column(s) you declared. A) key B) composite C) unique D) primary
unique
A determinant is any attribute whose value determines other values within a column. A) True B) False
True
The query 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 values of V_CODE match is _____. 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 P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE; C) SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE <= VENDOR.V_CODE; D) SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE => VENDOR.V_CODE;
SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE;
UPDATE Customer ****** WHERE Customer_ID = 'CUS7878'; The _____ command replaces the ***** in the syntax of the UPDATE command, shown above. A) SET columnname = expression B) columnname = expression C) expression = columnname D) LET columnname = expression
SET columnname = expression
What is a characteristic of a database that is normalized to the first normal form? A) Tables contain only columns that are dependent on the table's primary key. B) All tables are related using foreign keys. C) Tables do not contain multi-valued or repeating fields. D) All data is stored in a single table.
Tables do not contain multi-valued or repeating fields.
What is a characteristic of a database that is normalized to the second normal form? A) Tables contain only columns that are dependent on the table's primary key. B) There are no values that are dependent on only part of a composite primary key. C) Tables do not contain multi-valued or repeating fields. D) All data is stored in a single table.
There are no values that are dependent on only part of a composite primary key.
