Presentation for FDW
What is RDBMS ?
The Relational Database Model:
Relational database management systems, where all data
are kept in tables or relations.
More flexible & easy to use.
Almost any item of data can be accessed more quickly than
the other models.
Retrieval time is reduced so that interactive access becomes
more feasible.
Advantages of RDBMS
Improved conceptual simplicity
Easier database design, implementation, management,
and use
Ad hoc query capability (SQL)
Powerful database management system
Disadvantages of RDBMS
Possibility of poor design and implementation
Relational databases do not have enough storage area
to handle data such as images, digital and audio/video.
The requirement that information must be in tables
where relationships between entities are defined by
values
Relational Database: Definitions
Relational database: a set of normalized relations with
distinct relation names.
Relation: made up of 2 parts:
Instance : a table, with rows and columns.
#Rows = cardinality, #fields = degree / arity.
Schema : specifies name of relation, plus name and type of each
column.
E.G. Students(sid: string, name: string, login: string,
age: integer, gpa: real).
Example Instance of Students Relation
sid name login age gpa
53666 Jones jones@cs 18 3.4
53688 Smith smith@eecs 18 3.2
53650 Smith smith@math 19 3.8
Cardinality = 3, degree = 5, all rows distinct
What is Database Normalization?
Cures the ‘Spreadsheet Syndrome’
Store only the minimal amount of information.
Remove redundancies.
Restructure data.
Benefits of Database Normalization?
Decreased storage requirements!
1 VARCHAR(20)
converted to 1 TINYINT UNSIGNED
in a table of 1 million rows
is a savings of ~20 MB
Faster search performance!
Smaller file for table scans.
More directed searching.
Improved data integrity!
What are the Normal Forms?
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Boyce-Codd Normal Form (BCNF)
Fourth Normal Form (4NF)
Fifth Normal Form (5NF)
Our Table
user name phone1 phone2 email1 email2
Mike Hillyer 403-555-1717 403-555-1919 mike@hoppen.com mhillyer@mysite.co
name
nickname
m
phone1
phone2
phone3
cell
pager Tom Jensen 403-555-1919 403-555-1313 tom@openwin.org tom@supersite.org
address
city
province
postal_code
country
email1
email2 Ray Smith 403-555-1919 403-555-1111 ray@cpma.com
web_url
company
department
picture
notes
email_format
First Normal Form
Remove horizontal redundancies
No two columns hold the same information
No single column holds more than a single item
Each row must be unique
Use a primary key
Benefits
Easier to query/sort the data
More scalable
Each row can be identified for updating
One Solution
user
first_nam last_nam phone email
first_name e e
last_name
nickname Mike Hillyer 403-555-1717 mike@hoppen.com
phone
cell
Mike Hillyer 403-555-1919 mhillyer@mysite.com
pager
address Tom Jensen 403-555-1919 tom@openwin.org
city
province Tom Jensen 403-555-1313 tom@supersite.org
postal_code
country Ray Smith 403-555-1919 ray@cpma.com
web_url
department Ray Smith 403-555-1111
picture
notes
• Multiple rows per user
• Emails are associated with only one other phone
• Hard to Search
Satisfying 1NF
user
PK user_id
first_name
last_name
phone
nickname email
address PK phone_id
city PK email_id
province country_code
postal_code address number
country extension
web_url
company
department
picture
notes
Second Normal Form
Table must be in First Normal Form
Remove vertical redundancy
The same value should not repeat across rows
Composite keys
All columns in a row must refer to BOTH parts of the
key
Benefits
Increased storage efficiency
Less data repetition
Satisfying 2NF
user user_phone phone
user
PK user_id PK,FK1 user_id PK phone_id
PK user_id PK,FK2 phone_id
first_name country_code
first_name
last_name number
last_name
nickname extension
address
nickname type
city
address
province
city
email postal_code
province user_company
email country
postal_code company
PK address web_url
country PK,FK1 user_id
PK address PK company_id
picture
web_url PK,FK2 company_id
type notes
picture
FK1 user_id name
FK1 user_id email_format
notes department
Third Normal Form
Table must be in Second Normal Form
If your table is 2NF, there is a good chance it is 3NF
All columns must relate directly to the primary key
Benefits
No extraneous data
Satisfying 3NF
user_phone
phone
user
PK,FK1 user_id
PK phone_id
PK user_id PK,FK2 phone_id
country_code
first_name extension
number
last_name
type
nickname
address
city
email province
postal_code user_company
PK address country company
PK,FK1 user_id
web_url
PK,FK2 company_id PK company_id
FK1 user_id picture
format notes
department name
Relational Query Languages
A major strength of the relational model:
supports simple, powerful querying of data.
Queries can be written intuitively, and the
DBMS is responsible for efficient evaluation.
What is SQL?
When a user wants to get some information from
a database file, he can issue a query.
A query is a user–request to retrieve data or
information with a certain condition.
SQL is a query language that allows user to specify
the conditions. (instead of algorithms)
4/28/2014
Concept of SQL
The user specifies a certain condition.
The program will go through all the records in
the database file and select those records that
satisfy the condition.(searching).
Statistical information of the data.
The result of the query will then be stored in
form of a table.
4/28/2014
SQL Data Definition Language (DDL)
The Data Definition Language (DDL) part of SQL permits
database tables to be created or deleted. We can also define
indexes (keys), specify links between tables, and impose
constraints between database tables.
The most important DDL statements in SQL are:
•CREATE TABLE - creates a new database table
•ALTER TABLE - alters (changes) a database table
•DROP TABLE - deletes a database table
•CREATE INDEX - creates an index (search key)
•DROP INDEX - deletes an index
SQL Data Manipulation Language (DML)
SQL (Structured Query Language) is a syntax for executing
queries. But the SQL language also includes a syntax to
update, insert, and delete records.
These query and update commands together form the Data
Manipulation Language (DML) part of SQL:
•SELECT - extracts data from a database table
•UPDATE - updates data in a database table
•DELETE - deletes data from a database table
•INSERT INTO - inserts new data into a database table
Basic structure of an SQL query
General SELECT, ALL / DISTINCT, *,
Structure AS, FROM, WHERE
Comparison IN, BETWEEN, LIKE "% _"
Grouping GROUP BY, HAVING,
COUNT( ), SUM( ), AVG( ), MAX( ), MIN( )
Display Order ORDER BY, ASC / DESC
Logical AND, OR, NOT
Operators
Output INTO TABLE / CURSOR
TO FILE [ADDITIVE], TO PRINTER, TO SCREEN
Union UNION
4/28/2014
SQL Database Tables
A database most often contains one or more tables. Each table
is identified by a name (e.g. "Customers" or "Orders"). Tables
contain records (rows) with data.
Below is an example of a table called "Persons":
LastName FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Svendson Tove Borgvn 23 Sandnes
Pettersen Kari Storgt 20 Stavanger
SQL CREATE STATEMENT
The create statement is used to create a table.
The SQL syntax for CREATE TABLE is
CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",
... )
So, if we are to create the persons table specified as above, we would type
in
CREATE TABLE persons
(lastname varchar2(50),
firstname varchar2(50),
address varchar2(50),
City varchar2(50));
SQL The SELECT Statement
The SELECT statement is used to select data from a table. The
tabular result is stored in a result table (called the result-set).
Syntax
SELECT column_name(s)
FROM table_name
SELECT LastName FROM Persons
LastName
Gives a result set like this:
Hansen
Svendson
Pettersen
The INSERT INTO Statement
The INSERT INTO statement is used to insert new rows into
a table.
Syntax
INSERT INTO table_name
VALUES (value1, value2,....)
You can also specify the columns for which you want to insert
data:
INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,....)
The Update Statement
The UPDATE statement is used to modify the data in a table.
Syntax
UPDATE table_name
SET column_name = new_value
WHERE column_name = some_value
The Delete Statement
The DELETE statement is used to delete rows in a table.
Syntax
DELETE FROM table_name
WHERE column_name = some_value
Update one Column in a Row
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Storgt 67
We want to add a first name to the person with a last name of
"Rasmussen":
UPDATE Person SET FirstName = 'Nina'
WHERE LastName = 'Rasmussen'
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Storgt 67
Update several Columns in a Row
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Storgt 67
We want to change the address and add the name of the city:
UPDATE Person
SET Address = 'Stien 12', City = 'Stavanger'
WHERE LastName = 'Rasmussen'
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Stien 12 Stavanger
Delete a Row
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Rasmussen Nina Stien 12 Stavanger
"Nina Rasmussen" is going to be deleted:
DELETE FROM Person WHERE LastName = 'Rasmussen'
LastName FirstName Address City
Nilsen Fred Kirkegt 56 Stavanger
Delete All Rows
It is possible to delete all rows in a table without deleting the
table. This means that the table structure, attributes, and indexes
will be intact:
DELETE FROM table_name
Or
DELETE * FROM table_name
