03 CHAPTER TWO - CONCEPTUAL DATABASE DESIGN.pdf

database beginners slide

1. 1C O M P I L E D B Y : G / S L A S S I E E .

2. 2C O M P I L E D B Y : G / S L A S S I E E .

3. 3
CHAPTER TWO: Conceptual Database design: THE ER Model
Roles in Database Environment
Data Representation Model
Entities (Strong, Weak and Associative)
Attributes
Simple (Atomic) vs. Composite Attributes
Single-Valued vs. Multi-Valued Attributes
Stored vs. Derived Attribute
Null Values Attribute
Concept of Key Attributes
•(Super key, Candidate key and Primary key)
Relationship
DEGREE of a Relationship
•(Unary/Recursive, Binary, Ternary, N -nary)
Role Names and Recursive Relationships
CARDINALITY of a Relationship (1:1, 1:N, N:1, N:N)
Entity Relationship Diagrams (ERDs )
C O M P I L E D B Y : G / S L A S S I E E .

4. Cont.
RolesinDatabaseEnvironment
Rolesindatabaseenvironment willbeatdifferent
levelsofdatabasedesignanddevelopment.
Heremoreemphasisisgiventothedesignphasesof
thesystemdevelopment lifecycle.
Accordinglythemajorstepsindatabasedesignare;
Planning:thatisidentifyinginformation gapinan
organizationandproposeadatabasesolutiontosolvethe
problem.
Analysis:thatconcentratesmoreonfactfindingabout
theproblem ortheopportunity.Possibilityanalysis,
requirementdeterminationandstructuring,andselection
ofbestdesignmethodarealsoperformedatthisphase.
Design:indatabasedesigningmoreemphasisisgivento
thisphase.Thephaseisfurtherdividedintothreesub-
phases:Conceptual,LogicalandPhysical.
4C O M P I L E D B Y : G / S L A S S I E E .

5. Cont.
ConceptualDesign
Concisedescriptionofthedata,datatype,relationship
betweendataandconstraintsonthedata.Thereisno
implementation orphysicaldetailconsideration.
Conceptual designistheprocessofconstructinga
modeloftheinformationusedinanenterprise.
Itisthesourceofinformationforthelogicaldesign
phase.
MostlyusesanEntityRelationshipModeltodescribe
thedataatthislevel.
AfterthecompletionofConceptualDesignonehasto
goforrefinementoftheschema,whichisverification
ofEntities,Attributes,andRelationships.
5C O M P I L E D B Y : G / S L A S S I E E .

6. Cont.
LogicalDesign
Ahigherlevelconceptualabstractionwithselected
specificdatamodeltoimplementthedatastructure.
Logicaldesignistheprocessofconstructingamodel
oftheinformationusedinanenterprisebasedona
specificdatamodel(e.g.relational,hierarchicalor
networkorobjectoriented),butindependent ofa
particularDBMSandotherphysicalconsiderations.
PhysicalDesign
Itistheprocessofproducingadescriptionofthe
implementation ofthedatabaseonsecondarystorage.
Definesspecificstorageoraccessmethods usedby
database.customized toaspecificDBMS system
operatingsystems.
Developalltechnology&organizationalspecification.
6C O M P I L E D B Y : G / S L A S S I E E .

7. Cont.
Implementation :thetestinganddeployment ofthe
designeddatabaseforuse.
OperationandSupport:administeringandmaintaining
theoperationofthedatabasesystemandproviding
supporttousers.
Aspeopleareoneofthecomponents inDBMS
environment, therearegroupofrolesplayedby
differentstakeholdersatthedifferentlevelsofthe
designingandoperationofadatabasesystem
DBDesigner
ApplicationProgrammer andSystemAnalyst
DBAdministrator
User
7C O M P I L E D B Y : G / S L A S S I E E .

8. Cont.
DatabaseDesigner(DBD)
Identifiesthedatatobestoredandchoosetheappropriate
structurestorepresentandstorethedata.
Shouldunderstandtheuserrequirementandshouldchoose
howtheuserviewsthedatabase.
Wehavetwodistinctionsofdatabase designers,one
involvinginthelogicalandconceptualdesignandanother
involvinginphysicaldesign.
SystemAnalystandApplicationProgrammer
TheSystemsAnalystlooksattherequirements,considers
andotheraspectsoftherequirements, anddesignsthe
solutionatthesystems levelanddesigntheexternal
interfacesandfunctionalityofeachpiece.
TheProgrammer /SoftwareEngineertakesthedesignfrom
theanalystandimplements/codeseachcomponent,testing
alongthewaytoensurethattheimplementation meetsthe
design.
8C O M P I L E D B Y : G / S L A S S I E E .

9. Cont.
DatabaseAdministrator(DBA)
Responsible tooversee,controlandmanage the
databaseresources(thedatabaseitself,theDBMSand
otherrelatedsoftware)
Coordinatingandmonitoringtheuseofthedatabase.
Accountable forproblems likepoorsecurity,poor
performanceofthesystem.
Responsiblefordeterminingandacquiringhardware
andsoftwareresources.
Involvesinallstepsofdatabasedevelopment
Furtherclassificationsofthisroleinbigorganizations
having huge (vast)amount ofdataanduser
requirement.
DataAdministrator(DA)
DatabaseAdministrator(DBA)
9C O M P I L E D B Y : G / S L A S S I E E .

10. Cont.
DataAdministrator(DA)
Itisresponsibleonmanagement ofdataresources.
Involves indatabase planning,development,
maintenance ofstandardspoliciesandproceduresat
theconceptualandlogicaldesignphases.
TheDApositionbeinglargelymanagerialjobhasthe
followingprimaryroles:-
Toprovidecentralizedcontroloverthedataforthe
entireorganization.
Tosetdatadefinitionstandardstoensuretheall
theapplicationsuseconsistentformatandnaming
conventions.
10C O M P I L E D B Y : G / S L A S S I E E .

11. Cont.
DatabaseAdministrator(DBA)
Itismoretechnicallyorientedrole.
Responsible forthephysicalrealizationofthe
database.
Involvesinphysicaldesign,implementation,security
andintegritycontrolofthedatabase.
Thebasictasksareperformance monitoring,backup
andrecovery,andassigningandcontrollingsecurity.
Databaseadministratorsaretrainedinthedetailsof
installing,configuringandoperatingtheDBMS.
11C O M P I L E D B Y : G / S L A S S I E E .

12. Cont.
InformationSystemdevelopment withDatabaseapplication
consistsofseveraltaskswhichinclude:
PlanningofInformationsystemsDesign
Requirements Analysis,
DatabaseDesign(Conceptual,Logical&PhysicalDesign)
Interface,programetcdesignsarealsothere
Implementation
OperationandSupport
Indeveloping agooddesign,oneshouldanswer such
questionsas:
WhataretherelevantEntitiesfortheOrganization
WhataretheimportantfeaturesofeachEntity
WhataretheimportantRelationships
Whataretheimportantqueriesfromtheuser
WhataretheotherrequirementsoftheOrganizationand
theUsers
12C O M P I L E D B Y : G / S L A S S I E E .

13. Cont.
DataRepresentationModel
Realityiswhatexistsintherealworld.Thisreality
mightbeinaphysicalorlogicalform.
Therepresentation wouldbebyconsidering the
propertiestherealityexhibits.
When thesepropertiesassume aspecificvalueit
wouldrepresenttherealworldobjectforwhichdatais
requiredtobecapturedfor.
Thusanydatamodelwillbehavingarepresentation
mechanism forbuildingblocksofdatarepresentation.
Buildingblocksofadatarepresentationmodelare:
1.Entities
2.Attributes
3.Relationship
13C O M P I L E D B Y : G / S L A S S I E E .

14. Cont.
1.TheENTITIES
Realworldphysicalorlogicalobjectforwhichdata
needtobecapturedfor
Physicalexistence(tangible)aparticularperson,
car,houseoremployee.
Logicalexistence(intangible)acompany,ajobor
auniversitycourse.
Thenamegiventoanentityshouldalwaysbea
singularnoundescriptiveofeachitemtobestoredin
it.E.g.:studentNOTstudents
Everyrelationhasaschema,whichdescribesthe
columns,orfieldstherelationitselfcorrespondstoour
familiarnotionofatable
Arelationisacollectionoftuples,eachofwhich
containsvaluesforafixednumberofattributes.
14C O M P I L E D B Y : G / S L A S S I E E .

15. Cont.
ClassificationofEntities
Entitysetscanbebroadlyclassifiedinto3:
1.StrongEntity-isonewhoseexistencedoesnot
dependonotherentityandhavingaprimarykey
(keyattribute).Aweakentityisrepresentedby
single-linedrectangle.E.g.:Student(Strongentity)
Stud_ID(attribute)
2.WeakEntity-isonewhoseexistencedependson
otherentityanddonothaveaprimarykey.Aweak
entityisrepresented bydouble-linedrectangle.
E.g.:Grade(Weakentity)DependonStudent(Strongentity)
3.AssociativeEntity-isanentitythatisformed
whentwodifferententitiesarerelatedusinga
manytomanyrelationship.
Student (Strong entity) Stud_ID Course (Strong entity) C_Code
Course Taken (Associative entity)
15C O M P I L E D B Y : G / S L A S S I E E .

16. Cont.
2.TheATTRIBUTES
PropertiesusedtodescribeeachEntityorrealworld
object.
Attributesarepiecesofinformationaboutentities.
Theitemsofinformation whichcharacterizeand
describetheseentities.
Attributeswillgiverisetorecordeditemsofdatain
thedatabase.
Atthislevelweneedtoknowsuchthingsas:
Attributename(beexplanatorywordsorphrases)
TheDomainfromwhichattributevaluesaretaken
(ADomainisasetofvaluesfromwhichattribute
valuesmaybetaken.)
16C O M P I L E D B Y : G / S L A S S I E E .

17. Cont.
Whether itisPermanent orTime-Varying(which
attributesmaychangetheirvaluesovertime)E.g:Age
Whether itisRequiredorOptionalfortheentity
(whose valueswillsometimes beunknown or
irrelevant)E.g:HouseNumber
TypesofAttributes
1.Simple(Atomic)vs.CompositeAttributes
Simple:containsasinglevalue(notdividedinto
subparts)
E.g.Age,Gender
Composite:Dividedintosubparts(composed of
otherattributes)
E.g.Name,Address
17C O M P I L E D B Y : G / S L A S S I E E .

18. Cont.
2.Single-Valuedvs.Multi-ValuedAttributes
Single-valued:haveonlysinglevalue(thevalue
maychangebuthasonlyonevalueatonetime)
E.g.Name,Sex,Id.No.,Color_Of_Eyes
Multi-Valued:havemorethanonevalue
E.g.Dependent-Name,CollegeDegreesattributefor
aperson;Personmayhaveseveralcollegedegrees
3.Storedvs.DerivedAttribute
Stored:notpossibletoderiveorcompute
E.g.Name,Address
Derived:Thevaluemaybederived(computed)
fromthevaluesofotherattributes.
E.g.Age(currentyear–yearofbirth);Lengthof
employment (currentdate-startdate);Profit
(earning-cost);G.P.A(gradepoint/credithours)
18C O M P I L E D B Y : G / S L A S S I E E .

19. Cont.
4.NullValues(Null“Ok”Attributes)
NULLappliestoattributeswhicharenotapplicableor
whichdonothavevalues.ThemeaningoftheNullis
Unknown.
YoumayenterthevalueNA(meaningnotapplicable)
ThecategoryofNullcanbefurtherclassifiedintotwo
cases.
Whenitisknownthattheattributevalueexistsbut
ismissing.E.g,iftheHeightattributeofapersonis
listedasnull.
Whenitisnotknownwhethertheattributevalue
exists.E.g,iftheHomePhoneattributeofaperson
isnull.
19C O M P I L E D B Y : G / S L A S S I E E .

20. Cont.
5.KeyAttribute
Animportantconstraintontheentitiesofanentity
typeisthekeyoruniquenessconstraintonattributes.
KeyAttribute-itsvaluescanbeusedtoidentifyeach
entityuniquely.
E.g,theNameattributeisakeyoftheCompany entity
type,becausenotwocompanies areallowedtohave
thesamename.
ForthePersonentitytype,atypicalkeyattributeis
SocialSecurityNumber(SSN).
20C O M P I L E D B Y : G / S L A S S I E E .

21. Cont.
ConceptofKeyAttributes
Keyisanattributeorgroupofattributes,whichis
usedtoidentifyarowinarelation.
Keycanbebroadlyclassifiedintothree:-
1.Superkey
2.Candidatekey,and
3.Primarykey
E.g:-Considerthe‘Employee’ relation,whichis
characterizedbytheattributes(EID,EName,Age,
Experience,andSalary).
1.Superkey:-isasubsetofattributesofanentity-set
thatuniquelyidentifiestheentities.Superkeys
representaconstraintthatpreventstwoentitiesfrom
everhavingthesamevalueforthoseattributes.
SuperkeyscanbeEID,EName,Age,andExperience.
21C O M P I L E D B Y : G / S L A S S I E E .

22. Cont.
2.CandidateKey:isasmallestsuperkey.Acandidate
keyforarelationschemaisasmallestsetofattributes
whose values uniquely identifytuplesinthe
correspondingrelation.
Insuchcase,oneofcandidatekeyischosentobe
primarykey,theremainingcandidatekeyarecalled
AlternateKeys.
CandidatekeyscanbeEID,EName,Age.
3.PrimaryKey:isadesignatedcandidatekey.
Itistobenotedthattheprimarykeyshouldnotbe
nullandredundant(ithastobedistinct).
PrimarykeyisEID.
ForeignKey-issetoffieldsorattributesinone
relationthatisusedto“refer”toatupleinanother
relation.
22C O M P I L E D B Y : G / S L A S S I E E .

23. Cont.
3.TheRELATIONSHIPS
Associationsbetweenentitieswhichexistandmustbe
takenintoaccountwhenprocessinginformation.
AssociationiswhatwecallaRELATIONSHIP between
entityobjects.
Arelationshipshouldbenamedbyawordorphrase
whichexplainsitsfunction.
ForeachRELATIONSHIP ,
OnecantalkaboutDEGREE(thenumberofentities)
TheCARDINALITY (number oftuples orrow)
participatingintheassociation.
23C O M P I L E D B Y : G / S L A S S I E E .

24. Cont.
DEGREEofaRelationship
Animportantpointaboutarelationshipishowmany
entitiesparticipateinit.
Thenumberofentitiesparticipatinginarelationshipis
calledtheDEGREE oftherelationship.Among the
Degreesofrelationship,thefollowingarethebasic:
Unary/Recursive Relationship:Tuples/records ofa
Singleentityarerelatedwithyeachother.
BinaryRelationships:Tuples/records oftwoentities
areassociatedinarelationship.
TernaryRelationship:Tuples/recordsofthreedifferent
entitiesareassociated.
Andageneralizedone:
N-naryRelationship:Tuplesfromarbitrarynumberof
entitysetsareparticipatinginarelationship.
24C O M P I L E D B Y : G / S L A S S I E E .

25. 25
Unary/Recursive Relationship
Binary Relationships
C O M P I L E D B Y : G / S L A S S I E E .

26. Cont.
26
DEGREEofaRelationship
C O M P I L E D B Y : G / S L A S S I E E .

27. Cont.
RoleNamesandRecursiveRelationships
TheRoleNamesignifiestherolethataparticipating
entityfromtheentitytypeplaysineachrelationship
instance,andhelpstoexplainwhattherelationship
means.
However,insome casesthesame entitytype
participatesmorethanonceinarelationshiptypein
differentroles.
Insuchcasestherolenamebecomes essentialfor
distinguishingthemeaningofeachparticipates.Such
relationshiptypesarecalledRecursiveRelationships.
E.g.TheSUPERVISION relationshiptyperelatesanemployee
toasupervisor,wherebothemployeeandsupervisorentities
aremembers ofthesameEMPLOYEE entitytype.
Hence,theEMPLOYEE entitytypeparticipatestwicein
SUPERVISION :onceintheroleofsupervisor(orboss),and
onceintheroleofsupervisee(orsubordinate). 27C O M P I L E D B Y : G / S L A S S I E E .

28. Cont.
28C O M P I L E D B Y : G / S L A S S I E E .

29. Cont.
CARDINALITY ofaRelationship
Themajorcardinalitiesofarelationshipare:
ONE-TO-ONE(1:1):onetupleisassociatedwithonly
oneothertuple.
E.g.Building–Location:asasinglebuildingwillbelocated
inasinglelocationandasasinglelocationwillonly
accommodate asingleBuilding.
Head–Department:ADepartment headmanages A
Department.
ONE-TO-MANY(1:N):onetuplecanbeassociatedwith
manyothertuples,butnotthereverse.
E.g.Department –Student:asonedepartment canhave
multiplestudents.
29C O M P I L E D B Y : G / S L A S S I E E .

30. Cont.
MANY-TO-ONE(N:1):manytuplesareassociatedwith
onetuplebutnotthereverse.
E.g.Employee–Department:asmanyemployeesbelongto
asingledepartment.
MANY-TO-MANY (N:N):onetupleisassociatedwith
manyothertuplesandfromtheotherside,witha
differentrolenameonetuplewillbeassociatedwith
manytuples.
E.g.Student–Course:asastudentcantakemany
coursesandasinglecoursecanbeattendedbymany
students.
30C O M P I L E D B Y : G / S L A S S I E E .

31. Cont.
31
CARDINALITY ofaRelationship
C O M P I L E D B Y : G / S L A S S I E E .

32. Cont.
ParticipationofanEntitySetinaRelationshipSet
Participationconstraintofarelationshipisinvolvedin
identifyingandsettingtherequiredoroptionalfeature
ofanentityoccurrencetotakearoleinarelationship.
Therearetwodistinctparticipationconstraintswith
thisrespect,namely:
TotalParticipationand
PartialParticipation
32C O M P I L E D B Y : G / S L A S S I E E .

33. Cont.
TotalParticipation:
Everytupleintheentityorrelationparticipatesinat
leastonerelationshipbytakingarole.
Thismeans,everytupleinarelationwillbeattached
withatleastoneothertuple.
Theentitywithtotalparticipationinarelationshipwill
beconnectedtotherelationshipusingadoubleline.
PartialParticipation:
Sometupleintheentityorrelationmaynotparticipate
intherelationship.
Thismeans,thereisatleastonetuplefromthat
Relationnottakinganyroleinthatspecific
relationship.
Theentitywithpartialparticipationinarelationship
willbeconnectedtotherelationshipusingasingle
line. 33C O M P I L E D B Y : G / S L A S S I E E .

34. Cont.
E.g.1:ParticipationofEMPLOYEE in“belongsto”relationshipwith
DEPARTMENT isTOTALPARTICIPATION sinceeveryemployeeshould
belongtoadepartment.
ParticipationofDEPARTMENT in“belongsto”relationshipwith
EMPLOYEE istotalsinceeverydepartment shouldhavemorethan
oneemployee.
E.g.2:ParticipationofEMPLOYEE in“manages”relationshipwith
DEPARTMENT ,isPARTIALPARTICIPATION sincenotallemployees
aremanagers.
ParticipationofDEPARTMENT in“Manages” relationshipwith
EMPLOYEE istotalsinceeverydepartmentshouldhaveamanager.
34
Employee
Department
Belongs
To
Employee DepartmentManages
C O M P I L E D B Y : G / S L A S S I E E .

35. 35
Cardinality Constraints
C O M P I L E D B Y : G / S L A S S I E E .

36. A) Mandatory cardinalities
A patient must have
recorded at least one
history, and can have many
A patient history is
recorded for one and
only one patient
36
C O M P I L E D B Y : G / S L A S S I E E .

37. 37
B) One Mandatory, One Optional
An employee can be assigned to
any number of projects, or may
not be assigned to any at all
A project must be assigned to
at least one employee, and
may be assigned to many
C O M P I L E D B Y : G / S L A S S I E E .

38. C) Optional Cardinalities
A person is married
to at most one
other person, or
may not be married
at all
38
C O M P I L E D B Y : G / S L A S S I E E .

39. Cont.
ConceptualDatabaseDesign
Conceptual designrevolvesarounddiscoveringand
analyzingorganizationalanduserdatarequirements
Theimportantactivitiesaretoidentify
Entities
Attributes
Relationships
Constraints
Andbasedonthesecomponents developtheERmodel
usingERdiagramcomponents.
39C O M P I L E D B Y : G / S L A S S I E E .

40. Cont.
Beforeworking ontheconceptual designofthe
database,onehastoknowandanswerthefollowing
basicquestions
Whataretheentitiesandrelationshipsinthe
enterprise?
What information about these entitiesand
relationshipsshouldwestoreinthedatabase?
Whataretheintegrityconstraintsthathold?
•Constraintsoneachdatawithrespecttoupdate,
retrievalandstore.
Represent thisinformation pictoriallyinER
diagrams,thenmapERdiagramintoarelational
schema.
40C O M P I L E D B Y : G / S L A S S I E E .

41. Cont.
ModelingTools
TherearemanyERdiagramming tools:
RationalRose,MicrosoftVisio,OracleDesigner,
PowerDesigneretc.
Toidentifytheentities,attributes,relationships,and
constraintsonthedata,therearedifferentsetof
methodsusedduringtheanalysisphase.
Theseincludeinformationgatheredthrough
Interviewingendusersindividuallyandinagroup
Questionnairesurvey
Directobservation
Examiningdifferentdocuments
Generallyunderstandthebusinessrules
41C O M P I L E D B Y : G / S L A S S I E E .

42. Cont.
EntityRelationshipDiagrams(ERDs)
ERDsareamajordatamodelingtoolandwillhelp
organizethedatainyourprojectintoentitiesand
definetherelationshipsbetweentheentities.
Thisprocesshasprovedtoenabletheanalystto
produceagooddatabasestructuresothatthedata
canbestoredandretrievedinamostefficientmanner.
Byusingagraphicalformatitmayhelpcommunication
aboutthedesignbetweenthedesignerandtheuser
andthedesignerandthepeoplewhowillimplementit.
Note:AnentitytypeisrepresentedinERdiagrams(We
areusinganotationforERdiagramsthatiscloseto
theoriginalproposednotation(Chen1976)).
42C O M P I L E D B Y : G / S L A S S I E E .

43. Cont.
Steps:CreatinganERD
Herearethestepsyoumayfollowtocreateanentity-
relationshipdiagram.
IdentifyEntities:
•Thesearetypicallythenounsandnoun-phrasesin
thedescriptivedataproducedinyouranalysis.
•Donotincludeentitiesthatareirrelevanttoyour
domain.
FindRelationships:
•Theseareusuallytheverbsthatconnectthenouns.
•Notallrelationshipsarethisobvious,youmayhave
todiscoversomeonyourown.
•Theeasiestwaytoseeallpossiblerelationshipsis
tobuildatablewiththeentitiesacrossthecolumns
anddowntherows,andfillinthosecellswherea
relationshipexistsbetweenentities.
43C O M P I L E D B Y : G / S L A S S I E E .

44. Cont.
DrawRoughERD:
•Drawtheentitiesandrelationshipsthatyouhave
discovered.
FillinCardinality:
•Determinethecardinalityoftherelationships.
•Youmaywanttodecideoncardinalitywhenyou
arecreatingarelationshiptable.
DefinePrimaryKeys:
•Identifyattribute(s)thatuniquelyidentifyeach
occurrenceofthatentity.
DrawKey-BasedERD:
•Nowaddthem(theprimarykeyattributes)toyour
ERD.
•Reviseyourdiagramtoeliminatemany-to-many
relationships,andtagallforeignkeys.
44C O M P I L E D B Y : G / S L A S S I E E .

45. Cont.
IdentifyAttributes:
•Identifyallentitycharacteristicsrelevanttothe
domainbeinganalyzed.
MapAttributes:
•Determine towhichentityeachcharacteristic
belongs.
•Donotduplicateattributesacrossentities.
DrawfullyattributedERD:
•Nowaddtheseattributes.
•Thediagram may need tobemodified to
accommodate necessarynewentities.
CheckResults:
•Isthediagram aconsistent andcomplete
representationofthedomain.
45C O M P I L E D B Y : G / S L A S S I E E .

46. Cont.
Basic ER-Diagram Notation
The following figures the respective terms in ER -Diagram:
46
For Multivalued Attributes
For Derived Attributes
For Strong Entity
For Weak Entity For Simple and Single
Valued Attributes
For Key Attributes
For Strong Relationship
For Weak Relationship
To Connect the Attribute
and Entity Relationships
For Associative Entity C O M P I L E D B Y : G / S L A S S I E E .

47. Cardinality and Participation Constraints
47
C O M P I L E D B Y : G / S L A S S I E E .

48. 48C O M P I L E D B Y : G / S L A S S I E E .

49. 49
Example of Entity, Attributes and Relationship
C O M P I L E D B Y : G / S L A S S I E E .

50. Cont.
RefiningtheERDesignfortheCOMPANY Database:-
InourE.g.,wespecifythefollowingrelationshiptypes:
MANAGES ,a1:1relationshiptypebetween EMPLOYEE and
DEPARTMENT .
WORKS_FOR ,a1:NrelationshiptypebetweenDEPARTMENT and
EMPLOYEE.
CONTROLS ,a1:NrelationshiptypebetweenDEPARTMENT and
PROJECT.
SUPERVISION ,a1:NrelationshiptypebetweenEMPLOYEE (in
thesupervisorrole)andEMPLOYEE (inthesuperviseerole).
WORKS_ON ,determined tobeanM:Nrelationshiptypewith
attributeHours.
DEPENDENTS_OF ,a1:NrelationshiptypebetweenEMPLOYEE
andDEPENDENT .
Showshowtheschemaforthisdatabaseapplicationcanbedisplayedby
meansofthegraphicalnotationknownasERdiagrams.Wedescribethe
processofderivingthisschemafromthestatedrequirementsandexplainthe
ERdiagrammatic notationasweintroducetheERmodelconceptsinthe
followingsection.
50C O M P I L E D B Y : G / S L A S S I E E .

51. 51
1
N
Supervisor
1: N
EMPLOYEE
FName MName LName
Name
Sex
BDate
EID
Address
Salary
Location Number
Name
DEPARTMENT
Num. of Employee
Works For
Manages
N
1
1
1
Composite
Multi-Valued
Derived
PROJECT
Controls
Location
Number Name
Works On
N
N
1
N
Has
Dependent
Supervision
Relationship BDate
Name
Sex
Supervisee
N: 1
Weak Relationship
Weak Entity C O M P I L E D B Y : G / S L A S S I E E .