Unit - 1

Introduction

1.1 Purpose of Database System

It is a set of tools that allows a user to construct and manage databases. In other words, it is general-purpose software that enables users to define, construct, and manipulate databases for a variety of applications.

Database systems are made to handle massive amounts of data. Data management entails both the creation of structures for storing data and the provision of methods for manipulating data. Furthermore, despite system crashes or efforts at illegal access, the database system must preserve the security of the information stored.

If data is to be shared across multiple users, the system must avoid any unexpected outcomes.

Fig 1: process of transforming data

The figure above depicts the process of transforming data into information, knowledge, and action in a database management system.

The file system was used as the foundation for the database applications.

The goal of a database management system (DBMS) is to transform the following:

1. Data into information.

2. Information into knowledge.

3. Knowledge to the action.

Uses of DBMS

The main uses of DBMS are as follows −

          Data independence and efficient access of data.

          Application Development time reduces.

          Security and data integrity.

          Uniform data administration.

          Concurrent access and recovery from crashes.

Characteristics of DBMS

●       It stores and manages information in a digital repository hosted on a server.

●       It can provide a logical and transparent picture of the data manipulation process.

●       Automatic backup and recovery mechanisms are included in the DBMS.

●       It has ACID qualities, which keep data healthy in the event of a failure.

●       It has the ability to simplify complex data relationships.

●       It's utilized to help with data manipulation and processing.

●       It is used to ensure data security.

●       It can examine the database from several perspectives depending on the user's needs.

1.2 Views of data

View of data in DBMS narrate how the data is visualized at each level of data abstraction? Data abstraction allow developers to keep complex data structures away from the users. The developers achieve this by hiding the complex data structures through levels of abstraction.

There is one more feature that should be kept in mind i.e. the data independence. While changing the data schema at one level of the database must not modify the data schema at the next level. In this section, we will discuss the view of data in DBMS with data abstraction, data independence, data schema in detail.

Data Abstraction

Data abstraction is hiding the complex data structure in order to simplify the user’s interface of the system. It is done because many of the users interacting with the database system are not that much computer trained to understand the complex data structures of the database system.

To achieve data abstraction, we will discuss a Three-Schema architecture which abstracts the database at three levels discussed below:

Three-Schema Architecture:

The main objective of this architecture is to have an effective separation between the user interface and the physical database. So, the user never has to be concerned regarding the internal storage of the database and it has a simplified interaction with the database system.

The three-schema architecture defines the view of data at three levels:

1. Physical level (internal level)
2. Logical level (conceptual level)
3. View level (external level)

1. Physical Level/ Internal Level

The physical or the internal level schema describes how the data is stored in the hardware. It also describes how the data can be accessed. The physical level shows the data abstraction at the lowest level and it has complex data structures. Only the database administrator operates at this level.

2. Logical Level/ Conceptual Level

It is a level above the physical level. Here, the data is stored in the form of the entity set, entities, their data types, the relationship among the entity sets, user operations performed to retrieve or modify the data and certain constraints on the data. Well adding constraints to the view of data adds the security. As users are restricted to access some particular parts of the database.

It is the developer and database administrator who operates at the logical or the conceptual level.

3. View Level/ User level/ External level 

It is the highest level of data abstraction and exhibits only a part of the whole database. It exhibits the data in which the user is interested. The view level can describe many views of the same data. Here, the user retrieves the information using different application from the database.

The figure below describes the three-schema architecture of the database:

Fig 2 - Three-schema architecture

In the figure above you can clearly distinguish between the three levels of abstraction. To understand it more clearly let us take an example:

We have to create a database of a college. Now, what entity sets would be involved? Student, Lecturer, Department, Course and so on…

Now, the entity sets Student, Lecturer, Department, Course will be stored in the storage as the consecutive blocks of the memory location. This is the physical or internal level and is hidden from the programmers but the database administrator is it aware of it.

At the logical level, the programmers define the entity sets and relationship among these entity sets using a programming language like SQL. So, the programmers work at the logical level and even the database administrator also operates at this level.

At the view level, the users have the set of applications which they use to retrieve the data they are interested in.

Data Independence

Data independence defines the extent to which the data schema can be changed at one level without modifying the data schema at the next level. Data independence can be classified as shown below:

Logical Data Independence:

Logical data independence describes the degree up to which the logical or conceptual schema can be changed without modifying the external schema. Now, a question arises what is the need to change the data schema at a logical or conceptual level?

Well, the changes to data schema at the logical level are made either to enlarge or reduce the database by adding or deleting more entities, entity sets, or changing the constraints on data.

Physical Data Independence:

Physical data independence defines the extent up to which the data schema can be changed at the physical or internal level without modifying the data schema at logical and view level.

Well, the physical schema is changed if we add additional storage to the system or we reorganize some files to enhance the retrieval speed of the records.

Instances and Schemas

What is an instance?

We can define an instance as the information stored in the database at a particular point of time. Let us discuss it with the help of an example.

As we discussed above the database comprises of several entity sets and the relationship between them. Now, the data in the database keeps on changing with time. As we keep inserting or deleting the data to and from the database.

Now, at a particular time if we retrieve any information from the database then that corresponds to an instance.

What is schema?

Whenever we talk about the database the developers have to deal with the definition of database and the data in the database.

The definition of a database comprises of the description of what data it would contain what would be the relationship between the data. This definition is the database schema.

Key Takeaway

          View of data in DBMS describes the abstraction of data at three-level i.e. physical level, logical level, view level.

          The physical level of abstraction defines how data is stored in the storage and also reveals its access path.

          Abstraction at the logical level describes what data would be stored in the database? what would be the relation between the data? and the constraints applied to the data.

          The view level or external level of abstraction describes the application which the users use to retrieve the information from the database.

          Data independence explains the extent to which data at a certain level can be modified without disturbing the data next higher levels.

          An instance is the retrieval of information from the database at a certain point of time. An instance in a database keeps on changing with time.

          Schema is the overall design of the entire database. Schema of the database is not changed frequently.

1.3 Data models

Data Model is the modelling of the data description, data semantics, and consistency constraints of the data. It provides the conceptual tools for describing the design of a database at each level of data abstraction. Therefore, there are following four data models used for understanding the structure of the database:

Fig 3 – Data Models

1) Relational Data Model: This type of model designs the data in the form of rows and columns within a table. Thus, a relational model uses tables for representing data and in-between relationships. Tables are also called relations. This model was initially described by Edgar F. Codd, in 1969. The relational data model is the widely used model which is primarily used by commercial data processing applications.

2) Entity-Relationship Data Model: An ER model is the logical representation of data as objects and relationships among them. These objects are known as entities, and relationship is an association among these entities. This model was designed by Peter Chen and published in 1976 papers. It was widely used in database designing. A set of attributes describe the entities. For example, student_name, student_id describes the 'student' entity. A set of the same type of entities is known as an 'Entity set', and the set of the same type of relationships is known as 'relationship set'.

3) Object-based Data Model: An extension of the ER model with notions of functions, encapsulation, and object identity, as well. This model supports a rich type system that includes structured and collection types. Thus, in 1980s, various database systems following the object-oriented approach were developed. Here, the objects are nothing but the data carrying its properties.

4) Semistructured Data Model: This type of data model is different from the other three data models (explained above). The semistructured data model allows the data specifications at places where the individual data items of the same type may have different attributes sets. The Extensible Markup Language, also known as XML, is widely used for representing the semistructured data. Although XML was initially designed for including the markup information to the text document, it gains importance because of its application in the exchange of data.

Key takeaway

Data Model is the modelling of the data description, data semantics, and consistency constraints of the data. It provides the conceptual tools for describing the design of a database at each level of data abstraction.

1.4 Database management system

Database management system is software that is used to manage the database.

What is Database?

The database is a collection of inter-related data which is used to retrieve, insert and delete the data efficiently. It is also used to organize the data in the form of a table, schema, views, and reports, etc.

For example: The college Database organizes the data about the admin, staff, students and faculty etc.

Using the database, you can easily retrieve, insert, and delete the information.

Database Management System

         Database management system is a software which is used to manage the database. For example: MySQL, Oracle, etc are a very popular commercial database which is used in different applications.

         DBMS provides an interface to perform various operations like database creation, storing data in it, updating data, creating a table in the database and a lot more.

         It provides protection and security to the database. In the case of multiple users, it also maintains data consistency.

DBMS allows users the following tasks:

         Data Definition: It is used for creation, modification, and removal of definition that defines the organization of data in the database.

         Data Updation: It is used for the insertion, modification, and deletion of the actual data in the database.

         Data Retrieval: It is used to retrieve the data from the database which can be used by applications for various purposes.

         User Administration: It is used for registering and monitoring users, maintain data integrity, enforcing data security, dealing with concurrency control, monitoring performance and recovering information corrupted by unexpected failure.

Characteristics of DBMS

         It uses a digital repository established on a server to store and manage the information.

         It can provide a clear and logical view of the process that manipulates data.

         DBMS contains automatic backup and recovery procedures.

         It contains ACID properties which maintain data in a healthy state in case of failure.

         It can reduce the complex relationship between data.

         It is used to support manipulation and processing of data.

         It is used to provide security of data.

         It can view the database from different viewpoints according to the requirements of the user.

Advantages of DBMS

         Controls database redundancy: It can control data redundancy because it stores all the data in one single database file and that recorded data is placed in the database.

         Data sharing: In DBMS, the authorized users of an organization can share the data among multiple users.

         Easily Maintenance: It can be easily maintainable due to the centralized nature of the database system.

         Reduce time: It reduces development time and maintenance need.

         Backup: It provides backup and recovery subsystems which create automatic backup of data from hardware and software failures and restores the data if required.

         multiple user interface: It provides different types of user interfaces like graphical user interfaces, application program interfaces

Disadvantages of DBMS

         Cost of Hardware and Software: It requires a high speed of data processor and large memory size to run DBMS software.

         Size: It occupies a large space of disks and large memory to run them efficiently.

         Complexity: Database system creates additional complexity and requirements.

         Higher impact of failure: Failure is highly impacted the database because in most of the organization, all the data stored in a single database and if the database is damaged due to electric failure or database corruption then the data may be lost forever.

Key takeaway

Database management system is software that is used to manage the database.

The database is a collection of inter-related data which is used to retrieve, insert and delete the data efficiently. It is also used to organize the data in the form of a table, schema, views, and reports, etc.

For example: The college Database organizes the data about the admin, staff, students and faculty etc.

Using the database, you can easily retrieve, insert, and delete the information.

1.5 Three-schema architecture of DBMS

The architecture of a database management system influences its design. When dealing with a large number of PCs, web servers, database servers, and other networked elements, the same client/server architecture is used.

A client/server architecture is made up of several PCs and a workstation that are all linked by a network.

The design of a database management system is determined by how users link to the database in order to complete their requests.

Fig 4: types of architecture

A single tier or multi-tier database architecture can be seen. However, database design can be divided into two categories: 2-tier architecture and 3-tier architecture.

1-tier Architecture:

The database is directly accessible to the user in this architecture. It means that the user can sit on the DBMS and use it directly. Any modifications made here will be applied directly to the database. It does not provide end users with a useful tool.

For the creation of local applications, a 1-tier architecture is used, in which programmers can interact directly with the database for fast responses.

Fig 5: 1-tier architecture

2-tier Architecture:

The 2-tier architecture is similar to the basic client-server architecture. Client-side applications can interact directly with the database on the server side in a two-tier architecture. APIs such as ODBC and JDBC are used for this interaction.

The client-side runs the user interfaces and application programs. The server side is in charge of providing features such as query processing and transaction management. The client-side application creates a link with the server side in order to communicate with the DBMS.

Fig 6: 2-tier architecture

3-tier Architecture:

Between the client and the server is another layer in the 3-tier architecture. The client cannot communicate directly with the server in this architecture. The client-side program communicates with an application server, which in turn communicates with the database system.

Beyond the application server, the end user is unaware of the database's presence. Aside from the submission, the database has no knowledge about any other users.

In the case of a wide web application, the 3-tier architecture is used.

Fig 7: 3-tier architecture

1.6 Components of DBMS

You may split the database management system into five main components, which are

1. Hardware
2. Software
3. Data
4. Procedure
5. Database Access Language

In order to see how they all work together to form a database management system, let's have a basic diagram.

Fig 8: component of DBMS

1. Hardware

When we say hardware, before any data is successfully stored in the memory, we mean computers, hard discs, I/O channels for data, and any other physical part involved.

When we run Oracle or MySQL on our personal computer, the hard disc of our computer, the keyboard we use to type all the commands, the RAM of our computer, the ROM, all become part of the hardware of the DBMS.

2.     Software

As this is the software that governs everything, this is the key part. More like a wrapper around the physical database, the DBMS programme gives us an easy-to-use interface for storing, accessing and upgrading data.

The DBMS programme is able to understand and translate the Database Access Language into actual database commands in order to execute them on the DB.

3.     Data

Data is the resource for which the DBMS is intended. The motive behind the creation of DBMS was to store and use information.

Data saved by the user is present in a standard database and meta data is stored.

Metadata is data about knowledge. To better understand the data contained in it, this is information stored by the DBMS.

4.     Procedure

Procedures refer to general instructions for using a method to handle a database. This includes procedures for setting up and installing a DBMS, logging in and logging out of the DBMS programme, database maintenance, backups, report creation, etc.

5.     Database Access language

Database Access Language is a basic language designed to access, attach, update and delete data stored in any database by writing commands.

In the Database Access Language, a user can write commands and send them to the DBMS for execution, which is then interpreted and executed by the DBMS.

The user can use the access language to build new databases, tables, insert data, fetch stored data, update data and remove data.

Key takeaway:

1. Hardware means computers, hard discs, I/O channels for data, and any other physical part involved.

2. Software that governs everything, this is the key part.

3. Data is the resource for which the DBMS is intended.

4. Procedures refer to general instructions for using a method to handle a database.

5. In the Database Access Language, a user can write commands and send them to the DBMS.

1.7 E/R Model - Conceptual data modelling

●      The Entity-Relationship model (ER model) is a type of entity-relationship model. It's a data model at a high level. The data elements and relationships for a given system are described using this model.

●      It creates a database's conceptual architecture. It also creates a very convenient and easy-to-design data view.

●       The database structure is depicted as a diagram called an entity-relationship diagram in ER modeling.

Let's say we're creating a database for a school. The student will be an object in this database, with attributes such as address, name, id, age, and so on. There will be a relationship between the address and another entity with attributes such as city, street name, pin code, and so on.

Fig 9: Example of ER

Component of ER model -

The following components are composed of an ER Diagram:

1. Entity
2. Attributes
3. Relationships

Fig 10: Component of ER model

1.Entity

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

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

Fig 11: Entity

Entity type:

A list or a set of entities having certain common attributes is an entity type. In a database, a name and a list of attributes define each type of entity.

Entity set:

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

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

2.Attribute

In an Entity-Relationship Model, an attribute defines an entity's properties or characteristics. It is represented in the ER diagram by an oval or ellipse shape. Each oval shape represents one attribute and is directly linked to the person that is in the shape of the rectangle.

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

Fig 12: Different attributes of employee

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

1.Simple attribute:

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

Fig 13: Simple attribute

2. Key attribute:

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

Fig 14: Key attributes

3. Composite attribute:

An attribute that is a combination of two or more basic attributes is called a composite attribute. It is defined by an ellipse in an Entity-Relationship diagram, and that ellipse consists of other ellipses. For example, an employee entity type's name attribute consists of first name, second name, and last name.

Fig 15: Composite attribute

4.Derived attribute:

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

Fig 16: Derived attribute

5.Multivalued attributes:

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

Fig 17: Multivalued attribute

3.Relationship

In the Entity-Relation Model, a relationship is used to define the relationship between two or more entities. In the ER diagram, it is illustrated by a diamond shape.

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

Degree of Relationship

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

Degree of relationship can be categorized into the following types:

1. Unary Relationship:

A relationship in which a single group of individuals is involved is referred to as a unary relationship. For instance, in a company, an employee manages or supervises another employee.

Fig 18: Unary relationship

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

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

Fig 19: Ternary relationship

4. N-nary Relationship:

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

Key takeaway:

1. The object, location, person, or event that stores data in the database may be an entity.
2. In an Entity-Relationship Model, an attribute defines an entity's properties or characteristics.
3. Attributes are represented in the ER diagram by an oval or ellipse shape.
4. In the Entity-Relation Model, a relationship is used to define the relationship between two or more entities.

1.8 Motivation

The primary goal of a conceptual model is to communicate the fundamental principles and basic functions of the system it depicts. A conceptual model must also be designed in such a way that the model's users can understand the system interpretation. When effectively implemented, a conceptual model should achieve four main goals.

1. Improve a person's knowledge of the representative system.

2. Facilitate the effective exchange of system information among stakeholders.

3. Assist system designers in extracting system requirements by providing a point of reference.

4. Provide a way for cooperation and document the system for future reference.

It is common to use the term conceptual model. It could refer to either a "conceptual model" or a "conceptual model." It is possible to distinguish between what models are and what models are built of. Most models are conceptions, with the exception of notable models such as a scale model of Winchester Cathedral. However, they are primarily intended to be representations of real-world situations. A model's worth is usually related to how well it matches a past, present, future, actual, or potential state of affairs. A model of an idea is distinct since it does not need to conform to the real world to be a good model.

1.9 Entities

Entity in DBMS can be a real-world object with an existence, For example, in a College database, the entities can be Professor, Students, Courses, etc.

Entities has attributes, which can be considered as properties describing it, for example, for Professor entity, the attributes are  Professor_Name, Professor_Address, Professor_Salary, etc. The attribute value gets stored in the database.

Example of Entity in DBMS

Let us see an example −

<Professor>

Here, Professor_Name, Professor _Address and Professor _Salary are attributes.
Professor_ID is the primary key

1.10 Entity types

The following are the types of entities in DBMS −

Strong Entity

The strong entity has a primary key. Weak entities are dependent on strong entity. Its existence is not dependent on any other entity.

Strong Entity is represented by a single rectangle −

Continuing our previous example, Professor is a strong entity here, and the primary key is Professor_ID.

Weak Entity

The weak entity in DBMS do not have a primary key and are dependent on the parent entity. It mainly depends on other entities.

Weak Entity is represented by double rectangle −

Continuing our previous example, Professor is a strong entity, and the primary key is  Professor_ID. However, another entity is Professor_Dependents, which is our Weak Entity.

<Professor_Dependents>

This is a weak entity since its existence is dependent on another entity Professor, which we saw above. A Professor has Dependents.

Example of Strong and Weak Entity

The example of strong and weak entity can be understood by the below figure. 

Fig 20 - Example

The Strong Entity is Professor, whereas Dependent is a Weak Entity. 

ID is the primary key (represented with a line) and Name in Dependent entity is called Partial Key (represented with a dotted line).

Key takeaway

Entity in DBMS can be a real-world object with an existence, For example, in a College database, the entities can be Professor, Students, Courses, etc.

Entities has attributes, which can be considered as properties describing it, for example, for Professor entity, the attributes are  Professor_Name, Professor_Address, Professor_Salary, etc. The attribute value gets stored in the database.

1.10 Attributes relationships

         Attributes are the descriptive properties which are owned by each entity of an Entity set.

         There exist a specific domain or set of values for each attribute from where the attribute can take its values.

Types of Attributes-

In ER diagram, attributes associated with an entity set may be of the following types-

Fig 21 – Types of Attributes

1. Simple attributes
2. Composite attributes
3. Single valued attributes
4. Multi valued attributes
5. Derived attributes
6. Key attributes

1. Simple Attributes-

Simple attributes are those attributes which can not be divided further.

Example-

Here, all the attributes are simple attributes as they can not be divided further.

2. Composite Attributes-

Composite attributes are those attributes which are composed of many other simple attributes.

Example-

Here, the attributes “Name” and “Address” are composite attributes as they are composed of many other simple attributes.

3. Single Valued Attributes-

Single valued attributes are those attributes which can take only one value for a given entity from an entity set.

Example-

Here, all the attributes are single valued attributes as they can take only one specific value for each entity.

4. Multi Valued Attributes-

Multi valued attributes are those attributes which can take more than one value for a given entity from an entity set.

Example-

Here, the attributes “Mob_no” and “Email_id” are multi valued attributes as they can take more than one values for a given entity.

5. Derived Attributes-

Derived attributes are those attributes which can be derived from other attribute(s).

Example-

Here, the attribute “Age” is a derived attribute as it can be derived from the attribute “DOB”. 

6. Key Attributes-

Key attributes are those attributes which can identify an entity uniquely in an entity set.

Example-

Here, the attribute “Roll_no” is a key attribute as it can identify any student uniquely.

Key takeaway

         Attributes are the descriptive properties which are owned by each entity of an Entity set.

         There exist a specific domain or set of values for each attribute from where the attribute can take its values.

1.11 Relationship types

A relationship is any connection between two types of entities. The relationship involves entities. A diamond shape is used to represent it.

A teacher, for example, instructs students. "Teaches" is a relationship in this case, and it refers to the relationship between a Teacher and a Student entity.

'Customer' (Customer id, Name, City, Phone) and 'Account' (Account no, Type, Balance) are the two entity types we have. We keep the 'Customer' data in one table and his account information in the 'Account' table. To connect these two tables, we must first put the 'Customer id' primary key from the 'Customer' table into the 'Account' table. This key relates to a column with the same name in the 'Customer' database and serves as a foreign key for the 'Account' table. This is how a connection between two tables is made. There are three different types of relationships that two entities might have:

●      One-to-One Relationship

●      One-to-Many or Many-to-One Relationship

●      Many-to-Many Relationship

One -to-One Relationship

When each record in one table is linked to only one record in the other table, this is known as a one-to-one relationship.

If there are two entities named ‘Person' (Id, Name, Age, Address) and ‘Passport' (Passport id, Passport no), for example. As a result, each individual can only have one passport, and each passport can only belong to one person.

It's not very usual to have a connection like this. This type of interaction is, nonetheless, used for security reasons. We can easily save the passport id in the ‘Person' database in the preceding example. However, we create a separate database for the ‘Passport' because the passport number is potentially sensitive information that should be hidden from some users. As a result, by creating a separate table, we can add an extra layer of protection by limiting access to just particular database users.

One-to-Many or Many-to-One Relationship

When each entry in one table may be linked to one or more records in the other table, this is known as a cross-table relationship. This is the most common type of relationship found. Depending on how we look at it, a one-to-many relationship can also be described as a many-to-one relationship.

If there are two entity types, for example, ‘Customer' and ‘Account,' each ‘Customer' can have many ‘Accounts,' but each ‘Account' can only be owned by one ‘Customer.' In this case, we can say that each Customer is linked to a number of accounts. As a result, the relationship is one-to-many. However, if we look at it from the other perspective, where multiple accounts are linked to a single customer, we can call it a many-to-one relationship.

Many-to-Many Relationship

When each record of the first table can be associated to one or more records of the second table, and a single record of the second table may be related to one or more records of the first table, such a relationship exists. A many-to-many relationship is formed by two one-to-many relationships that are connected by a 'linking table' or 'associate table.' By having fields that are the primary keys of the other two tables, the bridging table connects two tables. The following example will help us comprehend this.

For example, if the entity types ‘Customer' and ‘Product' exist, each customer can purchase several products, and a product can be purchased by multiple customers.

To better understand the linking table concept, consider the ‘Order' entity as a linking table that connects the ‘Customer' and ‘Product' entities. This many-to-many relationship can be broken down into two one-to-many partnerships. To begin with, each ‘Customer' can have many ‘Orders,' whereas each ‘Order' is only associated with one ‘Customer.' Second, each 'Order' is associated with only one Product, despite the fact that several orders for the same Product may exist.

The notion of linking in the previous example can be understood by considering all of the attributes of the entities 'Customer,' 'Order,' and 'Product.' The main keys of both the 'Customer' and 'Product' entities are included in the connecting table, i.e. the 'Order' table, as can be seen. When referring to the respective table from the 'Order' table, these keys operate as foreign keys.

Key takeaway

• A relationship is any connection between two types of entities. The relationship involves entities. A diamond shape is used to represent it.
• When each record in one table is linked to only one record in the other table, this is known as a one-to-one relationship.
• When each entry in one table may be linked to one or more records in the other table, this is known as a cross-table relationship. This is the most common type of relationship found.
• A many-to-many relationship is formed by two one-to-many relationships that are connected by a 'linking table' or 'associate table.' By having fields that are the primary keys of the other two tables, the bridging table connects two tables.

1.12 E/R diagram notation

Database can be represented using the notations. In ER diagram, many notations are used to express the cardinality. These notations are as follows:
 

Fig 24 : Notations of ER diagram

Key takeaway

Database can be represented using the notations. In ER diagram, many notations are used to express the cardinality.

1.13 Examples

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

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

The ER diagram is given below:

Fig 25: ER diagram

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

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

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

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

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

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

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

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

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

Fig 26: table structure

Key takeaway

1. Each entity set or relationship set can be represented in tabular form in the database.
2. Using notations, the database can be represented, and these notations can be reduced to a set of tables.

References :

1. Korth, Silbertz, Sudarshan,” Database Concepts”, McGraw Hill
2. Date C J, “An Introduction to Database Systems”, Addision Wesley
3. Elmasri, Navathe, “Fundamentals of Database Systems”, Addision Wesley
4. O’Neil, Databases, Elsevier Pub.