Database Administration

Lecture 01: Introduction to Database

Administration
A database is information that is set up
for easy access, management and
updating. Computer databases typically
store aggregations of data records or
files that contain information, such as
sales transactions, customer data,
financials and product information.
Department of Computer
Databases are used for storing, Science
maintaining and accessing any sort of
data, the one is managing is called Semester 8
Database Administrator
Lecturer Yasir A. Mohamed
 Cont.…
They collect information on people, places or things. That
information is join together in one place, so that it can be simple to
analyzed. Databases can be organized collection of information and
can un organized collection of information it deepens on the skills of
the administrator.
 What are databases used for?
Organizations use database for storing data to make up-to-date
business decisions. Some of the aims organizations use databases
include the following:

 Improve business processes. Companies collect data about

business processes, such sales, order processing and customer
service. They analyze that data to improve these processes,
expand their business and growth and revenue.
 Cont…
 Keep track of customers Databases often store information about
people, such as customers or users. For example, social media
platforms use databases to store user information, such as
names, email addresses and user behavior. The data is used to
recommend content to users and improve the user experience.

 Secure personal health information: Healthcare providers use

databases to securely store personal health data to inform and
improve patient care.
 Cont…
 Store personal data. Databases can also be used to store personal
information. For example, personal cloud storage is available for
individual users to store media, such as photos, in a managed
cloud.
 Evolution of databases
Databases were first created in the 1960s. These early databases
were network models where each record is related to many primary
and secondary records. Hierarchical databases were also among the
early models.

Relational databases were developed in the 1970s. Object-oriented

databases came next in the 1980s. Today, we use Structured Query
Language (SQL), PL/SQL and cloud databases.
 Types of databases
There are many types of databases. They may be classified according
to content type: data types, query automation and other.

In computing, databases are often classified based on the

organizational approach they use.

Some of the main organizational databases include the following:

 Types of databases
 A relational database is a type of database that stores and
provides accessibility to the data points that are related to one
another. Relational databases are based on the relational model.
 Now that you understand how relational databases work, you can
begin to learn about the many relational database management
systems that use the relational database model.

 A relational database management system (RDBMS) is a program

used to create, update, and manage relational databases. Some
of the most well-known RDBMSs include MySQL, PostSQL,
MariaDB, Microsoft SQL Server, Sybase database and Oracle
Database.
Cont…
 A distributed database is a collection of multiple interconnected
databases, which are spread physically across various locations
that communicate via a computer network.

A distributed database is a database that consists of two or more

files located in different sites either on the same network or on
different networks. Information in the database are stored in
multiple physical locations and processing is distributed among
multiple database nodes.
 Cont…
A distributed database is basically a database that is not limited to
one system it has its own Features they are

 It is used to create, retrieve, update and delete distributed

databases.
 It confirms that the data modified at any site is completely
updated.
 It is used in application areas where large volumes of data are
processed and accessed by copious users simultaneously.
 It is designed for automated database platforms.
 It maintains confidentiality or privacy and of data integrity of the
database.
 Cloud Database
A cloud database is a type of mostly used database built to run in a
public. cloud environment to help organize, store, and manage data
within an organization. Cloud databases can be offered as a
managed database-as-a-service (DBaaS) or deployed on a cloud-
based virtual machine (VM).
These databases are built in a public, private or hybrid cloud for a
virtualized environment. Users are charged $ based on how much
storage and bandwidth they use. They also get the capacity they
request and high accessibility. These databases can work with
organized applications such Cloud Data Storing applications (Google
Cloud Platform, Dropbox, Gmail, Amazon Web Services.)
 What is Database as a service
Database as a service (DBaaS) is a cloud computing managed service
offering that provides access to a database without requiring the
setup of physical hardware, the installation of software or the need
to configure the database.
 NoSQL. Database
NoSQL databases are good when dealing with large collections of
distributed data. They can address big data performance issues
better than relational databases. They also well for analyzing large
unstructured data sets and data on virtual servers in the cloud.
These databases can also be called non-relational databases.

Note that SQL is bit programming language inform of query but it

uses interface with relational databases. (Relational databases
model data as records in rows and tables with logical links between
them). NoSQL is a class of DBMs that are non-relational and
generally do not use SQL queries.
 Object-oriented Database
An object-oriented database (OOD) is a database system that can
work with complex data objects —this used in object-oriented
programming languages. Shorted by (OOP).
Do object-oriented databases use SQL?
Nearly all ODBMS products use an SQL-92-based query language.
database administrators already had a SQL-like query language
(OQL is also based on SQL-92) so Object-oriented programming has
become the leading software-development method
 Graph Database
Graph database: These databases are a type of NoSQL database.
They store chart relationships and resulting concepts as form of
graph theory. Graph database is a database that uses graph
constructions for data with nodes to store data.

These databases are often used to analyze interconnections of data

inside the software. Graph databases are also used to analyze data
about to simply interact and understand how the organizations
database woks.
Graph Database diagram
 What are the components of a database?
the different types of databases, data structure and data types most
suited to them, they are all comprised of the same five basic
components

 Hardware. This is the physical device that database software runs

on. Database hardware includes computers, servers and hard
drives.
 Software. Database software or application gives users control of
the database. Database management system (DBMS) software is
used to manage and control databases.
 What are the components of a database?
the different types of databases, data structure and data types most
suited to them, they are all comprised of the same five basic
components

 Data are raw facts and information that need to be organized and
processed to make it more meaningful

 Procedures refer to the instructions used in a database

management system and include everything from instructions to
setup and install, login and logout, manage the day-to-day
operations, take backups of data, and generate reports.
 What are the components of a database?
the different types of databases, data structure and data types most
suited to them, they are all comprised of the same five basic
components

 Database Access Language is a language used to write commands

to access, update, and delete data stored in a database.

Users can write commands using Database Access Language before

submitting them to the database for execution. Using Database
Access Language, users can create new databases, tables, insert
data, and delete data.
 What are database challenges?
Setting up, operating and maintaining a database has some common
challenges, such as the following:

 Data security is required because data is the key business asset.

Protecting data requires skilled DBA staff, which can be costly.

 Data integrity/ reliability to confirms that the data is truthful. It is

not always easy to achieve data integrity because it means
restricting access to databases to only those eligible to access it.
 What are database challenges?
Setting up, operating and maintaining a database has some common
challenges, such as the following:

 Database performance requires regular database updates and

maintenance. Without the proper support to DB, database
functionality can decline as the technology supporting database
changes.
 Database integration/combination can also be difficult. It can
involve integrating data sources from different types of databases
and structures into a single database.
 Database Administrator
A database administrator (DBA) is a specialized role within computer
systems administration. DBAs develops and operates and actives the
data storage system that digital business rely on.

In a modern IT organization, the database administrator (DBA) is often

defined set of responsibilities that manage database operations include
topologies, data copies, and application access configurations are
some functions of DBA

They are responsible for capacity planning and infrastructure.

Cont.
 Cont.
 DBAs work with developers to make sure that data structure
supports DBMS application requirements. They also certify that
databases are resilient or strong for providing high-availability of
DBMS software when accessing.

 Database administration is the function of managing and

maintaining database management systems (DBMS) software.
Normal DBMS software's such as Oracle, IBM Db2 and Microsoft
SQL Server.
 Cont.

 In other words, database administrators administer the designing,

maintenance and security of your databases.

 They do this with a working knowledge of (and experience with)

database management products and software.
What Do Database Administrators Do?
 Database administrators are highly qualified, technically skilled
individuals who use modern DBMS, cloud platforms to organize,
store, and protect critical data. Database administrators confirms
that the databases run powerfully.

 Both Database administrators and system designers creates or

organizes systems such system with DB to store and secure a
diversity amount of data, such as financial information and
customer delivery records. They also make sure that the data are
available to authorized users.
 The role of database administrator traditionally
includes the following activities:
 Management of data policies, procedures, standard
understanding of data.
 Planning and development of an organization’s enterprise data
model architecture
 Resolving data conflicts
 Managing data sources
 Define and model data requirements, business rules, operational
requirements, and maintain corporate data companies own policy
 To have ability to face any disaster affects to the DB and look for
the suitable plan for data recovery.
 Database Administrator Responsibilities?

In addition to the overall health and availability of databases, a DBA

is also responsible for:

 Defining and implementing data management policies

 Training employees in database management and use
 Directing with information systems managers to customize
organizations’ newly update and the database solutions
 Debugging code and upgrading software
 Backing up database systems for recovery scenarios
Database Administrator Skills
A skilled DBA must track emerging database technologies and newly
application architectures. DBA combines a database proficiency and
planning and creation of new database administration applications
and database administration software.
To achieve proficiency, a DBA should be knowledgeable in a range of
query languages, including:
 Structured Query Language (SQL)
 PL/SQL (Procedural Language – extension for SQL oracle)
 OQL (Object Query Language)
 CQL (Contextual Query Language)
 XQuery (XML Query
Database Administrator Training
Most database administrators have backgrounds in information
technology, management information systems, computer science, or
information security. They typically have earned computer science
degrees.
To become a database administrator, some advanced database
administration require a database administrator certification:
 Microsoft Certified Database Administrator (MCDBA)
 Microsoft Technology Associate (MTA)
 MCSA (Microsoft Certified Solutions Associate)
 Microsoft Certified Systems Engineer (MCSE)
 Database Administrator Tasks inside DBMS
Application DBAs focus on managing a specific application working
with the database.

The application DBA:

 Handles application performance issues

 Monitors database disk space, memory usage, and CPU utilization
 Analyzes queries to find long-running queries
 Identifies and applies keys to service performance
 Archives old data for a specific application
 Retrieves archived data and runs custom reports to support
application users
Typical daily tasks for both types of database
administrators are:

Some daily tasks of database administrators are the flowing

 Securing organizational data

 Database administration planning
 Backing up and restoring lost data
 Automating database processes
 Creating new user permissions
 Testing daily modifications
Typical daily tasks for both types of database
administrators are:

Some daily tasks of database are the flowing

 Merging old databases

 Monitoring database systems to make sure productivity
 Installing updated database hardware and software
 Troubleshooting accidental database issues
Network Engineer vs Database Administrator

 Network engineers and DBAs accomplish different but related

roles. Network engineers design, build and maintain the daily
operation of computer network systems, including intranet,
extranet, local area network (LAN), and wide area network
(WAN).

 Database administrators focus on the management of databases

that function as part of wider computer systems that network
engineers build.
Network Administrator vs Database Administrator

 Network administrators manage the day-to-day maintenance and

keep of the physical computer and network systems, including
components such as firewalls, routers, switches and internet
hardware for companies and organizations.

 Database administrators manage the physical or virtual servers

running a database management system to ensure data is
available and secure.
Thanks!
Any questions?

You can find me at:

yaasiraliboss65@gmail.com
sunriseacademy@outlook.com
www.sunriseacademy.com
Database Administration
Lecture 02: Database Security
Introduction to Database Security
 Database security is the technique that
protects and secures the database against
planned or accidental threats.
 Security concerns will be relevant not only
to the data resides in an organization's
database, because the breaking of
security may harm other parts of the
system, which may finally affect the Department of Computer
database structure. Therefore database Science
security includes hardware parts, software
Semester 8
parts, human resources, and data.
Lecturer Yasir A. Mohamed
 Introduction to Database Security
Types of Security issues
 Legal and ethical issues
 Policy issues
 System-related issues
 The need to identify multiple security levels
 Introduction to Database Security

 Threats that effect the performance and accessibility to the

databases include
 Loss of integrity (Modified data by un authorized user)
 Loss of availability (Loss of valid tables and availability of users inside
the Database)
 Loss of confidentiality (when the data have access third party without
permission)
 Introduction to Database Security

 To protect database against these types of threats there’re four

kinds of control can be implemented:
 Access control: To deny accessing the data by un authorized user)
 Inference control: Is technique to protect gaining from partial
information?
 Flow control: Prevents data from being transferred in such a way that it
can be accessed by unauthorized agents.)
 Encryption: Is a method of protecting data confidentiality by converting
it to encoded information
 Loss of integrity

Loss of integrity means that data or an IT system (database system)

has been modified or cracked by an unauthorized entity. This could
be the modification of a file, or the change in the configuration to a
system environment.
For example, if a file is infected with a virus, the file has lost the
integrity of data.
 Loss of availability

Availability loss − Availability loss refers to with out availability of

database objects by valid users. its the disruption of access to or use
of information in database management system.
Some of the most fundamental threats to availability are non-
malicious in nature and include hardware failures, unscheduled
software update itme and network bandwidth issues.
 Loss of confidentiality

A breach of confidentiality occurs when information given in to

third party without permission. Most confidentiality breaches
happen accidentally. those affected face financial login losses For
example, if an unauthorized employee is able to view payroll data,
personal data, trade secrets, and other private business data are
accessed accidentally by unauthorized access without permission,
this is a loss of confidentiality.
 Cont…
DBMS software's includes a database security and authorization model
(DSAM) that is responsible for ensuring the security portions of a
database against unauthorized access.

Two types of database security mechanisms:

 Discretionary security mechanisms
 Mandatory security mechanisms
 Discretionary security mechanisms

 Discretionary access control (DAC) is an identity-based access

(IBC) this is a control model that provides users with a certain
amount of control over their data. Data owners (document
creators or any users authorized to control data) can define access
permissions for specific users or groups of users.
 Mandatory security mechanisms
 Mandatory access control (also called security system) in this
security system controls used by the emerging databases,
information stored in database cannot be changed by individual
users. It is used to administer multi-level security by classifying
the data and users into various security classes or levels.
 Thus, in the scheme, each data object is and users have certain
classification level and each user is given a certain level to access.
 Thus, a mandatory access control technique classifies data and
users based on security classes.
 Cont..

 The database administrator must ability to maintain the security

mechanism of a DBMS and have provisions for restricting access
to the database as a whole.

 This function is called access control and is controlled by creating

user accounts and passwords to control login process by the
DBMS.
 Cont..
 A final security among DB is data encryption, which is used to
protect sensitive data (such as credit card numbers) when
communicated via some type communication network.

 The data is encoded it means disappearing the actual record by

some encoding algorithm.
 An unauthorized user who access encoded data will have
difficulty to gain the actual asset, but authorized users are given
decoding or decrypting algorithms (or keys) to decode or access
data.
 Database Security and the DBA role
The database administrator (DBA) is the central authority for
managing a database system. The DBA’s responsibilities for security
of the whole DBMS system such the flowing.
 granting privileges to users who need to use the system
 classifying users and data in accordance with the policy of the
organization.
 The DBA is responsible for the overall security of the database
system.
 Database Security and the DBA role
The DBA has a DBA account in the DBMS software's.
Sometimes these are called a system or superuser account
These accounts provide powerful tasks such as:
1. Account creation
2. Privilege granting
3. Privilege revoking
4. Security level Classification
Action 1 is access control, whereas 2 and 3 are discretionary (users
with a certain amount of control over their data) and 4 is used to
control mandatory authorization (classifies data and users based on
security classes.
)
 Database Security and the DBA role
Task1 (Account creation) is access control for creation databases,
creation of tables and other tasks for access controlling.

Task2 and Task3 (Privilege granting and Privilege revoking)

Privilege granting are used to give the specific user privileges, to
access the database and Privilege revoking is a type to revoke, all of
the privileges from the user or role for the specified table.
You can also revoke one or more table privileges by specifying a
privilege-list. Use the DELETE privilege type to revoke permission to
delete rows from the specified table.
 Database Security and the DBA role

Task4 (Security level classification) is used to control mandatory

authorization, It is used to administer multi-level security lowest to
highest by classifying the data and users into various security classes
or levels.
 Grant-Revoke Privileges Examples in DBMS
 Whenever the owner gives grant to privilege to another account,
privilege can be given to with GRANT or without the GRANT
option.
 If the FULL GRANT OPTION is given to User A, this means that
User A can also gives grant-Revoke privilege to other accounts.

 If the owner account revokes the privilege granted to User A, all

the privileges that User A gives other will automatically be
revoked by the system, loosing accessibility to the system.
Grant-Revoke Privileges Examples in (RDBMS) - SQL
Examples 1 Grant
 Suppose that the DBA creates four accounts
A1, A2, A3, A4 and wants only A1 to be able to create base relations.
Then the DBA must issue the following GRANT command in SQL

T-SQL QUERY

GRANT CREATETAB TO A1;

In SQL the QUERY, can be accomplished by having the DBA same role
to A1 using schema
CREATE SCHEMA command as follows:
CREATE DATABASE EXAMPLE_db AUTHORIZATION A1;
Grant-Revoke Privileges Examples in (RDBMS) - SQL
Examples 1 Grant

User account A1 can create tables under the schema called

EXAMPLE_db.
Suppose that A1 creates the two base relations EMPLOYEE and
DEPARTMENT tables.
A1 is the owner of these two relations and have all the relation
privileges on each of them.
Suppose that A1 wants to grant A2 the privilege to insert and delete
tables in both of these relations, but A1 does not want A2 to be able
to propagate these privileges to additional accounts:
GRANT INSERT, DELETE ON
EMPLOYEE, DEPARTMENT TO A2;
Grant-Revoke Privileges Examples in (RDBMS) - SQL
Examples 1 Grant (the executed T-SQL Command Output)

To see the grant access for A2, to display the table by coding this
command
SELECT * FROM TableNames;
Grant-Revoke Privileges Examples in (RDBMS) - SQL
Examples 2 Grant
Suppose that A1 wants to allow A3 to retrieve information
from either of the two tables and also to be able to propagate
the SELECT privilege to other accounts.
A1 can issue the command:
GRANT SELECT ON EMPLOYEE, DEPARTMENT
TO A3 WITH GRANT OPTION;
A3 can grant the SELECT privilege on the EMPLOYEE relation
to A4 by restricting the option of grant:
GRANT SELECT ON EMPLOYEE TO A4;
Notice that A4 can’t propagate the SELECT privilege same like A3
because GRANT OPTION was not given to A4
Grant-Revoke Privileges Examples in (RDBMS) - SQL
Examples 3 Revoke
Suppose that A1 decides to revoke the SELECT privilege on
the EMPLOYEE relation from A3; A1:
REVOKE SELECT ON EMPLOYEE FROM A3;

Note that automatically revoke will happen on the SELECT

privilege on EMPLOYEE tbl from A4, too, because A3 granted
that privilege to A4 and A3 does not have the privilege any
more because it was already revoked by the A1.
Grant-Revoke Privileges Examples in (RDBMS) - SQL

Examples 4 how to limit Grant privilege For Specific User

 Suppose that A1 wants to give back to A3 with a limited capability

to SELECT from the EMPLOYEE relation and wants to allow A3 to
be able to transmit the privilege others again.
 The limitation is to retrieve only the NAME, BDATE, and ADDRESS
Field attributes and only for the tuples with unique records NO=5.
Grant-Revoke Privileges Examples in (RDBMS) - SQL
Examples 4 how to limit Grant privilege For Specific User

Note: A1 creates the view of limit granting privilege :

CREATE VIEW A3 EMPLOYEE AS
SELECT NAME, BDATE, ADDRESS
FROM EMPLOYEE_tbl
WHERE DNO = 5;
After the view is created, A1 can grant SELECT on the view
A3MPLOYEE view table to A3 as follows:
GRANT SELECT ON A3EMPLOYEE TO A3
WITH GRANT OPTION;
Grant-Revoke Privileges Examples in (RDBMS) - SQL
Examples 5 how to limit Grant privilege For Specific User

Finally, suppose that A1 wants to allow A4 to update only the SALARY

attribute of EMPLOYEE_tbl;
A1 can issue:
GRANT UPDATE ON EMPLOYEE_tbl (SALARY) TO A4;

The UPDATE or INSERT privilege can specify particular fields of the

table that may be updated or inserted in a relation.
 Other privileges (SELECT, DELETE) are not attribute specific.
Access Protection, User Accounts, and Database
Audits

For database access protection Whenever a person or group of

person s need to access a database system, the individual or group
must first apply for a user account.

The DBA will then create a new account id and password for
protection, the user if he/she thinks there is a accurate need to
access the database. The user must log in to the DBMS by entering
account id and password whenever database access is needed.
Access Protection, User Accounts, and Database
Audits
 The database system must also keep track of all operations on the
database that are applied by a certain user throughout each login
session for the User Accounts created by the database
administrator.
 For users to access the database, you must assign the created user
accounts and grant accurately database access privileges to those
user accounts.
 The user account in database management systems is identified by
user name and password given by specific user to gain accessibility
to the database.
Access Protection, User Accounts, and Database
Audits
 A database audit is defined of reviewing the logs to check all
accesses and operations applied to the database during a certain
time period or whole period.
 A database log :is previously done database work, that is used
mainly for security purposes is sometimes called an audit trail.
 A database audit enables you to track and understand how your
records are used and gives you visibility into any risks of misuse or
breaches that may harm the database.
Access Protection, User Accounts, and Database
Audits Diagram
Thanks!
Any questions?

You can find me at:

yaasiraliboss65@gmail.com
sunriseacademy@outlook.com
www.sunriseacademy.com
Database Administration
Lecture 03: Database Backup and
Recovery
Introduction to Database Backup and
Recovery
Backup and recovery
describes the process of creating
and storing copies of data that
can be used to protect
organizations against data loss. Department of Computer
Science
This is sometimes referred to as
operational recovery. Semester 8
Lecturer Yasir A. Mohamed
 Database Backup and Recovery
 It is absolutely significant to have a backup of the database in
case the original is corrupted or lost because of any reason. Using
this backup, the database can be recovered before and after the
failure.
 Database backup basically means to have a duplicate of the
database information and data is created and stored in backup
server just to be on the safe side.
 Transaction logs are also stored in the backup along with the
database data because without them, the data would be useless.
 Reasons of Failure in a Database
 There can be multiple reasons of failure in a database because of
which a database backup and recovery plan is required. Some of
these reasons that cause database failure are the flowing:

 User Error - Normally, user error is the biggest reason of data

destruction or corruption in a database. To resolve the error, the
database needs to be restored to the point in time before the
error happened.
 Reasons of Failure in a Database
 Hardware Failure - This can also lead to loss of data in a
database. The database is stored on multiple hard drives across
various locations. These hard drives may sometimes breakdown
or get infected So, it is important to change them, When this
error is occurred at that situation we say this is Hardware Failure
 Catastrophic Event - A catastrophic event is also called accidental
database failure can be a natural calamity like a flood or
earthquake. At that time the database data may be corrupted and
backup may be required.
 Methods of Database Backup

The different methods of backup in a database are:

 Full Backup - This method takes a lot of time as the full copy of
the database is made including the data and the transaction
records.
 Transaction Log Backup - Only the transaction logs are saved as
the backup in this method. To keep the backup file as small as
possible, the previous transaction log details are deleted once a
new backup record is made.
 Methods of Database Backup

The different methods of backup in a database are:

 Differential Backup - This is similar to full backup, it stores both
the data and the transaction records. However only that
information is saved in the backup that has changed since the last
full backup. Because of this, differential backup leads to smaller
files than full backup files.
 Database Recovery

There are two methods that are mainly used for database recovery.
These are:
 Log based recovery - In log based recovery (IBR), logs of all
database transactions are stored in a secure area, in case of a
system failure, the database can recover the data. All log
information, such as the time of the transaction should be stored
after the transaction become executed. Commonly this type of
database recovery is concerned for recovering system log
information.
 Cont…

There are two methods that are mainly used for database recovery.
These are:
 Shadow paging - In shadow paging, after the transaction is
completed, its data is automatically stored for trust. For example:
you’re using PL/SQL RDBMS you executed command for creating
tables, after that you planned to insert multiple rows in that
database object, after the insertion if the system crashes in the
middle of a transaction, changes made (the rows affected) it will
be available when use this method of database recovery.
 How to take backup of a single table in a
MySQL database?
 The backup of a table can be made with the help of storing
backup data in objects as well through mysqldump utility.
 The mysqldump client utility performs logical backups, producing
a set of SQL statements that can be executed to reproduce the
original database object.
 The backup table concept was used in MySQL version 5.0 and its
earlier version.
 How to take backup of a single table in a
MySQL database?
 Here, I am performing backup with the help of mysqldump.
Firstly, we will open cmd with the help of shortcut key. The
mysqldump will run at the cmd. Therefore, firstly open cmd with
the help of shortcut key windowskey+R; Here is the snapshot −
How to take backup of a single table in a
MySQL database?

Now, cmd will open −

In this, the MySQL bin folder is present at the following

location −
C:\Program Files\MySQL\MySQL Server 8.0\bin
How to take backup of a single table in a
MySQL database?
Type the above path to reach the “bin” folder.

Now, we are in “bin” folder. The query to take backup is

as follows −
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump -u
Manish -p business student < E:\student.sql Enter password:
***********
Cont…
The following is the output of backup table named students−
Cont…
The following is the output of backup table named students−
 How can we take a backup of the single
database by using mysqldump client
program?
 By using mysqldump client program we can take the backup
of a database into a file having the extension ‘.sql’. it can be
understood with the help of following example −
Example
In this example, with the help of mysqldump client program,
we are taking the backup of a database named ‘PSUDATABASE’
in a file named ‘psudatabase.sql’. The following command will do
this −
C:\mysql\bin>mysqldump -u root psudatabase.sql>
psudatabase.sql.sql
 ow can we take a backup of the single
database by using mysqldump client
program?
 The above command will create a file named ‘psudatabase.sql’ in
the bin folder of MySQL. This file will contain drop table, create a
table and insert command for all the tables in the psudatabase.
 Following is a partial output of tutorials.sql showing the dump
information of ‘rtgs’ table −
 ow can we take a backup of the single database
by using mysqldump client program?
 Following is a partial output of PSUDATABASE.sql showing the dump
information of ‘rtgs’ table −
 How to Restore MySQL with mysqldump
Step 1: Create New Database
 On the system that hosts the database, use MySQL to create a
new database.

 Make sure you’ve named it the same as the database you

lost. This creates the foundation file that mysqldump will
import the data into. Since the importing file has the commands
to rebuild the database, you only need to create the empty
database.
 Using phpMyAdmin to Back Up or Restore
MySQL

If you’re running phpMyAdmin backing up and restoring your MySQL

database is simple.
The export function is used as a backup, and the import function is
used to restore
 Step 1: Create a MySQL Database Backup

1. Open phpMyAdmin. On the directory on the left, click the database

you want to back up.
This should open the directory structure in the right-hand window.
You’ll also notice that, in the directory tree on the left, all the assets
under the main database are highlighted.
2. Click Export on the menu across the top of the display.
You’ll see a section called “Export Method.” Use Quick to save a copy of
the whole database. Choose Custom to select individual tables or other
special options.
EXPORT AND IMPORT MySQL Database
Backup
 Step 1: Create a MySQL Database Backup

Set the Format field set to .SQL, unless you have a good reason to
change other extensions.

3. Click Go. If you select Quick, your web browser will download a copy
of the database into your specified downloads folder. You can copy
that to a safe location.
Thanks!
Any questions?

You can find me at:

yaasiraliboss65@gmail.com
sunriseacademy@outlook.com
www.sunriseacademy.com
Database Administration
Lecture 04: Database Design
Introduction to Database Design

This lecture is about the basis

of relational database design
and explains how to make a
good database design. It is a
rather long text, but we advise
Department of Computer
to read all of it. Designing a Science
database is in fact fairly easy,
but there are a few rules to Semester 8

stick to. Lecturer Yasir A. Mohamed

 Cont..
 It is important to know what these rules are, but more
importantly is to know why these rules exist, otherwise you will
tend to make mistakes!

 The design of the database makes your data model flexible and
that makes working with your data much easier. Please, take the
time to learn these rules and apply them! The database used in
this article is designed with our database design and modeling
tool DeZign for Databases.
 Cont..
Before the design make sure to to have knowledge about data in
database, It is a collection of information.
The facts that can be recorded and which have contained meaning
information known as 'data'.

Example:
Customer table
1.cname. Cname Cno Ccity
2.cno.
3.ccity.
 Database System
It is computerized designed system, whose overall purpose is
to maintain the information and to make that the information is
available on request.
Advantages:
 Redundancy can be reduced.
 Conflict can be avoided.
 Data can be shared.
 Standards can be required.
 Security restrictions can be applied.
 Integrity can be maintained.
 Data gathering can be possible.
 Requirements can be balanced.
 Cont…
People who deal with databases

Many persons are involved in the design, use and maintenance of

any database. These persons can be
classified into 2 types as below.

1. Actors on the scene:

The people, whose jobs involve the day-to-day use of a database are
called as 'Actors on the scene',
listed as below.
 1.Database Administrators (DBA):
The DBA is responsible for authorizing access to the database, for
Managing and monitoring its use and for acquiring software and
hardware resources as needed. These are the people, who maintain
and design the database daily. DBA responsibilities already reviewed
the early lectures
 the day-to-day use of a database acting as
Actors on the scene:
2.Database Designers: Database designers are responsible for
identifying the data to be stored in the database and for choosing
correct design of the database to represent and store this data.

3. End Users: People who wish to store and use data in a database.
End users are the people whose jobs require access to the database
for querying, updating and generating reports, listed as below.
 the day-to-day use of a database acting as
Actors on the scene:

4.System Analyst: These people determine the requirements of end

users and develop specifications for transactions.

5.Application Programmers (Software Engineers): These people

can test, debug, document and maintain the specified transactions
 OVERVIEW OF DATABSE DESIGN

 Database Design is a collection of processes that facilitate the

designing, development, implementation and maintenance of
data database design management.

 Properly designed database are easy to maintain, improves data

reliability and are cost effective in terms of disk storage space.
The database designer decides how the data elements correlate
and what data must be stored.
 Database Design Life Cycle

Although, the life cycle of a database is an important life-cycle

discussion that has to be taken forward in earlier chapters, but now
we are focusing on the database design. But, before jumping
directly on the designing models constituting database design it is
important to understand the overall workflow and life-cycle of the
database.
 Requirement Analysis
 Database Designing
 Implementation
Cont…
the overall workflow and life-cycle of the database.
 Database Design Life Cycle
The design process consists of the following steps:
 Determine the purpose of your database
This helps prepare you for the remaining steps.
 Find and organize the information required
Gather all of the types of information you might want to record in
the database, such as product name and order number.
 Divide the information into tables
Divide your information items into major entities or subjects, such as
Products or Orders table. Each subject then becomes a table.
 Database Design Life Cycle
 Turn information items into columns
Decide what information you want to store in each table. Each item
becomes a field, and is displayed as a column in the table. For
example, an Employees table might include fields such as Last Name
and Date.
 Specify primary keys
Choose each table’s primary key. The primary key is a column that is
used to uniquely identify each row. An example might be Product
ID or Order ID.
 Database Design Life Cycle
 Set up the table relationships
Look at each table and decide how the data in one table is related to
the data in other tables. Add fields to tables or create new tables to
clarify the relationships, as necessary.
 Refine your design
Analyze your design with out errors. Create the tables and add a few
records of sample data. See if you can get the results you want from
your tables. Make corrections to the design, as needed.
 Apply the normalization rules if failure affacted
Apply the data normalization rules to see if your tables are
structured correctly. Make adjustments to the tables, as needed.
 Types of Database design

Logical model design– This stage is concerned with developing a

database design based on requirements. The entire design is on
paper without any physical implementations or specific DBMS
considerations.
Physical model design – This stage implements the logical model of
the database taking into account the DBMS and physical
implementation factors.
 Types of Database design

Logical model design– This stage is concerned with developing a

database design based on requirements. The entire design is on
paper without any physical implementations or specific DBMS
considerations.
Physical model design – This stage implements the logical model of
the database taking into account the DBMS and physical
implementation factors.
Logical model design Diagram
The entire design is on paper without any physical implementations
Physical model design Diagram
The entire design is on paper without any physical implementations
Thanks!
Any questions?

You can find me at:

yaasiraliboss65@gmail.com
sunriseacademy@outlook.com
www.sunriseacademy.com