1 / 65

More on ER Model Detail Relationships

More on ER Model Detail Relationships. 4. Detail Conceptual Data Model. Attributes. Attribute A property or characteristic of an entity type that is of interest to the organisation Simple Attribute An attribute that cannot be broken down into smaller components e.g. Emp No. Emp No.

alicia
Download Presentation

More on ER Model Detail Relationships

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. More on ER Model Detail Relationships 4

  2. Detail Conceptual Data Model Attributes • Attribute • A property or characteristic of an entity type that is of interest to the organisation • Simple Attribute • An attribute that cannot be broken down into smaller components e.g. Emp No Emp No

  3. First Name Mid Initials Name Last Name Attributes Cont’d • Multi-valued Attribute • An attribute that may take on more than one value for a given entity instance e.g. Employee Skills, Qualifications • Composite Attribute • An attribute that can be broken down into component parts e.g. Address (Street, City, State, Postal Code) Name (First Name, Middle Initials, Last Name) Skills

  4. Attributes Cont’d • Stored Attribute • An attribute whose valued is stored in the database • Derived Attribute • An attribute whose values can be calculated from related attribute values e.g. Years Employed (using Employed Date) Age (using Date of Birth) Age

  5. Employee Flight Emp No Flight Id Flight No Date Identifier • Identifier • An attribute (or combination of attributes) that uniquely identifiers individual instances of an entity type e.g. Emp No • Composite Identifier • An identifier that consists of a composite attribute e.g. Flight Id (Flight No, Date)

  6. Identifier • Choose an identifier that will not change its value over the life of each instance of the entity type • Choose an identifier such that each instance of the entity type, the attribute is guaranteed to have valid values and not be null (or unknown) • Avoid the use of so-called intelligent identifiers, whose structure indicates classifications, etc. • Consider substituting single-attribute identifiers for large composite identifiers

  7. Detailed Conceptual Design Attributes Department Number Location Manager Start date Name Phone Control Projects Employees work for Number of Employees

  8. Dept No Dept Name Department Location Phone Employees Detailed Conceptual Design Dept No unique identifier of a dept. Identifier Dept Name nameof a department Unique Location location of a department Multi-valued Phone phone no. of a department Employees no. of employees in a dept. Derived

  9. Proj No Project Proj Name Location Detailed Conceptual Design Project Name Number Location Department Control Leader  Employee Proj No unique identifier of a project Identifier Proj Name name of a project Unique Location location of a project

  10. Detailed Conceptual Design Employee Name National ID Address Salary Sex Birth Date Works for Department Supervise Employee Emp No

  11. Detailed Conceptual Design Employee Emp No unique identifier of an emp. Identifier Emp Name name of an employee Composite First Name first name of an employee Mid Initials middle initials of an employee Last Name last name of an employee NID national id of an employee Unique Address address of an employee Salary salary of an employee Gender sex of an employee DOB birth date of an employee

  12. First Name Emp No Mid Initials Emp Name Employee Last Name NID Address Salary Gender DOB Detailed Conceptual Design

  13. Depd Name Gender Dependent DOB Relation Detailed Conceptual Design Dependent Name Sex Birth Date Relationship Depd Name name of a dependent Part of Key Gender sexof a dependent DOB birth date of a dependent Relation relationship of a dependent to an employee

  14. First Name Emp No Mid Initials Location NID Emp Name Last Name Dept Name Address supervise Phone works Dept No Start d Department Employee manage Salary Hours Employees has control works on Gender Proj No DOB Dependent Project Depd Name Gender Proj Name DOB Location Relation

  15. Entity Types • Strong (Regular) Entity • An entity that exists independently of other entity types • Weak Entity • An entity types whose existence depends on some other entity Employee Dependent

  16. Entity Types • Identifying Owner • The entity type on which the weak entity type depends e.g. Employee is the Owner of Dependent • Identifying Relationship • A relationship between a weak entity type and its owner has

  17. Conceptual Design showing weak entities supervise works Department Employee manage has control works on Dependent Project

  18. Sample Entity Definitions • Name: Department Type: Regular Definition: a department of an organisation Identifier: Dept_No • Name: Dependent Type: Weak Definition: a person who is a dependent of an employee and entitle for insurance Identifier: Depd_Name (partial only)

  19. Sample Attribute Definitions • Name: Emp_No Domain: employee identities Definition: unique identifier of an employee Null: No • Name: Emp_Name Components: First_Name, Mid_Initials, Last_Name Definition: a partial identifier of a name Null: No

  20. Sample Relationship Definitions • Name: Works_for Type: binary 1:M Definition: associates each employee with a department Constraint: each employee must be attached to a department Attributes: none

  21. Domain Constraints • A specification of the characteristics of the data values that can be associated with one or more attributes Sample Domain Constraints • Name: Employee identities Data Type: character Length: 5 Allowable Characters: digits

  22. Sample Domain Constraints Cont’d • Name: Last name Data Type: character Max Length: 20 • Name: DOB Data Type: date Format: dd/mm/yyyy dd = day mm = month yyyy = year

  23. Certificate Associative Entity • An entity type that associates the instances of one or more entity types and contains attributes that are peculiar to the relationship between those entity instances

  24. Course Id Course Id Emp No CName CName Date Comp Emp Name Employee Course Course Completes Emp No Date Comp Emp Name Employee Certificate Cert No 30 4 1 many to may relationship 30 4 2 one to many relationships

  25. Associative Entity • All of the relationships for the participating entity types are “many” relationships • The resulting associative entity type has independent meaning to end users, and preferably can be identified with a single-attribute identifier • The associative entity has one or more attributes, in addition to the identifier • The associative entity participates in one or more relationships independent of the entities related in the associated relationships

  26. manages Employee Relationships • Unary Relationship • A relationship between the instances of a single entity type e.g. Person is married to a Person (1:1) Employee manages Employees (1:M) • Binary Relationship • A relationship between the instances of two entity types

  27. Part Shipping Mode Supplies Vendor Unit Cost Warehouse Relationships • Ternary Relationship • A simultaneous relationship among the instances of three entity types S1 P1 W1 Land 10 S1 P1 W2 Sea 15 S1 P2 W1 Air 20 S2 P1 W1 Air 15

  28. Relationships • Ternary Relationship • can be treated as two many to many relationships Shipping Mode Part Supplies Warehouse supply Vendor Unit Cost

  29. given family faculty 1 SCHOOL REG name studno hons m year STUDENT m m n YEARREG 1 labmark YEAR slot ENROL TUTOR 1 exammark YEARTUTOR m n TEACH courseno 1 m 1 name COURSE STAFF subject roomno m 1 equip appraisee appraiser APPRAISAL Entity Relationship Model

  30. Superclasses, Subclasses; Specialisation & Generalisation Relationships • Subclasses and Superclasses • a subclass entity type is a specialised type of superclass entity type • a subclass entity type represents a subset or subgrouping of superclass entity type’s instances • e.g. undergraduates and postgraduates are subclasses of student superclass • Attribute Inheritance • subclasses inherit properties (attributes) of their superclasses

  31. Constraints on Specialisation & Generalisation • Specialisation • the process of defining a set of more specialised entity types of an entity type • Generalisation • the process of defining a generalised entity type from a set of entity types • Predicate/Condition defined • determine the entities that will become members of each subclass by a condition on an attribute value. All member instances of the subclass must satisfy the predicate • e.g. first years and second years are subclasses of undergraduates based on their year attribute. • User defined • no condition for determining subclass membership

  32. Constraints on Specialisation & Generalisation • Disjointness • Overlap • the same entity instance may be a member of more than one subclass of the specialisation • Disjoint • the same entity instance may be a member of only one subclass of the specialisation • Completeness • Total • every entity instance in the superclass must be a member of some subclass in the specialisation • Partial • an entity instance in the superclass need not be a member of any subclass in the specialisation

  33. studno Specialisation & Generalisation Relationships given family name STUDENT d STAFF   1 year tutor m postgraduate undergraduate thesis title

  34. name payroll no STAFF length of service O level    grade ACADEMIC TECHNICAL ADMIN project Superclasses, SubclassesSpecialisation & Generalisation Relationships

  35. name PERSON address O   salary fee EMPLOYEE STUDENT O   d  thesis  RESEARCH TEACHING POST UNDER GRAD GRAD 1-2  O  year = 3  1-2 TUTORS LECTURING FINAL SUPERVISOR YEAR courseno project Superclasses, SubclassesSpecialisation & Generalisation Relationships

  36. Categories and Categorisation • a single superclass/subclass relationship with more than one superclass, where the superclasses represent different entity types (sometimes with different keys) COMPANY PERSON personid compid U  duration of ownership OWNER

  37. studno given family name STUDENT d   year postgraduate undergraduate thesis title Specialisation & Generalisation Option A 1. Create a relation for superclass 2. Create a relation for each subclass such that: {primary_key of superclass} U {attributes of subclass} key for subclass is (primary_key of superclass) Inclusion dependency: p<key>(superclass) p<key>(subclass ) Covering dependency: n (number of subclasses) p<key>(subclass ) = p<key>(superclass) i=1 Disjoint dependency: n (number of subclasses) p<key>(subclass ) =  i=1

  38. studno given family name STUDENT d   year postgraduate undergraduate thesis title Specialisation & Generalisation Option B 1. Create a relation for each subclass such that: {primary_key U {attributes U {attributes of of superclass} of superclass} subclass} key for each relation is (primary_key of superclass) • Works for total and disjoint constraints • Partial: lose any entity that is not in a subclass • Overlapping: redundancy • To recover the superclass can do an OUTER UNION on the subclass relations

  39. studno given family name STUDENT d   year postgraduate undergraduate thesis title Specialisation & Generalisation Option C 1. Create one relation such that: {primary_key U {attributes U {attributes U {type of superclass} of superclass} of all subclasses} attribute} • key for subclass is (primary_key of superclass) • Many ‘not-applicable’ nulls • Does away with joins • Disjoint: one type which indicates which subclass the tuple represents • Overlap: set of types = number of subclasses • Partial: type is null \ represents superclass

  40. 1.STAFF(payrollno,name,lengthofservice) ACADEMIC(payrollno,level) TECHNICAL(payrollno,project) ADMIN(payrollno,grade) 2.ACADEMIC(payrollno,name,lengthofservice, level) TECHNICAL(payrollno,name,lengthofservice, project) ADMIN(payrollno,name,lengthofservice,grade) 3.STAFF(payrollno,name,lengthofservice,level, project,grade,type1,type2,type3) STAFF(payrollno,name,lengthofservice,level, project,grade,type) type = powerset of classes name payroll no STAFF length of service O level    grade ACADEMIC TECHNICAL ADMIN project Specialisation & Generalisation Overlapping

  41. name PERSON address O   salary fee EMPLOYEE STUDENT O   d  thesis  RESEARCH TEACHING POST UNDER GRAD GRAD 1-2  O  year = 3  1-2 TUTORS LECTURING FINAL SUPERVISOR YEAR courseno project Specialisation & Generalisation Relationships

  42. Staff payroll no d   d  Manager Hourly Staff     Salaried Staff   Academic Technical Admin Admin Manager Specialisation Lattice with Shared Subclass • To be a shared subclass the superclasses must have the same key.

  43. Categories and Categorisation • A category is a subclass of the union of two or more superclasses that can have different keys because they can be of different entity types • If defining superclasses have different keys, specify a new surrogate key COMPANY PERSON personid compid U  duration of ownership OWNER( ??? ) PERSON( ??? ) COMPANY( ??? ) OWNER

  44. a)Mapping of EER to Relational model • Create a relation for the super class. with attributes {k,a1..an} • Create a relation Li for each Si • Attributes of Li={k}U{Attributes of Si} • Constraints can be disjoint/overlapping, Total/Partial

  45. b)Mapping of EER to Relational model • Create a relation Li for each Si • Attributes of Li={k a1..an}U{Attributes of Si} • Constraint must be disjoint and total

  46. c)Mapping of EER to Relational model • Create a single relation L • Attributes of L={k a1..an}U{Attributes of S1}U…U{Attributes of Sm}U{t} • ‘t’ is a type attribute that indicates the sub-class to which each tuple belongs. • This may create a lot of null values • Constraint is disjoint (total or partial) • Not very space efficient hence can use a previous method • But no need of ’joins’ to get all attributes

  47. d)Mapping of EER to Relational model • Create a single relation L • Attributes of L={k a1..an}U{Attributes of S1}U…U{Attributes of Sm}U{t1,t2 ,..tm } • ‘t’ is a type attribute that indicates the sub-class to which each tuple belongs. • This may create a lot of null values • Constraint is overlapping (total or partial) • Not very space efficient hence can use a previous method • But no need of ’joins’ to get all attributes

  48. Mapping shared sub-classes • Option ‘a’is usually used • Options ‘c’ & ‘d’ can also be used

  49. Mapping of categories • If the super-classes have different keys we have to define a new key attribute called a ‘surrogate key’. When creating a relation to correspond to the category. • Create a relation to correspond to the category,include any atributes of the category.The primary key is the surrogate key • Each super-class is also mapped into a relation with its own primary key, the surrogate key becomes a foreign key for this.

  50. Eg. For mapping category • Person(PersonID,name,…,OwnerID) • Company(CompID,name,…,OwnerID) • Owner(OwnerID, OwnerType)

More Related