11

Databases & SQLDatabases & SQL

By:By: Julia Tartakovsky Julia TartakovskyCS 340, Fall 2004CS 340, Fall 2004

Acknowledgement (for help with the J# code Acknowledgement (for help with the J# code connecting to an Oracle DB):connecting to an Oracle DB): Yosef Lifshits Yosef Lifshits

22

Database Management System Database Management System (DBMS)(DBMS)

A DBMS is:A DBMS is:– A collection of interrelated data (a database), andA collection of interrelated data (a database), and– A set of programs to access the data A set of programs to access the data

DBMS provides an environment that is both DBMS provides an environment that is both convenientconvenient and and efficientefficient to use. to use.

Database Applications:Database Applications:– Banking:Banking: all transactions all transactions– Airlines:Airlines: reservations, schedules reservations, schedules– Universities:Universities: registration, grades registration, grades– Sales:Sales: customers, products, purchases customers, products, purchases– Manufacturing:Manufacturing: production, inventory, orders, supply chain production, inventory, orders, supply chain– Human resources:Human resources: employee records, salaries, tax deductions employee records, salaries, tax deductions

33

Purpose of Database SystemPurpose of Database System

In the early days, database applications were built on top of file systemsIn the early days, database applications were built on top of file systems– We are managing data in this way even todayWe are managing data in this way even today

– E.g. using Windows Explorer to find your filesE.g. using Windows Explorer to find your files

Drawbacks of using file systems to store data:Drawbacks of using file systems to store data:– Data redundancy and inconsistencyData redundancy and inconsistency

Multiple file formats, duplication of information in different filesMultiple file formats, duplication of information in different files

– Difficulty in accessing data Difficulty in accessing data Need to write a new program to carry out each new taskNeed to write a new program to carry out each new task

– Data isolation — multiple files and formatsData isolation — multiple files and formats

– Integrity problemsIntegrity problemsIntegrity constraints (e.g. account balance > 0) become part of Integrity constraints (e.g. account balance > 0) become part of program codeprogram codeHard to add new constraints or change existing onesHard to add new constraints or change existing ones

44

Purpose of Database Systems Purpose of Database Systems (Cont.)(Cont.)

Drawbacks of using file systems (cont.) Drawbacks of using file systems (cont.) – Atomicity of updatesAtomicity of updates

Failures may leave database in an inconsistent state with partial Failures may leave database in an inconsistent state with partial updates carried outupdates carried outE.g. transfer of funds from one account to another should either E.g. transfer of funds from one account to another should either complete or not happen at allcomplete or not happen at all

– Concurrent access by multiple usersConcurrent access by multiple usersConcurrent accessed needed for performanceConcurrent accessed needed for performanceUncontrolled concurrent accesses can lead to inconsistenciesUncontrolled concurrent accesses can lead to inconsistencies

– E.g. two people reading a balance and updating it at the same E.g. two people reading a balance and updating it at the same timetime

– Security problemsSecurity problems

Database systems offer solutions to all the above problemsDatabase systems offer solutions to all the above problems

55

Application ArchitecturesApplication Architectures

Two-tier architecture: E.g. client programs using ODBC/JDBC to communicate with a databaseThree-tier architecture: E.g. web-based applications, and applications built using “middleware”

66

Levels of AbstractionLevels of Abstraction

Physical levelPhysical level describes how a record (e.g., customer) is stored. describes how a record (e.g., customer) is stored.

Logical levelLogical level:: describes data stored in database, and the describes data stored in database, and the relationships among the data.relationships among the data.

typetype customer = customer = recordrecordnamename : string; : string;streetstreet : string; : string;citycity : integer; : integer;

endend;;

View levelView level:: application programs hide details of data types. Views application programs hide details of data types. Views can also hide information (e.g., salary) for security purposes. can also hide information (e.g., salary) for security purposes.

77

View of DataView of Data

An architecture for a database system

88

Schemas and InstancesSchemas and InstancesAnalogous to Analogous to typestypes and and variablesvariables in programming languages in programming languages

SchemaSchema – the logical structure of the database – the logical structure of the database – e.g., the database consists of information about a set of customers and e.g., the database consists of information about a set of customers and

accounts and the relationship between themaccounts and the relationship between them– Analogous to type information of a variable in a programAnalogous to type information of a variable in a program– Physical schemaPhysical schema: database design at the physical level: database design at the physical level– Logical schemaLogical schema: database design at the logical level: database design at the logical level

InstanceInstance – the actual content of the database at a particular point in time – the actual content of the database at a particular point in time – Analogous to the value of a variableAnalogous to the value of a variable

Physical Data IndependencePhysical Data Independence – the ability to modify the physical schema – the ability to modify the physical schema without changing the logical schemawithout changing the logical schema– Applications depend on the logical schemaApplications depend on the logical schema– In general, the interfaces between various levels and components In general, the interfaces between various levels and components

should be well defined so that changes in some parts do not seriously should be well defined so that changes in some parts do not seriously influence others.influence others.

99

Data ModelsData Models

A collection of conceptual tools for describing A collection of conceptual tools for describing – data data – data relationshipsdata relationships– data semanticsdata semantics– data constraintsdata constraints

Entity-Relationship modelEntity-Relationship modelRelational modelRelational modelOther models: Other models: – object-oriented modelobject-oriented model– Object-relational modelObject-relational model– semi-structured data models, XMLsemi-structured data models, XML

1010

Entity-Relationship ModelEntity-Relationship Model

Example of schema in the entity-relationship model:Example of schema in the entity-relationship model:

1111

Entity Relationship Model (Cont.)Entity Relationship Model (Cont.)

E-R model of real worldE-R model of real world– Entities (objects) Entities (objects)

E.g. customers, accountsE.g. customers, accounts

– Relationships between entitiesRelationships between entitiesE.g. Account A-101 is held by customer JohnsonE.g. Account A-101 is held by customer JohnsonRelationship set Relationship set depositordepositor associates customers with associates customers with accountsaccounts

Widely used for database designWidely used for database design– Database design in E-R model usually converted to Database design in E-R model usually converted to

design in the relational model (coming up next) which design in the relational model (coming up next) which is used for storage and processingis used for storage and processing

1212

Relational ModelRelational Model

A collection of tablesA collection of tablesExample of tabular data in the relational modelExample of tabular data in the relational model

customer-name

Customer-id

customer-street

customer-city

account-number

Johnson

Smith

Johnson

Jones

Smith

192-83-7465

019-28-3746

192-83-7465

321-12-3123

019-28-3746

Alma

North

Alma

Main

North

Palo Alto

Rye

Palo Alto

Harrison

Rye

A-101

A-215

A-201

A-217

A-201

Attributes

1313

A Sample Relational DatabaseA Sample Relational Database

1414

What is SQL?What is SQL?

SQL (pronounced "ess-que-el") stands for Structured Query SQL (pronounced "ess-que-el") stands for Structured Query Language. Language.

SQL is used to communicate with a database. SQL is used to communicate with a database.

SQL statements are used to perform tasks such as:SQL statements are used to perform tasks such as:– update data on a database (an update data on a database (an actionaction query) query)

– retrieve data from a database (a retrieve data from a database (a selectselect query) query)

1515

What is SQL? (cont.)What is SQL? (cont.)

Relational database management systems that use SQL: Relational database management systems that use SQL: – OracleOracle– SybaseSybase– Microsoft SQL ServerMicrosoft SQL Server– AccessAccess– IngresIngres

Standard SQL commands:Standard SQL commands:– "Select“"Select“– "Insert" "Insert" – "Update“"Update“– "Delete“"Delete“– "Create“"Create“– "Drop""Drop"

1616

SQLSQL

SQL: widely used non-procedural languageSQL: widely used non-procedural language– E.g. find the name of the customer with customer-id E.g. find the name of the customer with customer-id

192-83-7465192-83-7465selectselect customer.customer-namecustomer.customer-namefromfrom customercustomerwherewhere customer.customer-idcustomer.customer-id = ‘192-83-7465’ = ‘192-83-7465’

– E.g. find the balances of all accounts held by the E.g. find the balances of all accounts held by the customer with customer-id 192-83-7465customer with customer-id 192-83-7465

selectselect account.balanceaccount.balancefromfrom depositordepositor, , accountaccountwherewhere depositor.customer-iddepositor.customer-id = ‘192-83-7465’ = ‘192-83-7465’ andand depositor.account-number = account.account-depositor.account-number = account.account-

numbernumber

1717

SELECTSELECT

customer id name address

1

2

3

John

Smith

Joan

4

birth year

1975

1955

1980

Kowloon

Kowloon

Hong Kong

Bobby

5 Shirley

1976

1974

CUSTOMER

Hong Kong

Hong Kong

SELECTFROM

customer_id, nameCUSTOMER

WHERE birth_year>=1975

customer id name

1

3

John

Joan

RESULT

4 Bobby

For more examples and an interactive environment, go to:http://sqlcourse.com/select.html

1818

SELECT with “distinct”SELECT with “distinct”

Eliminate identical Eliminate identical records.records.

customer id name address

1

2

3

John

Smith

Joan

4

birth year

1975

1955

1980

Kowloon

Kowloon

Hong Kong

Bobby

5 Shirley

1976

1974

CUSTOMER

Hong Kong

Hong Kong

RESULT

address

Kowloon

Kowloon

Hong Kong

Hong Kong

Hong Kong

SELECTFROM

addressCUSTOMER

SELECTFROM

addressCUSTOMER

distinct

address

Hong Kong

Kowloon

RESULT

1919

Aggregate with distinctAggregate with distinct

customer id name address

1

2

3

John

Smith

Joan

4

birth year

1975

1955

1980

Kowloon

Kowloon

Hong Kong

Bobby

5 Shirley

1976

1974

CUSTOMER

Hong Kong

Hong Kong

SELECTFROM

count(address)CUSTOMER

ANSWER: 5

SELECTFROM

count(distinct address)CUSTOMER

ANSWER: 2For more examples and an interactive environment, go to:http://sqlcourse2.com/agg_functions.html

2020

Group-byGroup-by

Grouping tuples with identical attributes.Grouping tuples with identical attributes.

account id customer id balance

A1

A2

A3

1

1

2

A4

type

saving 20k

5k

35k

3

A5 3

100k

30k

saving

saving

check

check

ACCOUNT

customer id balancetypeaccount id

account id customer id balance

A1

A2

A3

1

1

2

A4

type

saving 20k

5k

35k

3

A5 3

100k

30k

saving

saving

check

check

RESULT - GROUP BY customer_id

customer id balancetypeaccount id

For more examples and an interactive environment, go to:http://sqlcourse2.com/groupby.html

2121

Summarized information by groupsSummarized information by groupsSELECTFROM

customer_id, COUNT(*)ACCOUNT

GROUP BY customer_id

customer id balancetypeaccount id

account id customer id balance

A1

A2

A3

1

1

2

A4

type

saving 20k

5k

35k

3

A5 3

100k

30k

saving

saving

check

check

GROUP BY customer_id

customer id balancetypeaccount id

customer id COUNT

1

2

2

1

RESULT

3 2

2323

JoinJoin

customer id name

1 John

CUSTOMER

account id customer id balance

A1

A2

1

1

20k

5k

ACCOUNT

1 John

RESULT

A3 2 35k2 Smith

A4 3 100k3 Joan

JOIN

SELECTFROM

*CUSTOMER, ACCOUNT

WHERE CUSTOMER.customer_id = ACCOUNT.customer_id

customer id name

1

2

3

John

Smith

Joan

CUSTOMER

account id customer id balance

A1

A2

A3

1

1

2

A4

20k

5k

35k

3 100k

ACCOUNT

The result table The result table removes removes meaningless meaningless tuples from the tuples from the cartesian product.cartesian product.

2424

Join with group-by and havingJoin with group-by and having

customer id name

1 John

CUSTOMER

account id customer id balance

A1

A2

1

1

20k

5k

ACCOUNT

1 John

RESULT

A3 2 35k2 Smith

A4 3 100k3 Joan

JOIN

SELECT

FROM

CUSTOMER.name,

CUSTOMER, ACCOUNT

WHERE CUSTOMER.customer_id = ACCOUNT.customer_id

GROUP BY CUSTOMER.nameHAVING COUNT(*)=2

sum(ACCOUNT.balance)

name balance

name balance

name

John

CUSTOMER ACCOUNT

John

Smith

Joan

balance

20k

5k

35k

100k

SELECTFROM

*CUSTOMER, ACCOUNT

WHERE CUSTOMER.customer_id = ACCOUNT.customer_id

name

John

JOIN RESULT

SUM

25k

QUESTIONQUESTION:: What is the meaning of this query?What is the meaning of this query?

2525

CREATE TableCREATE Table

CREATE TABLECREATE TABLE table-name (attr1 attr-type table-name (attr1 attr-type PRIMARYKEY, attr2 attr-type,…,attrN attr-type);PRIMARYKEY, attr2 attr-type,…,attrN attr-type);

Adds a new table with the specified attributes Adds a new table with the specified attributes (and types) to the database.(and types) to the database.

For more examples and an interactive environment, go to: http://sqlcourse.com/create.htmlhttp://sqlcourse.com/create.html

2626

Access Data TypesAccess Data Types

NumericNumeric (1, 2, 4, 8 bytes, fixed or float) (1, 2, 4, 8 bytes, fixed or float)TextText (255 max) (255 max)MemoMemo (64000 max) (64000 max)Date/TimeDate/Time (8 bytes) (8 bytes)CurrencyCurrency (8 bytes, 15 digits + 4 digits decimal) (8 bytes, 15 digits + 4 digits decimal)AutonumberAutonumber (4 bytes) (4 bytes)Yes/NoYes/No (1 bit) (1 bit)OLEOLE (limited only by disk space) (limited only by disk space)HyperlinksHyperlinks (up to 64000 chars) (up to 64000 chars)

2727

Access Numeric typesAccess Numeric typesByteByte

– Stores numbers from 0 to 255 (no fractions). 1 byteStores numbers from 0 to 255 (no fractions). 1 byte

IntegerInteger– Stores numbers from –32,768 to 32,767 (no fractions) 2 bytesStores numbers from –32,768 to 32,767 (no fractions) 2 bytes

Long IntegerLong Integer (Default)(Default) – Stores numbers from –2,147,483,648 to 2,147,483,647 (no fractions). Stores numbers from –2,147,483,648 to 2,147,483,647 (no fractions).

4 bytes4 bytes

SingleSingle– Stores numbers from -3.402823E38 to –1.401298E–45 for negative Stores numbers from -3.402823E38 to –1.401298E–45 for negative

values and from 1.401298E–45 to 3.402823E38 for positive values.values and from 1.401298E–45 to 3.402823E38 for positive values.4 bytes4 bytes

DoubleDouble– Stores numbers from –1.79769313486231E308 to –Stores numbers from –1.79769313486231E308 to –

4.94065645841247E–324 for negative values and from 4.94065645841247E–324 for negative values and from 1.79769313486231E308 to 4.94065645841247E–324 for positive 1.79769313486231E308 to 4.94065645841247E–324 for positive values.values. 1515 8 bytes8 bytes

Replication IDReplication ID– Globally unique identifier (GUID)Globally unique identifier (GUID) N/AN/A 16 bytes16 bytes

2828

Oracle Data TypesOracle Data Types

CHAR (size) -- max 2000CHAR (size) -- max 2000VARCHAR2(size) -- up to 4000VARCHAR2(size) -- up to 4000DATEDATEDECIMAL, FLOAT, INTEGER, INTEGER(s), DECIMAL, FLOAT, INTEGER, INTEGER(s), SMALLINT, NUMBER, NUMBER(size,d)SMALLINT, NUMBER, NUMBER(size,d)– All numbers internally in same format…All numbers internally in same format…

LONG, LONG RAW, LONG VARCHARLONG, LONG RAW, LONG VARCHAR– up to 2 Gb -- only one per tableup to 2 Gb -- only one per table

BLOB, CLOB, NCLOB -- up to 4 GbBLOB, CLOB, NCLOB -- up to 4 GbBFILE -- file pointer to binary OS fileBFILE -- file pointer to binary OS file

2929

Creating a new table from existing tablesCreating a new table from existing tables

Syntax:Syntax:– SELECTSELECT [DISTINCT] attr1, attr2,…, attr3 [DISTINCT] attr1, attr2,…, attr3

INTOINTO newtablename newtablename FROMFROM rel1 r1, rel2 r2,… rel1 r1, rel2 r2,… rel3 r3 rel3 r3 WHEREWHERE condition1 {AND | OR} condition1 {AND | OR} condition2 condition2 ORDER BYORDER BY attr1 [DESC], attr3 attr1 [DESC], attr3 [DESC] [DESC]

3030

ALTER TableALTER Table

ALTER TABLE table-name ADD COLUMN ALTER TABLE table-name ADD COLUMN attr1 attr-type;attr1 attr-type;

… … DROP COLUMN attr1;DROP COLUMN attr1;Adds a new column to an existing Adds a new column to an existing database table.database table.

3131

INSERTINSERT

INSERT INTOINSERT INTO table-name (attr1, attr4, attr5,…, table-name (attr1, attr4, attr5,…, attrK) attrK) VALUESVALUES (“val1”, val4, val5,…, “valK”); (“val1”, val4, val5,…, “valK”);

Adds a new row(s) to a table.Adds a new row(s) to a table.

INSERT INTOINSERT INTO table-name (attr1, attr4, attr5,…, table-name (attr1, attr4, attr5,…, attrK) attrK) VALUESVALUES SELECT ... SELECT ...

For more examples and an interactive environment, go to: http://sqlcourse.com/insert.htmlhttp://sqlcourse.com/insert.html

3232

DELETEDELETE

DELETE FROMDELETE FROM table-name table-name WHEREWHERE <where clause>;<where clause>;

Removes rows from a table.Removes rows from a table.

For more examples and an interactive environment, go to: http://sqlcourse.com/delete.htmlhttp://sqlcourse.com/delete.html

3333

UPDATEUPDATE

UPDATEUPDATE tablename tablename SETSET attr1=newval, attr1=newval, attr2 = newval2 attr2 = newval2 WHEREWHERE <where clause>; <where clause>;

changes values in existing rows in a table changes values in existing rows in a table (those that match the WHERE clause).(those that match the WHERE clause).

For more examples and an interactive environment, go to: http://sqlcourse.com/update.htmlhttp://sqlcourse.com/update.html

3434

DROP TableDROP Table

DROP TABLEDROP TABLE tablename; tablename;

Removes a table from the database.Removes a table from the database.

For more examples and an interactive environment, go to:http://sqlcourse.com/drop.html

3535

J# Source Code Connecting to an Oracle DBJ# Source Code Connecting to an Oracle DBpackage juliadb1; import System.Drawing.*; import System.Collections.*; import System.ComponentModel.*; import System.Windows.Forms.*; import System.Data.*; // ODP.NET Oracle managed provider /** * Summary description for Form1. */ public class Form1 extends System.Windows.Forms.Form { private System.Windows.Forms.Button button1; private System.Windows.Forms.Label label1; private System.Windows.Forms.Label label2; /** * Required designer variable. */ private System.ComponentModel.Container components = null; public Form1() { // // Required for Windows Form Designer support // InitializeComponent(); // // TODO: Add any constructor code after InitializeComponent call // } /** * Clean up any resources being used. */ protected void Dispose(boolean disposing) { if (disposing) { if (components != null) { components.Dispose(); } } super.Dispose(disposing); } #region Windows Form Designer generated code /** * Required method for Designer support - do not modify * the contents of this method with the code editor. */ private void InitializeComponent() {

Microsoft Word Document

3636

Screenshot of J# Program OutputScreenshot of J# Program Output

3737

Questions?Questions?

For a neat SQL tutorial and an interactive environment, go to:

http://sqlcourse2.com/intro2.htmlhttp://sqlcourse2.com/intro2.html