Translation of ER-diagram into Relational SchemaLearning ObjectivesSlide 3Slide 4Slide 5Process of Database DesignSlide 7Relational Database ModelSlide 9Transforming E-R Diagrams into RelationsSlide 11Slide 12Slide 13Slide 14Slide 15Slide 16Slide 17Slide 18Slide 19Slide 20Slide 21Primary Key ConstraintsForeign Keys, Referential IntegrityEnforcing Referential IntegrityLogical DB Design: ER to RelationalRelationship Sets to TablesReview: Key ConstraintsTranslating ER Diagrams with Key ConstraintsReview: Participation ConstraintsParticipation Constraints in SQLReview: Weak EntitiesSlide 32Translating Weak Entity SetsReview: Binary vs. Ternary RelationshipsBinary vs. Ternary Relationships (Contd.)Slide 36Slide 37An ExampleConstraints in Create TableNot Null ConstraintPrimary Key ConstraintPrimary Key Constraint (Syntax 2)Another TableForeign Key ConstraintTranslating ER-Diagrams to Table DefinitionsRelations vs. TablesTranslating EntitiesSlide 48Translating Relationships (without constraints)Translating relationships (without constraints)Translating Recursive Relationships (without constraints)Translating relationships (key constraints): Option 1Slide 53Translating relationships (key constraints): Option 2Slide 55Translating relationships (key constraints)Translating relationships (participation constraints)Slide 58Slide 59Slide 60Translating ISA: Option 1Translating ISA: Option 2Which Option To Choose?Translating Aggregation1Translation of ER-diagram Translation of ER-diagram into Relational Schemainto Relational SchemaProf. Sin-Min LeeDepartment of Computer Science2Learning ObjectivesLearning ObjectivesDefine each of the following database termsRelationPrimary keyForeign keyReferential integrityFieldData typeNull value9.29.2Discuss the role of designing databases in the analysis and design of an information systemLearn how to transform an entity-relationship (ER) Diagram into an equivalent set of well-structured relations349.49.456Process of Database Process of Database Design Design •Logical Design–Based upon the conceptual data model–Four key steps1. Develop a logical data model for each known user interface for the application using normalization principles.2. Combine normalized data requirements from all user interfaces into one consolidated logical database model3. Translate the conceptual E-R data model for the application into normalized data requirements4. Compare the consolidated logical database design with the translated E-R model and produce one final logical database model for the application9.69.679.79.78Relational Database ModelRelational Database Model•Data represented as a set of related tables or relations•Relation–A named, two-dimensional table of data. Each relation consists of a set of named columns and an arbitrary number of unnamed rows–Properties•Entries in cells are simple•Entries in columns are from the same set of values•Each row is unique•The sequence of columns can be interchanged without changing the meaning or use of the relation•The rows may be interchanged or stored in any sequence9.89.89Relational Database ModelRelational Database Model•Well-Structured Relation–A relation that contains a minimum amount of redundancy and allows users to insert, modify and delete the rows without errors or inconsistencies9.99.910Transforming E-R Diagrams Transforming E-R Diagrams into Relationsinto Relations•It is useful to transform the conceptual data model into a set of normalized relations•Steps1. Represent entities2. Represent relationships3. Normalize the relations4. Merge the relations9.109.1011Transforming E-R Diagrams Transforming E-R Diagrams into Relationsinto Relations–The primary key must satisfy the following two conditionsa. The value of the key must uniquely identify every row in the relationb. The key should be nonredundant9.119.11129.129.121314Transforming E-R Diagrams Transforming E-R Diagrams into Relationsinto Relations2. Represent Relationships–Binary 1:N Relationships•Add the primary key attribute (or attributes) of the entity on the one side of the relationship as a foreign key in the relation on the right side•The one side migrates to the many side9.149.14159.159.1516Transforming E-R Diagrams Transforming E-R Diagrams into Relationsinto Relations–Binary or Unary 1:1•Three possible optionsa.Add the primary key of A as a foreign key of Bb.Add the primary key of B as a foreign key of Ac.Both9.169.1617Transforming E-R Diagrams Transforming E-R Diagrams into Relationsinto Relations2. Represent Relationships (continued)–Binary and higher M:N relationships•Create another relation and include primary keys of all relations as primary key of new relation9.179.17189.189.1819Transforming E-R Diagrams Transforming E-R Diagrams into Relationsinto Relations–Unary 1:N Relationships•Relationship between instances of a single entity type•Utilize a recursive foreign key–A foreign key in a relation that references the primary key values of that same relation–Unary M:N Relationships•Create a separate relation•Primary key of new relation is a composite of two attributes that both take their values from the same primary key9.199.19209.209.20219.219.2122Primary Key ConstraintsPrimary Key Constraints•A set of fields is a key for a relation if :1. No two distinct tuples can have same values in all key fields, and2. This is not true for any subset of the key.–Part 2 false? A superkey.–If there’s >1 key for a relation, one of the keys is chosen (by DBA) to be the primary key.•E.g., sid is a key for Students. (What about name?) The set {sid, gpa} is a superkey.Primary key can not have null value23Foreign Keys, Referential Foreign Keys, Referential IntegrityIntegrity•Foreign key : Set of fields in one relation that is used to `refer’ to a tuple in another relation. (Must correspond to primary key of the second relation.) Like a `logical pointer’.•E.g. sid is a foreign key referring to Students:–Enrolled(sid: string, cid: string, grade: string)–If all foreign key constraints are enforced, referential integrity is achieved, i.e., no dangling references.–Can you name a data model w/o referential integrity? •Links in HTML!24Enforcing Referential Enforcing Referential IntegrityIntegrity•Consider Students and Enrolled; sid in Enrolled is a foreign key that references Students.•What should be done if an Enrolled tuple with a non-existent student id
View Full Document
Unlocking...