Web-Enabled Decision Support Systems

Web-Enabled Decision Support Systems Relational Data Modeling and Normalization Prof. Name name@email.com Position (123) 456-7890 University Name

Overview • 4.1 Introduction • 4.2 The Relational Data Model • 4.3 Relational Keys • 4.4 Relational Data Integrity Constraints • 4.5 Transforming E-R Diagrams into Relational Schemas • 4.6 Case Study: Logical Design for a University Database • 4.7 Introduction to Normalization • 4.8 Data Redundancy • 4.9 Database Anomalies • 4.10 Functional Dependencies • 4.11 Forms of Normalization • 4.12 In-Class Assignment • 4.13 Summary

Introduction • In the previous chapter, we described conceptual database design using object-based entity-relationship (E-R) data modeling • The objective of a logical database design is to transform the conceptual data model into a set of relations used for physical database design • We describe logical database design using record-based relational data modeling • Widely used in contemporary database applications • General modeling approach

Relational Data Model • First introduced by IBM’s E.F. Codd in the 1970s • Based on mathematical concept of a relation • Physically represented as a relation or a table that stores data • Consists of three components: • Relational data structure • Where data is organized • Data manipulation • Operations used to manipulate data stored in the data structure • Relational data integrity • Rules that maintain the integrity of data when manipulated

Relational Data Structure • A relation is the main data structure that stores and organizes data in the relational data model • Two-dimensional grid that holds data about the object in the database E-R Diagram and Relational Schema for the Student Relation

Relational Data Structure (cont.) • A column of a relation is referred to as an attribute • The number of attributes in a relation is called the degree of the relation • A row is referred to as a record ora tuple • The number of records in a relation is defined as the cardinality of the relation • Relational schema example: • STUDENT (SSN, Name, Email, DeptName)

Properties of a Relation • Each relation is uniquely identified by its name • Each cell of a relation contains exactly one (atomic) value • Each record of a relation is unique • Each attribute in a relation has a distinct name • The values of an attribute are from the same domain • The order of attributes is irrelevant • The order of records is also irrelevant

Data Manipulation • A way to access and manipulate the data in the relations • Done using a data manipulation language: • Structured Query Language (SQL) • SQL example:

Relational Keys • We need to specify one or more attributes (relational keys) that uniquely identify each record in a relation • A super key is a set of one or more attributes that uniquely identifies each record in a relation • A candidate key is a minimal super key (one that has a minimum number of attributes)

Primary and Composite Keys • A primary key is a candidate key that has been selected to uniquely identify records in a relation • Selection of primary key: • It must be unique within its domain at all times • The candidate key can never change • It cannot hold a NULL value • A composite key is a key that has more than one attribute

Foreign Key • A foreign key is an attribute or a set of attributes in a relation that serves as a primary key of the same or some other relation Foreign Key Primary Key Student and Department Relations Illustrating Foreign Keys (DeptName)

About NULL • Situations where attribute cannot be assigned with a data value • There is no applicable data value • When the value is unknown • Assign NULL value in such situations • NULL means that the value is either unknown or is not applicable • NULL is not same as zero or white space

Integrity Constraints • There are three types of data integrity constraints: • Domain constraints • Entity constraints • Referential constraints

Domain Constraints • A domain is the set of values that can be assigned to an attribute • The domain constraint states that all the values of an attribute must be from the same domain The Domain Definition

Entity Constraints • Entity constraints ensure that every relation of a relational data model has a primary key and that the value of the primary key cannot be NULL • Proof: • Primary key: • Minimal set of attributes that uniquely identify tuples • Say primary key can have nulls: • We don’t need all attributes of a primary key to identify the tuples uniquely • CONTRADICTION!

Referential Constraints • Areferential integrity constraintensures that the foreign key values of a relation must come from the primary key values of the related relation; otherwise, the value of a foreign key must be NULL Violation of a Referential Integrity Constraint

Referential Constraints (cont.) • Representation: • Add an arrow starting from foreign key attribute(s) pointing to the associated primary key attribute(s) Graphical Representation of a Relational Schema

Task 1: Transforming Regular Entities • Transform a regular entity type in an E-R diagram into a relation. • Assign the entity name in an E-R diagram as a relation name. • Make each simple attribute of a regular entity an attribute of a relation. • Make the identifier of the regular entity type the primary key of a relation. Transformation of a Regular Entity

Task 2: Transforming Composite Attributes • Include simple attributes of a composite attribute in the relation. Transformation of a Composite Attribute

Task 3: Transforming Multi-Valued Attributes • Transform a regular entity as described; however, do not add any multi-valued attributes to the relation. • Create a new relation (one for each multi-valued attribute). • The new relation should have two attributes: • Identifier of a regular entity • Multi-valued attribute • The name of the new relation should be a logical name that reflects the meaning of a multi-valued attribute. • The primary key of a new relation is a composite key. • The two attributes of a new relation together serve as its primary key.

Task 3: Transforming Multi-Valued Attributes Transformation of Multi-Valued Attribute

Task 4: One-to-Many Unary Relationships • One-to-Many Unary Relationships: • Transform an entity of a unary relationship as a regular entity as described previously. • Add a new attribute, primary key of the same relation as a foreign key. • Draw an arrow that originates from the foreign key and points towards the primary key. Student Relation After Transformation

Task 4: One-to-Many Unary Relationships Transformation of a Unary One-to-Many Relationship

Task 4: Many-to-Many Unary Relationships • Many-to-Many Unary Relationships: • Transform the entity of a unary relationship as a regular entity. • Create and name a new relation to represent the many-to-many relationship. • The new relation gets the primary key of the entity and a second attribute representing the many-to-many relationship. • Primary key of the new relation is a composite key of the two foreign keys. • Draw referential integrity arrows for the foreign keys. • Add any attributes of the many-to-many relationship in the new relation.

Task 4: Many-to-Many Unary Relationships Transformation of a Unary Many-to-Many Relationship

Task 4: Many-to-Many Unary Relationships Relation Item and the New Relation Component After Transformation

Task 5: One-to-One Binary Relationships • Create two relations, one for each entity. • Transform each entity into a relation as a regular entity. • Include the primary key of one relation as a foreign key to the other relation. • Mandatory side migrates towards the optional side. • Show the referential integrity constraint. • Any attributes on the relationship along with the foreign key migrate toward the optional side of the relationship.

Task 5: One-to-One Binary Relationships Transformation of a Binary One-to-One Relationship

Task 5: One-to-One Binary Relationships Employee and Workstation Relations After Transformation

Task 5: One-to-Many Binary Relationships • Create two relations, one for each entity. • Transform each entity into a relation as a regular entity. • The primary key of a relation on the “one” side of the relationship migrates towards the relation on the “many” side of the relationship. • Show referential integrity constraints. • Any attributes on the relationship along with the foreign key migrate toward the relation on the “many” side of the relationship.

Task 5: One-to-Many Binary Relationships Transformation of a Binary One-to-Many Relationship

Task 5: One-to-Many Binary Relationships Student and Department Relations After Transformation

Task 5: Many-to-Many Binary Relationships • Create two relations, one for each entity. • Transform each entity into a relation as a regular entity. • Create a third new relation to represent the many-to-many relationship. • Primary key from both the relations migrates to the new relation. • Show referential integrity constraints. • Primary key of the new relation is a composite key with foreign keys of relations. • Any attributes of the relationship migrate toward the intermediate relation.

Task 5: Many-to-Many Binary Relationships Transformation of Binary Many-to-Many Telationship

Task 5: Many-to-Many Binary Relationships Transformed Binary Many-to-Many Relationship

Task 6: Transforming Ternary Relationships • Create three relations, one for each entity in a ternary relationship. • Transform each entity into a relation as a regular relation. • Create a fourth relation that represents the ternary relationship. • Primary key from the first three relations migrates to the new relations. • Show referential integrity constraints. • The three foreign keys of the new relation together serve as a composite primary key. • Any attributes of the relationship migrate toward the intermediate relation.

Task 6: Transforming Ternary Relationships Transformation of a Ternary Relationship

Task 6: Transforming Ternary Relationships Transformed Ternary Relationship

Task 7: Transforming Superclass/Subclass Relationships • Create a separate relation for the superclass and each subclass entity. • Transform each entity into a relation as a regular entity. • All common attributes of superclass entity are assigned to the relation for the subclass entity. • Identifier of the superclass serves as the primary key for the superclass relation. • Assign attributes unique to each subclass to each subclass relation. • Primary key of superclass migrates as a foreign key to each subclass relation. • Transform the subclass discriminator as a multi-valued attribute in case of the overlap rule of EER.

Task 7: Transforming Superclass/Subclass Relationships Transformation of a Superclass/Subclass Relationship

Task 7: Transforming Superclass/Subclass Relationships Transformed Superclass/ Subclass Relationship

Task 8: Transforming Weak Entities • Create a relation for the owner entity type. • Create a new relation for a weak entity type and transform it as a regular entity. • Include the primary key of the owner relation as a foreign key. • Show referential integrity constraints. • Primary key of the new relation is the composite key formed by partial identifier attributes of the weak entity and a foreign key.

Task 8: Transforming Weak Entities Transformation of a Weak Entity

Task 9: Transforming Associative Entities • Create two relations, one for each entity types. • Transform them as regular entities. • Create another relation for the associative entity and transform it as a regular entity. • Add the primary key of relations for participating entities as a foreign key for the new relation. • Associative entity with its own identifier becomes primary key for that relation. • If associative entity does not have its own identifier, the keys of the participating entities serve as the composite primary key.

Task 9: Transforming Associative Entities Transformation of an Associative Entity With an Identifier

Web-Enabled Decision Support Systems