Introduction to Database Administration

Welcome to Database Administration

Welcome to Lecturus.org. My name is Md Samdani and I will be your instructor for Introduction of Database Administration. This is an advance level Database course and it requires at least the fundamental knowledge of database such as you must know what database is, what is SQL, how to design ERD, how does it connect it with database, how to query and how database works etc. If you haven’t done our Introduction of Database course please visit our courses page at Lecturus.org. If you think about learning simple manageable database system, we offer Microsoft Access lessons.

What is Database Administration

The administration of the database applies to the entire spectrum of tasks carried out by a database manager to ensure that the database is still accessible as required. Other tasks and functions closely related to this include database management, database maintenance and troubleshooting and future growth planning.

For any company based on one or more databases, the management of databases is an essential task. Database Administrator (DBA) is the role who takes care of all databases and maintain essential needs for the database runs ongoing.

The DBA is typically a dedicated position for large companies in the IT department. Nonetheless, many smaller businesses that are not able to afford a full-time DBA usually outsource or assume specialist vendor roles or combine their position in the ICT department to ensure that all companies are handled by one person.

The main objective of database administration is to ensure the database has maximum time to be available whenever necessary. In general, proactive periodic monitoring and troubleshooting when it required. It in turn carries with its other professional skills from the DBA. In addition to an in-depth knowledge of that database, the DBA also needs knowledge and maybe training on the platform that runs the database management system.

In this course we will cover very brief of what Database Administration is, what is DBA roll, and what that roll responsible for? This is not a course for Database Developer or DBA certification. But this knowledge leads you how to pursue that certification in easy way.

We also cover

Installation Microsoft SQL Server 2017
Create Database
Create Table
Restore and Backup
Write Simple Query
How we generate a simple report
What is Store Procedure and how to create one
How to use a reporting tool
Report Visualization.

Open Source Books: archive.org

Install Database Management System DBMS

Install Microsoft SQL server 2017 is one of the very important and sensitive part and we have to be very careful on that. If we miss any step system might not work. Even though we try to show very simple steps but still we need to be very careful.

Resource File:

Create First Database

In this lesson we are going to learn how to create your first database on SQL server 2017. As always we are using GUI click, drag and drop etc on Microsoft SQL server Management Studio.

Few things we need to remember:
1. Database name must be unique.
2. Make sure Select SA(system Admin) in database owner section.

Click HERE for more detail.

Create First Table

Create a new Table in SQL Server 2017.

Few things we need to remember:
1. Table/Entity name must be unique in database or schema.
2. Table must be normalized.
3. Each table should have a unique PK( Primary Key) that contains one or more column.
3. Column/field/attribute needs proper data type.

Click here for detail Instruction

Restore Database

Restore a Database in SQL server

Restore is the method that the data is copied from the backup and the logged data is applied. Restore is the backups you ‘re doing. Restore the backup file and return it to a database.

Click here for detail Instruction

Resource File: Click Here

Backup Database

How to Backup any Database and how to find where you backed it up.

Backup is a process or method of making a copy of data from SQL Server to desired safe location. This file usually restore and recover after any malfunction of database or any data failure. Data backup Server backups can also be used to recover a new copy of the database.

Types of Database Backup:

Most Using Backup:

Full
Differential
Transaction Log

Least use Backup type:

Partial
Filegroup
File

FULL Backup: As the name suggests, a full backup supports it all. This is the basis of any form of backup. This is a copy that stores all the data objects: tables, processes, functions, views, indexes and so on. You can restore a database with a complete backup in exactly the same manner as it was when it was backed up.

More Detail: Click Here

Write First Query

Query is nothing but simple question based on requirement. Query always contains few questions.

What are you looking for?
Where the data we getting from? Means which table?
Is the any condition we need to apply?

There are different types of queries you can experience. Such as SQL query, sub-query, co-related nested query.

For detail please click Here. Microsoft provide in-depth detail regard SQL Server.

More Resource: Click Here

Generate a simple report

A report is nothing but a query may contains with different data from different table based on business requirement.

For example: A company may have one or many different products. In order to see company sells or total production company need the data that they will come analyze that data and make decision. In that case we need a business report that shows all the data to make that decision.

Store Procedure

Store Procedure is a very important term for Database developer which is a collection of one or more database SQL statements. If you need to write same query again and again you could leverage store procedure and make your work simple.

Once you call a stored process for first time, it generates an execution plan ad saves it in SQL Server. SQL Server reuse the plan when running the stored procedure afterwards, to allow the stored process to run very quickly with a consistent output.

For Example:

CREATE or REPLACE PROCEDURE name(parameters)
IS
variables;
BEGIN
//Desired statements(Query) ;
END;

Reporting Tool

Reporting tool is nothing but a process that visualize your data into graph. They play a vital role in transforming vast volumes of unfiltered data into visually appealing reports and dashboards on time. All of these business intelligent tools help capture and process large amounts of unstructured data and help find the information you need quickly through queries.

Report Visualization

Download Microsoft Power BI: Click Here

Conclusion

Thanks you for being part of the Introduction of Database Administration course. In future we will bring in-depth course which will cover a lot more. Click the book below to learn More about Database Administration.