Scribd
Upload
45 views2 pages

Relational Database Schema Design Steps

The document outlines a seven-step algorithm for converting an ER model conceptual schema design into a relational database schema. The steps include: mapping regular and weak entity types to relations, mapping binary 1:1, 1:N, and M:N relationship types using foreign keys or separate relations, mapping multi-valued attributes to separate relations, and mapping n-ary relationships to relations.

Uploaded by

nagarajuvcc123
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
45 views2 pages

Relational Database Schema Design Steps

The document outlines a seven-step algorithm for converting an ER model conceptual schema design into a relational database schema. The steps include: mapping regular and weak entity types to relations, mapping binary 1:1, 1:N, and M:N relationship types using foreign keys or separate relations, mapping multi-valued attributes to separate relations, and mapping n-ary relationships to relations.

Uploaded by

nagarajuvcc123
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Design a relational database schema

Based on a conceptual schema design


Seven-step algorithm to convert the basic
ER model constructs into relations

Step 1: Mapping of regular Entity Types. [ Strong entities has PKs Weak entities has FKs ]
For each regular entity type, create a relation R that includes all the simple attributes of E.
Called Entity Relations.
o Each tuple represents an entity instance.
Step 2: Mapping of Weak entity Types.
For each weak entity type, create a relation R and include all simple attributes of the entity type
as attributes of R.
Include primary key attribute of owner as foreign key attributes of R.

Step 3: Mapping of Binary 1:1 Relationship types.
For each binary 1:1 relationship type
o Identify relations that correspond to entity types participating in R.
Possible approaches.
o Foreign key approach
o Merged relationship approach
o Cross-reference or relationship relation approach.
Step 4: Mapping of Binary 1:N Relationship types.
For each regular binary 1:N relationship type.
o Identify relation that represents participating entity type at N-side of relationship type.
o Include primary key of other entity type as foreign key in S.
o Include simple attributes of 1:N relationship type as attributes of S.
Alternative approach.
o Use the relationship relation (cross-reference) option as in the third option for binary
1:1 relationships.
Step 5: Mapping of Binary M:N Relationship Types.
For each binary M:N relationship type
o Create a new relation S
o Include primary key of participating entity types as foreign key attributes in S.
o Include any simple attributes of M:N relationship type.
Step 6: Mapping of Multi valued Attributes.
For each multivalued attribute.
o Create a new relation
o Primary key of R is the combination of A and K
o If the multivalued attribute is composite, include its simple components.
Step 7: Mapping of N-ary Relationship Types.
For each n-ary relationship type R.
o Create a new relation S to represent R.
o Include primary keys of participating entity types as foreign keys.
o Include any simple attributes as attributes.

You might also like