[go: up one dir, main page]

0% found this document useful (0 votes)
7 views81 pages

CP4152 DP Lab Manual

The document outlines a practical record for students at Dr. G.U. Pope College of Engineering, detailing various database management experiments and SQL commands. It includes sections on Data Definition Language, Data Manipulation Language, and different types of joins in SQL, along with instructions for MySQL installation. The document serves as a guide for students to document their practical work and understanding of database concepts during the academic year.

Uploaded by

DAN ANGEL
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
7 views81 pages

CP4152 DP Lab Manual

The document outlines a practical record for students at Dr. G.U. Pope College of Engineering, detailing various database management experiments and SQL commands. It includes sections on Data Definition Language, Data Manipulation Language, and different types of joins in SQL, along with instructions for MySQL installation. The document serves as a guide for students to document their practical work and understanding of database concepts during the academic year.

Uploaded by

DAN ANGEL
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 81

CSI THOOTHUKUDI-NAZARETH DIOCESE

Dr.G.U.POPE COLLEGE OF ENGINEERING


POPE NAGAR, SAWYERPURAM-628 251
Thoothukudi District

Register Number:

Certified that this is a bonafide record of work done by


the student Selvan/Selvi........................................................... of. ................ in
.............................................................................during the academic year ..............

Staff Incharge Head of Dept.

Submitted for the University Practical Examination held on...............................

Internal Examine External Examiner


TABLE OF CONTENTS

S.NO. DATE EXPERIMENT TITLE MARKS/10 SIGN.

1. Practicing Data Definition Language and Data


Manipulation Language

2. A) Practicing EquiJoin, Left Outer Join, Right Outer


Join and full outer join
B) Practicing Aggregate Functions

C) Practicing Set Operations

D) Practicing Transaction Control Language

3. Distributed Database Design and Implementation

4. Practicing Triggers

5. Accessing a Relational Database using PHP

6. Creating XML Documents, Document Type


Definition and XML Schema

7. Using a Relational Database to store the XML


documents as text

8. Using a Relational Database to store the XML


documents as data elements

9. A) Extracting XML Documents from Relational


Databases
B) XML Querying

10. Creating Databases using MongoDB, DynamoDB,


Voldemort Key-Value Distributed Data Store Hbase
and Neo4j.
2

INTRODUCTION

Database Management System


This model is like a hierarchical tree structure, used to construct a hierarchy of
records in the form of nodes and branches. The data elements present in the
structure have Parent-Child relationship. Closely related information in the
parent-child structure is stored together as a logical unit. A parent unit may have
many child units, but a child is restricted to have only one parent.
The drawbacks of this model are:
The hierarchical structure is not flexible to represent all the relationship
proportions, which occur in the real world. It cannot demonstrate the overall data
model for the enterprise because of the nonavailability of actual data at the time
of designing the data model. It cannot represent the Many-to-Many relationship.
Network Model
It supports the One-To-One and One-To-Many types only. The basic objects in
this model are Data Items, Data Aggregates, Records and Sets. It is an
improvement on the Hierarchical Model. Here multiple parent-child relationships
are used. Rapid and easy access to data is possible in this model due to multiple
access paths to the data elements.
Relational Model
Does not maintain physical connection between relations Data is organized in
terms of rows and columns in a table The position of a row and/or column in a
table is of no importance The intersection of a row and column must give a single
value
Features of an RDBMS
The ability to create multiple relations and enter data into them An attractive
query language Retrieval of information stored in more than one table An
RDBMS product has to satisfy at least Seven of the 12 rules of Codd to be
accepted as a full- fledged RDBMS.
Relational Database Management System
RDBMS is acronym for Relation Database Management System. Dr. E. F. Codd
first introduced the Relational Database Model in 1970. The Relational model
allows data to be represented in a simple row- column. Each data field is
considered as a column and each record is considered as a row. Relational
Database is more or less similar to Database Management S ystem. In relational
model there is relation between their data elements. Data is stored in tables.
Tables have columns, rows and names.
SQL supports the following categories of commands:
1. Data Definition Language
Create, Alter, Drop and Truncate
2. Data Manipulation Language
Insert, Update, Delete and Select
3. Transaction Control Language Commit, Rollback and Save point
3

4. Data Control Language


Grant and Revoke
Data type Description
Char(Size)
Stores fixed-length character data to store alphanumeric values, with a maximum
size of 2000 bytes. Default and minimum size is 1 byte.
Varchar2(Size)
Stores variable-length character data to store alphanumeric values, with
maximum size of 4000 bytes.
char(Size)
Stores fixed-length character data of length size characters or bytes, depending
on the choice of national character set. Maximum size if determined by the
number of bytes required storing each character with an upper limit of 2000 bytes.
Default and minimum size is 1 character or 1 byte, depending on the character
set.
Nvarchar2(Size)
Stores variable-length character string having maximum length size characters or
bytes, depending on the choice of national character set. Maximum size is
determined by the number of bytes required to store each character, with an upper
limit of 4000 bytes.
AIM: Installation of MySQL and practicing DDL & DML commands.
1. Steps for installing MySQL
Step1
Make sure you already downloaded the MySQL essential 5.0.45 win32.msi
file. Double click on the .msi file.
Step2
This is MySQL Server 5.0 setup wizard. The setup wizard will install
MySQL Server 5.0 release 5.0.45 on your computer. To continue, click next.

Step3
4

Choose the setup type that best suits your needs. For common program
features select Typical and it’s recommended for general use. To continue,
click next.

Step 4
This wizard is ready to begin installation. Destination folder will be in
C:\Program Files\MySQL\MySQL Server 5.0\. To continue, click next.

Step5
The program features you selected are being installed. Please wait while the
setup wizard installs MySQL 5.0. This may take several minutes.
5

Step6
To continue, click next

Step7
To continue, click next.
6

Step8
Wizard Completed. Setup has finished installing MySQL 5.0. Check the
configure the MySQL server now to continue. Click Finish to exit the
wizard

Step9
The configuration wizard will allow you to configure the MySQL Server 5.0
server instanceTo continue, click next.

Step10
Select a standard configuration and this will use a general purpose
configuration for the server that can be tuned manually. To continue, click
next.
7

Step11
Check on the install as windows service and include bin directory in
windows path. To continue, click next.

Step12
Please set the security options by entering the root password and confirm
retype the password. continue, click next
8

Step13
Ready to execute? Clicks execute to continue.

Processing configuration in progress

Step15
Configuration file created. Windows service MySQL5 installed. Press
finish to close the wizard.
9

EX NO:1 Practicing Data Definition Language and Data


Manipulation Language
Date:

AIM:
To practice the database Data Definition Language and Data Manipulation
Language.
Data Definition Language
The data definition language is used to create an object, alter the structure
of an object and also drop already created object. The Data Definition
Languages used for table definition can be classified into following:
• Create table command
• Alter table command
• Drop table command
Data Manipulation Language
• Insert, Delete, Update
Creating of Tables on Student:
Table is a primary object of database, used to store data in form of rows
and columns. It is created using following command:
Create Table (column1 datatype(size), column2 datatype(size),column(n)
datatype(size));
Example(Create)
create table student(name varchar(50),rollNo int(10),branch varchar(5));

Desc command
Describe command is external command . The describe command is used
to view the structure of a table as follows.

MySQL [college]> desc students;


+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| roll_no | int(11) | YES | | NULL | |
| student_name | varchar(150) | YES | | NULL | |
| course | varchar(150) | YES | | NULL | |
+ + + + + + +
3 rows in set (0.268 sec)
10

Inserting of Tables on Student:

INSERT INTO table_name (column1, column2, column3, ...)


VALUES (value1, value2, value3, ...);
Example:
MySQL [college]> insert into students values(1,'ashish','java');
Query OK, 1 row affected (0.177 sec)

MySQL [college]> Insert into students values(2,’rahul’,’C++’);


Query OK, 1 row affected (0.040 sec)
MySQL [college]> select * from students;
+ + + +
| roll_no | student_name | course |
+ + + +
| 1 | ashish | java |
| 2 | rahul | C++ |
+ + + +
2 rows in set (0.015 sec)
11

MySQL [college]> desc students;


+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| roll_no | int(11) | YES | | NULL | |
| student_name | varchar(150) | YES | | NULL | |
| course | varchar(150) | YES | | NULL | |
+ + + + + + +
3 rows in set (0.135 sec)

The SQL UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

UPDATE Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

update students set roll_no=roll_no+10 where student_name='ashish';


MySQL [college]> update students set roll_no=roll_no+10 where
12

student_name='ashish';
Query OK, 1 row affected (0.148 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [college]>
MySQL [college]> update students set roll_no=roll_no+10 where
student_name='ashish';
Query OK, 1 row affected (0.127 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [college]> select * from students;
+ + + +
| roll_no | student_name | course |
+ + + +
| 21 | ashish | java |
| 2 | rahul | C++ |
| 3 | judy | Arch |
+ + + +
3 rows in set (0.001 sec)
13

MySQL [college]> update students12 set student_name='aman' where


roll_no=2;
ERROR 1146 (42S02): Table 'college.students12' doesn't exist
MySQL [college]> update students 12 set student_name='aman' where
roll_no=2;
MySQL [college]> select * from students;
+ + + +
| roll_no | student_name | course |
+ + + +
| 21 | ashish | java |
| 2 | rahul | C++ |
| 3 | judy | Arch |
+ + + +
3 rows in set (0.001 sec)
14

The SQL DELETE Statement:


DELETE:

The DELETE statement is used to delete existing records in a table.

DELETE Syntax
DELETE FROM table_name WHERE condition;
Example:
delete from students where roll_no=11;
MySQL [college]> delete from students where roll_no=11;
Query OK, 0 rows affected (0.023 sec)
MySQL [college]> select * from students;
+ + + +
| roll_no | student_name | course |
+ + + +
| 21 | ashish | java |
| 2 | aman | C++ |
| 3 | judy | Arch |
+ + + +
3 rows in set (0.001 sec)
15

MySQL [college]> delete from students where roll_no=21;


Query OK, 1 row affected (0.118 sec)
MySQL [college]> select * from students;
+ + + +
| roll_no | student_name | course |
+ + + +
| 2 | aman | C++ |
| 3 | judy | Arch |
+ + + +
2 rows in set (0.000 sec)
MySQL [college]> delete from students where student_name= 'divya';
Query OK, 0 rows affected (0.001 sec)
MySQL [college]> select * from students;
+ + + +
| roll_no | student_name | course |
+ + + +
| 2 | aman | C++ |
| 3 | judy | Arch |
+ + + +
2 rows in set (0.000 sec)
16

MySQL [college]> delete from students where student_name= 'aman';


Query OK, 1 row affected (0.037 sec)
MySQL [college]> select * from students;
+ + + +
| roll_no | student_name | course |
+ + + +
| 3 | judy | Arch |
+ + + +
1 row in set (0.000 sec)

Enforce Primary Key:

MySQL [college]> CREATE TABLE t1 (


-> id INT PRIMARY KEY,
-> `desc` VARCHAR(30)
-> );
Query OK, 0 rows affected (0.243 sec)
MySQL [college]> select * from t1;
17

Empty set (0.001 sec)


MySQL [college]> desc t1;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| id | int(11) | NO | PRI | NULL | |
| desc | varchar(30) | YES | | NULL | |
+ + + + + + +
2 rows in set (0.077 sec)

Result:

Thus the DDL and DML Language commands were practiced and
implemented.
18

EX NO:2a Practicing EquiJoin, Left Outer Join, Right Outer Join and
Full Outer Join
Date:
AIM:
To practice the Equi Join, Left Outer Join, Right Outer Join and Full Outer
Join commands .
Description:
Inner Join
Inner join, includes only those tuples that satisfy the matching criteria.
EQUI Join
When a theta join uses only equivalence condition, it becomes a equi join.
Natural Join(⋈)
Natural join can only be performed if there is a common attribute (column)
between the relations.
Outer Join
In an outer join, along with tuples that satisfy the matching criteria.
Left Outer Join( )
In the left outer join, operation allows keeping all tuple in the left relation.
Right Outer join( )
In the right outer join, operation allows keeping all tuple in the right relation.
Inner Join Syntax:
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Example:
Create Table as t1,t2
MySQL [college]> CREATE TABLE t1 (
-> id INT PRIMARY KEY,
19

-> `desc` VARCHAR(30)


-> );
Query OK, 0 rows affected (0.243 sec)
MySQL [college]> select * from t1;
Empty set (0.001 sec)
MySQL [college]> create table t1(id int primary key,'desc' varchar(30));
MySQL [college]> desc t1;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| id | int(11) | NO | PRI | NULL | |
| desc | varchar(30) | YES | | NULL | |
+ + + + + + +
2 rows in set (0.077 sec)
MySQL [college]> CREATE TABLE t2 (
-> id INT PRIMARY KEY,
-> `desc` VARCHAR(30)
-> );
Query OK, 0 rows affected (0.153 sec)
MySQL [college]> desc t2;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| id | int(11) | NO | PRI | NULL | |
| desc | varchar(30) | YES | | NULL | |
+ + + + + + +
2 rows in set (0.022 sec)
20

Insert the values to t1,t2:

MySQL [college]> INSERT INTO t1 VALUES


-> (1, 'ID 1 in t1'),
-> (2, 'ID 2 in t1'),
-> (3, 'ID 3 in t1');
Query OK, 3 rows affected (0.107 sec)
Records: 3 Duplicates: 0 Warnings: 0
MySQL [college]> desc t1;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| id | int(11) | NO | PRI | NULL | |
| desc | varchar(30) | YES | | NULL | |
+ + + + + + +
2 rows in set (0.026 sec)
21

MySQL [college]> select * from t1;


+ + +
| id | desc |
+ + +
| 1 | ID 1 in t1 |
| 2 | ID 2 in t1 |
| 3 | ID 3 in t1 |
+ + +
3 rows in set (0.001 sec)

MySQL [college]> INSERT INTO t2 VALUES


-> (2, 'ID 2 in t2'),
-> (3, 'ID 3 in t2'),
-> (4, 'ID 4 in t2');
Query OK, 3 rows affected (0.125 sec)
Records: 3 Duplicates: 0 Warnings: 0
MySQL [college]> select * from t2;
22

+ + +
| id | desc |
+ + +
| 2 | ID 2 in t2 |
| 3 | ID 3 in t2 |
| 4 | ID 4 in t2 |
+ + +
3 rows in set (0.001 sec)

INNER JOIN QUERY Example


SELECT * FROM t1 INNER JOIN t2;
Example:
MySQL [college]> SELECT *
-> FROM t1 INNER JOIN t2;
MySQL [college]> SELECT *
-> FROM t1 INNER JOIN t2;
+ + + + +
| id | desc | id | desc |
23

+ + + + +
| 1 | ID 1 in t1 | 2 | ID 2 in t2 |
| 2 | ID 2 in t1 | 2 | ID 2 in t2 |
| 3 | ID 3 in t1 | 2 | ID 2 in t2 |
| 1 | ID 1 in t1 | 3 | ID 3 in t2 |
| 2 | ID 2 in t1 | 3 | ID 3 in t2 |
| 3 | ID 3 in t1 | 3 | ID 3 in t2 |
| 1 | ID 1 in t1 | 4 | ID 4 in t2 |
| 2 | ID 2 in t1 | 4 | ID 4 in t2 |
| 3 | ID 3 in t1 | 4 | ID 4 in t2 |
+ + + + +
9 rows in set (0.001 sec)

MySQL [college]> SELECT *


-> FROM t1 INNER JOIN t2 ON t1.id = t2.id;
+ + + + +
| id | desc | id | desc |
+ + + + +
24

| 2 | ID 2 in t1 | 2 | ID 2 in t2 |
| 3 | ID 3 in t1 | 3 | ID 3 in t2 |
+ + + + +
2 rows in set (0.023 sec)
MySQL [college]> SELECT *
-> FROM t1 INNER JOIN t2 ON t1.id = t2.id;
+ + + + +
| id | desc | id | desc |
+ + + + +
| 2 | ID 2 in t1 | 2 | ID 2 in t2 |
| 3 | ID 3 in t1 | 3 | ID 3 in t2 |
+ + + + +
2 rows in set (0.001 sec)

Normal Join:
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id;
Example:
MySQL [college]> SELECT *
-> FROM t1 JOIN t2 ON t1.id = t2.id;
25

+ + + + +
| id | desc | id | desc |
+ + + + +
| 2 | ID 2 in t1 | 2 | ID 2 in t2 |
| 3 | ID 3 in t1 | 3 | ID 3 in t2 |
+ + + + +
2 rows in set (0.001 sec)
LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
Example:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id;
MySQL [college]> SELECT *
-> FROM t1 LEFT JOIN t2 ON t1.id = t2.id;
+ + + + +
| id | desc | id | desc |
+ + + + +
| 1 | ID 1 in t1 | NULL | NULL |
| 2 | ID 2 in t1 | 2 | ID 2 in t2 |
| 3 | ID 3 in t1 | 3 | ID 3 in t2 |
+ + + + +
3 rows in set (0.029 sec)
MySQL [college]> SELECT *
-> FROM t1 LEFT JOIN t2 USING (id);
26

+ + + +
| id | desc | desc |
+ + + +
| 1 | ID 1 in t1 | NULL |
| 2 | ID 2 in t1 | ID 2 in t2 |
| 3 | ID 3 in t1 | ID 3 in t2 |
+ + + +
3 rows in set (0.001 sec)
MySQL [college]> SELECT t1.id, t1.desc
-> FROM t1 LEFT JOIN t2 USING (id)
-> WHERE t2.id IS NULL;
+ + +
| id | desc |
+ + +
| 1 | ID 1 in t1 |
+ + +
1 row in set (0.017 sec)
RIGHT JOIN Syntax
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
Example:
SELECT *FROM t1 RIGHT JOIN t2 ON t1.id = t2.id;
MySQL [college]> SELECT *
-> FROM t1 RIGHT JOIN t2 ON t1.id = t2.id;
27

+ + + + +
| id | desc | id | desc |
+ + + + +
| 2 | ID 2 in t1 | 2 | ID 2 in t2 |
| 3 | ID 3 in t1 | 3 | ID 3 in t2 |
| NULL | NULL | 4 | ID 4 in t2 |
+ + + + +
3 rows in set (0.001 sec)
MySQL [college]> SELECT *
-> FROM t1 RIGHT JOIN t2 USING (id);
+ + + +
| id | desc | desc |
+ + + +
| 2 | ID 2 in t2 | ID 2 in t1 |
| 3 | ID 3 in t2 | ID 3 in t1 |
| 4 | ID 4 in t2 | NULL |
+ + + +
3 rows in set (0.001 sec)

Result:
Thus the Equi Join, Left Outer Join, Right Outer Join and Full Outer Join
commands were practiced and implemented.
28

EX NO:2b Practicing Aggregate Functions


Date:

AIM:
To practice the Aggregate Functions commands .
Aggregate Functions
COUNT()
AVG
SUM()
MIN()
MAX()
COUNT()
The COUNT() function returns the number of rows that matches a
specified criterion.
COUNT() Syntax
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
Table Creation(Product table)
MySQL [college]> CREATE TABLE IF NOT EXISTS products (
-> productID INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> productCode CHAR(3) NOT NULL DEFAULT '',
-> name VARCHAR(30) NOT NULL DEFAULT '',
-> quantity INT UNSIGNED NOT NULL DEFAULT 0,
-> price DECIMAL(7,2) NOT NULL DEFAULT 99999.99,
-> PRIMARY KEY (productID)
-> );
Query OK, 0 rows affected (0.132 sec)
29

MySQL [college]> SHOW TABLES;


+ +
| Tables_in_college |
+ +
| comp |
| products |
| students |
| t1 |
| t2 |
+ +
5 rows in set (0.053 sec)
MySQL [college]> DESCRIBE products;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| productID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| productCode | char(3) | NO | | | |
| name | varchar(30) | NO | | | |
| quantity | int(10) unsigned | NO | |0 | |
| price | decimal(7,2) | NO | | 99999.99 | |
+ + + + + + +
5 rows in set (0.039 sec)
MySQL [college]> SHOW CREATE TABLE products \G
*************************** 1. row ***************************
Table: products
Create Table: CREATE TABLE `products` (
`productID` int(10) unsigned NOT NULL AUTO_INCREMENT,
30

`productCode` char(3) NOT NULL DEFAULT '',


`name` varchar(30) NOT NULL DEFAULT '',
`quantity` int(10) unsigned NOT NULL DEFAULT '0',
`price` decimal(7,2) NOT NULL DEFAULT '99999.99',
PRIMARY KEY (`productID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.009 sec)

MySQL [college]> INSERT INTO products VALUES (1001, 'PEN', 'Pen


Red', 5000, 1.23);
Query OK, 1 row affected (0.068 sec)
MySQL [college]> INSERT INTO products VALUES
-> (NULL, 'PEN', 'Pen Blue', 8000, 1.25),
-> (NULL, 'PEN', 'Pen Black', 2000, 1.25);
Query OK, 2 rows affected (0.102 sec)
Records: 2 Duplicates: 0 Warnings: 0
MySQL [college]> INSERT INTO products (productCode, name,
quantity, price) VALUES
-> ('PEC', 'Pencil 2B', 10000, 0.48),
-> ('PEC', 'Pencil 2H', 8000, 0.49);
31

Query OK, 2 rows affected (0.053 sec)


Records: 2 Duplicates: 0 Warnings: 0
MySQL [college]> INSERT INTO products (productCode, name) VALUES
('PEC', 'Pencil HB');
Query OK, 1 row affected (0.038 sec)
MySQL [college]> INSERT INTO products values (NULL, NULL, NULL,
NULL, NULL);
ERROR 1048 (23000): Column 'productCode' cannot be null
MySQL [college]> SELECT * FROM products;
+ + + + + +
| productID | productCode | name | quantity | price |
+ + + + + +
| 1001 | PEN | Pen Red | 5000 | 1.23 |
| 1002 | PEN | Pen Blue | 8000 | 1.25 |
| 1003 | PEN | Pen Black | 2000 | 1.25 |
| 1004 | PEC | Pencil 2B | 10000 | 0.48 |
| 1005 | PEC | Pencil 2H | 8000 | 0.49 |
| 1006 | PEC | Pencil HB | 0 | 99999.99 |
+ + + + + +
6 rows in set (0.027 sec)
COUNT(*)
SELECT COUNT(*) AS `Count` FROM products;
MySQL [college]> SELECT COUNT(*) AS `Count` FROM products;
+ +
| Count |
+ +
| 6|
+ +
32

1 row in set (0.044 sec)


MySQL [college]> SELECT productCode, COUNT(*) FROM products
GROUP BY productCode;
+ + +
| productCode | COUNT(*) |
+ + +
| PEC | 3|
| PEN | 3|
+ + +
2 rows in set (0.103 sec)
MySQL [college]> SELECT productCode, COUNT(*) AS count
-> FROM products
-> GROUP BY productCode
-> ORDER BY count DESC;
+ + +
| productCode | count |
+ + +
| PEN | 3|
| PEC | 3|
+ + +
2 rows in set (0.001 sec)
33

AVG()

The AVG() function returns the average value of a numeric column.

AVG() Syntax
SELECT AVG(column_name)
FROM table_name
WHERE condition;
SUM()

The SUM() function returns the total sum of a numeric column.

SUM() Syntax
SELECT SUM(column_name)
FROM table_name
WHERE condition;
MIN()

The MIN() function returns the smallest value of the selected column.

MIN() Syntax
SELECT MIN(column_name)
FROM table_name
WHERE condition;
MAX()

The MAX() function returns the largest value of the selected column.

MAX() Syntax
SELECT MAX(column_name)
FROM table_name
WHERE condition;
Example:
SELECT MAX(price), MIN(price), AVG(price), STD(price),
SUM(quantity) FROM products;
34

MySQL [college]> SELECT MAX(price), MIN(price), AVG(price),


STD(price), SUM(quantity)
-> FROM products;
+ + + + + +
| MAX(price) | MIN(price) | AVG(price) | STD(price) | SUM(quantity)
|
+ + + + + +
| 99999.99 | 0.48 | 16667.448333 | 37267.445582 | 33000 |
+ + + + + +
1 row in set (0.060 sec)

MySQL [college]> SELECT productCode, MAX(price) AS `Highest Price`,


MIN(price) AS `Lowest Price`
-> FROM products
-> GROUP BY productCode;
+ + + +
| productCode | Highest Price | Lowest Price |
+ + + +
| PEC | 99999.99 | 0.48 |
| PEN | 1.25 | 1.23 |
+ + + +
35

2 rows in set (0.001 sec)


MySQL [college]> SELECT productCode, MAX(price), MIN(price),
-> CAST(AVG(price) AS DECIMAL(7,2)) AS `Average`,
-> CAST(STD(price) AS DECIMAL(7,2)) AS `Std Dev`,
-> SUM(quantity)
-> FROM products
-> GROUP BY productCode;
+ + + + + + +
| productCode | MAX(price) | MIN(price) | Average | Std Dev |
SUM(quantity) |
+ + + + + + +
| PEC | 99999.99 | 0.48 | 33333.65 | 47140.22 | 18000 |
| PEN | 1.25 | 1.23 | 1.24 | 0.01 | 15000 |
+ + + + + + +
2 rows in set (0.023 sec)

MySQL [college]> SELECT


-> productCode AS `Product Code`,
-> COUNT(*) AS `Count`,
36

-> CAST(AVG(price) AS DECIMAL(7,2)) AS `Average`


-> FROM products
-> GROUP BY productCode
-> HAVING Count >=3;
+ + + +
| Product Code | Count | Average |
+ + + +
| PEC | 3 | 33333.65 |
| PEN | 3| 1.24 |
+ + + +
2 rows in set (0.002 sec)

MySQL [college]> SELECT


-> productCode,
-> MAX(price),
-> MIN(price),
-> CAST(AVG(price) AS DECIMAL(7,2)) AS `Average`,
-> SUM(quantity)
-> FROM products
-> GROUP BY productCode
-> WITH ROLLUP;
+ + + + + +
| productCode | MAX(price) | MIN(price) | Average | SUM(quantity) |
37

+ + + + + +
| PEC | 99999.99 | 0.48 | 33333.65 | 18000 |
| PEN | 1.25 | 1.23 | 1.24 | 15000 |
| NULL | 99999.99 | 0.48 | 16667.45 | 33000 |
+ + + + + +
3 rows in set (0.025 sec)

Result:

Thus the Aggregate Functions commands were implemented.


38

EX NO:2c Practicing Set Operations


Date:
AIM:
To practice the Set Operations commands .

Set Operations
UNION
UNION ALL
GROUP BY
HAVING
The SQL UNION Operator

The UNION operator is used to combine the result-set of two or


more SELECT statements.

• Every SELECT statement within UNION must have the same


number of columns
• The columns must also have similar data types
• The columns in every SELECT statement must also be in the same
order

UNION Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
Table Creation:
MySQL [college]> CREATE TABLE EmployeeUK
-> (
-> EmployeeId INT PRIMARY KEY,
-> FirstName VARCHAR(50),
-> LastName VARCHAR(50),
-> Gender VARCHAR(10),
-> Department VARCHAR(20)
-> );
39

Query OK, 0 rows affected (0.460 sec)

MySQL [college]> INSERT INTO EmployeeUK VALUES(1, 'Pranaya',


'Rout', 'Male','IT');
Query OK, 1 row affected (0.095 sec)
MySQL [college]> INSERT INTO EmployeeUK VALUES(2, 'Priyanka',
'Dewangan', 'Female','IT');
Query OK, 1 row affected (0.130 sec)
MySQL [college]> INSERT INTO EmployeeUK VALUES(3, 'Preety',
'Tiwary', 'Female','HR');
Query OK, 1 row affected (0.142 sec)
MySQL [college]> INSERT INTO EmployeeUK VALUES(4, 'Subrat',
'Sahoo', 'Male','HR');
Query OK, 1 row affected (0.223 sec)
MySQL [college]> INSERT INTO EmployeeUK VALUES(5, 'Anurag',
'Mohanty', 'Male','IT');
Query OK, 1 row affected (0.374 sec)
MySQL [college]> INSERT INTO EmployeeUK VALUES(6, 'Rajesh',
'Pradhan', 'Male','HR');
Query OK, 1 row affected (0.154 sec)
MySQL [college]> INSERT INTO EmployeeUK VALUES(7, 'Hina',
'Sharma', 'Female','IT');
Query OK, 1 row affected (0.151 sec)
40

MySQL [college]> CREATE TABLE EmployeeUSA


-> (
-> EmployeeId INT PRIMARY KEY,
-> FirstName VARCHAR(50),
-> LastName VARCHAR(50),
-> Gender VARCHAR(10),
-> Department VARCHAR(20)
-> );
Query OK, 0 rows affected (0.170 sec)
MySQL [college]>
MySQL [college]> INSERT INTO EmployeeUSA VALUES(1, 'James',
'Pattrick', 'Male','IT');
Query OK, 1 row affected (0.096 sec)
MySQL [college]> INSERT INTO EmployeeUSA VALUES(2,
'Priyanka', 'Dewangan', 'Female','IT');
Query OK, 1 row affected (0.160 sec)
MySQL [college]> INSERT INTO EmployeeUSA VALUES(3, 'Sara',
'Taylor', 'Female','HR');
Query OK, 1 row affected (0.170 sec)
41

MySQL [college]> INSERT INTO EmployeeUSA VALUES(4, 'Subrat',


'Sahoo', 'Male','HR');
Query OK, 1 row affected (0.153 sec)
MySQL [college]> INSERT INTO EmployeeUSA VALUES(5,
'Sushanta', 'Jena', 'Male','HR');
Query OK, 1 row affected (0.297 sec)
MySQL [college]> INSERT INTO EmployeeUSA VALUES(6, 'Mahesh',
'Sindhey', 'Female','HR');
Query OK, 1 row affected (0.054 sec)
MySQL [college]> INSERT INTO EmployeeUSA VALUES(7, 'Hina',
'Sharma', 'Female','IT');
Query OK, 1 row affected (0.083 sec)
MySQL [college]> desc EmployeeUK;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| EmployeeId | int(11) | NO | PRI | NULL | |
| FirstName | varchar(50) | YES | | NULL | |
| LastName | varchar(50) | YES | | NULL | |
| Gender | varchar(10) | YES | | NULL | |
| Department | varchar(20) | YES | | NULL | |
+ + + + + + +
5 rows in set (0.101 sec)

MySQL [college]> desc EmployeeUSA;


+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
42

| EmployeeId | int(11) | NO | PRI | NULL | |


| FirstName | varchar(50) | YES | | NULL | |
| LastName | varchar(50) | YES | | NULL | |
| Gender | varchar(10) | YES | | NULL | |
| Department | varchar(20) | YES | | NULL | |
+ + + + + + +
5 rows in set (1.007 sec)

UNION()
SELECT FirstName, LastName, Gender, Department FROM
EmployeeUK
-> UNION
-> SELECT FirstName, LastName, Gender, Department FROM
EmployeeUSA;
MySQL [college]> SELECT FirstName, LastName, Gender,
Department FROM EmployeeUK
-> UNION
-> SELECT FirstName, LastName, Gender, Department FROM
EmployeeUSA;
+ + + + +
43

| FirstName | LastName | Gender | Department |


+ + + + +
| Pranaya | Rout | Male | IT |
| Priyanka | Dewangan | Female | IT |
| Preety | Tiwary | Female | HR |
| Subrat | Sahoo | Male | HR |
| Anurag | Mohanty | Male | IT |
| Rajesh | Pradhan | Male | HR |
| Hina | Sharma | Female | IT |
| James | Pattrick | Male | IT |
| Sara | Taylor | Female | HR |
| Sushanta | Jena | Male | HR |
| Mahesh | Sindhey | Female | HR |
+ + + + +
11 rows in set (0.023 sec)
44

UNION ALL()
SELECT FirstName, LastName, Gender, Department FROM
EmployeeUK
-> UNION ALL
-> SELECT FirstName, LastName, Gender, Department FROM
EmployeeUSA;
MySQL [college]> SELECT FirstName, LastName, Gender, Department
FROM EmployeeUK
-> UNION ALL
-> SELECT FirstName, LastName, Gender, Department FROM
EmployeeUSA;
+ + + + +
| FirstName | LastName | Gender | Department |
+ + + + +
| Pranaya | Rout | Male | IT |
| Priyanka | Dewangan | Female | IT |
| Preety | Tiwary | Female | HR |
| Subrat | Sahoo | Male | HR |
| Anurag | Mohanty | Male | IT |
| Rajesh | Pradhan | Male | HR |
| Hina | Sharma | Female | IT |
| James | Pattrick | Male | IT |
| Priyanka | Dewangan | Female | IT |
| Sara | Taylor | Female | HR |
| Subrat | Sahoo | Male | HR |
| Sushanta | Jena | Male | HR |
| Mahesh | Sindhey | Female | HR |
| Hina | Sharma | Female | IT |
+ + + + +
45

14 rows in set (0.002 sec)

MySQL [college]> SELECT FirstName, LastName, Gender,


Department FROM EmployeeUK
-> UNION
-> SELECT FirstName, LastName, Gender, Department FROM
EmployeeUSA
-> ORDER BY FirstName;
+ + + + +
| FirstName | LastName | Gender | Department |
+ + + + +
| Anurag | Mohanty | Male | IT |
| Hina | Sharma | Female | IT |
| James | Pattrick | Male | IT |
| Mahesh | Sindhey | Female | HR |
| Pranaya | Rout | Male | IT |
| Preety | Tiwary | Female | HR |
| Priyanka | Dewangan | Female | IT |
46

| Rajesh | Pradhan | Male | HR |


| Sara | Taylor | Female | HR |
| Subrat | Sahoo | Male | HR |
| Sushanta | Jena | Male | HR |
+ + + + +
11 rows in set (0.002 sec)

MySQL [college]> SELECT * FROM EmployeeUK


-> WHERE FirstName NOT IN (SELECT FirstName FROM
EmployeeUSA);
+ + + + + +
| EmployeeId | FirstName | LastName | Gender | Department |
+ + + + + +
| 1 | Pranaya | Rout | Male | IT |
| 3 | Preety | Tiwary | Female | HR |
| 5 | Anurag | Mohanty | Male | IT |
| 6 | Rajesh | Pradhan | Male | HR |
+ + + + + +
47

4 rows in set (0.045 sec)

Result:
Thus the Set Operations commands were practiced and implemented.
48

EX NO:2d Practicing Transaction Control Language


Date:
AIM:
To practice the Transaction Control Language commands .

Transaction Control Language

• Commit,
• Rollback and
• Save Points

Commit()
49

Rollback()

Result:
Thus the Transaction Control Language commands were practiced and
implemented.
50

EX NO:3 Distributed Database Design and Implementation


Date:
AIM:
To design and implenet a Distributed Database Design and Implementation .

Steps of Distributed Database Design


There are in general several design alternatives.
Top-down approach:
First the general concepts, the global framework are defined, after then the
details. Down-top approach: first the detail modules are defined, after then
the global framework.
If the system is built up from a scratch, the top-down method is more
accepted. If the system should match to existing systems or some modules are
yet ready, the down-top method is usually used.
General design steps according to the structure:
- analysis of the external, application requirements
- design of the global schema
- design of the fragmentation
- design of the distribution schema
- design of the local schemes
51

- design of the local physical layers


DDBMS -specific design steps:
- design of the fragmentation
- design of the distribution schema During the requirement analysis phase,
also the fragmentation and distribution requirements are considered.

Down-Top Design
Usually existing and heterogeneous databases are integrated into a common
distributed system.
Steps of integration:
- Common data model selection
As the different component databases may have different data models and
the DDBMS should based on a single, common data model, the first step is to
convert the different models into a common model. The common model is
usually an intermediate model, different from the data model of the
components
- Translation of each local schema into the common model
The different schema element descriptions should be converted into this
common model.
- Integration of the local schema into a common global schema
Beside the collection of the component descriptions, the integration should
deal with the matching of the different semantic elements and with the
resolving of the different types of inconsistency.
52

- Design the translation between the global and local schemes


To access all of the components on a homogeneous way, a conversion
procedure should be applied.
Goals of the Fragmentation and Distribution Design
Local processing
It is desirable to perform as much tasks as possible at the local level, i.e.
without any access to the other sites. The local processing provides an easier
management and a more efficient execution.
Although a complete locality is an aim from the performance point of view,
it can not be realized due to the distribution requirements of the system.
Availability and reliability of the DDB
It is desirable to distribute the data over different sites to provide higher
availability. Thus, in the case of site failures a site can replace the other, no
service or functionality will be lost. The replication, distribution is a useful
method to perform a recovery if the data on some site would be destroyed.
Distribution of processing load
It is desirable to distribute the processing power over the different sites to
provide a higher throughput. The different processing steps of a complex task
will be distributed among several sites enabling a parallel processing too.
Storage cost reduction
It may be cost effective if not every site is equipped with the same high
performance and costly elements. It is enough to install only some of such
specialized sites, the others can use it in a shared way. We should find the
trade-off of these requirements
Horizontal Fragmentation
The relation is partitioned horizontally into fragments..
Primary fragmentation: the assignment of the tuple depends on the attribute
values of the tuple
Derived fragmentation: the assignment of the tuple depends not on the
attributes of this tuple, but on the attributes of another tuple(s).
Horizontal Fragmentation Example 1
The sample table to be fragmented:
Employee(Name, Department, Skill, Salary)
The applications requiring access to the Employee table:
Application 1: it will access the employees in the department with Department
id = 1.
Application 2: it will access the programmers independently from their
departments
The relevant simple predicates - for application 1: Department = 1 - for
application 2: Skill = ‘Programmer’
Predication set:
P = { Department = 1, Skill = ‘Programmer’}
The minterm predicates:
53

Department = 1 AND Skill = ‘Programmer’


Department = 1 AND Skill <> ‘Programmer’
Department <> 1 AND Skill = ‘Programmer’
Department <> 1 AND Skill <> ‘Programmer’
A not relevant predicate:
Salary > 250000
Vertical Fragmentation
The assignment of the data elements in a table is based on the attribute
identifier of the data. In this case the different projections are the content of
the fragments.
Fragmentation rule:
Every attribute must belong to at least one fragment, and every fragment must
have a tuple identifier.
Vertical partitioning: every attribute is contained in only one fragment.
Vertical clustering: an attribute may be contained in more than one
fragments.
Vertical fragmentation There are two main heuristics methods to perform the
vertical fragmentation:
- split approach : a global relation schema is recursive split into disjoint
segments
- grouping approach : the single attributes are progressively grouped into
larger segments The main steps in the design of the vertical fragmentation
- analyze the applications (A1,A2,A3,A4)
- analyze the queries (Q1,Q2,Q3,Q4)
- determine the reference-matrix (1: Qi references Aj , 0: not)

Result:
Thus a Distributed Database was designed and Implemented.
54

EX NO:4 Practicing Triggers


Date:

AIM:
To practice the Triggers commands .

Triggers
MySQL Triggers
We assume that you are habituated with "MySQL Stored Procedures", if not
you can read our MySQL Procedures tutorial. You can use the following
statements of MySQL procedure in triggers:

• Compound statements (BEGIN / END)


• Variable declaration (DECLARE) and assignment (SET)
• Flow-of-control statements
(IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE, ITERATE)
• Condition declarations
• Handler declarations
• Syntax:
• CREATE
• [DEFINER = { user | CURRENT_USER }]
• TRIGGER trigger_name
• trigger_time trigger_event
• ON tbl_name FOR EACH ROW
• trigger_body
• trigger_time: { BEFORE | AFTER }
• trigger_event: { INSERT | UPDATE | DELETE }

Example:
MySQL [college]> CREATE TABLE account (acct_num INT, amount
DECIMAL(10,2));
Query OK, 0 rows affected (0.184 sec)
CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;
Sample Examples:
55

MySQL [college]> CREATE TRIGGER ins_sum BEFORE INSERT ON


account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.373 sec)
MySQL [college]> desc account;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| acct_num | int(11) | YES | | NULL | |
| amount | decimal(10,2) | YES | | NULL | |
+ + + + + + +
2 rows in set (0.095 sec)
MySQL [college]> select * from account;
Empty set (0.001 sec)
MySQL [college]> SET @sum = 0;
Query OK, 0 rows affected (0.000 sec)
MySQL [college]> INSERT INTO account
VALUES(137,14.98),(141,1937.50),(97,-100.00);
Query OK, 3 rows affected (0.185 sec)
Records: 3 Duplicates: 0 Warnings: 0
MySQL [college]> SELECT @sum AS 'Total amount inserted';
+ +
| Total amount inserted |
+ +
| 1852.48 |
+ +
1 row in set (0.001 sec)
MySQL [college]> DROP TRIGGER test.ins_sum;
56

ERROR 1360 (HY000): Trigger does not exist


MySQL [college]> CREATE TRIGGER ins_transaction BEFORE INSERT ON
account
-> FOR EACH ROW PRECEDES ins_sum
-> SET
-> @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
-> @withdrawals = @withdrawals + IF(NEW.amount<0,-
NEW.amount,0);
ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to
use near 'ins_sum
SET
@deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),' at line 2
MySQL [college]> CREATE TRIGGER ins_sum BEFORE INSERT ON
account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple
triggers with the same action time and event for one table'
MySQL [college]> delimiter //
MySQL [college]> CREATE TRIGGER upd_check BEFORE UPDATE ON
account
-> FOR EACH ROW
-> BEGIN
-> IF NEW.amount < 0 THEN
-> SET NEW.amount = 0;
-> ELSEIF NEW.amount > 100 THEN
-> SET NEW.amount = 100;
-> END IF;
-> END;//
Query OK, 0 rows affected (0.301 sec)
57

MySQL [college]> delimiter ;


MySQL [college]>

Result:
Thus the Triggers commands were practiced and implemented.
58

EX NO:5 Accessing a Relational Database using PHP


Date:

AIM:
To Access a Relational Database using PHP.

Steps:
The steps required to access data from a table in a database provided by
MYSQL can be summarized as follows:

• Establish or open a connection to the MYSQL server.


• Select a database.
• Execute the query against the database.
• Process the result returned by the server.
• Close the connection

Craete Table in Phpmyadmin :


Step1: Create table as Player

INSERT INTO `players` (`id`, `surname`, `firstname`, `nickname`, `avatar`)


VALUES (NULL, 'pate', 'Andrew', 'arp', NULL), (NULL, 'anim', 'rose', '',
NULL);
59

INSERT INTO `Games` (`id`, `surname`, `firstname`, `nickname`, `avatar`)


VALUES (NULL, 'pate', 'Andrew', 'arp', NULL), (NULL, 'anim', 'rose', '',
NULL);
60

Step 3: Make a relation to the database and table using Phpmyadmin(Please


write the steps in ur word)
61
62

PHP Code:
&lt;?php
$servername&nbsp;=&nbsp;"localhost";
$database =&nbsp;"database";
$username&nbsp;=&nbsp;"username";
$password&nbsp;=&nbsp;"password";
$charset&nbsp;=&nbsp;"utf8mb4";
try {
$dsn&nbsp;=&nbsp;"mysql:host=$servername;dbname=$database;charset=$ch
arset";
$pdo&nbsp;=&nbsp;new&nbsp;PDO($dsn,&nbsp;$username,&nbsp;$passwor
d);
$pdo-&gt;setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
echo “Connection Okay”;
return $pdo
}
catch&nbsp;(PDOException&nbsp;$e)
{
echo “Connection failed: ”. $e-&gt;getMessage();
}
?&gt;

Result:
Thus a Relational Database using PHP was accessed and implemented.
63

EX NO:6 Creating XML Documents, Document Type Definition and


XML Schema
Date:

AIM:
To Create XML Documents, Document Type Definition and XML Schema.
XML DTD

An XML document with correct syntax is called "Well Formed".

An XML document validated against a DTD is both "Well Formed" and


"Valid".

What is a DTD?

DTD stands for Document Type Definition.

A DTD defines the structure and the legal elements and attributes of an
XML document.

Valid XML Documents

A "Valid" XML document is "Well Formed", as well as it conforms to the rules


of a DTD:

<?xml version="1.0" encoding="UTF-8"?>


<!DOCTYPE note SYSTEM "Note.dtd">
<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
</note>
XML DTD

The purpose of a DTD is to define the structure and the legal elements and
attributes of an XML document:

Note.dtd:
<!DOCTYPE note
[
<!ELEMENT note (to,from,heading,body)>
<!ELEMENT to (#PCDATA)>
64

<!ELEMENT from (#PCDATA)>


<!ELEMENT heading (#PCDATA)>
<!ELEMENT body (#PCDATA)>
]>

The DTD above is interpreted like this:

• !DOCTYPE note - Defines that the root element of the document is note
• !ELEMENT note - Defines that the note element must contain the
elements: "to, from, heading, body"
• !ELEMENT to - Defines the to element to be of type "#PCDATA"
• !ELEMENT from - Defines the from element to be of type "#PCDATA"
• !ELEMENT heading - Defines the heading element to be of type
"#PCDATA"
• !ELEMENT body - Defines the body element to be of type "#PCDATA"

Tip: #PCDATA means parseable character data.


Using DTD for Entity Declaration

A DOCTYPE declaration can also be used to define special characters or


strings, used in the document:

Example
<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE note [
<!ENTITY nbsp "&#xA0;">
<!ENTITY writer "Writer: Donald Duck.">
<!ENTITY copyright "Copyright: W3Schools.">
]>

<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
<footer>&writer;&nbsp;&copyright;</footer>
</note>
65

OUTPUT:

XML Schema

An XML Schema describes the structure of an XML document, just like a DTD.

An XML document with correct syntax is called "Well Formed".

An XML document validated against an XML Schema is both "Well Formed"


and "Valid".

XML Schema

XML Schema is an XML-based alternative to DTD:

<xs:element name="note">
<xs:complexType>
<xs:sequence>
<xs:element name="to" type="xs:string"/>
<xs:element name="from" type="xs:string"/>
<xs:element name="heading" type="xs:string"/>
<xs:element name="body" type="xs:string"/>
</xs:sequence>
</xs:complexType>

</xs:element>
66

The Schema above is interpreted like this:

• <xs:element name="note"> defines the element called "note"


• <xs:complexType> the "note" element is a complex type
• <xs:sequence> the complex type is a sequence of elements
• <xs:element name="to" type="xs:string"> the element "to" is of type
string (text)
• <xs:element name="from" type="xs:string"> the element "from" is of
type string
• <xs:element name="heading" type="xs:string"> the element "heading" is
of type string
• <xs:element name="body" type="xs:string"> the element "body" is of
type string

Result:
Thus the Documents, Document Type Definition and XML Schema was created
and implemented.
67

EX NO:7 Using a Relational Database to store the XML documents as text


Date:

AIM:
To Use a Relational Database to store the XML documents as text
XML document
An XML document is a basic unit of XML information composed of
elements and other markup in an orderly package. An XML document can
contains wide variety of data. For example, database of numbers, numbers
representing molecular structure or a mathematical equation.

XML Document Example

A simple document is shown in the following example −


<?xml version = "1.0"?>
<contact-info>
<name>Tanmay Patil</name>
<company>TutorialsPoint</company>
<phone>(011) 123-4567</phone>
</contact-info>
The following image depicts the parts of XML document.
68

Result:

Thus a Relational Database to store the XML documents as text was used
and implemented.
69

EX NO: 8 Using a Relational Database to store the XML documents as


data elements
Date:

AIM:
To Use a Relational Database to store the XML documents as data
elements.
XML Elements

An XML document contains XML Elements.

What is an XML Element?

An XML element is everything from (including) the element's start tag to


(including) the element's end tag.

<price>29.99</price>

An element can contain:

• text
• attributes
• other elements
• or a mix of the above

<bookstore>
<book category="children">
<title>Harry Potter</title>
<author>J K. Rowling</author>
<year>2005</year>
<price>29.99</price>
</book>
<book category="web">
<title>Learning XML</title>
<author>Erik T. Ray</author>
<year>2003</year>
<price>39.95</price>
</book>
</bookstore>

In the example above:

<title>, <author>, <year>, and <price> have text content because they contain
text (like 29.99).
70

<bookstore> and <book> have element contents, because they contain


elements.

<book> has an attribute (category="children").

Empty XML Elements

An element with no content is said to be empty.

In XML, you can indicate an empty element like this:

<element></element>

You can also use a so called self-closing tag:

<element />

Result:

Thus a Relational Database to store the XML documents as data elements


was used and implemented.
71

EX NO:9a Extracting XML Documents from Relational Databases


Date:

AIM:
To Extract XML Documents from Relational Databases.
Extracting XML Documents from Relational Databases
1. Creating Hierarchical XML Views over Flat or Graph-Based Data
XML uses a hierarchical (tree) model to represent documents. The database
systems with the most widespread use follow the flat relational data model. When
we add referential integrity constraints, a relational schema can be considered to
be a graph structure (for example, see Figure 3.7). Similarly, the ER model
represents data using graph-like structures (for example, see Figure 7.2). We saw
in Chapter 9 that there are straightforward mappings between the ER and
relational models, so we can conceptually represent a relational database schema
using the corresponding ER schema. Although we will use the ER model in our
discussion and examples to clarify the conceptual differences between tree and
graph models, the same issues apply to converting relational data to XML.
We will use the simplified UNIVERSITY ER schema shown in Figure 12.8 to
illustrate our discussion. Suppose that an application needs to extract XML
documents for student, course, and grade information from
the UNIVERSITY database. The data needed for these documents is contained
in the database attributes of the entity

XML SCHEMA CODE AS


XML schema document with course as the root.

<xsd:element name=“root”>

<xsd:sequence>

<xsd:element name=“course” minOccurs=“0” maxOccurs=“unbounded”>

<xsd:sequence>

<xsd:element name=“cname” type=“xsd:string” />


72

<xsd:element name=“cnumber” type=“xsd:unsignedInt” />

<xsd:element name=“section” minOccurs=“0” maxOccurs=“unbounded”>

<xsd:sequence>

<xsd:element name=“secnumber” type=“xsd:unsignedInt” />

<xsd:element name=“year” type=“xsd:string” />


<xsd:element name=“quarter” type=“xsd:string” />
<xsd:element name=“student” minOccurs=“0”
maxOccurs=“unbounded”><xsd:sequence>
<xsd:element name=“ssn” type=“xsd:string” /><xsd:element name=“sname”
type=“xsd:string” /><xsd:element name=“class” type=“xsd:string”
/><xsd:element name=“grade” type=“xsd:string” />

</xsd:sequence>

</xsd:element>

</xsd:sequence>

</xsd:element>

</xsd:sequence>

</xsd:element>

</xsd:sequence>

</xsd:element>
73

OUTPUT:

Result:
Thus the XML Documents from Relational Databases was extracted and
implemented.
74

EX NO:9b XML Querying


Date:

AIM:
To USE XML Querying.
XQuery?

XQuery is to XML what SQL is to databases.


XQuery is designed to query XML data.

XQuery Example
for $x in doc("books.xml")/bookstore/book
where $x/price>30
order by $x/title
return $x/title

What is XQuery?

• XQuery is the language for querying XML data


• XQuery for XML is like SQL for databases
• XQuery is built on XPath expressions
• XQuery is supported by all major databases
• XQuery is a W3C Recommendation

XQuery and XPath

XQuery 1.0 and XPath 2.0 share the same data model and support the same
functions and operators. If you have already studied XPath you will have no
problems with understanding XQuery.

XQuery - Examples of Use

XQuery can be used to:

• Extract information to use in a Web Service


• Generate summary reports
75

• Transform XML data to XHTML


• Search Web documents for relevant information

XQuery First Example

Here, the XML document is named as courses.xml and xqy file is named
as courses.xqy

courses.xml

1. <?xml version="1.0" encoding="UTF-8"?>


2. <courses>
3. <course category="JAVA">
4. <title lang="en">Learn Java in 3 Months.</title>
5. <trainer>Sonoo Jaiswal</trainer>
6. <year>2008</year>
7. <fees>10000.00</fees>
8. </course>
9. <course category="Dot Net">
10. <title lang="en">Learn Dot Net in 3 Months.</title>
11. <trainer>Vicky Kaushal</trainer>
12. <year>2008</year>
13. <fees>10000.00</fees>
14. </course>
15. <course category="C">
16. <title lang="en">Learn C in 2 Months.</title>
17. <trainer>Ramesh Kumar</trainer>
18. <year>2014</year>
19. <fees>3000.00</fees>
20. </course>
21. <course category="XML">
22. <title lang="en">Learn XML in 2 Months.</title>
23. <trainer>Ajeet Kumar</trainer>
24. <year>2015</year>
25. <fees>4000.00</fees>
26. </course>
27.</courses>

courses.xqy
76

1. for $x in doc("courses.xml")/courses/course
2. where $x/fees>5000
3. return $x/title

This example will display the title elements of the courses whose fees are greater
than 5000.

XQueryTester.java

1. import java.io.File;
2. import java.io.FileInputStream;
3. import java.io.FileNotFoundException;
4. import java.io.InputStream;
5.
6. import javax.xml.xquery.XQConnection;
7. import javax.xml.xquery.XQDataSource;
8. import javax.xml.xquery.XQException;
9. import javax.xml.xquery.XQPreparedExpression;
10.import javax.xml.xquery.XQResultSequence;
11.
12.import com.saxonica.xqj.SaxonXQDataSource;
13.
14. public class XQueryTester {
15. public static void main(String[] args){
16. try {
17. execute();
18. }
19.
20. catch (FileNotFoundException e) {
21. e.printStackTrace();
22. }
23.
24. catch (XQException e) {
25. e.printStackTrace();
26. }
27. }
28.
29. private static void execute() throws FileNotFoundException, XQException{
77

30. InputStream inputStream = new FileInputStream(new File("courses.xqy"));


31. XQDataSource ds = new SaxonXQDataSource();
32. XQConnection conn = ds.getConnection();
33. XQPreparedExpression exp = conn.prepareExpression(inputStream);
34. XQResultSequence result = exp.executeQuery();
35. while (result.next()) {
36. System.out.println(result.getItemAsString(null));
37. }
38. }
39.}

Execute XQuery against XML

Put the above three files to a same location. We put them on desktop in a
folder name XQuery2. Compile XQueryTester.java using console. You must
have JDK 1.5 or later installed on your computer and classpaths are
configured.

Compile:

javac XQueryTester.java

Execute:

java XQueryTester

Output:

Result:
Thus the XML Querying were used and implemented.
78

EX NO:10 Creating Databases using MongoDB, DynamoDB, Voldemort


Key-Value Distributed Data Store Hbase and Neo4j.
Date:

AIM:
To Creating Databases using MongoDB, DynamoDB, Voldemort Key Value
Distributed Data Store Hbase and Neo4j

The use Command

MongoDB use DATABASE_NAME is used to create database. The command


will create a new database if it doesn't exist, otherwise it will return the existing
database.
Syntax
Basic syntax of use DATABASE statement is as follows −
use DATABASE_NAME

Example
If you want to use a database with name <mydb>, then use
DATABASE statement would be as follows −
>use mydb
switched to db mydb
To check your currently selected database, use the command db
>db
Mydb
79

If you want to check your databases list, use the command show dbs.
>show dbs
local 0.78125GB
test 0.23012GB
80

Your created database (mydb) is not present in list. To display database, you need
to insert at least one document into it.
>db.movie.insert({"name":"tutorials point"})
>show dbs
local0.78125GB
mydb 0.23012GB
test 0.23012GB
In MongoDB default database is test. If you didn't create any database, then
collections will be stored in test database.

The createCollection() Method

MongoDB db.createCollection(name, options) is used to create collection.


Syntax
Basic syntax of createCollection() command is as follows −
db.createCollection(name, options)

Result:
Thus Databases using MongoDB, DynamoDB, Voldemort Key Value
Distributed Data Store Hbase and Neo4j were created and implemented.

You might also like