Data Security Requirement

We should use technology to ensure a secure computing environment for the organization. Although it is not possible to find a technological solution for all problems, most of the security issues could be resolved using appropriate technology. The bas~c security standards which technology can ensure are confidentiality, integrity and availability.

Confidentiality

A secure system ensures the confidentiality of data. This means that it allows individuals to see only the data they are supposed to see. Confidentiality has several aspects like privacy of communications, secure storage of sensitive data, authenticated users and authorization of users.

Privacy of Communications

The DBMS should be capable of controlling the spread of confidential personal information such as health, employment, and credit records. It should also keep the corporate data such as trade secrets, proprietary information about products and processes, competitive analyses, as well as marketing and sales plans secure and away from the unauthorized people.

Secure Storage of Sensitive Data

Once confidential data has been entered, its integrity and privacy must be protected on the databases and servers wherein it Resides.

Authentication

One of the most basic concepts in database security is authentication, which is quite simply the process by which it system verifies a user's identity, A user can respond to a request to authenticate by providing a proof of identity, or an authentication token

You're probably already familiar with concept. If you have ever been asked to show a photo ID (for example, when opening a bank account), you have been presented with a request for authentication. You proved your identity by showing your driver's license (or other photo ID). In this case, your driver's license served as your authentication token.

Despite what you see in the movies, most software programs cannot use futuristic systems such as face recognition for authentication. Instead most authentication requests ask you to provide a user ID and a password. Your user ID represents your claim to being a person authorized to access the environment, and the password is protected and you are the only person who knows it.

Authorization

An authenticated user goes through the second layer of security, authorization. Authorization is the process through which system obtains information about the authenticated user, including which database operations that user may perform and which data objects that user may access.

Your driver's license is a perfect example of an authorization document. Though it can be used for authentication purposes, it also authorizes you to drive a certain class of car. Furthermore, the type of authorization you have gives you more or fewer privileges as far as driving a vehicle goes.

A user may have several forms of authorization on parts of the database. There are the following authorization rights.

• Read authorization allows reading, but not modification, of data.

• Insert authorization allows insertion of new data, but not modification of existing data.

• Update authorization allows modification, but not deletion of data.

• Delete authorization allows deletion of data.

A user may be assigned all, none, 'or a combination of these types of authorization. In addition to these forms of authorization for access to data, a user may be granted authorization to modify the database schema:

• Index authorization allows the creation and deletion of indexes.

• Resource authorization allows the creation of new relations.

• Alteration authorization allows the addition or deletion of attributes in a relation.

• Drop authorization allows the deletion of relations.

The drop and delete authorization differ in that delete authorization allows deletion of tuples only. If a user deletes all tuples of a relation, the relation still exists, but it is empty. If a relation is dropped it no longer exists. The ability to create new relations is regulated through resource authorization. A user with resource authorization who creates a relation is given a privilege on that relation automatically. Index authorization is given to user to get the fast access of data on the bases of some key field.

Integrity

A secure system en sums that the data it contains is valid. Data integrate means that data is protected from deletion and corruption, both while it resides within the data-case, and while it is being transmitted over the network. The detailed discussion on Integrity is un next section.

Availability

A secure system makes data available to authorized users, without delay. Denial of service attacks are attempts to block authorized users' ability to access and use the system when needed.

Security Levels

To protect the database, we must take security measures at several levels:

• Physical: The sites containing the computer systems must be secured against armed or surreptitious entry by intruders.

• Human: Users must be authorized carefully to reduce the chance of any such user giving access to an intruder in exchange for a bribe or other favors .

•Operating System: No matter how secure the database system is, weakness in operating system security may serve as a means of unauthorized access to the database.

• Network: Since almost all database systems allow remote access through terminals or networks, software-level security within the network software is as important as physical security, both on the Internet and in networks private to an enterprise.

• Database System: Some database-system users may be authorized to access only a limited portion of the database. Other users may be allowed to issue queries, but may be forbidden to modify the data. It is responsibility of the database system to ensure that these authorization restrictions are not violated.

Security at all these levels must be maintained if database security is to be ensured. A weakness at a low level of security (physical or human) allows circumvention of strict high level (database) security measures.

Importance of Database Security

Security is an important issue in database management because information stored in a database is very valuable and many time, very sensitive commodity. So the data in a database management system need to be protected from abuse and should be protected from unauthorized access and updates. It is popular belief that hackers cause most security breaches, but in reality 80% of data loss is to insiders.

Importance of Security in Database Environment

Database security is the protection of the database against intentional and unintentional threats that may be computer-based or non-computer-based. Database security is the business of the entire organization as all people use the data held in the organization's database and any loss or corruption to data would affect the day-to-day operation of the organization and the performance of the people. Therefore, database security encompasses hardware, software, infrastructure, people and data of the organization.

Now there is greater emphasis on database security than in the past as the amount of data stored in corporate database is increasing and people are depending more on the corporate data for decision-making, customer service management, supply chain management and so on. Any loss or unavailability to the corporate data will cripple today's organization and will seriously affect its performance. Now the unavailability of the database for even a few minutes could result in serious losses to the organization.

Data Security Risks

We have seen that the database security is the concern of the entire organization. The organization should identify all the risk factors and weak elements from the database security Perspective and find solutions to counter and neutralize each such threat.

A threat is any situation, event or personnel that will adversely affect the database security and the smooth and efficient functioning of the organization. A threat may be caused by a situation or event involving a person, action or circumstance that is likely to bring harm to the organization. The harm may be tangible, such as loss of data, damage to hardware, loss of software or intangible such as loss of customer goodwill or credibility and so on.

Data Tampering

Privacy of communications is essential to ensure that data cannot be modified or viewed in transit. The chances of data tampering are high in case of distributed environments as data moves between sites. In a data modification attack, an unauthorized party on the network intercepts data in transit and changes that data before retransmitting it. An example of this is changing the amount of a banking transaction from Rs. 1000 to Rs. 10000.

Data Theft

Data must be stored and transmitted securely, so that information such as credit card numbers cannot be stolen. Over the Internet and Wide Area Network (WAN) environments, both public carriers and private network owners often route portions of their network through insecure landlines, extremely vulnerable microwave and satellite links, or a number of servers. This situation leaves valuable data opens to view by any interested party. In Local Area Network (LAN) environments within a building or campus, insiders with access to the physical wiring can potentially view data not intended for them.

Falsifying User Identities

In a distributed environment, it becomes more feasible for a user to falsify an identity to gain access to sensitive and important information. Criminals attempt to st.eal users' credit card numbers, and then make purc~1ases against the accounts. Or they steal other personal data, such as bank account numbers and driver's license numbers, and setup bogus credit accounts in someone else's name.

Password-Related Threats

In large systems, users must remember multiple passwords for the different applications and services that they use. Users typically respond to the problem of managing multiple passwords in several ways:

• They may select easy-to-guess password

• They may also choose to standardize passwords so that they are the same on all machines or websites.

All these strategies compromise password secrecy and service availability. Moreover, administration of multiple user accounts and passwords is complex, time-consuming, and expensive.

Unauthorized Access to Tables and Columns

The database may contain confidential tables, or confidential columns in a table, whichshould not be available indiscriminately to all users authorized to access the database. It shouldbe possible to protect data on a column level.

Unauthorized Access to Data Rows

Certain data rows may contain confidential information that should not be available indiscriminately to users authorized to access the table. For example, in a shared environment' businesses should have access only to their own data; customers should be able to see only their own orders.

Lack of Accountability

If the system administrator is unable to track users' activities, then users cannot ~e held responsible for their actions. There must be some reliable ways to monitor who is performing what operations on the data.

Complex User Management Requirements

System must often support large number of users and therefore they must be scalable.

In such large-scale environments, the burden of managing user accounts and passwords makes your system yulnerable to error and attack.

Convert ER into Tables/Relation

Introduction

As the database grows, the ER diagram representation becomes more complex and crowded. It is creates a difficult situation to understand the requirement and their structure as a whole. Similarly, if ER diagram is represented at very high level, it again creates a difficulty in understanding the system. But representation at high level and till the minute levels is very necessary to understand the system well. These concepts are well defined by generalization and specialization. Sometimes, we would have divided the entities into two or more entities to be more accurate in design. But when compared to the whole database or user, it can be combined to one entity. Such a process is called as aggregation.

Once designing ER diagram is complete, we need to put it into logical structure. But how it can be done? Let us discuss this in the last section.

Generalization

In our Employee example, we have seen different types of employees like Engineer, Accountant, Salesperson, Clerk etc. Similarly each employee belongs to different departments. We can represent it in an ER diagram as below. When you see this diagram for the first time, you will not understand it quickly. One will take time to understand it or he might misunderstand some requirement.

What if we group all the sub departments into one department and different employees into one employee? However sub departments and different employee types have same features in their own domain. So if we merge the child entities into their parent, it makes the diagram simpler, hence easy to understand. This method of merging the branches into one is called generalization. We can see the generalized structure of requirement to understand it quickly.  So above ER diagram will be changed to as below:

Isn’t it simpler? Generalization is the bottom up approach which helps to design the requirement at high level. Thus making one to understand quickly.

Specialization

It is opposite approach of generalization. Here, each entity is further divided into sub levels to understand it deeper. In the above example, Department entity is further divided into sub departments to understand how they are scattered. This method of representation helps the developer to code correctly and quickly. It is a top down approach of breaking higher level entity to low level entity. Once the entities are understood at higher level, it makes easy to understand the requirement at low level.

One more example of specialization would be Person. We can further divide person as STUDENT, TEACHER, ENGINEER, SOLDIER etc. (Merging STUDENT, TEACHER, ENGINEER etc into PERSON is an example of generalization).

Aggregation

Look at below ER diagram of STUDENT, COURSE and SUBJECTS. What does it infer? Student attends the Course, and he has some subjects to study. At the same time, Course offers some subjects. Here a relation is defined on a relation. But ER diagram does not entertain such a relation. It supports mapping between entities, not between relations. So what can we do in this case?

If we look at STUDENT and COURSE from SUBJECT’s point of view, it does not differentiate both of them. It offers it’s subject to both of them. So what can we do here is, merge STUDENT and COURSE as one entity. This process of merging is called aggregation. It is completely different from generalization. In generalization, we merge entities of same domain into one entity. In this case we merge related entities into one entity.

Here we have merged STUDENT and COURSE into one entity STUDENT_COURSE. This new entity forms the mapping with SUBJECTS. The new entity STUDENT_COURSE, in turn has two entities STUDENT and COURSE with ‘Attends’ relationship.

Transform ER Diagram into Tables

Since ER diagram gives us the good knowledge about the requirement and the mapping of the entities in it, we can easily convert them as tables and columns. i.e.; using ER diagrams one can easily created relational data model, which nothing but the logical view of the database.

There are various steps involved in converting it into tables and columns. Each type of entity, attribute and relationship in the diagram takes their own depiction here. Consider the ER diagram below and will see how it is converted into tables, columns and mappings.

The basic rule for converting the ER diagrams into tables is

• Convert all the Entities in the diagram to tables.

All the entities represented in the rectangular box in the ER diagram become independent tables in the database. In the below diagram, STUDENT, COURSE, LECTURER and SUBJECTS forms individual tables.

• All single valued attributes of an entity is converted to a column of the table

All the attributes, whose value at any instance of time is unique, are considered as columns of that table. In the STUDENT Entity, STUDENT_ID, STUDENT_NAME form the columns of STUDENT table. Similarly, LECTURER_ID, LECTURER_NAME form the columns of LECTURER table. And so on.

• Key attribute in the ER diagram becomes the Primary key of the table.

In diagram above, STUDENT_ID, LECTURER_ID, COURSE_ID and SUB_ID are the key attributes of the entities. Hence we consider them as the primary keys of respective table.

• Declare the foreign key column, if applicable.

In the diagram, attribute COURSE_ID in the STUDENT entity is from COURSE entity. Hence add COURSE_ID in the STUDENT table and assign it foreign key constraint. COURSE_ID and SUBJECT_ID in LECTURER table forms the foreign key column. Hence by declaring the foreign key constraints, mapping between the tables are established.

• Any multi-valued attributes are converted into new table.

A hobby in the Student table is a multivalued attribute. Any student can have any number of hobbies. So we cannot represent multiple values in a single column of STUDENT table. We need to store it separately, so that we can store any number of hobbies, adding/ removing / deleting hobbies should not create any redundancy or anomalies in the system. Hence we create a separate table STUD_HOBBY with STUDENT_ID and HOBBY as its columns. We create a composite key using both the columns.

• Any composite attributes are merged into same table as different columns.

In the diagram above, Student Address is a composite attribute. It has Door#, Street, City, State and Pin. These attributes are merged into STUDENT table as individual columns.

• One can ignore derived attribute, since it can be calculated at any time.

In the STUDENT table, Age can be derived at any point of time by calculating the difference between DateOfBirth and current date. Hence we need not create a column for this attribute. It reduces the duplicity in the database.

These are the very basic rules of converting ER diagram into tables and columns, and assigning the mapping between the tables. Table structure at this would be as below:

Let us see some of the special cases.

• Converting Weak Entity

Weak entity is also represented as table. All the attributes of the weak entity forms the column of the table. But the key attribute represented in the diagram cannot form the primary key of this table. We have to add a foreign key column, which would be the primary key column of its strong entity. This foreign key column along with its key attribute column forms the primary key of the table.

In our example above, SUBJECTS is the weak entity. Hence, we create a table for it. Its attributes SUBJECT_ID and SUBJECT_NAME forms the column of this table. Although SUBJECT_ID is represented as key attribute in the diagram, it cannot be considered as primary key. In order to add primary key to the column, we have to find the foreign key first. COURSE is the strong entity related to SUBJECT. Hence the primary key COURSE_ID of COURSE is added to SUBJECT table as foreign key. Now we can create a composite primary key out of COURSE_ID and SUBJECT_ID.

• Representing 1:1 relationship

Imagine SUBJECT is not a weak entity, and we have LECTURER teaches SUBJECT relation. It is a 1:1 relation. i.e.; one lecturer teaches only one subject. We can represent this case in two ways

1. Create table for both LECTURER and SUBJECT. Add the primary key of LECTURER in SUBJECT table as foreign key. It implies the lecturer name for that particular subject.
2. Create table for both LECTURER and SUBJECT. Add the primary key of SUBJECT in LECTURER table as foreign key. It implies the subject taught by the lecturer.

In both the case, meaning is same. Foreign key column can be added in either of the table, depending on the developer’s choice. 

• Representing 1:N relationship

Consider SUBJECT and LECTURER relation, where each Lecturer teaches multiple subjects. This is a 1: N relation. In this case, primary key of LECTURER table is added to the SUBJECT table. i.e.; the primary key at 1 cardinality entity is added as foreign key to N cardinality entity

• Representing M:N relationship

Consider the example, multiple students enrolled for multiple courses, which is M:N relation. In this case, we create STUDENT and COURSE tables for the entities. Create one more table for the relation ‘Enrolment’ and name it as STUD_COURSE. Add the primary keys of COURSE and STUDENT into it, which forms the composite primary key of the new table.

That is, in this case both the participating entities are converted into tables, and a new table is created for the relation between them. Primary keys of entity tables are added into new table to form the composite primary key. We can add any additional columns, if present as attribute of the relation in ER diagram.

• Self Referencing 1:N relation

Consider the example of HOD and Lecturers. Here one of the Lecturers is a HOD of the department. i.e.; one HOD has multiple lecturers working with him. In this case, we create LECTURER table for the Lecturer entity. Create the columns and primary keys as usual. In order to represent HOD, we add one more column to LECTURER table which is same column as primary key, but acts as a foreign key. i.e.; LECTURER_ID is the primary key of LECTURER table. We add one more column HOD, which will have LECTURER_ID of the HOD. Hence LECTURER table will show HOD’s Lecturer ID for each Lecturer. In this case, primary key column acts as a foreign key in the same table.

• Self Referencing M:N relation

Consider Student and Teacher example as ‘Manny students have Many Teachers teaching the subjects’. Here relation between Student and Teacher is M:N. In this case, create independent tables for student and teacher, and set their primary keys. Then we create a new table for the relationship ‘have’ as STUDENT_TEACHER, which will have student and teacher combination, and any other columns if applicable. Basically, student-teacher combination is the two primary key columns from respective tables, hence establishing the relationship between them. Both the primary keys from both tables act as a composite primary key in the new table. This reduces the storing of redundant data and consistency in the database.

ER and Object based Data Model

Imagine we have to design database for college. What is the real world entities involved with college? They are college, Students, Lecturer, Course, Subject, Marks etc. Once all the entities are listed, we find out the relationship between them and try to map all of them. Also we list what are the attributes related to each entity like student id, name, lecturer name, course that he is teaching, different subjects, pass mark, grade levels etc. Here we are not bothered about what data value is stored, what is the size of each data etc. We know only entities involved, their attributes and mapping at this stage.

Object based Data Models are based on above concept. It is designed using the entities in the real world, attributes of each entity and their relationship. It picks up each thing/object in the real world which is involved in the requirement.

There are two types of object based data Models – Entity Relationship Model and Object oriented data model. ER data model is one of the important data model which forms the basis for the all the designs in the database world. It defines the mapping between the entities in the database. Object oriented data model, along with the mapping between the entities, describes the state of each entity and the tasks performed by them.

Entity Relationship Data Models

Consider the example above. It maps entities like Student, Lecturer, Subjects, and Marks with each other to form the relation among them. It also list attributes of each objects. ER model represents the all these entities, attributes and their relationship in the form of picture to make the developer understand the system better. A simple ER diagram for above example can be drawn as below. Are you able to understand what are the entities involved, what are its attributes and their relations that we were discussing better here? Yes, it is clean and clear what a STUDENT database look like. It gives the clear understanding of how they are scattered and mapped. If we have missed any entities or attribute or the mapping, we can easily identify here. If we represent it in some tables, it would be difficult to identify this gap.

In the below diagram, Entities or real world objects are represented in a rectangular box. Their attributes are represented in ovals. Primary keys of entities are underlined. All the entities are mapped using diamonds. This is one of the methods of representing ER model. There are many different forms of representation. More details of this model are described in ER data model article.

Basically, ER model is a graphical representation of real world objects with their attributes and relationship. It makes the system easily understandable. This model is considered as a top down approach of designing a requirement.

Advantages

• It makes the requirement simple and easily understandable by representing simple diagrams.
• One can covert ER diagrams into record based data model easily.
• Easy to understand ER diagrams

Disadvantages

• No standard notations are available for ER diagram. There is great flexibility in the notation. It’s all depends upon the designer, how he draws it.
• It is meant for high level designs. We cannot simplify for low level design like coding.

Object Oriented Data Models

This data model is another method of representing real world objects. It considers each object in the world as objects and isolates it from each other. It groups its related functionalities together and allows inheriting its functionality to other related sub-groups.

Let us consider an Employee database to understand this model better. In this database we have different types of employees – Engineer, Accountant, Manager, Clark. But all these employees belong to Person group. Person can have different attributes like name, address, age and phone. What do we do if we want to get a person’s address and phone number? We write two separate procedure sp_getAddress and sp_getPhone.

What about all the employees above? They too have all the attributes what a person has. In addition, they have their EMPLOYEE_ID, EMPLOYEE_TYPE and DEPARTMENT_ID attributes to identify them in the organization and their department. We have to retrieve their department details, and hence we sp_getDeptDetails procedure. Currently, say we need to have only these attributes and functionality.

Since all employees inherit the attributes and functionalities of Person, we can re-use those features in Employee. But do we do that? We group the features of person together into class. Hence a class has all the attributes and functionalities. For example, we would create a person class and it will have name, address, age and phone as its attribute, and sp_getAddress and sp_getPhone as procedures in it. The values for these attributes at any instance of time are object. i.e. ; {John, Troy, 25, 2453545 : sp_getAddress (John), sp_getPhone (John)} forms on person object. {Mathew, Fraser Town, 28, 5645677: sp_getAddress (Mathew), sp_getPhone (Mathew} forms another person object.

Now, we will create another class called Employee which will inherit all the functionalities of Person class. In addition it will have attributes EMPLOYEE_ID, EMPLOYEE_TYPE and DEPARTMENT_ID, and sp_getDeptDetails procedure. Different objects of Employee class are Engineer, Accountant, Manager and Clerk.

Here we can observe that the features of Person are available only if other class is inherited from it. It would be a black box to any other classes. This feature of this model is called encapsulation. It binds the features in one class and hides it from other classes. It is only visible to its objects and any inherited classes.

Advantages

• Because of its inheritance property, we can re-use the attributes and functionalities. It reduces the cost of maintaining the same data multiple times. Also, these informations are encapsulated and, there is no fear being misused by other objects. If we need any new feature we can easily add new class inherited from parent class and adds new features. Hence it reduces the overhead and maintenance costs.
• Because of the above feature, it becomes more flexible in the case of any changes.
• Codes are re-used because of inheritance.
• Since each class binds its attributes and its functionality, it is same as representing the real world object. We can see each object as a real entity. Hence it is more understandable.

Disadvantages

• It is not widely developed and complete to use it in the database systems. Hence it is not accepted by the users.
• It is an approach for solving the requirement. It is not a technology. Hence it fails to put it in the database management systems.

ER MODEL

Before define ER Model, there are some terms which is require to understand ER model properly. In short, ER Model is a diagrammatic representation of entire Database tables. It represents a High Level Database Design.

For Designing the Softwares : This phase is done in softwares by UML (Unified Modelling Language).

For Designing the Database : ER Model is used to design database.

Definitions :

Entity : an object (Record in the table). It a a real world object, such as person, place, thing, event, or concept about which information is recorded. For example, In a banking environment, entities are CUSTOMERS, BANK SUPPLIERS etc. Entities may be –

• Physical – Can be touched(sensed) Example Fan.
• Conceptual – Cannot be touched. Example course offered, brand name etc.

Storing conceptual entities is a big problem.

Entity Types : is defined as a collection of entities that have the same attributes. For example, employee in a company database. As same attributes are shared by the employee entities, but such entity has its own value for each attributes.

Entity Set : Collection of similar entities of same type that share the same attribute at any point in time. For example, a Faculty set may contain all the teachers of a college, Set of all persons, Set of Companies etc. The Entity Set is also called extension of the Entity Type.

Attribute : It is the name of the column. An attribute gives the characteristics of the entity.  It is also called as data element, data field, a field, a data item, or an elementary item.

Some examples of Attributes which clear the concept of attributes –

a) A house can be described by its colour, size and surroundings 
b) A bank account can be described by its bank account number, account 
   type, account balance, account statement etc.
c) A customer of bank may be described by : name, address, customer ID 
   number, phone number.

So, in example a) colour, size and surroundings are attributes of entity 
   "HOUSE".
    In example b) account number, type, balance and statement are 
    attributes of entity "ACCOUNT"
    In example c) name address ID number and phone number are attributes 
    of entity "CUSTOMER"

Data Value : It is the actual data or information contained in each data element(attribute). The data element “Supplier Name” can take value as “Anil Mittal”

Difference between Entity, Attributes, and Data Values –

ENTITY	ATTRIBUTES(OR DATA ELEMENTS)	VALUES
CUSTOMER	Customer ID Number Customer Name Customer Address Customer Phone Number	985678 Ankit Mittal B-176,MC Colony, Bhiwani 57845-69821
ACCOUNT	Account Type Account Number Account Balance Account Statement	Savings 1800221133665587 125000 5000_Credit
SUPPLIER	Supplier Name Supplier Address Supplier Phone Number Supplier Bill Number	Vishal Rastogi H-456, Shiv Nagar, Bhiwani 789546256 B-896541

Difference Between Entity, Entity Type and Entity Sets – 

Entity	Entity Type	Entity Set
Entity is a person, place, thing, event, or even a concept. It may be tangible or intangible.	An entity type defines the collection of entities that have the same attributes.	An entity set is a set of entities of the same type that share the same properties at any point in time (or) It is the collection of entities of an entity type at a point of time.
For Example, Entity is e1 or e2 or e3 or an apple where e1 e2 e3 are employees and an apple is a fruit.	For example, entity type is EMPLOYEE or Fruit	Entity set is a bucket of apple, banana, mango etc. or {e1,e2……}

Example :

Entity Type	Employee
Attributes	Name, Age, Pay
Entity Set (extension)	e1 (Rakesh, 40, 10000) e2 (Arun, 55, 8500) e3 (Sandeep, 30, 14000)

Notations Of above Definitions in ER Diagram –

	Entity Type
	Entity Set
	Attribute

Types of Relationships in ER Diagram

Different types of relationships in er diagram are – 1) Relationships based on degree, 2) Recursive Relationship. Let us discuss these one by one.

Relationship and Relationship Set :  

Relationships connect the entities and represent meaningful dependencies between them.It represents an association among several entities.
Relationships sets is a set of relationships of the same type. It is a mathematical relation on entity sets (n>=2). Relationship set R is a subset of –

{(r1,r2,r3,....rn)| r1∈E1, r2∈E2, rn∈En}

where r1,r2,….rn are called relationships and E1,E2,….En are entity sets.

The way in which two or more entity types are related is called relation type.For example, consider a relationship type WORKS_FOR between the two entity types EMPLOYEE and DEPARTMENT, which associates or links each employee with the department the employee works for.
The WORKS_FOR relation type is shown as –

---

---

In the above figure, each instance of relation type WORKS_FOR i.e.(r1, r2,…,r5) is connected to instances of employee and department entities. Employee e1, e2 and e5 work for department  d2 and employee e3 and e4 work for department d1.

Notation to Represent Relation Type in ER Diagram-

Relation types are represented as diamond shaped boxes.

Degree of a Relationship Type-

The number of participating entity types is known as the degree of relationship type.

Types of Relationship Type Based on Degree –

• Binary Relationship – A relationship type of degree two is called binary relationship. The WORKS_FOR in above figure is a binary relationship as  there are two participating entities-employee and department.
  
• Ternary Relationship- A relationship type of degree three is a ternary relationship for example, in the below figure supply relationship connects three entities SUPPLIER, PART AND PROJECT.
  
  The above diagram can be read as – a supplier supplies the parts to projects
• N-ary Relationship Set – A relationship type of degree n is called n ary relationship . For example

Role Names-

A relationship type has a name which signifies what role a participating entity plays in that relationship instance. The role names helps to explain what the  relationship means.

In the first example WORKS_FOR  relationship type, employee plays the role of worker and department plays the role of employee(because a department consists of a number of employees.

Recursive  Relationship

If the same entity type participate more than once in a  relationship type in different roles then such  relationship types are called recursive  relationship. For example, in the below figure REPORTS_TO is a recursive relationship as the Employee entity type plays two roles – 1) Supervisor and 2) Subordinate.

You can also define the above example of recursive relationship as the relationship between a manager and an employee. An employee is a manager as well as employee.

This is commonly know as a ‘pig’s ear’.

To implement recursive relationship, a foreign key of the employee’s manager number would be held in each employee record.

Emp_entity( Emp_no,Emp_Fname, Emp_Lname, Emp_DOB, Emp_NI_Number, Manager_no);

Manager no - (this is the employee no of the employee's manager)

Another more Complicated Example of Recursive Relationship :

Relationship between a person and their parents :

Relationship Constraints in DBMS –

There are three Types of Relationship Constraints-

1. Structural Constraints
   •  Participation Constraints
   • Cardinality Ratio
2. Overlap Constraints
3. Covering Constraints

Structural Constraints are applicable for binary relationships and
Overlap and Covering Constraints are applicable for EERD(Extended ER Diagrams).

Participation (or) Optionality Constraints-

Participation concerns with the involvement of entities in a relationship. It specifies whether the existence of an entity depends on another entity. There are two types of Participation Constraints –

1. Total/Mandatory Participation
2. Partial/Optional Participation

Notations of Different Types of Participation In ER Diagram –

Total/Mandatory Participation or Existence Dependency-

Participation is said to be total if every entity in E participates in atleast one relationship in R (or) every entity in entity set must depend on another entity. For example, every department will have a startup date as a department was created on that startup date (SINCE ATTRIBUTE of MANAGE relationship). and that department is being handled from that date through a Manager. So, the participation of DEPARTMENT entity in the “MANAGE” relationship type is total.

Total Participation is also known as Existence Dependency.
In ER Diagram, it is represented as a Double Line, connecting the participating entity to the relationship.

Partial/Optional Participation-

Participation is said to be partial if only some entities in E participate in relationships in R (or) some entities in entity set are depend on some another entities in entity set. For example, It is not necessary that all employees manage some department Because all employees may not be the Manager . So the participation of “EMPLOYEE” entity in the “MANAGES” relationship type is partial.

                            (Figure-1)

Cardinality/Mapping Cardinality-

Cardinality expresses the number of entities to which another entity can be associated via a relationship set (or)
It specifies the number of relationship instances that an entity can participate in a relation set.
There are 4 types of Cardinality Ratios :

• One-to-One Cardinality (1:1)
• One-to-Many Cardinality (1:m)
• Many-to-One Cardinality (m:1)
• Many-to-Many Cardinality (m:n)

Notations of Different Types of Cardinality In ER Diagram –

One-to-One Cardinality (1:1) –

An entity in set A is associated with atmost one entity in B, and an entity in B is associated with atmost one entity in A. This type of cardinality is referred as one to one Cardinality.
For example, an Employee as a  Manager manage only one Department  and the vice versa is also true as a department have only one Manager

Create Table MANAGE
( SSN varchar(10),
  DNO varchar(15),
  Primary key(SSN),
  Foreign Key(SSN) .........,
  Foreign Key(DNO) .........,
);

Candidate Keys of "MANAGE" relationship - SSN,DNO

One to Many Cardinality (1:M) –

An entity in A is associated with any number (0 or more) with an entity B, but a entity in B, however can be associated with atmost one entity in A. For example, An employee as a Manager can manage more than one Department.

Create Table MANAGE
( SSN varchar(10),
  DNO varchar(15),
  SINCE date,
  Primary key(DNO),
  Foreign Key(SSN) .........,
  Foreign Key(DNO) .........,
);

Candidate Keys of "MANAGE" relationship - DNO

Many to One Cardinality (M:1) –

An entity in A is associated with atmost one entity in B. An entity in B, however, can be associated with any number (0 or more) of entities in A. For example, An Employee can work only for one Department, But each Department can have 0 or more employees.

Create Table WORKS_ON
( SSN varchar(10),
  DNO varchar(15),
  SINCE date,
  Primary key(SSN),
  Foreign Key(SSN) .........,
  Foreign Key(DNO) .........,
);

Candidate Keys of "WORKS_ON" relationship - SSN

Many to Many Cardinality (M:N) –

An entity in A is associated with any number (0 or more) of entities in B, and an entity in B is associated with any number( 0 or more) of entities in A. For example, An Employee can works on several Projects and a Project may have several Employees.

Create Table WORKS_ON
( SSN varchar(10),
  DNO varchar(15),
  SINCE date,
  Primary key(SSN,DNO),
  Foreign Key(SSN) .........,
  Foreign Key(DNO) .........,
);

Candidate Keys of "WORKS_ON" relationship - SSN PID

Some Points regarding Cardinality and Participation –

1. Minimum Cardinality = 0, for partial participation and equal to 1 for 
   total participation.
2. Maximum Cardinality = n, if one entity occurrence relates to multiple
   entity occurrences.
3. Minimum Cardinality give partial or total participation.
4. Maximum Cardinality give the maximum number of entities that is related
   to.

Relation Between Cardinality and Joins

Cardinality means it is a relation between tables using joins which tells that how many rows of one table will match with rows in other tables when these tables are joined.
In other words Relationships between the tables define Cardinality while explaining how each table links to the other.

Example :

Consider the two tables Consumer(custID) and Bill(BID, FK_custID). Each consumer pays the electricity bill every month. So, a customer will be associated with 12 bills after one year. It means there is one to many relationship between consumer and the bills he paid.  In this case, the cardinality is defined from primary key of the Customer table(PK_Custid) to the foreign key (FK_Custid) of the Bill table.

The number of same rows in both the tables depends upon which join is used.

Complete ER diagram

Let us create a simple ER diagram for a STUDENT database. What is the requirement of this database?

‘Student attends class. Each class is divided into one or more sections. Each class will have its own specified subjects. Students have to attend all the subjects of the class that he attends’.

Now let us identify what are the entities? STUDENT, CLASS, SECTION, SUBJECT are the entities. Attributes of these entities are not specified here. But we know what could be the entities of each of the entities. We can list them as below at this point of time.

STUDENT	CLASS	SECTION	SUBJECT
STUDENT_ID	CLASS_ID	SECTION_ID	SUBJECT_ID
STUDENT_NAME	CLASS_NAME	SECTION_NAME	SUBJECT_NAME
ADDRESS
DOB
AGE
CLASS_ID
SECTION_ID

What are the relationships we have? ‘Attends’, ‘has section’, ‘have subjects’ and ‘studies subjects’ are the relations here. With this knowledge of requirement, we can draw the ER diagram as below.

Observe the diagram carefully. Did we miss or drew it correctly? Are we missing anything on the diagram? Is it inferring correct requirement? What are our observations?

• Age attribute can be derived from DOB. Hence we have to draw dashed oval.
• Address is a composite attribute. We have to draw its sub attributes too. So that we will be very clear about his address details.
• If we see the SECTION entity, by section id, will we be able get the section that student attends? There is no relation mentioned between Student and Section. But Section is mapped only with Class. What do we understand from this? Section is a weak entity. Hence we have to represent it properly.
• If we look at ‘attends’ relationship between STUDENT and CLASS, we can have ‘Joining Date’ and ‘Total Number of Hours’ attributes. But it is an attribute of relation. We have to show them in the diagram.
• Since each class is having different subjects and Students attends those subjects, we can modify the relation ‘studies’ to ‘has’ relation on the relation ‘attends’.

Now the diagram will change to reflect all above points.

Are done with complete diagram? We have not added the cardinality and participation in the diagram.

What are the participation constraints here?

• All the Students attend any one of the class, but class can have only certain group of students. Hence total participation of Students and partial participation of class in ‘Attends’ relation.
• All the class has section and all the section has class. Hence both are total participation.
• All the Students study some of the subjects specific for their class and each class has only some group of subjects. Hence partial participation of both STUDENT and CLASS. Each subject will be studied by some students and it will be part of some class. Hence this also partial participation.

What are the cardinalities of all the relationship?

• Each Student attends only one class at a time. Hence it is a 1: 1 relation.
• Each class has one or more sections. Hence it can be considered as 1: N relation.
• Each student attends many subjects and each class has many subjects. Hence it is a 1:N relation.

Note: If you look at STUDENT and CLASS relationship as many Students attend one class, then it would be an M: 1 relation. It is all up to the developer, how he looks at the requirement.

Now it is a complete ER diagram for simple Student database.