csce520 lect2

Information about csce520 lect2

Published on November 16, 2007

Author: VolteMort

Source: authorstream.com

Content

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

Related presentations


Other presentations created by VolteMort

Global Supply Chain
13. 04. 2008
0 views

Global Supply Chain

Green Accounting CT 2006
18. 04. 2008
0 views

Green Accounting CT 2006

Chapter 01
10. 04. 2008
0 views

Chapter 01

jmkfah
09. 04. 2008
0 views

jmkfah

climate and science 07
07. 04. 2008
0 views

climate and science 07

2006080708
26. 03. 2008
0 views

2006080708

wcor06
21. 03. 2008
0 views

wcor06

FP7 MTAPU
18. 03. 2008
0 views

FP7 MTAPU

apostles symbols
16. 08. 2007
0 views

apostles symbols

Your First Flex Application
28. 11. 2007
0 views

Your First Flex Application

G070221 00
28. 11. 2007
0 views

G070221 00

how we help you become pregnant
03. 12. 2007
0 views

how we help you become pregnant

greenfieldlrp
03. 10. 2007
0 views

greenfieldlrp

ch4
16. 11. 2007
0 views

ch4

Habitat El Bosque Tropical
20. 11. 2007
0 views

Habitat El Bosque Tropical

ghostgirl
21. 11. 2007
0 views

ghostgirl

serious blow
16. 08. 2007
0 views

serious blow

Gospel
16. 08. 2007
0 views

Gospel

Jesus Feeds 5000
16. 08. 2007
0 views

Jesus Feeds 5000

Exegeting Jesus Parables
16. 08. 2007
0 views

Exegeting Jesus Parables

KNEZEVIC Desimir
04. 10. 2007
0 views

KNEZEVIC Desimir

week9
14. 12. 2007
0 views

week9

coldwar
19. 12. 2007
0 views

coldwar

icde07 hagonzal
23. 11. 2007
0 views

icde07 hagonzal

organic solvents
30. 12. 2007
0 views

organic solvents

ID172 LOGANALY
31. 12. 2007
0 views

ID172 LOGANALY

Meconium
04. 01. 2008
0 views

Meconium

dutch history fo rdummies
11. 12. 2007
0 views

dutch history fo rdummies

life cycle
09. 08. 2007
0 views

life cycle

2006Stats RRT C 5 3
09. 08. 2007
0 views

2006Stats RRT C 5 3

Ethical Issues at End of Life
09. 08. 2007
0 views

Ethical Issues at End of Life

Lecturx7
09. 08. 2007
0 views

Lecturx7

ESI toolkit
07. 11. 2007
0 views

ESI toolkit

Sure of faith
16. 08. 2007
0 views

Sure of faith

HARTLEY WSOWS 20040204
05. 09. 2007
0 views

HARTLEY WSOWS 20040204

Biodiesel web
09. 11. 2007
0 views

Biodiesel web

SM Aula 8 v3
28. 12. 2007
0 views

SM Aula 8 v3

33Chris
04. 01. 2008
0 views

33Chris

Celebration of life martha
09. 08. 2007
0 views

Celebration of life martha

life as an astronomer
09. 08. 2007
0 views

life as an astronomer

Ilene Lewis
09. 08. 2007
0 views

Ilene Lewis

Deep Drill Active HT2
09. 08. 2007
0 views

Deep Drill Active HT2

issues
28. 12. 2007
0 views

issues

families
24. 02. 2008
0 views

families

2005 OSD CAIG Presentation
04. 03. 2008
0 views

2005 OSD CAIG Presentation

sears techmission values2005
16. 08. 2007
0 views

sears techmission values2005

GSutter ppts
24. 06. 2007
0 views

GSutter ppts

griffiths LD summit 08nov06 post
24. 06. 2007
0 views

griffiths LD summit 08nov06 post

Griffiths D4L 26apr07 v2
24. 06. 2007
0 views

Griffiths D4L 26apr07 v2

Charlotte English FINAL
12. 03. 2008
0 views

Charlotte English FINAL

FOREIGN POLICY 1920s 1930s
14. 03. 2008
0 views

FOREIGN POLICY 1920s 1930s

Florence Labord Moodle
24. 06. 2007
0 views

Florence Labord Moodle

Einführung MKT Übungen
24. 06. 2007
0 views

Einführung MKT Übungen

kt cohort seminars sep20 2006
24. 06. 2007
0 views

kt cohort seminars sep20 2006

Karpati EPICT Final Review
24. 06. 2007
0 views

Karpati EPICT Final Review

JCampbell ppt
24. 06. 2007
0 views

JCampbell ppt

Boise Police 2 1 05
06. 11. 2007
0 views

Boise Police 2 1 05

Cheryl Keenan COABE 050505
09. 08. 2007
0 views

Cheryl Keenan COABE 050505

Lum db v1
16. 08. 2007
0 views

Lum db v1

2007 April Poll Powerpoint
05. 09. 2007
0 views

2007 April Poll Powerpoint

Viennot
02. 10. 2007
0 views

Viennot

EUROPEAN E INVESTOR 20040921
03. 10. 2007
0 views

EUROPEAN E INVESTOR 20040921

PulmonaryThromboembo lism
19. 11. 2007
0 views

PulmonaryThromboembo lism

Raffle Pandemic Flu Planning
05. 09. 2007
0 views

Raffle Pandemic Flu Planning

Face
11. 10. 2007
0 views

Face

navypowerpt
30. 11. 2007
0 views

navypowerpt

IHSLG CPD ERes RCSI 07
24. 06. 2007
0 views

IHSLG CPD ERes RCSI 07

retreat petery
16. 08. 2007
0 views

retreat petery

Open Day talk StudentLife
05. 12. 2007
0 views

Open Day talk StudentLife

LSIDs
09. 08. 2007
0 views

LSIDs

HF diff
16. 08. 2007
0 views

HF diff

IKT taikymas pradiniame ugdyme
24. 06. 2007
0 views

IKT taikymas pradiniame ugdyme

Dylan Symposium Presentation
09. 08. 2007
0 views

Dylan Symposium Presentation

Are We There Yet
16. 08. 2007
0 views

Are We There Yet

h0ykom ren
24. 06. 2007
0 views

h0ykom ren

hAykom ren
24. 06. 2007
0 views

hAykom ren

Halton Data Fair Winner MsNeilly
09. 08. 2007
0 views

Halton Data Fair Winner MsNeilly

careers 07
09. 08. 2007
0 views

careers 07

IBC05B
02. 01. 2008
0 views

IBC05B

Eating the Elephant
26. 11. 2007
0 views

Eating the Elephant

LSLR pt2
09. 08. 2007
0 views

LSLR pt2

pgeog251 ch18 af
27. 11. 2007
0 views

pgeog251 ch18 af

henny eyova
24. 06. 2007
0 views

henny eyova

ksypolitos SCH TelematicServices
24. 06. 2007
0 views

ksypolitos SCH TelematicServices

Morgan town 2007
09. 08. 2007
0 views

Morgan town 2007

IMS State of Open Source 0606
24. 06. 2007
0 views

IMS State of Open Source 0606

h dillon training shoestring
24. 06. 2007
0 views

h dillon training shoestring

Click on SUNY for internetII
05. 09. 2007
0 views

Click on SUNY for internetII

kruul
24. 06. 2007
0 views

kruul

228 210 RSCEast PowerPressed
24. 06. 2007
0 views

228 210 RSCEast PowerPressed

kanayama
24. 06. 2007
0 views

kanayama

Businessplan NYSERnet
05. 09. 2007
0 views

Businessplan NYSERnet

gse program
24. 06. 2007
0 views

gse program

ica 20105 cert 2 it 2007
24. 06. 2007
0 views

ica 20105 cert 2 it 2007

Mukherjee sbatransition
09. 08. 2007
0 views

Mukherjee sbatransition

Kinsinger VA
09. 08. 2007
0 views

Kinsinger VA

ALA05O lszewski
29. 11. 2007
0 views

ALA05O lszewski

lec12 04 lcca
09. 08. 2007
0 views

lec12 04 lcca

I GIG2
16. 08. 2007
0 views

I GIG2