Lecture 2 - Relational Schema Good Or Bad

Question 1

What performance metrics may we look at when judging the goodness of a relational schema?

Answer 1

• goodness i.e. whether the attributes form a good relation
• what pitfalls exist
• operation efficiency
• maximize amount of information conveyed while minimizing redundancy

Question 2

Guidelines for good design of a relational schema?

Answer 2

1. attributes of a relation should make sense
2. avoid redundant tuples
3. relations should minimize the amount of null values held
4. design relations to avoid fictitious tuples after join

Question 3

Explain what is meant by the first guideline : The attributes of a relation should make sense.

Answer 3

Attributes of different entities should not be in the same relation, as to minimize the similarity between relations. Relationships should on be represented by Primary and Foreign keys.

Question 4

What is the impact of guideline 2: Avoid redundant tuples?

Answer 4

Having repetition of data:
- wastes storage space
- leads to inconsistencies (as replicas must be changed when one tuple is changed) and anomalies

Question 5

Why have NULL values in tuples?

Answer 5

• to represent inapplicable or invalid attribute for that tuple
• value is unknown
• value is known, but unavailable

Question 6

What should we do to meet guideline 3?

Answer 6

Attributes that are frequently NULL should be placed in
separate relations to avoid wasting storage & reducing uncertainty!

Question 7

What is guideline 4 : Design relations to avoid fictitious tuples after join.

Answer 7

Splitting a relation on the wrong attribute, means after we try to combine we may get non-existing entries!