سعي

Relational Mappingالتحويل إلى النموذج العلائقي

الشريحة 1 من 15

مقدمة الفصل

ماذا ستتعلم في هذا الفصل

  1. The seven-step algorithm that converts the basic ER constructs into relations.
  2. Mapping strong and weak entity sets, 1:1, 1:N and M:N relationship sets, multivalued attributes and n-ary relationship sets.
  3. The correspondence between ER and relational models.
  4. Step 8: the four options (8A–8D) for mapping specialization or generalization.

أقسام الفصل

  1. 1The seven-step ER-to-relational algorithmخوارزمية الخطوات السبع
  2. 2Steps 1 and 2: strong and weak entity setsالخطوتان 1 و2: الكيانات القوية والضعيفة
  3. 3Step 3: binary 1:1 relationship setsالخطوة 3: علاقات 1:1
  4. 4Step 4: binary 1:N relationship setsالخطوة 4: علاقات 1:N
  5. 5Steps 5 and 6: M:N relationships and multivalued attributesالخطوتان 5 و6: علاقات M:N والصفات متعددة القيم
  6. 6Step 7: n-ary relationship setsالخطوة 7: العلاقات النونية
  7. 7Steps 5 and 6: M:N relationships and multivalued attributesالخطوتان 5 و6: علاقات M:N والصفات متعددة القيم
  8. 8Step 8: mapping specialization or generalizationالخطوة 8: تحويل التخصيص أو التعميم

تلميح: استخدم أسهم لوحة المفاتيح للتنقل بين الشرائح.

تفاصيل المصدرالفصل 5.2 · الشريحة ⁦2⁩
المصدر
الفصل 5.2 · الشريحة ⁦2⁩

The seven stepsالخطوات السبع

The seven-step ER-to-relational algorithm

Converting the basic ER constructs into relations (slide 2)

  1. 1

    Step 1: regular (strong) entity sets

  2. 2

    Step 2: weak entity sets

  3. 3

    Step 3: binary 1:1 relationship sets

  4. 4

    Step 4: binary 1:N relationship sets

  5. 5

    Step 5: binary M:N relationship sets

  6. 6

    Step 6: multivalued attributes

  7. 7

    Step 7: n-ary relationship sets

In the seven-step ER-to-relational algorithm, which construct is mapped in step 5?

تفاصيل المصدرالفصل 5.2 · الشريحة ⁦2⁩
المصدر
الفصل 5.2 · الشريحة ⁦2⁩

The COMPANY ER schemaمخطط COMPANY

The seven-step ER-to-relational algorithm

Entity sets in the ER conceptual schema diagram for the COMPANY database (slide 3)
Entity setAttributes as drawn
EMPLOYEESsn (underlined), Name (composite: Fname, Minit, Lname), Bdate, Address, Sex, Salary
DEPARTMENTName and Number (both underlined), Locations (double oval: multivalued), NumberOfEmployees (dashed oval: derived)
PROJECTName and Number (both underlined), Location
DEPENDENT (double rectangle: weak)Name (dashed underline: partial key), Sex, BirthDate, Relationship
Relationship sets (slide 3)
Relationship setمجموعة العلاقةBetweenبينRatioالنسبةLinesالخطوط
WORKS_FOREMPLOYEE (N) and DEPARTMENT (1)N:1double lines on both sides
MANAGES (attribute StartDate)EMPLOYEE (1) and DEPARTMENT (1)1:1EMPLOYEE single, DEPARTMENT double
CONTROLSDEPARTMENT (1) and PROJECT (N)1:NDEPARTMENT single, PROJECT double
WORKS_ON (attribute Hours)EMPLOYEE (M) and PROJECT (N)M:Ndouble lines on both sides
SUPERVISIONEMPLOYEE as supervisor (1) and as supervisee (N)1:Nsingle lines
DEPENDENTS_OF (double diamond: identifying)EMPLOYEE (1) and DEPENDENT (N)1:NEMPLOYEE single, DEPENDENT double
تفاصيل المصدرالفصل 5.2 · الشريحة ⁦3⁩
المصدر
الفصل 5.2 · الشريحة ⁦3⁩

Steps 1 and 2: entity setsالكيانات

Steps 1 and 2: strong and weak entity sets

Step 1 — regular entity setالكيان القوي
Create a relation R that includes all the simple attributes of E. EMPLOYEE (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary); DEPARTMENT (Dname, Dnumber); PROJECT (Pname, Pnumber, Plocation).
Step 2 — weak entity setالكيان الضعيف
Create a relation R with all simple attributes of the weak entity set and include the primary key of the owner as foreign key attributes of R. DEPENDENT (Essn, name, Sex, Birthdate, Relationship) — Essn is highlighted as the owner's key.

من الشرائح

  • EMPLOYEE has DEPENDENT (DEPENDENT drawn as a weak entity): EMPLOYEE (**EmpNo**, Lname, Fname, DOB); DEPENDENT (**DepNo**, **EmpNo**, FName) — EmpNo in DEPENDENT is the foreign key, in red on slide 6
  • In the schemas on this screen, bold marks the attributes underlined on the slides (the primary key).

How is the weak entity set DEPENDENT of EMPLOYEE mapped?

تفاصيل المصدرالفصل 5.2 · الشرائح ⁦4–6⁩
المصدر
الفصل 5.2 · الشرائح ⁦4–6⁩

Step 3: 1:1 relationshipsعلاقات 1:1

Step 3: binary 1:1 relationship sets

  1. 1Foreign key approachالمفتاح الأجنبيInclude in S the primary key of T. **Better to choose an entity set with total participation in R in the role of S.**
  2. 2Merged relation optionالدمجMerge the two entity sets and the relationship into one relation — **appropriate when both participations are total**.
  3. 3Cross-reference optionعلاقة الربطA **third relation R** cross-referencing the primary keys of S and T.

MANAGES (slide 8)

  • The primary key of EMPLOYEE goes into DEPARTMENT, renamed Mgr_ssn; the relationship attribute Start_date goes too, as Mgr_start_date.
  • EMPLOYEE (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary) and DEPARTMENT (Dname, Dnumber, Mgr_ssn, Mgr_start_date, No.of_Emp), with an arrow from Mgr_ssn to Ssn.

With the foreign key approach for a 1:1 relationship, which entity set should preferably play the role of S (the relation that receives the foreign key)?

تفاصيل المصدرالفصل 5.2 · الشرائح ⁦7–8⁩
المصدر
الفصل 5.2 · الشرائح ⁦7–8⁩

1:1 example: manageمثال 1:1

Step 3: binary 1:1 relationship sets

المعطيات

EMPLOYEE
(EmpNo, Lname, Fname, DOB) — participation (0,1)
BRANCH
(BrnNo, Name) — participation (1,1)
manage
1:1, attributes StartD, EndD

EMPLOYEE (0,1) manage (1,1) BRANCH is 1:1, with attributes StartD and EndD. Which schema results?

تفاصيل المصدرالفصل 5.2 · الشريحة ⁦14⁩
المصدر
الفصل 5.2 · الشريحة ⁦14⁩

Step 4: 1:N relationshipsعلاقات 1:N

Step 4: binary 1:N relationship sets

For each binary 1:N relationship set (slide 9)

  1. 1

    Identify the relation S at the N-side of the relationship set.

  2. 2

    Include the primary key of the other entity set as a foreign key in S.

  3. 3

    Include the simple attributes of the 1:N relationship as attributes of S.

COMPANY 1:N relationships (slide 9)
RelationshipالعلاقةForeign key addedالمفتاح الأجنبيResultالنتيجة
WORKS_FORDnumber of DEPARTMENT → EMPLOYEE.DnoEMPLOYEE (…, Dno)
SUPERVISIONSsn of EMPLOYEE → EMPLOYEE.Super_ssn (same relation)EMPLOYEE (…, Dno, Super_ssn)
CONTROLSDnumber of DEPARTMENT → PROJECT.DnumPROJECT (Pname, Pnumber, Plocation, Dnum)

من الشرائح

  • EMPLOYEE (M) allocate BRANCH: EMPLOYEE(EmpNo, Lname, Fname, DOB, BrnNo); BRANCH(BrnNo, Name) — slide 15

WORKS_FOR is a 1:N relationship: a department has many employees. Where does the foreign key go?

تفاصيل المصدرالفصل 5.2 · الشرائح ⁦9, 15⁩
المصدر
الفصل 5.2 · الشرائح ⁦9, 15⁩

Step 5: M:N relationshipsعلاقات M:N

Steps 5 and 6: M:N relationships and multivalued attributes

Step 5
For each binary M:N relationship set: create a new relation S; include the primary keys of the participating entity sets as foreign keys in S; include any simple attributes of the relationship set.

من الشرائح

  • WORKS_ON (**Essn**, **Pno**, Hours) — slide 10
  • EMPLOYEE M — work-on (hours) — N PROJECT: EMPLOYEE(**EmpNo**, Lname, Fname, DOB); PROJECT(**ProjNo**, Name); Work-on(**EmpNo**, **ProjNo**, hours) — slide 16

Employees work on many projects and each project has many employees, with hours recorded. Which schema represents work-on?

تفاصيل المصدرالفصل 5.2 · الشرائح ⁦10, 16⁩
المصدر
الفصل 5.2 · الشرائح ⁦10, 16⁩

Step 6: multivalued attributesمتعددة القيم

Steps 5 and 6: M:N relationships and multivalued attributes

Step 6
For each multivalued attribute A: create a new relation R; its primary key is the combination of A and K (the key of the entity); if A is composite, include its simple components.

من الشرائح

  • DEPT_LOCATIONS (**Dnumber**, **Dlocation**) — slide 11
  • EMPLOYEE with EmpNo, DOB and the multivalued Tel_no: EMPLOYEE(**EmpNo**, DOB); EMPLOYEE_PHONE(**EmpNo**, **tel_no**) — slide 17

A department can have several locations (multivalued attribute Dlocation). What is the primary key of DEPT_LOCATIONS (Dnumber, Dlocation)?

تفاصيل المصدرالفصل 5.2 · الشرائح ⁦11, 17⁩
المصدر
الفصل 5.2 · الشرائح ⁦11, 17⁩

Step 7: n-ary relationshipsالعلاقات النونية

Step 7: n-ary relationship sets

Step 7
For each n-ary relationship set R: create a new relation S to represent R; include the primary keys of the participating entity sets as foreign keys; include any simple attributes.

من الشرائح

  • SUPPLIER (**Sname**, …); PROJECT (**Proj_name**, …); PART (**Part_no**, …); SUPPLY (**Sname**, **Proj_name**, **Part_no**, Quantity) — slide 19, where the ternary SUPPLY (attribute Quantity) joins SUPPLIER, PROJECT and PART

SUPPLY is a ternary relationship among SUPPLIER, PROJECT and PART with attribute Quantity. Which schema represents it?

تفاصيل المصدرالفصل 5.2 · الشرائح ⁦18–19⁩
المصدر
الفصل 5.2 · الشرائح ⁦18–19⁩

The COMPANY result and the ER–relational correspondenceالنتيجة والتقابل

Steps 5 and 6: M:N relationships and multivalued attributes

Result for COMPANY (slide 12)

  • EMPLOYEE (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Dno, Super_ssn)
  • DEPARTMENT (Dname, Dnumber, Mgr_ssn, Mgr_start_date, No.of_Emp)
  • DEPT_LOCATIONS (Dnumber, Dlocation)
  • PROJECT (Pname, Pnumber, Plocation, Dnum)
  • WORKS_ON (Essn, Pno, Hours)
  • DEPENDENT (Essn, name, Sex, Birthdate, Relationship)
  • In the schemas on this screen, bold marks the attributes underlined on the slides (the primary key).
Correspondence between ER and relational models (slide 20)
ER modelنموذج ERRelational modelالنموذج العلائقي
Entity set"Entity" relation
1:1 or 1:N relationship setForeign key
M:N relationship set"Relationship" relation and two foreign keys
n-ary relationship set"Relationship" relation and n foreign keys
Simple attributeAttribute
Composite attributeSet of simple component attributes
Multivalued attributeRelation and foreign key

In the correspondence between ER and relational models, what does a 1:N relationship set become?

تفاصيل المصدرالفصل 5.2 · الشرائح ⁦12, 20⁩
المصدر
الفصل 5.2 · الشرائح ⁦12, 20⁩

Figure 7.2: the COMPANY relational schemaالشكل 7.2

Steps 5 and 6: M:N relationships and multivalued attributes

Figure 7.2 (slide 13). **Bold** = underlined. The last column lists the arrows (foreign key → referenced attribute).
RelationAttributesArrows
EMPLOYEEFname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno
  • Super_ssn → EMPLOYEE.Ssn
  • Dno → DEPARTMENT.Dnumber
DEPARTMENTDname, Dnumber, Mgr_ssn, Mgr_start_date, No.of Emp (added in a highlighted box)Mgr_ssn → EMPLOYEE.Ssn
DEPT_LOCATIONSDnumber, DlocationDnumber → DEPARTMENT.Dnumber
PROJECTPname, Pnumber, Plocation, DnumDnum → DEPARTMENT.Dnumber
WORKS_ONEssn, Pno, Hours
  • Essn → EMPLOYEE.Ssn
  • Pno → PROJECT.Pnumber
DEPENDENTEssn, Dependent_name, Sex, Bdate, RelationshipEssn → EMPLOYEE.Ssn
تفاصيل المصدرالفصل 5.2 · الشريحة ⁦13⁩
المصدر
الفصل 5.2 · الشريحة ⁦13⁩

Options 8A and 8Bالخياران 8A و8B

Step 8: mapping specialization or generalization

Option 8A — superclass and subclasses8A

  • A relation for C with {k, a1, …, an}; a relation for each subclass Si with {k} ∪ attributes of Si; PK k.
  • Works for any specialization (total or partial, disjoint or overlapping).
  • EMPLOYEE(Ssn, Fname, Minit, Lname, Birth_date, address, Job_type); SECRETARY(Ssn, TypingSpeed); TECHNICIAN(Ssn, TGrade); ENGINEER(Ssn, EngType) — Ssn of each subclass in red (the foreign key).

Option 8B — subclass relations only8B

  • A relation for each subclass with its attributes ∪ {k, a1, …, an}; PK k; no relation for C.
  • Only works if the subclasses are total; only recommended if disjoint.
  • CAR (Vehicle_id, License_plate_no, Price, Max_speed, No_of_passengers); TRUCK (Vehicle_id, License_plate_no, Price, No_of_axles, Tonnage).
The EER diagrams of slides 23–24 and 27–28
SlideSuperclassSpecializationSubclasses
23, 27 (option 8A)
  • EMPLOYEE (Name: FName, MInit, LName
  • Ssn
  • BirthDate
  • Address
  • JobType)
on Job Type (circle, no letter)
  • SECRETARY (TypingSpeed) "Secretary"
  • TECHNICIAN (TGrade) "Technician"
  • ENGINEER (EngType) "Engineer"
24, 28 (option 8B)VEHICLE (VehicleId, Price, LicensePlateNo)d (disjoint)
  • CAR (NoOfPassengers, MaxSpeed)
  • TRUCK (NoOfAxles, Tonnage)
  • Slide 27 draws the 8A result as tables: EMPLOYEE (Ssn, Fname, Minit, Lname, Birth_date, Address, Job_type); SECRETARY (Ssn, Typing_speed); TECHNICIAN (Ssn, Tgrade); ENGINEER (Ssn, Eng_type) — an arrow from each subclass Ssn to EMPLOYEE.Ssn.
  • Slide 28 draws CAR and TRUCK as tables with the attributes above.

Option 8B creates relations for the subclasses only. When does it work?

تفاصيل المصدرالفصل 5.2 · الشرائح ⁦22, 23, 24, 26, 27, 28⁩
المصدر
الفصل 5.2 · الشرائح ⁦22, 23, 24, 26, 27, 28⁩

Options 8C and 8Dالخياران 8C و8D

Step 8: mapping specialization or generalization

Option 8C — one type attribute8C

  • A single relation with all attributes of C and of every subclass ∪ {t}; t is a type (discriminating) attribute naming the subclass of each tuple.
  • Works only for disjoint subclasses.
  • EMPLOYEE (Ssn, Fname, Minit, Lname, Birth_date, Address, Job_type, Typing_speed, Tgrade, Eng_type) — Job_type circled on the slide.

Option 8D — multiple type attributes8D

  • A single relation with all attributes ∪ {t1, …, tm}; each ti is a Boolean showing whether the tuple belongs to Si.
  • Used for overlapping subclasses.
  • PART (Part_no, Description, Mflag, Drawing_no, Manufacture_date, Batch_no, Pflag, Supplier_name, List_price) — Mflag and Pflag are the Boolean type fields, circled on the slide.
The EER diagrams of slides 30–31
SlideSuperclassSpecializationSubclasses
30 (option 8C)EMPLOYEE (as on slide 23)d (disjoint) on Job Type
  • SECRETARY (TypingSpeed)
  • TECHNICIAN (TGrade)
  • ENGINEER (EngType)
31 (option 8D)PART (PartNo, Description)o (overlapping)
  • MANUFACTURED_PART (ManufactureDate, DrawingNo, BatchNo)
  • PURCHASED_PART (SupplierName, ListPrice)

A part can be both manufactured and purchased (overlapping subclasses). Which option and design fits?

تفاصيل المصدرالفصل 5.2 · الشرائح ⁦29–31⁩
المصدر
الفصل 5.2 · الشرائح ⁦29–31⁩

الخلاصة

أنهيت الدرس

الخطوة التالية: ثبّت المعلومات بالبطاقات، ثم اختبر نفسك بالأسئلة.

البطاقاتالتدريب

ما راجعته في هذا الدرس

تفاصيل المصدر
المصدر
الفصل 5.2

خريطة الفصل

    1. 1مقدمة الفصل

  2. The seven-step ER-to-relational algorithmخوارزمية الخطوات السبع

    1. 2The seven stepsالخطوات السبع
    2. 3The COMPANY ER schemaمخطط COMPANY

  3. Steps 1 and 2: strong and weak entity setsالخطوتان 1 و2: الكيانات القوية والضعيفة

    1. 4Steps 1 and 2: entity setsالكيانات

  4. Step 3: binary 1:1 relationship setsالخطوة 3: علاقات 1:1

    1. 5Step 3: 1:1 relationshipsعلاقات 1:1
    2. 61:1 example: manageمثال 1:1

  5. Step 4: binary 1:N relationship setsالخطوة 4: علاقات 1:N

    1. 7Step 4: 1:N relationshipsعلاقات 1:N

  6. Steps 5 and 6: M:N relationships and multivalued attributesالخطوتان 5 و6: علاقات M:N والصفات متعددة القيم

    1. 8Step 5: M:N relationshipsعلاقات M:N
    2. 9Step 6: multivalued attributesمتعددة القيم

  7. Step 7: n-ary relationship setsالخطوة 7: العلاقات النونية

    1. 10Step 7: n-ary relationshipsالعلاقات النونية

  8. Steps 5 and 6: M:N relationships and multivalued attributesالخطوتان 5 و6: علاقات M:N والصفات متعددة القيم

    1. 11The COMPANY result and the ER–relational correspondenceالنتيجة والتقابل
    2. 12Figure 7.2: the COMPANY relational schemaالشكل 7.2

  9. Step 8: mapping specialization or generalizationالخطوة 8: تحويل التخصيص أو التعميم

    1. 13Options 8A and 8Bالخياران 8A و8B
    2. 14Options 8C and 8Dالخياران 8C و8D
    1. 15الخلاصة