Creating an employee database to set various constraints and

Creation of Views, Indexes, Save point.

Experiment No: 3(i)

CREATION OF EMPLOYEE DATABASE TO SET VARIOUS CONSTRAINTS

Aim
To create an employee database to set various constraints

CONSTRAINTS:

Constraints are used to specify rules for the data in a table. If there is any violation between the
constraint and the data action, the action is aborted by the constraint. It can be specified when the
table is created (using CREATE TABLE statement) or after the table is created (using ALTER
TABLE statement).

1. NOT NULL:
When a column is defined as NOTNULL, then that column becomes a mandatory column. It
implies that a value must be entered into the column if the record is to be accepted for storage in
the table.

Syntax:
CREATE TABLE Table_Name (column_name data_type (size) NOT NULL, );
Example:
CREATE TABLE student (sno integer(3)NOT NULL, name varchar(10));

2. UNIQUE:
The purpose of a unique key is to ensure that information in the column(s) is unique i.e. a value
entered in column(s) defined in the unique constraint must not be repeated across the column(s).
A table may have many unique keys.

Syntax:
CREATE TABLE Table_Name(column_name data_type(size) UNIQUE, ….);

Example:
CREATE TABLE student (sno integer(3) UNIQUE, name varchar(10));

3. CHECK:

Specifies a condition that each row in the table must satisfy. To satisfy the
constraint, each row in the table must make the condition either TRUE or unknown (due to a
null).

Syntax:
CREATE TABLE Table_Name(column_name data_type(size) CHECK(logical
expression), ….);

Example:
mysql> CREATE TABLE Persons (
-> ID int NOT NULL,
-> LastName varchar(255) NOT NULL,
-> FirstName varchar(255),
-> Age int,
-> CHECK (Age>=18)
 -> );
Query OK, 0 rows affected (0.09 sec)

4. PRIMARY KEY:

A field which is used to identify a record uniquely. A column or

combination of columns can be created as primary key, which can be used as a reference
from other tables. A table contains primary key is known as Master Table.
 iquely identify each record in a table.





Syntax:

CREATE TABLE Table_Name(column_name data_type(size) PRIMARY KEY,….);

Example:

CREATE TABLE faculty (fcode integer(3) PRIMARY KEY, fname varchar(10));

5. FOREIGN KEY:

The foreign key is used to link one or more than one table together. It is also known as
the referencing key. A foreign key matches the primary key field of another table. It means a
foreign key field in one table refers to the primary key field of the other table. It identifies each
row of another table uniquely that maintains the referential integrity in MySQL.

A foreign key makes it possible to create a parent-child relationship with the tables. In this
relationship, the parent table holds the initial column values, and column values of child table
reference the parent column values. MySQL allows us to define a foreign key constraint on the
child table.
MySQL defines the foreign key in two ways:

1. Using CREATE TABLE Statement

2. Using ALTER TABLE Statement

Syntax:

[CONSTRAINT constraint_name]
FOREIGN KEY [foreign_key_name] (col_name, ...)
REFERENCES parent_tbl_name (col_name,...)
ON DELETE referenceOption
ON UPDATE referenceOption

In the above syntax, we can see the following parameters:

constraint_name: It specifies the name of the foreign key constraint. If we have not provided
the constraint name, MySQL generates its name automatically.

col_name: It is the names of the column that we are going to make foreign key.

parent_tbl_name: It specifies the name of a parent table followed by column names that
reference the foreign key columns.

Reference_option: It is used to ensure how foreign key maintains referential integrity using ON
DELETE and ON UPDATE clause between parent and child table.

MySQL contains five different referential options, which are given below:

CASCADE: It is used when we delete or update any row from the parent table, the values of the
matching rows in the child table will be deleted or updated automatically.

SET NULL: It is used when we delete or update any row from the parent table, the values of the
foreign key columns in the child table are set to NULL.

RESTRICT: It is used when we delete or update any row from the parent table that has a
matching row in the reference(child) table, MySQL does not allow to delete or update rows in
the parent table.

NO ACTION: It is similar to RESTRICT. But it has one difference that it checks referential
integrity after trying to modify the table.

SET DEFAULT: The MySQL parser recognizes this action. However, the InnoDB and NDB
tables both rejected this action.

Example:
Table: customer

CREATE TABLE customer (

ID INT NOT NULL,
Name varchar(50) NOT NULL,
City varchar(50) NOT NULL,
PRIMARY KEY (ID)
);

Table: contact

CREATE TABLE contact ( ID INT, Customer_Id INT,

Customer_Info varchar(50) NOT NULL, Type varchar(50) NOT NULL,
CONSTRAINT fk_customer FOREIGN KEY (Customer_Id)
REFERENCES customer(ID)
ON DELETE CASCADE
ON UPDATE CASCADE
);

Defining integrity constraints in the alter table command:

Syntax:
ALTER TABLE Table_Name ADD PRIMARY KEY (column_name);

Example:
ALTER TABLE student ADD PRIMARY KEY (sno);

(Or)

Syntax:
ALTER TABLE table_name ADD CONSTRAINT constraint_name
PRIMARY KEY(colname)

Example:
ALTER TABLE student ADD CONSTRAINT SN PRIMARY KEY(SNO)
Dropping integrity constraints in the alter table command:

Syntax:
ALTER TABLE Table_Name DROP constraint_name;

Example:
ALTER TABLE student DROP PRIMARY KEY;
(or)
Syntax:
ALTER TABLE student DROP CONSTRAINT constraint_name;

Example:
ALTER TABLE student DROP CONSTRAINT SN;

6. DEFAULT :

The DEFAULT constraint is used to insert a default value into a column. The
default value will be added to all new records, if no other value is specified.

Syntax:

CREATE TABLE Table_Name(col_name1,col_name2,col_name3

DEFAULT ‘<value>’);

Example:
CREATE TABLE Persons1 (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);
Exercises-1

Create a table called EMP with the following structure.

Name Type

---------- ----------------------

EMPNO INTEGER(6)

ENAME VARCHAR(20)

JOB VARCHAR(10)

DEPTNO INTEGER(3)

SAL INTEGER(7)

Allow NULL for all columns except ename and job.

Q2. Add constraints to check, while entering the empid value (i.e) empid &gt; 100 and
implement not null

constraints.

Q3:Define the field DEPTNO as unique

Q4:Create a primary key constraint for the table(EMPNO).

RESULT

Thus the creation of employee database using various constraints has been executed
successfully
 Creating an employee database to set various constraints and
Creation of Views Indexes, Save point.
Experiment No: 3(ii)

CREATION OF VIEWS, INDEXING AND SAVEPOINT

Aim

To create a view, indexing and use savepoint in Mysql database.

MySQL View

A view is a database object that has no values. Its contents are based on the base table. It
contains rowsand columns similar to the real table. In MySQL, the View is a virtual
table created by a query by joining one or more tables. It is operated similarly to the base
table but does not contain any data of its own. The View and table have one main difference
that the views are definitions built on top of other tables (or views). If any changes occur in
the underlying table, the same changes reflected in the View also.

Syntax

CREATE [OR REPLACE] VIEW view_name AS

SELECT columns FROM tables

[WHERE conditions];

Indexes
 Indexing makes columns faster to query by creating pointers to where data is
stored within a database.

CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name

ON table_name (column1, column2, ...);

CREATE INDEX Example

The SQL statement below creates an index named "idx_lastname" on the "LastName" column in the "Persons" table:

CREATE INDEX idx_lastname

ON Persons (LastName);

Exercises-1

Consider the following

Q1: Create a name for view for the ‘courses’ table in databases.
Q2:Alter the already created VIEW name &quot;trainer&quot; by adding a new column.
Q3: Drop the existing VIEW by using the DROP VIEW statement.
Q4: The organization wants to display only the details of the employees those who are ASP.
Q5: The organization wants to display only the details like empno, empname,of the employees.
(Vertical
portioning)
Q6: Display all the views generated.
Q7: Execute the DML commands on the view created.
Q8: Drop a view.
Q9:create an index for the jobTitle column by using the CREATE INDEX statement.
Q10: Use savepoint command, Transaction update the age values of all the employees in the emp
table

Exercises-2

Consider the following

Q1: Display all the views generated.
Q2: Execute the DML commands on the view created.
Q3: Drop a view.
Q4:create an index for the jobTitle column by using the CREATE INDEX statement.
Q5: Use savepoint command, Transaction update the age values of all the employees in the emp
table

Animated videos for Ex. No - 3

S. No Topic Link Author

https://youtu.be/uPOGPL2C0_8
VL - 1 Constraints database Neso Academy
https://youtu.be/I_PrZ1NHZr8
VL - 2 Views Neso Academy
https://youtu.be/E--yzX05_k8
VL - 3 Indexes Gate Smashers

Concept videos for Ex. No - 1

S. No Topic Link Author
https://youtu.be/g6gdLN-7uaQ
VL - 1 Constraints database Develope with SJ
https://youtu.be/QngqhdLd1SE
VL - 2 Views Gate Smashers
https://youtu.be/tssM9hXw8xQ
VL - 3 Indexes Crack Concepts

RESULT

Thus the creation of view ,index and savepoint using employee database has been
executed successfully.