1 / 87

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

artemas
Download Presentation

Web-Enabled Decision Support Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


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

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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)

  8. 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

  9. 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:

  10. 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

  11. 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)

  12. 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

  13. 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)

  14. 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

  15. 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

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

  17. 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

  18. 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!

  19. 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

  20. 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

  21. 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

  22. 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

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

  24. 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.

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

  26. 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

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

  28. 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.

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

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

  31. 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.

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

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

  34. 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.

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

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

  37. 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.

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

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

  40. 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.

  41. Task 6: Transforming Ternary Relationships Transformation of a Ternary Relationship

  42. Task 6: Transforming Ternary Relationships Transformed Ternary Relationship

  43. 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.

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

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

  46. 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.

  47. Task 8: Transforming Weak Entities Transformation of a Weak Entity

  48. 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.

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

  50. 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

More Related