DBMS languages
Database languages are used to read, update and store
data in a database. There are several such languages that can be used for this
purpose; one of them is SQL (Structured Query Language).
·
DDL
(Data Definition Language): used (by the DBA and/or database designers) to specify
the conceptual schema.
·
SDL (Storage
Definition Language): used for specifying the internal schema
·
VDL
(View Definition Language): used for specifying the external schemas (i.e.,
user views)
·
DML
(Data Manipulation Language): used for performing operations such as retrieval
and update upon the populated database
The above description represents some
kind of ideal. In real-life, at least so far, the standard DBMS language is SQL
(Standard Query Language), which has constructs to support the functions needed
by DDL, VDL, and DML languages.
Types of DBMS
languages:
Data Definition
Language (DDL)
DDL is used for
specifying the database schema. It is used for creating tables, schema,
indexes, constraints etc. in database. Lets see the operations that we can
perform on database using DDL:
·
To
alter the structure of database – ALTER
·
To
delete tables in a database instance – TRUNCATE
·
To
rename database instances – RENAME
·
To
drop objects from database such as tables – DROP
·
To
Comment – Comment
All of these
commands either defines or update the database schema that’s why they come
under Data Definition language.
Data
Manipulation Language (DML)
DML is used for
accessing and manipulating data in a database. The following operations on
database comes under DML:
·
To
insert record(s) into the table(s) – INSERT
Data Control language
(DCL)
DCL is used for granting and revoking user access on a
database –
·
To
grant access to user – GRANT
·
To
revoke access from user – REVOKE (officially
cancel)
In practical data definition language, data
manipulation language and data control languages are not separate language,
rather they are the parts of a single database language such as SQL.
Transaction Control
Language(TCL)
The changes in the database that we made using DML
commands are either performed or rollbacked using TCL.
·
To
persist the changes made by DML commands in database – COMMIT
·
To
rollback the changes made to the database – ROLLBACK
2.4 Database Users
Database
users are the one who really use and take the benefits of database. There will
be different types of users depending on their need and way of accessing the
database.
Application Programmers
- They
are the developers who interact with the database by means of DML queries.
These DML queries are written in the application programs like C, C++, JAVA etc.
These queries are converted into object code to communicate with the database.
For example, writing a C program to generate the report of employees who are
working in particular department will involve a query to fetch the data from
database. It will include a embedded SQL query in the C Program.
Sophisticated Users - They
are database developers, who write SQL queries to select/insert/delete/update
data. They do not use any application or programs to request the database. They
directly interact with the database by means of query language like SQL. These
users will be scientists, engineers, analysts who thoroughly study SQL and DBMS
to apply the concepts in their requirement. In short, we can say this category
includes designers and developers of DBMS and SQL.
Specialized Users - These
are also sophisticated users, but they write special database application
programs. They are the developers who develop the complex programs to the
requirement.
Stand-alone Users - These
users will have stand –alone database for their personal use. These kinds of
database will have readymade database packages which will have menus and
graphical interfaces.
Native Users - these
are the users who use the existing application to interact with the database.
For example, online library system, ticket booking systems, ATMs etc which has
existing application and users use them to interact with the database to
fulfill their requests.
Database
Administrators
The life cycle of
database starts from designing, implementing to administration of it. A
database for any kind of requirement needs to be designed perfectly so that it
should work without any issues. Once all the design is complete, it needs to be
installed. Once this step is complete, users start using the database. The
database grows as the data grows in the database. When the database becomes
huge, its performance comes down. Also accessing the data from the database
becomes challenge. There will be unused memory in database, making the memory
inevitably(as is certain to happen) huge. These
administration and maintenance of database is taken care by database Administrator
– DBA.
A DBA has many
responsibilities. A good performing database is in the hands of DBA.
·
Installing and upgrading the DBMS Servers: - DBA is responsible
for installing a new DBMS server for the new projects. He is also responsible
for upgrading these servers as there are new versions comes in the market or
requirement. If there is any failure in up-gradation of the existing servers,
he should be able revert the new changes back to the older version, thus
maintaining the DBMS working. He is also responsible for updating the service
packs/ hot fixes/ patches to the DBMS servers.
·
Design and implementation: - Designing the
database and implementing is also DBA’s responsibility. He should be able to
decide proper memory management, file organizations, error handling, log
maintenance etc for the database.
·
Performance tuning: - Since database is huge and it will have
lots of tables, data, constraints and indices, there will be variations in the
performance from time to time. Also, because of some designing issues or data
growth, the database will not work as expected. It is responsibility of the DBA
to tune the database performance. He is responsible to make sure all the
queries and programs works in fraction of seconds.
·
Migrate database servers: - Sometimes, users
using oracle would like to shift to SQL server or Netezza. It is the
responsibility of DBA to make sure that migration happens without any failure,
and there is no data loss.
·
Backup and Recovery: - Proper backup and recovery programs
needs to be developed by DBA and has to be maintained him. This is one of the
main responsibilities of DBA. Data/objects should be backed up regularly so
that if there is any crash, it should be recovered without much effort and data
loss.
·
Security: - DBA is responsible for creating various
database users and roles, and giving them different levels of access rights.
·
Documentation: - DBA should be properly documenting all
his activities so that if he quits or any new DBA comes in, he should be able
to understand the database without any effort. He should basically maintain all
his installation, backup, recovery, security methods. He should keep various
reports about database performance.
In order to perform his
entire task, he should have very good command over DBMS.
No comments:
Post a Comment