What is Database? The Storage of Information

What is Database?

In its most basic form, a database is an ordered collection of connected data. Paper-based databases include things like shopkeepers’ customer records, instructors’ mark registers, and diaries that contain information about friends. On the other hand, the word “database” usually refers to a computerized database. Large amounts of data are intended to be managed by databases. Data management entails both establishing frameworks for information storage and offering tools for data manipulation. The data gathering in a database has an implied meaning and includes information pertinent to a company. Databases are employed in a wide range of important application sectors, including human resources, manufacturing, banking, aviation, and academia. A relational database’s data is usually arranged in one or more tables.

What is a Database Management System (DBMS)?

Software called a Database Management System (DBMS) lets users build and manage databases. It consists of a group of connected data and a collection of programs that are used to retrieve the data. A DBMS is basically a computerized record-keeping system or a computerized data file repository. A database management system’s main objective is to offer an effective and convenient method of storing and retrieving database data. It conceals from the user the intricacy of the underlying procedures while controlling the storage and retrieval of data to and from the database.

Structured data storage, querying the database (asking questions about the data), sorting and manipulating data, validating data entry and checking for discrepancies, and creating flexible reports are all made possible by a database management system. The stored data must also be protected from system failures and unauthorized access. MySQL, PostgreSQL, Access, Oracle, SQL Server, DB2, and Sybase are popular DBMSs.

What is an RDBMS (Relational Database Management System)?

Manually manipulating relational databases requires RDBMS software. MySQL describes itself as an RDBMS.

Tables hold data in a Relational Database. Rows and columns make these tables like spreadsheets. A table displays data in two dimensions. Relational databases organize data into tables and link them with relationships. This is done with a shared column.

A row or tuple is the name given to the horizontal portion inside a table. Each row is a record with data about a person, place, or thing. Columns or attributes are vertical subsets. Columns are often called fields. Each column must have the same data type and a unique name.

Relational databases define relationships and maintain data integrity with keys. The Primary Key field or set of fields in a table identifies each row with a unique value. MySQL doesn’t require a primary key, yet it’s beneficial for uniquely identifying rows. A foreign key is a field in one table that acts as a connecting element between two tables by linking to a primary key in another table. Potential primary keys are fields or combinations of fields known as candidate keys. Alternate keys are those that remain after one candidate key is selected as the primary key.

MySQL dominates databases. A quick, dependable, and simple RDBMS. It was first created in Sweden by Allan Larsson, David Axmark, and Michael Widenius. Standard SQL (Structured Query Language) is used by MySQL to manage databases.

The standard language used to work with relational databases is SQL. RDBMSs recognize this collection of instructions, which let users build and administer relational databases. SQL expresses database queries and modifications and defines database schema. Tables can be created, altered, and dropped using DDL commands. Table data can be modified with SELECT, INSERT, DELETE, and UPDATE.

Here are some basic SQL commands for interacting with a MySQL database:

To create a database:

CREATE DATABASE database_name;

For example:

mysql> CREATE DATABASE School;

To select (open) a database to work with:

USE database_name;

For example:

mysql> USE school;

To create a table within the selected database:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
);

For example, creating an Employee table:

mysql> CREATE TABLE Employee (
    empID INTEGER,
    ename VARCHAR(30),
    city VARCHAR(25),  
    pay DECIMAL(10,2)
);

Or a mytable with a primary key:

CREATE TABLE mytable (
   id              int unsigned NOT NULL auto_increment,
   username        varchar(100) NOT NULL,
   email           varchar(100) NOT NULL,
   PRIMARY KEY     (id)
);

To insert records into a table:

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Or, if inserting values for all columns:

INSERT INTO table_name VALUES (value1, value2, ...);

String and Date type values must be enclosed in single or double quotes. For example:

mysql> INSERT INTO Employee VALUES (1,‘Amitabh’,‘Allahabad’,15000);

To retrieve data from a table:

SELECT column1, column2, ... FROM table_name WHERE condition;

To select all columns:

SELECT * FROM table_name;

For example, to select all records from the customers table:

SELECT * FROM customers;

Or to select specific columns from tblReceptionist:

SELECT FirstName, LastName FROM tblReceptionist;

To delete a table:

DROP TABLE table_name;

For example:

mysql> DROP TABLE Student;

This permanently deletes the table’s structure and data.

In conclusion, a database is a structured set of information. Software known as a database management system (DBMS) controls this data and conceals the complexity of data storage while enabling users to build, administer, and access databases. A particular kind of DBMS called an RDBMS, like MySQL, is used to handle relational databases, in which data is organized into related tables made up of rows (records) and columns (fields/attributes), connected by primary and foreign keys. SQL is the means of communication with an RDBMS.