Module2 Flashcards
Select all of the following that might be represented with a multivalued attribute. (Choose all that apply.)
a. Household phone numbers
b. A person’s first name
c. A flag’s color
d. The ISBN code for a book
e. A bank account balance
a. Household phone numbers
c. A flag’s color
Multivalued attributes are attributes that can have many values.
The ERD below represents a way to store a multivalued attribute, by storing each value that comprises the attribute in a single table. Select all statements that correctly describe this design.
a. If another variant such as VALUE4 needs to be added, the structure of the table must be modified, and this is disadvantageous.
b. If a particular instance of TABLE does not have some of the value variants, then a null must be inserted for that variant.
c. The above design does not work at all. That is, a multi-valued attribute cannot be stored this way.
d. This design is ideal for multivalued attributes that consist of more than 20 values, especially if most the values will be null.
A, B
Select all that are true of the different types of attributes. (Choose all that apply.)
a. A single-valued attribute is an attribute that has only a single value, such as the weight of a coin.
b. A multi-valued attribute is an attribute that can have many values at once, such as the colors of a flag.
c. A simple attribute can be subdivided into its composite parts, for example, a name attribute could be a simple attribute that is subdivided into first_name and last_name attributes.
d. A composite attribute is an attribute with more than one simple component. For example the simple attributes first_name and last_name can be combined to form the composite attribute name.
A, B, D
Select all statements that are good business rules for data modeling, that is, that are business rules that directly affect the structure of the database.
a. A machine operator will be working from home or from the office.
b. Casual Fridays take place in the summer.
c. A customer may make many payments on an account.
d. An account can be owned by many customers.
e. A training session cannot be scheduled for fewer than 10 employees or more than 30 employees.
C, D, E
As important as casual Fridays are, this business rule is not likely to effect entities, relationships and constraints within the database and does not effect the data modeling. Also, a machine operator working from home or the office will not effect the data modeling.
You are given the following business rules:
A person may own any number of credit cards, including none.
A credit card must be owned by exactly one person.
Choose which is true about the statement:
a
b. According to the business rules a person can have a maximum of 100 credit cards.
c. According to the business rules a person may own 15 credit cards.
d. This is a many-to-many (M:N) relationship
e. This is a one-to-many (1:M) relationship
f.
C, E, F
Select all that are true of a candidate key. (Choose all that apply.)
a. A candidate key is always a multi-attribute key.
b. A candidate key can be a foreign key.
c. A candidate key cannot be composite.
d. A primary key must be a candidate key.
e. A candidate key can be classified as a minimal superkey, that is, one that does not contain a subset of attributes that is itself a superkey.
B, D, E
A candidate key can be classified as a minimal superkey, that is, one that does not contain a subset of
attributes that is itself a superkey.
Select all that are true of cardinality in relation to databases. (Choose all that apply.)
a. Identifying cardinalities is an important part of a database designer’s job.
b. The Crow’s foot and UML diagrams place the cardinalities next to the entity to which cardinalities apply.
c. The existence of a mandatory relationship indicates that the minimum cardinality is at least 1 for the mandatory entity.
d. Cardinality expresses only the maximum number of entity occurrences associated with one occurrence of the related entity.
A, B, C
Select the correct SQL query that returns all records from PRODUCT where V_CODE does NOT equal 21344. (Choose all that apply.)
a. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT
WHERE V_CODE =! 21344;
b. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT
WHERE V_CODE == 21344;
c. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT
WHERE V_CODE <> 21344;
d. SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE FROM PRODUCT
WHERE V_CODE != 21344;
C, D
SQL is used to create tables and relationships in a DBMS. Select all which must be included in the SQL commands in order to completely and correctly create two well-designed tables with a foreign key relationship between them.
a. The table and column names must be defined.
b. The datatypes for each column must be defined.
c. The primary, unique, and foreign key constraints must be defined.
d. One dummy row must be inserted into each table to clarify to the DBMS how the tables are related.
A, B, C
A property management company has, as part of its database design, the following entity relationship diagram:
In this design, TENANCY can be described as (select all answers that are correct):
a. a weak entity
b. an entity whose instances cannot exist independently of Apartment instances
c. a strong entity
d. an entity that inherits part of its primary key from Apartment
A, B, D
In the context of a database table, the statement “A determines B” indicates that ____.
a. you need to know the value of attribute A in order to look up the value of attribute B
b. knowing the value of attribute A, you can look up the value of attribute B
c. knowing the value of attribute B, you can look up the value of attribute A
d. knowing the value of attribute A, you cannot look up the value of attribute B
e. you do not need to know the value of attribute A in order to look up the value of attribute B
A, B
A student was asked to create an ER diagram using Crow’s Foot notation given the following business rules:
- A car must have only one make.
- A make may be made into many cars.
- A make may never be made into a car.
The student created the following solution:
Identify all problems with this solution.
a. The leftmost symbol adjacent to the CAR entity should be the symbol.
b. The leftmost symbol adjacent to the CAR table should be the symbol.
c. The rightmost symbol adjacent to the CAR table should be the symbol.
d. The symbol adjacent to the MAKE table should be the symbol.
e. The symbol adjacent to the MAKE table should be the symbol.
B, C, D, E
View the ER diagram using Crow’s Foot notation below, and follow the subsequent instructions.
MANUFACTURER_ID in the PRODUCT table is _______________ (select all answers that correctly complete this sentence).
a. a primary key
b. an attribute reflecting controlled redundancy.
c. an attribute reflecting uncontrolled redundancy
d. a composite key
e. a foreign key
B
Select the correct entity relationship diagram which matches the requirements for the HOUSE and DOOR entities below:
- HOUSE and DOOR have an identifying relationship
- DOOR is a weak entity
- HOUSE is not considered optional to DOOR, and DOOR is not optional to HOUSE
A
Select all statements that correctly describe the Crow’s Foot notation in an ERD.
a. In Crow’s Foot notation, a many end of a relationship is indicated by a branching symbol similar to a
crow’s foot.
b. Crow’s foot notation is defined by the Unified Modeling Language (UML).
c. Crow’s Foot notation does not support any form of cardinality.
d.I n Crow’s Foot notation, an optional relationship between entities is shown by drawing a small circle on
the side of the optional entity.
A, D
Which of the following are steps for building an entity relationship diagram (ERD)? (Choose all that apply.)
a. Write application code to implement the business rules.
b. Identify the main entities and relationships from the business rules.
c. Create hardware and software requirements.
d. Identify the attributes and keys of the entities.
e. Develop the initial ERD.
f. Identify the entities for the problem domain to be modeled.
B, D, E, F
Select all that is true of logical and physical entity-relationship models. (Check all that are true.)
a. A logical entity-relationship model is independent of software, but dependent on hardware.
b. A physical entity-relationship model is the representation of the database as “seen” by the DBMS.
c. A logical entity-relationship model requires the definition of both the physical storage devices and the
access methods required to reach the data within those storage devices.
d. A logical entity-relationship model is dependent upon the problem domain as well as the particular
implementation model, e.g., the relational or object-oriented model.
e. A physical entity-relationship model operates at the highest level of abstraction, describing the way data
are saved on storage media such as disks or tapes.
D
The logical entity-relationship model is dependent upon the particular implementation model, typically a SQL-based relational model. Additionally, a logical entity-relationship diagram also depends upon the problem domain. The conceptual model depends upon only the problem domain, the logical model depends upon both the problem domain and the implementation model, and the physical model depends upon the problem domain, the implementation model, and the particular DBMS chosen for
implementation.
Recall that, with respect to the entity relationship model, relationship classifications are distinct from relationship
connectivities, as well as cardinalities. Select all of the alternatives below that identify legal relationship classifications in the entity relationship model.
a. three-to-five
b. many-to-many
c. zero-to-many
d. one-to-many
e. one-to-one
B, D, E
A relationship connectivity indicates the upper and lower bound of a single relationship endpoint. One example is “0..1”, meaning “there may be no instances, or there may be at most one instance.” Another
Which of the following are valid definitions of the entity relationship model (ERM)? (Choose all that apply.)
a. The entity relationship model (ERM) forms the basis of an entity relationship diagram (ERD), and the
ERD represents the logical database as viewed by the end user.
b. In the entity relationship model (ERM), attributes are types of entities.
c. The entity relationship model (ERM) refers to a specific table row as an entity instance.
d. The entity relationship model (ERM) describes relationships among entities at the conceptual level with
the help of entity relationship diagrams (ERD)
A, C, D
Review the diagram below, then select all statements that correctly describe the diagram.
a. CUS_LNAME is an entity.
b. INVOICE is an entity.
c. CUS_PHONE is an entity.
d. CUSTOMER is an entity.
e. The attribute CUS_CODE inside of the Customer table is a foreign key.
f. CUS_CODE is a foreign key of Customer.
g. INV_NUMBER is the primary key of Invoice.
B, D, G
Select all conditions which make an entity a weak entity? (Choose all that apply.)
a, The entity is existence dependent.
b. The weak entity can exist apart from its related entities.
c. The entity has a primary key that is partially or totally derived from another entity’s primary key.
d. The entity is existence independent.
A, C
Select all that are true of foreign keys in relational databases.
a. A foreign key may reference the same table.
b. Foreign keys must always include the primary key of the referencing table.
c. A table may have any number of foreign keys.
d. A foreign key is an attribute or group of attributes the values of which are constrained by a foreign key
constraint.
A, C, D
Select all that is true of functional dependency.
a. To be functionally dependent an attribute must depend upon another single attribute, not on multiple
attributes.
b. Partial functional dependency is a condition in which an attribute is functionally dependent on a composite
key but not on any subset of that composite key.
c. If attribute B is functionally dependent on attribute A, and attribute C is functionally dependent on attribute
B, (which is written A -> B -> C), then attribute C is functionally dependent on attribute A.
d. 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).
e. For a relation to exhibit functional dependency, the relation must have a primary key.
C, D
Select all that are true about database indexes:
a. An index is an ordered arrangement of keys and pointers
b. An index can be associated with many tables
c. An index can have multiple attributes
d. Indexes are always created automatically by the DBMS
e. A table can have many indexes
A, C, E
Check all of the following which are uses for an index in an RDBMS:
a. preventing duplicate values in different rows for a set of columns
b. restoring table contents after an erroneous change.
c. grouping attributes.
d. improving the efficiency of searches.
A, D
A large retail business hires you to solve a database problem. Using SQL, they have defined the following table:
CREATE TABLE Line_item(
line_item_id DECIMAL NOT NULL PRIMARY KEY,
prod_name VARCHAR(255),
quantity DECIMAL(8), extension DECIMAL(8,2) );
They tell you that the above table has billions of rows. Their problem is that queries in the pattern of the following SQL
query take too long to execute.
SELECT line_item_id, quantity, extension
FROM Line_item
WHERE prod_name = ‘hammer’;
Create a unique index on the line_item_id column.
b. Create a regular index on the extension_name column.
c. Create a unique index on the quantity column.
d. Create a regular index on the prod_name column.
D
Indexes are very important for databases. Select all that are true about database indexes.
a. An index is usually created to improve database performance.
b. A table can have a maximum of three indexes.
c. An index speeds select performance, but it has no effect on the update, delete or insert performance.
d. An index is always created automatically when a table is created.
e. An index is a data structure used to efficiently access data.
A, E
Referential integrity requires that ____.
a. every non-null foreign key value must reference an existing primary key value
b. every null foreign key value must reference an existing primary key value
c. an attribute have a corresponding value
d. you cannot delete a row in one table whose foreign key refers to a primary key in another table
e. you cannot delete a row in one table whose primary key has a foreign key value in another table that
references it
A, E
Select all that are true of relational database integrity rules
a. All primary keys are unique and may be null.
b. A foreign key can have a null entry, as long as it’s not part of the primary key.
c. If the foreign key contains either matching values or nulls, the table that makes use of that foreign key is
said to exhibit referential integrity.
d. A table exhibits entity integrity when a primary key only has one null value.
B, C
Select all that are true of NULL values
a. A null value can be part of a primary key.
b. A blank value is the same as a null value in a character column in a database.
c. A zero is the same as a null value in a decimal column in a database.
d. The AVERAGE of 1, 2, and NULL is 1.
e. A zero length string is the same as a null.
f. There is never a good reason to use null values in a database.
g. A null signifies that there is no value at all.
G
Which of the following are true of database relationships?
a. A unary relationship exists when an association is maintained within a single entity.
b. A weak relationship exists when only two entities are associated.
c. A recursive relationship is one in which a relationship can exist between occurrences of the same.
d. A ternary relationship exists when three entities are associated.
e. A binary relationship is the most common in databases.
A, C, D, E
A table is created with the following SQL command. Assume the syntax of the command is correct.
CREATE TABLE toothbrush (
toothbrush_id DECIMAL PRIMARY KEY,
toothbrush_name VARCHAR(255) NULL,
toothbrush_length DECIMAL NOT NULL
);
Select all SQL commands that will succeed after the Toothbrush table is defined as above.
a. INSERT INTO toothbrush VALUES(1, NULL, 50);
b. INSERT INTO toothbrush VALUES(1, 50, ‘ninety nine’);
c. INSERT INTO toothbrush VALUES(toothbrush_id, toothbrush_length) VALUES(1, 50);
d. INSERT INTO toothbrush(toothbrush_id, toothbrush_name, toothbrush_length) VALUES(1, null, 50)
e. UPDATE toothbrush set toothbrush_name=’Big’ where toothbrush_id=1
f. UPDATE toothbrush set toothbrush_length=null where toothbrush_id=1
A,D, E
CREATE TABLE plant (
plant_num DECIMAL PRIMARY KEY,
plant_species VARCHAR(255) NOT NULL,
plant_common_name VARCHAR(255) NULL
);
INSERT INTO plant VALUES (1, ‘Ficus Benjamina’, ‘Weeping Fig’);
INSERT INTO plant VALUES (2, ‘Ficus Benjamina’, ‘Benjamins Fig’);
INSERT INTO plant VALUES (3, ‘Chamaedorea Seifritzii’, ‘Bamboo Palm’);
Select the best indication of what should happen when the following SQL command is executed.
INSERT INTO plant
SELECT plant_num, plant_species, plant_common_name FROM plant;
a. An error should be generated indicating that the SQL syntax is incorrect.
b. An error should be generated indicating that the unique or primary key constraint of plant_num has
been violated.
c. The INSERT statement will successfuly insert three rows into the PLANT table.
d. The INSERT statement will successfully insert one row into the PLANT table.
B
Assuming that the SQL INSERT statements below execute without error, select all alternatives that correctly describe
those statements.
INSERT INTO Customer(id, first, last, state, email)
VALUES (1245,’Jimmy’,’Smith’,’CA’, NULL);
INSERT INTO Customer (id, first, last, state, email)
VALUES (1246,’Jamie’,’Smith’,’CA’, NULL);
a. Each INSERT statement above adds only one table row.
b. There are exactly five columns in the Customer table.
c. After execution of both statements above, two records will be added to the Customer table.
d. There is at least one nullable attribute in the Customer table.
A, C, D
Consider the following query:
SELECT employee.emp_id, task.task_num
FROM employee, task
WHERE employee.emp_id = task.emp_id;
What kind of join is used to combine the EMPLOYEE and TASK tables in this query?
a. A cross join.
b. A left outer join.
c. An inner join.
d. A full outer join.
e. A right outer join.
C
Consider the following tables:
CREATE TABLE Camera(
camera_id DECIMAL(6) NOT NULL PRIMARY KEY,
camera_price DECIMAL(6,2)
);
CREATE TABLE Invoice(
invoice_id DECIMAL(8) NOT NULL PRIMARY KEY,
camera_id DECIMAL(6) NOT NULL
);
Choose all JOIN syntaxes that will produce the same results as the following query regardless of the data present in the
tables:
SELECT invoice_id, camera_price
FROM Camera, Invoice
WHERE camera.camera_id = invoice.camera_id;
a. SELECT invoice_id, camera_price
FROM Camera NATURAL JOIN Invoice;
b. SELECT invoice_id, camera_price
FROM Camera JOIN Invoice
USING (camera_id);
c. SELECT invoice_id, camera_price
FROM Camera FULL OUTER JOIN Invoice
ON camera.camera_id = invoice.camera_id;
d. SELECT invoice_id, camera_price
FROM Camera JOIN Invoice
ON camera.camera_id = invoice.camera_id;
e. SELECT camera_price
FROM Camera CROSS JOIN Invoice;
A, B, D
There are three types of outer joins – full outer join, right outer join, and left outer join. Choose all which are true about
these join types (Select all which apply).
