index.md (4838B)
1 +++ 2 title = "Database design: Conceptual models" 3 +++ 4 5 # Database design: Conceptual models 6 7 Three phases: 8 9 - conceptual: 10 - what do we store 11 - how are the elements related to each other 12 - what are the constraints 13 - models: Entity/Relationship, UML 14 - logical: 15 - transform conceptual schema into the schema supported by the 16 database 17 - models: relational model 18 - physical: 19 - design indices, table distribution, buffer sizes, etc. 20 - try to maximise performance of the final system 21 22 ## Conceptual models: Entity-Relationship & UML 23 24  25 26 static, models the structure and not operations. no methods or 27 functions. 28 29 - rectangles represent entity sets 30 - ellipses represent attributes 31 - double line - multivalued attributes 32 - dashed line - derived attributes 33 - diamonds are relationship sets 34 - lines link attributes and relationship sets to entity sets 35 - underline indicates primary key attributes 36 37 ### Entity sets 38 39 entity is an abstract object (person, company, event..) 40 41 entitties have attributes (names, addresses, \...) 42 43 entity set is is a collection of similar entities (those sharing the 44 same attributes). 45 46 represented by a set of attributes - descriptive properties possessed by 47 all entities. the domain is set of permitted values for each attribute. 48 49 attribute types: 50 51 - simple and composite (e.g. street is composed of street name and 52 number) 53 - number of values: 54 - single-valued: e.g. age of a person 55 - multivalued: e.g. person can have multiple phone numbers 56 - derived attributes: 57 - can be computed from other attributes 58 - e.g. age computed from date of birth 59 60 #### Weak entity sets 61 62 a weak entity set doesn\'t have a primary key. its existence depends on 63 existence of an identifying entity set. 64 65 there has to be a total, one-to-may relationship set from identifying to 66 weak entity set, shown by double diamond. 67 68 discriminator: a partial key, distinguishing weak entity only in 69 combination with identifying entity. denoted by dashed underline. 70 71 primary key of weak entity set is combination of discriminator and 72 primary key of identifying entity set. 73 74 ### Relationship sets 75 76 a relationship is association among several entities (a tuple (e₁, e₂, 77 \...) of entities) 78 79 a relationship set is a set of relationship of same kind. i.e. a set of 80 tuples (e₁, e₂, \...) where e₁ ∈ E₁, e₂ ∈ E₂, etc. Can have attributes. 81 82 example in UML with a relationship set *plays-in* and role indicators: 83 84  85 86 cardinality limits express number of entities to which another entity 87 can be associated via relationship set: 88 89 - 0\...1 means zero or one 90 - 1\...1 means precisely one 91 - 0\...\* means any number 92 - 1\...\* means at least one 93 94 if cardinalities are not given, the default is many-to-many. 95 96 total participation: every entity in the entity set participates in at 97 least one relationship in the relationship set 98 99 partial participation: entities may not participate in any relationship 100 in the set 101 102 degree of a relationship set is number of entity sets participating in 103 the relationship. 104 105 ### Is-a (inheritance) 106 107 lower-level entity sets are subgroups of the higher-level entity sets 108 (e.g. an employee \'is a\' person) 109 110  111 112 lower-level entity sets inherit all attributes and relationships of the 113 higher-level entity sets 114 115 you can go top-down and find subgroups (specialisation) or bottom-up and 116 combine entity sets (generalisation) 117 118 constraints: 119 120 - membership: value-based, assigns entity to subclass based on 121 attribute values 122 - disjointness: an entity can belong to at most one subclass (default 123 is overlapping, where it can belong to multiple) 124 - completeness: total specialisation, each superclass entity must 125 belong to a subclass 126 127 ### Aggregation 128 129 treat relationship set as an abstract entity. allows relations between 130 relations. 131 132 ### Summary 133 134   135 136 ## UML vs ER 137 138 attributes are shown within box of entity set, not as separate ellipses. 139 140 binary relationships are a line connecting entity sets, with name of 141 relationship next to line. 142 143 if relationship set has attributes, name of relationship set is written 144 in box along with attributes of relation; box is connected with dashed 145 line to the relation line. 146 147 non-binary relationship sets are drawn with a diamond. 148 149 important differences: 150 151 - keys: 152 - ER supports keys with underlining 153 - UML has no standard for keys 154 - aggregation: 155 - ER: treating relationship set as entity 156 - UML: part-whole relation (non-exclusive composition) 157 - weak entities: 158 - ER: entities without own key 159 - UML: composition is similar, but says nothing about keys