Database Management
Systems
DATABASE DESIGN USING E-R MODEL
DATABASE DESIGN USING E-R MODEL
▪ E-R model represents things as entities.
▪ The connection among different entities are represented by
relationships.
▪ These entities and relationships can be transformed into
relational model.
▪ These models can be used to design the database using
relational model.
CONVERTING ENTITIES INTO RELATION
▪ To convert entities into relation:
▪ the name of entity becomes the name of relation and
▪ attributes of entity becomes the fields of relation.
▪ After this conversion, the relation is examined according to the
normalization criteria.
▪ The relation can be modified if it does not satisfy the rules of
normalization.
CONVERTING ENTITIES INTO RELATION
CONVERTING COMPOSITE ATTRIBUTES
▪ If an entity contains composite attributes, each part of the attribute is represented by a separate
field in the relation.
CONVERTING MULTI-VALUED ATTRIBUTES
▪ If an entity contains multivalued attributes, the attribute is represented in a separate relation.
KEYS
▪ A Key is an attribute or set of attributes that uniquely identifies a tuple in a relation.
▪ The keys are defined in tables to access or sequence the stored data quickly and
smoothly.
▪ They are also used to create relationships between different tables.
SUPER KEY
▪ A super key is an attribute or set of attributes in a relation that identifies a tuple
uniquely within a relation.
▪ A super key is the most general type of key.
▪ Example: Roll_No is the super key.
CANDIDATE KEY
▪ A candidate key is a super key that contains no extra attribute.
▪ It consists of minimum possible attributes.
▪ A super key like Roll_No and Name contain an extra field Name.
PRIMARY KEY
▪ A primary key is a candidate key that is selected by the database designer to
identify tuples uniquely in a relation.
▪ A relation may contain many candidate keys.
▪ When the designer selects one of them to identify a tuple in the relation, it
becomes a primary key.
ALTERNATE KEY
▪ The candidate keys that are not selected by the database designer as primary key.
COMPOSITE KEY
▪ The primary key that consists of two or more attributes is known as composite key.
FOREIGN KEY
▪ An attribute or set of attributes in a relation (Child Table) whose values match a primary key n
another relation (Parent Table).
CONVERTING WEAK ENTITIES
▪ Weak entity does not exist independently. It depends on existence of
another entity (Identifying owner). To convert identifying and weak
entity:
▪ A relation is created for identifying owner.
▪ A separate relation is created for each weak entity.
▪ The weak entity relation is connected with the identifying relation.
▪ The primary key of identifying relation is used as foreign key in the weak entity
relation.
CONVERTING UNARY RELATIONSHIPS
▪ Unary relationship exists between the instances of same entity types.
▪ Also known as recursive relationship.
▪ Unary one-to-many relationship of ER model is represented in relations
by performing the following two steps:
1. Create a relation to represent the entity type.
2. Add another field as foreign key in the same relation that references
the primary key of the relation. The foreign key must have the same
domain as the primary key.
CONVERTING BINARY RELATIONSHIPS
▪ The process of representing relationship depends upon two things:
▪ 1. Degree of relationship
▪ 2. Cardinality of relationship.
CONVERTING BINARY RELATIONSHIPS
▪ Binary one to many
▪ One to many relationship of ER model is represented in relations by performing
the following two steps:
1. Create a relation for each of the two entity types participating in the relationship.
2. Include the primary key of the entity on one side of the relationship as a foreign
key in the relation that is on the many side of the relationship.
CONVERTING BINARY RELATIONSHIPS
▪ Binary many-to-many
▪ Many to many relationship of ER model is represented in relations
by performing the following two steps:
1. Create two relations A and B for each of the two entity types
participating in the relationship.
2. Create another relation C that contains the primary keys of
relations A and B as foreign key. These attributes become the
primary key of the relation C.
CONVERTING BINARY RELATIONSHIPS
▪ Binary one-to-one
▪ One-to-one relationship of ER model is represented in relations
by performing the following two steps
1. Create a relation for each of the two entity types participating
in the relationship.
2. Include the primary key of the first relation as a foreign key in
the second relation.
CONVERTING TERNARY RELATIONSHIPS
▪ Ternary relationships exist between the instances of three entity types.
▪ Ternary relationship of ER Model is represented in relations by performing the
following two steps.
1. Create a relation for each of the two entity types participating in the
relationship.
2. Create an associative relation to represent the link between three entities.
3. The primary key of associative relation consists of primary keys of three
entities in the relationship.
4. The attributes of associative entity type become attributes of the new relation.
SUPERTYPE/SUBTYPE RELATIONSHIPS
▪ Supertype/ subtype relationship of ER model is represented in
relations by performing the following steps:
1. Create a separate relation for supertype and each of the subtype.
2. The relation for supertype consists of the attributes which are
common in all members. Assign a primary key to the supertype
relation.
3. The relation for each subtype consists of the attributes which are
unique to that particular subtype.
4. Assign one or more attributes of the supertype to work as the
subtype discriminator.
