csce520 lect2

60 %
40 %
Information about csce520 lect2

Published on November 16, 2007

Author: VolteMort


ENTITY-RELATIONSHIP (E/R) MODEL:  ENTITY-RELATIONSHIP (E/R) MODEL Reading and Exercises:  Reading and Exercises Database Systems- The Complete Book: Chapter 2 Self testing exercises: 2.1.3 and 2.1.4 2.2.4 2.3.1.b Any of the remaining exercises in the textbook Following lecture slides are modified from Jeff Ullman’s slides for Fall 2002 -- Stanford Purpose:  Purpose Informal Database Design Entity-relationship diagram Easy to convert to implementations (e.g., relational databases) Entity Sets:  Entity Sets Database: collection of entities and relationship among entities Entity: object that exists and distinguishable from other objects Entity set: collection of similar objects Attribute: property of an entity set Each entity in the set has the same properties Attributes :  Attributes Domain: set of permitted values for each attributes Attribute types: Simple vs. composite Single-valued v.s. multi-valued Derived E/R Diagram:  E/R Diagram Entity sets: diagrams Attributes: oval Relationship sets between entity sets: diamond Example E/R Diagram:  Example E/R Diagram Dog Name Breed Age License # Phone Weight Name Name Phone Address Owns Owner Kennel Pays Boards Relationship Set:  Relationship Set Current value of an entity set is the set of entities that belong to it Value of a relationship is a set of lists of currently related entities (one from each entity sets) Example:  Example Example of the Owns relation Degree of Relationship Sets:  Degree of Relationship Sets Number of entity sets participating in a relationship set Binary relationship set: two entity sets (most common) Multiway relationship set: connects more than two entity sets E.g., An owner frequents certain kennels for certain dogs Binary relationship can’t represent these requirements Need 3-way relationship Slide11:  Example 3-Way Diagram Dog Owner Kennel Name Breed Age License # Phone Weight Name Name Phone Address Must Board Slide12:  Example 3-Way Relation Example of the Must-Board relation Mapping Cardinality:  Mapping Cardinality Number of entities to which another entity can be associated via a relationship set One-one Many-one (One-many) Many-many One-One Relationship:  One-One Relationship Each entity of either entity set is related to at most one entity of the other set E.g., assume each owner is allowed to have only 1 dog, and each dog must belong to 1 owner. The Owns relation between Dog and Owner is one-one Many-One Relationship:  Many-One Relationship Each entity of the first set is connected to at most one entity in the second set Entities in the second set can connect to 0, 1, 2, or more entities of the first set. E.g., assume, each dog has at most 1 owner, but an owner may have any number of dogs. TheOwns relation from Dog to Owner is is many-one. Many-Many Relationship:  Many-Many Relationship Entity of either set can be connected to many entities of the other set E.g., assume that a dog may belong to several persons, and a person may have any number of dogs. The Own relation between Dog and Owner is many-many Cardinality of Relationships:  Cardinality of Relationships one-one many-one many-many Representing Cardinality:  Representing Cardinality One-one: arrow in each side of the relationship Many-one: arrow on the “one” side Exactly one: each entity of the one set must be related to exactly on entity of the other set. Rounded arrow on the “one” side Relationship Attribute:  Relationship Attribute Relationships may have attributes Property of the relationship E.g., amount the owners pays to the kennel for boarding their dog Amount Relationship Attribute:  Relationship Attribute Other representation: Create an entity set to represent relationship property Create 3-way relationship among all entities Relationship Attributes:  Relationship Attributes Owner Kennel Pays Cost Amount Roles:  Roles Entity set may appear more then once in a relationship Label the edges between the relationships and the entity set with names called roles. E.g., relationships among the dogs: Dog Related to ancestor descendant Subclasses in E/R:  Subclasses in E/R Special case, fewer entities, more properties E.g., show dog is a dog, but not all dogs are show dogs. It also have properties, type of competition, rank, etc. Assume subclasses form a tree (no multiple inheritance) ISA relationship Example Subclass:  Example Subclass Dog Name Breed Age Weight Show-Dog Rank Type ISA Keys:  Keys Set of attributes for one entity set such that no two entities in the set has the same value for all the attributes of the key Each entity set must have a key Keys:  Keys Super key: set of one or more attributes whose value iniquely determine each entity Candidate key: minimal super key Primary key: a selected candidate key Keys in E/R Diagram:  Keys in E/R Diagram Underline the key attributes In ISA hierarchy: only the root entity set has a key, it is also a key for all subclasses Example Key for Owner :  Example Key for Owner Phone Name Owner Slide29:  Example Multi-Attribute Key for Owner Phone Name Owner Address There are more than One person with the Same name, but they Live at different Places. Weak Entity Sets:  Weak Entity Sets Entities of an entity set need “help” to identify them uniquely Entity set E is said to be weak if in order to identify entities of E uniquely, we need to follow one of more many-one relationships from E and include the key of the related entities from the connected entity sets. Example – Weak Entity Set:  Example – Weak Entity Set Dog Name Breed Phone Name Owns Owner Name and breed do not uniquely Identify a dog. However, name and breed and The name of the owner will Uniquely identify a dog. Representation: double line Around relationship diamond. Many-one relationship Keys for Relationship Sets:  Keys for Relationship Sets Combination of primary keys of participating entity sets Must consider mapping cardinalities to choose the candidate keys Need semantics of relationship set for selecting primary key Design Techniques:  Design Techniques Avoid redundancy Limit use of weak entity sets Don’t use entity set when can use attribute Binary v.s. multiway relationship sets Avoid Redundancy:  Avoid Redundancy Repeat the same thing in two or more different ways Bad because: Wastes space Encourages inconsistency Slide35:  Dog Name Breed Age Phone Weight Name Owns Owner Good Design: Avoid Redundancy Name of the owner Listed only once For each dog Slide36:  Dog Name Breed Age Phone Weight Name Owns Owner Bad Design: Avoid Redundancy Name of the owner Listed twice, once for each dog and once For the relationship Owns Owner Entity Set v.s. Attribute:  Entity Set v.s. Attribute Good Bad Weak Entity Set:  Weak Entity Set Try to create unique ids for entity sets Use weak entity sets only if impossible to create unique ids E.g., can not require all dog owners to give unique names for their pets Student Oracle Accounts:  Student Oracle Accounts Some helpful web links: -> My CSE Account -> Oracle account

Add a comment

Related presentations

Related pages


View Notes - csce520-lect2 from CSCE 522 at South Carolina. ENTITYRELATIONSHIP(E/R) MODEL ReadingandExercises DatabaseSystemsTheCompleteBook:Chapter2 Selft
Read more

csce520-lect2.a - CSCE 520- Relational Data Model Lecture ...

View Notes - csce520-lect2.a from CSCE 520 at South Carolina. CSCE 520- Relational Data Model Lecture 2 Note! Homework 1 is available
Read more