1 / 40

Conceptual / semantic modelling

Conceptual / semantic modelling. Overview. design; conceptual design; ER model concepts; semantic aspects; problems; Enhanced ER (EER) modelling; transforming an ER (EER) model into a relational model. Design.

corentine
Download Presentation

Conceptual / semantic modelling

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. Conceptual / semantic modelling

  2. Overview • design; • conceptual design; ER model • concepts; • semantic aspects; • problems; • Enhanced ER (EER) modelling; • transforming an ER (EER) model into a relational model

  3. Design • you know the mechanics for building a database system, but don’t know how to design it

  4. Design • requirements specification • conceptual (semantic) design • logical design • physical design and implementation • testing and validation • maintenance

  5. Conceptual (semantic) design • constructing a model of the information used in an organisation • independently of any targeted DBMS and of any physical considerations - high level of abstraction • conceptual model - a more structured description (as opposed to a NL description) • try to capture as much meaning as possible • model - constructed within a theory • terminology (ambiguity): model • most popular conceptual data model (theory) • ER (or E/R) model (theory) - Chen (1976) • UML notation

  6. ER model attribute

  7. Concepts of the ER model • semantic, not formal • may be understood differently (by designers); results: different models of the same real life system • however, an ER model is a semi-formal description (see below) • not directly implementable • needs to be translated into an implementable model (e.g. the relational model) • why, then, doing it in the first instance? • symbolic level • formal description based on a set of informally defined concepts • most popular: ER diagram

  8. Entity • entity type • a concept identified as having an independent existence • entity instance • strong/regular entity type • weak entity type • its existence is dependent on some other entity types • examples • think ...

  9. ER diagram - entity (UML) Student Student studNo Book-Loan

  10. ER diagram - entity (before)

  11. Attributes • a property/characteristic of an entity (or relationship) type • instances of the same type have properties in common • attribute domain • should be included in a fully developed data model • attributes • simple / composite • single-valued / multi-valued • derived • candidate key / primary key / composite key (note: no foreign key!)

  12. ER diagram - attributes (UML) Student name {PPK} dOB {PPK} day month year sex /age address no code city telNo[1..*] course /fees

  13. ER diagram - attributes (before)

  14. Relationships • do not mistake with relation (in the relational model) • relationship type • a (meaningful) association between entity types • participants - the entities involved in the relationship • relationship instance • degree • number of participating entities (types) • binary • ternary, quaternary … • recursive

  15. ER diagram - binary relationships (UML) Book Student Borrows Title Author Edition name dOB Department Belongs To name

  16. ER diagram - binary relationships (before)

  17. ER diagram - ternary relationship (UML) Module code Student Teach name dOB Lecturer name

  18. ER diagram - recursive relationships (UML) Representative Student Represents name dOB Represented

  19. ER diagram - more than one relationship between two entities HeadofDepartment Manages Staff Department name dOB name BelongsTo Member

  20. ER diagram - relationships with attributes (UML) IsAffiliatedTo Staff Department name dOB name date position

  21. ER diagram - relationships with attributes (before)

  22. Structural constraints on relationships • cardinality constraints • multiplicity • cardinality • participation • relevant to all possibilities - i.e., not to a certain extension

  23. Structural constraints on relationships (UML) Room Rents 0..1 hall no 0..1 Student Advises 0..* name dOB 1 0..* Tutor name Teaches 1..*

  24. tenant ER diagram - structural constraints (before) rented_house 1 M 1 owns has rents M 1 N landlord house history

  25. Problems with ER models • connection traps • a link between two entities seems to have been represented via another entity, but the actual model does not capture that • fan traps • symbolically: two (1:M) relationships fan out of an entity • it is not always a trap • chasm traps • symbolically: a pathway representing a link contains a relationship with a partial participation • a good understanding of the application is required

  26. ER diagram - fan trap Course Proposes Attends 1 1 code 1..* 1..* Project Student name name dOB

  27. ER diagram - fan trap explanation

  28. ER diagram - solution to a fan trap Student Takes Attends 1..* 1..* name dOB 1 1 Project Course name hall no can you think of another solution?

  29. ER diagram - chasm trap Project Takes Proposes 1..* 0..1 name 0..* 1 Student Course hall no name dOB

  30. ER diagram - solution to a chasm trap Project Takes Proposes 1..* 0..1 name 0..* 1 Student Course Attends 1 1..* hall no name dOB why not always represent all the relationships?

  31. The Enhanced ER model (EER) • additional semantic modelling concepts • for “richer” (more comprehensive) representations • type hierarchies • specialisation / generalisation • subclass / superclass • attribute inheritance • aggregation hierarchies • aggregation • composition

  32. Specialisation / generalisation • equivalent names: • specialisation hierarchy, generalisation hierarchy or IS-A hierarchy • specialisation • identifying classes among the instances of an entity type, by identifying distinguishing properties (attributes); • a top down approach • generalisation • grouping entity types into one single class, by disregarding differentiating properties (attributes) • a bottom up approach • attribute inheritance

  33. Specialisation / generalisation Staff empNo {PK} name salary position SalesStaff Developer Support Manager speciality sales language projects area experience bonus

  34. SalesStaff Developer Support Manager speciality sales language projects area experience bonus Specialisation / generalisation Staff empNo {PK} name salary position PartTime FullTime workLoad jobLevel notice

  35. Constraints • overlapping of the child subclasses • disjoint - {OR} • overlapping - {AND} • participation of the parent superclass • total - {Mandatory} • partial - {Optional}

  36. SalesStaff Developer Support Manager speciality sales language projects area experience bonus Constraints Staff empNo {PK} name salary position {Optional, OR} {Mandatory, OR} PartTime FullTime workLoad jobLevel notice

  37. Specialisation / generalisationmore than one parent Manager SalesStaff experience bonus speciality sales {Optional} SalesManager target child inherits attributes of both parents

  38. Aggregation Course code 0..* 0..* 1..1 IsRegisteredFor Supports 1..* 0..* 1..* Student Module Staff name name dOB code

  39. Composition Account no 1..1 1..1 1..1 1..* 0..* 1..1 Transaction Agreement Overdraft date type amount dateValidUntil amount text date

  40. Summary • conceptual model • ER Model • entity • attribute • relationship • multiplicity • traps • EER model • generalisation/specialisation • constraints • aggregation

More Related