ER Diagram introduction .ppt

.

1. Entity Relationship Model

2. Entity Relationship Model
•AnEntityRelationshipDiagram(ERD)isavisual
representationofdifferentdatausingconventionsthat
describehowthesedataarerelatedtoeachother.
•Forexample,theelementswriter,novel,andconsumermaybe
describedusingERdiagramsthisway:
Theelementsinsiderectanglesare
calledentitieswhiletheitemsinside
diamondsdenotetherelationships
betweenentities.

3. Entity
•Anentitycanbeareal-worldobject,eitheranimateorinanimate,
thatcanbeeasilyidentifiable.
–Forexample,inaschooldatabase,students,teachers,
classes,andcoursesofferedcanbeconsideredasentities.
–Alltheseentitieshavesomeattributesorpropertiesthatgive
themtheiridentity.
•Anentitysetisacollectionofsimilartypesofentities.
–Anentitysetmaycontainentitieswithattributesharing
similarvalues.
–Forexample,
–aStudentssetmaycontainallthestudentsofaschool;
–likewiseaTeacherssetmaycontainalltheteachersofaschool
fromallfaculties.Entitysetsneednotbedisjoint.

4. Weak Entity
•Aweakentityisanentitythatdependsontheexistenceof
anotherentity.
•Inmoretechnicaltermsitcandefinedasanentitythatcannotbe
identifiedbyitsownattributes.
•Itusesaforeignkeycombinedwithitsattributedtoformthe
primarykey.
•Anentitylikeorderitemisagoodexampleforthis.
–Theorderitemwillbemeaninglesswithoutanordersoit
dependsontheexistenceoforder.

5. Attributes
•Entitiesarerepresentedbymeansoftheirproperties,
calledattributes.
•Allattributeshavevalues.
•Forexample,astudententitymayhavename,class,
andageasattributes.
•Thereexistsadomainorrangeofvaluesthatcanbe
assignedtoattributes.
•Forexample,astudent'snamecannotbeanumeric
value.Ithastobealphabetic.Astudent'sagecannot
benegative,etc.

6. Types of Attributes
•Simpleattribute−Simpleattributesareatomicvalues,which
cannotbedividedfurther.Forexample,astudent'sphone
numberisanatomicvalueof10digits.
•Compositeattribute−Compositeattributesaremadeofmore
thanonesimpleattribute.Forexample,astudent'scomplete
namemayhavefirst_nameandlast_name.

7. Types of Attributes (contd.,)
•Derivedattribute−Derivedattributesaretheattributesthatdonotexistin
thephysicaldatabase,buttheirvaluesarederivedfromotherattributes
presentinthedatabase.
•Forexample,average_salaryinadepartmentshouldnotbesaveddirectlyin
thedatabase,insteaditcanbederived.
•Foranotherexample,agecanbederivedfromdata_of_birth.
•Forexampleforacircletheareacanbederivedfromtheradius.

8. Types of Attributes (contd.,)
•Single-valueattribute−Single-valueattributescontainsingle
value.Forexample−Social_Security_Number.
•Multi-valueattribute−Multi-valueattributesmaycontainmore
thanonevalues.Forexample,apersoncanhavemorethanone
phonenumber,email_address,etc.
•Forexampleateacherentitycanhavemultiplesubjectvalues.

9. •These attribute types can come together in a
way like −
•simple single-valued attributes
•simple multi-valued attributes
•composite single-valued attributes
•composite multi-valued attributes

10. Entity-Set and Keys
•Keyisanattributeorcollectionofattributesthatuniquely
identifiesanentityamongentityset.
•Forexample,theroll_numberofastudentmakeshim/her
identifiableamongstudents.
•SuperKey−Asetofattributes(oneormore)thatcollectively
identifiesanentityinanentityset.
•CandidateKey−Aminimalsuperkeyiscalledacandidatekey.An
entitysetmayhavemorethanonecandidatekey.
•PrimaryKey−Aprimarykeyisoneofthecandidatekeyschosen
bythedatabasedesignertouniquelyidentifytheentityset.

11. Relationship
•Theassociationamongentitiesiscalledarelationship.
•Forexample,anemployeeworks_atadepartment,a
studentenrollsinacourse.
•Here,Works_atandEnrollsarecalledrelationships.
•Forexample,theentity“carpenter”mayberelatedtotheentity
“table”bytherelationship“builds”or“makes”.Relationships
arerepresentedbydiamondshapesandarelabeledusingverbs.

12. Degree of Relationship
•Thenumberofparticipatingentitiesina
relationshipdefinesthedegreeofthe
relationship.
•Binary=degree2
•Ternary=degree3
•n-ary=degree

13. Mapping Cardinalities
•Cardinalitydefinesthenumberofentitiesinoneentityset,which
canbeassociatedwiththenumberofentitiesofothersetvia
relationshipset.
•One-to-one−OneentityfromentitysetAcanbeassociatedwith
atmostoneentityofentitysetBandviceversa.

14. •One-to-many−OneentityfromentitysetAcanbeassociated
withmorethanoneentitiesofentitysetBhoweveranentity
fromentitysetB,canbeassociatedwithatmostoneentity.

15. •Many-to-one−MorethanoneentitiesfromentitysetAcan
beassociatedwithatmostoneentityofentitysetB,however
anentityfromentitysetBcanbeassociatedwithmorethan
oneentityfromentitysetA.

16. •Many-to-many− One entity from A can be associated with
more than one entity from B and vice versa.

17. ER Diagram Representation
•LetusnowlearnhowtheERModelisrepresentedbymeansof
anERdiagram.
•Anyobject,forexample,entities,attributesofanentity,
relationshipsets,andattributesofrelationshipsets,canbe
representedwiththehelpofanERdiagram.

18. Entity
•Entitiesarerepresentedbymeansofrectangles.
Rectanglesarenamedwiththeentitysetthey
represent.

19. Attributes
•Attributesarethepropertiesofentities.Attributesare
representedbymeansofellipses.
•Everyellipserepresentsoneattributeandisdirectly
connectedtoitsentity(rectangle).

20. •Iftheattributesarecomposite,theyarefurtherdividedina
treelikestructure.Everynodeisthenconnectedtoits
attribute.Thatis,compositeattributesarerepresentedby
ellipsesthatareconnectedwithanellipse.

21. •Multivaluedattributes are depicted by double
ellipse.

22. •Derivedattributes are depicted by dashed ellipse.

23. Relationship
•Relationshipsarerepresentedbydiamond-shapedbox.
•Nameoftherelationshipiswritteninsidethediamond-box.
•Alltheentities(rectangles)participatinginarelationship,are
connectedtoitbyaline.
•BinaryRelationshipandCardinality
•Arelationshipwheretwoentitiesareparticipatingiscalledabinary
relationship.Cardinalityisthenumberofinstanceofanentityfrom
arelationthatcanbeassociatedwiththerelation.

24. •One-to-one−Whenonlyoneinstanceofanentityis
associatedwiththerelationship,itismarkedas'1:1'.The
followingimagereflectsthatonlyoneinstanceofeachentity
shouldbeassociatedwiththerelationship.Itdepictsone-to-
onerelationship.

25. •One-to-many−Whenmorethanoneinstanceofanentityis
associatedwitharelationship,itismarkedas'1:N'.The
followingimagereflectsthatonlyoneinstanceofentityon
theleftandmorethanoneinstanceofanentityontheright
canbeassociatedwiththerelationship.Itdepictsone-to-
manyrelationship.

26. •Many-to-one−Whenmorethanoneinstanceofentityis
associatedwiththerelationship,itismarkedas'N:1'.The
followingimagereflectsthatmorethanoneinstanceofan
entityontheleftandonlyoneinstanceofanentityontheright
canbeassociatedwiththerelationship.Itdepictsmany-to-one
relationship.

27. •Many-to-many−Thefollowingimagereflectsthatmorethan
oneinstanceofanentityontheleftandmorethanone
instanceofanentityontherightcanbeassociatedwiththe
relationship.Itdepictsmany-to-manyrelationship.

28. •Participation Constraints
•Total Participation− Each entity is involved in the relationship.
Total participation is represented by double lines.
•Partial participation− Not all entities are involved in the
relationship. Partial participation is represented by single lines.

29. Generalization Aggregation
•The ER Model has the power of expressing database entities in a conceptual
hierarchical manner.
•As the hierarchy goes up, it generalizes the view of entities, and as we go
deep in the hierarchy, it gives us the detail of every entity included.
•Going up in this structure is calledgeneralization, where entities are
clubbed together to represent a more generalized view.
•For example, a particular student named Mira can be generalized along with
all the students.
•The entity shall be a student, and further, the student is a person. The
reverse is called specializationwhere a person is a student, and that student
is Mira.

30. Generalization
•Theprocessofgeneralizingentities,wherethegeneralizedentitiescontain
thepropertiesofallthegeneralizedentities,iscalledgeneralization.
•Ingeneralization,anumberofentitiesarebroughttogetherintoone
generalizedentitybasedontheirsimilarcharacteristics.Forexample,
pigeon,housesparrow,crowanddovecanallbegeneralizedasBirds.

31. Specialization
•Specializationistheoppositeofgeneralization.In
specialization,agroupofentitiesisdividedinto
sub-groupsbasedontheircharacteristics.
•Takeagroup‘Person’forexample.Apersonhas
name,dateofbirth,gender,etc.These
propertiesarecommoninallpersons,human
beings.Butinacompany,personscanbe
identifiedasemployee,employer,customer,or
vendor,basedonwhatroletheyplayinthe
company.
•Similarly,inaschooldatabase,personscanbe
specializedasteacher,student,orastaff,based
onwhatroletheyplayinschoolasentities.

32. Inheritance
•We use all the above features of ER-
Model in order to create classes of
objects in object-oriented
programming.
•The details of entities are generally
hidden from the user; this process
known asabstraction.
•Inheritance is an important feature of
Generalization and Specialization. It
allows lower-level entities to inherit the
attributes of higher-level entities.
•For example, the attributes of a Person
class such as name, age, and gender can
be inherited by lower-level entities such
as Student or Teacher.

33. ER Diagram Example
•Suppose you are given the following requirements for a simple database for the
NationalHockey League (NHL):
•the NHL has many teams,
•each team has a name, a city, a coach, a captain, and a set of players,
•each player belongs to only one team,
•each player has a name, a position (such as left wing or goalie), a skill level, and
a setof injury records,
•a team captain is also a player,
•a game is played between two teams (referred to as host_teamand guest_team)
andhas a date (such as May 11th, 1999) and a score (such as 4 to 2).
•Construct a clean and concise ER diagram for the NHL database.

35. •Question 2:
•Auniversityregistrar’sofficemaintainsdataaboutthefollowing
entities:
•courses,includingnumber,title,credits,syllabus,and
prerequisites;
•courseofferings,includingcoursenumber,year,semester,section
number,instructor(s),timings,andclassroom;
•students,includingstudent-id,name,andprogram;
•instructors,includingidenti-cationnumber,name,department,
andtitle.
•Further,theenrollmentofstudentsincoursesandgrades
awardedtostudentsineachcoursetheyareenrolledformustbe
appropriatelymodeled.ConstructanE-Rdiagramforthe
registrar’sof-ce.Documentallassumptionsthatyoumakeabout
themappingconstraints.

37. •Question 3:
(a)Construct an E-R diagram for a car-insurance
company whose customers ownone or more cars
each. Each car has associated with it zero to any
number ofrecorded accidents.
(b)Construct appropriate tables for the above ER
Diagram ?

38. Car insurance tables:
•person (driver-id, name, address)
•car (license, year,model)
•accident (report-number, date, location)
•participated(driver-id, license, report-number,
damage-amount)