Database Design Theory and Methodology

Question 1

the grouping of attributes to form “good” relation schemas

Answer 1

relational database design

Question 2

2 levels of goodness of relation schemas

Answer 2

1. logical/conceptual level : “user view” level
2. implementation/storage level : “base relation” level

Question 3

measures of quality of a relational database design (4)

Answer 3

1. semantics of the relation attributes
2. redundant information in tuples and update anomalies
3. null values in tuples
4. spurious tuples

Question 4

refers to the interpretation of attribute values in a tuple

Answer 4

semantics of a relation attributes

Question 5

bottom line of relational database design (2)

Answer 5

1. design a schema that can be explained easily relation by relation
2. the semantics of attributes should be easy to interpret

Question 6

effects of redundant information in tuples (5)

Answer 6

1. wastes storage
2. causes update anomalies
3. insertion anomalies
4. deletion anomalies
5. modification anomalies

Question 7

some data cannot be easily inserted into the database

Answer 7

insertion anomaly

Question 8

deleting some data can cause other information to be lost

Answer 8

deletion anomaly

Question 9

all copies of the redundant data have to be changed or else the database will become inconsistent

Answer 9

modification anomaly

Question 10

relational database design guideline 1 (4)

Answer 10

• each tuple in a relation should represent one entity/relationship instance only
• attributes of different entities should not be mixed in the same relation
• only foreign keys should be used to refer to other entities
• entity and relationship attributes should be kept apart as much as possible

Question 11

relational database design guideline 2 (2)

Answer 11

• design a schema that does not suffer from the insertion, deletion and modification anomalies
• if there are any anomalies present, then note them so that applications can be made to take them into account

Question 12

reasons for null values in tuples (5)

Answer 12

1. attribute is not applicable or invalid
2. attribute value unknown but may not exist
3. value known to exist but unavailable
4. can waste storage space
5. leads to difficulty of performing selections, aggregation operations and specifying joins

Question 13

relational database design guideline 3 (2)

Answer 13

• relations should be designed such that their tuples will have as few NULL values as possible
• attributes that are NULL frequently could be placed in separate relations

Question 14

additional tuples that represent invalid information; a possible result of a JOIN operation on poorly designed relations

Answer 14

spurious tuples

Question 15

relational database design guideline 4

Answer 15

relations should be designed in a way that no spurious tuples will be generated if a natural-join operation is applied to them

Question 16

used to define normal forms for relations denoted by X -> Y

Answer 16

functional dependency

Question 17

a property of the meaning of data and hold at all times

Answer 17

functional dependency

Question 18

what inference rule for FDs is being shown:

If Y subset-of X, then X -> Y

Answer 18

reflexive

Question 19

what inference rule for FDs is being shown:

If X -> Y, then XZ -> YZ

Answer 19

augmentation

Question 20

what inference rule for FDs is being shown:

if X -> Y and Y -> Z, then X -> Z

Answer 20

transitive

Question 21

what inference rule for FDs is being shown:

if X -> YZ, then X -> Y and X -> Z

Answer 21

decomposition

Question 22

what inference rule for FDs is being shown:

if X -> Y and X -> Z, then X -> YZ

Answer 22

union

Question 23

what inference rule for FDs is being shown:

if X -> Y and WY -> Z, then WX -> Z

Answer 23

pseudotransitivity

Question 24

set of all dependencies that include F as well as all dependencies that can be inferred from F

Answer 24

Closure of F (F+)

Question 25

process of decomposing unsatisfactory "bad" relations by breaking up their attributes into smaller relations

Answer 25

normalization

Question 26

process of taking a relation schema through a series of tests to certify whether it satisfies a certain normal form is carried out in practice to minimize redundancy and insertion, deletion and modification anomalies

Answer 26

normalization

Question 27

refers to the highest normal form condition that a relation meets

Answer 27

normal form

Question 28

an attribute that is a member of some candidate keys

Answer 28

prime attribute

Question 29

an attribute that is not a member of any candidate key

Answer 29

nonprime attribute

Question 30

what normal form is being described: the domain of an attribute must include only atomic values and that the value of any attribute in a tuple must be a single value from the domain of that attribute

Answer 30

first normal form

Question 31

first normal form disallows (3)

Answer 31

1. composite attributes 2. multi-valued attributes 3. complex attributes

Question 32

what normal form is being described: a relation schema R is in this normal form if every non-prime attribute A in R is fully functionally dependent on the primary key of R

Answer 32

second normal form

Question 33

what normal form is being described: for relations where primary key contains multiple attributes, no nonkey attribute should be functionally dependent on a part of the primary key

Answer 33

second normal form

Question 34

what normal form is being described: No non-prime attribute A in R is transitively dependent on the primary key

Answer 34

third normal form

Question 35

what normal form is being described: each relation should not have a nonkey attribute that is functionally determined by another nonkey attribute

Answer 35

third normal form

Question 36

1st normal form: all attributes depend on the ___

Answer 36

key

Question 37

2nd normal form all attributes depend on the ____

Answer 37

whole key

Question 38

3rd normal form all attributes depend on __

Answer 38

nothing but the key