ER Diagram to Relational Schema
A database whose entities and relations have been modeled as an ER diagram can be represented as a set of relational schemas, one for each entity and relation.
A strong entity set reduces to a schema with the same attributes
For one-to-one relationship sets, either side can be chosen to act as the “many” side, i.e. the extra attribute can be added to either of the tables corresponding to the two entity sets
Composite attributes are flattened out by creating a separate attribute for each component attribute
A multivalued attribute M of an entity E is represented by a separate schema EM
Schema EM has attributes corresponding to the primary key of E and an attribute corresponding to multivalued attribute M
Ex. Multivalued attribute phone_number of instructor is represented by a schema:
inst_phone= ( ID, phone_number)
Each value of the multivalued attribute maps to a separate tuple of the relation on schema EM. For example, an instructor entity with primary key 22222 and phone numbers 456-7890 and 123-4567 maps to two tuples: (22222, 456-7890) and (22222, 123-4567)
Representing specializations as schemas:
Method 1: form a schema for the higher-level entity, form a schema for each lower-level entity set, then include primary key of higher-level entity set and local attributes. The drawback is that we would have to access two relations to get the required information
Method 2: form a schema for each entity set with all local and inherited attributes. The drawback is redundancy
Last updated