FINAL04 CISC3000 Final Review - Quiz

:::info Quizzes #06 and #07
Quiz 06 - Chapter 06

• Database Design Using the E-R Model
• Reducing E-R Diagrams to Relational Schemas

Quiz 07 - Chapter 07

• Relational Database Design :::

Quiz 06 - Chapter 06

Question 1

__ is a special type of integrity constraint that relates two relations & maintains consistency across the relations.
Select one:
a. Domain Constraints
b. Domain Integrity Constraints
c. Referential Integrity Constraints
d. Entity Integrity Constraints

Question 2

__ is preferred method for enforcing data integrity
Select one:
a. Triggers
b. Constraints
c. Cursors
d. Stored Procedure

:::tips Constraints are specified to restrict entries in the relation :::

Question 3

A _ constraint requires that an entity belong to no more than one lower-level entity set.
Select one:
a. Disjointness
b. Uniqueness
c. Relational
d. Special

:::tips For example, student entity can satisfy only one condition for the student type attribute;
an entity can be either a graduate student or an undergraduate student, but cannot be both. :::

Question 4

A window into a portion of a database is
Select one:
a. Data dictionary
b. Query
c. View
d. Schema

:::tips View is a logical portion of a database which needed by some users. :::

Question 5

An entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A.This is called as
Select one:
a. Many-to-many
b. One-to-one
c. Many-to-one
d. One-to-many

:::tips Here one entity in one set is related to one entity in other set. :::

Question 6

Drop Table cannot be used to drop a table referenced by a _ constraint.
Select one:
a. Primary Key
b. Local Key
c. Composite Key
d. Foreign Key

:::tips Foreign key is used when primary key of one relation is used in another relation. :::

Question 7

Entity is a _
Select one:
a. Thing in real world
b. Object of relation
c. Model of relation
d. Present working model

:::tips For example, each person in a university is an entity. :::

Question 8

Functional dependencies are a generalization of
Select one:
a. Key dependencies
b. Relation dependencies
c. Database dependencies
d. None of the mentioned

:::tips The subclasses are combined to form the superclass. :::

Question 9

Given the basic ER and relational models, which of the following is INCORRECT?
Select one:
a. An attribute of an entity can be composite
b. In a row of a relational table, an attribute can have exactly one value or a NULL value
c. In a row of a relational table, an attribute can have more than one value
d. An attribute of an entity can have more than one value

:::tips It is possible to have several values for a single attribute provide it is a multi-valued attribute :::

Question 10

If you were collecting and storing information about your music collection, an album would be considered a(an) _
Select one:
a. Entity
b. Attribute
c. Relation
d. Instance

:::tips An entity set is a logical container for instances of an entity type and instances of any type derived from that entity type. :::

Question 11

In E-R diagram generalization is represented by
Select one:
a. Rectangle
b. Ellipse
c. Triangle
d. Dashed ellipse

:::tips Ellipse represents attributes;
Rectangle represents entity.

:::

Question 12

Key to represent relationship between tables is called
Select one:
a. Primary key
b. Secondary Key
c. Foreign Key
d. None of the mentioned

:::tips Primary key of one relation used as an attribute in another relation is called foreign key. :::

Question 13

The attribute AGE is calculated from DATE_OF_BIRTH . The attribute AGE is
Select one:
a. Derived
b. Single valued
c. Multi valued
d. Composite

:::tips The value for this type of attribute can be derived from the values of other related attributes or entities. :::

Question 14

The entity relationship set is represented in E-R diagram as
Select one:
a. Diamond
b. Undivided rectangles
c. Dashed lines
d. Double diamonds

:::tips
Dashed lines link attributes of a relationship set to the relationship set.
:::

Question 15

The function that an entity plays in a relationship is called that entity’s _
Select one:
a. Instance
b. Participation
c. Role
d. Position

:::tips A relationship is an association among several entities. :::

Question 16

The total participation by entities is represented in E-R diagram as
Select one:
a. Circle
b. Double rectangle
c. Dashed line
d. Double line

:::tips It is used to represent the relation between several attributes
:::

Question 17

Weak entity set is represented as
Select one:
a. Double line
b. Double rectangle
c. Underline
d. Double diamond

:::tips In E-R diagrams, a weak entity set is depicted via a double rectangle with the discriminator being underlined with a dashed line.
While the relationship set connecting the weak entity set to the identifying strong entity set is depicted by a double diamond.
:::

Question 18

Which of the following is a low level operator?
Select one:
a. Directory
b. Update
c. Delete
d. Insert

:::tips Directory is a low level to word on in file system. :::

Question 19

Which of the following is a single valued attribute?
Select one:
a. Hobby
b. Driving_license_number
c. Address
d. Phone

Question 20

Which of the following is another name for a weak entity?
Select one:
a. Child
b. Owner
c. Dominant
d. All of the mentioned

:::tips A parent may be called as a strong entity. :::

Quiz 07 - Chapter 07

Question 1

_ can help us detect poor E-R design.
Select one:
a. E-R Design Process
b. Functional dependencies
c. Relational scheme
d. Database Design Process

:::tips For e.g. , Suppose an instructor entity set had attributes dept_name and dept_address
there is a functional dependency dept_name -> dept_address . :::

Question 2

If a multivalued dependency holds and is not implied by the corresponding functional dependency, it usually arises from one of the following sources.
Select one:
a. A one-to-many relationship set
b. A multivalued attribute of an entity set
c. A many-to-many relationship set
d. Both A many-to-many relationship set and A multivalued attribute of an entity set

:::tips Explanation:
For a many-to-many relationship set each related entity set has its own schema and there is an additional schema for the relationship set.
For a multivalued attribute, a separate schema is created consisting of that attribute and the primary key of the entity set. :::

Question 3

Representations such as the in the deptyearrelation, with one column for each value of an attribute, are called _ they are widely used in spreadsheets and reports and in data analysis tools.
Select one:
a. Cross-tabs
b. Snapshot
c. Both Cross-tabs and Snapshot
d. All of the mentioned

:::tips SQL includes features to convert data from a normal relational representation to a crosstab. :::

Question 4

The normal form which satisfies multivalued dependencies and which is in BCNF is
Select one:
a. 4 NF
b. 3 NF
c. 2 NF
d. All of the mentioned

:::tips Explanation:
Fourth normal form is more restrictive than BCNF. :::

Question 5

Which of the following has each related entity set has its own schema and there is an additional schema for the relationship set?
Select one:
a. A many-to-many relationship set
b. A multivalued attribute of an entity set
c. A one-to-many relationship set
d. None of the mentioned

:::tips Explanation:
Multivalued dependencies, do not rule out the existence of certain tuples. Instead, they require that other tuples of a certain form be present in the relation.
:::

Question 6

A relation is in __ if an attribute of a composite key is dependent on an attribute of other composite key.
Select one:
a. 1NF
b. BCNF
c. 3NF
d. 2NF

:::tips A relation is in 3NF if an attribute of a composite key is dependent on an attribute of other composite key.
(If an attribute of a composite key is dependent on attribute of other composite key then the relation is not in BCNF, hence it has to be decomposed). :::

Question 7

A table has fields F1, F2, F3, F4, and F5, with the following functional dependencies:
{F1 -> F3,
F2 -> F4,
(F1,F2) -> F5,
in terms of normalization, this table is in
Select one:
a. 1NF
b. 2NF
c. 3NF
d. None of the mentioned :::tips Since the primary key is not given we have to derive the primary key of the table.
Using the closure set of attributes we get the primary key as (F1, F2).
From functional dependencies, “ F1 -> F3, F2 -> F4”, we can see that there is partial functional dependency therefore it is not in 1NF.

Hence the table is in 1NF. ::: 语雀内容

Question 8

Consider a relation R(A,B,C,D,E) with the following functional dependencies:
{ A B C -> D E and
D -> A B. }
The number of superkeys of R is:
Select one:
a. 2
b. 7
c. 10
d. 12

:::tips Explanation:
A superkey is a combination of columns that uniquely identifies any row within a relational database mangement system (RDBMS) table.
Here we have
ABCDE, ABC, ABCD, ABCE,
ACD, ACDE,
BCD, BCDE,
DC, DCE :::

Question 9

Empdt1 (empcode, name, street, city, state, pincode)
For any pincode, there is only one city and state. Also, for given street, city and state, there is just one pincode. In normalization terms, empdt1 is a relation in
Select one:
a. 2 NF and hence also in 1 NF
b. 3NF and hence also in 2NF and 1NF
c. 1 NF only
d. BCNF and hence also in 3NF, 2NF and 1NF

:::tips The relation in second normal form is also in first normal form and no partial dependencies on any column in primary key. :::

Question 10

In 2NF
Select one:
a. No partial MVDs exist
b. No partial FDs exist
c. No functional dependencies (FDs) exist
d. No multivalued dependencies (MVDs) exist

:::tips Explanation:
If a multivalued dependency holds and is not implied by the corresponding functional dependency, it usually arises from this source. :::

Question 11

Inst_dept (ID, name, salary, dept_name, building, budget) is decomposed into
  instructor (ID, name, dept_name, salary)
  department (dept_name, building, budget)

This comes under
Select one:
a. Lossy-join decomposition
b. Lossy decomposition
c. Lossless-join decomposition
d. Both Lossy and Lossy-join decomposition

:::tips Explanation:
Lossless-join and lossless decomposition have the same meaning as well as lossy-join and lossy decomposition are also the same.

Lossless Decomposition

instructor ∩ department = { dept_name } and dept_name -> building, budget,
it is a lossless decomposition. :::

Question 12

Let R(A,B,C,D,E,P,G) be a relational schema in which the following FDs are known to hold:
{AB->CD, DE->P, C->E, P->C, B -> G}.
The relation schema R is
Select one:
a. in 3NF, but not in BCNF
b. not in 2NF
c. in 2NF, but not in 3NF
d. in BCNF

:::tips Explanation:
From the closure set of attributes we can see that the key for the relation is AB.
The FD { B -> G } is a partial dependency, hence it is not in 2NF. :::

Question 13

R (A,B,C,D) is a relation. Which of the following does not have a lossless join dependency preserving BCNF decomposition?
Select one:
a. AB -> C, C -> AD
b. A->B, B->CD
c. A -> BCD
d. A->B, B->C, C->D

:::tips Explanation:
This relation does not have a lossless join dependency preserving BCNF decomposition.
Analyze the FDs for R(A,B,C,D):
For FD = { A -> B, B -> C, C -> D}, we decompose R in R1(A,B), R2(B,C) and R3 (C,D)
For FD = { A -> BCD }, which is equivalent to set = { A -> B, A -> C, A -> D }, we decompose R in R1(A,B), R2(A,C), R3(A,D).
For FD = { A -> B, B -> CD }, we decompose R in R1(A,B) and R2(B,C,D).
All of the above preserve BCNF decomposition.
For FD = { AB -> C, C -> AD }, we decompose R in R1 (A, B, C) and R2(C,D).
This preserves all dependencies and the join is lossless too, but the relation R1 is not in BCNF.
The FD { C -> A } violates the condition for R1 to be in BCNF as C is not a superkey.
The condition that all relations formed after decomposition should be in BCNF is not satisfied here. :::

Question 14

Relation dept_year(dept_name, total_inst_2007, total_inst_2008, total_inst_2009) . Here the only functional dependencies are from dept_name to the other attributes. This relation is in
Select one:
a. Fourth NF
b. Third NF
c. BCNF
d. Second NF

:::tips Explanation:
BCNF has only one normal form :::

Question 15

Tables in second normal form (2NF):
Select one:
a. Eliminate the possibility of a insertion anomalies
b. Have all non key fields depend on the whole primary key
c. Have a composite key
d. Eliminate all hidden dependencies

:::tips Explanation:
The relation in second normal form (2NF) is also in first normal form and no partial dependencies on any column in primary :::

Question 16

The main task carried out in the __ is to remove repeating attributes to separate tables.
Select one:
a. Third Normal Form
b. First Normal Form
c. Second Normal Form
d. Fourth Normal Form

:::tips Explanation:
Multivalued dependencies, do not rule out the existence of centain tuples.
Instead, they require that other tuples of a certain form be present in the relation. :::

Question 17

Thus a _ of course data gives the values of all attributes, such as title and department, of all courses at a particular point in time.
Select one:
a. Instance
b. Snapshot
c. Both Instance and Snapshot
d. All of the mentioned

:::tips Explanation:
We use the term snapshot of data to mean the value of the data at a particular point in time. :::

Question 18

Which forms are based on the concept of functional dependency:
Select one:
a. 1NF
b. 2NF
c. 3NF
d. 4NF

:::tips Explanation:
The table is in 3NF if every non-prime attribute of R is non-transitively dependent (i.e. Directly dependent) on every superkey of R. :::

Question 19

Which normal form is considered adequate for normal relational database design?
Select one:
a. 4NF
b. 2NF
c. 5NF
d. 3NF

:::tips Explanation:
A relational database table is often described as “normalized” if it is in the Third Normal Form (3NF) because most of the 3NF tables are free of insertion, update, and deletion anomalies. :::

Question 20

Which of the following is a tuple-generating dependencies?
Select one:
a. Functional dependency
b. Non-functional dependency
c. Equality-generating dependencies
d. Multivalued dependencies

:::tips Explanaiton:
Multivalued dependencies, do not rule out the existence of centain tuples.
Instead, they require that other tuples of a certain form be present in the relation. :::