entity-relationship model€¦ · saklvitüal½y ebolr)ay entity-relationship model cmbuk ti 4...
TRANSCRIPT
saklviTüal½y ebolR)ay Entity-Relationship Model
CCMMBBUUkkTTII 44
EEnnttiittyy--RReellaattiioonnsshhiipp MMooddeell
Entity-Relationship (E-R) model KWCa high-level conceptual data model
EdlRtUv)anbegáIteLIgeday Chen kñúgqñaM 1976 edIm,ICYysMrYlkarksag database. Conceptual data model KWCasMnMuén concepts EdlBN’naGMBIrcnasm<½n§ (structure)
én database nig retrieval and update transaction elI database. eKalbMNg CacMbgkñúgkarksag high-level data model KWedIm,IpÁt;pÁg;karyl;eXIjeTAelI Tinñn½yrbs; user niglak;bM)aMgcMnucbec©keTsBak;B½n§dl;karksag database (The main purpose for developing a high-level data model is to support a user’s perception of the data and to conceal the more technical aspects associated with database design.) elIsBIenHeTot conceptual data model KWminGaRs½yeTAnwg DBMS nig Hardware platform EdleRbIR)as;sMrab;begáIt database eT. 1. The Concepts of the Entity-Relationship Model Basic concepts én entity-relationship model rYmman entity types,
relationship types, and attributes.
1. 1. Entity Types (Entity Sets) Entity is a person, place, object, or event in the user environment about
which the organization wishes to maintain data. Entity KWCamnusS/ TIkEnøg/ vtßú rWRBwtþikarN_kñúgmCÄdæanGñkeRbIR)as;Edl
organization cg;eFVIkarEfTaMTinñn½y. ]TahrN_mYycMnYneTAelI entities:
Person : EMPLOYEE, STUDENT, PATIENT Place : STORE, WAREHOUSE, STATE Object : MACHINE, BUILDING, AUTOMOBILE Event : SALE, REGISTRATION, RENEWAL
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 49
saklviTüal½y ebolR)ay Entity-Relationship Model
Entity type (Entity set) is a collection of entities that share common properties or characteristics. Entity type KWCakarRbmUlpþMú entities EdleRbIR)as;lkçN³rYm. RKb; entity type
kñúg entity-relationship diagram EtgEtmaneQμaHEdlsßitkñúgctuekaNEkgmYy (rectangle) ehIyeQ μaHenaHeRcInEtCaGkSrFMTaMgGs; ehIymanlkçN³ singular.
(rUb 4.1) Entity instance or Entity occurrence is a single occurrence of an entity type.
EMPLOYEE
Entity instance rW Entity occurrence KWsMedAelIkarelceLIg rWbgðajEtmþgkñúg entity type. ]TahrN_³
Entity type: EMPLOYEE Attributes: EMPLOYEE NUMBER CHAR(10) NAME CHAR(25) ADDRESS CHAR(30) ZIP CHAR(9) DATE HIRED DATE BIRTHDATE DATE Two Instances of EMPLOYEE:
642-17-8360 534-10-1971 Michelle Brady David Johnson 100 Pacific Avenue 450 Redwood Drive San Francisco Redwood City 98173 97142
03-21-1992 08-16-1994 06-19-1968 09-04-1975 (rUb 4.2)
Strong entity type (Strong entity set) is an entity type that exists independently of other entity type. Strong entity type KWCa entity type EdlekItmaneLIgedayminBwgBak;elI entity type d¾éTeTot. ]TahrN_³ STUDENT, EMPLOYEE, AUTOMOBILE nig COURSE suT§EtCa strong entity type. Weak entity type (Weak entity set) is an entity type whose existence depends on some other entity type.
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 50
saklviTüal½y ebolR)ay Entity-Relationship Model
Weak entity type KWCa entity type EdlekItmaneLIgedayBwgBak;elI entity
type epSgeTot. Weak entity type KμanGtßn½yRKb;RKan;eTxagvis½yCMnYjenAkñúg ER
diagram RbsinebIKμankarcUlrYmBI entity type EdlvaBwgBak;eT. Entity type
EdlvaBwgBak; eKehAfa identifying owner rW owner. Weak entity type Kμan identifier ekIteLIgeday attributes rbs;vaeT ehIyCaTUeTAkñúg ER diagram, weak
entity type man attributes bMerIkarCa partial key. Full key én weak entity type
ekItBIkarcUlpSMKñarvag partial key nig key én owner. Weak entity type
bgðajedayrUb double rectangles. ]TahrN_³ DEPEDENT KWCa weak entity type
eRBaHvak¾ekIteLIg)anGaRs½yelI owner entity EdltP¢ab;CamYyva.
(rUb 4.3)
1. 2. Attributes Attribute is a property of an entity or a relationship type. Attribute KWCalkçN³ rWCabMENkB½t’manén entity rW relationship. CagenHeTA eTot ral; entity instance TaMgGs;én entity type suT§Etman attributes dUcKñaTaMgGs;. Attribute én entity pÞúktMélEdlBN’naGMBI entity nImYy² ehIytMélenaHbgðajBIcMnucd¾ sMxan;énTinñn½yrkSaTukkñúg database.
Has EMPLOYEE
Employee_Name
Employee_ID Dependent_Name BirthDate
Partial key
DEPENDENT
Sex
Weak entity type Owner
Identifying relationship
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 51
saklviTüal½y ebolR)ay Entity-Relationship Model
]TahrN_³ BRANCH entity man attributes dUcCa branch number
(Branch_No), address (Address), phone number (Telephone) nig fax number
(Fax_No). Attribute domain is a set of values that may be assigned to an attribute. Attribute domain KWCasMnMutMélEdl)ankMnt;eTAelI attribute. ral; attributes
TaMgGs;suT§EtmantMélEdlGnuBaØatieGaybBa©Úl rW domain rbs;eKpÞal; ehIyCagenHeTA eTotkarbBa©ÚlTinñn½yeTAkan; attribute én entity type suT§EtTajecjBI domain rbs;va TaMgGs;. ]TahrN_³ Domain én attribute Sex manRbePTTinñn½yCatYGkSr (string or
text) ehIybBa©Úl)anEt 1 tYGkSrKt;KW M rW F. Data Type : Text Field Size : 1 Values : M or F Domain én attribute Score manRbePTTinñn½yCaelxmanek,ós (float or
single) ehIyGacbBa©ÚltMélcab;BI 0 eTAkan; 100. Data Type : Number Field Size : Single Values : 1 to 100 Domain én attribute Employee_Name manRbePTTinñn½yCaGkSr (string or
text) ehIybBa©Úl)anEt 50 tYGkSrKt;. Data Type : Text Field Size : 50 Values : Unlimited Attributes mYycMnYnGaceRbIR)as; domain rYm (Attributes may share a
domain). ]TahrN_³ Address attributes én Staff nig Owner entities eRbIR)as; domain rYm. eyIgeFVIkarEbkEckcMNat;fñak; attribute Ca³ simple or composite; single-valued or multi-valued; or derived.
A. Simple Attribute Simple (Atomic) Attribute is an attribute that cannot be broken down
into smaller components. Simple (Atomic) attribute KWCa attribute EdlminGacbMEbkteTAeTot)an.
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 52
saklviTüal½y ebolR)ay Entity-Relationship Model
]TahrN_ ³ Attribute Sex, Employee_ID, Weight, BirthDate Ca Simple
attribute BIeRBaHvaminGaceFVIkarbMEbkteTAeTot)an. B. Composite Attribute Composite Attribute is an attribute that can be broken down into
component parts. Composite attribute KWCa attribute EdlGaceFVIkarbMEbkeTACaEpñktUc²
teTAeTot)an. CaBiess attribute EbbenHeKGacbMEbk rWminbMEbkGaRs½yeTAtamtMrUvkar rbs;GñkeRbIR)as; database pÞal; ehIyCaTUeTAeKEtgEtbMEbk composite attribute
eTACa simple attribute eRBaHvamanPaBgayRsYlkñúgkarEsVgrk (easy to search). ]TahrN_ ³ Attribute Name KWCa Composite attribute eRBaHvaGac
eFVIkarbMEbkeTACa FirstName, MiddleName nig LastName )an. FirstName Name MiddleName LastName
EteyIgsegáteXIjfa attribute Name CaTUeTAeKeRcInEtbMEbkeTACa FirstName
nig LastName rWmineFVIkarbMEbkEtmþg BIeRBaHeyIgBuMTan;sa‘MueTAnwgkarbMEbkecjeRcIn EbbenHeT nigCaBiesseQμaHrbs;eyIgkMrman MiddleName Nas;. Attribute Address eyIgGacbMEbkeTACa HouseNo, StreetNo, Khan,
Province/City,.... HouseNo Address StreetNo Khan Province/City
C. Single-valued Attribute Single-valued Attribute is an attribute that holds a single value for a
single entity. Single-valued attribute KWCa attribute TaMgLayNaEdlbBa©ÚltMélEtmYyKt;
cMeBaHmYy single entity. ]TahrN_ ³ Attribute Sex én Students entity type eyIgGaceFVIkarbBa©Úl
Tinñn½y)anEtmYyKt; KW Male rW Female edayminGacbBa©ÚlTaMgBIr)aneT. eyIgdwg ehIyfamnusSmñak;EtgEtmanGayurbs;xøÜnEtmYyKt; )ann½yfa ebIGayu 18 KW 18
minGacniyayfa elakmñak;enaHmanGayudl;eTA 2 KW 18 nig 21 eT dUcenH attribute Age
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 53
saklviTüal½y ebolR)ay Entity-Relationship Model
én Students entity Ca single-valued attribute BIeRBaHeyIgGacbBa©Úl)anEtmYytMél Etb:ueNÑaHeT.
D. Multi-valued Attribute Multi-valued Attribute is an attribute that holds a multiple values for
a single entity. Multi-valued attribute KWCa attribute TaMgLayNaEdlGacbBa©ÚltMélCaeRcIn
cMeBaHmYy single entity. ]TahrN_ ³ Branch entity GacmanTUrs½BÞCaeRcIndUcCa 011 721727, 012
754155 nig 016 876312 dUcenHkñúgkrNIenH Tel_No Ca multiple-valued
attribute. Multi-valued attribute GacmansMnMutMélelxkMnt;BItMéltUcbMputeTAdl; tMélFMbMput (Multi-valued attribute may have a set of numbers with upper and
lower limits). ]TahrN_ ³ eK)ankMnt;fa Branch nImYy²mancMnYnTUrs½BÞBI 1 eTA 10
CaedIm. Staff entity man attribute Skill sMrab;kt;Rta Skill Edl Staff ecH ehIymüa:gvijeTotedaysar Staff Gacman Skill elIsBI 1 dUcCa C Programming
Language, Pascal Programming Language nig Visual Basic Programming
Language dUecñHkñúgkrNIenH Skill Ca multi-valued attribute. E. Derived Attribute Derived Attribute is an attribute whose values can be calculated from
related attribute values or a set of attributes values (plus possibly data not in the database, such as today’s date, the current time, or a security code provided by a system user).
Derived attribute KWCa attribute EdltMélrbs;vaKNnaecjBItMél attribute
mYy rWsMnuMén attributes ¬kñúgkrNImYycMnYn Tinñn½yenaHminsßitkñúg database dUcCa today’s date, the current time, or a security code pþl;eday system user¦.
]TahrN_ ³ Age attribute ekIteLIgedayeRbIR)as;GnuKmn_sMrab;KNnarkGayu eTAelI BirthDate attribute dUcenH attribute Age Ca derived attribute. Total_Staff attribute ekIteLIgBIkarrab; (count) eTAelI staffs TaMgGs;kñúg Staff entity.
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 54
saklviTüal½y ebolR)ay Entity-Relationship Model
Attribute Composite attribute Multi-valued attribute Derived attribute
STAFF
Staff_Name
DOB
Address
Staff_No
Sex FName
LName
Skill
SSN
Total_Staff
(rUb 4.4) Keys or Identifier eyIgcat;Tuk Key Ca data item EdlGaceFVIkarkMnt;GtþsBaØaNén entity enAkñúg
entity type. Key EckCa³ Candidate Key An attribute or set of attributes that uniquely
identifies individual occurrences of an entity type. Candidate key KWCa attribute mYy rWeRcInEdlGackMnt;GtþsBaØaNcMeBaH entity
nImYy²enAkñúg entity type. CaTUeTAenAkñúg entity type mYyGacman candidate key
elIsBI 1. ]TahrN_³ cMeBaH Staff entity type xagelI eyIgGackMnt;yk attribute eFVICa
candidate key )ancMnYn 2 KW Staff_No attribute rW SSN attribute. Primary Key The candidate key selected to be the primary key. Primary key KWCa candidate key 1 kñúgcMeNam candidate key TaMgGs;Edl
eyIgeRCIserIsCa primary key elIsBIenHeTotkñúg entity type mYyman primary key
EtmYyKt;. cMeBaH entity type Edlman candidate key elIsBI 1 Gñkksag database
RtUvEteRCIserIsmYykñúgcMeNamenaHmkeFVICa primary key. manlkçxNÐmYycMnYn kñúgkareRCIserIs primary key:
tMélelI primary key attribute kMrnwgERbRbYl. tMélelI primary key attribute CatMélRtwmRtUv (valid value) nigminpÞúktMél
Null (or unknown).
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 55
saklviTüal½y ebolR)ay Entity-Relationship Model
eCosevogkareRbI intelligent key Edlmanrcnasm<½n§bBa¢ak;BIcMNat;fñak; (classification), TIkEnøg (location)...... ]TahrN_³ 2 tYGkSrdMbUgéntMél primary
key sMrab;bBa¢ak;BI warehouse location. BicarNaeTAelIkarCMnYs large composite key eday primary key EdlekIt
eLIgEt 1 attribute. ]TahrN_³ Game_Number attribute én Game entity type
KYrEtGaceRbICMnYs composite key ekItBI Home_Team nig Visiting_Team. ]TahrN_³ cMeBaH Staff entity type xagelI eyIgGaceRCIserIs candidate key
mYykñúgcMeNamenaH KW Staff_No attribute eFVICa primary key nig candidate key
SSN Edlmin)aneRCIerIsCa primary key eKeGayeQμaHfa alternate key. Composite Key A candidate key that consists of two or more
attributes. Composite key KWCa candidate key EdlpSMeLIgeday attributes 2 rWeRcIn. ]TahrN_³ cMeBaH Student_Course entity type Edlman attributes dUcCa
Student_ID, Course_ID, Date_Start, Date_Complete, Grade. edayehtufa Student mñak;GacsikSa Course EtmYydUcKñakñúgeBlxusKña Et Course mYyGacman Student CaeRcInsikSakñúgeBlEtmYy dUcenHeyIgGackMnt;)an candidate key mYycMeBaH entity type enHEdlekIteLIgeday 3 attributes KW Student_ID, Course_ID nig Date_Start dUcenHeKniyayfa Student_Course entity type man composite key
mYyekItBI 3 attributes.
1. 3. Relationship Types (Relationship Sets) Relationship is an association among the instances of one or more entity types that is of interest to the organization. Relationship KWCakarcgTMnak;TMngrvag instances én entity type 1 rWeRcInEdl manplRbeyaCn_cMeBaH organization. Relationship type (Relationship set) is a collection of relationship. Relationship type KWsMnMuén relationship. Relationship instance or Relationship occurrence is a single occurrence of a relationship type. Relationship instance rW Relationship occurrence KWsMedAelIkarelceLIg rWbgðajEtmþgkñúg relationship type.
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 56
saklviTüal½y ebolR)ay Entity-Relationship Model
]TahrN_³ ]bmafa eyIgman entity types cMnYn 2 KW Employee nig Course
ehIy Course mYyGacman Employee CaeRcInsikSa nigbBa©b;vKÁ RBmTaMg Employee
mñak;GacsikSa nigbBa©b; Course CaeRcIn. edIm,Itamdan Course NaxøHEdl Employee
)anbBa©b;vKÁ eyIgeFVIkarkMnt; relationship rvag Employee nig Course entity types.
(rUb 4.5)
EMPLOYEE
Employee_Name
BirthDate
COURSE
Course_Title
Relationship type
Employee_ID Course_ID
Completes
Date_Complete
Degree of a Relationship is the number of entity types that participate in a relationship. Degree of Relationship KWCacMnYn entity types EdlcUlrYmkñúg relationship
enaH. Entity type EdlcUlrYmkñúg relationship enaHeKeGayeQμaHfa participants. Degree of relationship EckCa 4 KW Unary relationship A relationship where the same entity participates
more than once in different roles. Unary relationship KWCa relationship Edl entity dUcKñacUlrYmelIsBImþgeday edIrtYnaTIepSgKña. karkMnt;eQ μaHtYnaTI (role name) mansarHsMxan;Nas;kñúg unary
relationship (rWGacehAmüa:geTotfa recursive relationship) edIm,IkMnt;muxgar (function) rbs; entity kñúgkarcUlrYmenaH. ]TahrN_³ cMeBaH Staff entity type Edlman relationship ekIteLIgEtkñúg entity xøÜnÉgmYyeQμaHfa Supervises eBlenaHeyIgRtUvEtkMnt;muxgareTAeGay entity
EdlcUlrYm. manmuxgar 2 KW Supervisor nig Supervisee Edlbgðajfa Supervisor
eFVIkarRKb;RKgelI Supervisee rW Supervisee sßiteRkamkarRKb;RKgrbs; Supervisor.
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 57
saklviTüal½y ebolR)ay Entity-Relationship Model
STAFF
Supervises
Supervisor Supervisee
(One-to-Many) (One-to-Many)
Supervisor
Supervisee
STAFF Supervises
(One-to-One)
PERSON
Is_married_to
Husband Wife
COURSE
Requires
(Many-to-Many) (rUb 4.6)
Binary relationship A relationship between the instances of two entities.
Binary relationship KWCa relationship rvag instances én 2 entities. Binary
relationship KWCa relationship EdlCYbRbTHeRcInCageKkñúgkarksag database.
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 58
saklviTüal½y ebolR)ay Entity-Relationship Model
]TahrN_³ rUbxageRkambgðajBI relationship EdlekItmancMeBaH Student eFVIkar cuHeQμaH (register) elI Course.
EMPLOYEE DEPARTMENT Manages (One-to-One)
OWNER PROPERTY Owns (One-to-Many)
(Many-to-Many)
STUDENT COURSE Registers_for
(rUb 4.7) Ternary relationship A relationship between the instances of three
entities. Ternary relationship KWCa relationship rvag instances én 3 entities. ]TahrN_³ Relationship Interviews cMeBaHrUbxageRkambgðajsßankarN_Edl Staff mYycMnYnTTYlbnÞúkkñúgkareFVIsMPaseTAelI Client eTAtam Room Edl)ankMnt;.
(rUb 4.8)
CLIENT ROOM Interviews
STAFF
Quaternary relationship A relationship between the instances of four entities.
Quaternary relationship KWCa relationship rvag instances én 4 entities. ]TahrN_³ Relationship Arranges cMeBaHrUbxageRkambgðajsßankarN_Edl GñkTij (Buyer) EdlTTYlkarENnaMBIsμakþI (Solicitor) eRkamkareRCamERCgBI sßab½nhirBaØvtßú (Financial Institution) dak;tMéledjéfø (Bid) eTAelI Property.
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 59
saklviTüal½y ebolR)ay Entity-Relationship Model
(rUb 4.9)
BUYER FINANCIAL INSTITUTION
Arranges
SOLICITOR
BID
1. 4. Attributes on Relationships
Attributes GaceFVIkarP¢ab;eTAkan; relationship CaBiesscMeBaHTMnak;TMng Many-to-Many edIm,IbBa¢ak;Gtßn½yeGaykan;Etc,as;EfmeTot. ]TahrN_³ cMeBaH Date_Complete attribute xagelIP¢ab;eTAkan; relationship Completes edIm,Ibgðaj GMBIkalbriecäTEdl Employee )anbBa©b;karsikSaeTAelI Course. 2. Structural Constraints eBlenHeyIgRkeLkemIleTAelI constraint kMnt;eTAelI entities Edl)ancUlrYm kñúg relationship vijmþg. Constraints KYrEtbgðajBIkardak;kMrit (restrictions) eTAelI relationship dUceTAnwgkargarCak;EsþgEdleyIgEtgEtCYbRbTH. ]TahrN_eTAelI constraint EdlKitfa ral; property RtUvEtman owner nigRKb; branch office dac;xat RtUvEtmanbuKÁlikya:gehacNas;mñak;eFVIkarEdrCaedIm. kardak;kMriteTAelI relationship
EckCa 2 RbePTKW Cardinality and Participation Constraints.
2. 1. Cardinality Constraints Cardinality ratio Describes the number of possible relationships for
each participating entity. Cardinality ratio bgðajBIcMnYnTMnak;TMngEdlGacekItmancMeBaH entity cUlrYm nImYy². Cardinality ratio cMeBaH relationship EckCa 3 RbePTeTotKW One-to-One
(1:1), One-to-Many (1:M) nig Many-to-Many (M:N).
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 60
saklviTüal½y ebolR)ay Entity-Relationship Model
Cardinality ratio rvag entities KWCaeKalkarN_EdlkMnt;enAkñúg organization
edIm,IeGayskmμPaBkargardMeNIr)anRtwmRtUv ehIyeKalkarN_enaHeKeGayeQμaHehAfa business rules. Business rules bgðajCaEpñkd¾sMxan;mYykñúg modeling ehIyminEmn RKb; business rules TaMgGs;suT§EtGacbgðajkñúg ER diagram eT. ]TahrN_eTAelI business rules EbbenaH KWtMrUvkarEdlsmaCik staff TTYl)anéf¶Qb;sMrakbEnßmeTot (additional day’s holiday) erogral;qñaM. One-to-One Relationship sMedAeTAelI entity 1 kñúg entity type A manTMnak;TMngCamYy entity ya:geRcIn bMput 1 kñúg entity type B ehIy entity 1 kñúg entity type B manTMnak;TMngCamYy entity ya:geRcInbMput 1 kñúg entity type A vij.
A1
A2
A3
A4
(A)
B1
B2
B3
(B)
(rUb 4.10)
enAkñúgrUb 4.7 man binary relationship Manages EdltP¢ab;TMnak;TMngrvag Employee entity nig Department entity. Rkumh‘unmaneKalkarN_fa Employee
mñak;GaceFVIkarRKb;RKg)anEtmYy Department b:ueNÑaH ehIy Department mYymanEt Manager RKb;RKgEtmñak;Kt;. eyIgsegáteXIjfa TMnak;TMngenHCa TMnak;TMng One-to-
One.
(One-to-One)
EMPLOYEE DEPARTMENT Manages 1 1
(rUb 4.11)
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 61
saklviTüal½y ebolR)ay Entity-Relationship Model
One-to-Many Relationship sMedAeTAelI entity 1 kñúg entity type A manTMnak;TMngCamYy entity 1 rWeRcInkñúg
entity type B. eTaHbICaya:gNak¾eday entity 1 kñúg entity type B manTMnak;TMng CamYy entity ya:geRcInbMput 1 kñúg entity type A vijEtb:ueNÑaH.
A1
A2
A3
(A)
(B)
B1
B2
B3
B4
B5
(rUb 4.12)
enAkñúgrUb 4.7 man binary relationship Owns EdltP¢ab;TMnak;TMngrvag Owner
entity nig Property entity. Rkumh‘unmaneKalkarN_fa Owner mñak;Gacman Property
CaeRcInCaRTBüsm,tþrbs;xøÜn ehIy Property mYymanEt Owner Etmñak;Kt;RKb;RKgEt b:ueNÑaH. eyIgsegáteXIjfa TMnak;TMngenHCaTMnak;TMng One-to-Many.
OWNER PROPERTY Owns 1 M
(One-to-Many) (rUb 4.13)
Many-to-Many Relationship sMedAeTAelI entity 1 kñúg entity type A manTMnak;TMngCamYy entity 1 rWeRcInkñúg entity type B ehIy entity 1 kñúg entity type B manTMnak;TMngCamYy entity 1
rWeRcInkñúg entity type A vijEdr.
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 62
saklviTüal½y ebolR)ay Entity-Relationship Model
A1
A2
A3
A4
(A)
B1
B2
B3
B4
(B)
(rUb 4.14) enAkñúgrUb 4.7 man binary relationship Registers_for EdltP¢ab;TMnak;TMng rvag Student entity nig Course entity. salamaneKalkarN_fa Student
mñak;GaccuHeQμaHsikSaeTAelI Course )aneRcIn ehIy Course mYyman Student sikSa CaeRcIn. eyIgsegáteXIjfa TMnak;TMngenHCaTMnak;TMng Many-to-Many.
(Many-to-Many)
STUDENT COURSE Registers_for M N
(rUb 4.15)
2. 2. Participation Constraints Participation Constraint Determines whether the existence of an
entity depends upon it being related to another entity through the relationship.
Participation constraint kMnt;faetIkarekItman entity NamYyGaRs½yBwgBak; eTAelIkarTak;TgCamYy entity epSgeTotkñúg relationship.
Participation constraint EckCa 2 RbePTKW total nig partial. Participation
manlkçN³ total RbsinebIkarekItmanén entity NamYyTamTarkarekItmanén entity
epSgeTotkñúg relationship ebIBuMdUecñaHvamanlkçN³ partial. Participation enAkñúg relationship P¢ab;KñaedaybnÞat; (line) EdlbnÞat;mYy (single line) bgðajBIlkçN³ partial ehIybnÞat;BIr (double line) bgðajBIlkçN³ total.
Bakü total nig partial participation CYnkalGacsMedACa mandatory nig optional participation.
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 63
saklviTüal½y ebolR)ay Entity-Relationship Model
]TahrN_³ cMeBaH Branch IsAllocated Staff relationship enAkñúgrUbxageRkam ral; branch office EtgEtmansmaCik staff Canic© eBlenaH participation én Branch
entity manlkçN³ total. eTaHbICaya:gNak¾eday smaCikmYycMnYnén staffs
(]TahrN_/ Sales Personnel) GacminsßitenAkñúg branch office NamYyesaHenAkñúg IsAllocated relationship dUcenH participation én Staff entity manlkçN³ partial. (rUb 4.16)
Branch Staff IsAllocated 1 M
Branch_No Staff_No
eyIgGaceRbIR)as;sBaØaCMnYs (alternative notation) sMrab;bgðaj structural
constraint enAkñúg relationship edayeFVIkarbgðajtMéltUcbMput (minimum value) nig tMélFMbMput (maximum value) Cab;nwgExSbnÞab;tP¢ab; (connecting line) sMrab;bgðajBI participation én entity enAkñúg relationship. ]TahrN_³ eyIgeRbIR)as; notation edIm,IbgðajBI structural constraints enAkñúg Branch IsAllocated Staff relationship dUcrUbxageRkamenH. plRbeyaCn_énkareRbI R)as; notation GacbgðajBIB½t’manbEnßmeToteTAelI constraint enAkñúg relationship. (5, N) notation rvag Branch entity nig IsAllocated relationship bBa¢ak;fa ya:gehacNas; staffs 5 nak;sßitkñúg branch office nImYy² (Min = 5) ehIykñúg branch office nImYy²man staffs minkMnt; (Max = N). dUcKñaenHEdr (0, 1) notation
rvag Staff entity nig IsAllocated relationship bBa¢ak;fa staff mñak;Gacminsßitkñúg branch office NamYy Tal;EtesaH (Min = 0) nig staff mñak;Gacsßitkñúg branch
office ya:geRcInbMput 1 (Max = 1). (rUb 4.17)
Branch Staff IsAllocated
Branch_No Staff_No
(5, N) (0, 1)
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 64
saklviTüal½y ebolR)ay Entity-Relationship Model
3. The Enhanced Entity-Relationship Model Entity-Relationship Model (ER Model) manlkçN³RKb;RKan;sMrab;bgðaj
database schema TUeTAPaKeRcIncMeBaH traditional nig administrative-based
database b:uEnþcab;taMgBIqñaM 1980 mkmankarrIkcMerInya:gxøaMgkñúgkarGPivDÆn_ database
fμI²EdlmanlkçN³sμúKsμajsaMujaMuEdl ER Model BuMGacbMeBj)an eTIbbNþaleGayeK eFVIkarEksMrYledaybEnßmlkçN³mYYycMnYneTAelI ER model edIm,IeFVIeGayvaGacbgðaj Tinñn½ysμúKsμajkñúgeBlbc©úb,nñenHeGay)anRtwmRtUv edaydak;eQ μaHfa Enhanced
Entity-Relationship Model (EER Model). EER Model The model that has resulted from extending the original
ER model with new modeling constructs. EER model KWCa model EdlekItecjBIkarbEnßmlkçN³mYycMnYneTAelI ER
model EdlmanRsab;. Modeling construct fμIehIymansarHsMxan;bMputEdl)anbBa©ÚlenAkñúg EER
model KW Supertype/Subtype relationship.
3. 1. Supertypes (Superclasses) and Subtypes (Subclasses) Subtype A subgrouping of the entities in an entity type which has
attributes that are distinct from those in other subgroupings.
Subtype KWCabNþúMrg entity enAkñúg entity type Edlman Attributes xusBI bNþúMrgepSgeTot. CaTUeTAenAkñúg EER model, subtype EtgEtmantYnaTI (distinct
role) c,as;las;xusBI subtype d¾éTeTot. Supertype An generic entity type that has a relationship with one or
more subtypes. Supertype KWCa entity type TUeTAEdlmanTMnak;TMngCamYy subtypes mYy rWeRcIn.
enAkñúgkrNImYycMnYn entity type mYyRbEhlCaGacman subtypes CaeRcInEdl mantYnaTIxus²Kña. ]TahrN_³ Entities EdlCasmaCikén Staff entity type Gac cat;CaRkumeTAtamtYnaTICa Manager, Secretary nig Sales Personnel
EdlkñúgenaHeKcat;Tuk Staff entity set Ca supertype én subtypes: Manager,
Secretary nig Sales Personnel. Relationship EdlekIteLIgrvag supertype mYynig
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 65
saklviTüal½y ebolR)ay Entity-Relationship Model
subtype NamYykñúgcMeNam subtype TaMgGs; eKehAfa Supertype/Subtype
relationship (Superclass/Subclass relationship). ]TahrN_³ Staff/Manager KWCa supertype/subtype relationship.
ral;smaCikén subtype k¾KWCasmaCikén supertype pgEdr. elIsBIenHeTAeTot smaCikén subtype KWdUceTAnwg entity enAkñúg supertype b:uEnþmantYnaTIxusKña (Each member of a subtype is also a member of the supertype. In othe words, the subclass member is the same as the entity in the supertype, but has a distinct role.). b:uEnþmansmaCikmYycMnYnén supertype GacminEmnCasmaCikén subtype
NamYyTal;EtesaH. Relationship rvag supertype nig subtype KW One-to-One
(1:1) relationship. manehtuplsMxan; 2 EdlnaMeGaymankarEnnaMeGaysÁal;nUv concepts én
supertypes and subtypes KW - ehtuplTI 1: eCosvagkarBN’na concepts RsedogKñaeRcInelIkeRcInsar
edayehtuenHehIy eFVIeGaykat;bnßyeBlevlakñúgkarksag nigeFVIeGayeyIgemIl ER
diagram kan;Etqab;yl; (The first reason is that it avoids describing similar concepts more than once, thereby saving time for the designer and making the ER diagram more readable.).
- ehtuplTI 2: bEnßmB½t’manEfmeToteTAkan;karksagkñúgTMrg;mYyEdlmnusSPaK eRcInGacdwg rWsÁal;ya:gc,as; (The second reason is that it adds more semantic
information to the design in a form that is familiar to many people. ).
3. 2. Attribute Inheritance Subtype entity inherits values of all attributes of the supertype. Subtype entities eFVIkarTTYlykral; attributes TaMgGs;én Supertype. ]TahrN_³ Sales_Personnel subtype TTYlykral; attributes TaMgGs;én
Staff supertype dUcCa Staff_No, Name, Address nig DOB RBmTaMg attributes
pÞal;rbs;vadUcCa Car_Allowance nig Sales_Area. Subtype KWCa entity edayehtudUcenHvaRbEhlCaGacman subtypes Carbs;va
pgEdr. An entity, and its subtypes, and their subtypes, and so on, is called a
type hierarchy. eQμaHEdleKkMnt;eTAelI type hierarchy mandUcCa³
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 66
saklviTüal½y ebolR)ay Entity-Relationship Model
specialization hierarchy (]TahrN_³ Manager is a specialization of staff),
generalization hierarchy (]TahrN_³ Staff is a generalization of Manager) nig IS-A hierarchy (]TahrN_³ Manager IS-A (member of) Staff).
Sales_ Personnel
Car_ Allowance Bonus
Manager
Secretary
O
Typing_ Speed
Sales_ Area
Staff
Address
Sex
Staff_No
Name
FName LName
IsAllocated
Branch
IsAllocated
1
1
1 M
(rUb 4.18)
3. 3. Specialization Specialization The process of maximizing the differences between
members of an entity by identifying their distinguishing characteristics.
Specialization KWWCaRbtibtþikarkñúgBRgIkPaBxusKñarvagsmaCikén entity edayeFVI karkMnt;lkçN³xusEbøkKñarbs;BYkva. Specialization CaRbtibtþikarBIelIcuHmkeRkam (top-down process) kñúgkarkMnt;sMnMuén supertypes nig subtypes rbs;BYkeK ehIykarkMnt;sMnuMén subtypes edayQreTAelImUldæanénlkçN³xusEbøkKña (distinguishing characteristics) én entities enAkñúg supertype. bnÞab;BIeyIg
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 67
saklviTüal½y ebolR)ay Entity-Relationship Model
kMnt;)ansMnMu subtypes én entity type rYcmk eyIgeFVIkarP¢ab; specific attributes
eTAkan; subtype nImYy² (enAeBlcaM)ac;) nigkMnt; relationship pgEdrrvagral; subtypes nImYy² nig entity types rW subtypes d¾éTeTot (enAeBlcaM)ac;). ]TahrN_³ eFVIkarRtYtBinitüeTAelI specialization EdlkMnt;sMnMu subtypes rYmman Manager, Secretary nig Sales Personnel ecjBI Staff supertype. eyIgeRbIR)as; EER diagram edIm,IbgðajCarUbsMKal; specialization dUcrUb 4.18. eyIgkt;sMKal; eXIjfa Staff supertype nig subtypes epSgeTotbgðajCaragctuekaNEkg (rectangle) ehIy subclasses én specialization P¢ab;eTAkan;rgVg;mUl (circle) tamryH bnÞat;mYy (single line) ehIybnÞat;enaHk¾P¢ab;eTAkan; supertype teTAeTot. Subset
symbol or U-Shaped symbol ( ⊂ ) sßitenAelIbnÞat;enaHsMrab;bBa¢ak;BITisedAén supertype/subtype relationship (]TahrN_³ Manager ( ⊂ ) Staff). sBaØa ‘O’
enAkñúg specialization circle bgðajBI constraints elI supertype/subtype
relationship. Specific attributes P¢ab;eTAkan; subtypes rbs;eK ]TahrN_³ dUcCa Car_Allowance nig Sales_Area kñúgrUb 4.18 EdlP¢ab;eTAkan; Sales_Personnel
subtype EtmYyKt;. eyIgRbEhlCaGacman specializations mYycMnYneTAelI entity EtmYyedayQr mUldæaneTAelIlkçN³xusEbøkKña. ]TahrN_³ cMeBaHrUbxageRkam (rUb 4.19) bgðajBI specialization mYyepSgeTot EdlbegáIt subtypes 2 eTotKW Full_Time_Permanent
nig Part_Time_Temporary edayEbgEcklkçN³xusKñaelIkic©snüakarCYlrbs;buKÁlik (employment contract). tamryHrUbenH eyIgk¾bgðajBI specific attributes én Full_Time_Permanent
(Salary_Scale nig Holiday_Allowance) nig Part_Time_Temporary
(Hourly_Rate) subtypes. sBaØa ‘d’ kñúg specialization circle bgðajBI constraints
elI supertype/subtype relationship.
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 68
saklviTüal½y ebolR)ay Entity-Relationship Model
d
Bonus
Manager
Secretary
O
Typing_ Speed
Sales_ Personnel
Car_ Allowance
Sales_ Area
Staff
Address
Sex
Staff_No
Name
FName LName
IsAllocated
Branch
IsAllocated
1 M
(rUb 4.19) 3. 4. Generalization
Generalization The process of minimizing the differences between entities by identifying their common features.
Generalization KWWCaRbtibtþikarkñúgbRgYmPaBxusKñarvagsmaCikén entity edayeFVI karkMnt;lkçN³TUeTArbs;BYkva. Specialization CaRbtibtiþkarBIeRkameLIgelI (bottom-
up process) edayeFVIkarkMnt; generalized supertype ecjBI original subtypes. Rbtibtþikarén generalization manlkçN³pÞúyBI specialization. ]TahrN_³ eFVIkarRtYtBinitüeTAelI Manager, Secretary nig Sales Personnel
EdlbgðajCa entities dac;edayELkBIKña. RbsinebIeyIgeFVIRbtibtþikar generalization
eTAelI entities TaMgenaH KWeyIgcg;kMnt;PaBdUcKñarvagBYkvadUcCa common attributes
nig relationships. dUc)anerobrab;BIxagelI entities TaMgenHeRbIR)as; common
1
1
Part_Time Temporary
Full_Time Permanent
Salary_ Scale
Holiday_ Allowance
Hourly_ Rate
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 69
saklviTüal½y ebolR)ay Entity-Relationship Model
attributes cMeBaH staffs TaMgGs; ehIyehtudUecñHeyIgnwgkMnt; Manager, Secretary nig Sales_Personnel Ca subtypes én supertype Staff dUcrUb 4.18.
3. 5. Constraints on Specialization and Generalization enAkñúgcMnucenH eyIgnwgBN’naGMBI constraints EdlkMnt;eTAelI specialization rW generalization. Constraint TI 1 ehAfa Disjoint constraint. Constraint enHkMnt;fa RbsinebI subtypes én specialization manlkçN³ disjoint enaH entity GacCasmaCik én subtype mYykñúgcMeNam subtypes TaMgGs;én specialization (This constraint specifies that if the subtypes of a specialization are disjoint, then an entity can be a member of only one of the subtypes of the specialization.). sBaØatMNag eGay disjoint constraint tagedaytYGkSr ‘d’ (mann½yfa disjoint) dak;kñúgrgVg;mUl (circle) EdlP¢ab; subtypes eTAkan; supertype. Subtypes cMeBaH specialization
kic©snüakñúgkarCYl (Full_Time_Permanent, Part_Time_Temporary) kñúgrUb 4.19
bgðajBI disjoint constraint mann½yfa kic©snüakñúgkarCYlcMeBaHsmaCikén staff
ebIminmanlkçN³ full-time permanent KWmanlkçN³ part-time temporary
KWminGacmanlkçN³TaMg 2 kñúgeBlEtmYy)aneT. RbsinebI subtypes én specialization KμanlkçN³ disjoint enaHmann½yfa entity GacCasmaCikén subtype kñúg specialization elIsBI 1 (If subtypes of a specialization are not disjoint, then an entity may be a member of more than one subtype of a specialization.). edIm,IbgðajBI nondisjoint constraint
eKeRbItYGkSr ‘o’ (mann½yfa overlapping) dak;kñúgrgVg;mUl (circle) EdlP¢ab; subtypes
eTAkan; supertype. Subtypes cMeBaH specialization tYnaTIkargar (Manager,
Secretary, Sales_Personnel) kñúgrUb 4.19 bgðajBI nondisjoint constraint mann½yfa staff mYyGacCasmaCikén Manager nig Sales_Personnel subtypes TaMg 2 Etmþg. Constraint TI 2 elI specialization ehAfa participation constraint
EdlGacCa total rW partial. Specialization Edlman total participation kMnt;fa ral; entity nImYy²enAkñúg supertype RtUvEtCasmaCikén subtype kñúg specialization (A specialization with a total participation specifies that every entity in the
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 70
saklviTüal½y ebolR)ay Entity-Relationship Model
supertype must be a member of a subtype in the specialization.). edIm,IbgðajBI total participation eKeRbIExSbnÞat;BIr (double line) KUrP¢ab;BI supertype eTAkan; specialization circle. enAkñúgrUb 4.19 kic©snüakñúgkarCYlmanlkçN³ total
participation Edlmann½yfa ral;smaCikTaMGs;én staff suT§Et part-time rW full-time. Specialization cMeBaH partial participation kMnt;fa entity mYymincaM)ac;Ca kmμsiT§ rWCasmaCikén subtypes NamYyTal;EtesaHkñúgcMeNam subtypes TaMgGs;eT (A specialization with partial participation specifies that an entity need not belong to any of the subtypes of a specialization.). Partial participation
bgðajedayExSbnÞat;mYy (single line) KUrP¢ab;BI supertype eTAkan; specialization
circle. enAkñúgrUb 4.19 specialization tYnaTIkargarmanlkçN³ partial participation Edlmann½yfa smaCikén staff mincaM)ac;mantYnaTIkargarbEnßmeToteTdUcCa Manager,
Secretary or Sales_Personnel. karcUlrYmKñarvag disjoint nig participation constraints )anbegáItCa 4 RbePTKW disjoint and total, disjoint and partial, overlapping and total nig overlapping
and partial. 4. Transforming EER Diagrams into Relations
eRkayBIbegáIt EER diagram rYc eyIgRtUvbMElgvaeTACa relations (tables)
edayeRbIR)as;sMnMuénc,ab;mYycMnYnsMrab;kMnt;dUcteTA³ Step 1: Map Regular Entities ral; entity type FmμtaTaMgGs;enAkñúg ER diagram RtUvbMElgeTACa relation
(table) ehIyeQ μaH relation dUcnwgeQμaH entity type. ral; simple attribute TaMgGs; én entity type nwgbMElgeTACa attribute én relation elIsBIenHeTot key rW identifier
én entity type nwgbMElgeTACa primary key én relation.
Staff_No STAFF
Name Sex
Address
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 71
saklviTüal½y ebolR)ay Entity-Relationship Model
(rUb 4.20a) Staff (Staff_No, Name, Sex, Address) (rUb 4.20b)
Composite Attributes enAeBleFVIkarbMElgcMeBaH entity FmμtaEdlman composite attribute eyIgKYrEtbBa©Úl simple attributes TaMgGs;EdlCabMENkén composite attribute eTAkan; relation rWrkSaEt composite attribute enaH b:uEnþviFIEdl eKeRbIR)as;jwkjab;CageKKWbBa©Úl simple attributes TaMgGs;én composite attribute
eTAkan; relation.
(rUb 4.21a) Staff (Staff_No, Name, Sex, HouseNo, StreetNo, Province) (rUb 4.21b)
Multivalued Attributes enAeBleFVIkarbMElgcMeBaH entity FmμtaEdlman multivalued attribute eyIgRtUvEtbMElgvaeTACa 2 relations eday relation TI 1pÞúknUv simple attributes TaMgGs;én entity type elIkElgEt multivalued attribute ehIy relation TI 2 pÞúk attributes cMnYn 2 Edl attribute 1 kñúgcMeNamenaHKWCa primary key
én relation TI 1 nig attribute 1 eTotKWCa multivalued attribute elIsBIenHeTot primary key én relation TI 2 pSMeLIgeday attributes TaMg 2 enH.
(rUb 4.22a) Staff (Staff_No, Name, Sex Address) Staff_Skill (Staff_No, Skill) (rUb 4.22b)
Staff_No
Province
STAFF
Name Sex
Address
HouseNo StreetNo
STAFF
Name
Staff_No
Sex
Skill
Address
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 72
saklviTüal½y ebolR)ay Entity-Relationship Model
Step 2: Map Weak Entities eKeFVIkarbMElg weak entity edayeFVIkarbegáIt relation fμImYypÞúk simple
attributes TaMgGs;én weak entity RBmTaMg primary key én identifying owner Edl attribute enaHnwgkøayeTACa foreign key. Primary key én relation fμIenHpSMeday primary key én identifying owner rYmCamYy partial key én weak entity.
(rUb 4.23a) Employee (Employee_ID, Employee_Name) Dependent (Employee_ID, Dependent_Name, Sex, BirthDate) (rUb 4.23b)
Has EMPLOYEE
Employee_Name
Employee_ID Dependent_Name BirthDate
DEPENDENT
Sex
Step 3: Map Binary Relationships Cardinality ratio cMeBaH binary relationship EckecjCa 3 RbePTeTotKW
One-to-One, One-to-Many nig Many-to-Many. Map Binary One-to-Many Relationships eKeFVIkarbMElg binary 1:M relationship edaybegáIt relation epSg²KñasMrab;
entity types TaMg 2 Edl)ancUlrYmenAkñúg relationship. bnÞab;mkbEnßm primary key
én one-side entity eTAkan; many-side entity edIm,IeGayvakøayeTACa foreign key
eTIb relations TaMg 2 GaceFVIkarTak;TgKña)an.
CUSTOMER
Customer_Name
Address
ORDER
Order_Date Customer_ID Order_ID
Submits
(rUb 4.24a)
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 73
saklviTüal½y ebolR)ay Entity-Relationship Model
Customer (Customer_ID, Customer_Name, Address) Order (Order_ID, Order_Date, Customer_ID) (rUb 4.24b) Map Binary Many-to-Many Relationships eKeFVIkarbMElg binary M:N relationship edaybegáIt relation epSg²KñasMrab;
entity types TaMg 2 Edl)ancUlrYmenAkñúg relationship nigbegáIt relation fμImYy epSgeTotedaypÞúk primary key én relations TaMg 2 ehIy attributes TaMg 2 enaH PaKeRcIn eRcInEtcUlrYmpSMKñaeFVIkarCa primary key én relation fμIenaH.
EMPLOYEE
Employee_Name
BirthDate
COURSE
Course_Title Employee_ID Course_ID Completes
Date_Complete Result
(rUb 4.25a)
Employee (Employee_ID, Employee_Name, BirthDate) Course (Course_ID, Course_Title) Completes (Employee_ID, Course_ID, Date_Complete, Result) (rUb 4.25b)
Map Binary One-to-One Relationships
eKeFVIkarbMElg binary one-to-one relationship edayGnuvtþCa 2 CMhan. CMhanTI 1 RtUveFVIkarbegáIt relation nImYy²sMrab; entity types TaMg 2nigCMhanTI 2 Taj primary key én relation NamYyeTAdak;kñúg relation mYyeTotedIm,IkøayeTACa foreign
key.
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 74
saklviTüal½y ebolR)ay Entity-Relationship Model
(rUb 4.26a)
Employee (Employee_ID, Employee_Name, Address) Department (Dept_ID, Dept_Name, Phone, Mgr_ID) (rUb 4.26b) Step 4: Map Unary Relationships Cardinality ratio cMeBaH unary relationship EckecjCa 3 RbePTeTotKW
One-to-One, One-to-Many nig Many-to-Many. Map Unary One-to-One and One-to-Many Relationships eKeFVIkarbMElg unary 1:M relationship edaybegáIt relation mYyedaybBa©Úl
simple attributes TaMgGs; bnÞab;mkbEnßm foreign key eTAkan; relation dEdlenaH Edlcg¥úl rWTajtMél (references) eTAkan; primary key enAkñúg relation EtmYy.
(rUb 4.27a) For One-to-One relationship Person (Person_ID, Name, Sex, Address, Partner_ID) (rUb 4.27b)
EMPLOYEE
Employee_Name
Address
DEPARTMENT
Phone Employee_ID Dept_ID
Manages
Dept_Name
PERSON
Name
SexPerson_ID
Married
Address
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 75
saklviTüal½y ebolR)ay Entity-Relationship Model
(rUb 4.28a) For One-to-Many relationship Staff (Staff_ID, Staff_Name, Address, Supervisor_ID) (rUb 4.28b)
STAFF
Staff_Name
AddressStaff_ID
Supervises
Map Unary Many-to-Many Relationships eKeFVIkarbMElg unary M:N relationship edaybegáIt relation fμIcMnYn 2 eday
relation TI 1bgðajBI entity type enAkñúg relationship ehIy relation mYyepSgeTot bgðajBITMnak;TMng M:N CamYyxøÜnÉgpÞal;.
(rUb 4.29a) Course (Course_ID, Description, Credit) Course_Pre (Course_ID, Pre_Take) (rUb 4.29b)
COURSE
Description
CreditCourse_ID
Requires
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 76
saklviTüal½y ebolR)ay Entity-Relationship Model
Step 5: Map Ternary, Quaternary and n-ary Relationships eKeFVIkarbMElg ternary, quaternary and n-ary relationship edaybegáIt
relation epSg²KñasMrab; entity types EdlcUlrYmkñúg relationship nigbegáIt relation fμI mYyeTotedaypÞúk primary key én relations TaMgenaH kñúgkrNImYycMnYn attributes
TaMgenaHnwgkøayeTACa primary key sMrab; relation fμIenaH.
(rUb 4.30a)
Patient (Patient_ID, P_Name) Physician (Physician_ID, Phy_Name) Treatment (Treatment_Code, Description) Patient_Treatment (Patient_ID, Physician_ID, Treatment_Code, Date,
Result)
PATIENT
Patient_ID P_Name
PHYSICIAN
Physician_ID Phy_Name
TREATMENT Treatment_Code Description
Patient Treatment
Date
Result
(rUb 4.30b) Step 6: Map Supertype/Subtype Relationships manviFICaeRcInkñúgkarbMElg supertype/subtype relationship eTACa relations
b:uEnþminTan;manviFINamYyeKcat;TukCa standard enAeLIy b:uEnþeyIgelIkykviFINaEdl eKeRbIR)as;jwkjab;CageKEtb:ueNÑaH.
1. begáIt relation nImYy²sMrab; supertype nig subtypes nImYy² 2. bBa©Úl common attributes eTAkan; supertype relation edayrYmTaMg
primary key pgEdr
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 77
saklviTüal½y ebolR)ay Entity-Relationship Model
3. bBa©Úl primary key én supertype relation eTAkan; subtype relation
nImYy² RBmTaMg specific attributes cMeBaH subtype relation nImYy² 4. bBa©Úl attribute 1 (rWeRcIn) eTAkan; supertype eGayedIrtYnaTICa subtype
discriminator Subtype discriminator KW attribute sßitkñúg supertype entity EdltMélrbs;va
eRbIR)as;sMrab;bg¥úl rWbgðajBI subtype. kñúgkrNIEdl relationship manlkçN³ disjoint eBlenaHeKeRbIR)as; subtype
discriminator EtmYyehIytMélvaeRbIR)as;sMrab;cg¥úl subtype ]TahrN_³ tamryHrUb 4.31a eyIgeXIjfa ebIeyIgbBa©ÚltYGkSr “M” eTAkan; subtype discriminator mann½y fa hardware enaHmanRbePTCa Mouse b:uEnþebIbBa©ÚltYGkSr “K” mann½yfa hardware
enaHmanRbePTCa Keyboard ehIyebIbBa©ÚltYGkSr “H” vij mann½yfa hardware
enaHmanRbePTCa Hard disk.
Hard-Disk
Buttons
Mouse
Keyboard
D
Keys Size
Hardware
Made Year
Hardware ID
Model
H_Type
H_Type =
“M”
“K”
“H” Subtype discriminator
(rUb 4.31a)
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 78
saklviTüal½y ebolR)ay Entity-Relationship Model
Hardware (Hardware_ID, Made_Year, Model, H_Type) Mouse (Hardware_ID, Buttons) Keyboard (Hardware_ID, Keys) Hard_Disk (Hardware_ID, Size) (rUb 4.31b)
pÞúyeTAvij RbsinebI relationship manlkçN³ overlap eKRtUveRbIR)as; subtype
discriminator elIsBImYy ehIy subtype discriminators TaMgenaHGacpÞúktMél)anEt 2
Kt;KW True nig False. ]TahrN_³ tamryHrUb 4.32a eyIgeXIjfa ebIeyIgbBa©Úl staff
NamYyEdlmantYnaTIdl;eTA 2 Ca Manager nig Sale Personnel enaHeyIgRtUv bBa©ÚlcMeBaH record enaHnUv MFlag esμI True ehIIy SFlag esμI False nig PFlag esμI True.
O
STAFF
Name
Staff ID
Sex
S_Type
S_Type =
Address
Sale_
Bonus
Manager
Secretary
Typing_SpeedSale_ Area
Car_ Allowance
Manager?=”Y” Sale Personnel?=”Y”
Secretary?=”Y”
Manager?
Secretary?
Sale Personnel?
(rUb 4.32a)
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 79
saklviTüal½y ebolR)ay Entity-Relationship Model
Staff (Staff_ID, Name, Sex, Address, MFlag, SFlag, PFlag) Manager (Staff_ID, Bonus) Secretary (Staff_ID, Typing_Speed) Sale_Personnel (Staff_ID, Sale_Area, Car_Allowance)
(rUb 4.32b) Review Questions
1. cUrBnül;eGay)anek,aHk,aynUvBaküxageRkam (a) entity (b) relationship (c) weak entity 2. GVIeTACa attribute? EckCab:unμanRbePT? cUrBnül; 3. GVIeTACa degree of relationship? EckCab:unμanRbePT? cUrBnül; 4. GVIeTACa cardinality ratio? EckCab:unμanRbePT? cUrBnül; 5. cUrBnül;BI Attribute inheritance?
6. cUrBnül;BI Generalization, Specification nig constraint elI supertype/subtype relationship?
7. cUrBnül;BICMhankñúgkarbMElg EER diagrams eTACa relations?
rrYss
eroberogedaysa®sþacarü kaMg cnßa TMB½rTI 80