DBMS It is a collection of interrelated data

DBMS • It is a collection of interrelated data and a set of programs to access those data. The collection of data, usually referred to as the database, contains information relevant to an enterprise. • 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 (a drawing that represents an idea or theory and makes it easier to understand), views, and reports, etc. • The primary goal of a DBMS is to provide a way to store and retrieve database information that is both convenient and efficient

• Database management system is a software which is used to manage the database. For example: My. SQL, 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.

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.

DBMS • A datum, a unit of data – is a symbol or a set of symbols which is used to represent something. • Information is interpreted data – data supplied with semantics (relating to meaning in language or logic) • Knowledge refers to the practical use of information. While information can be transported, stored or shared Knowledge necessarily involves a personal experience. Third person reading the results will have information about it, while the person who conducted the experiment personally will have knowledge about it

Characteristics of Database 1. Concurrent Use: A database system allows several users to access the database concurrently. Answering different questions from different users with the same (base) data is a central aspect of an information system Example: (Bigger travel agency) The employees of different branches can access the database concurrently and book journeys for their clients. Each travel agent sees on his interface if there are still seats available for a specific journey or if it is already fully booked.

Characteristics of Database 2. Structured and Described Data Descriptions are basically details about the extent, the structure, the type and the format of all data and, additionally, the relationship between the data. This kind of stored data is called metadata ("data about data").

Characteristics of Database 3. Separation of Data and Applications: An application software does not need any knowledge about the physical data storage like encoding, format, storage place, etc. It only communicates with the DBMS via a standardized interface with the help of a standardized language like SQL. The access to the data and the metadata is entirely done by the DBMS. In this way all the applications can be totally separated from the data. Therefore database internal re-organisations or improvement of efficiency do not have any influence on the application software.

Characteristics of Database 4. Data Integrity (quality and reliability of the data ) In a broader sense data integrity includes also the protection of the database from unauthorized access (confidentiality) and unauthorized changes. Data reflect facts of the real world.

Characteristics of Database 5. Transactions A transaction is a bundle of actions which are done within a database to bring it from one consistent state to a new consistent state. Example: Transfer of an amount of money from one bank account to another. The debit of the money from one account and the credit of it to another account makes together a consistent transaction. This transaction is also atomic. The debit or credit alone would both lead to an inconsistent state. After finishing the transaction (debit and credit) the changes to both accounts become persistent and the one who gave the money has now less money on his account while the receiver has now a higher balance.

Characteristics of Database 6. Data Persistence: All data is maintained as long as it is not deleted explicitly. The life span of data needs to be determined directly or indirectly by the user and must not be dependent on system features. Additionally data once stored in a database must not be lost. Changes of a database which are done by a transaction are persistent. When a transaction is finished even a system crash cannot put the data in danger

Database Administration A person who has central control over the system is called database administrator. The function of DBA are: 1. creation and modification of conceptual Schema definition 2. Implementation of storage structure and access method. 3. schema and physical organization modifications. 4. granting of authorization for data access. 5. Integrity constraints specification. 6. Execute immediate recovery procedure in case of failures 7. ensure physical security to database

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 the data may be lost forever.

Applications Banking: all transactions Airlines: reservations, schedules Universities: registration, grades Sales: customers, products, purchases Online retailers: order tracking, customized recommendations • Manufacturing: production, inventory, orders, supply chain • Human resources: employee records, salaries, tax deductions • • •

Database language 1) Data definition language(DDL): DDL is used to define database objects. The conceptual schema is specified by a set of definitions expressed by this language. It also give some details about how to implement this schema in the physical devices used to store the data. This definition includes all the entity sets and their associated attributes and their relation ships. The result of DDL statements will be a set of tables that are stored in special file called data dictionary. It is a language that allows the users to define data and their relationship to other types of data. It is mainly used to create files, databases, data dictionary and tables within databases. .

Database language It is also used to specify the structure of each table, set of associated values with each attribute, integrity constraints, security and authorization information for each table and physical storage structure of each table on disk. The following table gives an overview about usage of DDL statements in SQL

Database language Data Manipulation Language (DML) • A DML is a language that enables users to access or manipulate data stored in the database. Data manipulation involves retrieval of data from the database, insertion of new data into the database and deletion of data or modification of existing data. There are basically two types of DML: - Procedural: Which requires a user to specify what data is needed and how to get it. - Non-rocedural: which requires a user to specify what data is needed with out specifying how to get it

Database language • It is a language that provides a set of operations to support the basic data manipulation operations on the data held in the databases. It allows users to insert, update, delete and retrieve data from the database. The part of DML that involves data retrieval is called a query language.

Database language • The following table gives an overview about the usage of DML statements in SQL:

Database language • Data Control Language (DCL) DCL statements control access to data and the database using statements such as GRANT and REVOKE. A privilege can either be granted to a User with the help of GRANT statement. The privileges assigned can be SELECT, ALTER, DELETE, EXECUTE, INSERT, INDEX etc. In addition to granting of privileges, you can also revoke (taken back) it by using REVOKE command.

Database language • The following table gives an overview about the usage of DCL statements in SQL: