Transcript Document

Storing and Retrieving Data
The Database
Dr. Miguel A. Labrador
Department of Computer Science & Engineering
[email protected]
http://www.csee.usf.edu/~labrador
1
Outline
• Databases and database management systems
• Relational databases
– Data Definition Language
– Data Manipulation Language
– Structured Query Language (SQL)
• Examples using the tracking application example
• The Postgres Database
2
Copyright© Dr. Miguel A. Labrador
2
The Database and DBMS
• One of the key elements of any information system is the data
• Database is an integrated collection of related records
• A Database Management System (DBMS) is a set of
applications and data that allows the definition, implementation,
access, and maintenance of the information in a database
– How the data is structured, stored, accessed, maintained
• A DBMS usually consists of the following list of subsystems:
– The database: files and data structures that contain the data
• Internal data structure and physical data representation of the
information in the database
– The model: definition of the structure and nature of the data to be
stored in the system
• Networks, relational, object-oriented
3
Copyright© Dr. Miguel A. Labrador
3
The Database and DBMS
• A DBMS usually consists of the following list of subsystems:
– The database engine: all the programs that have direct access to
manipulate the database
• Executes all operations requested by the user
• Maintains the physical structure in optimal conditions
• Integrity of the data and rapid access to it
– The data definition and manipulation language: programming
language that provides the communication channel between the
user and the database engine to implement the database model
and have access to the stored information
– The data administration system: group of applications that allow
the database manager to control the accessibility, security, backup,
and all administrative operations
– The user application program: interface that facilitates all the
operations that the user can perform via ``wizards'', menus,
automated tasks, visual representation of the information or models
4
Copyright© Dr. Miguel A. Labrador
4
Relational Databases
• Most commonly used databases
• Related data is grouped and data in different groups can be
related based on shared common parameters
• Main elements of a relational database
– Table
• Structure that stores the data
• More than one domain, which is related to a specific piece of the data
– A user has several domains (name, address, etc)
– Column
• Each column is a table corresponds to a domain
– Row or Register
• Holds an entire instance of information
5
Copyright© Dr. Miguel A. Labrador
5
Relational Databases
• Main elements of a relational database
– Keys
• Primary key is the minimal set of columns that allow the unique
identification of each row
– Usually a unique ID for each row (SSN, ID)
• Foreign key is the set of columns that contains the primary key of an
element in another table
– Used to make cross-references to elements in other tables
– Avoids the repetition of data
– View or Query
• Subset of data from a table requested by a user
• May involve one or more tables
6
Copyright© Dr. Miguel A. Labrador
6
A Tracking Application Example
• The design of a database depends on the requirements of the
information system to be implemented
• Here are the requirements for a system that stores geographical
tracking information from a group of users
– There is a group of users that will be geographically tracked via GPSenabled mobile devices
– Each user corresponds to a single individual. Each mobile device is
also unique
– The system must maintain basic information about the users: name,
address, date of birth, social security number, and id number
– The system must maintain basic information about the devices: serial
number, model, manufacturer, and year
– The devices are not used exclusively by a single user
– The period of time in which a user is being tracked is not predefined or
slotted, and its duration is variable
– The tracking information must include the different positions and the
time of occurrences associated to a user and the device used
7
Copyright© Dr. Miguel A. Labrador
7
Tracking Application Database
8
Copyright© Dr. Miguel A. Labrador
8
Structure Query Language (SQL)
• Language to work with relational databases
• Three sub-languages
– SQL
• To define queries
– Data Manipulation Language (DML)
• Additions, modification, and deletion of data in the tables
– Data Definition Language (DDL)
• Creation, modification, and deletion of tables
9
Copyright© Dr. Miguel A. Labrador
9
Data Definition Language (DDL)
• Creating the users’
table
– User ID as primary
key
– Automatically
generated by
Postgres
• Serial or sequence
statement
– Foreign key,
constraints and data
types
CREATE TABLE users(
id INTEGER,
ssn INTEGER,
first_name VARCHAR(20)
CONSTRAINT first_name_not_null NOT NULL,
mid_name VARCHAR(20),
last_name VARCHAR(20)
CONSTRAINT last_name_not_null NOT NULL,
birth_date DATE,
address VARCHAR(50)
CONSTRAINT address_not_null NOT NULL,
city VARCHAR(20) DEFAULT 'TAMPA'
CONSTRAINT city_not_null NOT NULL,
zipcode bigint DEFAULT 33620
CONSTRAINT zip_not_null NOT NULL,
state VARCHAR(2) DEFAULT 'FL'
CONSTRAINT state_not_null NOT NULL
CONSTRAINT state_check
CHECK (state IN ('FL', 'GA', 'AL')),
CONSTRAINT users_pk
PRIMARY KEY (id),
CONSTRAINT user_city_fk
FOREIGN KEY (city, zipcode, state)
REFERENCES cities_table (city, zipcode, state)
ON UPDATE CASCADE ON DELETE SET DEFAULT
)TABLESPACE pg_default;
10
Copyright© Dr. Miguel A. Labrador
10
Changing Tables
• Add and delete column
ALTER TABLE users
ADD suffix VARCHAR(5);
ALTER TABLE users
DROP COLUMN mid_name;
• Change data type
ALTER TABLE users
ALTER last_name TYPE VARCHAR(30);
• Add and delete constraint
ALTER TABLE users
ADD CONSTRAINT date_constraint
CHECK (birth_date > '1950-06-30');
ALTER TABLE users
DROP CONSTRAINT state_check;
11
Copyright© Dr. Miguel A. Labrador
11
Data Manipulation Language (DML)
• Inserting data
INSERT INTO users
(id, ssn, first_name, last_name,
suffix, birth_date, address, city, zipcode,
state)
VALUES (nextval('user_seq'), 123456789, 'John',
'Smith', NULL, '1980-01-01', '12345 W 1st.', 'Orlando',
32830, DEFAULT);
• Modifying data
UPDATE users u
SET u.suffix = 'Jr.', u.city = 'Tampa'
WHERE u.id = 321;
• Deleting data
DELETE
FROM cities_table
WHERE city = 'Topeka' AND state = 'KS';
12
Copyright© Dr. Miguel A. Labrador
12
Data Retrieval
• A list of all users
SELECT * FROM users
• A more specific list
SELECT u.id, u.first_name, u.mid_name,
u.last_name
FROM users u
WHERE u.first_name = 'John';
• List of the date, serial
number, and maker of the
device of all the shifts in
which the user 'John Smith'
participated
SELECT u.id, u.first_name,
u.last_name, s.device_id, d.maker
FROM users u, shifts s, devices d
WHERE u.id = s.user_id AND
s.device_id = d.id AND u.id = 321;
13
Copyright© Dr. Miguel A. Labrador
13
Geographical Databases - PostGIS
• Extension of Postgres to work with geographical objects
– Defines SQL operations to store, retrieve, query, and update
geometric data types
– Defines a set of functions that can be applied to these objects
• Any DB with geometrical data must have extra metadata tables
that contain information about the geometric type columns
– geometry_columns table
• Stores all the columns with geometrical information
– spatial_ref_sys table
• Set of available coordinate systems and transformations
– feature_table
• Stores a collection of features or geometric types
– geometry_table
• Stores the instances of geometric objects
14
Copyright© Dr. Miguel A. Labrador
14
Creating a Table in PostGIS
CREATE TABLE tracking(
tracking_id INTEGER,
shift_id INTEGER
CONSTRAINT shift_id_not_null NOT NULL,
date_collection date
CONSTRAINT date_coll_not_null NOT NULL,
date_insertion date
CONSTRAINT date_ins_not_null NOT NULL,
CONSTRAINT tracking_pk
PRIMARY KEY (tracking_id),
CONSTRAINT shift_id_fk
FOREIGN KEY (shift_id)
REFERENCES shifts (shift_id)
ON UPDATE CASCADE ON DELETE CASCADE
)TABLESPACE pg_default;
SELECT AddGeometryColumn( 'tracking', 'tracking_point',
32661, 'POINT', 2);
ALTER TABLE tracking
ADD CONSTRAINT tracking_point_valid_check
CHECK (isvalid(tracking_point));
15
Copyright© Dr. Miguel A. Labrador
15
Inserting and Retrieving GSP Fixes
• Inserting a GPS fix
INSERT INTO tracking
(tracking_id, shift_id, tracking_point, date_collection,
date_insertion)
VALUES
(nextval('tracking_seq'), 999, GeomFromText('POINT(1.9845
49.8543)', 32661),'2009-01-02', '2009-01-01');
– GeomFromText generates geometric object
• Retrieving all points and collection time from shift 999
SELECT AsText(t.tracking_point) AS tracking, t.date_collection
FROM tracking t
WHERE t.shift_id = 999;
– AsText () function transforms the internal representation of the
geometric object into well-know text (WKT) format
16
Copyright© Dr. Miguel A. Labrador
16
Accessing Your Database Using Java
• Java DataBase Connectivity (JDBC) API
• Connect, store, and retrieve data
try{
javax.naming.InitialContext ic =
new javax.naming.InitialContext();
javax.sql.DataSource dataSource =
(javax.sql.DataSource)ic.lookup("jdbc/lbsbook");
Connection lbs_book
= dataSource.getConnection();
// All operations on the databases are defined here!
lbs_book.close();
}catch(Exception ex){ex.printStack();}
17
Copyright© Dr. Miguel A. Labrador
17
Inserting a GPS Fix
PreparedStatement insertStatement =
lbs_book.prepareStatement("INSERT INTO tracking
(tracking_id, date_collection, shift_id, tracking_point, date_insertion)
VALUES
(nextval('tracking_seq'),?,?,ST_GeomFromText(?,32661),?)");
insertStatement.setTimestamp(1, new java.sql.Timestamp(timefix));
insertStatement.setLong(2, shift_id);
String coordinate = "POINT("+XLong+" "+YLat+")";
insertStatement.setString(3,coordinate);
insertStatement.setTimestamp(4,
new java.sql.Timestamp(new Date().getTime()));
insertStatement.executeUpdate();
18
Copyright© Dr. Miguel A. Labrador
18
Data Queries
//Open the connection with the database
Statement stmt = lbs_book.createStatement();
ResultSet rs;
rs = stmt.executeQuery("SELECT id, first_name, last_name
FROM users WHERE id < 2001");
while (rs.next()) {
int id = rs.getInt(1);
String firstName = rs.getString("first_name");
String lastName = rs.getString("last_name");
System.out.println(id + ``,'' + firstName + ``'' +
lastName + "\n");
}
//Close the connection with the database
19
Copyright© Dr. Miguel A. Labrador
19
The Postgres Database
20
Copyright© Dr. Miguel A. Labrador
20
Parameters of a New Database
21
Copyright© Dr. Miguel A. Labrador
21
Creating a New Table
22
Copyright© Dr. Miguel A. Labrador
22
Name of the New Table
23
Copyright© Dr. Miguel A. Labrador
23
New Column
24
Copyright© Dr. Miguel A. Labrador
24
List of All Columns
25
Copyright© Dr. Miguel A. Labrador
25
Primary Key Constraint
26
Copyright© Dr. Miguel A. Labrador
26
Columns Part of the Primary Key
27
Copyright© Dr. Miguel A. Labrador
27
Including a Constraint for the Zipcode
Column
28
Copyright© Dr. Miguel A. Labrador
28
Obtaining SQL Script for the Creation of the
Table
29
Copyright© Dr. Miguel A. Labrador
29
Opening the SQL Execution Module
30
Copyright© Dr. Miguel A. Labrador
30
Executing SQL Code to Create a New Table
31
Copyright© Dr. Miguel A. Labrador
31
Executing a SQL Query
32
Copyright© Dr. Miguel A. Labrador
32