ER Enhanced Entity Relationship Perancangan Basis Data Education

ER & Enhanced Entity Relationship Perancangan Basis Data Education Fair Use material Re-Arrange by : Feri Sulianta

Learning Objectives • MAPPING • ER MODEL – Weak entity sets – Aggregation • EER (Enhanced /Extended Entity Relationship) – Superclass/subclass relationships • Shared Subclass – Generalization – Specialization • Specialization Hierarchy • Specialization Lattice – Attribute Inheritance – Etc

MAPPING

FIGURE 1 Ternary relationship types. (a) The SUPPLY relationship.

FIGURE 2 Mapping the n-ary relationship type SUPPLY from Figure 1

Summary of Mapping constructs and constraints Table 1. 1 Correspondence between ER and Relational Models ER Model Entity type 1: 1 or 1: N relationship type M: N relationship type n-ary relationship type Simple attribute Composite attribute Multivalued attribute Value set Key attribute Relational Model “Entity” relation Foreign key (or “relationship” relation) “Relationship” relation and two foreign keys “Relationship” relation and n foreign keys Attribute Set of simple component attributes Relation and foreign key Domain Primary (or secondary) key

Weak Entity Sets • Entity sets that do not have sufficient attributes to form a key are called weak entity sets. • A weak entity set existentially depend upon (one or more) strong entity sets via a one-to-many relationship from whom they derive their key • A weak entity set may have a discriminator (or a partial key) that distinguish between weak entities related to the same strong entity • key of weak entity set = Key of owner entity set(s) + discriminator

Weak Entity Sets • Adalah set entitas yang tidak memiliki atribut kunci (key attributes)yang cukup. • Entias lemah ini bergantung kepada satu atau lebih entitas kuat menggunakan hubungan one-tomany • Suatu set entitas lemah memiliki kunci yang biasa disebut diskriminator. Fungsinya ? • Kunci dari set entitas = kunci dari entitas kuat yang berhubungan + diskriminator

Weak Entity Sets • The existence of a weak entity set depends on the existence of an identifying entity set. – it must relate to the identifying entity set via a total, one-tomany relationship set from the identifying to the weak entity set. – Identifying relationship depicted using a double diamond. • The discriminator (or partial key) of a weak entity set is the set of attributes that distinguishes among all the entities of a weak entity set. • The primary key of a weak entity set is formed by the primary key of the strong entity set on which the weak entity set is existence dependent, plus the weak entity set’s discriminator.

Weak Entity Sets • Keberadaan set entitas lemah tergantung dari set entitas kuatnya. – Gambar hubungannya dapat diidentifikasi dengan dobel diamond. • Diskriminator dari set entitas lemah adalah kumpulan atribut yang membedakan entitas yang ada dalam entitas lemah. • Primary key dari entitas lebah dibentuk dari primary key set entitas kuat ditambahkan dengan diskriminator.

Weak Entity Sets (cont. ) cust name street balance acct number Cust no city customer custacct opening date • Transaction is a weak entity set related to accounts via log relationship. • Trans# distinguish different transactions on same account log transaction Trans#

A Chain of Weak Entity Sets city Located in street Located in state • Names of state are unique and form the key. • Names of city are unique only within a state (e. g. , 24 Springfield’s within the 50 states). • Names of streets are unique within a city. Multiple cities could have streets with the same name. Example illustrating that a weak entity set might itself participate as owner in an identifying relationship with another weak entity set.

A Weak Entity Set with Multiple Owner Entity Sets title movie reviewer rating name • Reviewers review movie and assign a rating -- thumb up/thumbs down. • Review is a weak entity set whose owner sets correspond to both the movie and the reviewer entity sets. • Key for the review entity set = key of movie + key of reviewer

Weak Entity Sets (Cont. ) • Double rectangles represent weak entity sets. • Discriminator of a weak entity set underlined with a dashed line. • payment-number – discriminator of the payment entity set • Primary key for payment – (loan-number, payment-number)

Weak Entity Sets (Cont. ) • Note: the primary key of the identifying entity set is not explicitly stored with the weak entity set, since it is implicit in the identifying relationship. • If loan-number were explicitly stored, payment could be made a strong entity, but then the relationship between payment and loan would be duplicated by an implicit relationship defined by the attribute loannumber common to payment and loan.

Limitations of ER Model We wish to represent that an employee works on a specific project possibly using multiple tools employee incorrect since it requires each project to use tools project works_using tools work project employee relationships among relationships not permitted in ER! using tools

Aggregation employee works project N using N tools • Treat the relationship set work and the entity sets employee and projects as a higher level entity set-- an aggregate entity set • Permit relationships between aggregate entity sets and other entity sets

ER to Relational • Aggregation: Relationship will be mapped to a table, ; extended with p. k. of participating entity set(s) + relationship + its own attributes ( if exists) • Example: Company RESULTS_IN M N Interview Job-Applicant Results-In Job-Offer P. K(JOB_OFFER) P. K(INTERVIEW)

EER (Enhanced Entity Relationsip)

Sub-Class Shared Subclass Super-Class Generalization Specialization Hierarchy Specialization Lattice Attribute Inheritance

Summary of Symbols Used in E -R Notation

Summary of Symbols (Cont. )

Subclass • Entity type describes: – Type of entity – The entity set • Example: ‘EMPLOYEE’ • Employee can be sub-grouped into: – – Secretary Engineer Technician Manager • These are called the subclass of EMPLOYEE entity type.

Pengertian Subclass (EER) Sub-Kelas (Subclass) • Subset dari suatu entitas yang dikelompokkan dalam pengertian tertentu yang perlu disajikan secara eksplisit. • Contoh : Entitas PEGAWAI mempunyai beberapa subclass seperti SEKRETARIS, TEKNISI, AHLI (Gbr. 1 Spesialisasi tipe pekerjaan entity PEGAWAI)

Super-class • EMPLOYEE entity type is the super class of engineer, secretary & technician class • Subclass represent the same mini-world entity of the superclass, but in a distinct specific role. • These are also called IS-A relationships (SECRETARY IS-A EMPLOYEE, TECHNICIAN IS-A EMPLOYEE, …). • Entity in a subclass must be a member of a superclass, but not vice-versa! Example…? !

Pengertian Superclass Super-Kelas (Superclass) • Entitas yang merupakan induk dari subclassnya. • Contoh : Subclass SEKRETARIS, TEKNISI, SALES mempunyai superclass PEGAWAI (Gbr. 1) Spesialisasi (Spesialiazation) • Proses pemecahan entitas menjadi subclass-subclass beserta atribut-atributnya. • Terdapat beberapa jenis spesialisasi seperti Disjoint Total, Disjoint Partial, Overlapping Total dan Overlapping Partial. • Contoh : Spesialisasi dari PEGAWAI berdasarkan tipe pekerjaan.

EER • EER = ER + Generalisasi + Spesialisasi + Kategorisasi • Superclass : = set entitas yang umum dan memiliki relasi satu atau lebih subclass • Subclass : = adalah sub-sub kelompok dari entitas yang berguna bagi organisasi. Subclass merupakan spesialisasi dari entitas superclass. Subclass mewarisi atribut dan relasi yang berkaitan dengan superclassnya.

Type inheritance • An entity that is member of a subclass inherits all attributes of the entity as a member of the superclass • It also inherits all relationships

Specialization • Is a process of defining a set of subclasses of an entity type (the superclass) • Secretary, engineers, & technician are specialization of EMPLOYEE based on job type attribute

Spesialisasi (Spesialiazation) • Proses pemecahan entitas menjadi subclass-subclass beserta atributnya. • Terdapat beberapa jenis spesialisasi seperti Disjoint Total, Disjoint Partial, Overlapping Total dan Overlapping Partial. • Contoh : Spesialisasi dari PEGAWAI berdasarkan tipe pekerjaan.

Cont’d • May have several specialization based on different characteristics • Example, EMPLOYEE can be subclass-ed into Salaried_Employee and Hourly_Employee

Benefit of Specialization • Define a set of subclasses of an entity type • Establish additional specific attributes with each subclass • Establish additional specific relationship types between each subclass and other entity types or other subclasses • Refer to the EER diagram…!

Specialization Example

Subclass/Superclass Relationships account balance ISA savings interest rates • • checking overdraft amount savings and checking are subclasses of the account entity set account is a superclass of savings and checking entity sets An entity in a subclass has to belong to superclass as well -- that is, every savings account is also an account. Similarly every checking account is also an account Attribute Inheritance: subclasses inherit all the attributes of the superclass. Similarly, subclasses inherit all relationships in which the superclass participates

Constraints in Supertype/ Subtype Relationships • Completeness Constraints: Whether an instance of a supertype must also be a member of at least one subtype. – Total Specialization Rule: Yes (Fig. 4 -6 a note the double line convention) – Partial Specialization Rule: No (Fig. 4 -6 b single line convention)

Figure 4 -6 a – Examples of completeness constraints Total specialization rule A patient must be either an outpatient or a resident patient (at least one)

Figure 4 -6 b – Partial specialization rule A vehicle could be a car, a truck, or neither

Constraints in Supertype/ Disjointness constraint • Disjointness Constraints: Whether an instance of a supertype may simultaneously be a member of two (or more) subtypes. – Disjoint Rule: An instance of the supertype can be only ONE of the subtypes – Overlap Rule: An instance of the supertype could be more than one of the subtypes

Figure 4 -7 – Examples of disjointness constraints (a) Disjoint rule A patient can either be outpatient or resident, but not both

Figure 4 -7(b) Overlap rule A part may be both purchased and manufactured

UML Class Diagram Notation (Cont. ) *Note reversal of position in cardinality constraint depiction

Notations from the EER diagram • • Subset symbol Specific attributes, or local attributes Specific relationships Superclass/subclass EMPLOYEE/Secretary resembles 1: 1 relationship at the instance level, of one entity.

Disjoint, Overlap? Partial, Total ?

FIGURE 4. 4 EER diagram notation for an attribute-defined specialization on Job. Type. d

FIGURE 7. 4 Options for mapping specialization or generalization. (a) Mapping the EER schema in Figure 4. 4 using option 8 A.

FIGURE 7. 4 Options for mapping specialization or generalization. (c) Mapping the EER schema in Figure 4. 4 using option 8 C.

FIGURE 4. 5 EER diagram notation for an overlapping (nondisjoint) specialization. o

FIGURE 7. 4 Options for mapping specialization or generalization. (d) Mapping Figure 4. 5 using option 8 D with Boolean type fields Mflag and Pflag.

Generalization • Identify common features (attributes), and generalize into a superclass • Example: truck & car can be generalized into VEHICLE • Inverse of the specialization process

Generalisasi • Generalisasi adalah proses bottom-up dalam mendefinisikan dan melakukan generalisasi terhadap beberapa subclass menjadi satu entitas superclass. • Generalisasi merupakan proses untuk minimalisasi perbedaan antar entitas dengan mendefinisikan atribut yang sama.

FIGURE 4. 3 Generalization. (b) Generalizing CAR and TRUCK into the superclass VEHICLE.

FIGURE 7. 4 Options for mapping specialization or generalization. (b) Mapping the EER schema in Figure 4. 3 b using option 8 B.

Predicate defined subclasses • Also called condition-defined subclasses • Conditioned by a defining predicate at the superclass • Example, Job. Type = ‘Secretary’ • Place attribute name on the arc Rules & Pemahaman !

Rules & Pemahaman!

User defined subclass • Membership is specified individually for each entity by the user • Not by any condition that may be evaluated automatically • Example: Manager subclass Rules & Pemahaman !

Disjointness constraints • Subclasses of a specialization must be disjoint • An entity can only be at most one of the subclass • Look at the EER diagram • Use (d) Rules & Pemahaman !

Overlap • The same entity may be a member of more than one subclass of the specialization • Use the (o) • Example, a person can be: – A student – A faculty member – An alumni Rules & Pemahaman !

Rules & Pemahaman !

Completeness constraints • Total specialization: – Every entity in the superclass must be a member of some subclass – Example, the Salaried_Employee and Hourly_Employee – Shown using double line Rules & Pemahaman !

• Partial specialization: – Allows an entity not to belong to any subclass – Example: • Manager • Job type – Use single line Rules & Pemahaman !

Rules • Deleting entity from a superclass deletes it also from the subclasses • Inserting in a superclass, when attribute defined is filled must insert to the proper subclass as well • Inserting in superclass of total specialization must insert into at least one subclass Rules & Pemahaman !

Hierarchy & Lattice • Hierarchy: a subclass only participates in one class/subclass relationship • Example: Vehicle with Car and Trucks • Lattice: a subclass can participate in more than one class/subclass relationship • Example: an Engineering Manager, must be an Engineer, and also a Manager! • The concept of multiple inheritance

Shared Sub-class

Alternative Diagrammatic Notations Symbols for entity type / class, attribute and relationship Notations for displaying specialization / generalization Displaying attributes Various (min, max) notations Displaying cardinality ratios

Exercise 1. a property or description of an entity 2. a set of possible values for an attribute. 3. an object in the real world that is distinguishable from other objects such as the green dragon toy. 4. an association among two or more entities. 5. a collection of similar entities such as all of the toys in the toy department. 6. a collection of similar relationships 7. a key constraint that indicates that one entity can be associated with many of another entity. 8. an entity that cannot be identi. ed uniquely without considering some primary key attributes of another identifying owner entity. 9. is the least number of instances of an entity that can participate in an instance of a relationship 1. Entity set 2. One-to-many relationship 3. Relationship set 4. Attribute 5. Weak entity 6. Many-to-many relationship 7. Domain 8. Instance 9. Relationship

Exercise 1. a property or description of an entity 2. a set of possible values for an attribute. 3. an object in the real world that is distinguishable from other objects such as the green dragon toy. 4. an association among two or more entities. 5. a collection of similar entities such as all of the toys in the toy department. 6. a collection of similar relationships 7. a key constraint that indicates that one entity can be associated with many of another entity. 8. an entity that cannot be identi. ed uniquely without considering some primary key attributes of another identifying owner entity. 9. is the least number of instances of an entity that can participate in an instance of a relationship 1. Domain 2. Instance 3. Relationship 4. Entity set 5. One-to-many relationship 6. Relationship set 7. Attribute 8. Weak entity 9. Many-to-many relationship

Exercise (cont. )

Exercise (cont. ) • Develop ER-Diagram based on following facts ! – Manufacturers have a name, which we may assume is unique, an address, and a phone number – Product have a model number and a type (e. g. , television set). Each product is made by one manufacturer, and different manufacturers may have different products with the same model number. However, you may assume that no manufacturer would have two products with the same model number. – Customers are identified by their unique social security number. They have email addresses, and physical addresses. Several customers may live at the same (physical) address, but we assume that no two customers have the same email address. – An order has a unique order number, and a date. An order is placed by one customer. For each order, there are one or more products ordered, and there is a quantity for each product on the order.

More Complicated ?