3/1/23, 7:30 PM https://www.goseeko.

com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

DBMS

Unit 2

Relational Model

2.1 Components of ER model

The following components are composed of an ER Diagram:

1. Entity
2. Attributes
3. Relationships
4.

Fig 1: Component of ER model

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/dat… 1/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

1.Entity

The object, location, person, or event that stores data in the database may be an
entity. In an object-relationship diagram, a rectangle represents an entity.

Examples of an individual include a student, course, boss, employee, patient, etc.

Fig 2: Entity

Entity type:

A list or a set of entities having certain common attributes is an entity type. In a

database, a name and a list of attributes define each type of entity.

Entity set:

It is a set (or collection) of entities of the same kind that share attributes or related
properties.

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/dat… 2/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

For example, it is possible to describe the category of individuals who are lecturers
at a university as an entity-set lecturer. Similarly, the collection of students of the
organization could represent the community of all university students.

2.Attribute

In an Entity-Relationship Model, an attribute defines an entity's properties or

characteristics. It is represented in the ER diagram by an oval or ellipse shape. Each
oval shape represents one attribute and is directly linked to the person that is in the
shape of the rectangle.

For instance, the attributes defining the Employee form of entity are employee id,
employee name, gender, employee age, salary, and mobile no.

Fig 3: Different attributes of employee

In the ER model, the following categories can be defined by an attribute:

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/dat… 3/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

1.Simple attribute:

A simple attribute is called an attribute which contains an atomic value and can not
be divided further. The gender and salary of a worker, for instance, is also depicted
by an oval.

Fig 4: Simple attribute

2. Key attribute:

A key attribute is called an attribute that can uniquely identify an entity in an entity
set. It represents a primary key in the ER diagram. In an Entity Relationship diagram,
the key attribute is denoted by an oval with an underlying line. For example, for each
employee, the employee id would be unique.

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/dat… 4/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

Fig 5: Key attributes

3. Composite attribute:

An attribute that is a combination of two or more basic attributes is called a

composite attribute. It is defined by an ellipse in an Entity-Relationship diagram, and
that ellipse consists of other ellipses. For example, an employee entity type's name
attribute consists of first name, second name, and last name.

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/dat… 5/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

Fig 6: Composite attribute

4.Derived attribute:

A derived attribute is considered an attribute which can be derived from other

attributes. In an entity-relationship diagram, a dashed oval shape is used to represent
these attributes. Employee age is, for example, a derived attribute since it varies
over time and can be derived from another DOB attribute (Date of birth).

Fig 7: Derived attribute

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/dat… 6/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

5.Multivalued attributes:

An attribute which for a given entity contains more than one value. For instance, there
may be more than one mobile number and email address for an employee.

Fig 8: Multivalued attribute

3.Relationship

In the Entity-Relation Model, a relationship is used to define the relationship between

two or more entities. In the ER diagram, it is illustrated by a diamond shape.

For example, in college student studies, employees work in a department. Here, the
links are 'research in' and 'works in'.

Degree of Relationship

A partnership is called the degree of a relationship in which a variety of different

individuals participate.

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/dat… 7/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

Degree of relationship can be categorized into the following types:

1. Unary Relationship:

A relationship in which a single group of individuals is involved is referred to as a

unary relationship. For instance, in a company, an employee manages or supervises
another employee.

Fig 9: Unary relationship

2. Binary Relationship: When a relationship includes two people, it is considered a

binary relationship.

3. Ternary Relationship: When a relationship contains three individual sets, a

ternary relationship is called.

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/dat… 8/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

Fig 10: Ternary relationship

4. N-nary Relationship:

If a relationship includes more than three entity sets, an n-ary relationship is named.

Key takeaway :

- The object, location, person, or event that stores data in the database may be
an entity.

- In an Entity-Relationship Model, an attribute defines an entity's properties or

characteristics.

- Attributes are represented in the ER diagram by an oval or ellipse shape.

- In the Entity-Relation Model, a relationship is used to define the relationship

between two or more entities.

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/dat… 9/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

2.2 Conventions

The choice of names for entity types, attributes, relationship types, and (particularly)
functions is not always straightforward when designing a database schema. Names
that express, as far as possible, the meanings attached to the various constructs in
the schema should be selected.

For entity forms, rather than plural ones, we prefer to use singular names since the
name of the entity type refers to each particular entity belonging to that type of
entity.

We will use the convention in our ER diagrams that entity type and relationship type
names are uppercase letters, attribute names are capitalized by their initial letter,
and position names are lowercase letters.

In general, practice, the nouns appearing in the narrative tend to give rise to entity
form names, given a narrative definition of the database requirements, and the verbs
tend to imply names of types of relationships. Names of attributes typically come
from additional nouns which define the nouns corresponding to the types of entity.

Key takeaway:

- We will use the convention in our ER diagrams that entity type and relationship
type names.

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/d… 10/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/d… 11/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

Fig 11: symbols and their names

2.3 Converting ER diagrams into tables

Using notations, the database can be represented, and these notations can be reduced to a set
of tables.

Each entity set or relationship set can be represented in tabular form in the database.

The ER diagram is given below:

Fig 12: ER diagram

There are some points for converting the ER diagram to the table:

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/d… 12/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

● Entity type becomes a table.

In the given ER diagram, LECTURE, STUDENT, SUBJECT and COURSE forms individual tables.

● All single-valued attribute becomes a column for the table.

In the STUDENT entity, STUDENT_NAME and STUDENT_ID form the column of STUDENT table.
Similarly, COURSE_NAME and COURSE_ID form the column of COURSE table and so on.

● A key attribute of the entity type represented by the primary key.

In the given ER diagram, COURSE_ID, STUDENT_ID, SUBJECT_ID, and LECTURE_ID are the key
attribute of the entity.

● The multivalued attribute is represented by a separate table.

A hobby is a multivalued feature inside the student table. Thus, multiple values cannot be
expressed in a single column of the STUDENT table. We therefore generate a STUD HOBBY
table with the STUDENT ID and HOBBY column names. We create a composite key by using
both columns.

● Composite attribute represented by components.

Student address is a composite attribute in the given ER diagram. CITY, PIN, DOOR#, Path, and
STATE are included. These attributes will merge as an individual column in the STUDENT table.

● Derived attributes are not considered in the table.

In the STUDENT table, the derived attribute is age. It can be determined by measuring the
difference between the present date and the date of birth at any time.

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/d… 13/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

You can convert the ER diagram to tables and columns using these rules and assign the
mapping between the tables.

The following is the table structure for the given ER diagram:

Fig 13: Table structure

Key takeaway:

- Each entity set or relationship set can be represented in tabular form in the database.

- Using notations, the database can be represented, and these notations can be reduced to a
set of tables.

2.4 Relational Model

The primary data model is the Relational Data Model, which is commonly used for data storage
and processing around the world. This model is simple and has all the features and functionality
needed to process data with efficiency in storage.

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/d… 14/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

The relational model can be interpreted as a table with rows and columns. Each row is called a
tuple. There's a name or attribute for each table in the column.

2.4.1 Basic concept:

Table: Relationships are saved in the format of tables in a relational data model. The relationship
between entities is stored in this format. A table includes rows and columns, where rows
represent information, and attributes are represented by columns.

Tuple: A tuple is called a single row of a table, which contains a single record for that
relationship.

2.4.2 Attributes and Domains

Domain: It includes a set of atomic values that can be adopted by an attribute.

Attribute: In a specific table, it includes the name of a column. Every Ai attribute must have a
domain, a domain (Ai)

Relational instance: The relational example is represented in the relational database structure
by a finite set of tuples. There are no duplicate tuples for relation instances.

Relational schema: The name of the relationship and the name of all columns or attributes are
used in a relational schema.

Relational key: Each row has one or more attributes in the relational key. It can uniquely identify
the row in the association.

Example: STUDENT Relation

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/d… 15/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

NAME ROLL_NO PHONE_NO ADDRESS AGE

Ram 14795 7305758992 Noida 24

Shyam 12839 9026288936 Delhi 35

Laxman 33289 8583287182 Gurugram 20

Mahesh 27857 7086819134 Ghaziabad 27

Ganesh 17282 9028 9i3988 Delhi 40

● In the given table, NAME, ROLL_NO, PHONE_NO, ADDRESS, and AGE are the attributes.

● The instance of schema STUDENT has 5 tuples.

● t3 = <Laxman, 33289, 8583287182, Gurugram, 20>

2.4.3 Codd’s Rules:

After his thorough study into the Relational Model of Database Systems, Dr. Edgar F. Codd came
up with twelve rules of his own that, according to him, must be followed by a database in order
to be called a true relational database.

These principles can be extended to any database system that only uses its relational features
to handle stored data. This is a simple rule that serves as the basis for all the other rules.

1. Information Rule: The data contained in a database must be the value of a table cell, whether
it is user data or metadata. It is important to store everything in a database in a table format.

2. Guaranteed Access Rule: Each data element must be accessible by means of the name of
the table, its primary key, and the name of the attribute whose meaning is to be determined.

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/d… 16/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

3. Systematic Treatment of NULL values: A systematic and uniform treatment must be given to
the NULL values in a database. This is a very relevant rule since it is possible to interpret a NULL
as one of the following: information is missing, information is not known or information is not
applicable.

4. Active Online Catalog: The definition of the structure of the whole database must be stored
in an online catalogue, known as a data dictionary, accessible by registered users. The same
query language can be used by users to access the catalogue that they use to access the
database itself.

5. Comprehensive Data Sublanguage Rule: A database should be available in a language that

is supported for the process of description, manipulation and transaction management.

6. View Updating Rule: Various views that are generated for different purposes should be
automatically modified by the framework.

7. High level insert, update and delete rule: High-level addition, upgrading, and removal must
be assisted by a database. This must not be limited to a single row, which means that union,
intersection and minus operations must also be assisted in order to generate data record sets.

8. Physical data independence: At each relationship level, the Relational Model should support
insert, remove, update, etc. operations. Set operations such as Union, Intersection and minus
should also be endorsed.

9. Logical data independence: Any alteration of a table's logical or conceptual schema does
not involve modification at the level of the application. Merging two tables into one, for
example, does not impact access to the application, which is difficult to do.

10. Integrity Independence: Changed integrity restrictions at the database level do not
implement changes at the application level.

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/d… 17/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

11. Distribution Independence: For end-users, the distribution of data over different locations
should not be noticeable.

12. Non-Subversion Rule: Low level access to data should not be able to circumvent honesty
rules to alter data.

Key takeaway:

- Relational Data Model, which is commonly used for data storage and processing around the
world.

- Each row is called a tuple.

- There's a name or attribute for each table in the column.

- These Codd’s rule principles can be extended to any database system that only uses its
relational features to handle stored data.

2.5 Relational Integrity: Nulls

2.5.1 Nulls

In databases, what value or placeholder you use to reflect a missing value is a common
problem. This is solved with null in SQL. It is used to denote values which are absent or
unknown.

To show these values, the keyword NULL is used. NULL is not even as much of a particular
attribute as it is an indicator. Don't think of NULL as being zero or blank, but it's not the same
thing. The values are zero (0) and blank " ".

Each NULL value is commonly considered to be different from any other NULL in the database.
The outcome is assumed to be UNKNOWN when a NULL is involved in a comparison process.

2.5.2 Entity integrity

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/d… 18/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

● The honesty restriction of the organization states that the primary key value should not be
zero.

● This is because the primary key value is used to define the relationship between individual
rows, and if the primary key has a null value, then those rows cannot be identified.

● A table may contain a null value other than the field for the primary key.

Example

Fig 14: Example of entity

2.5.3 Referential integrity

● Between two relations or tables, the referential integrity constraints are defined and used to
preserve the consistency between the tuples in two relationships.

● If an attribute of the foreign key of the relationship R1 has the same domain(s) as the
primary key of the relationship R2, then the foreign key of R1 is said to refer to or refer to the
primary key of the relationship R2.

● Foreign key values in the R1 relationship tuple can either take the primary key values for a
certain R2 relationship tuple, or they can take NULL values, but cannot be zero.

Example

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/d… 19/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

Fig 15: Example of referential integrity

2.5.4 Enterprise constraints

Enterprise constraints are additional rules that users or database managers define and may be
based on several tables, often referred to as semantic constraints.

Some explanations are here.

● There can be a maximum of 30 students for a class.

● A maximum of four classes per semester can be taught by an instructor.

● An employee is unable to engage in more than five programmes.

● An employee's compensation cannot exceed the employee's manager's salary.

2.5.5 Views

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/d… 20/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

A view is just a relationship, but rather than a definition, we store a definition, A set of tuples.

Views can be lowered using the command DROP VIEW.

How can you treat DROP TABLE if there is a table view?

● The DROP TABLE command allows the user to define this choice.

● Create VIEW (name, grade) YoungActiveStudents AS SELECT

S.name, E.grade FROM Students S, Registered E WHERE S.sid =

E.sid and S.age<21 S.age

2.5.6 Schemas diagram

● An instance of the database is called the data that is stored in the database at a specific
moment in time.

● A database's overall architecture is called a schema.

● The skeleton structure of the data base is a database schema. It reflects the rational view of
the database as a whole.

● A schema includes schema objects such as a table, a foreign key, a primary key, views,
columns, data types, processes stored, etc.

● By using a visual diagram, a database schema can be represented. This diagram displays
the objects of the database and their relationship to each other.

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/d… 21/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

● Database designers develop a database schema to assist programmers whose software

communicates with the database. The database development method is called data modelling.

Only certain elements of a schema, such as the name of the record type, data type, and
constraints, can be seen in a schema diagram. You can't define other things through the schema
diagram. The provided figure, for instance, does not indicate either the data form of each data
item or the relationship between different files.

Real data changes very often inside the database. For example, the database changes whenever
we add a new grade or add a student. The data is called the instance of the database at a given
moment of time.

Fig 16: Schemas diagram

Key takeaway:

- Nulls are used to denote values which are absent or unknown.

- Between two relations or tables, the referential integrity constraints are defined and used to
preserve the consistency between the tuples in two relationships.

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/d… 22/23
3/1/23, 7:30 PM https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/seco…

- Enterprise constraints are additional rules that users or database managers define and may
be based on several tables, often referred to as semantic constraints.

- A view is just a relationship, but rather than a definition, we store a definition, A set of tuples.

- A schema includes schema objects such as a table, a foreign key, a primary key, views,
columns, data types, processes stored, etc.

References:

1. G. K. Gupta “Database Management Systems”, Tata McGraw Hill

2. Rab P., Coronel C. “Database Systems Design, Implementation and Management”, 5th edition,
Thomson Course Technology, 2002
3. Elmasri R., Navathe S. “Fundamentals of Database Systems”, 4th edition, Pearson Education,
2003

https://www.goseeko.com/reader/notes/savitribai-phule-pune-university-maharashtra/engineering/information-technology/second-year/sem-2/d… 23/23