SQL (Structure query language) is mainly termed as a domain-specific language. A domain specific language is a specialized computer language used for a particular domain. rdbms concepts are very important to get our basics and vision clear. RDBMS uses SQL language for stream processing. SQL is a set based, and a decorative programming language, and is not like C or basic. Programs like C or basic are Imperative programming language. A DBMS manages the database. MYSQL is a relational database management system. Records and other values are sorted over several tables instead of sorting all the items in a single table. MYSQL is moderately well known for light website application program .MySQL uses the standardized Structured Query Language (SQL) to manage the database.
Throughout so many years SQL has gone through number of changes:
Year | Name | Alias |
1986 | SQL-86 | SQL-87 |
1989 | SQL-89 | FIPS 127-1 |
1992 | SQL-92 | SQL2, FIPS 127-2 |
1999 | SQL:1999 | SQL3 |
2003 | SQL:2003 | |
2006 | SQL:2006 | |
2008 | SQL:2008 | |
2011 | SQL:2011 | |
2016 | SQL:2016 |
SQL RDBMS CONCEPTS:
What is a table?
The simplest form of data storage in a relational database is a “table”. Tables are the database objects, and information in RDBMS are stored in this database objects. Table is a collection of data distributed over rows, and columns.
Following figure represents a table in a RDBMS (relational database management system) system:
[Table name: padhle]
ShipperID | ShipperName | Phone |
1 | amazon | 100200300400 |
2 | Speedy | 400300200100 |
3 | fedex | 200100400300 |
Table mentioned above has following features:
- Column name: Shipper ID, ShipperName, Phone
- Row 1: 1 , amazon , 100200300400
Row 2; 2, speedy, 400300200100
Row3: 3, fedex, 200100400300
Every table has its own unique Features , and is defined by a name which makes it different and adds uniqueness to it. [for example- Table names-{ “harsh”, “padhle”,”padhleisbest”……..}].
What is a Field?
Every table has a set of data, and a table is broken up into entities called fields and record. , field in a table is created to hold specific information about every record in the table.
ShipperID | ShipperName | Phone |
Field in the following Tables are : ShipperId , ShipperName , Phone
What is a Record or a Row?
Row of data in a table is called as record. Synonym of row is record, and a record is a horizontal entity in a table.[Record–>Row].
3 | fedex | 200100400300 |
Above diagram is representing a record with values-” 3″,”fedex”,”200100400300″.The following Table consist of ‘3’ records/rows.
Row1: Row is a horizontal entity , and {1, amazon , 100200300400} are the values present in row 1.
1 | amazon | 100200300400 |
Row2: Row ‘2’ is a horizontal entity , and {1, amazon , 100200300400} are the values present in row 2.
2 | Speedy | 400300200100 |
Row3: Row ‘3’ is the last row of the table , and {1, amazon , 100200300400} are the values present in row 3.
3 | fedex | 200100400300 |
Number of Rows in table padhle:3
What is a column?
Vertical entity in a table is termed as column, and it contains all the information associated with a specific field in a table. For example, a column in padhle is –Shippername, which contains all the vertical values related to the field.[Column: Shippername,phone,ShipperId]
Shippername |
Amazon |
Speedy |
Fedex |
Column1:Shippername
Number of Columns present in table padhle: 3
What is a NULL value?
A Null value represents a field that appears to be blank; no value is assigned to the field. Null values are different from ‘0’. Fields with values ‘0’ is not considered as a Null value, but a field which has been left blank is considered to be a Null Value.
For example: A record is left empty , and no values has been assigned to it. In following example no values are assigned to Shipper ID, Shippername,Phone
Fields—>Blank—–>Null Value
ShipperID | ShipperName | Phone |
– | – | – |
SQL Constraints:
Data columns in a Table are bounded with set of rules, and these rules are called as Constraints. Constraints are used to check and limit the type of data that can enter the table. Constraints are used to ensure accuracy and maintain reliability in the database.
Following are some commonly used constraints in SQL:
- DEFAULT Constraint: A default value for a column is specified
- NOT NULL Constraint: It Is added to ensure that column in a table cannot have a NULL value.
- UNIQUE Constraint: Ensures that values in a column are different (Unique).
- Primary Key: Primary key is chosen By a Database Administrator (DBA), and it uniquely identifies record in a table.
- FOREIGN Key: Same function as primary key but it uniquely identifies a record in another table
- CHECK constraint: Check constraint matches the column values with conditions , and ensure that values in column satisfy the conditions
- INDEX: INDEX Constraint reduces the time for retrieving data from a database.
Data Integrity
The following types of Integrity are present in RDBMS
- Entity Integrity means No duplicate record in the table
- Domain Integrity which restricts type, format,and also range of values for a column
- Referential Integrity means rows cannot be deleted if they are used by other records
- User-Defined Integrity which is useful in enforcing business rules
Hope that this article would have helped you to understand the basics of rdbms concepts ,and if you have any doubts then drop it in the comments section down below.