Relationa DataBase Design Flashcards
(17 cards)
What is relational database design?
Grouping of attributes to form “Good” relation schemas
Two levels of goodness of relation schemas
Logical (conceptual level) - user view
Implementation (storage level) - base relation
Measures of Quality
- Semantics of the relation Attributes
- Redundant information in Tuples and Update anomalies
- Null values
- Spurious Tuples
refers to the interpretation of attribute values in a tuple
Semantics
Guideline 1
Each tuple in a relation should represent one entity or relationship instance only
Some data cannot be easily inserted into database
Insertion Anomaly
Redundant Information in Tuples effects:
- Waste Storage
- Causes update anomalies
- Insertion anomalies
- Deletion anomalies
- Modification anomalies
Deleting some data can cause data loss
deletion anomaly
Guideline 2
Design a schema that does not suffer from insertion, deletion and modification anomalies
Null values in tuples
- Attributes not applicaple or invalid
- unknown
- unavailable
Effects: - can waste storage space
- leads to difficult of performing selections, aggregation operations
Additional tuples that represent invalid information that are possible result of a join on relation that are poorly designed
Spurious Tuples
Guidelines 3
Relations should be designed such that their tuples will have as few NULL values as possible
Functional Dependency
Constraint between two sets of attributes from the database
Inference Rules for FD
Armstrong’s inference rules
IR1(Reflexive)
IR2(Augmentation)
IR3(Transitive)
IR4(Decomposition)
IR5(Union)
IR6(Pseudotransitivity)
Guideline 4
Relations should be designed in a way that no spurious tuples will be generated if a natural join operation is applied to them
For any two tuples t1 and t2 in any relation instance r(R) if t1[x]=t2[x] then t1[y] = t2[y] X->Y hold if whenever two tuples have the same value for X they must have the same value for Y if K is key of R then K functionally determines all attributes in R
Functional Dependency
