Database Design Spring Final Exam

true

A candidate key is an attribute or set of attributes that uniquely identifies individual occurrences of an entity type.

They differ in that the category is using Unions or ORs while the shared class using Intersections or ANDs

A category and a regular shared subclass:

Transitive Dependency

A condition where A, B, and C are attributes of a relation such that if A->B and B->C, then C is transitively dependent on A via B.

true

A many-to-many relationship of ER model can be simplified into two one-to-many relationships.

1NF (First Normal Form)

A relation in which the intersection of each row and column contains one and only one value

false Ex: non prime --> non prime is 3NF violation assuming 2NF The question claims the non prime --> prime which is violation of BCNF

A relation is in 3NF if it satisfies 2NF and not any prime attribute of R is transitively dependent on the any key.

2NF (Second Normal Form)

A relation that is in 1NF and every non-primary-key attribute is fully functionally dependent on the primary key

3NF (Third Normal Form)

A relation that is in 2NF and in which no non-primary-key attribute is transitively dependent on the primary key (no non-key attribute functionally determines another non-key attribute)

BCNF (Boyce-Codd Normal Form)

A relation that is in 3NF and in which every determinant is a candidate key

true

A relational schema is in 2NF if every nonprime attribute is fully functionally dependent on the primary key of R.

3 entities

A ternary relationship occurs when simultaneous relationship occurs among instances (records) of _______________.

true

A weak entity has no key among its attributes.

Male

All of the following would be instances of the entity PERSON except which? * David Jones * Angelina Rosalie * Mary Papas * Male

An instance of a type that is uniquely identifiable

An Entity is _________.

weak entity

An entity whose partial key depends on the primary key of another entity is called ___________.

Single-valued attribute

Attribute that can only have a single value is called _________________.

Derived Attribute

Attribute whose value is calculated from other attributes?

true

Attributes are inherited in Generalization.

true

BCNF means the realtion is in 3NF and for any functional dependency X --> Y, X is superkey or key of R.

* CD * AC

Check what could be a candidate key: A → B CD → E E → A B → D * CD * BD * AD * AC

One to one

Consider a directed line(->) from the relationship advisor to both entities instructor and student. This indicates _________ cardinality

* (5,3,4)

Consider a relation R(A) with r number of tuples, all unique within R, and a relation S(A) with s number of tuples, all unique within S. Let t represent the number of tuples in R minus S. Which of the following triples of values (r,s,t) is possible? * (5,3,1) * (8,2,2) . * (10,5,2) * (5,3,4)

* (2,3,6)

Consider a relation R(A,B) with r number tuples, all unique within R, and a relation S(B,C) with s number of tuples, all unique within S. Let t represent the number of tuples in R natural-join S. Which of the following triples of values (r,s,t) is possible? * (3,3,27) * (5,0,5) * (2,3,6)

* AC -->D, BCE --> A, CD -->E, CE --> B

Consider a relation R(A,B,C,D,E). For which of the following sets of FDs is R in Boyce-Codd Normal Form (BCNF)? * AC -->D, BCE --> A, CD -->E, CE --> B * BDE --> A, AC --> E, B --> C, DE --> A * BE --> D, B --> E, D --> E, CD --> A * ACD -->E, AE -->C, CE -->B, A --> D

EFG --> H

Consider a relation R(A,B,C,D,E,F,G,H) with the following set F of FDs. Which of FDs can be eliminated? F = { A → BCD AD → E EFG → H F → GH } * A --> BCD * AD --> E * EFG --> H * F --> GH

AD

Consider relation R(A,B,C,D,E) with functional dependencies: AB --> C, C --> D, BD --> E Which of the following sets of attributes does not functionally determine E? * BE * AD * BCD * BC

* ACD

Consider relation R(A,B,C,D,E) with functional dependencies: AB -->C, C --> D, BD --> E Which of the following sets of attributes does NOT functionally determine E? * BE * ABC * AB * ACD

* BCE Ex: BCE+ = ABCDE

Consider relation R(A,B,C,D,E) with functional dependencies: D --> C, CE --> A, D ---> A, AE --> D Which of the following is a key? * A * ABD * BD * BCE * No key is listed

* BCE

Consider relation R(A,B,C,D,E) with functional dependencies: D -->C, CE -->A, D --> A, AE --> D Which of the following is a key? * CE * A * BD * BCE

* ACDE

Consider relation R(A,B,C,D,E,F) with functional dependencies: CDE -->B, ACD --> F, BEF --> C, B --> D Which of the following is a key? * ABDF * ACDE * CD * BDF

* BDFG

Consider relation R(A,B,C,D,E,F,G) with functional dependencies: AB -->C, CD -->E, EF --> G, FG --> E, DE --> C, and BC --> A Which of the following is a key? * BDFG * ACDF * BCDE * BDEG

* The weak entity BANK_BRANCH depends on entity BANK and BANK_BRANCH has total participation. * The partial key of BANK_BRANCH is Branch_no.

Consider the ER diagram shown in Figure below for part of a BANK database. Select what is correct. https://solacc.instructure.com/assessment_questions/9218811/files/11501452/download?verifier=C48iRJ15kIfyMEy7LmL57KHgSodqZjhzNKPCbPXi&wrap=1 * The key of BANK_BRANCH is Branch_no. * The weak entity BANK_BRANCH depends on entity LOANS and BANK_BRANCH has total participation. * The weak entity BANK_BRANCH depends on entity BANK and BANK_BRANCH has total participation. * The partial key of BANK_BRANCH is Branch_no.

None of the listed is correct

Consider the following sets of functional dependencies over a relation R(A,B,C). F1 = {A → B, B → C} F2 = {A → B, A → C} F3 = {A → B, AB → C} Which sets are equivalent? * F1 and F2 * F1 and F3 * F2 and F3 * None of the listed is correct

* A --> C * B --> A * AB --> C

Consider the relation A B C 1 2 2 1 3 2 1 4 2 2 5 2 Check all FDs that are valid in R. * A --> B * A --> C * B --> A * AB --> C * AC --> B

* Foreign key needed only in Song table

Consider two tables: Album and Song, that are related by a "1-to-Many" relationship. Given this type of relationship, in which table should the corresponding foreign key be placed?

A new entity must be created, and its key is the combination of the key of Course plus the key of Student which are also FKs on individual basis.

Consider two tables: Student and Course, that are related by a "Many-to-Many" relationship. Given this type of relationship, in which table should the corresponding foreign key be placed?

* Specialization * The original concepts of ER * Generalization * Categorization

EER includes .....

False

Entities are usually verbs in the English description of a database.

Weak relationship

Exists if the primary key of the related entity does not contain a primary key component of the parent entity.

In a row of a relational table, an attribute can have more than one value

Given an ER model and its relational schema, which of the following is INCORRECT?

(1,2,4,7,9)

Suppose relation R(A,B) has the following tuples: A B 1 2 3 4 5 6 and relation S(B,C,D) has the following tuples: B C D 2 4 6 4 6 8 4 7 9 The theta-join of R and S is computed by taking the cartesian product R x S and the applying a selection on a condition. Computing the theta-join of R and S with the condition R.A < S.C AND R.B < S.D....... which of the following tuples is in the result? Assume each tuple has schema (A, R.B, S.B, C, D). * 3,4,5,7,9) * (1,2,4,7,9) * (1,2,4,4,6) * (1,2,2,6,8)

* (1, a, a, 7, 8)

Suppose relation R(A,B) has the following tuples: A B 1 a 7 t 2 g 4 c 9 t and relation S(B,C,D) has the following tuples: B C D c 5 6 a 7 8 t 8 9 The theta-join of R and S is computed by taking the cartesian product R x S and the applying a selection on a condition. Computing the theta-join of R and S with the condition R.B = S.B AND R.A < S.C .....which of the following tuples is in the result? Assume each tuple has schema (A, R.B, S.B, C, D). * (1, a, c, 5, 6) * (1, a, a, 7, 8) * (4, c, c, 7, 8) * (1, a, a, 8, 9)

* (1,2,3)

Suppose relation R(A,B,C) currently has only the tuple (0,0,0), and it must always satisfy the functional dependencies A --> B and B --> C. Which of the following tuples may be inserted into R legally? * (1,2,3) * (0,1,2) * (1,0,2) * (0,1,1)

* (2,5,3)

Suppose relation R(A,B,C) has the following tuples: A B C 1 2 3 4 2 3 4 5 6 2 5 3 1 2 6 and relation S(A,B,C) has the following tuples: A B C 2 5 3 2 5 4 4 5 6 1 2 3 Compute the intersection of the relations R and S. Which of the following tuples is in the result? * (4,2,3) * (2,5,3) * (1,2,4) * (2,2,6)

* (4,5,3)

Suppose relation R(A,B,C) has the following tuples: A B C 1 2 3 4 2 3 4 5 6 2 5 3 1 2 6 and relation S(A,B,C) has the following tuples: A B C 2 5 3 2 5 4 4 5 6 1 2 3 Compute the union of R and S. Which of the following tuples DOES NOT appear in the result? * (2,5,3) * (1,2,3) * (4,5,3) * (4,2,3)

* (6,2)

Suppose relation R(A,B,C) has the following tuples: A B C 1 2 3 4 2 3 4 5 6 2 5 3 1 2 6 Compute the projection π C,B (R). Which of the following tuples is in the result? * (2,6) * (6,4) * (2,3) * (6,2)

* (2,5,4)

Suppose relation R(A,B,C) has the following tuples: A B C 1 2 3 4 2 3 4 5 6 2 5 3 1 2 6 and relation S(A,B,C) has the following tuples: A B C 2 5 3 2 5 4 4 5 6 1 2 3 Compute (R - S) union (S - R), often called the "symmetric difference" of R and S. Which of the following tuples is in the result? * (2,5,4) * (4,5,3) * (4,5,6) * (1,5,6)

* AC --> B, BC --> A

Suppose we have relation R(A,B,C,D,E), with F = { AB-->D AC-->E, BC-->D D-->A, E-->B } We create S(A,B,C) What FDs hold in relation S? * AC --> B, B --> A * AC --> B, C --> A * C --> A, AC --> B * AC --> B, BC --> A

true

The (min, max) are placed at the opposite side of the entity in the ER diagram. For example, in a binary relationship called "Depends", between entity DEPENDENT and entity EMPLOYEE where an employee can have 0 or more dependents the ( 0, N ) is placed at the side where the EMPLOYEE entity is.

true

The (min, max) constraint is placed at the opposite side of the Entity Type it refers to.

Entity

The Rectangles divided into two parts, upper and lower, represents _______________.

Candidate key

The attributes of a determinant either individually or working together must be able to functionally determine all the other attributes in the relation.

* Section_identifier * Course_number, Semester, Year

The candidate key(s) of table SECTION is(are): https://solacc.instructure.com/assessment_questions/24686226/files/30891538/download?verifier=JNni7BF2BW3QiezpoKtDPNDmm75nc84ggnhWHdKO&wrap=1

true

The degree of a relationship type is the number of participating entity types.

conceptual

The entity relationship diagram is used to graphically represent the __________ database model.

BCNF

The highest normal form for relation schema R(A,B,C) with functional dependencies F = {AB→ C; B→A; C→B } is

degree

The number of entities participating in a relationship is called ____________.

false

The relationship between a superclass and a subclass is 1:N

true

Two or more superclasses that share a subclass have same keys

distinguish one instance of an entity from all other instances of that entity

Unique Identifiers( keys) in ER model....

False

Used to implement M:N relationships between two or more entities. Composed of the PKs of each of the entities to be connected.

2NF

What Normal Form the table and FDs satisfy? Table: ID Last First Grade Class Instructor 1 Wood Bob C Geog357 Mennis 2 Kent Chuck B Geog115 Brower 3 Smith Jane A Geog357 Mennis 4 Boone Dan B Geog357 Mennis FDs: ID --> Last, First, Grade, Class Class -->Instructor

* Entity X is not optional for entity Y. Ex: Entity Y has total participation in the relationship which makes necessary and not optional to relate one of the X records to a Y record.

https://solacc.instructure.com/assessment_questions/16440976/files/21303716/download?verifier=xqP9Qqza9WuSBgYNp0L4h87l3Wr3GJaVW83bDurz&wrap=1 Which of the following statements true? * Neither entity is mandatory for the other. * Cannot say based on diagram. * Entity Y is not optional for entity X. * Entity X is not optional for entity Y.

All dependents have exactly one employee the depend upon

https://solacc.instructure.com/assessment_questions/16540581/files/21456317/download?verifier=3lDSbX74UMoiNk8842ADzHMXYFCm6LLfnP5bbHMe&wrap=1 Check what is correct. * All dependents have exactly one employee the depend upon * One employee has zero or more dependents * Some dependents may not have an employee they depend upon * Zero or more employees have one dependent

* For exactly one department WORKS_FOR at least 4 employees or more. * The minimum number or employees WORKS_FOR a department has is 4.

https://solacc.instructure.com/assessment_questions/16540581/files/21456317/download?verifier=3lDSbX74UMoiNk8842ADzHMXYFCm6LLfnP5bbHMe&wrap=1 Check what is correct. * For exactly one department WORKS_FOR at least 4 employees or more. * The minimum number or employees WORKS_FOR a department has is 4. * The maximum number of departments an employee WORKS_FOR is 4. * All is correct.

* R1(A,B) and R2(B,C) Ex: AC→B C is redundant (A)+ = ABC The minimal cover is A->B , B->C

Given the relation schema R(A,B,C) and functional dependencies F = {A→B, B→C, AC→B}. What is the result of using the Relational database design algorithm for producing a database schema which is dependency preserving and has the lossless join property for relations in 3rd normal form? * R1(A,B), R2(B,C) and R3(A,C,B) * R1(A,B) and R2(A,C) * R1(A,B) and R2(B,C) * None listed is correct.

B and C

Given the relation schema R(A,B,C) and functional dependencies F ={AB→ C, B→A; C→B }. Which attribute(s) are prime? * Only A * Only B * A and B * B and C

* DeptNo, Month, Year --> Dname

Given the relation schema, DeptSales(DeptNo, Dname, Month, Year, Sales) and the set of functional dependencies, F = { DeptNo→Dname DeptNo,Month,Year→Dname Year→Sales } Which FD is valid? * DeptNo --> Sales * DeptNo, Month, Year --> Dname * Dname --> Sales * None listed is correct

Each subclass has as key the key of the superclass.

In EER-to-Relational Schema Mapping of Specialization or Generalization we can create a table for each superclass and for each subclass.

* we could merge the two entities and the relationship between the two entities into a single relation when both participations are total. That is, 2 rectangles and the diamond between them could produce one rectangle. * we prefer add the FK to an entity with total participation. * we add the FK to one of the two entities.

In ER-to-Relational Schema Mapping, for 1:1 binary relationship..... * we could merge the two entities and the relationship between the two entities into a single relation when both participations are total. That is, 2 rectangles and the diamond between them could produce one rectangle. * we prefer add the FK to an entity with total participation. * we add the FK to one of the two entities.

For each multivalued attribute A, create a new relation R and the primary key becomes the key of relation R.

In ER-to-Relational Schema Mapping, of Multivalued attributes....

true

In a binary relationship the (min, max) denotes the participation constraint of an entity.

parent

In a one-to-many relationship, the entity that is on the one side of the relationship is called a(n) ________ entity.

multivalued attribute

In an entity called EMPLOYEE, the employee can have more than one skills and the skill can be referred as __________________.

Number of tuples (rows in table).

In the relational schema, cardinality is termed as.........

ABC --> D

Let R(A,B,C,D,E) be a relation in Boyce-Codd Normal Form (BCNF). Suppose ABC is the only key for R. Which of the following functional dependencies is guaranteed to hold for R? * BCDE --> A * ABC --> D * BCE --> A * ACDE --> B

* ABCE --> D

Let R(A,B,C,D,E) be a relation in Boyce-Codd Normal Form (BCNF). Suppose ABC is the only key for R. Which of the following functional dependencies is guaranteed to hold for R? * BCDE --> A * ABCE --> D * ACD --> E * ACDE --> B

* A -->BC, B -->AC, C -->AB

Let relation R(A,B,C,D) satisfy the following functional dependencies: A -->B, B --> C, C --> A Call this set S1. A different set S2 of functional dependencies is equivalent to S1 if exactly the same FDs follow from S1 and S2. Which of the following sets of FDs is equivalent to the set above? * A -->BC, B -->AC * A -->B, B -->A, C -->A * A -->BC, B -->AC, C -->AB * B -->A, B -->C, C -->B

* C --> B, B -->A, A --> C

Let relation R(A,B,C,D) satisfy the following functional dependencies: A -->B, B --> C, C -->A Call this set S1. A different set S2 of functional dependencies is equivalent to S1 if exactly the same FDs can be generated from S1 and S2. Which of the following sets of FDs is equivalent to the set above? * C --> B, B -->A, A --> C * A --> B, B -->C, C--> B * B --> A, B -->C, C -->B * B -->A C, C -->AB

* ACE --> D

Let relation R(A,B,C,D,E) satisfy the following functional dependencies: AB -->C, BC --> D, CD --> E, DE -->A, AE -->B Which of the following FDs is also guaranteed to be satisfied by R? * ACE --> D * A --> B * BD --> E * CE --> B

CEG --> AB

Let relation R(A,B,C,D,E,F,G,H) satisfy the following functional dependencies: A --> B, CH --> A, B --> E, BD --> C, EG --> H, DE --> F Which of the following FDs is also guaranteed to be satisfied by R? * ADE --> CH * CGH --> BF * CDE --> AF * CEG --> AB

* BED --> CF

Let relation R(A,B,C,D,E,F,G,H) satisfy the following functional dependencies: A -->B, CH --> A, B --> E, BD --> C, EG --> H, DE -->F Which of the following FDs is also guaranteed to be satisfied by R? * CDE --> AF * ADE --> CH * CGH --> BF * BED --> CF

Two or more superclasses that share the subclass have same keys

Multiple inheritance can be used in a subclass when...

Composite identifier

Primary key composed of more than one attribute is called ________________.

* Anastasia Boleva * Freddy Wilson

Some of the following could be attributes of an ENTITY called PERSON. Select the incorrect attributes for PERSON. * Anastasia Boleva * Name * Freddy Wilson * Age

* (3,4,6,8)

Suppose relation R(A,B) has the following tuples: A B 1 2 3 4 5 6 and relation S(B,C,D) has the following tuples: B C D 2 4 6 4 6 8 4 7 9 Compute the natural join of R and S. Which of the following tuples is in the result? Assume each tuple has schema (A,B,C,D). * (1,2,4,8) * (1,4,6,8) * (3,4,6,8) * (1,2,6,8)

(1,2,4,6)

Suppose relation R(A,B) has the following tuples: A B 1 2 3 4 5 6 and relation S(B,C,D) has the following tuples: B C D 2 4 6 4 6 8 4 7 9 Compute the natural join of R and S. Which of the following tuples is in the result? Assume each tuple has schema (A,B,C,D). * (5,6,7,9) * (5,6,7,8) * (1,2,4,6) * (3,4,2,6)

unary

__________ relationship exists when an association is maintained within a single entity.

* The owner of an airplane is a corporation or a person or both. * For entity PERSON to make sense the entities OWNER, EMPLOYEE and PILOT must have the same key. * If we type (2, 4) between entity PLANE_TYPE and relationship OF_TYPE it implies that PLANE_TYPE has OF_TYPE minimum 2 AIRPLANE and maximum 4 AIRPLANE . aiport.jpg Ex: The ( 2,4) below the PLANE_TYPE and before the OF_TYPE implies that one PLANE_TYPE is OF_TYPE in minimum 2 AIRPLANE(s) and maximum 4 AIRPLANE(s) .

What is correct? https://solacc.instructure.com/courses/155394/files/40978816/preview * If we type (2, 4) between entity PLANE_TYPE and relationship OF_TYPE it implies that AIRPLANE has OF_TYPE minimum 2 PLANE_TYPE(s) and maximum 4 PLANE_TYPE(s) . * The owner of an airplane is a corporation or a person or both. * For entity PERSON to make sense the entities OWNER, EMPLOYEE and PILOT must have the same key. * If we type (2, 4) between entity PLANE_TYPE and relationship OF_TYPE it implies that PLANE_TYPE has OF_TYPE minimum 2 AIRPLANE and maximum 4 AIRPLANE . aiport.jpg

* R1(A,B) and R2(B,D) and R3(B,C) * R1(A,B) and R2(B,C) and R3(C,D)

What is the BCNF decomposition(s) for relational schema R(A,B,C,D) with the FDs {A→B; B→C; C→D} * R1(A,B) and R2(B,D) and R3(B,C) * R1(A,B) and R2(B,C) and R3(C,D) * none is in BCNF

Instance

What term is used to refer to a specific record in a music database; for instance, information stored about a specific album?

Recursive relationship

When relationships can exist between occurrences of the same entity set the we have a _______________________.

Maximum cardinality

Which of the following indicates the maximum number of entities(tuples, rows) that can be involved in a relationship?

* If A → B and BC → D, then AC → D * If A → B1,..,Bn and C1,..,Cm → D and {C1,...,Cm} is a subset of {B1,..,Bn}, then A → D

Which of the following rules for functional dependencies are correct? * If A → B and BC → D, then AC → D * If AB → C then A → C * If A → B1,..,Bn and C1,..,Cm → D and {C1,...,Cm} is a subset of {B1,..,Bn}, then A → D * If A → C and B → C and ABC → D, then A → D

The diagram does not suggest which table might have more attributes in its primary key.

Which of the following statements true? https://solacc.instructure.com/assessment_questions/9218807/files/11501408/download?verifier=0Tyx1RTWzVdHb3icchZJg8bPhYFiSh9FLdPAfH7t&wrap=1 * Table Y should have a larger number of key attributes. * Table X should have a larger number of key attributes. * Both tables should have the same number of (primary) key attributes. * The diagram does not suggest which table might have more attributes in its primary key.

Table Y has all of its records participating in the relationship with table X.

Which of the following statements true? https://solacc.instructure.com/assessment_questions/9218807/files/11501408/download?verifier=0Tyx1RTWzVdHb3icchZJg8bPhYFiSh9FLdPAfH7t&wrap=1 * Table Y should have a larger number of key attributes. * Table Y has all of its records participating in the relationship with table X. * Both tables should have the same number of (primary) key attributes. * Table X should have a larger number of key attributes.