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 53666 53688 53650 name login Jones jones@cs Smith smith@eecs Smith smith@math age 18 18 19 gpa 3.4 3.2 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!
converted to in a table of is a savings of 1 VARCHAR(20) 1 TINYINT UNSIGNED 1 million rows ~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
Mike Hillyer
phone1
403-555-1717
phone2
403-555-1919
email1
mike@hoppen.com
email2
mhillyer@mysite.co m
name nickname phone1 phone2 phone3 cell pager address city province postal_code country email1 email2 web_url company department picture notes email_format
Tom Jensen
403-555-1919
403-555-1313
tom@openwin.org
tom@supersite.org
Ray Smith
403-555-1919
403-555-1111
ray@cpma.com
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_name last_name nickname phone cell pager address city province postal_code country web_url department picture notes
first_nam last_nam e e
Mike Mike Tom Tom Ray Ray Hillyer Hillyer Jensen Jensen Smith Smith
phone
403-555-1717 403-555-1919 403-555-1919 403-555-1313 403-555-1919 403-555-1111
email
mike@hoppen.com mhillyer@mysite.com tom@openwin.org tom@supersite.org ray@cpma.com
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 nickname address city province postal_code country web_url company department picture notes
phone
email
PK
PK email_id address
phone_id country_code number extension
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 PK user_id PK user_id first_name first_name last_name last_name nickname address nickname city address province city postal_code province country postal_code web_url country picture web_url notes picture email_format notes
user_phone PK,FK1 user_id PK,FK2 phone_id PK
phone phone_id country_code number extension type
email email PK address PK address type FK1 user_id FK1 user_id
user_company PK,FK1 user_id PK,FK2 company_id department PK
company company_id name
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 user PK user_id first_name last_name nickname address city province postal_code country web_url picture notes PK,FK1 user_id PK,FK2 phone_id extension PK phone phone_id country_code number type
email PK FK1 address user_id format
user_company PK,FK1 user_id PK,FK2 company_id department company PK company_id 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 userrequest 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 Structure Comparison Grouping Display Order Logical Operators Output Union SELECT, ALL / DISTINCT, *, AS, FROM, WHERE IN, BETWEEN, LIKE "% _" GROUP BY, HAVING, COUNT( ), SUM( ), AVG( ), MAX( ), MIN( ) ORDER BY, ASC / DESC AND, OR, NOT
INTO TABLE / CURSOR TO FILE [ADDITIVE], TO PRINTER, TO SCREEN 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 Hansen Svendson Pettersen FirstName Ola Tove Kari Address Timoteivn 10 Borgvn 23 Storgt 20 City Sandnes Sandnes 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
Gives a result set like this:
LastName 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 Nilsen Rasmussen FirstName Fred Address Kirkegt 56 Storgt 67 City Stavanger
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 Nilsen Rasmussen FirstName Fred Nina Address Kirkegt 56 Storgt 67 City Stavanger
Update several Columns in a Row
LastName Nilsen Rasmussen FirstName Fred Address Kirkegt 56 Storgt 67 City Stavanger
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 Nilsen Rasmussen FirstName Fred Nina Address Kirkegt 56 Stien 12 City Stavanger Stavanger
Delete a Row
LastName Nilsen Rasmussen FirstName Fred Nina Address Kirkegt 56 Stien 12 City Stavanger Stavanger
"Nina Rasmussen" is going to be deleted:
DELETE FROM Person WHERE LastName = 'Rasmussen'
LastName Nilsen FirstName Fred Address Kirkegt 56 City 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
