3UNIT. Introduction To Relational Database, SQL and Relational Model Basics P re s e n t e d b y B i ka s h C h h e t r i

(1)

3

UNIT

Introduction To

Relational Database, SQL and Relational Model Basics

P r e s e n t e d b y B i k a s h C h h e t r i

(2)

3

UNIT

Content

3 . 1 I n t r o d u c t i o n t o E R m o d e l 3 . 2 I n t e g r i t y C o n s t r a i n t s

3 . 3 R e f e r e n t i a l I n t e g r i t y 3 . 4 S t r u c t u r e Q u e r y

L a n g u a g e ( S Q L ) 3 . 4 . 1 C R U D

3 . 4 . 2 J o i n

3 . 4 . 2 . 1 L e f t J o i n 3 . 4 . 2 . 2 R i g h t J o i n 3 . 4 . 2 . 3 I n n e r J o i n 3 . 4 . 2 . 4 O u t e r J o i n

3 . 5 D M L , D D L , D C L C o n c e p t

(3)

3.1 Introduction To ER Model

D a t e : 2 0 2 0 / 0 8 / 1 9

ER Model:

An ER model is a visual representation of different entities within a system and how they relate to each other.

Add a footer 3

(4)

FR

Relational Database

Relational Database:

It is defined as a group of

independent tables which are linked to each other using some common field of each related tables.

Add a footer 4

(5)

FR

RDBMS

Add a footer 5

S.NO. ER MODEL RDBMS

1.

ER model is a high level data model which tells us about the relationship between the entities.

RDBMS is a database management system which has a collection of tables which are related to each other.

2.

ER model is easier to

understand as it is in pictorial form.

RDBMS is comparatively more complex than ER model.

3.

ER model is made before the construction of database to get an idea of the database.

RDBMS is an implementation of the ER diagram.

4.

ER model has attributes, entities and relationships as its main components.

RDBMS has tuples, domains, keys as its main components.

5. ER model has mapping

cardinalities.

RDBMS doesn’t have mapping cardinalities.

7.

It requires comparatively less skillful people to construct the ER model.

It requires more skillful people to deal with the database systems.

(6)

FR

RDMS

1. RDBMS is a database management system which has a collection of tables which are related to each other.

2. RDBMS is comparatively more complex than ER model.

Add a footer 6

(7)

FR

Constraints

C o n s t r a i n t s a r e t h e r u l e s e n f o r c e d o n t h e d a t a c o l u m n s o f a t a b l e a n d u s e d t o l i m i t t h e t y p e o f d a t a t h a t c a n g o i n t o a t a b l e a l s o e n s u r e s t h e a c c u r a c y a n d r e l i a b i l i t y o f t h e d a t a i n t h e d a t a b a s e .

F o l l o w i n g a r e t h e m o s t u s e d c o n s t r a i n t s t h a t c a n b e a p p l i e d t o a t a b l e : 1 . N O T N U L L C o n s t r a i n t − E n s u r e s t h a t a c o l u m n c a n n o t h a v e N U L L v a l u e .

2 . D E FA U LT C o n s t r a i n t − P r o v i d e s a d e f a u l t v a l u e f o r a c o l u m n w h e n n o n e i s s p e c i f i e d .

3 . U N I Q U E C o n s t r a i n t − E n s u r e s t h a t a l l v a l u e s i n a c o l u m n a r e d i f f e r e n t . 4 . P R I M A R Y K e y − U n i q u e l y i d e n t i f i e s e a c h r o w / r e c o r d i n a d a t a b a s e t a b l e .

5 . F O R E I G N K e y − U n i q u e l y i d e n t i f i e s a r o w / r e c o r d i n a n y o f t h e g i v e n d a t a b a s e t a b l e .

6 . C H E C K C o n s t r a i n t − T h e C H E C K c o n s t r a i n t e n s u r e s t h a t a l l t h e v a l u e s i n a c o l u m n s a t i s f i e s c e r t a i n c o n d i t i o n s .

7 . I N D E X − U s e d t o c r e a t e a n d r e t r i e v e d a t a f r o m t h e d a t a b a s e v e r y q u i c k l y.

Add a footer 7

(8)

FR

Add a footer 8

3.2 Integrity Constraints

• Integrity constraints are a set of rules. It is used to maintain the quality of information.

• Integrity constraints ensure that the data insertion, updating, and other processes have to be performed in such a way that data integrity is not affected.

• Thus, integrity constraint is used to guard against accidental damage to the database.

(9)

FR

Add a footer 9

Domain Integrity Constraints

• Domain constraints can be defined as the definition of a valid set of values for an attribute.

• The data type of domain includes string, character, integer, time, date, currency, etc.

The value of the attribute must be available in the corresponding domain.

Example:-

(10)

FR

Add a footer 10

Entity integrity constraints

• The entity integrity constraint states that primary key value can't be null.

• This is because the primary key value is used to identify individual rows in relation and if the primary key has a null value, then we can't identify those rows.

• A table can contain a null value other than the primary key field.

Example:-

(11)

FR

Add a footer 11

3.3 Referential Integrity Constraints

• A referential integrity constraint is specified between two tables.

• In the Referential integrity constraints, if a foreign key in Table 1 refers to the

Primary Key of Table 2, then every value of the Foreign Key in Table 1 must be null or be available in Table 2.

(12)

FR

Add a footer 12

Key constraints

• Keys are the entity set that is used to identify an entity within its entity set uniquely.

• An entity set can have multiple keys, but out of which one key will be the primary key. A primary key can contain a unique and null value in the relational table.

Example:-

(13)

FR

Add a footer 13

3.4 Structure Query Language

Q. Definition of SQL?

1. SQL stands for structure query language used for storing and managing data in relational database management system(RDMS).

2. It is a standard language for relational database system.

3. It enables a user to create, read, update and delete relational database and tables.

4. SQL allows user to query the database in number of ways, using English like statements.

(14)

FR

Add a footer 14

SQL Datatype

• 1. SQL Datatype is used to define the values that a column can contain.

• 2. Every column is required to have a name and data type in the database table.

(15)

FR

Add a footer 15

SQL Command

• 1. SQL commands are instructions. It is used to communicate with the database. It is also used to perform specific tasks, functions, and quires of data.

• 2. SQL can perform various tasks like create a table, add data to tables, drop the table, modify the table, set permission for users.

(16)

FR

Add a footer 16

Types of SQL Command

SQL Commands

DCL TCL DQL

(17)

FR

Add a footer 17

3.4.1 CRUD

CRUD means Create, Read, Update, Delete, and it can mean different things in different systems, but for SQL Server, it is commonly considered to map to the following SQL operations on table records.

CRUD SQL

C - Create Insert

R - Read Select

U - Update Update

D - Delete Delete

(18)

FR

Add a footer 18

1. Create: Insert into Mobile (name, price)

2. Read: select * from Mobile where name in ('A', 'B')

3. Update: update p set p.Price = p.Price * 0.05 from Mobile where name in ('A', 'B');

update Product A and B to increase Price by 5%

4. Delete: delete from Mobile where name = 'B'

(19)

FR

Add a footer 19

3.4.2 JOIN

It is used to fetch data from two or more tables which is joined to appear as a single set of data.

Types of JOIN:-

(Inner Join)

(20)

FR

Add a footer 20

3.4.2.4 Outer Join

The outer join operation is an extension of the join operation. It is used to deal with missing information.

Example:

Employee

EMP_NAME STREET CITY

Manisha Nayapath Butwal

Rina Sangam path Chitwan

Ravi M.G street Kathmandu

Hari Civil line Kathmandu

(21)

FR

Add a footer 21

EMP_NAME BRANCH SALARY

Manisha Infosys 10000

Rina Wipro 20000

Shiva HCL 30000

Hari TCL 50000

FACT_WORKERS

INPUT: EMPLOYEE ⋈ FACT_WORKERS

OUTPUT EMP_NAME STREET CITY BRANCH SALARY

Manisha Nayapath Butwal Infosys 10000

Rina Sangampath Chitwan Wipro 20000

Hari Civil line Kathmandu HCL 50000

(22)

FR

Add a footer 22

3.4.2.1 Left Join

Left outer join contains the set of tuples of all combinations in R and S that are equal on their common attribute names.

In the left outer join, tuples in R have no matching tuples in S.

It is denoted by .

Example: Using the above EMPLOYEE table and FACT_WORKERS Table

Input:

1. EMPLOYEE FACT_WORKERS

(23)

FR

Add a footer 23

EMP_NAME STREET CITY BRANCH SALARY

Ravi MG Street Kathmandu NULL NULL

Hari Civil Line Kathmandu TCL 50000

(24)

FR

24

3.4.2.2 Right outer join

Right outer join contains the set of tuples of all combinations in R and S that are equal on their common attribute names.

In the right outer join, tuples in S have no matching tuples in R It is denoted by

Example: Using the above EMPLOYEE table and FACT_WORKER Relation.

Input:

1. EMPLOYEE FACT_WORKER

(25)

FR

25

OUTPUT:

Shiva NULL NULL HCL 30000

Hari Civil Line Kathmandu TCL 50000

(26)

FR

Add a footer 26

FULL Scale

Full outer join is like a left or right join except that it contain all rows from both tables.

In full outer join, tuples in R that have no matching tuples in S and tuples in S that have no matching tuples in R in their common attributes name.

It is denoted by

Example: Using the above EMPLOYEE table and FACT_WORKERS table

INPUT:

1. EMPLOYEE FACT_WORKERS

(27)

FR

27

Hari Civil line Kathmandu TCL 50000

Ravi M.G Street Kathmandu NULL NULL

Shiva NULL NULL HCL 30000

OUTPUT:

(28)

FR

28

3.4.2.3 Inner Join

It is also known as equi join. It is the most common join.

It is based on matched data as per the equality condition.

The equi join uses the comparison operator(=).

Example:

CUSTOMER RELATION

CLASS_ID NAME

1 John

2 Harry

3 Jackson

(29)

FR

29

PRODUCT

PRODUCT_ID CITY

1 Delhi

2 Mumbai

3 Noida

INPUT

CUSTOMER ⋈ PRODUCT

CLASS_ID NAME PRODUCT_ID CITY

1 John 1 Delhi

2 Harry 2 Mumbai

3 Jackson 3 Noida

(30)

FR

30

Syntax^:

1. Inner Join: SELECT Coiumn_name list FROM Table_name1 Inner Join table_name2 Where Table_name1.Column_name = Table_name2.Column_name;

for eg, SELECT *from CUSTOMER INNER JOIN PRODUCT WHERE CUSTOMER.CLASS_ID = PRODUCT.PRODUCT_ID

(31)

FR

Add a footer 31

1. Data Definition Language(DDL)

• DDL changes the structure of the table like creating a table, deleting a table, altering a table etc.

• All the command of DDL are auto- committed that means it permanently saved all the changes in the database.

• Here are some commands that come under DDL

• CREATE

• ALTER

• DROP

• TRUNCATE

(32)

FR

32

Create

: It is used a new table in the database.

Syntax:

CREATE TABLE TABLE_NAME(COLUMN_NAME DATA TYPES[…….]):

Example:

CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100), DOB DATE);

DROP

: It is used to delete both the structure and record stored in the table.

Syntax

DROP TABLE;

Example:

DROP TABLE EMPLOYEE;

(33)

FR

Add a footer 33

ALTER: It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing or probably to add new attribute.

Syntax:

To add a new column in the table

ALTER TABLE table_name ADD column_name COLUMN-definition;

Example:

ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20);

TRUNCATE : It is used to delete all the rows from the table and free the space containing the table.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE EMPLOYEE;

(34)

FR

Add a footer 34

2. Data Manipulation Language

• DML commands are used to modify the database. It is responsible for all form of changes in the database.

• The commands of DML is not auto-committed that means it can’t permanently save all the changes in the database. They can be roll back.

• Here are some commands that comes under DML

• INSERT

• UPDATE

• DELETE

(35)

FR

35

INSERT: The INSERT Statements in SQL query. It is used to insert data into the row of a table.

Syntax:

1. INSERT INTO TABLE NAME 2. (col1, col2, col3……..colN)

3. VALUES(Value1, value2, value3,……..valueN);

OR

1. INSERT INTO TABLE_NAME

2. VALUES(value1, value2, value3. …….valueN);

For eg: INSERT INTO Javapoint(Author, Subject) VALUES (“sono”, “DBMS”);

(36)

FR

Add a footer 36

DELETE: It is used to remove one or more row from a table.

SYNTAX:

DELETE FROM table_name[ WHERE condition ];

For example:

DELETE FROM Javapoint WHERE Author = “Sonoo”;

(37)

FR

37

Data Control Languages

• DCL Commands are used to grant and take back authority from any database user.

• Here are some commands that come under DCL 1. GRANT

2. REVOKE

GRANT: It is used to give user access privileges to a database.

Example:

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

(38)

FR

Add a footer 38

REVOKE: It is used to take back permission from the user.

For Example:

REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;