csce520 lect2

Download
Share  Add to  Flag Embed
Uploaded from authorPOINTLite
Views:
 
Category: Entertainment
     
 
License:   All Rights Reserved

Presentation Description

No description available.

Comments

 

By: binduz (39 month(s) ago)

pls send this ppt to binbin656@gmail.com

 

By: mohamedmeeran (46 month(s) ago)

it, was very good , how can i down lode this.

Presentation Transcript

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: https://www.cse.sc.edu -> My CSE Account -> Oracle account http://www.cse.sc.edu/~okeefe/tutorials/oracle_sqlplus/601.html