Database Management Systems

^4Th SEMESTER

UNIT-1 CIC-210

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

1

Unit 1 Notes� Learning Objective

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

2

• Introduction and applications of DBMS.
• Data Independence.
• Database System Architecture and their Levels.
• Mapping.
• Database Users and DBA.
• E-R Model.
• Design Issues.
• E-R Diagram.
• Extended E-R Features.

• Data- Facts that can be recorded in terms of Text, videos, images , numbers and audios.
• Or we can say Multimedia Data.
• Example- Flipkart and Online shopping.
• Database – It is a Collection of related data that represent some real world entities.
• Example – University.
• Information- Meaningful / processed data.
• EXAMPLE – YouTube.

​

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

3

Introduction….

• Problem:
• Redundancy and Data Inconsistency
• DBMS – User is interacting with dbms and the database.

It is a software tool that manage the data.

DB+ DBMS= Database System

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

4

Need or Purpose

1. Storage
2. Retrieval

RDBMS and DBMS

In case of RDBMS data is to be stored in the form of Tables

In case of DMBS there data is stored in hierarchal form.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

5

•  A database is a collection of information that is organized so that it can be easily accessed, managed and updated. Data is organized into rows, columns and tables, and it is indexed to make it easier to find relevant information.
•  A database is an organized collection of data. It is the collection of schemas, tables, queries, reports, views, and other objects.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

6

•  A database management system (DBMS) is system software for creating and managing databases. The DBMS provides users and programmers with a systematic way to create, retrieve, update and manage data.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

7

�

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

8

Database Applications

 A database application is a computer program whose primary purpose is entering and retrieving information from a computerized database. Early examples of database applications were accounting systems and airline reservations systems.���

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

9

�

• Applications are given as follows:
•  Telecom
•  Industry
•  Education sector
•  Online shopping
•  Banking
•  Airlines
•  Manufacturing and selling
•  Human resources

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

10

�

• Data that is well organized and integrated is very useful
• in decision making.
• Thus, we can infer some of the following uses of DBMS:
•  Effective and efficient management of data
•  Query processing and management
•  Easy to understand and user friendly
•  Security and integrity of data
•  Better Decision making
•  Data sharing and storage
•  Better access to accurate data
•  Ensures error free information

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

11

DATA INDEPENDENCE IN DBMS

• The main purpose of the three levels of data abstraction is to achieve data independence.
• As the database changes and expands over time, it is very important that the changes in one level should not affect the data at other levels of the database.
• This would save time and cost required while changing the database.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

12

Data Independence�

• Data independence refers to the property of DBMS through which we can modify the schema definition at any level without changing the schema definition at any higher level.

​

• A database has three levels of abstraction as shown in the diagram

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

13

Diagram:

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

14

• We have two levels of data independence that are defined on the basis of these three levels of abstraction.
• Physical Data Independence
• Logical Data Independence

​

• Physical Data Independence

​

• Physical Data Independence refers to the characteristic of changing the physical level without affecting the logical level or conceptual level. Using this property we can easily change the storage device of the database without affecting the logical schema.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

15

Example:

​

• Suppose you want to replace the storage device form hard disk to SSD or magnetic tape then it should not affect the data stored at the logical level.
• The changes in the physical level may include changes like:
• Using a new storage device like SSD, magnetic tape, hard disk, etc.
• Using a new data structure for storage.
• Using a different data access method or using an alternative file organization technique.
• Changing the location(like changing the drive) of the database.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

16

Logical DI

​

• It refers to the characteristics of changing the logical level without affecting the external or view level.
• This also helps in separating the logical level from the view level. If we do any changes in the logical level then the user view of the data remains unaffected.
• The changes in the logical level are required whenever there is a change in the logical structure of the database.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

17

• The changes in the logical level may include:

​

1. Changing the data definition.
2. Adding, deleting, or updating any new attribute, entity or relationship in the database.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

18

DBMS Architecture

• Database management systems architecture will help us understand the components of database system and the relation among them.
• The architecture of DBMS depends on the computer system on which it runs.
• For example, in a client-server DBMS architecture, the database systems at server machine can run several requests made by client machine.
• We will understand this communication with the help of diagrams.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

19

DBMS Architecture�

​

• There are three types of DBMS architecture:
• 1. Single tier architecture�2. Two tier architecture�3. Three tier architecture

​

• 1. Single tier architecture
• In this type of architecture, the database is readily available on the client machine, any request made by client doesn’t require a network connection to perform the action on the database.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

20

Continued….

• For example, lets say you want to fetch the records of employee from the database and the database is available on your computer system, so the request to fetch employee details will be done by your computer and the records will be fetched from the database by your computer as well.

​

• This type of system is generally referred as local database system.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

21

2. Two tier architecture�

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

22

• In two-tier architecture, the Database system is present at the server machine and the DBMS application is present at the client machine, these two machines are connected with each other through a reliable network .
• Whenever client machine makes a request to access the database present at server using a query language like sql, the server perform the request on the database and returns the result back to the client.
• The application connection interface such as JDBC, ODBC are used for the interaction between server and client.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

23

3. Three tier architecture�

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

24

Continued…

• In three-tier architecture, another layer is present between the client machine and server machine.
• In this architecture, the client application doesn’t communicate directly with the database systems present at the server machine, rather the client application communicates with server application and the server application internally communicates with the database system present at the server.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

25

DBMS – Three level Architecture

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

26

• This architecture has three levels:�1. External level�2. Conceptual level�3. Internal level

​

• 1. External level
• It is also called view level.
• The reason this level is called “view” is because several users can view their desired data from this level which is internally fetched from database with the help of conceptual and internal level mapping.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

27

• The user doesn’t need to know the database schema details such as data structure, table definition etc. user is only concerned about data which is what returned back to the view level after it has been fetched from database (present at the internal level).
• External level is the “top level” of the Three Level DBMS Architecture.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

28

2. Conceptual level�

• It is also called logical level. The whole design of the database such as relationship among data, schema of data etc. are described in this level.
• Database constraints and security are also implemented in this level of architecture. This level is maintained by DBA (database administrator).

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

29

3. Internal level�

• This level is also known as physical level.
• This level describes how the data is actually stored in the storage devices.
• This level is also responsible for allocating space to the data. This is the lowest level of the architecture.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

30

View of Data in DBMS�

• Abstraction is one of the main features of database systems.
• Hiding irrelevant details from user and providing abstract view of data to users, helps in easy and efficient user-database interaction. In the previous topic, we discussed the three level of DBMS architecture
• The top level of that architecture is “view level”. The view level provides the “view of data” to the users and hides the irrelevant details such as data relationship, database schema, constraints, security etc. from the user.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

31

• To fully understand the view of data, you must have a basic knowledge of data abstraction and instance & schema.
• Data abstraction
• Instance and schema

​

• Data Abstraction in DBMS
• Database systems are made-up of complex data structures.
• To ease the user interaction with database, the developers hide internal irrelevant details from users. This process of hiding irrelevant details from user is called data abstraction.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

32

Diagram Representation…..

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

33

�We have three levels of abstraction:

• �Physical level: This is the lowest level of data abstraction.
• It describes how data is actually stored in database. You can get the complex data structure details at this level.
• Logical level: This is the middle level of 3-level data abstraction architecture. It describes what data is stored in database.

​

• View level: Highest level of data abstraction. This level describes the user interaction with database system.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

34

Example:

• Let’s say we are storing customer information in a customer table. At physical level these records can be described as blocks of storage (bytes, gigabytes, terabytes etc.) in memory. These details are often hidden from the programmers.

​

• At the logical level these records can be described as fields and attributes along with their data types, their relationship among each other can be logically implemented.
• The programmers generally work at this level because they are aware of such things about database systems.

​

• At view level, user just interact with system with the help of GUI and enter the details at the screen, they are not aware of how the data is stored and what data is stored; such details are hidden from them.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

35

DBMS Schema

• Definition of schema: Design of a database is called the schema. 
• Schema is of three types: Physical schema, logical schema and view schema.
• For example: In the coming diagram, we have a schema that shows the relationship between three tables: Course, Student and Section.
• The diagram only shows the design of the database, it doesn’t show the data present in those tables. Schema is only a structural view(design) of a database as shown in the diagram in the next slide…

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

36

Diagram:

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

37

• The design of a database at physical level is called physical schema, how the data stored in blocks of storage is described at this level.
• Design of database at logical level is called logical schema, programmers and database administrators work at this level, at this level data can be described as certain types of data records gets stored in data structures, however the internal details such as implementation of data structure is hidden at this level (available at physical level).
• Design of database at view level is called view schema. This generally describes end user interaction with database systems.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

38

DBMS Instance�

• Definition of instance: The data stored in database at a particular moment of time is called instance of database. 
• Database schema defines the variable declarations in tables that belong to a particular database; the value of these variables at a moment of time is called the instance of that database.
• For example, lets say we have a single table student in the database, today the table has 100 records, so today the instance of the database has 100 records.
• Lets say we are going to add another 100 records in this table by tomorrow so the instance of database tomorrow will have 200 records in table.
• In short, at a particular moment the data stored in database is called the instance, that changes over time when we add or delete data from the database.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

39

Mapping:

• Process of transforming request and results between three level it's called mapping.
• There are the two types of mappings:
• Conceptual/Internal Mapping
• External/Conceptual Mapping

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

40

Conceptual/Internal Mapping:

​

• The conceptual/internal mapping defines the correspondence between the conceptual view and the store database.
• It specifies how conceptual record and fields are represented at the internal level.
• It relates conceptual schema with internal schema.
• If structure of the store database is changed.
• If changed is made to the storage structure definition-then the conceptual/internal mapping must be changed accordingly, so that the conceptual schema can remain invariant.
• There could be one mapping between conceptual and internal levels.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

41

External/Conceptual Mapping:

​

• The external/conceptual mapping defines the correspondence between a particular external view and conceptual view.
• It relates each external schema with conceptual schema.
• The differences that can exist between these two levels are analogous to those that can exist between the conceptual view and the stored database.
• Example: fields can have different data types; fields and record name can be changed; several conceptual fields can be combined into a single external field.
• Any number of external views can exist at the same time; any number of users can share a given external view: different external views can overlap.
• There could be several mapping between external and conceptual levels.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

42

Database users and DBA

​

• Database users are the ones who really use and take the benefits of the database. There will be different types of users depending on their needs and way of accessing the database.
• There are two types of database users, Users, and Administrators.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

43

• Application Programmers – They are the developers who interact with the database by means of DML queries.
• These DML queries are written in the application programs like C, C++, JAVA, Pascal, etc. These queries are converted into object code to communicate with the database.
• For example, writing a C program to generate the report of employees who are working in a particular department will involve a query to fetch the data from the database.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

44

• Sophisticated Users – They are database developers, who write SQL queries to select/insert/delete/update data. They do not use any application or programs to request the database. They directly interact with the database by means of a query language like SQL.

​

• These users will be scientists, engineers, analysts who thoroughly study SQL and DBMS to apply the concepts in their requirements. In short, we can say this category includes designers and developers of DBMS and SQL.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

45

• Specialized Users – These are also sophisticated users, but they write special database application programs. They are the developers who develop the complex programs to the requirement.
• Stand-alone Users – These users will have a stand-alone database for their personal use. These kinds of the database will have readymade database packages which will have menus and graphical interfaces.
• Native Users – these are the users who use the existing application to interact with the database. For example, online library system, ticket booking systems, ATMs etc which has existing application and users use them to interact with the database to fulfill their requests.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

46

Database Administrator

​

• The life cycle of a database starts from designing, implementing to the administration of it.
• A database for any kind of requirement needs to be designed perfectly so that it should work without any issues.
• Once all the design is complete, it needs to be installed. Once this step is complete, users start using the database. The database grows as the data grows in the database.
• When the database becomes huge, its performance comes down. Also accessing the data from the database becomes a challenge.
• There will be unused memory in the database, making the memory inevitably huge. This administration and maintenance of the database are taken care of by the database Administrator – DBA.
• �A DBA has many responsibilities. A good-performing database is in the hands of DBA.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

47

• Installing and upgrading the DBMS Servers: – DBA is responsible for installing a new DBMS server for the new projects. He is also responsible for upgrading these servers as there are new versions that come into the market or requirement.
• If there is any failure in the up-gradation of the existing servers, he should be able to revert the new changes back to the older version, thus maintaining the DBMS working.
• He is also responsible for updating the service packs/ hotfixes/ patches to the DBMS servers.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

48

• Design and implementation: – Designing the database and implementing is also DBA’s responsibility. He should be able to decide on proper memory management, file organizations, error handling, log maintenance, etc for the database.
• Performance tuning: – Since the database is huge and it will have lots of tables, data, constraints, and indices, there will be variations in the performance from time to time.
• Also, because of some designing issues or data growth, the database will not work as expected. It is the responsibility of the DBA to tune the database performance. He is responsible to make sure all the queries and programs work in a fraction of seconds.
• Migrate database servers: – Sometimes, users using oracle would like to shift to SQL server. It is the responsibility of DBA to make sure that migration happens without any failure, and there is no data loss.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

49

• Backup and Recovery: – Proper backup and recovery programs needs to be developed by DBA and has to be maintained him. This is one of the main responsibilities of DBA. Data/objects should be backed up regularly so that if there is any crash, it should be recovered without much effort and data loss.
• Security: – DBA is responsible for creating various database users and roles, and giving them different levels of access rights.
• Documentation: – DBA should be properly documenting all his activities so that if he quits or any new DBA comes in, he should be able to understand the database without any effort.
• He should basically maintain all his installation, backup, recovery, security methods.
• He should keep various reports about database performance.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

50

Types of DBA

• There are different kinds of DBA depending on the responsibility that he owns.
• Administrative DBA – This DBA is mainly concerned with installing, and maintaining DBMS servers. His prime tasks are installing, backups, recovery, security, replications, memory management, configurations, and tuning. He is mainly responsible for all administrative tasks of a database.
• Development DBA – He is responsible for creating queries and procedures for the requirement. Basically, his task is similar to any database developer.
• Database Architect – Database architect is responsible for creating and maintaining the users, roles, access rights, tables, views, constraints, and indexes. He is mainly responsible for designing the structure of the database depending on the requirement. These structures will be used by developers and development DBA to code.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

51

• Data Warehouse DBA –DBA should be able to maintain the data and procedures from various sources in the data warehouse. These sources can be files, COBOL, or any other programs. Here data and programs will be from different sources. A good DBA should be able to keep the performance and function levels from these sources at the same pace to make the data warehouse work.
• Application DBA –He acts like a bridge between the application program and the database. He makes sure all the application program is optimized to interact with the database. He ensures all the activities from installing, upgrading, and patching, maintaining, backup, recovery to executing the records work without any issues.
• OLAP DBA – He is responsible for installing and maintaining the database in OLAP systems. He maintains only OLAP databases.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

52

Entity Relationship Model

• An Entity–relationship model (ER model) describes the structure of a database with the help of a diagram, which is known as Entity Relationship Diagram (ER Diagram).
• An ER model is a design or blueprint of a database that can later be implemented as a database. The main components of E-R model are: entity set and relationship set.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

53

ER Diagram

• An ER diagram shows the relationship among entity sets.
• An entity set is a group of similar entities and these entities can have attributes.
• In terms of DBMS, an entity is a table or attribute of a table in database, so by showing relationship among tables and their attributes, ER diagram shows the complete logical structure of a database.
• Lets have a look at a simple ER diagram to understand this concept.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

54

ER Diagram

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

55

Explanation:

• In the previous diagram we have two entities Student and College and their relationship.
• The relationship between Student and College is many to one as a college can have many students however a student cannot study in multiple colleges at the same time.
• Student entity has attributes such as Stu_Id, Stu_Name & Stu_Addr and College entity has attributes such as Col_ID & Col_Name.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

56

• Here are the geometric shapes and their meaning in an E-R Diagram.
• Rectangle: Represents Entity sets.�Ellipses: Attributes�Diamonds: Relationship Set�Lines: They link attributes to Entity Sets and Entity sets to Relationship Set�Double Ellipses: Multivalued Attributes�Dashed Ellipses: Derived Attributes�Double Rectangles: Weak Entity Sets�Double Lines: Total participation of an entity in a relationship set

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

57

����� Components of a ER Diagram�

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

58

• As shown in the previous diagram, an ER diagram has three main components:�1. Entity�2. Attribute�3. Relationship

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

59

1 Entity�

• An entity is an object or component of data.
• An entity is represented as rectangle in an ER diagram.�For example: In the following ER diagram we have two entities
• Student and College and these two entities have many to one relationship as many students study in a single college.

​

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

60

Weak Entity

• An entity that cannot be uniquely identified by its own attributes and relies on the relationship with other entity is called weak entity.
• The weak entity is represented by a double rectangle. For example – a bank account cannot be uniquely identified without knowing the bank to which the account belongs, so bank account is a weak entity.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

61

2. Attribute

• An attribute describes the property of an entity. An attribute is represented as Oval in an ER diagram. There are four types of attributes:
• 1. Key attribute�2. Composite attribute�3. Multivalued attribute�4. Derived attribute

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

62

Key Attribute

• A key attribute can uniquely identify an entity from an entity set.
• For example, student roll number can uniquely identify a student from a set of students.
• Key attribute is represented by oval same as other attributes however the text of key attribute is underlined.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

63

Composite Attribute

• An attribute that is a combination of other attributes is known as composite attribute.
• For example, In student entity, the student address is a composite attribute as an address is composed of other attributes such as pin code, state, country.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

64

Multivalued Attribute

• An attribute that can hold multiple values is known as multivalued attribute. It is represented with double ovals in an ER Diagram.
• For example – A person can have more than one phone numbers so the phone number attribute is multivalued.
• �

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

65

Derived Attribute

• A derived attribute is one whose value is dynamic and derived from another attribute.
• It is represented by dashed oval in an ER Diagram. For example – Person age is a derived attribute as it changes over time and can be derived from another attribute (Date of birth).

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

66

Relationship

• A relationship is represented by diamond shape in ER diagram, it shows the relationship among entities.
• There are four types of relationships:�1. One to One�2. One to Many�3. Many to One�4. Many to Many

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

67

One to One Relationship�

• When a single instance of an entity is associated with a single instance of another entity then it is called one to one relationship.
• For example, a person has only one passport and a passport is given to one person.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

68

One to Many Relationship�

• When a single instance of an entity is associated with more than one instances of another entity then it is called one to many relationship.
• For example – a customer can place many orders but a order cannot be placed by many customers.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

69

Many to One Relationship�

• When more than one instances of an entity is associated with a single instance of another entity then it is called many to one relationship.
• For example – many students can study in a single college but a student cannot study in many colleges at the same time.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

70

Many to Many Relationship�

• When more than one instances of an entity is associated with more than one instances of another entity then it is called many to many relationship.
• For example, a can be assigned to many projects and a project can be assigned to many students.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

71

Key Concept in Dbms

• Keys play an important role in the relational database.
• It is used to uniquely identify any record or row of data from the table.
• It is also used to establish and identify relationships between tables.
• For example: In Student table, ID is used as a key because it is unique for each student. In PERSON table, passport_number, license_number, SSN are keys since they are unique for each person.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

72

Diagram

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

73

Types of Key

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

74

Primary Key

​

• It is the first key which is used to identify one and only one instance of an entity uniquely. An entity can contain multiple keys as we saw in PERSON table. The key which is most suitable from those lists become a primary key.
• In the EMPLOYEE table, ID can be primary key since it is unique for each employee. In the EMPLOYEE table, we can even select License_Number and Passport_Number as primary key since they are also unique.
• For each entity, selection of the primary key is based on requirement and developers.
• �

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

75

Diagram

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

76

Candidate Key

• A candidate key is an attribute or set of an attribute which can uniquely identify a tuple.
• The remaining attributes except for primary key are considered as a candidate key. The candidate keys are as strong as the primary key.
• For example: In the EMPLOYEE table, id is best suited for the primary key. Rest of the attributes like SSN, Passport_Number, and License_Number, etc. are considered as a candidate key.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

77

Diagram

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

78

Super Key

• Super key is a set of an attribute which can uniquely identify a tuple. Super key is a superset of a candidate key.
• For example: In the next EMPLOYEE table, for(EMPLOEE_ID, EMPLOYEE_NAME) the name of two employees can be the same, but their EMPLYEE_ID can't be the same.
• Hence, this combination can also be a key.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

79

Foreign Key

• Foreign keys are the column of the table which is used to point to the primary key of another table.
• In a company, every employee works in a specific department, and employee and department are two different entities. So we can't store the information of the department in the employee table. That's why we link these two tables through the primary key of one table.
• We add the primary key of the DEPARTMENT table, Department_Id as a new attribute in the EMPLOYEE table.
• Now in the EMPLOYEE table, Department_Id is the foreign key, and both the tables are related.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

80

Diagram

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

81

DBMS languages�

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

82

Data Definition Language (DDL)�

• DDL is used for specifying the database schema. It is used for creating tables, schema, indexes, constraints etc. in database. Lets see the operations that we can perform on database using DDL:
• To create the database instance – CREATE
• To alter the structure of database – ALTER
• To drop database instances – DROP
• To delete tables in a database instance – TRUNCATE
• To rename database instances – RENAME
• To drop objects from database such as tables – DROP
• To Comment – Comment
• All of these commands either defines or update the database schema that’s why they come under Data Definition language.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

83

Data Manipulation Language (DML)�

• DML is used for accessing and manipulating data in a database. The following operations on database comes under DML:

​

• To read records from table(s) – SELECT
• To insert record(s) into the table(s) – INSERT
• Update the data in table(s) – UPDATE
• Delete all the records from the table – DELETE

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

84

Data Control language (DCL)�

• DCL is used for granting and revoking user access on a database –

​

• To grant access to user – GRANT
• To revoke access from user – REVOKE
• In practical data definition language, data manipulation language and data control languages are not separate language, rather they are the parts of a single database language such as SQL.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

85

Transaction Control Language(TCL)�

• The changes in the database that we made using DML commands are either performed or rollbacked using TCL.
• To persist the changes made by DML commands in database – COMMIT
• To rollback the changes made to the database – ROLLBACK

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

86

Data models in DBMS�

• There are several types of data models in DBMS.
• Basic overview of types of models.

​

• Object based logical Models – Describe data at the conceptual and view levels.

​

• 1.E-R Model
• 2.Object oriented Model

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

87

Data Model :

• Record based logical Models – Like Object based model, they also describe data at the conceptual and view levels.
• These models specify logical structure of database with records, fields and attributes.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

88

Relational model in DBMS�

• In relational model, the data and relationships are represented by collection of inter-related tables.
• Each table is a group of column and rows, where column represents attribute of an entity and rows represents records.

​

• Sample relationship Model: Student table with 3 columns and four records.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

89

Table: Student

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

90

Table: Course

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

91

Explanation:

• Here Stu_Id, Stu_Name & Stu_Age are attributes of table Student and Stu_Id, Course_Id & Course_Name are attributes of table Course.

​

• The rows with values are the records (commonly known as tuples).

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

92

Hierarchical model in DBMS�

• In hierarchical model, data is organized into a tree like structure with each record is having one parent record and many children.

​

• The main drawback of this model is that, it can have only one to many relationships between nodes.

​

​

• Sample Hierarchical Model Diagram:
• Lets say we have few students and few courses and a course can be assigned to a single student only, however a student take any number of courses so this relationship becomes one to many.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

93

Diagram:

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

94

• Example of hierarchical data represented as relational tables: The previous hierarchical model can be represented as relational tables like this:

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

95

Course Table:

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

96

Constraints in DBMS�

• Constraints enforce limits to the data or type of data that can be inserted/updated/deleted from a table.
• The whole purpose of constraints is to maintain the data integrity during an update/delete/insert into a table.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

97

Types of constraints�

• NOT NULL
• UNIQUE
• DEFAULT
• CHECK
• Key Constraints – PRIMARY KEY, FOREIGN KEY
• Domain constraints
• Mapping constraints

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

98

NOT NULL

• �NOT NULL constraint makes sure that a column does not hold NULL value. When we don’t provide value for a particular column while inserting a record into a table, it takes NULL value by default. By specifying NULL constraint, we can be sure that a particular column(s) cannot have NULL values
• Example:

​

• CREATE TABLE STUDENT(
• ROLL_NO INT NOT NULL,
• STU_NAME VARCHAR (35) NOT NULL,
• STU_AGE INT NOT NULL,
• STU_ADDRESS VARCHAR (235),
• PRIMARY KEY (ROLL_NO)
• );

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

99

UNIQUE:�

• UNIQUE Constraint enforces a column or set of columns to have unique values. If a column has a unique constraint, it means that particular column cannot have duplicate values in a table.

​

• CREATE TABLE STUDENT(
• ROLL_NO INT NOT NULL,
• STU_NAME VARCHAR (35) NOT NULL UNIQUE,
• STU_AGE INT NOT NULL,
• STU_ADDRESS VARCHAR (35) UNIQUE,
• PRIMARY KEY (ROLL_NO)
• );

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

100

DEFAULT:

• The DEFAULT constraint provides a default value to a column when there is no value provided while inserting a record into a table.

​

• CREATE TABLE STUDENT(
• ROLL_NO INT NOT NULL,
• STU_NAME VARCHAR (35) NOT NULL,
• STU_AGE INT NOT NULL,
• EXAM_FEE INT DEFAULT 10000,
• STU_ADDRESS VARCHAR (35) ,
• PRIMARY KEY (ROLL_NO)
• );

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

101

CHECK:�

• This constraint is used for specifying range of values for a particular column of a table. When this constraint is being set on a column, it ensures that the specified column must have the value falling in the specified range.

​

• CREATE TABLE STUDENT(
• ROLL_NO INT NOT NULL CHECK(ROLL_NO >1000) ,
• STU_NAME VARCHAR (35) NOT NULL,
• STU_AGE INT NOT NULL,
• EXAM_FEE INT DEFAULT 10000,
• STU_ADDRESS VARCHAR (35) ,
• PRIMARY KEY (ROLL_NO)
• );

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

102

• In the above example we have set the check constraint on ROLL_NO column of STUDENT table. Now, the ROLL_NO field must have the value greater than 1000.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

103

Key constraints:�

• PRIMARY KEY:
• Primary key uniquely identifies each record in a table. It must have unique values and cannot contain nulls. In the below example the ROLL_NO field is marked as primary key, that means the ROLL_NO field cannot have duplicate and null values.

​

• CREATE TABLE STUDENT(
• ROLL_NO INT NOT NULL,
• STU_NAME VARCHAR (35) NOT NULL UNIQUE,
• STU_AGE INT NOT NULL,
• STU_ADDRESS VARCHAR (35) UNIQUE,
• PRIMARY KEY (ROLL_NO)
• );

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

104

Design Issues in DBMS

Here are some of the issues that can occur while ER diagram design process:

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

105

1. Choosing Entity Set vs Attributes�

• Here we will discuss how choosing an entity set vs an attribute can change the whole ER design semantics.
• To understand this lets take an example, let’s say we have an entity set Student with attributes such as student-name and student-id. Now we can say that the student-id itself can be an entity with the attributes like student-class and student-section.

​

• Now if we compare the two cases we discussed above, in the first case we can say that the student can have only one student id, however in the second case when we chose student id as an entity it implied that a student can have more than one student id.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

106

2. Choosing Entity Set vs. Relationship Sets

• It is hard to decide that an object can be best represented by an entity set or relationship set.
• To comprehend and decide the perfect choice between these two (entity vs relationship), the user needs to understand whether the entity would need a new relationship if a requirement arise in future, if this is the case then it is better to choose entity set rather than relationship set.

​

• Let’s take an example to understand it better: A person takes a loan from a bank, here we have two entities person and bank and their relationship is loan.
• This is fine until there is a need to disburse a joint loan, in such case a new relationship needs to be created to define the relationship between the two individuals who have taken joint loan. In this scenario, it is better to choose loan as an entity set rather than a relationship set.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

107

3. Choosing Binary vs n-ary Relationship Sets

• In most cases, the relationships described in an ER diagrams are binary. The n-ary relationships are those where entity sets are more than two, if the entity sets are only two, their relationship can be termed as binary relationship.
• lets take an example to understand how we can convert an n-ary relationship to multiple binary relationships.

​

• Now lets say we have to describe a relationship between four family members: father, mother, son and daughter. This can easily be represented in forms of multiple binary relationships, father-mother relationship as “spouse”, son and daughter relationship as “siblings” and father and mother relationship with their child as “child”.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

108

4. Placing Relationship Attributes�

• The cardinality ratio in DBMS can help us determine in which scenarios we need to place relationship attributes. It is recommended to represent the attributes of one to one or one to many relationship sets with any participating entity sets rather than a relationship set.

​

• For example, if an entity cannot be determined as a separate entity rather it is represented by the combination of participating entity sets. In such case it is better to associate these entities to many-to-many relationship sets.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

109

DBMS – ER Diagram to Table Conversion

• First we will convert simple ER diagrams to tables.
• In the end, we will take a complex ER diagram and then we will convert it into set of tables.
• 1. Strong Entity set with Simple attributes
• The Strong Entity set becomes the table and the attributes of the Entity set becomes the table attributes. The key attribute of the entity set becomes the primary key of the table.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

110

• Let’s take an example: Here we have an entity set Employee with the attributes Name, Age, Emp_Id and Salary.
• When we convert this ER diagram to table, the entity set becomes table so we have a table named “Employee” as shown in the following diagram.

​

• The attributes of the entity set becomes the attributes of the table.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

111

Diagram:

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

112

2. Strong Entity Set With Composite Attributes�

• Now we will see how to convert Strong entity set with composite attributes ER to table.
• The conversion is fairly simple in this case as well. The entity set will be the table and the simple attributes of the composite attributes will become the attributes of the table while the composite attribute itself will be ignored during conversion.

​

• Let’s take an example. As you can see we have a composite attribute Name and this composite attribute has two simple attributes First_N and Last_N. While converting this ER to table we have not used the composite attribute itself in the table instead we have used the simple attributes of this composite attribute as table’s attributes.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

113

Diagram:

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

114

3. Strong Entity Set With Multi Valued Attributes

• Entity set with multi-valued attributes will require two tables in the relational model.

​

• We will understand this conversion with the help of a diagram.
• Let’s take the same example that we have seen above, here we have added a new multi-valued attribute Dept. An employee can work in multiple department so we have this Dept attribute marked as multi-valued. Whenever we have a multi-valued attribute, there needs to be more than one table to represent the ER diagram. As you can see we have created two tables to represent this ER.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

115

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

116

4. Relationship Set to Table conversion

• While converting the relationship set to a table, the primary attributes of the two entity sets becomes the table attributes and if the relationship set has any attribute that also becomes the attribute of the table.

​

• In the following example, we have two entity sets Employee and Department. These entity sets are associated to each other using the Works relationship set. To convert this relationship set Works to the table, we take the primary attributes of each entity set, these are Emp_Id and Dept_Id and all the attributes of the relationship set and form a table.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

117

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

118

DBMS Generalization

• Generalization is a process in which the common attributes of more than one entities form a new entity.
• This newly formed entity is called generalized entity.
• Generalization Example
• Lets say we have two entities Student and Teacher.
• Attributes of Entity Student are: Name, Address & Grade
• Attributes of Entity Teacher are: Name, Address & Salary

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

119

The ER diagram before generalization looks like this:

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

120

• These two entities have two common attributes: Name and Address, we can make a generalized entity with these common attributes. Lets have a look at the ER model after generalization.

​

​

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

121

The ER diagram after generalization:

• We have created a new generalized entity Person and this entity has the common attributes of both the entities.
• As you can see in the following ER diagram that after the generalization process the entities Student and Teacher only has the specialized attributes Grade and Salary respectively and their common attributes (Name & Address) are now associated with a new entity Person which is in the relationship with both the entities (Student & Teacher).

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

122

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

123

• Note:
• 1. Generalization uses bottom-up approach where two or more lower level entities combine together to form a higher level new entity.
• 2. The new generalized entity can further combine together with lower level entity to create a further higher level generalized entity.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

124

DBMS Specialization�

• Specialization is a process in which an entity is divided into sub-entities. You can think of it as a reverse process of generalization, in generalization two entities combine together to form a new higher level entity. Specialization is a top-down process.

​

• The idea behind Specialization is to find the subsets of entities that have few distinguish attributes. For example – Consider an entity employee which can be further classified as sub-entities Technician, Engineer & Accountant because these sub entities have some distinguish attributes.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

125

Specialization Example

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

126

• In the previous diagram, we can see that we have a higher level entity “Employee” which we have divided in sub entities “Technician”, “Engineer” & “Accountant”.
• All of these are just an employee of a company, however their role is completely different and they have few different attributes. Just for the example, I have shown that Technician handles service requests, Engineer works on a project and Accountant handles the credit & debit details.
• All of these three employee types have few attributes common such as name & salary which we had left associated with the parent entity “Employee” as shown in the above diagram.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

127

DBMS Aggregation

• Aggregation is a process in which a single entity alone is not able to make sense in a relationship so the relationship of two entities acts as one entity.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

128

Aggregation Example

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

129

• In real world, we know that a manager not only manages the employee working under them but he has to manage the project as well.
• In such scenario if entity “Manager” makes a “manages” relationship with either “Employee” or “Project” entity alone then it will not make any sense because he has to manage both.
• In these cases the relationship of two entities acts as one entity. In our example, the relationship “Works-On” between “Employee” & “Project” acts as one entity that has a relationship “Manages” with the entity “Manager”.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

130

E-R Model into Relational Model

• First step of any relational database design is to make ER Diagram for it and then convert it into relational Model.
• What is relational model ?
• Relational Model represents how data is stored in database in the form of table.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

131

1.Entity Set:�

• Consider we have entity STUDENT in ER diagram with attributes Roll Number, Student Name and Class.

​

• To convert this entity set into relational schema
• 1.Entity is mapped as relation in Relational schema
• 2.Attributes of Entity set are mapped as attributes for that Relation.
• 3.Key attribute of Entity becomes Primary key for that Relation.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

132

Diagram:

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

133

2.Entity set with multi valued attribute:

• Consider we have entity set Employee with attributes Employee ID, Name and Contact number.
• Here contact number is multivalued attribute as it has multiple values. as an employee can have more than one contact number for that we have to repeat all attributes for every new contact number. This will lead to data redundancy in table.
• Hence to convert entity with multivalued attribute into relational schema
• separate relation is created for multivalued attribute in which
• 1.Key attribute and multivalued attribute of entity set becomes primary key of relation.
• 2.Separate relation employee is created with remaining attributes.
• Due to this instead of repeating all attributes of entity now only one attribute is need to repeat.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

134

Diagram:

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

135

3.Entity set with Composite attribute:

• Consider entity set student with attributes Roll Number, Student Name and Class.

​

• here student name is composite attribute as it has further divided into First name, last name.
• In this case to convert entity into relational schema,
• composite attribute student name should not be include in relation but all parts of composite attribute are mapped as simple attributes for relation.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

136

Diagram:

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

137

4. 1:M (one to many) Relationship:

• Consider 1:M relationship set enrolled exist between entity sets student and course as follow,

​

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

138

Explanation:

• Attributes of entity set student are Roll no which is primary key, student name and class
• Attributes of entity set course are Course code which is primary key, Course name and duration
• And date of enroll is attribute of relationship set enroll.
• Here Enroll is 1:M relationship exist between entity set student and course which means that one student can enroll in multiple courses
• In this case to convert this relationship into relational schema,
• 1.Separate relation is created for all participating entity sets (student and course)
• 2.Key attribute of Many’s side entity set (course) is mapped as foreign key in one’s side relation(Student)
• 3.All attributes of relationship set are mapped as attributes for relation of one’s side entity set (student)

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

139

Diagram:

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

140

5. M:1 (many to one) Relationship:

• Consider same relationship set enroll exist between entity sets student and course .
• but here student is many side entity set while course is one side entity set. Which means many student can enroll in one course.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

141

Explanation:

• To convert this relationship set into relational schema,
• 1.Separate relation is created for all participating entity sets.
• 2.Key attribute of Many’s side entity set student is mapped as foreign key in one’s side relation
• 3.All attributes of relationship set are mapped as attributes for one’s side relation course.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

142

M:N (many to many) Relationship:

• Consider same relationship set enrolled exist between entity sets student and course ,which means multiple student can enroll in multiple courses.

​

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

143

Explanation:

• To convert this Relationship set into relational schema,

​

• Relationship set is mapped as separate relation
• Key attributes of participating entity sets are mapped as primary key for that relation
• Attribute of relationship set becomes simple attributes for that relation
• And separate relation is created for other participating entities

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

144

Diagram:

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

145

7. 1:1 (one to one) Relationship:

• Consider same relationship set enroll exist between entity sets student and course ,which means one student can enroll in only one courses

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

146

• To convert this Relationship set into relational schema,

​

• Separate relation is created for all participating entity sets.
• Primary Key of Relation Student can be act as foreign key for relation Course OR Primary Key of Relation Course act as foreign key for relation Student.

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

147

Diagram:

Department of  Computer Science and Engineering , BVCOE New Delhi.  Subject: DBMS , Instructor: Mrs. Nitasha Rathore

148