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

Fig 1: Component of ER model

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.

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:

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.

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.

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

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.

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.

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.

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.

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:

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

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.

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

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.

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.

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.

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.

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

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.

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.

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

●      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

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

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.

●      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.

-         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