Perancangan Basis Data

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

2. 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

3. MAPPING

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

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

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

7. 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

8. 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-to-many • Suatu set entitas lemah memiliki kunci yang biasa disebut diskriminator. Fungsinya ? • Kunci dari set entitas = kunci dari entitas kuat yang berhubungan + diskriminator

9. 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-to-many 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.

10. 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.

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

12. A Chain of Weak Entity Sets city state Located in Located in • 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. street Example illustrating that a weak entity set might itself participate as owner in an identifying relationship with another weak entity set.

13. A Weak Entity Set with Multiple Owner Entity Sets title movie review 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

14. 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)

15. 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 loan-number common to payment and loan.

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

17. Aggregation • 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 project works employee N using N tools

18. 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: N Interview M Company Job-Applicant Results-In Job-Offer RESULTS_IN P.K(JOB_OFFER) P.K(INTERVIEW)

19. EER(Enhanced Entity Relationsip)

20. Sub-Class Shared Subclass Super-ClassGeneralizationSpecializationSpecialization HierarchySpecialization LatticeAttribute Inheritance

21. Summary of Symbols Used in E-R Notation

22. Summary of Symbols (Cont.)

23. 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.

24. 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)

25. 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…? !

26. Pengertian Superclass • Super-Kelas (Superclass) • Entitas yang merupakan induk dari subclass-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.

27. 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.

28. 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

29. 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

30. 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.

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

32. 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…!

33. Specialization Example

34. account account balance ISA checking savings overdraft amount interest rates Subclass/Superclass Relationships • 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

35. 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-6a - note the double line convention) • Partial Specialization Rule: No (Fig. 4-6b - single line convention)

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

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

38. 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

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

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

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

42. 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.

43. Disjoint,Overlap? Partial,Total ?

44. FIGURE 4.4EER diagram notation for an attribute-defined specialization on JobType. d

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

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

47. FIGURE 4.5EER diagram notation for an overlapping (nondisjoint) specialization. o

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

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

50. 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.