Unit 3: Introduction to Relational database, SQL and Relational Model Basics
Relational Database
The database which stores the data in tabular format of rows and columns, like spreadsheet is known as Relational database. In other words, relational database is a collection of organized set of tables from which data can be accessed easily. A software used to maintain relational databases is a relational database management system (RDBMS). Relational Database is most commonly used database. It consists of number of tables and each table has its own primary key
Integrity Constraints
Data integrity refers to the consistency and validity of data stored in database. Data integrity is preserved by using rules called integrity constraints. Thus, the rules that ensures the validity and consistency of the data stored in database are called integrity constraints. They may apply to each attribute or relationship between tables. These constraints are checked before performing any operation (insertion, deletion, updation) in database. Thus, integrity constraints guard against accidental damage to the database.
The different types of integrity constraints are:
· Entity integrity constraint: Entity integrity constraint states that the primary key attribute in a relation, should not accept a null value. This is because the primary key attribute value uniquely defines an entity in a relation. So, it being null would not work. Example: If we have a customer database and customer_table is present there with attributes like age and name. Then each customer should be uniquely identified. There might be two customers with the same name and same age, so there might be confusion while retrieving the data. If we retrieve the data of customer named 'Angel' then two rows are having this name and there would be confusion. So, to resolve this issues primary keys are assigned in each table and it uniquely identifies each entry of the table.
· Domain integrity constraint: A domain integrity constraint restricts the kind of values an attribute can hold in the database table. For example, we can specify if a particular column can hold null values or not, if the values have to be unique or not, the data type or size of values that can be entered in the column, the default values for the column, range of values for column etc. Example: If we have to store the salary of the employees in the 'employee_table' then we can put constraints that it should only be an INTEGER. Any entry other than integer like characters would not be acceptable and when we try to give input like this, the DBMS will produce errors.
· Referential integrity constraint: Referential Integrity is used to maintain the data consistency between two tables. The referential integrity is an integrity constraint that state that if a foreign key in the first table refers to the primary key of the second table then every value of foreign key in the first table should either be null or present in the second table. Example: Let us suppose we have two tables, student(student_id, name, age, course_id) and course(course_id, course_name, duration). Now, if any course_id is present in the student table which is not there in the course table then this is not allowed. The course_id in the student table should either be null or if any course_id is present in the student table then it should also be present in the course table. This is how referential integrity is maintained.
Constraints in RDBMS
Types of constraints in table
• NOT NULL
• UNIQUE
• DEFAULT
• CHECK
• Key Constraints – PRIMARY KEY, FOREIGN KEY
• Domain constraints
• Mapping constraints
NOT NULL:
NOT NULL constraint makes sure that a column does not hold NULL value. When we don’t provide value for a particular column while inserting a record into a table, it takes NULL value by default. By specifying NULL constraint, we can be sure that a particular column(s) cannot have NULL values.
UNIQUE:
UNIQUE Constraint enforces a column or set of columns to have unique values. If a column has a unique constraint, it means that particular column cannot have duplicate values in a table.
DEFAULT:
The DEFAULT constraint provides a default value to a column when there is no value provided while inserting a record into a table.
CHECK:
This constraint is used for specifying range of values for a particular column of a table. When this constraint is being set on a column, it ensures that the specified column must have the value falling in the specified range.
PRIMARY KEY:
Primary key uniquely identifies each record in a table. It must have unique values and cannot contain nulls. In the below example the ROLL_NO field is marked as primary key, that means the ROLL_NO field cannot have duplicate and null values.
SQL (Structure Query Language)
SQL is a standard language for storing, manipulating, and retrieving data in database. As the name suggests, SQL is used to manage the structured databases like RDBMS. Some of the Relational Database Management Systems like Oracle, MS Access, MySQL, etc. uses SQL for all the operations on the database. It is a user-friendly and domain-specific language.
Advantages/Importance:
· Faster Query Processing – Large amount of data is retrieved quickly and efficiently. Operations like Insertion, deletion, manipulation of data is also done in almost no time.
· No Coding Skills – For performing different operations in database, large number of lines of code is not required. All basic keywords such as SELECT, INSERT INTO, UPDATE, etc. are used and also the syntactical rules are not complex in SQL, which makes it a user-friendly language.
· Standardized Language – Due to documentation and long establishment over years, it provides a uniform platform worldwide to all its users.
· Portable – It can be used in programs in PCs, server, laptops independent of any platform (Operating System, etc.). Also, it can be embedded with other applications as per need/requirement/use.
· Interactive Language – Easy to learn and understand, answers to complex queries can be received in seconds.
· Multiple data view – Using the SQL language, the users can make different views of the database structure.
Features of SQL:
· SQL allows users to access data from relational database management systems.
· SQL allows users to describe the data.
· SQL allows users to create and drop the database and table.
· SQL allows users to create a view, stored procedure, function in a database.
· SQL allows users to set permissions on tables, procedures, and views.
· SQL allows to embed with other languages.
Note: Embedded SQL is a method of inserting inline SQL statements or queries into the code of a programming language
Types of SQL statements
There are various types of SQL statements/commands that are used for various different processes. These are:
DDL (Data Definition Language): DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema.[ It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects (such as tables, indexes) in the database.] DDL is a set of SQL commands used to create, modify, and delete database structures but not data. These commands are normally not used by a general user, who should be accessing the database via an application. All DDL commands are auto-committed. That means it saves all the changes permanently in the database. List of DDL commands:
· CREATE: This command is used to create the database or its objects (like table, index, function, views, store procedure, and triggers).
· DROP: This command is used to delete objects from the database.
· ALTER: This is used to alter the structure of the table.
· TRUNCATE: This is used to remove all records from a table, including all spaces allocated for the records are removed.
· COMMENT: This is used to add comments to the data dictionary.
· RENAME: This is used to rename an object existing in the database.
DML (Data Manipulation Language): The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements. DML commands are not auto-committed. It means changes are not permanent to database, they can be rolled back. List of DML commands:
· 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 records from a database table.
· LOCK: Table control concurrency.
· SELECT: it is not dml but we found somewhere it.
DCL (Data Control Language): It provides additional feature for security of table. DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system. List of DCL commands:
· GRANT: This command gives users access privileges to the database.
· REVOKE: This command withdraws the user’s access privileges given by using the GRANT command.
Transaction Control Language (TCL): TCL commands deal with the transaction within the database. Transaction Control Language or TCL is used to manage the changes that are made by the DML commands List of TCL commands:
· COMMIT: Commits a Transaction (to permanently save).
· ROLLBACK: Rollbacks a transaction in case of any error occurs.
· SAVEPOINT: Sets a save point within a transaction.
· SET TRANSACTION: Specify characteristics for the transaction.
DQL (Data Query Language): DQL is used to fetch the data from the database. List of DQL:
· SELECT: It is used to retrieve data from the database.
DML | DCL |
The SQL commands that deals with the manipulation of data present in the database belong to DML | Commands that mostly concerned with rights, permissions and other controls of the database system belongs to DCL. |
Stands for Data Manipulation language | Stands for Data control language |
Insert, update, delete are some DML commands | GRANT, REVOKE are some DCL commands. |
It has two classifications: Procedural and Non procedural DMLs. | It has no classification. |
CRUD
The CRUD stands for create, read, update and delete. These are the most common operations performed in any database.
Four commands used in database ( SEE sup. 2073)
1. Create operation: In CRUD operations, 'C' is an acronym for create, which means to insert a record in the table. So, firstly we will create a table using CREATE command and then we will use the INSERT INTO command to insert rows in the created table. The SQL supports create operation with INSERT command.
Syntax: CREATE TABLE table_name( column1 datatype , columnN datatype);
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Note: We can also create database using create command, for example CREATE DATABASE SCHOOL;
2. Read Operation: In CRUD operations, 'R' is an acronym for read, which means retrieving or fetching the data from the SQL table. SQL supports read operation with SELECT command. We can retrieve all the records from a table using an asterisk (*) in a SELECT command. There is also an option of retrieving only those records which satisfy a particular condition by using the WHERE clause in a SELECT command.
Syntax: SELECT column1, column2.. FROM Table_name;
OR, SELECT * FROM Table_name;
OR, SELECT * table_name FROM WHERE condition;
OR,SELECT DISTINCT field_name FROM table_name; {only select non redundant value}
3. Update Operation: In CRUD operations, 'U' is an acronym for the update, which means making updates to the records present in the SQL tables. SQL supports update operation with UPDATE command.
Syntax: UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
4. Delete Operation: In CRUD operations, 'D' is an acronym for delete, which means removing or deleting the records from the tables. SQL supports the delete operation with delete command. We can delete all the rows from the SQL tables using the DELETE command. There is also an option to remove only the specific records that satisfy a particular condition by using the WHERE clause in a DELETE command.
Syntax: DELETE FROM table_name WHERE condition;
OR, DELETE FROM table_name ;
Join in SQL
A JOIN is a clause in SQL used to combine rows from two or more tables, based on a related column between them. Minimum required condition for joining tables is (n-1) where n is the number of tables. The different types of join are explained below.
1. INNER JOIN: The inner join returns records that have matching values in both tables. In other words, this keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e. value of the common field will be same.
Syntax:
SELECT table1.column1, table1.column2,table2.column1,....
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;
2. LEFT (OUTER) JOIN: This join returns all records from the left table (table1), and the matching records from the right table (table2). The rows for which there is no matching row on right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER JOIN.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,…
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
QL LEFT OUTER Join Example Using the Select Statement
The first table is Purchaser table and second is the Seller table. The first table contains the list of the purchaser tables
Table 1: Purchaser
Purchaser_ID | Purchaser_Name | Plot_No | Service_Id |
1 | Sam | 12 | 1001 |
2 | Pill | 13 | 1002 |
3 | Don | 14 | 1003 |
4 | Brock | 15 | 1004 |
The second table is the table contains the list of sellers.
Table 2: Seller
Id | Seller_Name | Seller_Email |
1001 | Big Show | big@gmail.com |
1002 | Gem | gem@gmail.com |
1003 | Matt | matt@gmail.com |
Now, let’s combine the above two tables using the example given below.
Example
1 2 3 4 | SELECT Service_Id, Seller_Name, Purchaser_Name, Plot_No FROM Purchaser LEFT JOIN Seller ON Purchaser.Service_Id = Seller.Id |
Output
Service_Id | Seller_Name | Purchaser_Name | Plot_No |
1001 | Big Show | Sam | 12 |
1002 | Gem | Pill | 13 |
1003 | Matt | Don | 14 |
1004 | Null | Brock | 15 |
3. RIGHT (OUTER) JOIN: The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). . The rows for which there is no matching row on left side, the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,…
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;
4. FULL (OUTER) JOIN: FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for which there is no matching, the result-set will contain NULL values.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,…
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;
0 Comments