CP4152 DP Lab Manual
CP4152 DP Lab Manual
Register Number:
4. Practicing Triggers
INTRODUCTION
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.
Step15
Configuration file created. Windows service MySQL5 installed. Press
finish to close the wizard.
9
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.
UPDATE Syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Example:
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
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
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
+ + +
| id | desc |
+ + +
| 2 | ID 2 in t2 |
| 3 | ID 3 in t2 |
| 4 | ID 4 in t2 |
+ + +
3 rows in set (0.001 sec)
+ + + + +
| 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)
| 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
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
AVG()
AVG() Syntax
SELECT AVG(column_name)
FROM table_name
WHERE condition;
SUM()
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
+ + + + + +
| 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:
Set Operations
UNION
UNION ALL
GROUP BY
HAVING
The SQL UNION Operator
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
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
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
Result:
Thus the Set Operations commands were practiced and implemented.
48
• Commit,
• Rollback and
• Save Points
Commit()
49
Rollback()
Result:
Thus the Transaction Control Language commands were practiced and
implemented.
50
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
Result:
Thus a Distributed Database was designed and Implemented.
54
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:
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
Result:
Thus the Triggers commands were practiced and implemented.
58
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:
PHP Code:
<?php
$servername = "localhost";
$database = "database";
$username = "username";
$password = "password";
$charset = "utf8mb4";
try {
$dsn = "mysql:host=$servername;dbname=$database;charset=$ch
arset";
$pdo = new PDO($dsn, $username, $passwor
d);
$pdo->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
echo “Connection Okay”;
return $pdo
}
catch (PDOException $e)
{
echo “Connection failed: ”. $e->getMessage();
}
?>
Result:
Thus a Relational Database using PHP was accessed and implemented.
63
AIM:
To Create XML Documents, Document Type Definition and XML Schema.
XML DTD
What is a DTD?
A DTD defines the structure and the legal elements and attributes of an
XML document.
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
• !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"
Example
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE note [
<!ENTITY nbsp " ">
<!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; ©right;</footer>
</note>
65
OUTPUT:
XML Schema
An XML Schema describes the structure of an XML document, just like a DTD.
XML Schema
<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
Result:
Thus the Documents, Document Type Definition and XML Schema was created
and implemented.
67
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.
Result:
Thus a Relational Database to store the XML documents as text was used
and implemented.
69
AIM:
To Use a Relational Database to store the XML documents as data
elements.
XML Elements
<price>29.99</price>
• 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>
<title>, <author>, <year>, and <price> have text content because they contain
text (like 29.99).
70
<element></element>
<element />
Result:
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
<xsd:element name=“root”>
<xsd:sequence>
<xsd:sequence>
<xsd:sequence>
</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
AIM:
To USE XML Querying.
XQuery?
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 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.
Here, the XML document is named as courses.xml and xqy file is named
as courses.xqy
courses.xml
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
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
AIM:
To Creating Databases using MongoDB, DynamoDB, Voldemort Key Value
Distributed Data Store Hbase and Neo4j
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.
Result:
Thus Databases using MongoDB, DynamoDB, Voldemort Key Value
Distributed Data Store Hbase and Neo4j were created and implemented.