Database Management System

Module 2
The Relational Data Model and Relational Database Constraints
In the formal relational model terminology, a row is called a tuple, a column
header is called an attribute, and the table is called a relation. The data type
describing the types of values that can appear in each column is
represented by a domain.

Domains, Attributes, Tuples, and Relations

A domain D is a set of atomic values. By atomic we mean that each value in
the domain is indivisible as far as the formal relational model is concerned.
A relation schema R, denoted by R(A1, A2, ...,An), is made up of a relation
name R and a list of attributes, A1, A2, ..., An.

Each attribute Ai is the name of a role played by some domain D in the

relation schema R. D is called the domain of Ai and is denoted by dom(Ai).

A relation schema is used to describe a relation, R is called the name of this

relation.

The degree (or arity) of a relation is the number of attributes n of its

relation schema.

A relation of degree seven, which stores information about university

students, would contain seven attributes describing each student. as
follows:
The terms relation intension for the schema R .
Relation extension for a relation state r(R) are also commonly used.
STUDENT(Name, Ssn, Home_phone, Address, Office_phone, Age, Gpa).

BMSIT&M Page 1
Database Management System

A relation (or relation state) r(R) is a mathematical relation of degree n on

the domains dom(A1), dom(A2), ..., dom(An), which is a subset of the
Cartesian product (denoted by x) of the domains that define R:

Characteristics of Relations
Ordering of Tuples in a Relation.
A relation is defined as a set of tuples. Mathematically, elements of a set
have no order among them; hence, tuples in a relation do not have any
particular order. In other words, a relation is not sensitive to the ordering of
tuples. Tuple ordering is not part of a relation definition because a relation
attempts to represent facts at a logical or abstract level.
Ordering of Values within a Tuple and an Alternative Definition of a
Relation.
At a more abstract level, the order of attributes and their values is not that
important as long as the correspondence between attributes and values is

BMSIT&M Page 2
Database Management System

maintained. An alternative definition of a relation can be given, making

the ordering of values in a tuple unnecessary.

Values and NULLs in the Tuples.

Each value in a tuple is an atomic value hence, composite and multivalued
attributes are not allowed. This model is sometimes called the flat
relational model.
An important concept is that of NULL values, which are used to represent
the values of attributes that may be unknown or may not apply to a tuple. A
special value, called NULL, is used in these cases.

Interpretation (Meaning) of a Relation.

The relation schema can be interpreted as a declaration or a type of
assertion. For example, the schema of the STUDENT relation- in general, a
student entity has a Name, USN,Home_phone, Address, Office_phone, Age,
and Gpa.

Each tuple in the relation can then be interpreted as a fact or a particular

instance of the assertion.

Relational Model Notation:

R(A1, A2, ..., An) is a relational schema of degree n denoting that there is a
relation R having as its attributes A1, A2, ..., An. By convention, Q, R, and S
denote relation names.
By convention, q, r, and s denote relation states. For example, r(R) denotes
one possible state of relation R. If R is understood from context, this could
be written, more simply, as r.
By convention, t, u, and v denote tuples.
The "dot notation" R.A (e.g., [Link]) is used to qualify an attribute
name, usually for the purpose of distinguishing it from a same-named
attribute in a different relation (e.g., [Link]).

Relational Model Constraints and Relational Database Schemas

BMSIT&M Page 3
Database Management System

There are generally many restrictions or constraints on the actual values in

a database state. These constraints are derived from the rules in the
miniworld that the database represents.

Domain Constraints
Domain constraints specify that within each tuple, the value of each
attribute A must be an atomic value from the domain dom(A). The data
types associated with domains typically include standard numeric data
types for integers (such as short integer, integer, and long integer) and real
numbers (float and double precision float). Characters, Booleans, fixed-
length strings, and variable-length strings are also available, as are date,
time, timestamp, and money, or other special data types. Other possible
domains may be described by a subrange of values from a data type or as an
enumerated data type in which all possible values are explicitly listed.

Key Constraints and Constraints on NULL Values

A relation is defined as a set of tuples. By definition, all elements of a set are
distinct, hence- all tuples in a relation must also be distinct. There are other
subsets of attributes of a relation schema R with the property that no two
tuples in any relation state r of R should have the same combination of values
for these attributes. Suppose that we denote one such subset of attributes by
SK,then for any two distinct tuples t1 and t2 in a relation state r of R, we
have the constraint that: t1[SK]≠t2[SK]

Integrity, Referential Integrity, and Foreign Keys

The entity integrity constraint states that no primary key value can be
NULL. This is because the primary key value is used to identify individual
tuples in a relation
Having NULL values for the primary key implies that we cannot identify some
tuples. For example, if two or more tuples had NULL for their primary keys, we
may not be able to distinguish them if we try to reference them from other
relations.

BMSIT&M Page 4
Database Management System

The referential integrity constraint is specified between two relations and

is used to maintain the consistency among tuples in the two relations. the
referential integrity constraint states that a tuple in one relation that refers
to another relation must refer to an existing tuple in that relation. The
attribute Dno of EMPLOYEE gives the department number for which each
employee works; hence, its value in every EMPLOYEE tuple must match the
Dnumber value of some tuple in the DEPARTMENT relation.

We can diagrammatically display referential integrity constraints by drawing

a directed arc from each foreign key to the relation it references. For clarity,
the arrowhead may point to the primary key of the referenced relation.

The types of constraints we discussed so far may be called state

constraints because they define the constraints that a valid state of the
database must satisfy.

Update Operations, Transactions, and Dealing with Constraint

Violations

BMSIT&M Page 5
Database Management System

The operations of the relational model can be categorized into retrievals and
updates. There are three basic operations that can change the states of
relations in the database: Insert, Delete, and Update (or Modify).

They insert new data, delete old data, or modify existing data records.
Insert is used to insert one or more new tuples in a relation, Delete is used
to delete tuples.

Update (or Modify) is used to change the values of some attributes in

existing tuples.
Whenever these operations are applied, the integrity constraints specified on
the relational database schema should not be violated.

The Insert Operation

The Insert operation provides a list of attribute values for a new tuple t that
is to be inserted into a relation R. Domain constraints can be violated if an
attribute value is given that does not appear in the corresponding domain or is
not of the appropriate data type. Key constraints can be violated if a key
value in the new tuple t already exists in another tuple in the relation r(R).
Entity integrity can be violated if any part of the primary key of the new tuple
t is NULL. Referential integrity can be violated if the value of any foreign key
in t refers to a tuple that does not exist in the referenced relation.

The Delete Operation

The Delete operation can violate only referential integrity. This occurs if the
tuple being deleted is referenced by foreign keys from other tuples in the
database.
The Update Operation
The Update (or Modify) operation is used to change the values of one or
more attributes in a tuple (or tuples) of some relation R. It is necessary to
specify a condition on the attributes of the relation to select the tuple (or
tuples) to be modified.

BMSIT&M Page 6
Database Management System

The relational algebra is very important for several reasons. First, it provides
a formal foundation for relational model operations. Second, and perhaps
more important, it is used as a basis for implementing and optimizing queries
in the query processing and optimization modules that are integral parts of
relational database management systems (RDBMSs), Third, some of its
concepts are incorporated into the SQL standard query language for RDBMSs.

Unary Operations that operate on single relations.

JOIN and other complex binary operations, which operate on two tables by
combining related tuples (records) based on join conditions.

Unary Relational Operations:

SELECT and PROJECT
The SELECT Operation:
The SELECT operation is used to choose a subset of the tuples from a
relation that satisfies a selection condition. In general, the SELECT
operation is denoted by

<selection condition>(R)

where the symbol σ (sigma) is used to denote the SELECT operator and the
selection condition is a Boolean expression (condition) specified on the
attributes of relation R.
The Boolean expression specified in <selection condition> is made up of a
number of clauses of the form

<attribute name> <comparison op> <constant value>

or
<attribute name> <comparison op> <attribute name>

BMSIT&M Page 7
Database Management System

where <attribute name> is the name of an attribute of R, <comparison op> is

normally one of the operators {=, <, >, ≥,≤,≠ }, and <constant value> is a
constant value from the attribute domain.

Clauses can be connected by the standard Boolean operators and, or, not

Similarly,

The PROJECT operation, on the other hand, selects certain columns from
the table and discards the other columns. If we are interested in only certain
attributes of a relation, we use the PROJECT operation to project the relation
over these attributes only.

π Lname, Fname, Salary(EMPLOYEE)

The general form of the PROJECT operation is

π<attribute list>(R)
where π (pi) is the symbol used to represent the PROJECT operation, and
<attribute list> is the desired sublist of attributes from the attributes of
relation R.

The result of the PROJECT operation has only the attributes specified in
<attribute list> in the same order as they appear in the list. Hence, its degree
is equal to the number of attributes in <attribute list>. The PROJECT
operation removes any duplicate tuples, so the result of the PROJECT
operation is a set of distinct tuples, and hence a valid relation. This is
known as duplicate elimination.

BMSIT&M Page 8
Database Management System

πSex, Salary(EMPLOYEE)

Sequences of Operations and the RENAME Operation

A single relational algebra expression, also known as an in-line expression,
as follows:

shows the result of this in-line relational algebra expression.

Alternatively, we can explicitly show the sequence of operations, giving a

name to each intermediate relation, as follows:

It is sometimes simpler to break down a complex sequence of operations by

specifying intermediate result relations than to write a single relational
algebra expression.
We can also use this technique to rename the attributes.
We can also define a formal RENAME operation—which can rename either
the relation name or the attribute names, or both—as a unary operator. The
general RENAME operation when applied to a relation R of degree n is
denoted by any of the following three forms:

Relational Algebra Operations from Set Theory

The UNION, INTERSECTION, and MINUS Operations
Several set theoretic operations are used to merge the elements of two sets
in various ways, including UNION, INTERSECTION, and SET DIFFERENCE
(also called MINUS or EXCEPT)

BMSIT&M Page 9
Database Management System

The three operations UNION, INTERSECTION, and SET DIFFERENCE on

two union-compatible relations R and S as follows:

■ UNION: The result of this operation, denoted by R US, is a relation that

includes all tuples that are either in R or in S or in both R and S. Duplicate
tuples are eliminated.

■ INTERSECTION: The result of this operation, denoted by R ∩ S, is a

relation that includes all tuples that are in both R and S.

■ SET DIFFERENCE (or MINUS): The result of this operation, denoted by R

– S, is a relation that includes all tuples that are in R but not in S.

Notice that both UNION and INTERSECTION are commutative operations;

that is, R U S = S U R and R ∩ S = S ∩ R

Both UNION and INTERSECTION can be treated as n-ary operations

applicable to any number of relations because both are also associative
operations; that is, R U (S U T) = (R U S) U T and (R ∩ S ) ∩ T = R ∩ (S ∩ T )
1
The MINUS operation is not commutative; that is, in general, R − S ≠ S − R

INTERSECTION can be expressed in terms of union and set difference as

follows: R ∩ S = ((R U S ) − (R − S )) − (S − R)

BMSIT&M Page 10
Database Management System

The CARTESIAN PRODUCT (CROSS PRODUCT) Operation

CARTESIAN PRODUCT operation—also known as CROSS PRODUCT or
CROSS JOIN—which is denoted by ×. This is also a binary set operation,
but the relations on which it is applied do not have to be union compatible.

In general, the result of R(A1, A2, ..., An) × S(B1, B2, ..., Bm) is a relation Q
with degree n + m attributes Q(A1, A2, ..., An, B1, B2, ..., Bm), in that order.
The resulting relation Q has one tuple for each combination of tuples—one
from R and one from S.

Retrieve a list of names of each female employee‘s dependents

Binary Relational Operations: JOIN and DIVISION

The JOIN Operation

BMSIT&M Page 11
Database Management System

The JOIN operation, denoted by , is used to combine related tuples from

two relations into single ―longer‖ tuples. To illustrate JOIN, suppose that we
want to retrieve the name of the manager of each department.

To get the manager‘s name, we need to combine each department tuple with
the employee tuple whose Ssn value matches the Mgr_ssn value in the
department tuple.

Note that Mgr_ssn is a foreign key of the DEPARTMENT relation that

references Ssn, the primary key of the EMPLOYEE relation. This referential
integrity constraint plays a role in having matching tuples in the referenced
relation EMPLOYEE.

The general form of a JOIN operation on two relations5 R(A1, A2, ..., An) and
S(B1,B2, ..., Bm) is

A general join condition is of the form <condition> AND <condition>

AND...AND <condition> where each <condition> is of the form Ai ΘBj, Ai is
an attribute of R, Bj is an attribute of S, Ai and Bj have the same domain,
and Θ(theta) is one of the comparison operators {=, <, <=, >, >=, ≠}. A JOIN
operation with such a general join condition is called a THETA JOIN.

BMSIT&M Page 12
Database Management System

The DIVISION Operation

The DIVISION operation, denoted by ÷, is useful for a special kind of query
that sometimes occurs in database applications.

Q: Retrieve the names of employees who work on all the projects that
‘John Smith’ works on.
To express this query using the DIVISION operation, proceed as follows.
First, retrieve the list of project numbers that ‗John Smith‘ works on in the
intermediate relation SMITH_PNOS:
using the DIVISION operation retrieve the list of project numbers that ‗John
Smith‘ works on in the intermediate relation SMITH_PNOS:

create a relation that includes a tuple <Pno, Essn> whenever the employee
whose Ssn is Essn works on the project whose number is Pno in the
intermediate relation SSN_PNOS:

Finally, apply the DIVISION operation to the two relations, which gives the
desired employees‘ Social Security numbers:

BMSIT&M Page 13
Database Management System

ER-to-Relational Mapping Algorithm

Step 1: Mapping of Regular Entity Types
Step 2: Mapping of Weak Entity Types
Step 3: Mapping of Binary 1:1 Relation Types
Step 4: Mapping of Binary 1:N Relationship Types.
Step 5: Mapping of Binary M:N Relationship Types.
Step 6: Mapping of Multivalued attributes.
Step 7: Mapping of N-ary Relationship Types.

FIGURE :Result of mapping the COMPANY ER schema into a relational schema

BMSIT&M Page 14
Database Management System

Step 1: Mapping of Regular Entity Types.

For each regular (strong) entity type E in the ER schema, create a relation R
that includes all the simple attributes of E. Choose one of the key attributes
of E as the primary key for R. If the chosen key of E is composite, the set of
simple attributes that form it will together form the primary key of R.
Example: EMPLOYEE, DEPARTMENT, and PROJECT are the regular entities
in the ER diagram. SSN, DNUMBER, and PNUMBER are the primary keys
for the relations EMPLOYEE, DEPARTMENT, and PROJECT respectively.

Step 2: Mapping of Weak Entity Types For each weak entity type W in the
ER schema with owner entity type E, create a relation R and include all
simple attributes of W as attributes of R.
Example: Create the relation DEPENDENT in this step to correspond to the
weak entity type DEPENDENT. Include the primary key SSN of the
EMPLOYEE relation as a foreign key attribute of DEPENDENT (renamed to
ESSN). The primary key of the DEPENDENT relation is the combination
{ESSN, DEPENDENT_NAME} because DEPENDENT_NAME is the partial key
of DEPENDENT.

Step 3: Mapping of Binary 1:1 Relation Types For each binary 1:1
relationship type R in the ER schema, identify the relations S and T that
correspond to the entity types participating in R.
Example: 1:1 relation MANAGES is mapped by choosing the participating
entity type DEPARTMENT to serve in the role of S, because its participation
in the MANAGES relationship type is total.

Step 4: Mapping of Binary 1:N Relationship Types. For each regular

binary 1:N relationship type R, identify the relation S that represent the
participating entity type at the N-side of the relationship type.
Example: 1:N relationship types WORKS_FOR, CONTROLS, and
SUPERVISION in the figure. For WORKS_FOR we include the primary key
DNUMBER of the DEPARTMENT relation as foreign key in the EMPLOYEE
relation and call it DNO.

BMSIT&M Page 15
Database Management System

Step 5: Mapping of Binary M:N Relationship Types. For each regular

binary M:N relationship type R, create a new relation S to represent R.
Include as foreign key attributes in S the primary keys of the relations that
represent the participating entity types; their combination will form the
primary key of S.
Example: The M:N relationship type WORKS_ON from the ER diagram is
mapped by creating a relation WORKS_ON in the relational database
schema. The primary keys of the PROJECT and EMPLOYEE relations are
included as foreign keys in WORKS_ON and renamed PNO and ESSN,
respectively. Attribute HOURS in WORKS_ON represents the HOURS
attribute of the relation type. The primary key of the WORKS_ON relation is
the combination of the foreign key attributes {ESSN, PNO}.
Step 6: Mapping of Multivalued attributes. For each multivalued attribute
A, create a new relation R. This relation R will include an attribute
corresponding to A, plus the primary key attribute K-as a foreign key in R-of
the relation that represents the entity type of relationship type that has A as
an attribute.
Example: The relation DEPT_LOCATIONS is created. The attribute
DLOCATION represents the multivalued attribute LOCATIONS of
DEPARTMENT, while DNUMBER-as foreign key-represents the primary key
of the DEPARTMENT relation. The primary key of R is the combination of
{DNUMBER, DLOCATION}.

Step 7: Mapping of N-ary Relationship Types. For each n-ary relationship

type R, where n>2, create a new relationship S to represent R. Example: The
relationship type SUPPY in the ER below. This can be mapped to the relation
SUPPLY shown in the relational schema, whose primary key is the
combination of the three foreign keys {SNAME, PARTNO, PROJNAME}

Consider the following schema: Suppliers(sid: integer, sname: string,

address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid:
integer, pid: integer, cost: real)

BMSIT&M Page 16
Database Management System

1. Find the names of suppliers who supply some red part.

SQL: SELECT [Link]

FROM Suppliers S, Parts P, Catalog C
WHERE [Link]=‘red‘ AND [Link]=[Link] AND [Link]=[Link]

2. Find the sids of suppliers who supply some red or green part.

SQL:
SELECT [Link]
FROM Catalog C, Parts P
WHERE ([Link] = ‘red’ OR [Link] = ‘green’) AND [Link] = [Link]

3. Find the sids of suppliers who supply some red part or are at 221
Packer Street.

SQL:
SELECT [Link]
FROM Suppliers S
WHERE [Link] = ‘221 Packer street’ OR [Link] IN
( SELECT [Link]
FROM Parts P, Catalog C
WHERE [Link]=‘red‘ AND [Link] = [Link]
)

4. Find the sids of suppliers who supply some red part and some green
part.

SQL:
SELECT [Link]
FROM Parts P, Catalog C
WHERE [Link] = ‗red‘ AND [Link] = [Link] AND
EXISTS ( SELECT [Link]
FROM Parts P2, Catalog C2
WHERE [Link] = ‗green‘ AND [Link] = [Link] AND [Link] = [Link] )
5. Find the sids of suppliers who supply every part.

BMSIT&M Page 17
Database Management System

SQL:
SELECT [Link]
FROM Catalog C
WHERE NOT EXISTS (SELECT [Link] FROM Parts P WHERE NOT EXISTS
(SELECT [Link] FROM Catalog C1 WHERE [Link] = [Link] AND [Link] = [Link]))

6. Find the sids of suppliers who supply every red part.

SQL:
SELECT [Link]
FROM Catalog C
WHERE NOT EXISTS (SELECT [Link] FROM Parts P WHERE [Link] = ‗red‘
AND (NOT EXISTS (SELECT [Link] FROM Catalog C1 WHERE [Link] = [Link]
AND [Link] = [Link])))

7. Find the sids of suppliers who supply every red or green part.

SQL:
SELECT [Link]
FROM Catalog C
WHERE NOT EXISTS (SELECT [Link] FROM Parts P WHERE ([Link] = ‗red‘ OR
[Link] = ‗green‘) AND (NOT EXISTS (SELECT [Link] FROM Catalog C1 WHERE
[Link] = [Link] AND [Link] = [Link])))

8. Find the sids of suppliers who supply every red part or supply every
green part.

SQL:
SELECT [Link] FROM Catalog C WHERE (NOT EXISTS (SELECT [Link] FROM
Parts P WHERE [Link] = ‗red‘ AND (NOT EXISTS (SELECT [Link] FROM
Catalog C1 WHERE [Link] = [Link] AND [Link] = [Link])))) OR ( NOT EXISTS
(SELECT [Link] FROM Parts P1 WHERE [Link] = ‗green‘ AND (NOT EXISTS
(SELECT [Link] FROM Catalog C2 WHERE [Link] = [Link] AND [Link] =
[Link]))))

BMSIT&M Page 18
Database Management System

9. Find pairs of sids such that the supplier with the first sid charges more
for some part than the supplier with the second sid.

SQL:
SELECT [Link], [Link] FROM Catalog C1, Catalog C2 WHERE [Link] = [Link]
AND [Link] = [Link] AND [Link] > [Link]

10. Find the pids of parts supplied by at least two different suppliers.

SQL:
SELECT [Link] FROM Catalog C WHERE EXISTS (SELECT [Link] FROM Catalog
C1 WHERE [Link] = [Link] AND [Link] != [Link] )

11. Find the Supplier names of the suppliers who supply a red part that
costs less than 100 dollars.

12. Find the Supplier names of the suppliers who supply a red part that
costs less than 100 dollars and a green part that costs less than 100
dollars.

13. Find the Supplier ids of the suppliers who supply a red part that costs
less than 100 dollars and a green part that costs less than 100 dollars.

14. Find the Supplier names of the suppliers who supply a red part that
costs less than 100 dollars and a green part that costs less than 100
dollars.

BMSIT&M Page 19
Database Management System

Relational Database Design using ER to Relational

Mapping

In Database Management Systems, ER stands for Entity-Relationship. ER modelling

help to figure out the set of entities, attributes of each entitiy, and the relationship
that is shared between entities. In other words it helps us to explain the logical
structure of databases.

Step by step method to map an ER Model to Relational Diagram.

Consider the below company database ER diagram

ER-to-Relational Mapping Algorithm

 Step 1: Mapping of Regular Entity Types
 Step 2: Mapping of Weak Entity Types
 Step 3: Mapping of Binary 1:1 Relation Types
 Step 4: Mapping of Binary 1:N Relationship Types.
 Step 5: Mapping of Binary M:N Relationship Types.
 Step 6: Mapping of Multivalued attributes.
Step 7: Mapping of N-ary Relationship Types

BMSIT&M Page 20
 Database Management System

Step 1:
 Figure out all the regular/strong entity from the diagram and
then create a corresponding relation(table) that includes all the
simple attributes.

 Choose one of the attributes as a primary key. If composite, the

simple attributes together form the primary key.

 For the given ER-Diagram we have Employee, Department

and Project as strong/regular entity, as they are enclosed in
single rectangle.

After Step 1

Step 2:
 Figure out the weak entity types from the diagram and create a
corresponding relation(table) that includes all its simple attributes.
 Add as foreign key all of the primary key attributes in the entity
corresponding to the owner entity.
 The primary key is a combination of all the primary key
attributes from the owner and the primary key of the weak entity.
 For the given ER-Diagram we have Dependent as a weak
entity, as it is enclosed in a double rectangle that is indicative of an
entity being weak.
 The Dependent relation(table) is created that is shown in the
figure below.

BMSIT&M Page 21
Database Management System

After step2
Step 3:
 The entities for which there exists a 1-to-1 relationship, choose
one relation(table) as S, the other as T.
Better if S has total participation (reduces the number of NULL
values).
 Then we need to add to S all the simple attributes of the
relationship if there exists any.
 After that, we add as a foreign key in S the primary key
attributes of T.
 For the given ER-Diagram there exists a 1-to-1 relationship
between Employee and Department entity.
 Here Department has total participation therefore consider it
as relation S and Employee as relation T.
 The 1-to-1 mapping between Employee and Department is
depicted in the figure below

After step 3

BMSIT&M Page 22
Database Management System

Step 4:
 Now we need to figure out the entities from ER diagram for
which there exists a 1-to-N relationship.
 The entities for which there exists a 1-to-N relationship, choose
a relation as S as the type at N-side of relationship and other as
T.
 Then we add as a foreign key to S all of the primary key
attributes of T.
 In the given ER diagram there are two 1-to-N relationships that
exists between Employee-Department and Employee-
Dependent entity.
 The 1-to-N mapping between Employee-
Department and Employee-Dependent is depicted in the figure
below.

After step 4

BMSIT&M Page 23
Database Management System

Step 5:
 Now we need to figure out the entities from ER diagram for
which there exists an M-to-N relationship.
 Create a new relation(table) S.
 The primary keys of relations(tables) between which M-to-N
relationship exists, are added to the new relation S created, that
acts as a foreign key.
 Then we,add any simple attributes of the M-to-N relationship
to S.
 For the given ER-Diagram there exists M-to-N relationship
between Employee and Project entity.
 The new table Works_On is created for mapping the
relationship between Employee and Project relation(table).

Step 5
Step 6:

BMSIT&M Page 24
 Database Management System

 Now identify the relations(tables) that contain multi-valued

attributes.
 Then we need to create a new relation S
 In the new relation S we add as foreign keys the primary keys
of the corresponding relation.
 Then we add the multi-valued attribute to S; the combination
of all attributes in S forms the primary key.
 For the given ER-Diagram there exists a multi-valued attribute
(Locations) in Department relation(table).
 So, we create a new relation called Dept_Locations. To this
new relation we add the primary key of Department Table that
is D_Number and the multi-valued attribute Locations.

Step 7: Mapping of N-ary Relationship Types.

o For each n-ary relationship type R, where n>2, create a new
relationship S to represent R.
o Include as foreign key attributes in S the primary keys of the
relations that represent the participating entity types.
o Also include any simple attributes of the n-ary relationship type (or
simple components of composite attributes) as attributes of S.

BMSIT&M Page 25
Database Management System

 Example: The relationship type SUPPLY in the ER on the next

slide.
o This can be mapped to the relation SUPPLY shown in the
relational schema, whose primary key is the combination of the
three foreign keys {SNAME, PARTNO, PROJNAME}

Mapping the n-ary relationship type SUPPLY

Correspondence between ER and Relational Models

ER Model Relational Model
Entity type “Entity” relation
1:1 or 1:N relationship type Foreign key (or “relationship” relation)
M:N relationship type “Relationship” relation and two foreign keys
n-ary relationship type “Relationship” relation and n foreign keys
Simple attribute Attribute
Composite attribute Set of simple component attributes
Multivalued attribute Relation and foreign key
Value set Domain
Key attribute Primary (or secondary) key

BMSIT&M Page 26