A PRACTICAL REPORT ON
“Introduction to ArcGIS”
M.Sc. GEOINFORMACTICS
NAME: PAYAL HAZRA
ID: 23GI04
SEMESTER: 1
YEAR: 2023-2024
Guided by:
Akangsha Varma
Asst.professor, Geoinformatics Department -ISTAR
The CVM Universtiy
V.V. Nagar-388120, Gujarat, India
INSTITUTE OF SCIENCE AND TECHNOLOGY FOR
ADVANCED STUDIES & RESEARCH
(ISTAR)
1
CERTIFICATE
This is to certify that the project work embodied in this report entitled "JOURNAL ON
RDMS" was carried out by Miss. Payal Hazra (23GI04) at the Geoinformatics
department, Institute of Science and Technology for Advanced Studies and Research
(ISTAR)- CVM University, Vallabh Vidyanagar fulfilment of the requirement for the
award of M.Sc. (Geoinformatics) for the Project Work (201400107). This project work has
been carried out under my guidance and it is up to my satisfaction.
Akangsha Varma Dr. M. M. Raj
Project Guide, I/C Principal,
Asst.professor, ISTAR-CVM University
ISTAR-CVM University
Date of Submission: 25/10/2023
Signature:
Akangsha Varma
Asst.professor
M.Sc. Geoinformatics
ISTAR, CVM University
2
ACKNOWLEDGEMENT
My Sincere efforts have made me to accomplish the task of completing this project. I have
taken effort in this project. However it would not have been possible without the kind support
and help of many individuals.
I would like to express my gratitude to my Principal Dr. M .M. Raj , I/C Principal, ISTAR
CVM University and the college for providing me with facilities required to do the project.
I am highly indebted to Akangsha Varma, Asst.professor, Geoinformatics Department-
ISTAR for his valuable guidance which has promoted my efforts in all stages of this project
work. My thanks and appreciation goes to my classmates who has helped me in any stages of
completing this project without their help it would not been possible.
Finally, words are not sufficient to express gratitude my cherished family members for
supporting me without their encouragement and support I would have not reached this stage.
Thanking you
Payal Hazra
3
LIST OF CONTENT
1 . Introduction to RDMS............................................................................................................................6
2. MY SQL....................................................................................................................................................8
3. KEY ELEMENT......................................................................................................................................9
4. SQL words and phrases:........................................................................................................................11
5. RDMS PRACTICAL.............................................................................................................................13
6. My SQL QUERRY.................................................................................................................................19
4
List of Figure
Figure 1: Showing MySQL app..................................................................................................................13
Figure 2 : Showing first page that appears after opening of the SQL app...................................................13
Figure 3 : Showing the password page........................................................................................................14
Figure 4 : Showing to create the schemas...................................................................................................14
Figure 5 : showing creation of new schemas................................................................................................15
Figure 6 : Selecting from table bar..............................................................................................................15
Figure 7 : selection of table.........................................................................................................................15
Figure 8: steps for creation of table.............................................................................................................16
Figure 9 : Entering the headings and text type............................................................................................16
Figure 10 : one of the steps for creating table.............................................................................................17
Figure 11 : last step for creating table.........................................................................................................17
Figure 12 : Showing Table..........................................................................................................................18
Figure 13 : Showing and confirming that your table will be saved , after apply and finish........................18
Figure 14 : Showing Querry of 1.................................................................................................................19
Figure 15 : Showing Query of 4..................................................................................................................21
Figure 16: Showing query of 5....................................................................................................................21
Figure 17: Showing Querry of 6..................................................................................................................22
Figure 18 : Showing Querry........................................................................................................................22
Figure 19: Showing Querry of 9..................................................................................................................23
Figure 20 : Showing Querry of 10...............................................................................................................24
Figure 21: Showing Querry of 13..............................................................................................................25
5
1 . Introduction to RDMS
A Relational Database Management System (RDMS) is a type of database management
system that organizes and stores data in a structured format using tables and relationships
between them. Key features of RDMS includes:
TABLES: Data is organized into tables, each containing rows and columns.
Each table represent special; entity or data type.
DATA INTEGRITY: RDMS enforces data integrity through constraints, ensuring
that data is accurate and consistent.
RELATIOSHIPS: RDMS allows you to establish relationships between tables,
typically through keys (primary keys and foreign keys), enabling the retrieval of
related data.
SQL : Structured Query Language (SQL) is used to interact with an RDMS. It
provides a standardized way to perform operations like data retrieval,
insertion, updating, and deletion.
Popular examples of RDBMS systems include Oracle Database, Microsoft SQL Server,
MySQL, PostgreSQL, and SQLite. RDBMS is commonly used for a wide range of
applications, from business data management to GIS, where it helps in the storage and
retrieval of structured data efficiently.
RDMS IN GIS:
A Relational Database Management System (RDBMS) in Geographic Information Systems
(GIS) is used to store and manage spatial and attribute data in a structured and relational
manner. It allows you to organize, query, and analyze geospatial data efficiently.
Some common RDBMS used in GIS applications include:
PostgreSQL with Post GIS: PostgreSQL is an open-source RDBMS, and PostGIS is an
extension that adds spatial capabilities, making it a powerful choice for GIS applications.
Oracle Spatial: Oracle offers a spatial extension that allows for the storage and analysis of
spatial data within the Oracle database.
Microsoft SQL Server with Spatial: Microsoft SQL Server includes spatial data types and
functions, making it suitable for GIS applications.
MySQL with Spatial Extensions: MySQL also provides spatial extensions for handling
geospatial data, although it may be less feature-rich compared to PostgreSQL and SQL
Server.
6
These RDBMS systems allow us for efficient storage, querying, and analysis of geographic
data, making them integral components of GIS workflows.
7
2. MY SQL
SQL (Structured Query Language) is a powerful and standardized programming language
used for managing and manipulating relational databases. It is essential in various data-
related tasks, including data retrieval, data insertion, data updating, and data deletion.
MySQL is a popular open-source Relational Database Management System (RDBMS)
commonly used in Geographic Information Systems (GIS) for storing and managing spatial
and attribute data. When integrating MySQL into a GIS workflow, you can take advantage of
its spatial extensions to work with geospatial data. Here's how MySQL can be used in GIS:
1. Spatial Data Types: MySQL has spatial data types, such as Point, Line String,
Polygon, and Geometry, which allow you to store and manage geographic data efficiently.
2. Spatial Functions: MySQL provides a set of spatial functions to perform various spatial
operations, including distance calculations, spatial joins, and geometric operations on
spatial data.
3. Indexing: You can create spatial indexes to optimize the performance of spatial
queries and retrievals in GIS applications.
4. Compatibility: MySQL can be used with various GIS software and libraries, making
it versatile for geospatial data management.
5. Open Source: MySQL is open source and cost-effective, which can be advantageous
for small to medium-scale GIS projects.
8
3. KEY ELEMENT
SQL (Structured Query Language) consists of several key elements that make up its syntax
and structure:
1. Clauses: SQL queries are composed of various clauses, each serving a specific
purpose. Common clauses include:
- SELECT: Specifies which columns to retrieve.
- FROM: Specifies the table(s) from which to retrieve data.
- WHERE: Filters rows based on specified conditions.
- GROUP BY: Groups rows for aggregation.
- HAVING: Filters grouped data.
- ORDER BY: Sorts the result set.
- JOIN: Combines data from multiple tables.
2. Statements: SQL consists of different types of statements:
- SELECT statement for querying data.
- INSERT, UPDATE, and DELETE statements for data manipulation.
- CREATE, ALTER, and DROP statements for database and table management.
- GRANT and REVOKE statements for managing permissions.
- COMMIT and ROLLBACK statements for managing transactions.
3. Expressions: Expressions are used to perform calculations, transformations, and
comparisons within SQL queries. Examples include arithmetic expressions, string
functions, and conditional expressions.
4. Operators: SQL supports various operators, including arithmetic operators (+, -, *, /),
comparison operators (=, <>, >, <, >=, <=), logical operators (AND, OR, NOT), and
concatenation operator (|| or + for string concatenation).
5. Functions: SQL provides a range of built-in functions for data manipulation and
analysis. Common functions include COUNT, SUM, AVG, MAX, MIN for aggregation,
and string functions like CONCAT, SUBSTRING, and date functions like DATEADD,
DATEDIFF.
6. Constraints: Constraints are rules applied to columns to maintain data integrity. Common
constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and
CHECK.
7. Indexes: Indexes are data structures used to optimize data retrieval. They improve
query performance by creating a quick lookup structure for columns.
8. Views: Views are virtual tables created from one or more tables, which simplify
complex queries and provide an abstracted layer for data retrieval.
9. Transactions: Transactions are sequences of SQL statements that are executed as a
single unit. They follow the ACID properties (Atomicity, Consistency, Isolation, Durability)
to ensure data integrity.
9
10. Joins: Joins are used to combine data from multiple tables based on related
columns. Common types include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL
JOIN.
These elements form the foundation of SQL, allows us to interact with and manipulate data
within relational databases effectively.
1
4. SQL words and phrases:
Here are some commonly used SQL words and phrases:
1. SELECT: Used to retrieve data from a database.
2. FROM: Specifies the table or tables from which data is retrieved.
3. WHERE: Filters data based on specified conditions.
4. INSERT: Adds new data into a database.
5. UPDATE: Modifies existing data in a database.
6. DELETE: Removes data from a database.
7. CREATE: Used to create a new database, table, or other database objects.
8. ALTER: Modifies the structure of an existing database object, such as a table.
9. DROP: Deletes a database object, such as a table, index, or database itself.
10. JOIN: Combines data from multiple tables based on a related column.
11. GROUP BY: Groups rows based on specified columns for aggregation.
12. HAVING: Filters grouped data based on conditions after using GROUP BY.
13. ORDER BY: Sorts the result set in ascending or descending order.
14. COUNT: Calculates the number of rows in a result set or a specific column.
15. SUM: Adds up the values in a numeric column.
16. AVG: Calculates the average value of a numeric column.
17. MAX: Retrieves the maximum value in a column.
18. MIN: Retrieves the minimum value in a column.
19. PRIMARY KEY: Ensures unique values and identifies each row uniquely in a table.
20. FOREIGN KEY: Establishes a relationship between tables based on a column in
one table that refers to the primary key in another table.
21. INDEX: Improves query performance by creating a data structure for faster data retrieval.
22. UNIQUE: Ensures that values in a column are unique.
1
23. NULL: Represents missing or unknown data in a column.
24. INNER JOIN: Retrieves records that have matching values in both tables.
25. LEFT JOIN (or LEFT OUTER JOIN): Retrieves all records from the left table
and matching records from the right table.
26. RIGHT JOIN (or RIGHT OUTER JOIN): Retrieves all records from the right table
and matching records from the left table.
27. FULL JOIN (or FULL OUTER JOIN): Retrieves all records when there is a match
in either the left or right table.
These SQL words and phrases are fundamental to working with relational databases and
performing various data operations.
1
5. RDMS PRACTICAL
In order to perform RDMS, we will be using MySQL which provides spatial
extensions for handling geospatial data.
STEP 1 : At first we will download MySQL workbench .
Figure 1: Showing MySQL app.
STEP 2 : We will open the app .
Figure 2 : Showing first page that appears after opening of the SQL app
STEP 3 : Then we have to click on the gry box in the left side and then enter
the password that we kept while downloading the app.
1
Figure 3 : Showing the password page.
STEP 4 : At first we have to create schema. For creating the schema we need to go to
toolbar and find create a new schema in connecting server, and click on it and than give a
name to your schema and click on apply and now our, schema is created.
Figure 4 : Showing to create the schemas.
STEP 5 : Next step is to create table and input data as per the requirement.
1
Figure 5 : showing creation of new schemas.
STEP 6 : Next step is to create table. We need to click on table from the tool
bar .
Figure 6 : Selecting from table bar.
Figure 7 : selection of table.
1
STEP 7 : then as per the requirement we will fill the heading for the table.
Figure 8: steps for creation of table.
Figure 9 : Entering the headings and text type .
1
Figure 10 : one of the steps for creating table.
STEP 8: Then we have click on apply.
Figure 11 : last step for creating table.
1
STEP 9: As per the requirement we will we fill and create tables.
Figure 12 : Showing Table.
Figure 13 : Showing and confirming that your table will be saved , after apply and finish.
1
6. My SQL QUERRY
If we want to solve or have any question or find anything from the table we
have to write a query about it and run it, it will show the thing we want.
Here are some of the examples of SQL Query:
1) Display all item prices rounded to the nearest rupee.
Figure 14 : Showing Querry of 1.
1
2) The payment is due in two months from the invoice date. Find the payment due
date.
3) Display invoice dates in ‘SEPTEMBER 05,2000’ format.
2
4) Find customers who are in not from MP or MH states.
Figure 15 : Showing Query of 4.
5) Find invoices in which three or more items are ordered.
Figure 16: Showing query of 5.
2
6) Find the total price amount of each invoice.
Figure 17: Showing Querry of 6.
7) Find items that are cheaper than NUT.
Figure 18 : Showing Querry.
2
8) Display Invoice, Customer Names, Items Names and Quantities together.
9) Display the name of the students who are staying in ‘Square’ Hostel and studying
in MSc (GIS).
Figure 19: Showing Querry of 9.
2
10) Display the name of the hostel which has the highest capacity.
Figure 20 : Showing Querry of 10 .
11) Count and display the number of students for each course along with
course name.
2
12) Display the course name in which ‘Aryan’ study.
13) List the details of students whose name have ‘A’ as one of the character.
Figure 21: Showing Querry of 13.
2
2
