14CS440 / Database Management Systems 2018
Relational Model
Structure of Relational Databases
The current values (relation instance) of a relation are specified by a
table
An element t of r is a tuple, represented by a row in a table
Order of tuples is irrelevant (tuples may be stored in an arbitrary order)
Schema Diagram
Rajeswari A.M. Page 1 of 6
14CS440 / Database Management Systems 2018
Mapping ER Model to Relational Model
Entity sets and relationship sets can be expressed uniformly as relation
schemas that represent the contents of the database.
A database which conforms to an E-R diagram can be represented by a
collection of schemas.
For each entity set and relationship set there is a unique schema that is
assigned the name of the corresponding entity set or relationship set.
Each schema has a number of columns (generally corresponding to
attributes), which have unique names.
Representing Strong Entity Sets
A strong entity set reduces to a schema with the same attributes
instructor (ID, name, salary )
student(ID, name, tot_cred)
Rajeswari A.M. Page 2 of 6
14CS440 / Database Management Systems 2018
Representing Weak Entity Sets
A weak entity set becomes a schema that includes all the attributes of
the weak entity set plus the primary key of the identifying strong entity
set
section ( course_id, sec_id, sem, year )
Representing Relationship Sets
A many-to-many relationship set is represented as a schema with
attributes for the primary keys of the two participating entity sets, and
any descriptive attributes of the relationship set.
Example: schema for relationship set advisor
advisor = (student_id, instructor_id)
Rajeswari A.M. Page 3 of 6
14CS440 / Database Management Systems 2018
Representing Entity Sets with Composite Attributes
Composite attributes are flattened out by creating a separate attribute for
each component attribute
Example: given entity set instructor with composite attribute name with
component attributes first_name and last_name the schema
corresponding to the entity set has two attributes name_first_name and
name_last_name
Prefix omitted if there is no ambiguity (name_first_name could be
first_name)
Ignoring multivalued attributes, extended instructor schema is
instructor(ID,first_name, middle_initial,
last_name, street_number, street_name,
apt_number, city, state, zip, date_of_birth)
Rajeswari A.M. Page 4 of 6
14CS440 / Database Management Systems 2018
Representing Entity Sets with Multivalued Attributes
A multivalued attribute M of an entity E is represented by a separate
schema EM with the attributes corresponding to the primary key of E
and an attribute corresponding to multivalued attribute M
Eg: Multivalued attribute phone_number of instructor represented as
inst_phone= ( instructor_ID, phone_number)
Redundancy of Schemas
Many-to-one and one-to-many relationship sets that are total on the
many-side can be represented by adding an extra attribute to the “many”
side, containing the primary key of the “one” side
Eg: Instead of creating a schema for relationship set inst_dept, add an
attribute dept_name to the schema arising from entity set instructor
Thus, we can eliminate inst_dept and stud-dept
Rajeswari A.M. Page 5 of 6
14CS440 / Database Management Systems 2018
For one-to-one relationship sets, either side can be chosen to act as the
“many” side
o That is, an extra attribute can be added to either of the tables
corresponding to the two entity sets
If participation is partial on the “many” side, replacing a schema by an
extra attribute in the schema corresponding to the “many” side could
result in null values
The schema corresponding to a relationship set linking a weak entity set
to its identifying strong entity set is redundant.
Example: The section schema already contains the attributes that would
appear in the sec_course schema
Thus, we can eliminate sec_course
**************
Rajeswari A.M. Page 6 of 6
