Data abstraction:-

Data Abstraction is a process of hiding unwanted or irrelevant details from the end
user. It provides a different view and helps in achieving data independence which
is used to enhance the security of data. The database systems consist of
complicated data structures and relations. For users to access the data easily, these
complications are kept hidden, and only the relevant part of the database is made
accessible to the users through data abstraction.
Database systems include complex data-structures. In terms of retrieval of data,
reduce complexity in terms of usability of users and in order to make the system
efficient, developers use levels of abstraction that hide irrelevant details from the
users. Levels of abstraction simplify database design.
Mainly there are three levels of abstraction for DBMS, which are as follows −
 Physical or Internal Level
 Logical or Conceptual Level
 View or External Level

Physical or Internal Level

It is the lowest level of abstraction for DBMS which defines how the data is
actually stored, it defines data-structures to store data and access methods
used by the database. Actually, it is decided by developers or database
application programmers how to store the data in the database. So, overall,
the entire database is described in this level that is physical or internal level.
It is a very complex level to understand. For example, customer's
information is stored in tables and data is stored in the form of blocks of
storage such as bytes, gigabytes etc.

Logical or Conceptual Level

Logical level is the intermediate level or next higher level. It describes what
data is stored in the database and what relationship exists among those data.
It tries to describe the entire or whole data because it describes what tables
to be created and what are the links among those tables that are created .It is
less complex than the physical level. Logical level is used by developers or
database administrators (DBA). So, overall, the logical level contains tables
(fields and attributes) and relationships among table attributes.

View or External Level

It is the highest level. In view level, there are different levels of views and
every view only defines a part of the entire data. It also simplifies interaction
with the user and it provides many views or multiple views of the same
database. View level can be used by all users (all levels' users). This level is
the least complex and easy to understand. For example, a user can interact
with a system using GUI that is view level and can enter details at GUI or
screen and the user does not know how data is stored and what data is
stored, this detail is hidden from the user.

Data Independence:-

Data independence can be explained using the three-schema architecture.

Data independence refers characteristic of being able to modify the schema
at one level of the database system without altering the schema at the next
higher level.
There are two types of data independence:

1. Logical Data Independence

  Logical data independence refers characteristic of being able to
change the conceptual schema without having to change the external
schema.
 Logical data independence is used to separate the external level from
the conceptual view.
 If we do any changes in the conceptual view of the data, then the user
view of the data would not be affected.
 Logical data independence occurs at the user interface level.

2. Physical Data Independence

 Physical data independence can be defined as the capacity to

change the internal schema without having to change the conceptual
schema.
 If we do any changes in the storage size of the database system
server, then the Conceptual structure of the database will not be
affected.
 Physical data independence is used to separate conceptual levels
from the internal levels.
 Physical data independence occurs at the logical interface level.
Data Definition Language(DDL):-
Data definition language (DDL) is a language that allows the user to define the
data and their relationship to other types of data. Data Definition language
statements work with the structure of the database table. Various data types used in
defining columns in a database table Integrity and value constraints Viewing,
modifying and removing a table structure
DDL Commands
The Data Definition Languages (DDL) Commands are as follows −
Create − It is used to create a new table or a new database.
Alter − It is used to alter or change the structure of the database table.
Drop − It is used to delete a table, index, or views from the database.
Truncate − It is used to delete the records or data from the table, but its structure
remains as it is.
Rename − It is used to rename an object from the database.
Data Manipulation Language(DML)
A data manipulation language (DML) is a family of computer languages including
commands permitting users to manipulate data in a database. This manipulation
involves inserting data into database tables, retrieving existing data, deleting data
from existing tables and modifying existing data. DML is mostly incorporated in
SQL databases.
DML performs the following tasks:
 Insert new data into the database.
 Delete existing data from the database.
 Retrieve data contained in the database.
 Modify data stored in the database.

DML can be of two types

 Procedural DML
  Non-procedural DML
Procedural DML:-procedural DML allows user to describe ‘what’ data is
needed and ‘how’ to get it. The user expresses all the data access operation
that are needed to obtain the required information. Procedural DML are
embedded in a high-level language that contains features that encourage
integration and handle navigation logic.
Non-procedural :– Non-procedural DML is a language that describes what
does needed without specifying how to get it. The DBMS translates the
DML statements into a procedure that manipulates required records.

ER model
 ER model stands for an Entity-Relationship model. It is a high-level data
model. This model is used to define the data elements and relationship for a
specified system.
 It develops a conceptual design for the database. It also develops a very
simple and easy to design view of data.
 In ER modeling, the database structure is portrayed as a diagram called an
entity-relationship diagram.
1. Entity:

An entity may be any object, class, person or place. In the ER diagram, an entity
can be represented as rectangles.

Consider an organization as an example- manager, product, employee, department

etc. can be taken as an entity.

Weak Entity
An entity that depends on another entity called a weak entity. The weak entity
doesn't contain any key attribute of its own. The weak entity is represented by a
double rectangle.

2. Attribute

The attribute is used to describe the property of an entity. Eclipse is used to

represent an attribute.

a. Key Attribute

The key attribute is used to represent the main characteristics of an entity. It

represents a primary key. The key attribute is represented by an ellipse with the
text underlined.

b. Composite Attribute
An attribute that composed of many other attributes is known as a composite
attribute. The composite attribute is represented by an ellipse, and those ellipses
are connected with an ellipse.

c. Multivalued Attribute

An attribute can have more than one value. These attributes are known as a
multivalued attribute. The double oval is used to represent multivalued attribute.

For example, a student can have more than one phone number.
d. Derived Attribute

An attribute that can be derived from another attribute is known as a derived

attribute. It can be represented by a dashed ellipse.

For example, A person's age changes over time and can be derived from another
attribute like Date of birth.

Relationship

A relationship is used to describe the relation between entities. Diamond or

rhombus is used to represent the relationship.

Types of relationship are as follows:

a. One-to-One Relationship

When only one instance of an entity is associated with the relationship, then it is
known as one-to-one relationship.

For example, A female can marry to one male, and a male can marry to one
female.

b. One-to-many relationship
When only one instance of the entity on the left, and more than one instance of an
entity on the right associates with the relationship then this is known as a one-to-
many relationship.

For example, Scientist can invent many inventions, but the invention is done by
the only specific scientist.

c. Many-to-one relationship

When more than one instance of the entity on the left, and only one instance of an
entity on the right associates with the relationship then it is known as a many-to-
one relationship.

For example, Student enrolls for only one course, but a course can have many
students.

d. Many-to-many relationship

When more than one instance of the entity on the left, and more than one instance
of an entity on the right associates with the relationship then it is known as a many-
to-many relationship.

For example, Employee can assign by many projects and project can have many
employees.
Network model
The network model was created to represent complex data relationships more
effectively when compared to hierarchical models, to improve database
performance and standards. It has entities which are organized in a graphical
representation and some entities are accessed through several paths. A User
perceives the network model as a collection of records in 1:M relationships. Given
below is the pictorial representation of the network model in DBMS −
Features
The features of a Network Model are as follows −

Ability to Merge Relationships − In this model, because of more

relationships the data is more related. It has an ability to manage one-to-one
relationships as well as many-to-many relationships.
Many paths − There can be more than one path to the same record because
of more relationships. It makes data access fast and simple.
Circular Linked List − The operations in this model are done with the help
of the circular linked list. The current position is maintained with the help of
a program and navigates through the records based on relationships.
Advantages
The advantages of network model are as follows:
Network models represent complex data relationships better than the
hierarchical models.
It handles so many relationship types.
Data access is more flexible than hierarchical models.
Improved database performance.
 It includes Data Definition Language (DDL) and Data Manipulation
Language (DML) commands.
Disadvantages
The disadvantages of network model are as follows:
Database contains a complex array of pointers.
System complexity limits efficiency.
Structural changes require changes in all application programs.
Navigation systems yield complex implementation and management.
Keep heavy pressure on programmers due to the complex structure.
Any change like updating, deletion, insertion is very complex.

Relational and object-oriented Data models

An Object relational model is a combination of a Object-oriented database model
and a Relational database model. So, it supports objects, classes, inheritance etc.
just like Object Oriented models and has support for data types, tabular structures
etc. like Relational data model. One of the major goals of Object relational data
model is to close the gap between relational databases and the object-oriented
practices frequently used in many programming languages such as C++, C#, Java
etc.
History of Object Relational Data Model
Both Relational data models and Object-oriented data models are very useful. But
it was felt that they both were lacking in some characteristics and so work was
started to build a model that was a combination of them both. Hence, Object
relational data model was created as a result of research that was carried out in the
1990’s.

Advantages of Object Relational model

The advantages of the Object Relational model are −
Inheritance
The Object Relational data model allows its users to inherit objects, tables etc. so
that they can extend their functionality. Inherited objects contain new attributes as
well as the attributes that were inherited.
Complex Data Types
Complex data types can be formed using existing data types. This is useful in
Object relational data model as complex data types allow better manipulation of
the data.
Extensibility
The functionality of the system can be extended in Object relational data model.
This can be achieved using complex data types as well as advanced concepts of
object-oriented model such as inheritance.
Disadvantages of Object Relational model
The object relational data model can get quite complicated and difficult to handle
at times as it is a combination of the Object-oriented data model and Relational
data model and utilizes the functionalities of both of them.
Integrity Constraints
 Integrity constraints are a set of rules. It is used to maintain the quality of
information.
 Integrity constraints ensure that the data insertion, updating, and other
processes have to be performed in such a way that data integrity is not
affected.
 Thus, integrity constraint is used to guard against accidental damage to the
database.

Types of Integrity Constraints

1. Domain constraints

 Domain constraints can be defined as the definition of a valid set of values

for an attribute.
 The data type of domain includes string, character, integer, time, date,
currency, etc. The value of the attribute must be available in the
corresponding domain.

Example:

2. Entity integrity constraints

 The entity integrity constraint states that primary key value can't be null.
 This is because the primary key value is used to identify individual rows in
relation and if the primary key has a null value, then we can't identify those
rows.
 A table can contain a null value other than the primary key field.

Example:
3. Referential Integrity Constraints

 A referential integrity constraint is specified between two tables.

 In the Referential integrity constraints, if a foreign key in Table 1 refers to
the Primary Key of Table 2, then every value of the Foreign Key in Table 1
must be null or be available in Table 2.

Example:
4. Key constraints

 Keys are the entity set that is used to identify an entity within its entity set
uniquely.
 An entity set can have multiple keys, but out of which one key will be the
primary key. A primary key can contain a unique and null value in the
relational table.

Example
Data manipulation operations.
Data manipulation is the method of organizing data to make it easier to read or
more designed or structured. For instance, a collection of any kind of data could be
organized in alphabetical order so that it can be understood easily.
Data manipulation is a key feature for business operations and optimization. You
need to deal with data in a proper manner and manipulate it into meaningful
information like doing trend analysis, financial data, and consumer behavior. Data
manipulation offers an organization multiple advantages; some are discussed
below:
Consistent data: Data manipulation provides a way to organize your data
inconsistent format that makes it structured, which can be read easily and
better understood. When you are collecting data from different-different
sources, you may not have a unified view; but data manipulation provides
you surety that the data is well-organized, structured, and stored
consistently.
Project data: Especially when it comes to finances, data manipulation is
more useful as it helps to provide more in-depth analysis by using historical
data to project the future.
Delete or neglect redundant data: Data manipulation helps to maintain
your data and delete unusable data that is always present.

Data manipulation tips

One of the widely used tools for data manipulation is Microsoft Excel.
Below there are some tips to work on this tool.

Formulas and functions: In Excel, you can use essential math

functions easily to modify your data through desired values, such as
Addition, subtraction, multiplication, and division. However, you
should know that how to use these basic math functions in Excel.

Autofill: This feature is useful when you want to the same equation
across multiple fields or cells without re-entering the information
from scratch. If you do not use this function, you have to retype the
formula or need to drag the cursor from the cell's lower right corner
until the cell you want to fill up. Therefore, users can rely on this
feature for the sake of efficiency through the data manipulation
process.

Sort and Filter: When you are analyzing data and need to find specific
data, you can save a lot of time at that time by using the filtering
options. It helps to isolate the information you wish to see.

Removing duplicates: When you are collecting and assimilating data,

there are often chances of the same sets of information. In Excel, you
can delete duplicate spreadsheet entries easily by using the Delete
Duplicate feature.

Combining column: To paint a clear picture, you can merge columns

or rows in Excel or use other means to organize your data. Column
splitting, merging, and merging-Columns or rows offer users surety
that the most relevant cells are immediately visible.