Gnanamani College of Technology

Accredited by NBA , Namakkal- 637018

DEPARTMENT OF ARTIFICIAL INTELLIGENCE & DATA

SCIENCE LABORATORY SYLLABUS
NAME OF THE SUBJECT : DATABASE DESIGN AND MANAGEMENT LABORATORY
SUBJECT CODE: AD3381 SEMESTER -III
COURSE OUTCOMES
Students will be able to
CO1: Understand the Database Development Life Cycle.
CO2: Design Relational Database Using Conceptual-to-Relational Mapping, Normalization.
CO3: Apply Sql for Creation, Manipulation and Retrieval of Data.
CO4: Develop a Database Application for Real-Time Problems.
CO5: Design and Query Object-Relational Databases.

LIST OF EXPERIMENTS:-
Exp.No Name of Experiments CO’s BL
Database Development Life cycle:
1 Problem definition and Requirement CO1 L4
analysis Scope and Constraints
Database design using Conceptual modeling (ER-EER) – top-down
2 approach Mapping conceptual to relational database and validate using CO2 L6
Normalization
Implement the database using SQL Data definition with constraints,
3 Views CO3 L6
4 Query the database using SQL Manipulation CO3 L2
Querying/Managing the database using SQL Programming
5 - Stored Procedures/Functions CO3 L2
- Constraints and security using Triggers
6 Database design using Normalization – bottom-up approach CO2 L6
Develop database applications using IDE/RAD
7 tools (Eg., NetBeans,VisualStudio)
CO4 L6
8 Database design using EER-to-ODB mapping / UML class diagrams CO5 L3
Object features of SQL-UDTs and sub-types, Tables using UDTs,
9 CO5 L2
Inheritance,Method definition
10 Querying the Object-relational database using Objet Query language CO5 L2

Content beyond Experiments

NOSQL Commands using MONGODB, CASSANDRA,
1 CO3 L2
HIVE, ORIENTDB

2 Spatial Data Storage and Retrieval in MYSQL CO3 L2

* BL: L1-Remembering, L2-Understanding, L3-Applying, L4-Analyzing, L5-Evaluating, L6-Creating

EX. NO: 1 DATABASE DEVELOPMENT LIFE CYCLE

DATE: Problem definition, Requirement analysis, Scope and Constraints

AIM

To study about the various concepts such as Problem definition, Requirement analysis, Scope
and Constraints involved in Database Development Life Cycle.

DESCRIPTION
DATABASE ENVIRONMENT

A database environment is a collective system of components that comprise and regulates the
group of data, management, and use of data, which consist of software, hardware, people, techniques
of handling database, and the data also. One of the primary aims of a database is to supply users with
an abstract view of data, hiding a certain element of how data is stored and manipulated.

 Hardware in a database environment means the computers and computer peripherals that are
being used to manage a database,
 Software includes operating system (OS) to the application programs that include database
management software like M.S. Access or SQL Server.
 People in a database environment include administrators and data users in system
COMPONENTS OF DATABASE SYSTEM ENVIRONMENT
Even the database system environment is made up of the following
1. Hardware
2. Software
3. People
4. Procedures
5. Data

System Utilities: Database system utilities are the tools that can be used by the database system
administrator to control and manage the database system.
1. Loading Utility
Loading database utility helps in loading the database file into the database.
2. Backup Utility
The backup utility in the database environment helps in creating a backup copy of the entire database.
3. Database Storage Reorganization Utility
It helps to relocate and organize the database files to a different location and it also produces a new
access path to access the files from its new location.
4. Performance Monitoring Utility
It monitors the usage of the database by its user and provides statistics for the same to the DBA.
DATABASE SYSTEM DEVELOPMENT LIFECYCLE:

 Database life cycle (DBLC) defines the stages involved for implementing a database,
starting with requirements analysis and ending with monitoring and modification.
 DBLC never ends because database monitoring, modification, and maintenance are part of
the life cycle, and these activities continue long after a database has been implemented.
 DBLC encompasses the lifetime of database.

Five stages in database life cycle are:

1. Requirements analysis

2. Logical design

3. Physical design

4. Implementation

5. Monitoring, Modification and Maintenance

Database Development Life Cycle

Steps in Database System Development Process

I. REQUIREMENTS ANALYSIS or REQUIREMENTS COLLECTION:

 Requirements Analysis is the first and most important stage in the Database Life Cycle.
 Process of collecting and analyzing information about organization that is to be supported by
database system, and using this information to identify requirements for new system.

Requirements analysis

Planning – This stages of database design concepts are concerned with planning of entire Database
Development Life Cycle.

System definition – This stage defines the scope and boundaries of the proposed database system.
Requirement Analysis - Example

Customer/End-user expectation: Customer/End-user receive:

Below are the two methodologies used for requirement analysis phase,

1) SDA - Structured data analysis

2) SSAD - Structured systems analysis and design method

1. Structured Data Analysis (SDA)is a method for analysing the flow of information within an
organization using data flow diagrams.

Specification documents created in requirement analysis phase are used as input to conceptual schema
design phase. Structured data analysis (systems analysis) consists of

 Data Flow Diagrams

 Systems Analysis
 Data Processing

Data Flow Diagrams

 A Data-Flow Diagram is a way of representing a flow of data through a process or a system.

DFD also provides information about the outputs and inputs of each entity and the process
itself. A data-flow diagram has no control flow — there are no decision rules and no loops.
Specific operations based on the data can be represented by a flowchart.
  Systems analysis is "the process of studying a procedure or business to identify its goal and
purposes and create systems and procedures that will efficiently achieve them"
 Data processing is, "the collection and manipulation of items of data to produce meaningful
information." It is a subset of information processing.

Data processing may involve various processes, including:

 Validation –
 Sorting –
 Summarization(statistical) or (automatic) –
 Aggregation –Analysis –
 Reporting –
 Classification

2. Structured Systems Analysis and Design Method (SSADM), as methodology, is a systems

approach to the analysis and design of information systems.

SSADM techniques Three most important techniques that are used in SSADM are as follows:

 Logical design
 Physical Design
 Implementation

II. Logical design

 During the first part of Logical Design, a conceptual model is created based on the
needs assessment performed in stage one.
 A conceptual model is typically an entity-relationship (ER) diagram that shows tables, fields,
and primary keys of database, and how tables are related (linked) to one another.
 Tables in the ER diagram are then normalized. Normalization resolves any problems associated
with database design, so that data can be accessed quickly and efficiently.

1. CONCEPTUAL MODEL: describes the structure of a database.

2. ENTITY-RELATIONSHIP (ER) DIAGRAM: A diagram used during design phase of
database development and represents relationships between data during database design.
3. NORMALIZATION: The process of applying increasingly stringent rules to a relational
database to correct any problems associated with poor design.
III. Physical Design

Physical Design stage has only one purpose: to maximize database efficiency.
This means finding ways to speed up the performance of the RDBMS. Manipulating certain database
design elements can speed up the two slowest operations in an RDBMS: retrieving data from and
writing data to a database.

The final two stages in the DBLC,

1. Implementation
2. Monitoring, Modification, and Maintenance occur after database design is complete.

IV. Implementation

During the implementation stage of the DBLC, the tables developed in the ER diagram (and
subsequently normalized) are converted into SQL statements. These SQL statements are then executed
in the RDBMS to create a database. By this stage in the database life cycle, the System Administrator
has installed and configured an RDBMS.

V. Monitoring, modification, and maintenance

A successfully implemented database must be carefully monitored to ensure that it is functioning
properly and that it is secure from unauthorized access.

RDBMS usually provides utilities to help monitor database functionality and security.

Database modification involves adding and deleting records, importing data from other systems (as
needed), and creating additional tables, user views, and other objects and tools.

DATABASE DESIGN:

 Database Design is a collection of processes that facilitate the designing, development,

implementation and maintenance of enterprise data management systems.
 Properly designed database are easy to maintain, improves data consistency and cost effective in
terms of disk storage space.
 Database designer decides how data elements correlate and what data must be stored.
Database Design

It helps produce database systems

1. That meet the requirements of the users

2. Have high performance.
3. The database development life cycle has a number of stages that are followed when developing
database systems.
4. The steps in the development life cycle do not necessarily have to be followed religiously in a
sequential manner.
5. On small database systems, the process of database design is usually very simple and does not
involve a lot of steps.

Database designing

 Logical model – This stage is concerned with developing a database model based on
requirements. The entire design is on paper without any physical implementations or specific
DBMS considerations.
 Physical model – This stage implements the logical model of the database taking into account
the DBMS and physical implementation factors.

Implementation

 Data conversion and loading – this stage of relational databases design is concerned with
importing and converting data from the old system into the new database.
 Testing – this stage is concerned with the identification of errors in the newly implemented
system. It checks the database against requirement specifications.

RESULT:

Thus the various concepts such as Problem definition, Requirement analysis, Scope
and Constraints involved in Database Development Life Cycle has been studied.
EX. NO: 2 IMPLEMENT THE DATABASE USING SQL DATA DEFINITIONLANGUAGE

DATE:

AIM To create a database using SQL Data Definition Language using SQL queries.

PROCEDURE

SQL Data Definition Language (DDL) Commands

 DDL or Data Definition Language consists of the SQL commands that can be used to define
the database schema
 DDL is set of SQL commands used to create, modify, and delete the structure of database
objects in the database but not data.
 DDL changes structure of table like creating a table, deleting a table, altering a table, etc.
 All the command of DDL are auto-committed that means it permanently save all the changes.

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 database.
 TRUNCATE: This is used to remove all records from a table, including all spaces
 RENAME: This is used to rename an object existing in the database.

a. SQL CREATE command It is used to create a new table in the database.

Syntax:
CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,. ..]);
Or
Create table "table_name"

("column1" "data type",

"column2" "data type",

"column3" "data type", ...

 "columnN" "data type");

Example creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName,
Address, and City:

Example 1

CREATETABLE Persons
( PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

PersonID LastName FirstName Address City

Example 2

SQL> CREATE TABLE EMPLOYEE (

EMP_ID INT NOT NULL,

EMP_NAME VARCHAR (25) NOT NULL,

PHONE_NO INT NOT NULL,

ADDRESS CHAR (30),

PRIMARY KEY (ID));

Use DESC command as follows:

SQL> DESC EMPLOYEE;

Field Type Null Key Default Extra

EMP_ID int(11) NO PRI NULL
EMP_NAME varchar(25) NO NULL
PHONE_NO NO int(11) NULL
ADDRESS YES NULL char(30)
b. SQL DROP Command:

 The DROP TABLE statement is used to drop an existing table in a database

 A SQL drop table is used to delete a table definition and all the data from a table.
 It is used to delete both the structure and record stored in the table.

Syntax: DROP TABLE table_name;

Example

DROP TABLE EMPLOYEE;

c. SQL ALTER Command: It is used to alter the structure of the database. This will modify the
characteristics of an existing attribute or to add a new attribute.

Syntax: To add a new column in the table

ALTER TABLE table_name ADD column_name COLUMN-definition;

To modify existing column in the table:

ALTER TABLE table_name MODIFY(column_definitions... );

SQL ALTER TABLE Example

"Persons" table:
ID LastName FirstName Address City

ALTER TABLE Persons ADD DateOfBirth date;

ID LastName FirstName Address City DateOfBirth

d. SQL TRUNCATE:

The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table
itself. 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;

e. SQL RENAME

Database user can easily change the name of the table by using the RENAME TABLE and ALTER
TABLE statement in SQL. This helps in changing the name of the table.

Syntax:

RENAME old_table _name To new_table_name ;

 Suppose, you want to change the above table name Cars into "Car_2021_Details".

RENAME Cars To Car_2021_Details ;

RESULT:

Thus the Database has been created using Data Definition Language using SQL queries.
EX. NO: 3 IMPLEMENT DATABASE USING SQL DATA DEFINITION WITH VIEWS
DATE:

AIM: To Implement the database using SQL Data definition with Views

VIEWS DESCRIPTION:

 A database view is a logical or virtual table based on a query. Views are queried just
like tables.

 View is a virtual table based on the result-set of an SQL statement. A view also contains rows
and columns
 To create the view, we can select the fields from one or more tables present in database.
 A view can either have specific rows based on certain condition or all the rows of a
table. A DBA or view owner can drop a view with the DROP VIEW command.

TYPES OF VIEWS
• Updatable views – Allow data manipulation
• Read only views – Do not allow data manipulation

Example 1:

Sample table: Student_Detail

STU_ID NAME ADDRESS

1 Stephan Delhi
2 Kathrin Noida
3 David Ghaziabad
4 Alina Gurugram

Student_Marks

STU_ID NAME MARKS AGE

1 Stephan 97 19
2 Kathrin 86 21
3 David 74 18
4 Alina 90 20
 5 John 96 18

1. Creating view:

A view can be created using CREATE VIEW statement. Create a view from a single table or multiple
tables.

Syntax:

CREATE VIEW view_name

AS SELECT column1,
column2.....
FROM table_name
WHERE condition;

2. Creating View from a single table

In this example, we create a View named DetailsView from the table Student_Detail.

Query:

CREATE VIEW DetailsView AS

SELECT NAME, ADDRESS
FROM Student_Details
WHERE STU_ID < 4;

Just like table query, we can query the view to view the data.

SELECT * FROM DetailsView;

Output:

NAME ADDRESS
Stephan Delhi
Kathrin Noida
David Ghaziabad

3. Creating View from multiple tables

View from multiple tables can be created by including multiple tables in SELECT statement.
In this, view is created named MarksView from two tables Student_Detail and Student_Marks.

Query:

CREATE VIEW MarksView AS

SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Student_Marks.MARKS
FROM Student_Detail, Student_Mark
WHERE Student_Detail.NAME = Student_Marks.NAME;

To display data of View MarksView:SELECT * FROM MarksView;

NAME ADDRESS MARKS
Stephan Delhi 97
Kathrin Noida 86
David Ghaziabad 74
Alina Gurugram 90

4. Deleting View - A view can be deleted using the Drop View statement.

Syntax: DROP VIEW view_name;

Example: If we want to delete the View MarksView, we can do this as:

DROP VIEW MarksView;

5. SQL Updating a View -

A view can be updated with the CREATE OR REPLACE VIEW statement.

SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACEVIEW view_name

AS SELECTcolumn1, column2, ...
FROM table_name
WHERE condition;

RESULT:

Thus the Views using SQL has been executed using DDL and DML statements.
EX. NO: 4 QUERY THE DATABASE USING SQL
MANIPULATION DATE:

AIM:

To Query the database using SQL Manipulation i.e. Data Manipulation Language (DML)
Commands.

DESCRIPTION:

SQL DATA MANIPULATION LANGUAGE (DML)

 DML commands are SQL commands, used to manipulation of data i.e., modify
the data present in the database
 It is responsible for all form of changes in the database.
 The command of DML is not auto-committed that means it can't permanently save
all the changes in the database. They can be rollback.

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.
 SELECT: It is used to query or retrieve data from a table in the database

a. SQL INSERT: SQL INSERT statement is used to insert a single or multiple data into row of a
table.

Syntax:

INSERT INTO TABLE_NAME

(col1, col2, col3,.. . .col N)
VALUES (value1, value2, value3,.....valueN);
Or
INSERT INTO TABLE_NAME
VALUES (value1, value2, value3,.....valueN);
For example:

In SQL, You can insert the data in two ways:

 Without specifying column name

 By specifying column name

Sample Table - EMPLOYEE

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30
2 Robert Austin 300000 26
3 Christian Denver 100000 42
4 Kristen Washington 500000 29
5 Russell Los angels 200000 36

1. Without specifying column name

Syntax: INSERT INTO

TABLE_NAME

VALUES (value1, value2, value 3,.....Value N);

Query: INSERT INTO EMPLOYEE VALUES (6, 'Marry', 'Canada', 600000, 48);

Output:

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30
2 Robert Austin 300000 26
3 Christian Denver 100000 42
4 Kristen Washington 500000 29
5 Russell Los angels 200000 36
6 Marry Canada 600000 48

2. By specifying column name

To insert partial column values, you must have to specify the column names.
Syntax

INSERT INTO TABLE_NAME

[(col1, col2, col3,.....col N)]

VALUES (value1, value2, value 3,.....Value N);

Query

INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, AGE) VALUES (7, 'Jack', 40);

Output: After executing this query, the table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30
2 Robert Austin 300000 26
3 Christian Denver 100000 42
4 Kristen Washington 500000 29
5 Russell Los angels 200000 36
6 Marry Canada 600000 48
7 Jack null null 40

b. SQL UPDATE:

 This command is used to update or modify the value of a column in the table.
 SQL UPDATE statement is used to modify the data that is already in the database.
 The condition in the WHERE clause decides that which row is to be updated.

Syntax:

UPDATE table_name

SET column1 = value1, column2 = value2, ...

[WHERE CONDITION]
Sample Table:

EMPLOYEE

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30
2 Robert Austin 300000 26
3 Christian Denver 100000 42
4 Kristen Washington 500000 29
5 Russell Los angels 200000 36
6 Marry Canada 600000 48

Updating single record

Update column EMP_NAME and set the value to 'Emma' in the row where SALARY is 500000.

Syntax: UPDATE table_name

SET column_name =

value WHERE condition;

Query

UPDATE EMPLOYEE

SET EMP_NAME = 'Emma'

WHERE SALARY =

500000;

Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30
2 Robert Austin 300000 26
3 Christian Denver 100000 42
4 Emma Washington 500000 29
5 Russell Los angels 200000 36
6 Marry Canada 600000 48
Updating multiple records

If you want to update multiple columns, you should separate each field assigned with a comma.Syntax

UPDATE table_name

SET column_name = value1, column_name2 = value2

WHERE condition;

Query

UPDATE EMPLOYEE

SET EMP_NAME = 'Kevin', City = 'Boston'

WHERE EMP_ID = 5;

Output

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30
2 Robert Austin 300000 26
3 Christian Denver 100000 42
4 Kristen Washington 500000 29
5 Kevin Boston 200000 36
6 Marry Canada 600000 48

Without use of WHERE clause

Syntax: UPDATE table_name

SET column_name = value1;

Query: UPDATE EMPLOYEE

SET EMP_NAME =

'Harry';
Output

EMP_ID EMP_NAME CITY SALARY AGE

1 Harry Chicago 200000 30
2 Harry Austin 300000 26
3 Harry Denver 100000 42
4 Harry Washington 500000 29
5 Harry Los angels 200000 36
6 Harry Canada 600000 48

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

Syntax:

DELETE FROM table_name [WHERE condition];

Example :

Suppose, the EMPLOYEE table having the following records:

EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000
2 Russell Austin 9262738271 200000
3 Denzel Boston 7353662627 100000
4 Angelina Denver 9232673822 600000
5 Robert Washington 9367238263 350000
6 Christian Los angels 7253847382 260000

The following query will DELETE an employee whose ID is 3.

SQL> DELETE FROM

EMPLOYEE WHERE EMP_ID = 3;

EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000
 2 Russell Austin 9262738271 200000
4 Angelina Denver 9232673822 600000
5 Robert Washington 9367238263 350000
6 Christian Los angels 7253847382 260000

If you don't specify the WHERE condition, it will remove all the rows from the table.

DELETE FROM EMPLOYEE;

Now, the EMPLOYEE table would not have any records.

Deleting Single Record

Delete the row from the table EMPLOYEE where EMP_NAME = 'Kristen'. This will delete only
the fourth row.

Query

DELETE FROM EMPLOYEE

WHERE EMP_NAME =

'Kristen';

Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30
2 Robert Austin 300000 26
3 Christian Denver 100000 42
5 Russell Los angels 200000 36
6 Marry Canada 600000 48

Deleting Multiple Record

Delete the row from the EMPLOYEE table where AGE is 30.

Query

DELETE FROM EMPLOYEE WHERE AGE= 30;

Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

2 Robert Austin 300000 26
3 Christian Denver 100000 42
5 Russell Los angels 200000 36
6 Marry Canada 600000 48

Delete all of the records

Delete all the row from the EMPLOYEE table. After this, no records left to display. The EMPLOYEE
table will become empty.

Syntax

DELETE * FROM table_name;

or

DELETE FROM table_name;

Query

DELETE FROM EMPLOYEE;

Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

DATA QUERY LANGUAGE - DQL is used to fetch data from database. It uses only one
command - SELECT

SELECT:

SELECT statement is used to query or retrieve data from a table in the

database. The returns data is stored in a table, and the result table is known as
result-set.
Syntax:
SELECT expressions
FROM TABLES
WHERE conditions;
Or
SELECT column1, column2, ...
FROM table_name;
For example:
Use the following syntax to select all the fields available in the table:
SELECT * FROM table_name;

Example: EMPLOYEE

EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000
2 Russell Austin 9262738271 200000
3 Angelina Denver 9232673822 600000
4 Robert Washington 9367238263 350000
5 Christian Los angels 7253847382 260000

To fetch the EMP_ID of all the employees, use the following

query: SELECT EMP_ID FROM EMPLOYEE;
Output
EMP_ID
1
2
3
4
5
To fetch the EMP_NAME and SALARY, use the following

query: SELECT EMP_NAME, SALARY FROM

EMPLOYEE;

EMP_NAME SALARY
Kristen 150000
Russell 200000
Angelina 600000
Robert 350000
Christian 260000

To fetch all the fields from the EMPLOYEE table, use the following query:

SELECT * FROM EMPLOYEE

Output

EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000
2 Russell Austin 9262738271 200000
3 Angelina Denver 9232673822 600000
4 Robert Washington 9367238263 350000
5 Christian Los angels 7253847382 260000

RESULT:

Thus, SQL Data Manipulation Language (DML) Commands for Querying the database
has been executed.
EX. NO: 5 CREATION OF A DATABASE USING SQL QUERIES DDL,

DML DATE:

AIM To create a database and to retrieve the information from the database using SQL queries.

DDL COMMANDS DESCRIPTION

CREATE

SQL> create table student(sno number(3),name varchar(20),marks number(3),dep varchar(2),age

number(2));

Table created

Sql> desc

student;

SNO NAME MARKS DEP AGE

ALTER

SQL> alter table student add(ph_no

number(10)); Table altered

Sql> desc student;

SNO NAME MARKS DEP AGE PH_NO

MODIFY

SQL> alter tables student modify(dep

varchar(5)); Table altered

Sql> desc student;

SNO NAME MARKS DEP AGE PH_NO

TRUNCATE

SQL> truncate table student;

Table truncated
SQL> select * from student;

No rows selected

DROP

SQL> alter table student drop column ph_no

; Table altered

SQL> desc student ;

SNO NAME MARKS DEP AGE

DML COMMANDS

INSERT

SQL> insert into student values(01,‘Manoj‘,99,‘IT‘,18) ;

1 row created

SQL>insert into student values(02,‘Ramana‘,98,‘IT‘,19);

1 row created

SQL>insert into student

values(03,‘Samy‘,90,‘CSE‘,17); 1 row created

SQL>insert into student values(04,‘Prabha‘,99, ‘CSE‘,16);

1 row created

SELECT SQL> select * from student;

SNO NAME MARKS DEP AGE

01 Manoj 99 IT 18

02 Ramana 98 IT 19

03 Samy 90 CSE 17

04 Prabha 99 CSE 16
UPDATE

SQL>update student set age=18 where sno=‘04‘;

1 row updated

SQL>select * from student;

SNO NAME MARKS DEP AGE

01 Manoj 99 IT 18

02 Ramana 98 IT 19

03 Samy 90 CSE 17

04 Prabha 99 CSE 18

DELETE

SQL> delete from student where sno=‘03‘;

1 rows deleted

SQL>select * from student;

SNO NAME MARKS DEP AGE

01 Manoj 99 IT 18

02 Ramana 98 IT 19

04 Prabha 99 CSE 18

RESULT
Thus the creation of database and the SQL queries to retrieve information from the
database has been implemented using DDL and DML statements.
EX. NO : 6a SQL PROGRAMMING USING FUNCTIONS
DATE:

AIM: To implement the concept of function Using SQL Programming

FUNCTION DESCRIPTION:

Functions are subprograms return a value. PL/SQL Function is very similar to PL/SQL
Procedure. The main difference between procedure and a function is, a function must always return
a value, and procedure may or may not return a value.

To create a function to return the total number of users in the user1 table.

Function Syntax:

Create or replace function function_name[(argument1,argument2,....., argument n)]

Return datatype

is

[declaration_section]

Begin

Executable-section

Return function_value

End;

TABLE CREATION: user1 table

Create table user1(id number(10) primary key, name varchar2(100));

Inserting Values in user1 Table:

After inserting 5 rows, Final table will be:

Insert into user1 values(101,’Rahul’);

Insert into user1 values(102,’Risha’);

Insert into user1 values(103,’Rithu’);

Insert into user1 values(104,’Roja’);

Insert into user1

values(105,’Rubika’);
Select * from user1

ID NAME
101 Rahul
102 Risha
103 Rithu
104 Roja
105 Rubika

CREATE FUNCTION:

CREATE OR REPLACE FUNCTION totalCustomers

RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM user1;
RETURN total;
END;
/
Output:
Function created.

FUNCTION CALL:

DECLARE
c number(2);
BEGIN
c := totalCustomers();
dbms_output.put_line('Total no. of Customers: ' || c);
END;
/
Output:
Total no. of Users: 5
Statement processed.

RESULT:

Thus the SQL Programming Functions was executed and output was verified.
EX. NO: 6b FUNCTIONS IN SQL PROGRAMMING
DATE:

AIM: To Query/Manage the Database Using SQL Programming Functions

DESCRIPTION: To perform the addition of two numbers using Functions in SQL Programming

FUNCTION CREATION

create or replace function add(n1 in number, n2 in

number) return number
is
n3 number(8);
begin
n3 :=n1+n2;
return n3;
end;
/
OUTPUT:
Function Created

FUNCTION CALL: Program to call the function add()

DECLARE
n3 number(2);
BEGIN
n3 := add(11,22);
dbms_output.put_line('Addition is: ' ||
n3); END;
/
OUTPUT:

Addition is: 33

Statement processed.

RESULT:

Thus Function for addition of two numbers using SQL Programming was executed.
EX. NO: 7 QUERYING/MANAGING THE DATABASE USING SQL PROGRAMMING

DATE: FUNCTIONS

AIM: To Query/Manage the Database Using SQL Programming Functions

DESCRIPTION: To create a function for deposit and withdrawal of money from an account in a
bank management system this uses bank table.

TABE CREATION:

create table bank_acc(acc_no number, b_name varchar2(10), balance number);

Table created.

insert into bank_acc values (100,‘SBI‘, 25000)

1 row created.

insert into bank_acc values (101,‘SBT‘, 5000)

1 row created.

insert into bank_acc values (102,‘FEDERAL‘, 10000)

1 row created.

insert into bank_acc values (103,‘AXIS‘,15000)

1 row created.

insert into bank_acc values (104,‘CANARA‘, 50000)

1 row created

select * from bank_acc;

ACC_NO B_NAME BALANCE

100 SBI 25000

101 SBT 5000

102 Federal 10000

103 axis 15000

104 canara 50000

WITHDRAW FUNCTION:

Create a function for withdrawing money from an account in bank

PROGRAM:

create or replace function withdraw (n in number, amt in number)

return number is

b number;

begin

select balance into b from bank_acc where

acc_no=n; if b-500>amt then

b:=b-amt;

update bank_acc set balance=b where acc_no=n;

else

dbms_output.put_line('can not

withdraw'); end if;

return b;

end;

/
Function created

WITHDRAW FUNCTION CALL:

Calling function from a PL\SQL block:

declare

n number;

begin

n:=withdraw(100,20000);

end

Statement processed.
select * from bank_acc;

ACC_NO B_NAME BALANCE

100 SBI 5000
101 SBT 5000
102 Federal 10000
103 axis 15000
104 canara 50000

Calling function from a PL\SQL block:

declare

n number;

begin

n:=withdraw(101,20000);

end
/

can not withdraw

Statement processed.

select * from bank_acc;

ACC_NO B_NAME BALANCE

100 SBI 5000
101 SBT 5000
102 Federal 10000
103 axis 15000
104 canara 50000

DEPOSIT FUNCTION:
Create a function for depositing money to an account in a bank

create or replace function deposit(n in number, amt in

number) return number is

b number;
 begin

select balance into b from bank_acc where acc_no=n;

b:=b+amt;

update bank_acc set balance=b where acc_no=n;

return b;

end

/
Function created

DEPOSIT FUNCTION CALL

Calling function from a PL/SQL block

declare

n number;

begin

n:=deposit(104,5000);

end

Statement processed.

select * from bank_acc;

ACC_NO B_NAME BALANCE

100 SBI 5000

101 SBT 5000

102 Federal 10000

103 axis 15000

104 canara 55000

RESULT:

Thus the Function for deposit and withdrawal of money in an account in a Bank Management
System was successfully executed.
EX. NO: 8 SQL PROGRAMMING PROCEDURES
DATE:

AIM:
To write a simple procedure to perform simple insertion operation Using SQL Programming

DESCRIPTION PROCEDURE:
Procedures are subprograms do not return a value directly; mainly used to perform an action.
PL/SQL Procedure is a PL/SQL block which performs one or more specific tasks. The procedure
contains a header and a body.

 Header: It contains name of procedure and the parameters or variables passed to

the procedure.

 Body: It contains a declaration section, execution section and exception section

SYNTAX:

CREATE OR REPLACE PROCEDURE procedure_name (parameter_list)

IS
[declaration_statements]
BEGIN
[executable_ statements]
EXCEPTION
[exception statements]
END;

PROCEDURE CREATION:

Create a simple procedure that displays the string 'Hello World!'

CREATE OR REPLACE PROCEDURE

greetings IS
BEGIN
dbms_output.put_line('Hello World!');
END;
/
Procedure created.
PROCEDURE CALL

BEGIN

greetings;

END;

OUTPUT: Hello World

TABLE CREATION: user1 table

Create table user1(id number(10) primary key,name varchar2(100));

Table Description:

desc user1;

Table Column Data Type Length Precision Scale Primary Key Nullable

USER1 ID Number - 10 0 1 -

NAME Varchar2 100 - - -

PROCEDURE CREATION:

To create a Procedure to insert records in the "user1" table

create or replace procedure INSERTUSER(id in number, name in varchar)

is

begin

insert into user1 values(id,name);

end;

Output:

Procedure created.
PROCEDURE CALL:

Oracle program code to call above created procedure.

BEGIN

insertuser(101,'Rahul');

dbms_output.put_line('record inserted successfully');

END;
/

OUTPUT:

record inserted successfully

Statement processed.

In the "USER1" table, one record is inserted.

select * from user1;

ID NAME
101 Rahul

Insert the second record in user1 table:

BEGIN
insertuser(102,'Risha');
dbms_output.put_line('record inserted
successfully');
END;
/
Insert the third record in user1 table:
BEGIN
insertuser(103,'Rithu');
dbms_output.put_line('record inserted
successfully');
END;
/
Insert the Fourth record in user1 table:
BEGIN
insertuser(104,'Roja');
 dbms_output.put_line('record inserted successfully');
END;
/
Insert the Fifth record in user1 table:
BEGIN
insertuser(105,'Rubika');
dbms_output.put_line('record inserted
successfully');
END;
/
After inserting 5 rows, Final table will be:

select * from user1

ID NAME
101 Rahul
102 Risha
103 Rithu
104 Roja
105 Rubika

DROP PROCEDURE:

Syntax:

DROP PROCEDURE

procedure_name; Drop the procedure

insertuser

DROP PROCEDURE

insertuser; Output:

Procedure dropped.

RESULT:

Thus the Procedure for simple insertion operation Using SQL Programming was successfully
executed.
EX. NO: 9 QUERYING/MANAGING THE DATABASE USING SQL
PROGRAMMING DATE: PROCEDURES

AIM: To Query/Manage the Database Using SQL Programming Procedures

DESCRIPTION:
To create a procedure for deposit and withdrawal of money in an account in a bank table

Table Creation:

create table bank(acc_no number, name varchar2(10), bal number);

Table created.

Inserting Values in table:

insert into bank values (100,‘Anil‘,50000)

1 row created.

insert into bank values (101,‘Abi‘,10000)

1 row created.

insert into bank values (102,‘Bavi‘,2500)

1 row created.

insert into bank values (103,‘Chandru‘,1000)

1 row created.

insert into bank values (104,‘Divakar‘,20000)

1 row created.

select * from bank;

ACC_NO NAME BAL

100 Anil 50000

101 Abi 10000

102 Bavi 2500

103 Chandru 1000

104 Divakar 20000

CREATING A PROCEDURE

Create a procedure for deposit & withdrawal of money in account in a Bank table.

create or replace procedure bank_pro(opt number, amount number, n number)

as

balance number;

begin

select bal into balance from bank where

acc_no=n; if opt=1 then

balance:= balance+amount;

update bank set bal=balance where

acc_no=n; commit;

dbms_output.put_line('Balance after deposition is '||

balance); elsif opt=2 then

balance:=balance-amount;

if balance<1000 then

dbms_output.put_line('Cannot withdraw… Balance lower than'||

balance); else

update bank set bal=balance where

acc_no=n; commit;

dbms_output.put_line('Balance after withdrawal is'||

balance); end if;

end if;

end;

Procedure created.
PROCEDURE CALL - DEPOSIT

begin

bank_pro(1,40000,100);

end;

/
Balance after deposition is 90000

Statement processed.

select * from bank;

ACC_NO NAME BAL

100 Anil 90000

101 Abi 10000

102 Bavi 2500

103 Chandru 1000

104 Divakar 20000

PROCEDURE CALL - WITHDRAW

begin

bank_pro(2,1500,102);

end;

OUTPUT:

Balance after withdrawal

is1000 Statement processed.

Select * from bank;

ACC_NO NAME BAL

100 Anil 90000

101 Abi 10000

102 Bavi 1000

103 Chandru 1000

104 Divakar 20000

PROCEDURE CALL - WITHDRAW

begin

bank_pro(2,1500,103);

end;

OUTPUT:

Cannot withdraw… Balance lower than-

500 Statement processed.

select * from bank;

ACC_NO NAME BAL

100 Anil 90000

101 Abi 10000

102 Bavi 1000

103 Chandru 1000

104 Divakar 20000

RESULT:

Thus the Procedure for Deposit and Withdrawal of money in an account in a

Bank Management System was successfully executed.
EX. NO: 10 QUERYING/MANAGING THE DATABASE USING SQL
PROGRAMMING DATE: TRIGGERS

AIM: To Query/Manage the Database Using SQL Programming triggers

TRIGGER DESCRIPTION:

A trigger is a stored procedure in database which automatically invokes whenever a

special event in the database occurs. For example, a trigger can be invoked when a row is inserted
into a specified table or when certain table columns are being updated.

SYNTAX:
Create trigger trigger name

BEFORE | AFTER

DELETE, INSERT, UPDATE

ON table name

FOR EACH

ROW BEGIN

Executable-statements

END;

Example:

Given Student Database, in which student marks assessment is recorded.

Create a trigger so that the total and average of specified marks is automatically calculated whenever
a record is inserted.

Database creation:

create table student(sid number(4), name varchar(30), sub1 number(2), sub2 number(2),
sub3 number(2), total number(3), per number(3));

Table Description:

Desc student;
Before Trigger Execution: Inserting Values in table:

insert into student values(1, 'Nivetha', 50, 60, 70, 0, 0);

1 row(s) inserted.

select * from student;

CREATING TRIGGER

create trigger strigger

before insert

on

Student

for each row

Begin

update student set Student.total = Student.sub1 + Student.sub2 + Student.sub3,

Student.per = Student.total/3;

end;

/
OUTPUT: Trigger Created.

After Trigger Execution Inserting Values in table:

Insert into student values(2, 'Rithani', 90, 80, 90, 0, 0);

1 row(s) inserted.

select * from student

insert into student values(3, 'Sankar', 90, 90, 90, 0, 0);

1 row(s) inserted.

select * from student;

insert into student values(4, 'Vijay', 95, 95, 95, 0, 0);

1 row(s) inserted.

select * from

student; OUTPUT:

RESULT:
Thus the trigger while insert or update or delete operations are performed on the table
student was successfully executed and output was verified
EX. NO: 11 DATABASE DESIGN USING CONCEPTUAL MODELING (ER-EER)

DATE: MAPPING CONCEPTUAL TO RELATIONAL DATABASE

AIM:

To perform mapping from a ER-EER Model to Relational Database in a Database Design

Using Conceptual Modeling

DESCRIPTION:

ER model
 ER model stands for an Entity-Relationship model. It is a high-level data model. This model is
used to define the data elements and relationship for a specified system.
 It develops a conceptual design for the database. It also develops a very simple and easy
to design view of data.
 In ER modeling, database structure is portrayed as a diagram called entity-relationship
diagram.

Example: Design a school database.

In this database, the student will be an entity with attributes like address, name, id, age, etc. The
address can be another entity with attributes like city, street name, pin code, etc and there will be
a relationship between them.
Component of ER Diagram

ER MODEL to RELATIONAL MAPPING

The database can be represented using the notations, and these notations can be reduced to a collection
of tables.
In the database, every entity set or relationship set can be represented in tabular form.

RULES FOR REDUCTION OF ER DIAGRAM TO TABLE

 Entity type becomes a table.

 All single-valued attribute becomes a column for the table.

 A key attribute of the entity type represented by the primary key.

 The multivalued attribute is represented by a separate table.

 Composite attribute represented by components

 Derived attributes are not considered in the table.

The ER diagram is given below:

After Mapping, Final Relational Database Table will be:

SAMPLE ENTITY-RELATIONSHIP DIAGRAM:

TABLE CREATION
CREATE TABLE SALESMAN
(SALESMAN_ID NUMBER
(4),
NAME VARCHAR2 (20),
CITY VARCHAR2 (20),
COMMISSION VARCHAR2 (20),
PRIMARYKEY (SALESMAN_ID));

CREATE TABLE
CUSTOMER1 (CUSTOMER_ID
NUMBER (4),
CUST_NAME VARCHAR2 (20),
CITY VARCHAR2 (20),
GRADE NUMBER (3),
PRIMARY KEY (CUSTOMER_ID),
SALESMAN_ID REFERENCES SALESMAN (SALESMAN_ID) ON DELETE SET NULL);

CREATE TABLE ORDERS

(ORD_NO NUMBER (5),

PURCHASE_AMT NUMBER (10, 2),

ORD_DATE DATE,
 PRIMARY KEY (ORD_NO),

CUSTOMER_ID REFERENCES CUSTOMER1 (CUSTOMER_ID) ON DELETE CASCADE,

SALESMAN_ID REFERENCES SALESMAN (SALESMAN_ID) ON DELETE CASCADE);

TABLE DESCRIPTION:

RESULT:
Thus the mapping from a ER-EER Model to Relational Database in a Database Design Using
Conceptual Modeling was done successfully.
EX. NO: 12 DATABASE DESIGN USING NORMALIZATION
DATE:

AIM:

To Validate a Database Design Using Normalization

DESCRIPTION:

NORAMALIZATION OF TABLE

 Database normalization is the process of removing redundant data from tables in order to improve
storage efficiency, data integrity, and scalability

 Normalization is process of minimizing redundancy from a relation. Redundancy in relation may

cause insertion, deletion, and update anomalies. So, it helps to minimize redundancy in relations.

 Normal forms are used to eliminate or reduce redundancy in database tables

Normalization of Tables in Database

Normal Form Description

1NF A relation is in 1NF if it contains an atomic value.

2NF A relation will be in 2NF if it is in 1NF and all non-key attributes are fully
functional dependent on the primary key.

3NF A relation will be in 3NF if it is in 2NF and no transition dependency exists.

BCNF A stronger definition of 3NF is known as Boyce Codd's normal form.

4NF A relation will be in 4NF if it is in Boyce Codd's normal form and has
no multi-valued dependency.

5NF A relation is in 5NF. If it is in 4NF and does not contain any join
dependency, joining should be lossless.
1. FIRST NORMAL FORM

 If a relation contain composite or multi-valued attribute, it violates first normal form

 If a relation is in first normal form if it should contain only atomic values.

Example: EMPLOYEE table:

EMP_ID EMP_NAME EMP_PHONE EMP_STATE

14 John 7272826385, UP
9064738238
20 Harry 8574783832 Bihar
12 Sam 7390372389, Punjab
8589830302

The decomposition of the EMPLOYEE table into 1NF has been shown below:

EMP_ID EMP_NAME EMP_PHONE EMP_STATE

14 John 7272826385 UP
14 John 9064738238 UP
20 Harry 8574783832 Bihar
12 Sam 7390372389 Punjab
12 Sam 8589830302 Punjab

Now the table is in First normal Form

2. SECOND NORMAL FORM:

 In the 2NF, relational must be in 1NF.

 In second normal form, all non-key attributes are fully functional dependent on the primary key
 Prime attribute − An attribute, which is a part of the candidate-key, is known as a prime
attribute.
 Non-prime attribute − An attribute, which is not a part of the prime-key, is said to be a non-
prime attribute.

Example 1:
In Second normal form, every non-prime attribute should be fully functionally dependent on prime
key attribute. That is, if X → A holds, then there should not be any proper subset Y of X, for which Y
→ A also holds true.

In Student_Project relation that the prime key attributes are Stu_ID and Proj_ID.

According to the rule, non-key attributes, i.e. Stu_Name and Proj_Name must be dependent upon
both and not on any of the prime key attribute individually.

But Stu_Name can be identified by Stu_ID and Proj_Name can be identified by Proj_ID
independently. This is called partial dependency, which is not allowed in Second Normal
Form.

In the above tables there is no partial dependency. So it is in second normal form.

Example: 2

TEACHER table

TEACHER_ID SUBJECT TEACHER_AGE

25 Chemistry 30
25 Biology 30
47 English 35
83 Math 38
83 Computer 38

In the given table, non-prime attribute TEACHER_AGE is dependent on TEACHER_ID which is

a proper subset of a candidate key. That's why it violates the rule for 2NF.
To convert the given table into 2NF, we decompose it into two tables:
TEACHER_DETAIL table:
TEACHER_ID TEACHER_AGE
25 30
47 35
83 38

TEACHER_SUBJECT table:

TEACHER_ID SUBJECT
25 Chemistry
25 Biology
47 English
83 Math
83 Computer

3. THIRD NORMAL FORM (3NF)

 A relation will be in 3NF if it is in 2NF and not contain any transitive partial dependency.
 If there is no transitive dependency for non-prime attributes, then the relation must be in third
normal form.

For a relation to be in Third Normal Form, it must be in Second Normal form and must satisfy −

 No non-prime attribute is transitively dependent on prime key attribute.

 For any non-trivial functional dependency, X → A, then either −
o X is a superkey or,
o A is a prime attribute, i.e., each element of Y is part of some candidate key.

Example:1
In the above Student_detail relation, Stu_ID is the key and only prime key attribute.

City can be identified by Stu_ID as well as Zip itself. Neither Zip is a superkey nor is City a
prime attribute. Additionally, Stu_ID → Zip → City, so there exists transitive dependency.

To bring this relation into third normal form, we break the relation into two relations as follows −

Now both the relations in Third Normal Form

Example 2: EMPLOYEE_DETAIL table:

EMP_ID EMP_NAME EMP_ZIP EMP_STATE EMP_CITY

222 Harry 201010 UP Noida
333 Stephan 02228 US Boston
444 Lan 60007 US Chicago
555 Katharine 06389 UK Norwich
666 John 462007 MP Bhopal

Super key in the table above:

{EMP_ID}, {EMP_ID, EMP_NAME}, {EMP_ID, EMP_NAME, EMP_ZIP}.. .so on

Candidate key: {EMP_ID}

Non-prime attributes: In the given table, all attributes except EMP_ID are non-prime.

Here, EMP_STATE & EMP_CITY dependent on EMP_ZIP and EMP_ZIP dependent on EMP_ID.
The non-prime attributes (EMP_STATE, EMP_CITY) transitively dependent on super key(EMP_ID).

It violates the rule of third normal form. That's why we need to move the EMP_CITY
and EMP_STATE to the new <EMPLOYEE_ZIP> table, with EMP_ZIP as a Primary
key.
After 3rd Normalization

EMPLOYEE table:

EMP_ID EMP_NAME EMP_ZIP

222 Harry 201010
333 Stephan 02228
444 Lan 60007
555 Katharine 06389
666 John 462007

EMPLOYEE_ZIP table:

EMP_ZIP EMP_STATE EMP_CITY

201010 UP Noida
02228 US Boston
60007 US Chicago
06389 UK Norwich
462007 MP Bhopal

4. BOYCE CODD NORMAL FORM (BCNF)

Boyce-Codd Normal Form (BCNF) is an extension of Third Normal Form.

BCNF states that For any non-trivial functional dependency, X → A, X must be a super-key.

In the above Example, Stu_ID is the super-key in the relation Student_Detail

and Zip is the super-key in the relation ZipCodes.

Stu_ID → Stu_Name, Zip

Zip → City

Which confirms that both the relations are in BCNF.

 BCNF is the advance version of 3NF. It is stricter than 3NF.
 A table is in BCNF if every functional dependency X → Y, X is the super key of the table.
 For BCNF, the table should be in 3NF, and for every FD, LHS is super key.

RESULT: Thus the Database Design Using Normalization was performed

EX. NO: 13 QUERYING THE OBJECT-RELATIONAL DATABASE USING
DATE: OBJET QUERY LANGUAGE

AIM:

To Query the Object-Relational database using Objet Query language

DESCRIPTION:

OBJECT QUERY LANGUAGE

 Object Query Language (OQL) is a version of the Structured Query Language. Like
SQL, OQL is a declarative (not procedural) language
 Object Query Language is a query language standard for object-oriented databases modeled
after SQL and developed by the Object Data Management Group.
 OQL is the way to access data in an O2 database. OQL is a powerful and easy-to-use SQL-
like query language with special features dealing with complex objects, values and methods.
 OQL is an attempt by the OO community to extend languages like C++ with SQL-
like, relation-at-a-time dictions.

CREATING DATABASE AND TABLE

Create databases and tables with the create command

Create command to create a

database create database

database_name ; Create command to

create a table

create table
database_name.table_name (
column_name [constraints ] [default ] ,
[ column_name [ constraints ] [default ] , ]
[ additional_columns ]
[ unique ( column_name ) , ]
[ counter ( column_name ) ,
]
[ timestamp ( column_name ) ]
);
Example 1

create database staff; // creates the staff

database The following insert defines the managers

table. create table staff.managers

EmployeeID int NOT NULL PRIMARY KEY,

Name text NOT NULL,

Department text default "Sales",

Gender text,

Age int,

unique ( EmployeeID )

);

For the managers table:

 The EmployeeID and Name columns cannot be NULL.

 The EmployeeID column is the primary key and must be unique.

 If no value is inserted into the Department column for a given record it takes the value "Sales".

Example 2

The following insert creates the staff.employees table.

create table staff.employees

EmployeeID int NOT NULL PRIMARY KEY,

Name text NOT NULL,

Skills text,

Gender text,

Age int

);

For the staff.employees table:

 The EmployeeID and Name columns cannot be NULL.

INSERTING DATA INTO A TABLE

Use the insert keyword to insert data into a table.

Syntax: insert into database_name.table_name

(
Column [ , column ] [ , column ]
[ ... ]
)
values
(
Data [ , data ] [ , data ]
[ ... ]
);

Specifying all the column names:

insert into
staff.managers (
EmployeeID, Name, Department, Gender, Age
)
values
(
1, "Matt", "Development", "M", 28
);

Does not specifying column names:

insert into

staff.managers values
(
2, "Jane", "Customer Services", "F", 27
);
SELECTING DATA FROM A TABLE

 Select keyword is used to Query or retrieve the data in a table

Syntax:
select comma_separated_column_list

from database_name.table_name

[ where conditional_test ]

[ order by field_name [asc|desc] ];

The * symbol can be used in a select statement to return all the columns of the table.

Example 1 : select * from staff.managers;

Output:

EmployeeID=1;
Name='Matt';
Department='Development';
Gender='M';
Age=28;
EmployeeID=2;
Name='Jane';
Department='Customer Services';
Gender='F';
Age=27;
( 2 record(s) : Transaction complete )

Example 2: select EmployeeID, Name from

staff.managers where Department =

"Development";

Output:

EmployeeID=1;
Name='Matt';
( 1 record(s) : Transaction complete )
UPDATE RECORDS IN TABLE

 Use the update keyword to update an existing record in a table.

Syntax

The following syntax shows how to use the update

keyword. update database_name.table_name

set column = value [ , column = value ... ]

[ where conditional_test ] ;

If the update statement is used without a where condition, all records are updated.

Example: Updates Age column of staff.managers table for any records where Name="John"

Update staff.managers

set Age=30

where Name="John";

DATABASE AND TABLE LISTINGS

 Use the show keyword to list the databases, columns, or tables or the current service.

Syntax

show databases ;

show tables from database_name ;

show table database_name.table_name ;

Example 1 shows all the databases of the current service.

show databases;

databases = [ 'staff'

( 1 Record(s) : Transaction complete )

Example 2 shows all the tables from the staff database.

show tables from staff;

tables = [ 'managers', 'employees', 'contractors'

] ( 1 Record(s) : Transaction complete )

DELETION OF A RECORD FROM A DATABASE TABLE

 Delete a record from a table using the delete command.

Syntax: how to use the delete command.

Delete from database_name.table_name [ where conditional_test ] ;

Example - Deletes all records from the staff.contractors table where Name="James".

delete from

staff.contractors where

Name="Jane";

Deletion of a database or table

 Delete a database or table using the drop command.

Syntax to use the drop command

drop table database_name.table_name

; drop database databse_name ;

Example deletes the entire staff.managers table.

drop table staff.managers;

drop database staff;

RESULT:
Thus the Querying of Object-Relational database using Objet Query language was executed
EX. NO: 14 OBJECT FEATURES OF SQL-UDTS AND SUB-TYPES, TABLES USING
UDTS, DATE: INHERITANCE, METHOD DEFINITION

AIM:
To Study about the various Object Features such as SQL-UDTS And Sub-Types, Tables
Using UDTS, Inheritance, Method Definition etc.,

DESCRIPTION

USER DEFINED TYPE (UDT)

 Most important object-oriented feature added to SQL is a user defined type
(UDT). The definition of a UDT is similar to the definition of a class.
 It can include attributes and methods and supports
inheritance. The following expression defines a new UDT called
Person.

create type Person as (pid varchar(10), pname varchar(20)) not final

 UDT Person could also be the root of an inheritance hierarchy

 Key word “not final” indicate that subtypes of Person can be defined.
 Create two new UDTs called customer and employee both of which inherit from Person.
 Key word “under” is similar to the key word “extend” in Java.

create type Customer under Person as

(...) create type Employee under Person

as (...)

Modifiers associated with create type statements include

 not instantiable - objects of that type cannot be created

 instantiable - Objects can be created similar to abstract classes in Java

 final - type cannot have subtypes (or subclasses)

SUPERTYPES AND SUBTYPES

A subtype can be derived from a supertype either directly or indirectly through levels of
other subtypes. A supertype can have multiple sibling subtypes, but a subtype can have at most one
direct parent supertype (single inheritance).
 Figure: Supertypes and Subtypes in Type Hierarchy

The hierarchy is shown as four boxes labelled A, B, C, and D.

 A is the supertype of all

 B and D are direct subtypes of A
 C is a direct subtype of B

TYPE INHERITANCE

create type Person (name varchar(20), address varchar (20));

create type Student under Person (degree varchar(20), department varchar

(20)); create type Teacher under Person (salary integer, department varchar

(20));

Both Student and Teacher inherit the attributes of Person—namely, name and address. Student
and Teacher are said to be subtypes of Person, and Person is a supertype of Student, as well as of
Teacher

Multiple inheritance:

create type TeachingAssistant under Student , Teacher;

TeachingAssistant inherits all the attributes of Student and Teacher.

Example Creation of a simple UDT hierarchy for a business. The hierarchy implements a Person
UDT and two subtypes:

 Agent and
 Customer.

create type Person as Object (pid varchar(10), pname

varchar(30), address varchar(50)) not final
create type Agent under Person (commission int) not final
create type Customer under Person (discount int) not
final

Objects can also be attributes of other objects. The Person type has been created to include an address
UDT. After the types are created a typed table of Agent objects is created and a new row is inserted
into the table.

create type Address as Object (street varchar(30), city

varchar(30), state char(2), zip char(10)) not final

create type Person as Object ( pid varchar(10), pname

varchar(30), pAddr Address) not final

create table Agents of Agent (pid primary key)

insert into Agents values ('001', 'John Smith', Address('123 Main', 'Lal street)

OBJECT FEATURES OF SQL

(a) Using UDTs as types for attributes such as Address and Phone

CREATE TYPE STREET_ADDR_TYPE AS (

NUMBER VARCHAR(5),
STREET_NAME VARCHAR(25),
APT_NO VARCHAR(5),
SUITE_NO VARCHAR(5)
);
CREATE TYPE USA_ADDR_TYPE AS (
STREET_ADDR STREET_ADDR_TYPE,
CITY VARCHAR(25),
ZIP VARCHAR(10)
 );
CREATE TYPE USA_PHONE_TYPE AS (
PHONE_TYPE VARCHAR(5),
AREA_CODE CHAR(3),
PHONE_NUM CHAR(7)
);

(b) Specifying UDT for PERSON_TYPE

CREATE TYPE PERSON_TYPE AS (

NAME VARCHAR(35),
SEX CHAR,
BIRTH_DATE DATE,
PHONES USA_PHONE_TYPE ARRAY[4],
ADDR USA_ADDR_TYPE
INSTANTIABLE
NOT FINAL
INSTANCE METHOD AGE() RETURNS INTEGER;
CREATE INSTANCE METHOD AGE() RETURNS
INTEGER
FOR PERSON_TYPE
BEGIN
RETURN /* CODE TO CALCULATE A PERSON’S AGE FROM TODAY’S DATE AND BIRTH_DATE */
END;
);

(c) Specifying UDTs for STUDENT_TYPE and EMPLOYEE_TYPE as two subtypes of

PERSON_TYPE.

CREATE TYPE GRADE_TYPE AS (

COURSENO CHAR(8),
SEMESTER VARCHAR(8),
YEAR CHAR(4),
GRADE CHAR
);
CREATE TYPE STUDENT_TYPE UNDER PERSON_TYPE AS (
MAJOR CODE CHAR (4),
STUDENT_ID CHAR (12),
DEGREE VARCHAR (5),
TRANSCRIPT GRADE_TYPE ARRAY
[100]
INSTANTIABLE
NOT FINAL
INSTANCE METHOD GPA() RETURNS FLOAT;
CREATE INSTANCE METHOD GPA() RETURNS
FLOAT
FOR STUDENT_TYPE
BEGIN
RETURN /* CODE TO CALCULATE A STUDENT’S GPA */
END;
);
CREATE TYPE EMPLOYEE_TYPE UNDER PERSON_TYPE AS (
JOB_CODE CHAR(4),
SALARY FLOAT,
SSN CHAR(11)
INSTANTIABLE
NOT FINAL
);
CREATE TYPE MANAGER_TYPE UNDER EMPLOYEE_TYPE AS (
DEPT_MANAGED CHAR (20)
INSTANTIABLE
);

(d) Creating tables based on some of the UDTs, and illustrating table inheritance,

CREATE TABLE PERSON OF PERSON_TYPE

CREATE TABLE EMPLOYEE OF
EMPLOYEE_TYPE
UNDER PERSON;
CREATE TABLE MANAGER OF MANAGER_TYPE
UNDER EMPLOYEE;
CREATE TABLE STUDENT OF STUDENT_TYPE
UNDER PERSON;

RESULT:
Thus various Object Features such as SQL-UDTS And Sub-Types, Tables Using
UDTS, Inheritance, Method Definition have been studied.
EX. NO: 15 DEVELOP DATABASE APPLICATIONS USING IDE (NETBEANS)
DATE:
AIM:
To Develop Database Applications Using IDE (Netbeans) To Connect Java Program With
MS-Access Using JDBC Connectivity

JDBC DESCRIPTION

Java has defined its own API (JDBC API) that uses JDBC drivers (written in Java language).

JDBC API is used to handle database using Java program and can perform the following

activities:

1. Connect to the database

2. Execute queries and update statements to the database
3. Retrieve the result received from the database.

Java Database Connectivity with 5 Steps

There are 5 steps to connect any java application with the database using JDBC.
These steps are as follows:

1) Register the driver class

2) Create the connection object

3) Create the Statement object

4) Execute the query

5) Close the connection object

Connect Java Application with access with DSN

Creating a MS Access Database

Step 1: Open Microsoft Access and select Blank database option and give the database name as File
name option
Step 2: Create a table and insert your data into the table

Step 3: Save the table with the desired name;, we save the following records with the table
name student.

Now Creating DSN of your database

Step 4: Open your Control Panel and then select Administrative

Tools. Step 5 : Click on Data Source(ODBC)-->System DSN.

Step 6: Now click on add option for making a new DSN.select Microsoft Access Driver (*.mdb.
*.accdb) and then click on Finish

Step 7: Make your desired Data Source Name and then click on the Select option, for example in this
article we use the name mydsn
 Step 8: Now you select your data source file for storing it and then click ok and then click on Create
and Finish

Step 9: Java program

code Test.java
import java.sql.*;
class TestDB
{
public static void main(String ar[])
{
Try
 {
String url="jdbc:odbc:mydsn";
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection c=DriverManager.getConnection(url);
Statement st=c.createStatement();
ResultSet rs=st.executeQuery("select * from
login"); while(rs.next())
{
System.out.println(rs.getString(1) + " " + rs.getString(2)+ " " rs.getString(2));
}
}
catch(Exception ee){System.out.println(ee);
} } }

RESULT:
Thus the Database Applications Using IDE (Netbeans) To Connect Java Program With MS-
Access Using JDBC Connectivity is executed.
CONTENT BEYOND THE LAB SYLLABUS
Ex.No :1
NOSQL Commands using MONGODB,CASSANDRA,HIVE,ORIENTDB
Date:

A) MONG
ODBAIM
:
To write NOSQL QUERIES to understand the concept of Open Source Database
ManagementSystem such as MongoDB.
PROCEDURE:
Step 1: Start the MongoDB Server (Mongos).
Step 2: Start the Client (Mongod)
Step 3: Perform the MongoDB Curd Operations such as (Create,
Update,Read,Delete).Syntax: To Create/Select a Collection: USE
DATABASE_NAME.
Syntax: To Insert: DB.COLLECTION_NAME.INSERT(DOCUMENT).
Syntax: To Update: DB.COLLECTION_NAME.UPDATE(<FILTER>, <UPDATE> )
Syntax: To Display/Search: DB.COLLECTION_NAME.FIND ()
Syntax: To Delete: DB.COLLECTION_NAME.REMOVE (DELETION_CRITERIA)

QUERIES WITH EXECUTION:

Create :
> use mca;
switched to
db mca

Insert:

> db.mca.studentInfo.insertOne({name: "Surya", age: 23, town: "Salem"});

{
"acknowledged" : true,
"insertedId" : ObjectId("62f34f875705a8979214f6cd")
}

> db.mca.studentInfo.insertMany([{name: "Arya", age: 23, town: "USA"},

... {name: "Saran", age: 22, town: "Salem"},
... {name: "Ajith", age: 21, town: "Namakkal"},
... {name: "Vijay", age: 40, town: "Chennai"}
... ]);
{
"acknowledged" :
true,"insertedIds" :
[
ObjectId("62f34fde5705a8979214f6c
 e"),
ObjectId("62f34fde5705a8979214f6c
f"),
ObjectId("62f34fde5705a8979214f6d0
"),
ObjectId("62f34fde5705a8979214f6
d1")
]
}

Update:

> db.mca.studentInfo.updateOne({name: "Vijay"}, {$set:{age: 22}});

Delete:

> db.mca.studentInfo.deleteOne({name: "Arya"})

> db.mca.studentInfo.remove({name: "Vijay"})

Find:

> db.mca.studentInfo.find({})

RESULT:

Thus the above MongoDB Queries has been executed successfully.

B) CASSANDRA

AIM:
To write NOSQL QUERIES to understand the concept of Open Source Database Management
System such as CASSANDRA.

PROCEDURE:

Step 1: Start the CASSANDRA Server (Cassandra) using CMD.

Step 2: Start the Client (CQLSH.py) using CMD.
Step 3: Perform the Cassandra Table Operation, Curd Operation and CQL Types.
Cassandra Table Operations:

1. Create Key Space in Cassandra. CREATE KEYSPACE <identifier> WITH <properties>

2. To Create Cassandra Table, Using Create Command.
3. To Change the structure of the table, Using Alter Command.
4. To delete the existing table in Cassandra, Using Truncate Command.
5. To Insert the values in CQL, use insert command
6. The SELECT command is used to read data from Cassandra table
7. The UPDATE command is used to update the existing data in a Cassandra.
8. The DELETE command is used to delete data from
Cassandra Step 4 : Close the command prompt
Step 5 : Stop the Server.

QUERIES WITH EXECUTION:

Create KeySpace:

cqlsh>CREATE KEYSPACE gct WITH replication = {'class':'SimpleStrategy',

'replication_factor': 3};

cqlsh>describe keyspaces; //Display the Created KeySpaces

system_auth system_distributed gct system_schema system system_traces

cqlsh>use gct;

Create Table
cqlsh:gct>CREATE TABLE VVT(Id int PRIMARY KEY,name text,city text,fees varint);
Alter Table
cqlsh:gct>ALTER TABLE VVT ADD email text;

View Table
cqlsh:gct> select * from vvt;
 id | city | email | fees | name
+ + + + (0 rows)
Alter Table
cqlsh:gct> ALTER TABLE VVT DROP
email;cqlsh:gct> select * from vvt;

id | city | fees | name

+ + + (0 rows)

Truncate Data
cqlsh:gct> TRUNCATE VVT;
cqlsh:gct> INSERT INTO VVT(Id, fees,name,city)VALUES(1,5000, 'Surya
S','Namakkal');cqlsh:gct> select * from vvt;

id | city | fees | name

+ + + 1 | Namakkal | 5000 | Surya S (1 rows)
Update Data
cqlsh:gct> UPDATE VVT SET fees= 500, Name='Surya S' WHERE id=1;
cqlsh:gct> select * from vvt;

select * from vvt;

id | city | fees | name

+ + + 1 | Namakkal | 500 | Surya S2 | Aathur | 5000 | Rahul
3 | Salem | 5000 | Partha

Delete Data
cqlsh:gct> DELETE FROM VVT WHERE
id=3;cqlsh:gct> select * from vvt;

id | city | fees | name

+ + + 1 | Namakkal | 500 | Surya S 2 | Aathur | 5000 | Rahul (2 rows)
Drop Table
cqlsh:gct> describe
columnfamiliesvvt
cqlsh:gct> DROP TABLE VVT;
cqlsh:gct> describe
columnfamilies
<empty
>
cqlsh:g
ct>

RESULT:

Thus the above Cassandra Queries has been executed successfully.

C)HIVE:

AIM:
To write NOSQL QUERIES to understand the concept of Open Source Database Management System
such as HIVE.

PROCEDURE:
Step 1: Start the Hadoop Cluster from sbin Folder (Run start-dfs, start-yarn).
Step 2: Start the derby node using the command(StartNetworkServer -h
0.0.0.0)Step 3: Then Start the Hive.(Hive command)
Step 4: Hive data types are categorized in numeric types, string types, misc types, and complex types.
Step 5: Syntax to Create a Database.first we have to check weather the DB is Already Exist or Not for
thatshow database;
Step 6: if Not Exist Create Database Database_Name;
Step 7: Perform Some Table Operations in Hive such as Create, Alter, Drop Table.
Step 8: Finally Partitioning the Hive.

QUERIES WITH EXECUTION:

hive> show
databases;OK
Default
Time taken: 0.271 seconds, Fetched: 1
row(s)hive> create database demo;
hive> show
databases;OK
Time taken: 0.215 seconds
hive> create a database if not exists
demo;OK
Time taken: 0.107 seconds
hive>create database demo WITH DBPROPERTIES('creator' = 'Natz', 'date' = '2019-06-03');
OK
Time taken: 2.389 seconds
hive> create table demo.employee (Id int, Name string , Salary float);
OK
Time taken: 0.461 seconds
hive> select * from
demo.employee;OK
1 “SURYA S” 30000.0
2 “SUNDAR S” 40000.0
3 “SURESH C” 50000.0
4 “MUNISH” 90000.0
hive> describe
demo.employeeOK
id int
Name
string
salary
fl
oat
Time taken: 0.215 seconds
hive> Alter table employee rename to
employee_data;OK
Time taken: 6.06 seconds
hive> describe
employee_data;OK
id int
Name
string
salary
fl
oat
Time taken: 0.275
secondshive> show
tables;
OK
Employee
Employee_d
ata
Time taken: 0.098 seconds, Fetched: 2 row(s)

hive>Alter table employee_data add columns (age

int);OK
id

int
Name
string
salary
floatage
Time taken: 0.275

secondshive> show

tables;

OK
employee
employee_d
ata
Time taken: 0.098 seconds, Fetched: 2 row(s)

hive> drop table

new_employee;OK
Time taken: 17.5
secondshive> show
tables;
OK
emp
emplo
yee
Time taken:
0.098 seconds
hive>
drop
database demo; OK
Time taken: 2.354 seconds

Static Partitioning

hive> use test;

hive> create table student (id int, name string, age int, institute string) partitioned by
(course string);OK
Time taken: 3.054 seconds

hive> describe
student;OK
id int
name string
age int
institute course
course string
# Partition Information
# col_name data_type comment

Course string
Time taken: 1.054 seconds, Fetched: 10 row(s)

RESULT:

Thus the above Hive Queries has been executed successfully.

C) ORIENTDB:

AIM:
To write NOSQL QUERIES to understand the concept of Open Source Database Management
System such as OrientDB Graph.

PROCEDURE:

Step1: Start the Server form the Orientdb Bin folder.

Step2: Use the respective url http://192.168.43.111:2480/studio/index.html to login to OrientDb browser.

Step3: Choose the Database and enter the username and password for the OrientDB

Server. Step4: In the Menu choose the Graph Tab.

Step5: Create a Class, Node, Edges and Insert the fields Finally connect the graph display the output.

Step6: Logout from the Browser.

Step7: Stop the Server.

QUERIES

create class gct extends v

create vertex gct set
name='ahmed',class='I_MCA' create vertex gct
set name='bala',class='I_MCA' create vertex gct
set name='partha',class='I_MCA' create vertex
gct set name='praveen',class='I_MCA'create
vertex gct set name='tamil',class='I_MCA' create
vertex gct set name='ragul',class='I_MCA' create
edge from #43:0 to #44:0
create edge from #43:0 to #45:0
create edge from #46:0 to #46:0
create edge from #43:1 to #44:0
create edge from #44:1 to #45:0
select *from gct;
OUTPUT:

RESULT:

Thus the above Orient DB Queries has been executed successfully.

Ex.No :2
Spatial Data Storage and Retrieval in MYSQL
Date:

AIM:

To implement spatial data storage and retrieval in MYSQL.

PROCEDURE:

Step 1: Start the MYSQL

Server.Step 2: Create a

Database.

Step 3: Create a table with spatial data

type.Step 4: Insert the spatial values in

the table.

Step 5: Display the output using the select command

Step 6: In the output screen Select Form Editor output will be displayed.
QUERIES WITH EXECUTION:

CREATE DATABASE spatial1;

CREATE TABLE
spatial1.restaurants (name
VARCHAR(20),
location GEOMETRY NOT
NULL,SPATIAL
INDEX(location)
);

INSERT INTO spatial1.restaurants

VALUES('Salem', ST_GeomFromText('POINT(11.659365 78.157068)')),
('Erode', ST_GeomFromText('POINT(11.324675 77.723072)')),
('Coimbatore', ST_GeomFromText('POINT(10.993233 76.973483)')),
('Madurai', ST_GeomFromText('POINT(9.883899 78.130947)')),
('Sivakasi', ST_GeomFromText('POINT(9.451873 77.797697)')),
('Tirunelveli', ST_GeomFromText('POINT(8.727173 77.703005)')),
('Rameswaram', ST_GeomFromText('POINT(9.268303 79.243055)')),
('Thanjavur', ST_GeomFromText('POINT(10.782451 79.137895)')),
('Puducherry', ST_GeomFromText('POINT(11.929367 79.809884)'));

SELECT name, ST_AsText(location)FROM spatial1.restaurants

WHERE ST_Distance_Sphere(location,
ST_GeomFromText('POINT(11.65936578.157068)'))<=100*1000
ORDER BY name;
Output:
Additional Program:
CREATE DATABASE spatial2

CREATE TABLE restaurants (name

VARCHAR(100),location GEOMETRY NOT
NULL,
SPATIAL INDEX(location)
);

INSERT INTO restaurants VALUES ('Restaurant

1', ST_GeomFromText('POINT(-26.66115
40.95858)'));
INSERT INTO restaurants VALUES ('Restaurant
2', ST_GeomFromText('POINT(-26.68685
40.93992)'));
INSERT INTO restaurants VALUES ('Restaurant
3', ST_GeomFromText('POINT(-31.11924
42.39557)'));

SELECT name,AsText(location) FROM restaurants WHERE

ST_Distance_Sphere(location,ST_GeomFromText('POINT(-26.640.9)'))<=10*1000
ORDER BY name;
RESULT:

Thus the above program for spatial data storage and retrieval in MYSQL executed successfully.