1 / 11

Seminar: Introduction to relational databases

Seminar: Introduction to relational databases. Introduction to the database field: Transforming ER models to relational schemas. Transformation. Design of Relational Tables. The conceptual model must be transformed to an implementation model, e.g. relational schemas .

abner
Download Presentation

Seminar: Introduction to relational databases

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. Seminar:Introduction to relational databases Introduction to the database field: Transforming ER models torelational schemas Transformation FEN 2012-09-01

  2. Design of Relational Tables • The conceptual model must be transformed to an implementation model, • e.g. relational schemas. • This can be done using a seven steps algorithm • Does not always yield an optimal design, but provides a good starting point for the final design of tables Transformation FEN 2012-09-01

  3. Design of Relational Tables Step 1: For each regular entity create a table: • For composite attributes only the components are included. • Multi-value attributes are not included (they are considered in step 6). • Choose a primary key. FEN 2012-09-01

  4. Design of Relational Tables Step 2: For each weak entity create atable • All attributes from the weak entity are included. • The primary key from the owner is included as foreign key. • The primary key is composed by the owner’s primary key and the partial key. FEN 2012-09-01

  5. Design of Relational Tables Step 3: For each (binary) 1:1-relationinclude the primary key of one participant as foreign key in the other • Any attributes on the relation are included with the key. • If possible, include on a side with total participation. FEN 2012-09-01

  6. Design of Relational Tables Step 4: For each (binary) 1:n relationinclude primary key of 1-side as foreign key on n-side • Any attributes on the relation are included with the key on the n-side. FEN 2012-09-01

  7. Design of Relational Tables Step 5: For each (binary) n:m relation create table with the primary keys of participatingentities as foreign keys • Any attribute on the relation is included in the new table. • Primary key is composed of the foreign keys. • This may also be applied to binary 1:1- and 1:n relations – in particularly if there are relatively few instants of the relation. FEN 2012-09-01

  8. Design of Relational Tables Step 6: For each multi value attribute create a table with primary key of the entity as foreign key and the multi value attribute • The primary key of the new table is composed of the foreign key and the multi value attribute. FEN 2012-09-01

  9. Design of Relational Tables Step 7: For each n-ary (n>2) relation create a table with the primary keys of all participating entity as foreign keys • Any attribute on the relation is included. • The primary key is composed of the included foreign keys. FEN 2012-09-01

  10. Design of Relational Tables Result: FEN 2012-09-01

  11. Example/Exercise (Elmasri) • Use the seven steps algorithm to design tables for this database. FEN 2012-09-01

More Related