You think you are expert in the database then let’s have a look at basics to check and upgrade your knowledge.
Let’s get started
Staircase of Database
Databases are the primary form of storage in both today’s online and offline worlds. Databases are used to store millions of different types or combinations of information including Product Details, Employees, Personal Address Books, News, etc, which are unable to store otherwise. Before you can begin to design a database, however, you must understand the underlying concepts and theories of why databases are used and how they are created. This article will explain all of this and more. Let’s take a look at what a database is, the relational database model, metadata and indexes, the DBMS and MYSQL.
A relational database (RDB) is a collective set of multiple data sets organized by tables, records and columns. RDBs establish a well-defined relationship between database tables. Tables communicate and share information, which facilitates data searchability, organization and reporting.
RDBs use Structured Query Language (SQL), which is a standard user application that provides an easy programming interface for database interaction.
RDB is derived from the mathematical function concept of mapping datasets and was developed by Edgar F. Codd.
A relational database allows you to easily find specific information. It also allows you to sort based on any field and generate reports that contain only certain fields from each record. Relational databases use tables to store information. The standard fields and records are represented as columns (fields) and rows (records) in a table.
With a relational database, you can quickly compare information because of the arrangement of data in columns. The relational database model takes advantage of this uniformity to build completely new tables out of required information from existing tables. In other words, it uses the relationship of similar data to increase the speed and versatility of the database.
One or more data or record characteristics relate to one or many records to form functional dependencies. These are classified as follows:
- One to One: One table record relates to another record in another table.
- One to Many: One table record relates to many records in another table.
- Many to One: More than one table record relates to another table record.
- Many to Many: More than one table record relates to more than one record in another table.
RDB performs “select”, “project” and “join” database operations.Select is used for data retrieval, the project identifies data attributes, and join combines relations.
The “relational” part of the name comes into play because of mathematical relations. A typical relational database has anywhere from 0 to more than 1,000 tables. Each table contains a column or columns that other tables can key on to gather information from that table.
By storing this information in another table, the database can create a single small table with the locations that can then be used for a variety of purposes by other tables in the database. A typical large database, like the one a big Web site, such as Amazon would have, will contain hundreds or thousands of tables like this all used together to quickly find the exact information needed at any given time.
Relational databases are created using a special computer language, structured query language (SQL), that is the standard for database interoperability. SQL is the foundation for all of the popular database applications available today, from Access to Oracle.
SQL stands for Structured Query Language. It lets you access and manipulate databases. It is an ANSI (American National Standards Institute) standard.
- SQL can execute queries against a database. It can-
- retrieve data from a database.
- insert records in a database.
- update records in a database.
- delete records from a database.
- create new databases, new tables in a database.
- generate stored procedures in a database.
- create views in a database.
- set permissions on tables, procedures, and views.
Some queries from the beginning:
SHOW DATABASES; USE DATABASENAME; OR CREATE DATABASE NEWNAME; CREATE TABLE TABLENAME( COLUMN_1_NAME DATATYPE(SIZE), COLUMN_2_NAME DATATYPE(SIZE), COLUMN_3_NAME DATATYPE(SIZE), ……… COLUMN_m_NAME DATATYPE(SIZE)); DESC TABLENAME; INSERT INTO TABLENAME VALUES(VALUE_1 , VALUE_2 , VALUE_3 , …….VALUE_N);
IF VALUE ARE:-
Note: Varchar and char are same except char are of fixed length whereas varchar is of variable length.
To Be Continued…..