conceptual-to-relational-models.md (2436B)
1 +++ 2 title = "Conceptual to relational models" 3 +++ 4 5 # Conceptual to relational models 6 7 entity sets and relationship sets are represented as tables 8 9 - name of table is name of entity/relationship set 10 - columns (unique names) usually correspond to attributes 11 12 a strong entity set becomes a table with columns for the attributes. 13 14 a weak entity set becomes table with: 15 16 - columns for the attributes 17 - columns for the primary keys of the identifying entity 18 19 many-to-many relationship set becomes table with: 20 21 - columns for attributes of relationship 22 - columns for primary keys of participating entity sets 23 24 many-to-(zero or one) relations can be represented by adding extra 25 attribute/column to the many-side with primary key of one-side 26 27 for one-to-one relationship sets, either side can have the key of the 28 other. 29 30 if participation is partial (0..1), replacing table by an attribute will 31 lead to null values for those that don\'t participate. 32 33 if participation is total, foreign key should be `NOT NULL`. 34 35 tables for relationship sets linking weak entity sets to identifying 36 entity set can always be eliminated.o 37 38 when translating sets to tables: 39 40 - every table should have a primary key 41 - declared foreign key references for each relationship 42 - declared whether foreign keys are nullable 43 - declared unique if appropriate 44 45 composite attributes are flattened out by creating separate column for 46 each component attribute. 47 48 multi-valued attribute A of entity set E is represented by separate 49 table with: 50 51 - columns for primary key of E 52 - column for attribute value 53 - each single value of multi-valued attributes gets its own row 54 55 Is-a to relational model: 56 57 - Method 1: hierarchy of tables 58 - table for higher-level entity set 59 - table for each lower-level entity set (includes primary key of 60 higher-level entity set & local attributes) 61 - Method 2: many tables 62 - form table for each entity set with all local & inherited 63 attributes 64 - if specialisation is total, we don\'t need table for generalised 65 entity - table for generalised entity can be defined as view 66 containing union of specialisation tables 67 - Method 3: one table with null values 68 - form a single table with all local and specialized attributes 69 70 recursive association: translates to foreign key that refers to the same 71 table 72 73 - requires separate table with two foreign keys to parent table