Dealing with databases

What is a database?

A graphical image for a database

Simply, A database is an organized collection of structured information, or data, typically stored electronically in a computer system.

Designing a database

There are many ways you can design your database depending on what kind of database you are going to design. In general, Let’s see how to design a SQL database. There are 3 main to design a SQL database.

Those are:

1. Decide what are the objects you want to include in your database.
2. Determine which of these objects should be tables and which should be columns within those tables.
3. Define tables based on how you need to organize the objects.

Step 1: Define objects

As the first step in designing a database, you have to select important aspects of the system to include in the model. Treat each aspect as an object. List all the objects as you can think of.

When you are done, decide how these objects related to each other. Each object considered as an entity. And each entity has its own attributes. Some of the objects are major entities and other objects are subsidiary to those major entities.

Step 2: Identify tables and columns

Major entities become database tables. Its attributes become table columns. Many business databases, for example, consider a CUSTOMER table that keeps track of customers’ names, addresses, and other permanent information. Each attribute of a customer — such as name, street, city, state, etc —becomes a column in the CUSTOMER table.

It is better to find a set of rules to identify which objects should be tables and which attributes belong to which table.

Step 3: Define tables

Now you have to define tables for each entity and columns for each attribute. You can use either a rapid application development (RAD) tool or SQL’s Data Definition Language (DDL) to create tables.

SQL

All the data, that are organized and stored in databases, should be able to access. That is where SQL comes.

Introduction

SQL which stands for (Structured Query Language), is used to access and modify data stored in databases. It was developed by Donald D. Chamberlin and Raymond F. Boyce at IBM in the early 1970s. It was originally called SEQUEL (Structured English Query Language), but later it became SQL.

What does SQL do?

• Creating the database with SQL’s Data Definition Language (DDL).
• The main thing SQL does is accessing the data stored in databases.
• In a sense, SQL is the thing that stays in the middle and connects front-end with the back-end databases.

Commands

SQL contains many important commands to deal with data.

• SELECT — extracts data from a table
• DELETE — deletes data stored on a table
• CREATE DATABASE — creates a new database
• INSERT INTO — puts your data into a table
• ALTER DATABASE — makes changes to a database
• CREATE TABLE — creates a new table within a database
• CREATE INDEX — creates a new search key
• DROP — delete an existing table or a database

With these commands, you can easily modify and manipulate data as you want.

Examples:

CREATE DATABASE Students;

CREATE TABLE Student_Info(
stdID int,
name varchar(50),
address varchar(50),
city varchar(20),
phoneNumber int
);

INSERT INTO Student_Info VALUES(
001,
'student name1',
'address1',
'city1',
99345638906
);

INSERT INTO Student_Info VALUES(
002,
'student name2',
'address2',
'city2',
99343213459
);

SELECT name FROM Student_Info WHERE stdID=001;

SELECT * FROM Student_Info

Advantages

• High speed
• Portable
• No coding skills needed
• Easy to learn and understand
• Multiple data views

Disadvantages

• Difficult interface
• Some versions are costly
• Complete control is not given to the database

Why SQL is Very Popular

Businesses love SQL because it is a quick and efficient way of retrieving, viewing, and editing large volumes of data. StackOverflow report that SQL is the second most used programming language (JavaScript is first) and regularly used by 58% of full-stack developers, 53% of back-end developers, and 25% of front-end developers.

Physical databases

Physical databases are both the actual device housing the information files and the search paths used to access information between sources. They are technically smaller units of storage. A field is the smallest unit of storage housing only a single file.

As a summary, a physical database can be called as a refinement of the logical database design.

According to Microsoft, the term “database” refers only to the logical database controlling information files for the entire system.