Database Design Final

Find closures of functional dependencies (candidate keys) below for the following relation R = (A, B, C, D, E, F, G, H) F = {{A} → {B, C, D}, {A, E} → {G, H}, {E} → {F}, {F} → {E}}

A+ = ABCD AE+ = ABCDEFGH ----- E+ = EF F+ = EF AF+ = ABCDEFGH ----- AEF+ = ABCDEFGH, EF+ = EF, ØSo the candidate keys are {AE, AF}

The normalization process that starts with a universal relation and uses decomposition to produce a set of normalized relations is called A. Analysis B. Synthesis C. Abstraction D. Classification

A. Analysis

If X and Y are sets of attributes of relation R, we say that Y is functionally dependent on X if A. for each X value there is only one Y value B. for each Y value there is only one X value C. no two X values have the same Y value D. when two rows have the same Y value they also have the same X value

A. for each X value there is only one Y value Functional dependency is actually a many-to-one relationship from attribute set X to attribute set Y.

In the SELECT operation, the theta-condition refers to A. the selection predicate B. the selection subject C. the equality or inequality operator used D. the null condition

A. the selection predicate

To change the minimum cardinality on the parent side from zero to one, the foreign key, which would have been NULL, must be changed to NOT NULL. A.TRUE B.FALSE

A.TRUE

When increasing cardinalities from 1:N to N:M, we basically create a new intersection table, fill it with data and drop the old foreign key. A.TRUE B.FALSE

A.TRUE

A determinant of a functional dependency may or may not be unique in a relation. (Note: In functional dependencies, the attribute whose value is known or given is referred to as the determinant.) A.True B.False

A.True

Find closures of functional dependencies (candidate keys) below for the following relation R = (A, B, C, D, E, F, G, H, I, J) F = {A, B → C; A → D, E; B → F; F → G, H; D → I, J}

AB+=ABC AB+=ABCDE AB+=ABCDEFGH AB+=ABCDEFGHIJ ØSo the candidate key is {AB}

Saying that two entities are functionally dependent means that ________. A.The entities are always connected by a mathematical equation. B.For one of the entities, if we are given the value of that entity, we can determine the value of other entity. C.For both of the entities, if we are given the value of that entity, we can determine the value of other entity. D.None of the above.

B is not the answer as A → B, A has a many to one relationship with B. the functional dependency will have to be removed through normalization

If S = {a,b} and T = {1,2,3} then the Cartesian product S×T has how many ordered pairs? A. 5 B. 6 C. 8 D. 9

B. 6

In the relation R(A, B, C, D), having the composite key {A,B}, which of the following FDs would demonstrate that the relation is not 2NF? A. A → B B. A → C C. C → D D. All of the above

B. A → C

A relation having only one candidate key is third normal form if it is 2NF and A. No nonkey attribute is determined by only part of the key B. No nonkey attribute is dependent on another nonkey attribute C. No part of the key is dependent on another part of the key D. There are no partial functional dependencies

B. No nonkey attribute is dependent on another nonkey attribute

If entity instances can belong to more than one subclass in a specialization, the subclasses are A. Disjoint B. Overlapping C. Incorrect D. Complete

B. Overlapping

The process of identifying subsets of existing entity set is called A. Generalization B. Specialization C. Abstraction D. Aggregation

B. Specialization

The normalization process in which we find and group together functional dependencies with the same determinant is called A. Analysis B. Synthesis C. Generalization D. Specialization

B. Synthesis

Completeness constraints tell us whether a specialization is A. Disjoint or Overlapping B. Total or Partial C. Predicate-defined or User-defined D. Union or Category

B. Total or Partial

In the relation R(A, B, C, D), having the composite key {A,B}, which of the following FDs would demonstrate that the relation is not 3NF? A. C → {A, B} B. {A,B} → C C. C → D D. any of the above

C. C → D

If entity instances cannot belong to more than one subclass, we say the subclasses are A. Complete B. Partial C. Disjoint D. Total

C. Disjoint

A relation is second normal form if it is 1NF and A. Every attribute is single valued B. Every attribute is determined by a portion of the key C. Every nonkey attribute is dependent on the entire key D. No nonkey attribute determines another

C. Every nonkey attribute is dependent on the entire key

Which of the following operators allows us to combine pieces of information about an entity that appear on separate tables? A. SELECT B. PROJECT C. NATURAL JOIN D. UNION

C. NATURAL JOIN

If subclass membership can be determined by examining a condition for each subclass, we describe the specialization as A. Attribute-defined B. Conditional C. Predicate-defined D. Complete

C. Predicate-defined

A functional dependency in which the attributes on the right hand side are included in the left hand side (determinant) is called A. Redundant B. Normal C. Trivial D. Multi-valued

C. Trivial

To find rows of the Employee table that have no value for rating, use the predicate A. WHERE RATING =' ' B. WHERE RATING = 0 C. WHERE RATING IS NULL D. WHERE RATING = NULL

C. WHERE RATING IS NULL

The operation that is equivalent to doing a SELECT operation on a product of two tables is the A. natural join B. theta select C. theta join D. intersection

C. theta join

The _________________concept provides firm theoretical foundation for designing a database: A.Equivalence sets B.Uniqueness constraint C.Functional dependencies D.Minimal sets E.Min max constraint

C.Functional dependencies

Explain how union differs from generalization.

In generalization, a subclass inherits all the properties of all of its superclasses in a hierarchical arrangement. In union, instead of a single superclass at the higher level, we have a collection of superclasses of types that can be quite different from one another. Each instance of the subclass inherits from just one member of this collection. However, different instances can inherit from different members.

Which normal forms are concerned with functional dependencies?

Normal forms 2NF through Boyce-Codd Normal Form (BCNF) are concerned with functional dependencies, particularly modification anomalies from functional dependencies.

●Another example of a concept for grasping the firm theoretical foundation is:

Normalization processes

Distinguish between predicate-defined and attribute-defined specialization.

Predicate-defined means that there is some predicate that can be used for each subclasses, but the predicates can involve different attributes. For attribute-defined, the predicate must for all subclasses use the same attribute.

Explain the role of referential integrity constraints in normalization.

Since the referential integrity constraint requires that a value of the foreign key exist as a primary key value, it will maintain consistency between the two relations. We are protected against update anomalies being created when we create the new relation and move the existing data into it, and against insertion anomalies being created when we add data to the new relation.

Consider the relation TEACH below: Teach (Student, Course, Instructor) which has the following FDs: FD1: {Student, Course} → Instructor FD2: Instructor → Course what are the keys and what normal form this relation is in?

The key is {Student, Course} and the relation is in 3NF but not in BCNF

Using a E-R diagram can be helpful in designing a normalized schema because A.The standard mapping of an E-R diagram to a relational model always results in a normalized schema B.The process of mapping an E-R diagram to a relational model is equivalent to the synthesis method of normalization C.The standard mapping of an E-R diagram to a relational model results in a schema that is close to normalized D.The process of mapping an E-R diagram to a relational model is equivalent to the decomposition algorithm for BCNF

C.The standard mapping of an E-R diagram to a relational model results in a schema that is close to normalized

A relation is in first normal form if A. Every attribute is single-valued for each tuple B. The domains of the attributes are atomic C. Each cell of the table can contain only one value D. All of the above

D. All of the above

Which of the following is/are the major objectives of normalization? A. Removing redundancy B. Removing anomalies C. Increasing model flexibility D. All the above E. Only a and b

D. All the above

In a functional dependency every determinant (i.e. left hand side) is always A. A candidate key B. A superkey C. A composite key D. None of the above

D. None of the above

A collection of superclasses with a subclass such that each member of it belongs to only one of them is called a A. Group B. Generalization C. Consolidation D. Union

D. Union

A spurious tuple is one that A. appears in the original relation B. does not have a candidate key C. has repeating values for an attribute D. is created by a lossy join

D. is created by a lossy join

If set S{A,B,C} is a superkey for the relation R(A,B,C,D,E), then A. {A,B} is also a superkey for R B. No subset of S is also a superkey for R C. {D, E} is also a superkey for R D. {A,B,C,D} is also a superkey for R

D. {A,B,C,D} is also a superkey for R

Which of the following options best describe the statement below: A ________ is used to limit the possible values of a foreign key. A.Composite key B.Surrogate key C.Functional dependency D.Referential integrity constraint

D.Referential integrity constraint

Consider the following two sets of FDs F = { A → C, B → C, A → {D, E}, B → F, F → {G, H}, D → {I, J} } G = { {A, B} → C, {B, D} → {E, F}, {A, D} → {G, H}, A → I, H → J} Check whether they are equivalent.

To show equivalence, We prove that F covers G and G covers F. (i.e. every FD in G can be inferred from F and Every FD in F can be inferred from G) Proof F is covered by G (every FD in G can be inferred from F): {AB}+ = {A, B, C} (From IR4 Union Rule) can infer {A, B} → C in G {A}+ = {A, D, E} (with respect to F) Therefore, A → D & A → E {A}+ = {A, D, I, J} (From IR3 Transitive Rule) can infer A → I in G {D}+ = {D, I, J} (with respect to F) Therefore, D → I & D → J {A, D}+ = {A, D, E, I, J} (From IR4 Union Rule) {B}+ = {B, F} (with respect to F) {D}+ = {D, I, J} (with respect to F) {B, D}+ = {B, F, D, I, J} (From IR4 Union Rule) Therefore, these two sets of FDs are not equivalent.

How do you demonstrate your competence in implementation of database applications.

ØPerfectly implement the software requirements specification ØDemonstrate proficient programming skills by showing that the implementation is completely free from programming errors.

Converts the following business statements into dependencies. Consider the following relation DiskDrive(serialNumber, manufacturer, model, batch, capacity, retailer) Each tuple in the relation DiskDrive contains information about a disk drive with a unique serialNumber, made by a manufacturer, with a particular model, released in a certain batch, which has a certain storage capacity, and is sold by a certain retailer. For example, the tuple DiskDrive (1978619, WesternDigital, A2235X, 765234, 500, CompUSA) specifies that WesternDigital made a disk drive with serial number 1978619, model number A2235X in batch 765235 with 500GB that is sold by CompUSA. Write each of the following dependencies as an FD: a)The manufacturer and serial number uniquely identifies the drive b)A model number is registered by a manufacturer and hence can't be used by another manufacturer. c)All disk drives in a particular batch are the same model. d)All disk drives of a particular model of a particular manufacturer have exactly the same capacity.

a. manufacturer, serialNumber → model, batch, capacity, retailer b. model → manufacturer c. manufacturer, batch → model d. model → capacity or it can be "model, manufacturer → capacity

Consider the universal relation R = {A, B, C, D, E, F, G, H, I} and the set of FDs F = { {A, B} →{C}, {A} → {D, E}, {B} → {F}, {F} → {G, H}, {D} → {I, J}} Find the key for R.

ØA minimal set of attributes whose closure includes all the attributes in R is a key. {A, B}+ = {A, B, C} Because of {A, B} → C, and since B → F in F, so {A, B}+ = {A, B, C, D, E, F} Because of F → G, H and since D → I, J in F, so {A, B}+ = {A, B, C, D, E, F, G, H, I, J} uniquely identifies all attributes Since the closure of {A, B} , is {A, B}+ = R. Then one key of R is {A, B}, and this is the only key

How do you show that you have contributed towards solutions of technical problems during the a project that more than two people working on it.

ØAttend the meetings and contribute significantly to solve the challenging problems that might occur to accomplish the project. Ø ØCollects and classifies a great deal of information - all relates to the topic. Ø ØSorts and relays a great deal of information - all relates to the topic. Ø ØHelp & hands in all assignments on time and announces the other teammates about that.

Consider the universal relation R = {A, B, C, D, E, F, G, H, I} and the set of FDs F = { {A, B} → {C}, {A} → {D, E}, {B} → {F}, {F} → {G, H}, {D} → {I, J}} Find the minimal set of FDs for R?

ØReplace all RHS composite with single attribute. ØReplace if possible LHS composite attribute with single attribute. ØRemove redundant FDs (i.e. dependencies that can be inferred from other dependencies). ØThen the remaining is the minimal set of FDs. {A} → {D}, {A} → {E}, {B} → {F}, {F} → {G}, {F} → {H}, {D} → {I}, {D} → {J} {A, B} → {C}, Minimal Set of FDs

How do you show that, you have the ability to work in heterogeneous environments which are diverse in gender, ethnicity, and academic accomplishment.

ØThe ability to work effectively to complete the project fully in a team. ØActively working toward group goals without prompting. ØWillingly accepting and fulfilling individual role within the team. ØSensitive to the feelings and learning needs of all team members. ØConsistently and actively contributes knowledge, opinions, and skills. ØValue the opinion and knowledge, skills of all group members and encourages their contribution. ØHelp group identify necessary changes and encourages group action for change.

What are the goal of creation of useful software architecture documentation:

•Describe clearly the overall architecture of the project. •Describe clearly the functions of modules/sub-systems in the architecture. •Report is easily to read and understandable by a general audience. ( that is spelling and syntax error free).

Prove that any relation schema with two attributes is in BCNF.

●Consider a relation schema R= {A, B} with two attributes. The possible (non-trivial) FDs are {A} → {B} and {B} → {A}. ●There are four possible cases: 1)No FD holds in R. 1)In this case, the key is {A, B} and the relation satisfies BCNF. 2)Only {A} → {B} holds. 1)In this case, the key is {A} and the relation satisfies BCNF. 3)Only {B} → {A} holds. 1)In this case, the key is {B} and the relation satisfies BCNF. 4)Both {A} → {B} and {B} → {A} hold. 1)In this case, there are two keys {A} and {B} and the relation satisfies BCNF. Hence, any relation with two attributes is in BCNF.

Consider a relation R(A,B,C,D,E) with the following dependencies: F = { {A, B} → {C}, {C, D} → {E}, {D, E} → {B} } Is AB a candidate key of this relation? If not, is ABD a candidate key? Explain your answer.

●No, AB+ = {A,B,C},a proper subset of {A,B,C,D,E} ●Yes, ABD+ = {A,B,C,D,E}