ER Model to Relational Model Mapping - Database Management System Video Lecture - Computer Science Engineering (CSE)

Oh
I know to sahaja yoga why they does mean
all of us are officially
[Music]
[Music]
entity relationship model are what is
called as the ER model and the second
was the relational data model we also
saw typical database design crosses and
placed these models into appropriate
positions in the process the entity
relationship model or the ER model is
essentially meant for human
comprehension
it basically is meant for creating a
conceptual database or conceptual schema
or what is termed as a logical schema of
the database system and the relational
data model is used for the physical
schema or something that is implemented
on now in the DBMS so and both the DBMS
independent models that is no matter
which company's database that you are
going to use you can still use the same
year model for representing your data
and even the same relational model for
representing the schema that goes onto
your DBMS now we in this session we are
going to address one important term
issue now our this we are going to ask
the question are these two different
data models ER and relational model
completely independent of each other or
are they the same or is it is there some
way I can map between the ER schema and
the relational schema without having to
break my head too much essentially that
means to say or to put it to take it to
its logical extreme can I design some
tools or can I design some kinds of
software that takes a near diagram of a
given system and generates appropriate
relational schema for the system and we
have also seen in the session on
functional dependencies we have seen how
we can optimize a given relation
schema up to certain extent using some
kinds of automated techniques we have
seen how to take a relation to be CNF or
third normal format or the fourth normal
form and so on so suppose we want to
build a tool to automate this process we
need to be able to first map between an
ear database schema and a relational
schema and and then use techniques from
functional dependencies to optimize this
relational schema so that we can build a
database application around it so what
we are going to study today form the
underpinnings of what are called as
lifecycle tools or database lifecycle
tools there are several different
lifecycle tools which provide support
for the entire lifecycle of a database
system starting from the
conceptualization of the problem to the
actual implementation of the application
and and maintenance of the database and
so on so before we begin let us briefly
summarize what we have learnt about ear
models and relational models the ear
models as you already know is used for
conceptual modeling of the database
schema conceptual modeling or to create
the logical database schema this is
meant for human comprehension this is
essentially used to show end users what
you have understood about their problem
domain this is a high-level database
design and there are no implementation
details about that are included as part
of the ear model and of course the year
model is a DBMS independent and it is
made of building blocks like entities
relationships and attributes which which
can be attributed to both entities and
relationships in contrast a relational
data model is the data model that is
most popularly used for physical schema
design a physical schema is is the
schema that is actually implemented on
the computer therefore the the
relational data model is meant for or
optimized towards machine consumption
that is how do we
efficiently store data and in my
database how do I efficiently search for
a given data element how do i
efficiently update a given data element
so that it does not create anomalies how
do I delete data elements again without
creating any anomalies and so on and of
course the relational data model is also
DBMS independent that is no matter what
kind of database that you use you can
still use the same data model as far as
the database that you are using is a
relational database you can use the same
data model to represent your data on the
DBMS of course reality is quite
different from from the concept of for
DBMS independent and some DBMS systems
may include more features than
traditionally what is supported by the
relational model the relational model
also supports some kinds of automated
optimization techniques which we have
seen in the session on functional
dependencies where you can optimize a
given relational schema you can reason
whether a given relational schema is is
optimal or not whether it is going to
create redundant data in in its DBMS or
whether it is going to create some kind
of anomalies during updation and
deletion and so on and how we can
systematically change the database
schema without changing the correctness
but increasing the overall efficiency in
terms of retrieval and updates and what
are the building blocks of the
relational model we have relations which
which comprised of several different
attributes and the notion of keys forms
a very crucial role or plays a very
crucial role in the relational database
form let us come back to the year model
and look at some of the notations which
will require if you have to if you have
to study translation into year models
the entities are represented using
rectangles and a strong entity type that
is an entity type which has its own key
attribute and which represents a
physical or which represents some kind
of
logical entity of the of the real-life
is represented by a rectangle with solid
lines around it
for example the slide shows this entity
type called employee which depicts all
objects of type employee which which are
present in the present current system on
the other hand we also have what are
called as weak entities weak entities
are those which do not have an existence
of their own or without being associated
with a strong entity type the slide
shows the example of an insurance record
an insurance record does not mean
anything unless it is associated with
some person in a in a company for
example an employee therefore we have to
when we talk about insurance record we
have to say whose insurance record and
so on so that that is the general idea
and more specifically the insurance
record entity type does not have any key
attribute it has to be associated with
with the strong entity type called
employee which in turn has a key
attribute therefore such a weak entity
types are depicted using dashed lines or
dotted lines for the rectangle we then
have the relationship type for example a
relationship called handles so employee
handles project or something like that
which which is represented by a diamond
and a normal relationship type is
represented by a diamond using solid
lines whereas what are called as
identifying relationship types that is
the relationship types that identify a
weak entity or provide an identity for
weak entities by associating them with
strong entities they are shown with
double lines in the diameter entities
and attributes are associated or
entities and relationships are
associated with attributes which are
some values in a given domain attributes
are depicted using ovals and normal
attribute or a simple attribute is
depicted by oval with a
with a solid line and key attributes in
this example an attribute called pan or
pan number which uniquely identifies
each income taxpayer is shown as a key
attribute and it is shown underlined
saying that this this attribute is a key
attribute for this entity and then there
are multivalued attributes which which
can have several values for the same
attribute we took an example of the
color of a peacock now the color of a
peacock is is actually given by several
different colors and all of which in
combination formed from the color of the
bird such kinds of attributes are
depicted using double lines as shown in
the slide here and then there are
derived attributes that is attributes
whose values can be derived from other
attributes and these are shown using
dotted lines we took an example of the
age of an employee that is if you know
the date of the date of birth of an
employee in the current date we can
derive the age of an employee let us
also look at some definitions from the
relational model the relational model is
based around the the notion of a
mathematical relation now a mathematical
relation is said to comprise of atomic
values or atomic data values and what is
atomic data value a data value is called
atomic if it cannot be subdivided into
smaller values for example the age of a
person similarly each data value is said
to reside in a domain in the ER model a
domain is also called a value set which
is a term that is generally used by
several people and in the relational
model usually the term domain is used
which is going to which is going to
specify the range of values that a
particular attribute can take similarly
a relation schema or a relational schema
is denoted by a schema name that is in
this example shown by the name R and a
set of attributes in this example shown
by a 1 a 2 until a N and each attribute
has a specific value that lies within
the domain
specified as domain of AI we have also
defined what is known as the degree of
particular relationship that the degree
of the relationship is simply the number
of attributes in its relation schema if
you remember the same definition of the
degree of relationship also applied to
the ER model that is a relationship
diamond can be a binary relationship or
a ternary relationship unary
relationship or in a relationship that
is it can it can be associated with 1 2
3 or any number of entity types and the
slide shows that the relation is
actually is actually a subset of the
Cartesian product of all of the domains
that that form the attributes in the
relational model the notion of keys
plays play a very crucial role
especially we saw in the notion in the
in the process of decomposing relational
schema in order to make them normalized
or conformed into let us say bcnf or
third normal form or fourth normal form
and so on so let us revisit the notion
of keys in in a little more detail and
our keys are again very important when
and we translate from an ear model to a
relational model we have to be aware
which attributes are the key and which
attributes are the foreign key and so on
so a key constraint in the relational
model essentially defines the notion of
a super key which is a set of attributes
of a relation which can uniquely
identify each each tuple in the relation
that is each instance of the relation
and a key or a minimal superkey is
something which which is minimal in a
sense that if you if you remove any
element of the minimal superkey ceases
to be a super key anymore and there is
also the well-known entity integrity
constraint in the in the relational
model which says that the primary key of
of a given tuple may never be null the
primary keys is the minimal superkey
that is going
we used to uniquely identify a given
tuple in the relation and we also saw
the notion of referential integrity
which is again an important issue in in
the relational data model and the
referential integrity constraint says
that if a tuple of one relation refers
to another tuple of another relation it
should refer to an existing tuple that
means foreign keys that is keys of
primary keys of another relation
embedded into the into the tuple of yet
another relation should refer to tuples
that already exist in the in the first
relation so the foreign key constraints
are shown in this slide here that is
first of all the attribute of the
foreign key or the domain of the foreign
keys should be the same as the domain of
the primary key of the other relation
and they have to refer to existing
tuples in the other relation and we also
saw that relations can be are popularly
viewed as tables and which is what is
the notion used in SQL that is a
relation of the form student with three
attributes roll number name and lab can
be specified in the form of a table with
the name student and three kinds of
columns called
roll number name and lab so so let us
now come to the issue of mapping between
a given year model and a under
relational database model now I had said
in the beginning of this session why
this such such a mapping is important
there are several different commercial
tools that are available which are
called as lifecycle tools of DBMS design
a lifecycle tool provides support in
several or or in most of the phases of a
typical database lifecycle that means
the tool should be able to are using the
tool you should be able to create a
logical schema
talk to your end user saying this is
what I have understood by your
requirements of of your of your system
these are the different data elements
these are the different functionality
requirements that that form your system
and so on and then using the same tool
you should be able to create a physical
schema toff from the logical schema by
automatically translating them to
whatever extent possible in practice it
is not possible to completely automate
this process that is automatically
generate a relational model and optimize
it sometimes some kind of human
intervention is necessary in order when
when the human knows some domain
knowledge that cannot be captured into
the year model but there are several
such tools an example is is the tool
called er VIN from from Computer
Associates which which provide such a
support for automatically translating
between ER and the relational model so
let us see how we can go about such a
translation the first case that we are
going to take is the case of a simple
relation or a simple entity so the slide
here shows a simple entity type called
Department and it has three different
attributes Department name Department ID
and manager and the department ID
obviously is the key or the key
attribute of this department
now given such a relation it is fairly
obvious to see given such an entity type
it is fairly obvious to see that it can
be translated into a relation of the
type Department that is also shown in
the slide here just below the figure so
this ear model can be translated into
such a relation where the name is called
Department and which has three different
attributes Department ID Department name
and manager and also note that the key
attribute is retained that is the
department ID which is the key attribute
of this entity here becomes a primary
key of the relation that is formed so
this is straightforward that is as long
as we have a simple entity type with
simple attributes
note that the attributes are also simple
there are no multivalued attributes or
composite attributes and so on and
it can be translated in a
straightforward fashion to the
relational model what happens if we have
a composite attribute remember that a
composite attribute is something that is
made up of sub attributes a composite
attribute is different from a multi
valued attribute that is a multivalued
attribute is something which can have
many values for the same attribute the
color of a word can can have several
different colors on the other hand a
composite attribute is made up of two or
more other attributes each having its
own domain for example the slide shows a
composite attribute called Department ID
for the same example of a department
entity type so the department ID is the
attribute here which has which is a
composite attribute which in turn is
made up of two other attributes called
location ID and region number so on now
a location ID could could have a cell
could have a different domain let us say
let us say location IDs are given
alphabets like ABCD and so on and region
number are given numbers 1 2 3 4 and so
on
ok so both of them may have different
domains and they combine to form the
attribute called Department ID which in
turn is is also the primary key of this
department therefore we are considering
two different aspects here one is how to
deal with the composite attributes and
the second is what happens if the
composite attribute is the key attribute
of of the entity type so the slide here
shows shows and shows the example of and
shows how we can translate this into
relational model firstly the the name
Department of the entity type becomes
the name of the relation called
department and all the other simple
attributes are retained department name
is retained as department name manager
is retained as manager and only here for
the composite attribute the department
ID never appears here it is just that
all the simple components of the comp of
the composite attribute are a straight
straightaway loaded into this relation
that is location ID comes here and
region number comes here and
in fact if either of these two let us
say location ID our region number is
again a composite attribute and it has
some more attributes just take all the
simple components of the composite
attributes so do not take region number
and just take whatever is a simple
component of this region number and add
it to the relation here and all of these
simple components which form the
composite attribute which is the key
becomes the primary key that is the the
the primary key here is a composite key
in made up of tour two or more different
attributes which combined li identify or
help in identifying a tuple of the
relation the next example that we are
going to see is the example of how to
map relationships first of all let us
look at the following relationship that
is shown in this slide here what
characteristics can we can we ascribe to
the relationship that is shown here
firstly we notice that the relationship
here is a one is to one relationship
that is one employee is associated with
one insurance record okay or rather the
other way around in this case that is
one insurance record is associated with
one employee and have a look at the
Association as well the association or
the relationship type is is an
identifying relationship type that means
the relationship type called insurance
details shown in the slide here is used
to identify or provide an identity for
the insurance record by associating it
with an employee and also the insurance
record has a total participation in this
relationship that is insurance record
has no existence without this
relationship okay now how do we
translate translate such entity types
okay now so essentially the idea here is
what how do we translate insurance
record into the
model okay so so for weekend she types
the the translation is shown here in the
in the slide below just create a
relationship or just create a relation
of the same name as the entity type but
since it does not have a key - because
weak entity types do not have key
attributes so since it does not have a
key attribute use the key attribute from
employee with which it is associated
with and take that key attribute and
make it into the key attribute of of
insurance record however note that since
pan number here is also the primary key
for employee this has to be made as a
foreign key of insurance record that
means whenever we are we are updating or
altering the table we have to use the
Cascade option whenever let us say the
employee type is updated or deleted that
means to say that if if the employee
relation is deleted deleted from the
database this has to in turn delete all
the insurance record relations from the
database itself because insurance
records do not have any existence
without the employee records so so the
three steps here in order in order to
translate a weak entity type is to first
identify or is to first locate the
identifying relationship and see which
entity type is this weak entity type
associated with and use the primary key
of that entity type as the key for the
weak entity type or the record of the
relation for the weak entity type and
make it into a foreign key of of this
entity type and use cascade options
whenever updations or deletions are
performed on the strong entity entity
type let us move on with the translating
relationships so how does it translate
let us take the simplest form of
relationship again the one is to one
relationship we saw what happens or how
do how do we translate one is to one
relationships when we continue types are
considered now let us consider an entity
type which is not V but still in WA is
involved in a total participation that
this is shown in the figure here the
figure shows a relationship type called
managed by which relates two different
entity types that is a department and
manager and there are attributes
relevant attributes are shown for each
of them that is the department has a key
attribute called Department ID and
manager has a key attribute called
employee number and the relationship
itself has a key attribute called
secretary that is a secretary is
assigned for a department that is
managed by a manager that is the
secretary attribute does not have an
existence without an existence of this
relationship that is if if a department
is not managed by a manager then there
is no secretary that is associated with
with this okay now how do we translate
this the translation is again shown in
the slide below so first create create
an entity type create a relationship
create a relation called manager let me
repeat this again for this for this
relationship create a relation in the
our DBMS a model called manager with the
following attributes now you might be
wondering why should we create the the
why should we create a relation called
manager
why not Department okay now let us think
about it a little further see in this
slide here that manager is a is a strong
entity type it is not a weak entity type
however it is involved in a total
participation in this relationship type
what
a total participation the total
participation is that the entity type
does not have any existence without
being participating in a relationship
type of this kind so what it essentially
means that means here is that a manager
has no existence that is a manager would
probably be just an employee so so a
manager would have no existence unless
he or she is associated or is given a
department to manage okay so so it is
the it is the entity type that is
involved in the in the total
participation is taken as a primary
entity type or the base entity type a
primary relationship manager and then
employee number becomes the key here
that is the key for manager okay and the
department ID which is a primary key for
department becomes a foreign key in
manager and water attributes are
associated with the relationship itself
become attributes of this relation here
that is of the manager relation here
okay
so therefore the manager relation has a
primary called primary key called
employee number and a a foreign key
called department ID
note that this makes this makes sense
when we note that manager is a does not
have any existence without this
relationship that is a refer to the
problem of referential integrity in
relational data model what is the
referential integrity stipulate whenever
a foreign key refers to a tuple in
another relation the tuple should exist
that is it should refer to an existing
tuple in the other relation now if we
had made department as the base entity
or the base relation here we cannot use
employee number as as the as a foreign
key because the manager relation won't
even exist before this this relation
that is managed by is formed on the
other hand Department has an independent
existence without whether or not a
manager is associated with it
therefore
that forms a rationale behind why we
choose the entity type which entity type
which ship which is involved in total
participation as a base entity type for
the translation so let us summarize this
the previous slide once again so in any
one is to one binary relationship
between types SMT choose one of them as
a base relation in case one of them is
involved in a total participation choose
that as the base if neither Dept nor
manager were to be involved in total
participation it does not matter which
you are going to choose as the base
relation encrypt the primary key of the
primary key of the other entity type as
a foreign key in the base relation and
include any relationship attributes as
attributes of the base relation consider
the example shown in the slide here what
happens if in a one is to one
relationship both entity types that is
both entity types that are participating
in this one is to one binary
relationship are involved in the total
participation take a look at the slide
here the slide shows two entity types
project and consultant okay and each
project is uniquely identified by a
project ID and each consultant is
uniquely identified by his or her band
member and there is a relationship
called relationship type called
consultation which has its own attribute
called secretary and it is a one is to
one relationship and both of them are
involved in the total participation that
is a project has no existence unless it
it is being consulted by a consultant
and a consultant has no existence unless
he or she is associated with the project
so neither of them will have independent
existence without the other okay in such
cases we cannot identify any relation as
the base relation if I identify project
as a base relation and try to use pan
number of the consultant as the foreign
key then referential integrity
could be violated it is the same in the
other way around as well if we use
consultant as the base relation and try
to use project ID as the foreign key
again there is a chance of violating the
referential integrity in such cases that
the simplest way is to take the
relationship type in this case the
consultation as the base relation that
is a former relation called consultation
and use project ID and pan as as a
primary key of consultation and then all
of the attributes from both of them will
become attributes of this relation so in
case both entities in a one is to one
binary relationship or both are both in
total participation then we merge both
of the entity types into one usually in
the name of the relationship that is in
the name of the relationship
consultation in the previous example
now let us see how do we map one to n
relationship what is the 1 is to n
relationship that is n entities of one
of the entity types could be associated
with one entity of the other entity type
that is it forms some kind of a tree
relationship that is one entity being
associated with n different entities of
the other type so the slide here shows
such an example that is employee works
in department that is employees and in
two type so n different employees can
work in one department that is a one
department may have a several employees
but each employee is associated with
only one department and of course there
are keys called employee number for
employee and department ID for
department the slide also shows how we
can reduce this to a to a relation the
simplest way is to take the entity type
on the N side of a relationship okay
so in this case the employee so take
this as the base that is translated into
a relation called employee and the the
primary key of employee becomes a key of
the employee relation to T type here and
the department ID becomes a foreign key
here okay so this is a as simple as that
that is for each binary one is to end
relationships identify the relation s
that represents the entity type on the N
side why why is this so because each
entity type on the on the N side
uniquely identifies a department that is
uniquely identifies the entity type of
on the other side okay therefore we can
use the primary key on the of the entity
type on the other side as a foreign key
in the in the base relation therefore
use this as the base relation and create
a relation including the key of the
other entity type as the foreign key
how do we map em in relationships now
what is an MN relationship an MN
relationship essentially says that M
different entities of the first type can
be associated with n different entities
of the second type therefore there is no
unique identification that is given an
employee in the previous case one could
uniquely identify the department with
which the employee is working in because
each employee can can work in at most
one department on the other hand here
let us say the relation called
relationship called deputed - so an
employee could be deputed to several
departments let us say okay so so M
different employees can be deputed to n
different departments and of course
employee has has employee number as the
key and department has Department ID as
the key and so on okay and deputed 2
also has a has a has an attribute called
record number which maintains a record
of which employees reputed where and so
on okay so in order to translate such
relationships not note the steps that
are shown in the slide here there are
three different relations that are that
are formed one is the employee relation
okay that is one of the entity types of
this relationship type so the employee
relation is formed with employee number
as the primary key and and all the other
attributes that form the employee entity
type similarly a department relationship
relation is formed with a department ID
as the as the primary key and all other
relations and then a separate relation
is created for the relationship type
itself so deputed 2 becomes a separate
relation by itself and then uses
employee number and department ids as
foreign keys and also the primary keys
of this of this relation and whatever
attributes that belong to the that
belong to the relation becomes a part of
our belong to the relationship type
becomes part of the relation that is
created here
is deputy the record number attribute
becomes one of the attributes of deputed
to relation note that we cannot move
this recorded record number that is the
attribute of this relation to either the
employee or a department because it does
not uniquely identify either employee or
Department each employee could be
associated with n different record
numbers because there are so they could
be associated with n different
departments and similarly each
department could be associated with M
different record numbers because M
different employees could be working in
the term in the department so this slide
shows summarizes how M is to n
relationships are translated in a M s to
n binary relationship it is not possible
to collapse the relationship into one of
the entity types because neither of the
entity types uniquely identifies the
other entity type therefore a separate
relation is required and usually this is
in the name of the relationship type
itself ok so a separate relation is
required in order to complete the
mapping and of course the Cascade option
should be used whenever updates are
performed on any of the relations
pertaining to the entity types that is
whenever the whenever the entity type
called employee or department is updated
or deleted then these changes should be
cascaded so that they are reflected in
the deputed to relationship as well that
is if implies employee entity type is is
deleted then of course the deputy to
relationship should also be deleted
now one might ask the question is it
possible to is it possible to use the
strategy that is use the relationship
type as the base relation rather than
any of the participating entity types
can we use the strategy for mapping one
is to 1 and 1 is to n relations as well
because M is 2 n is simply a
generalization of 1 is to 1 and 1 is to
n relationships right so of course it is
possible that is a take the example of
employee works in department that is n
different employees working in one
department we can still create a
separate relation called works in where
it can use the employee number and the
department ID as the foreign keys of
this relation however it just creates an
extra relationship or extra relation in
there in the database that is that is
totally unnecessary but this is
sometimes actually attractive to use
then then then collapsing the
relationship into one of the relations
especially where especially if you have
to avoid null values that is especially
if you have to especially if you have
cases where there are some employees who
do not work in any department if n
different if an employee can be
associated with at most one department
it means that an employee can also be
associated with 0 departments so in that
case the the department ID field of of
employee would be null it does not
violate referential integrity because
remember that referential integrity says
that a foreign key should refer to an
existing tuple or else should be null
therefore it does not violate a
referential integrity but creates a lot
of null values in the database schema in
the database itself so if you have to
avoid null values it is actually
preferable to use the relationship type
as the base relation when performing the
translation how do we map multivalued
attributes we have seen how to map
composite attributes and
simple attributes and keys and so on but
what happens if there are multivalued
attributes composite attributes are
different from multivalued attributes in
the in the sense that each of them can
have several different domains that is
it is just a combination of several
simple attributes so we just open up the
combination when we are when we are
translating a composite attribute and
then include all the simple attributes
that form part of the composite
attribute on the other hand a
multivalued attribute is not a is not a
composition of several several sub
attributes instead it is an attribute
that can take on several values instead
of one value and the example this slide
shows is that of a bird okay
so a bird has a multivalued attribute
called color okay so what is the color
of this bird a bird could have several
colors it need not have just one color
and of course there is a primary key
called species which which identifies
each bird uniquely so in order to
translate multivalued attributes take a
look at the lower half of the slide
which shows a two different relations
which which make up this translation the
first relation shows a shows a relation
called bird with species as a primary
key and all other attributes except the
color attribute okay all other
attributes of the entity entity type
called bird and then a separate relation
is created called bird colors where
species and color are both are both
included and are both a part of the key
that is a combined li defined the key of
this bird therefore we say that bird
species X has a color Y X has color Z X
as color a and so on okay so the color
attribute may be repeated in in in
several or several tuples or rather the
species attribute may be repeated in
several tuples one once for each
different color that the bird can take
now and both of them that is species and
color become the primary key for bird
colors
so for each multivalued attribute of a
given entity type we have to create a
separate relation that has a primary key
of s paired with all possible values
that the multivalued attribute can take
and of course the Cascade option should
be used for referential integrity for on
the bird relation that is whenever the
bird relation is deleted or updated the
corresponding changes has to be made in
bird colors as well how do we map any
relationships until now we have been
looking at binary relationships what
happens when there are any relationships
and different entities forming part of
the relationship the slide here shows
such an example that is the standard
example of supplier supplies part to
project so there is a supplier who is
uniquely identified by the supplier or
the sales tax registration number or
something ST reg number and there is a
project that is uniquely identified with
project ID and there is a part that is
uniquely identified by part ID and then
the supplies relation which relates all
of these three different entity types so
the simple way of translating this is to
use a separate relation called supplies
as the base and of course separate
relations each for supplier project and
part with the corresponding primary keys
and the supplies relation which which
has a primary case of of each of these
relations as the foreign key of this of
this relation so for each for each any
relationship of a Fenny type R where n
is greater than 2 we have to create a
new relation as to represent this
relationship to represent this
relationship type R and of course the
primary keys of the participating
relations become foreign keys in in this
new relation and the Cascade option
should be used for all of the
sessions that correspond to the entity
types that participate in this
relationship what happens if one of the
relations in in an energy relationship
type is a weak entity type that is let
us say part is a weak entity type there
is no existence for part unless it is
associated with a supplier supplying it
to some project in that case we have to
identify we have we have to first we
have to first identify the entity type
which gives an existence to part and the
part a relation here has only foreign
keys it does not have any primary keys
but the supplies relationship does not
change that is it does not have any part
ID it just has it simply has the
supplier primary key and the project
primary key without the part primary
therefore therefore we get two different
relations that have foreign keys and and
they do not have their own primary keys
so so that that brings us to the end of
the this this session that talked about
that give a that they talked about how
we can map ER models into into a
relation relational database models
using several different rules and of
course this is not a comprehensive set
of rules because there are several other
sets of rules used for example in
derived attributes or or enhanced year
models like generalization and
specialization which which are not
covered here but all of them in totality
are used to create the basis for any
kind of a tool a software tool that can
translate between a given ER schema and
its a corresponding relational schema so
this slide shows a summary of each of
these mappings that is it gives a set of
thumb rules
saying if this is what is given in the
ER model then what happens in the
relational model okay
so in an ER model if an entity type is
given then a corresponding entity
relation that is a relation in the name
of the entity to entity type is created
if a 1 is to 1 or a 1 is to n binary
relationship is given then we create
corresponding foreign keys from from the
N side to the one side or from the weak
entity type to the to the to the strong
entity entity type in this image okay so
we create a relation and create
appropriate foreign keys from if a ms to
end relationship type is given as shown
in the slide here then we create a then
then we create a relation with the name
of the relationship type okay so there
is a within quotes which is shown as
relationship relation with two foreign
keys that is one for each entity entity
type that participates in this relation
if an energy relationship type is given
it is still the same strategy that is we
create a relationship relation with n
different foreign keys that is one for
each entity type or rather n different
foreign keys as long as this entity
types are strong entity types if there
is a simple attribute in a year model
that that simply becomes an attribute in
one of the relations in the relational
model if it is a composite attribute in
the year model then it becomes set of
simple attributes that is you take the
simple part of all component composite
attributes that is just go on finding
the simple attributes that form the
composite attribute and then make all of
them as part of this relation if it is a
multivalued attribute then we need to
create a separate relation and a foreign
key that is you have to associate the
primary key of the base relation with
each possible value of the multi valued
attribute if it is a value set it
becomes a domain a value set in the in
the ER model the it becomes a domain
and the key attribute in the in the
primary in the ear model will become
either a primary or a secondary key in
the in the relational model so that
brings us to the to the end of this
session
[Music]
you