cs 440 lecture 3

cs 440 lecture 3 - 1/30/2009 CS 440: Database Management...

Info iconThis preview shows pages 1–5. Sign up to view the full content.

View Full Document Right Arrow Icon
1/30/2009 1 CS 440: Database Management Systems The Big Question z Design Physical (base level tables) Logical (user views) z How good is our design? z How do we measure this anyway? Some Guidelines z Semantics of the attributes z Reducing the redundant values in tuples z Reducing NULL values in tuples z Disallowing the possibility of generating spurious tuples Semantics of the Relation Attributes z Each tuple (record) should represent one entity or relationship instance. z Attributes of different entities should *not* mix together in the same relation. z *Only* foreign keys should be used to refer to other entities. z Entity and relationship attributes should be kept as apart as possible. Semantics of the Relation Attributes z Design your database schema such that it can be explained easily relation by relation. z The semantics of each relation should be easy to interpret. (This is an informal way to measure the “goodness” of your design. An Example of Good Design 10:21 PM
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
1/30/2009 2 An Example of Good Design 10:21 PM Examples of Not-So-Good Design Examples of Not-So-Good Design Examples of Not-So-Good Design Examples of Not-So-Good Design 10:21 PM Redundant Information in Tuples and Update Anomalies z Mixing attributes of multiple entities may cause problems. z Information stored redundantly may waste space. z Problems with update anomalies Insertion Modification Deletion
Background image of page 2
1/30/2009 3 Redundant Information in Tuples and Update Anomalies z How to insert a new department when there is no employees working for it? 10:21 PM z Need to be consistent when updating the department info Redundant Information in Tuples and Update Anomalies z Design the base relation schema such that no insertion/deletion/modification anomalies are present in the relations are present in the relations. z If they are present, note them and make sure they are accounted in the applications. NULL Values in Tuples z Relations should be designed to have as few NULL values as possible. z Reasons for NULL values: Attribute values not available or invalid Attribute values unknown (may exist) Values known to exist, but unavailable z Problems associated with NULL values Storage waste Cause problems for aggregating functions, such as SUM, COUNT. NULL Values in Tuples z Avoid placing attributes in a table whose values are often NULL. z If they are unavoidable, make sure they only occur in very few cases. Spurious Tuples z Poor design can lead to spurious tuples when performing certain join operations. z The “loseless join” property is used to guarantee meaningful results for join operations. Spurious Tuples 10:21 PM
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
1/30/2009 4 Spurious Tuples Spurious Tuples EName 10:21 PM Spurious Tuples z We need to make sure when splitting a base table into two or more tables, join operations will not create spurious tuples will not create spurious tuples.
Background image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 06/28/2009 for the course CS 440 taught by Professor Staff during the Spring '08 term at Oregon State.

Page1 / 16

cs 440 lecture 3 - 1/30/2009 CS 440: Database Management...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online