Transcript Transforming the conceptual Model

Omsætning af en model til en
RDB
Jesper Tørresø
DAB1 E08
08. Oktober 2008
”I en nøddeskal..”
Koncept til SQL
eller rettere DDL
Logical Schema
• A Schema/Diagram is a data model that is intended to
be used with a database system
– External schemas are defined for the users of a database
– Logical schema defines the representation as a collection
of tables that are stored in a database server
– Internal schema defines the representation used by the
database server to store the tables in memory or files
External schema 1
Datab ase
tab les
External schema 2
External schema 3
External level
Logical to external mappings
Logical schema
Logical level
Internal to logical mapping
disk
Internal schema
Internal level
En naturlig ”bundethed”
• En ERD eller UML model kan via regler
omsættes til en normaliseringsbar
relationel database.
• Oplagt at implementere i IT-værktøjer
• ”Det bedste argument for at udføre en
konceptuel modellering!”
Her er 10 regler..
1. Entity – directly to a SQL table
2. Many-to-many binary relationship – directly to a SQL table, taking the 2 primary
keys in the 2 entities associated with this relationship as foreign keys in the new table
3. One-to-many binary relationship – primary key on “one” side entity copied as a
foreign key in the “many” side entity’s table
4. Recursive binary relationship – same rules as other binary relationships
5. Ternary relationship – directly to a SQL table, taking the 3 primary keys of the 3
entities associated with this relationship as foreign keys in the new table (+ other stuff)
6. Attribute of an entity – directly to be an attribute of the table transformed from this
entity
7. Generalization super-class (super-type) entity – directly to a SQL table
8. Generalization subclass (subtype) entity – directly to a SQL table, but with the
primary key of its super-class (super-type) propagated down as a foreign key into its
table (Her er dog flere muligheder se Scott Amblers noter under Quick Links)
9. Mandatory constraint (1 lower bound) on the “one” side of a one-to-many
relationship – the foreign key in the “many” side table associated with the primary
key in the “one” side table should be set as “not null” (when the lower bound is 0,
nulls are allowed as the default in SQL)
• Der mangler en hvilken?
En til En
•
•
•
•
Hvilken entitet er subjekt. Objektet der har..
Targets opmærksomhed!
Subjekt får Targets PK som FK (Regel 10)
Men den anden vej rundt kan også gælde,
men så skal man passe på (Regel 9 fra før)
• Tvungen versus optionel deltagelse
kræver lidt opmærksomhed ved 1:1
En til En Regel 10
En til En Regel 10
Regel 7 og 8
Regel 5 2 FD’er
Øvrige regler
• Følge [TEROY] kap 5. der viser både ERD
og UML diagrammers omsætning.
• Eller brug DDS-LITE som guide!