Part 5 Relational Design ConceptsCopyright  1971-2002 Thomas P. Sturm Relational Design Concepts Part 5, Page 2 Logical Database Design • How do we decide on a logical structure? • How do we decide what relations are needed? • How do we decide what attributes to put into each relation? • What criteria do we use to evaluate whether one design is “better” than the other?Copyright  1971-2002 Thomas P. Sturm Relational Design Concepts Part 5, Page 3 Database Modeling RealWorldAbstraction(Application of apoint of view)PerceivedWorldData Modeling(Application ofsound databasedesign principles)DatabaseWorldCopyright  1971-2002 Thomas P. Sturm Relational Design Concepts Part 5, Page 4 Data model (Kent) - A basic system of constructs used to describe reality • the corporate information resource is a model of corporate reality (Tsichritzes, Lochovsky) - An intellectual tool that provides an interpretation of the world which is sufficiently abstract to allow minor perturbations, yet is sufficiently powerful to give some understanding concerning how data about the world are related • it partially captures the meaning of dataCopyright  1971-2002 Thomas P. Sturm Relational Design Concepts Part 5, Page 5 Justification for a Data Model UnderstandDocumentValidateCommunicateIntegrateApplicationRequirementsCopyright  1971-2002 Thomas P. Sturm Relational Design Concepts Part 5, Page 6 Data Modeling Task Identify and Describe: Entities (objects types, roles) Attributes (characteristics) Relationships (associations, dependencies) Constraints (rules)Copyright  1971-2002 Thomas P. Sturm Relational Design Concepts Part 5, Page 7 Data Modeling Issues Model must be capable of describing reality Methodologies must exist to build the model Methodologies must exist to use the model Modeling must lead to implementation The model must support correct query formation Model must yield a “good” implementation Model should have theoretical underpinning • no surprises • no “special rules” • no “that's a feature, not a bug”Copyright  1971-2002 Thomas P. Sturm Relational Design Concepts Part 5, Page 8 Entity Types / Entity Instances Entity Type (Kent) - A category, arbitrarily defined (but agreed to) so that membership within the category can be established, at least at a point in time Each relation pertains to only one entity type E.g. dept, emp, task, proj Entity Instance Occurrence of a member in the category in the world E.g. a particular department, employee, task, or projectCopyright  1971-2002 Thomas P. Sturm Relational Design Concepts Part 5, Page 9 Design Tools Decomposition Normalization section • each document, report, data-flow, etc. is defined to be a relation • any relations that violate the required normal form are divided into 2 or more relations that satisfy the normal form • the resulting relational structure has a relation for each entity Construction E-R Modeling, LDS • identify entities - objects that have attributes, identifiers, and relationships • assign attributes to the right entities • attributes apply to all entity-instances • attributes are fully functionally dependent on the whole identifier • form “roles” and “intersection entities” where necessaryCopyright  1971-2002 Thomas P. Sturm Relational Design Concepts Part 5, Page 10 Attributes for a Relational model Each entity instance has exactly one value for each attribute (within the scope of the data model) • atomic • repeating groups are not allowed • vectors are not allowed • pointers and other abstract references are not allowed • values for a particular attribute come from a specified pool of values (called its domain) An attribute (or a specific set of attributes) forms an identifier for each entity instance • if the entity instances are different, so is the value (or set of values) for the attribute (or set of attributes)Copyright  1971-2002 Thomas P. Sturm Relational Design Concepts Part 5, Page 11 Relational Keys Super Key – an identifier in a relation • there may be more than one, especially since a set of attributes (a composite key) can be an identifier Candidate Key – a minimal super key • must be a minimal identifier (cannot discard any attribute without losing uniqueness) Primary Key - one selected candidate key Alternate Key - a candidate key not selected to be the primary key Note: Primary keys provide the sole tuple-level addressing mechanism in the relational model. If you have two different tuples, they must have different values for their primary keys. If you have different values for the primary key, the tuples (and entity instances) must be different. Foreign Key - an attribute in one relation whose values are required to match those of a primary key in another relationCopyright  1971-2002 Thomas P. Sturm Relational Design Concepts Part 5, Page 12 Fundamental Relational Integrity Rules Entity Integrity No attribute participating in the primary key of a base relation is allowed to accept null values Referential Integrity The values of foreign keys must either • be equal to an existing primary key • be “wholly null” - have null for all attributes making up the foreign key Example: Relation emp cannot have a null Ename field The field Mgr in the emp relation must have a value that occurs in Ename or be null.Copyright  1971-2002 Thomas P. Sturm Relational Design Concepts Part 5, Page 13 Essentiality A data object is essential if its loss would cause a loss of information • hierarchical and network models have non-essential navigational information • sorted files that must be kept in that order to preserve the implied value of an un-stored field have essential ordering • fields sorted on a stored field have inessential ordering • in the relational model the only essential data objects are the relationsCopyright  1971-2002 Thomas P. Sturm Relational Design Concepts Part 5, Page 14 Essential Ordering Example County code County name County name 01 Aitkin Aitkin 02 Anoka Anoka 03 Becker Becker 04 Beltrami Beltrami 05 Benton Benton 06 Big Stone Big Stone 07 Blue Earth Blue Earth 08 Brown Brown 09 Carlton Carlton 10 Carver Carver 11 Cass Cass 12 Chippewa Chippewa 13 Chisago Chisago 14