Databases, Different Types, Characteristics and Design

Record: Records are composed of fields, each of which contains one item of information. A set of records constitutes a file. For example, a personnel file might contain records that have three fields: a name field, an address field, and a phone number field.

Database: It is a collection of related data. The database is a collection of interrelated 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, views, and reports, etc.

For example: The college Database organizes the data about the admin, staff, students and faculty etc.

Using the database, the information can be easily retrieved, inserted, and deleted.

DBMS 

• Database is a collection of data and Management System is a set of programs to store and retrieve those data.
• 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.

1. Tasks performed by DBMS:

• 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.

Functions of a DBMS

DBMS provides the following functions:

• Concurrency: concurrent access (meaning 'at the same time') to the same database by multiple users.
• Security: security rules to determine access rights of users.
• Backup and recovery: processes to back-up the data regularly and recover data if a problem occurs.
• Integrity: database structure and rules improve the integrity of the data.
• Data descriptions: a data dictionary provides a description of the data.

Advantages of DBMS:

• Reduced updating errors and increased consistency
• Improved data security
• Reduced data redundancy.
• Facilitated development of new applications program
• Greater data integrity and independence from applications programs
• Improved data access to users through use of host and query languages
• Reduced data entry, storage, and retrieval costs

Disadvantages of DBMS:

• Database systems are complex, difficult, and time-consuming to design
• Extensive conversion costs in moving form a file-based system to a database system
• Initial training required for all programmers and users.
• Substantial hardware and software start-up costs
• Damage to the database affects virtually all applications programs.

1. Database Languages:

• A DBMS has appropriate languages and interfaces to express database queries and updates.
• Database languages can be used to read, store and update the data in the database.

1. Data Definition Language: DDL stands for Data Definition Language. It is used to define database structure or pattern. Data definition language is used to store the information of metadata like the number of tables and schemas, their names, indexes, columns in each table, constraints, etc.

Here are some tasks that come under DDL:

Create: It is used to create objects in the database.

Alter: It is used to alter the structure of the database.

Drop: It is used to delete objects from the database.

Truncate: It is used to remove all records from a table.

Rename: It is used to rename an object.

Comment: It is used to comment on the data dictionary.

1. Data Manipulation Language: DML stands for Data Manipulation Language. It is used for accessing and manipulating data in a database. It handles user requests.

Select: It is used to retrieve data from a database.

Insert: It is used to insert data into a table.

Update: It is used to update existing data within a table.

Delete: It is used to delete all records from a table.

Merge: It performs UPSERT operation, i.e., insert or update operations.

Call: It is used to call a structured query language or a Java subprogram.

Explain Plan: It has the parameter of explaining data.

Lock Table: It controls concurrency.

1. Data Control Language: DCL stands for Data Control Language. It is used to retrieve the stored or saved data.

The DCL execution is transactional. It also has rollback parameters.

3.DATA INDEPENDENCY

User can access data without knowing how data can be stored in a database file.

In DBMS there are two types of data independence:

3.1.         Logical Data Independence: 

• It is mainly concerned with the structure or changing the data definition.
• It is difficult as the retrieving of data is mainly dependent on the logical structure of data.

3.2.         Physical Data Independence:

• Mainly concerned with the storage of the data.
• It is easy to retrieve.

1. FILE BASED SYSTEM VS DBMS

1. RDBMS RULES

• Data in the database file must be in tabular format.
• No two rows of the database table must be the same.

Example:

• Relational schema: Definition or structure of database table

• Relational Instance: Set of all records of DBMS table.
• Arity: No. of attributes of relational table.
• Cardinality: No. of records of database table.

1. RELATION

• Relation is used to refer to a table in a relational database. It is the defining feature of relational databases.
• Relational data model is the primary data model, which is used widely around the world for data storage and processing. This model is simple, and it has all the properties and capabilities required to process data with storage efficiency.
• The relational database is only concerned with data and not with a structure which can improve the performance of the model
• A relation is a named, two-dimensional table of data.
• A table consists of rows (records) and columns (attribute or field)

Attribute: Each column in a Table is called as an attribute. Attributes are the properties which define a relation. e.g., S_id , S_name , DOB

Degree: The total number of attributes which are present in a relation is called the degree of the relation.

Tables: In relational data models, relations are saved in the form of Tables. This format stores the relation among entities. A table has rows and columns, where rows represent records and columns represent the attributes.

Tuple: A single row of a table, which contains a single record for that relation is called a tuple.

Cardinality: Total number of rows present in the Table.

Relation instance: A finite set of tuples in the relational database system represents relation instances. Relation instances do not have duplicate tuples.

Relation schema: A relation schema describes the relation name (table name), attributes, and their names.

Column: The column represents the set of values for a specific attribute.

Relation key: Each row has one or more attributes, known as relation key, which can identify the row in the relation (table) uniquely.

Attribute domain: Every attribute has some predefined value scope, known as attribute domain.

6.1.         Properties of Relational Tables

Relational tables have six properties:

1. Values Are Atomic:

This property implies that columns in a relational table are not repeating group or arrays. The atomic value property of relational tables is important because it is one of the cornerstones of the relational model.

1. Column Values Are of the Same Kind:

In relational terms this means that all values in a column come from the same domain. This property simplifies data access because developers and users can be certain of the type of data contained in each column. It also simplifies data validation. Because all values are from the same domain, the domain can be defined and enforced with the Data Definition Language (DDL) of the database software.

1. Each Row is Unique:

This property ensures that no two rows in a relational table are identical; there is at least one column, or set of columns, the values of which uniquely identify each row in the table. This property guarantees that every row in a relational table is meaningful and that a specific row can be identified by specifying the primary key value.

1. The Sequence of Columns is Insignificant:

Columns can be retrieved in any order and in various sequences. It also permits the physical structure of the database to change without affecting the relational tables.

1. The Sequence of Rows is Insignificant:

The main benefit is that the rows of a relational table can be retrieved in different order and sequences. Adding information to a relational table is simplified and does not affect existing queries.

1. Each Column Has a Unique Name:

Because the sequence of columns is insignificant, columns must be referenced by name and not by position. 

1. OPERATIONS IN RELATIONAL MODEL

Four basic update operations performed on relational database model are

Insert, update, delete and select.

• Insert is used to insert data into the relation
• Delete is used to delete tuples from the table.
• Modify allows you to change the values of some attributes in existing tuples.
• Select allows you to choose a specific range of data.

1. DBMS ARCHITECTURE

Database architecture focuses on the design, development, implementation and maintenance of computer programs that store and organize information for organization.

8.1.         Types of DBMS architecture:

8.1.1. 1-Tier Architecture

• In this architecture, the database is directly available to the user.
• Any changes done here will directly be done on the database itself. It doesn't provide a handy tool for end users.
• The 1-Tier architecture is used for development of the local application, gives the user/developers the ability to communicate directly to the database without any intervention 

8.1.2. 2-Tier Architecture

• The 2-Tier architecture of DBMS consists of two tiers. Tier-1 being the database server and Tier-2 being the users or clients of the application.
• In the two-tier architecture, applications on the client end can directly communicate with the database at the server side. 
• The server side is responsible to provide the functionalities like: query processing and transaction management.
• To communicate with the DBMS, client-side application establishes a connection with the server side.

8.1.3. 3-Tier Architecture

• The 3-Tier architecture of DBMS is a fully fledged software system that is responsible for generating response to user queries in the most efficient and suitable manner.
• The 3-Tier architecture contains another layer between the client and server. In this architecture, client can't directly communicate with the server.
• The application on the client-end interacts with an application server which further communicates with the database system.
• End user has no idea about the existence of the database beyond the application server. The database also has no idea about any other user beyond the application.
• The 3-Tier architecture is used in case of large web application.
• Security, Data Backup, Recovery, Concurrency Control and Low Data Redundancy are some of the features of a 3-Tier architecture, which makes it the most widely used database architecture.

1. ER DIAGRAM

∙ It’s a logical tool which is used for database design schemes.

∙ ER model stands for an Entity-Relationship model. It is a high-level data model. This model is used to define the data elements and relationship for a specified system.

∙ It develops a conceptual design for the database. It also develops a very simple and easy to design view of data.

∙ In ER modelling, the database structure is portrayed as a diagram called an entity-relationship diagram.

Candidates can practice 150+Mock Tests with BYJU'S Exam Prep Test Series for exams like GATE, ESE, NIELIT from the following link:

Click Here to Avail Electronics Engineering Test Series (150+ Mock Tests)

Get unlimited access to 24+ structured Live Courses all 150+ mock tests to boost your GATE 2021 Preparation with Online Classroom Program:

Click here to avail Online Classroom Program for Electronics Engineering

Thanks

Sahi Prep Hai To Life Set Hai.