Database Management System ( DBMS )

According to syllabus, students must be able to:

1. Introduce Database Management System (DBMS) with its aspects.

2. State the advantages of using DBMS.

3. Define Data Definition Language (DDL) and Data Manipulation Language (DML).

4. Introduce and use database model.

5. State the concept of normalization.

6. Compare between centralized and distributed database.

7. Introduce database security and apply it.

DBMS is a collection of programs that enables users to create, operate and maintain a database.

Data: Data is a collection of raw facts and figures which is isolated, un-interpreted, unrelated, and unorganized, but able to organize into useful information.

Information: Information is the processed data i.e. after processing the data we can get information. Information is the data arranged in an order that is useful to people who receive it.

Database: A database is a collection of interrelated data of a certain place or person or things in such a way that it can be easily accessed by an application program like DBMS.

Database System: A database system consists of a collection of interrelated data and a set of application programs to access, update and manage the data.

Uses of database application:

1. Storing data

2. Accessing data when it is required.

3. Updating/modifying data.

4. Deleting or removing data.

5. Providing security on the data so that it can be used only by the authorized persons.

6. Sharing of data for concurrent access.

7. Maintaining accuracy of the data.

8. Enforcing standards or rules to access the data.

9. Reducing data redundancy.

10. Providing timely and accurate information.

Database Management System (DBMS)

A DBMS is a set of programs that manages the database files. The DBMS allows accessing the files, updating the records, and retrieving data as requested. The DMBS acts as an interface between the application program and the data in the database. 

Examples of DBMS are Microsoft Access, MySQL, SQL Server, dBase, etc. 

Objective of DBMS

1. To provide for mass storage of relevant data

2. To make easy access to data for the authorized user

3. To provide prompt response to the users request for data.

4. To allow multiple users to be active at one time

5. To protect the data from physical harm and unauthorized access.

6. To allow the growth of the database

7. To provide multiple views for same data

8. To eliminate data redundancy (duplication of data)

Advantages of using DBMS

1. Controlling data redundancy: Data is recorded in only one place in the database and it is not duplicated. It saves the storage space.

2. Data consistency: Data item appears only one ( no redundancy ). So the updated value is immediately available to all users.

3. Backup and recovery procedures: It automatically create the backup of data and restore data if required.

4. Program data independence: There is separation of data structure of database from application program that uses the data is called data independence.

5. Enforcing data integrity: In the database approach, enforcing data integrity is much easier. Various integrity constraint are identified by the database designer during database design.

6. Data sharing: The data stored in the database can be shared among multiple users or application program.

7. Ease of application development: The application programmer develops the application programs according to the needs of the users. Other tasks are handled by DBMS like concurrent access, security, data integrity etc. This makes application development an easier task.

8. Data security: The DBMS ensures that the only means of access to the database is through an authorized channel. To ensure security, DBMS provides security tools such as user codes and passwords.

9. Supports multiple views of the data: A database can be accessed by many users and each user have a different view of the data. A database system provides a facility to define different views of the data for different users.

10. Data automicity: It is the duty of DBMS to store a complete transaction in database. If any transaction is partially completed then it roll backs them.

11. Concurrency control: If two users are accessing data simultaneously and they both want to update values of same record then it may create inconsistency. DBMS has the power to control concurrency so that no transactions are lost.

Disadvantages of DBMS

1. Cost of hardware and software: Installing a new database system may require investment in hardware and software. Processor with high speed of data processing and memory of large size is required. DBMS is quite expensive, therefore a company needs to consider the overhead cost of implementing a new database system.

2. Cost of staff training or training new personnel: When an organization plans to adopt a database system, it may need to recruitor hire a specialized data administration group which can coordinate with different user groups. Hiring such professionals is expensive. Also lot of training of staff is required to run DBMS.

3. System failure: When a computer system containing the database fails, all users have to wait until the system is functional again. A permanent damage may also occur to the database, if DBMS or the application program fails.

4. Explicit backup and recovery: To make stored database accurate and available at all times, a system using on-line updating requires explicit backup and recovery procedures.

Structured Query Language (SQL)

DBMS provides one or more specialized programming languages called database languages.

SQL is a database language that has been taken as the standard database language. SQL is a programming language that is specially designed for managing data sets in a relational database management system. SQL contains the statements that describe the data contained in the database.

DDL (Data Definition Language): A computer language which is used to create and modify the structure of database objects in a database. These database objects include views, schemas, tables, indexes etc. It is a link between logical and physical structures of the database.

DML (Data Manipulation Language): A computer language that provides the techniques for processing the database, such as retrieval, sorting, display and deletion of data or records. DML writes programs to build updates and retrieve data from the database.

S.N	DDL	S.N	DML
1.	DDL is a computer language that is used to create and modify the structure of database objects in a database.	1.	DML is a computer language that is used to add, delete and modify data in a database.
2.	DDL commands affects the database or the table.	2.	DML commands affect one or more records in a table.
3.	DDL commands are CREATE, DROP, ALTER, TRUNCATE, RENAME etc.	3.	DML commands are SELECT, INSERT, UPDATE, DELETE etc.
4.	These SQL statements cannot be rolled back.	4.	These SQL statements can be rolled back.
5.	Low cost	5.

There are two types of DML:

1. Procedural DML: It requires a user to specify what data are needed and how to get those data.

2. Non-procedural DML: It requires a user to specify what data are needed without specifying how to get those data.

Data Dictionary: Data Dictionary is a collection of names, definitions and attributes about data elements that are being used or captured in a database, information system, or part of a research project. 

Data Dictionary is consulted before actual data are read or modified in the database system. A language that is used to define the data dictionary is called data dictionary language.

Database Model: Database Model is a type of data model that determines the logical structure of a database. It defines how the data are stored and accessed.

Types of Database Model

1. Hierarchical Database Model: A hierarchical database is a data model in which data is stored in the form of records and organized into a tree-like structure, or parent-child structure, in which one parent node can have many child nodes connected through links. All records in the hierarchy are called nodes.

Advantages

1. It is the easiest model of the database.

2. Database security is enforced.

3. Promotes data security

4. High performance

5. Searching is fast and easy if the parent is known.

6. Very efficient in handling 'one to many' relationship.

Disadvantages

1. Poor flexibility

2. Rigid structure

3. No DML or DDL

4. Complex relationships are not supported

5. It cannot handle many to many relationship.

6. It increases data redundancy as same data can be saved in different places.

    

2. Network Database Model: A network database model is a data model in which multiple records are linked to the same owner file. In network database structure each node may have several parents. 

Advantages

1. It is more flexible than hierarchical model as it accepts many to many relationships.

2. It reduces redundancy because data should not be repeated if same data is needed.

3. Searching is fast because of multi directional pointers.

4. It is simple and implementation is effortless.

Disadvantages

1. It is one of the complex database models.

2. It needs larger programs to handle the relationships.

3. Pointers needed in this model increase the overhead of storage.

4. It is less secure compared to hierarchical model.

  

3. Relational Database Model:  Relational database model is a database model in which data are arranged in two dimensional table which is easy for a user to develop and understand. It consists of a collection of tables, each of which is assigned a unique name.

Advantages

1.Data accuracy

2. Easy access of data

3. Flexibility

4. High security

5. Normalization of the database is possible

6. Very low data redundancy

7. Rapid processing of database is possible

Disadvantages

1. It is more complex than other models.

2. It consists of many rows and columns and hence consumes more storage.

4. Object-oriented Model: Object-oriented model is a database model that is based on object oriented programming. The data is represented and stored in the form of objects.

• Entity: An entity is a name of thing or person or object in the real world that is distinguishable from all other objects. For eg: student, employee, book etc.
• Attribute: The properties that characterize an entity set are called its attribute. For eg: sutdent_id, student_name etc
• Relationship: Relationship is the association between entities. They are: One-to-one, one-to-many, many-to-one, many-to-many
• Key: Key is an attribute that is used to identify a particular record in a database. Types of keys are: Candidate key, Primary key, Super key, Alternate key, Foreign key

Concept of Normalization: 

Data normalization is a process to present a database in a normal form to avoid undesirable things such as repetition of information, inability to represent information, loss of information, etc.

Normalization is a database design technique that reduces data redundancy and eliminates undesirable characteristics like insertion, update and deletion anomalies.

Normalization rules divides larger tables into smaller tables and links them using relationships.

Advantages

1. Reduces data redundancy.

2. Improves faster sorting and index creation.

3. Creates a few indexes and nulls.

4. Ignores the repetition of information.

5. Simplifies the structure of tables.

6. Improves the performance of a system.

7. Avoids the loss of information.

First Normal Form ( 1NF ): A relation or table is said to be 1NF if its all attributes are atomic. If any characteristics is repeated again and again in the same table/row, then such attributes are removed either into a separate table or decomposed into different rows.

Rules of 1NF

1. Each column should contain atomic values.

2. A column should contain values that are of same type.

3. Each column should have a unique name. Same names leads to confusion at the time of retrieval.

4. Order in which data is saved does not matter. Using SQL query, you can easily fetch data in any order from a table.

Second Normal Form ( 2NF ): A relation is said to be in second normal form if it is in first normal form and each attribute is functionally dependent on the entire primary key. Each attribute in the table must depend on the whole key, not just the part of it.

Rules of 2NF

1. It should be in 1NF.

2. It should not have any partial dependencies.

Third Normal Form ( 3NF ): A relation is said to be in the third normal form if it is in the second normal form and it does not contain any transitive dependency on the primary key.

All attributes that are not dependent upon the primary key must be eliminated. Transitive dependency is the one in which, among three attributes A,B and C, if A--->B, B--->C then C--->A.

Differences between Centralized and Distributed Database System:

S.N	Centralized Database System	S.N	Distributed Database System
1.	Data reside in a single location.	1.	Data reside in several locations.
2.	Files are kept on the basis of the  location of disk drives and names	2.	Files are kept on the basis of names  rather than location.
3.	It does not contain several sites or  nodes.	3.	It contains several computers(sites) and  communicates with one another through communication  media.
4.	No risk of data loss	4.	High risk of data theft, hacking etc.

Database Security: Comming soon

The End