INFORMATION TECHNOLOGY�SUBJECT CODE – 802�Grade 12
UNIT 1
DATABASE CONCEPTS
AUTHOR: GAURAV KUMAR VASHISHT
BASIC CONCEPTS
Definition of Database
Spreadsheet v/s DBMS
Why database is needed?
For example
Data Base Management System (DBMS)
The various operations performed by DBMS:
1. Defining the Database: It involves specifying the data type of data that will be stored in the database and also any constraints on that data.
2. Populating the Database: It involves storing the data on some storage medium that is controlled by DBMS.
3. Manipulating the Database: It involves modifying the database, retrieving data or querying the database, generating reports from the database etc.
4. Sharing the Database: Allow multiple users to access the database at the same time.
5. Protecting the Database: It enables protection of the database from software/ hardware failures and unauthorized access.
6. Maintaining the Database: It is easy to adapt to the changing requirements.
Some examples of DBMS are – MySQL, Oracle, DB2, IMS, IDS etc.
Characteristics of DBMS
Types of Users of DBMS
Advantages of using DBMS Approach
Limitations of using DBMS Approach
Relational Database
Important Terms in Relational Database
(a) Name – Set of character strings representing names of persons.
(b) Employee_ID–Set of 4-digit numbers
(c) Gender – male or female
(d) Salary – Number
(e) Date_of_Birth – Should have a valid date, month and year in the format dd-mm-yyyy.
<Paras Bansal, 2134, Male, 25000, 19-10-1993>,
<Himani Verma, 3145, Female, 20000, 23-11-1992>}
How to remember Cardinality and Degree
Some More Characteristics of Relations:
Relational Model Constraints
Domain Constraint:
Null Value Constraint:
Super Key Constraint:
Primary Key
Null Value Constraint
Entity Integrity Constraint
Referential Integrity Constraint
Structured Query Language (SQL)
How to download MySQL
3. Once you have downloaded the file mysql-installer-community 5.6.20.0.msi, double click on the downloaded file and then click on the “Run” button.
4. MySQL Installer will start installing. Click on the “Install MySQL Products” option.
�5. Check the option “I accept the license terms” and then Click on “Next” button.
6. Then next click on the “Execute” button.
7. On successful execution, click on the “Next” button.
8. Select the “Server only” option. Then click on “Next” button.
9. Installer will check for the requirements. If any requirements are required, you have to download them first before installing MySQL server. If all the requirements are met, then the following message will be displayed. Click “Next” to continue.
10. Click on “Execute” to install MySQL Server 5.6.20.
11. On successful installation, Click on “Next”
12. Click on “Next” to start initial configuration.
13. Select the following configurations and then Click “Next”.
14. Type the MySQL root password (minimum 4 characters long) and then click “Next”
15. Click on “Next” on the following window.
16. Installer will configure the server. On successful configuration, click on “Next”.
17. Then Click on “Finish”. The installation and configuration of MySQL Server 5.6.20 is now complete. You can now start using the server for creating and modifying databases.
Create Table Command
CREATE TABLE<table name>
(
<column 1><data type> [constraint] ,
<column 2><data type>[constraint],
<column 3><data type>[constraint]
);
where [ ]=optional
CREATE TABLE Teacher
(
Teacher_ID INTEGER,
First_Name VARCHAR(20),
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2),
Date_of_Birth DATE,
Dept_No INTEGER
);
Teacher_ID | First_Name | Last_Name | Gender | Salary | Date_of_Birth | Dept_No |
| | | | | | |
Name of the Table: TEACHER
Commonly used Data types
Creating a Database in MySQL
CREATE DATABASE School;
SHOW DATABASES;
In case you want to remove a database
DROP DATABASE�
To tell the server which database we will use for further statements
SHOW TABLES
Database Constraints: NOT NULL
CREATE TABLE TEACHER
(
Teacher_ID INTEGER,
First_NameVARCHAR(20) NOT NULL,
Last_NameVARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2),
Date_of_Birth DATE,
Dept_No INTEGER
);
Database Constraints: DEFAULT
CREATE TABLE TEACHER
(
Teacher_ID INTEGER,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2) DEFAULT 40000,
Date_of_Birth DATE,
Dept_No INTEGER
);
If you want to look at the structure and description of the tables created, DESC command can be used
Database Constraints : CHECK
CREATE TABLE TEACHER
(
Teacher_ID INTEGER,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2) DEFAULT 40000,
Date_of_Birth DATE,
Dept_No INTEGER CHECK (Dept_No<=110)
);
Database Constraints : KEY CONSTRAINT
CREATE TABLE TEACHER
(
Teacher_ID INTEGER PRIMARY KEY,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2) DEFAULT 40000,
Date_of_Birth DATE,
Dept_No INTEGER
);
If you want to look at the structure and description of the tables created, DESC command can be used
By default, Primary keys are NOT NULL and there is no need to mention this
constraint separately.
COMPOSITE KEY
CREATE TABLE TEACHER
(
Teacher_ID INTEGER,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2) DEFAULT 40000,
Date_of_Birth DATE,
Dept_No INTEGER,
PRIMARY KEY (Teacher_ID, Date_of_Birth)
);
REFERENTIAL INTEGRITY CONSTRAINT
FOREIGN KEY
CREATE TABLE Department
(
Dept_ID INTEGER PRIMARY KEY,
Dept_Name VARCHAR(20) NOT NULL
);
CREATE TABLE Teacher
(
Teacher_ID INTEGER PRIMARY KEY,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2) DEFAULT 40000,
Date_of_Birth DATE,
Dept_No INTEGER,
FOREIGN KEY (Dept_No) REFERENCES Department(Dept_ID)
);
DESC Teacher;
CREATE TABLE Department�(�Dept_ID INTEGER PRIMARY KEY,�Dept_Name VARCHAR(20) NOT NULL�);
CREATE TABLE Teacher
(
Teacher_ID INTEGER PRIMARY KEY,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2) DEFAULT 40000,
Date_of_Birth DATE,
Dept_No INTEGER,
FOREIGN KEY (Dept_No) REFERENCES Department (Dept_ID) ON
DELETE SET NULL ON UPDATE SET NULL
);
Thus if a department with a given value of Dept_ID is deleted in Department table, then the corresponding tuples that contains the deleted value for Dept_No attribute in Teacher table would be set to NULL. Similarly, if Dept_ID value is updated then also the corresponding attribute in Teacher table would be set to NULL.
CREATE TABLE Teacher
(
Teacher_ID INTEGER PRIMARY KEY,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2) DEFAULT 40000,
Date_of_Birth DATE,
Dept_No INTEGER,
FOREIGN KEY (Dept_No) REFERENCES Department (Dept_ID) ON
DELETE CASCADE ON UPDATE CASCADE
);
In the above table, if a department with a given value of the Dept_ID attribute in Department table is deleted, then the corresponding rows in the Teacher table would also be deleted. However if Dept_ID value is updated in the Department table, the change in corresponding value is also reflected in Teacher Table.
CREATE TABLE Department�(�Dept_ID INTEGER PRIMARY KEY,�Dept_Name VARCHAR(20) NOT NULL�);
CREATE TABLE Teacher
(
Teacher_ID INTEGER PRIMARY KEY,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2) DEFAULT 40000,
Date_of_Birth DATE,
Dept_No INTEGER,
FOREIGN KEY (Dept_No) REFERENCES Department (Dept_ID) ON
DELETE RESTRICT ON UPDATE RESTRICT
);
RESTRICT option will reject the delete or update operation for the referenced table if there are one or more related foreign key values in a referencing table, i.e, you cannot delete or update a department if there are teachers who belong to that department.
CREATE TABLE Department�(�Dept_ID INTEGER PRIMARY KEY,�Dept_Name VARCHAR(20) NOT NULL�);
Self-Referencing Tables:
CREATE TABLE Employee
(
Employee_ID INTEGER PRIMARY KEY,
Name VARCHAR(30),
Age INTEGER,
Salary DECIMAL(10,2),
Manager_ID INTEGER,
FOREIGN KEY (Manager_ID) REFERENCES Employee (Employee_ID)
);
Drop Table Command:
Alter Table Command
Adding a column:
Dropping a column:
Altering a Column
Dropping keys
Adding a Constraint
Insert Command
CREATE TABLE Teacher
(
Teacher_ID INTEGER,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2) DEFAULT 40000,
Date_of_Birth DATE,
Dept_No INTEGER,
CONSTRAINT TEACHER_PK PRIMARY KEY (Teacher_ID),
);
INSERT INTO Teacher
VALUES (101,"Shanaya", "Batra", 'F', 50000, '1984-08-11', 1);
Another form of INSERT command
INSERT INTO Teacher (First_Name, Last_Name, Gender, Teacher_ID, Date_of_Birth, Dept_No, Salary)
VALUES ("Shanaya", "Batra", 'F', 101, '1984-08-11', 1, 50000);
INSERT INTO Teacher (First_Name, Last_Name, Gender, Teacher_ID, Date_of_Birth, Dept_No, Salary) VALUES ("Shanaya", "Batra", 'F', 101, '1984-08-11', 1,);
Update Command:
UPDATE Teacher
SET Salary=55000
WHERE Teacher_ID=101;
UPDATE Teacher
SET Salary=Salary+5000
WHERE Teacher_Name="Shanaya";
Delete Command:
DELETE FROM Teacher
WHERE Teacher_ID=101;
DELETE FROM Teacher;
Select Command
Syntax of
SELECT Command is as follows:
SELECT <attribute list>
FROM <table list>
WHERE <condition>
Query:
SELECT *
FROM Teacher
WHERE Teacher_ID=101;
Output
Query:
SELECT First_Name,Last_Name
FROM Teacher
WHERE salary > 50000;
Output:
Query:
SELECT Teacher_ID,First_Name, Last_Name, Dept_No
FROM Teacher
WHERE Dept_No = 4 OR Dept_No = 7;
Output:
Query:
SELECT *
FROM Teacher
WHERE Dept_No = 4 AND Gender =‘F’;
Output:
DISTINCT
Query:
SELECT Dept_No
FROM Teacher;
WHERE GENDER ='M';
Query:
SELECT DISTINCT Dept_No
FROM Teacher;
WHERE GENDER ='M';
Sometimes it is required to match part of the string. This is called as string pattern matching. We can use 'LIKE' keyword along with two more reserved characters - % (percent) and _ (underscore) for specifying different number of characters. % replaces zero or more number of random characters and _ replaces a single character.
SELECT First_Name
FROM Teacher
WHERE First_Name LIKE "S%";
Output:
Query: To retrieve names of all the teachers having 6 characters in the first name
and starting with 'S'.
SELECT First_Name
FROM Teacher
WHERE First_Name LIKE "S_ _ _ _ _";
Output:
Suppose it required to sort the result of a query based on some attributes. This can be achieved by using the clause – ORDER BY. (By default the order is ascending) For ascending order the keyword ASC and for descending order the keyword DESC is used. By default the order is ascending.
Output:
To list the names of teachers in alphabetical order.
SELECT First_Name, Last_Name
FROM Teacher
ORDER BY First_Name, Last_Name;
Query: To list the names of all the Departments in the descending order of their names.
SELECT Dept_Name
FROM Department
ORDER BY Dept_Name DESC;
Output:
Query: To retrieve the names and department numbers of all the teachers ordered by the Department number and within each department ordered by the names of the teachers in descending order.
SELECT First_Name, Last_Name, Dept_No
FROM Teacher
ORDER BY Dept_No ASC, First_Name DESC, Last_Name DESC;
Output:
To test whether a value is unavailable or unknown or not applicable in a column (i.e, NULL values), SQL allows us to test this condition using keywords IS NULL and IS NOT NULL.
Query: To retrieve all the details of those employees whose last name is not specified.
SELECT *
FROM Teacher
WHERE Last_Name IS NULL;
We can have another query in the WHERE clause of SQL query if the condition is based on the result of another query as shown below (The query is called a nested query):
Query: To retrieve the names of all the departments having female teachers.
SELECT DISTINCT Dept_Name
FROM Department
WHERE Dept_ID IN (Select Dept_No
FROM Teacher
WHERE Gender = 'F');
Result:
Between clause
Query:
SELECT First_Name
FROM Teacher
WHERE Salary
BETWEEN 40000 and 50000;
Result:
This is to note that the same query can also be written as:
SELECT First_Name
FROM Department
WHERE Salary >=40000 and Salary<=50000;
Important: in between clause both the values are counted.
IN clause
Query:
SELECT First_Name
FROM Teacher
WHERE Salary
IN (40000 , 50000);
Result:
Here only 40000 and 50000 value are seen. No other values are counted.
We could have also written the above query by using JOIN condition as shown below:
SELECT DISTINCT Dept_Name
FROM Department , Teacher
WHERE Department.Dept_ID = Teacher.Dept_No AND Gender='F';
Result:
Joining
Query:
SELECT First_Name, Last_Name, Dept_ID, Dept_Name
FROM Teacher, Department
WHERE Dept_ID=Dept_No;
Output:
Now suppose we have to retrieve the similar details for the teacher in Chemistry�department, the query would be:�SELECT First_Name, Last_Name, Dept_ID, Dept_Name�FROM Teacher, Department�WHERE Dept_ID=Dept_No AND Dept_name=”Chemistry”;
Output:
Suppose the teacher and department table both had same names for the department number, say Dept_ID as shown below:
CREATE TABLE Department
(
Dept_ID INTEGER PRIMARY KEY,
Dept_Name VARCHAR (30) NOT NULL
);
CREATE TABLE Teacher
(
Teacher_ID INTEGER,
First_Name VARCHAR(20) NOT NULL,
Last_Name VARCHAR(20),
Gender CHAR(1),
Salary DECIMAL(10,2) DEFAULT 40000,
Date_of_Birth DATE,
Dept_ID INTEGER,
CONSTRAINT TEACHER_PK PRIMARY KEY (Teacher_ID),
CONSTRAINT TEACHER_FK FOREIGN KEY (Dept_ID) REFERENCES
Department (Dept_ID)
);
SELECT First_Name, Last_Name
FROM Teacher, Department
WHERE Department. Dept_ID=Teacher. Dept_ID AND Dept_Name="Hindi";
Create Aliases
SELECT First_Name, Last_Name
FROM Teacher AS T, Department AS D
WHERE D.Dept_ID = T. Dept_ID AND Dept_Name="Hindi";
SELECT First_Name AS Fname, Last_Name AS Lname
FROM Teacher AS T, Department AS D
WHERE D.Dept_ID = T. Dept_ID AND Dept_Name="Hindi";
Output:
Previous Result
Aggregate Functions
To find total salary of all the teachers
SELECT SUM(Salary) AS Total_Salary
FROM Teacher;
Output:
To find the maximum and minimum salary.
SELECT MAX(Salary) AS Max_Salary, MIN(Salary) AS
Min_Salary
FROM Teacher;
Output:
To count the number of teachers earning more than Rs 40000.
SELECT COUNT(Salary)
FROM Teacher
WHERE Salary > 40000;
Output:
SELECT COUNT(Salary) AS Salary
FROM Teacher
WHERE Salary > 40000;
Output:
SELECT COUNT(Last_Name)
FROM Teacher;
Output:
SELECT COUNT(*)
FROM Teacher
Output:
(*) Asterisk symbol is used to count the number of rows in the result of the query.
Count(Last_Name)
12
Count(Last_Name)
14
SELECT AVG(Dept_No)
FROM Teacher;
Output:
SELECT AVG(Dept_No)
FROM Teacher
Output:
AVG(Dept_No)
4.57
If null is there in place of 2
AVG(Dept_No)
4.76
64/14 = 4.57
62/13 = 4.76
We can also use arithmetic operators in the SELECT clause. For example, if we want to display Teacher name, current salary and a 10% increase in the salary for those teachers who belongs to Department number 4, the SELECT statement can be written as shown below:
SELECT First_Name, Last_Name, Salary, Salary*1.1 AS New_Salary
FROM Teacher WHERE Dept_No = 4;
For permanent change:
Update TEACHER set Salary = Salary + Salary *10/100 where Dept_No = 4;
Grouping based on an attribute can be done in SQL. For such grouping, GROUP BY clause is added in the SQL query.
For example, we have to find the number of teachers teaching in each Department. Thus we have to group the result based on the Departments and for each Department we have to count number of teachers who teach in that Department. This query is written by using GROUP BY clause and aggregate function as shown below:
Output:
SELECT Dept_No, COUNT(*) AS No_of_Teachers
FROM Teacher
GROUP BY Dept_No;
The above result can be enhanced if we display the name of Departments also as shown below:
SELECT Dept_No, Dept_Name, COUNT(*) AS No_of_Teachers
FROM Teacher, Department
WHERE Dept_ID = Dept_No
GROUP BY Dept_No;
Result:
It is also possible to apply some condition on the group. This condition will not come under the WHERE clause, but in a new clause HAVING.
For example, we have to find those departments which have more than one teacher.
SELECT Dept_No, Dept_Name, COUNT(*) AS No_of_Teachers FROM Teacher, Department
WHERE Dept_No=Dept_ID
GROUP BY Dept_No HAVING COUNT(*) > 1;
Result:
A Select command can also result in an empty set.
For example, retrieve the name of Teacher with ID=115. Since there is no such teacher in the Teacher table, following query results in an empty set.
SELECT *
FROM Teacher
WHERE Teacher_ID = 115;
Result:
Empty set
END OF THE CHAPTER
Q1.Consider the following Employee table:
The primary key of this table is Employee_ID and Manager_ID is a foreign key that references Employee_ID.
Write SQL commands for the following: