Database - School of Information

obtainablerabbiΔιαχείριση Δεδομένων

31 Ιαν 2013 (πριν από 4 χρόνια και 6 μήνες)

111 εμφανίσεις


Information Architecture & Design II

David Kim

1

April 28 2005

Web Databases



David Kim

David Kim

2

April 28, 2005



What is a Database?



File System vs. DBMS



Database Models



Database Modeling



Databases



SQL



DBC



Choosing a database



IA & DB



Conclusion



References

Index

David Kim

3

April 28, 2005

What is a Database ?


A

very

large,

integrated

collection

of

data




A

collection

of

information

stored

in

a

computer

in

a

systematic

way



DBMS

(=

Database

Management

System)



-

A

software

used

to

manage

and

query

a

database



David Kim

4

April 28, 2005



What is a Database?



File System vs. DBMS



Database Models



Database Modeling



Databases



SQL



DBC



Choosing a database



IA & DB



Conclusion



References

Index

David Kim

5

April 28, 2005

FMS vs. DBMS

advantages

disadvantages

FMS



Simpler to use



Less expensive



Typically no multi
-
user access



Limited to smaller databases



Limited functionality



Decentralization of data



Redundancy and integrity issues

DBMS



Greater flexibility



Greater processing power



Ensures data integrity



Supports simultaneous


access



Provides backup and


recovery controls



Advanced security



Difficult to learn



Packaged separately from the


OS



Slower processing speeds



Requires skilled administrators



Expensive

David Kim

6

April 28, 2005



What is a Database?



File System vs. DBMS



Database Models



Database Modeling



Databases



SQL



DBC



Choosing a database



IA & DB



Conclusion



References

Index

David Kim

7

April 28, 2005

Database Models 1

File System

(before ’70s)

Hybrid model

(mid ’80s)

Relational model

(late ’80s)

Object
-
oriented
model (mid ’90s)

Object
-
relational
model (late ’90s)

data

modeling

capacity

data

management

facilities

* adapted from the material in http://www.ktdata.co.kr/ktdata/kthome7/zeus_5.htm

David Kim

8

April 28, 2005

Database Models 2

Database Model: a collection of logical constructs



flat (or table) model




hierarchical model




network model




relational model




object
-
oriented model





object/relational (= post
-
relational) model



David Kim

9

April 28, 2005



What is a Database?



File System vs. DBMS



Database Models



Database Modeling



Databases



SQL



DBC



Choosing a database



IA & DB



Conclusion



References

Index

David Kim

10

April 28, 2005

DB Modeling 1

ORDER

CUSTOMER

ITEM

PRODUCT

Is for

Initiate

Is part of

consist of

has

Is included

ID

Order date

Order type

Name

Phone #

SSN

Birthday

ID

Quantity

Price

ID

Description

Type

ER Model

Discount

Description

*source: www.fkii.or.kr/data/seminar/uploaded_files/
유해진
%20
부장
%20(
액센츄어
).ppt

David Kim

11

April 28, 2005

DB Modeling 2



Relational Model

ORDER

CUSTOMER

ORDER_ITEM

ITEM

ID

Order_date (M)

Order_type (M)


SSN

Name (M)

Phone #

Birthday

Order_ID

Item_ID

Quantity (M)

Discount

ID

Description (M)

Price (M)

PRT_ID

PRODUCT

ID

Description (M)

Type

*source: www.fkii.or.kr/data/seminar/uploaded_files/
유해진
%20
부장
%20(
액센츄어
).ppt

David Kim

12

April 28, 2005

Normalization 1


1
st

Normal Form :
(No Repeating Groups)






2
nd

Normal Form

:
(
Non
-
key Attribute depend
on the whole primary key)




Children1

Comment1


.

ChildrenX

CommentX

Customer_ID

Comment

Customer_ID

Child

Supplier_Name

Part_color

Contact_Name

Contact_Phone

Part_Price

Supplier_ID

Part_No

City_Name

Supplier_Name

Supplier_ID

Part_Color

Part_Price

Part_No


Contact_Name

Contact_Phone

Supplier_ID

Part_No

City_Name

*adpated from www.fkii.or.kr/data/seminar/uploaded_files/
유해진
%20
부장
%20(
액센츄어
).ppt

David Kim

13

April 28, 2005

Normalization 2


3
rd

Normal Form :
(Non
-
Key Attributes depend
only and fully on primary key)







Boyce
-
Codd Normal Form
(
BCNF)


4
NF


5NF

Supplier_Name

Part_color

Contact_Name

Contact_Phone

Part_Price

Part_No

Contact_Phone

Supplier_Name

Contact_Name


Part_Color

Part_Price

Supplier_Name

Contact_Name

Part_No


*adpated from www.fkii.or.kr/data/seminar/uploaded_files/
유해진
%20
부장
%20(
액센츄어
).ppt

David Kim

14

April 28, 2005



What is a Database?



File System vs. DBMS



Database Models



Database Modeling



Databases



SQL



DBC



Choosing a database



IA & DB



Conclusion



References

Index

David Kim

15

April 28, 2005

Databases

Open source Databases



MySQL



PostgreSQL

Commercial Databases



MS SQL Server



ORACLE



DB2



Informix

David Kim

16

April 28, 2005



What is a Database?



File System vs. DBMS



Database Models



Database Modeling



Databases



SQL



DBC



Choosing a database



IA & DB



Conclusion



References

Index

David Kim

17

April 28, 2005

SQL

Structured Query Language
:
an ANSI standard computer
language for accessing and manipulating databases




ANSI SQL
: SQL Standard (SQL
-
92)





MySQL
: MySQL Server




Jet SQL
: MS Access





T
-
SQL

(Transact
-
SQL) : SQL Server





SQL*Plus, PL/SQL

(Procedural Language/SQL) :


Oracle


David Kim

18

April 28, 2005



What is a Database?



File System vs. DBMS



Database Models



Database Modeling



Databases



SQL



DBC



Choosing a Database



IA & DB



Conclusion



References

Index

David Kim

19

April 28, 2005

Database Providers






ODBC (Open Database Connectivity)



OLEDB(Object Linking and Embedding DB)



ADO.NET (ActiveX Data Objects for .NET)



JDBC (Java Database Connectivity)


Data Consumer

Data Provider

Data Store

SQL Server

ADO.NET

ASP.NET

David Kim

20

April 28, 2005



What is a Database?



File System vs. DBMS



Database Models



Database Modeling



Databases



SQL



DBC



Choosing a Database



IA & DB



Conclusion



References

Index

David Kim

21

April 28, 2005

Choosing a Database 1



Desktop or Server Database



OS Environment



Size of the project



Flexibility



Portability



Cost/Budget



What else?

David Kim

22

April 28, 2005

Choosing a Database 2

POSTGRESQL

MYSQL

ANSI SQL compliance

Closer to ANSI SQL standard

Follows some of the ANSI SQL
standards

Performance

Slower

Faster

Sub
-
selects

Yes

No

Transactions

Yes

Yes, however InnoDB table
type must be used

Database replication

Yes

Yes

Foreign key support

Yes

No

Views

Yes

No

Stored procedures

Yes

No

Triggers

Yes

No

Unions

Yes

No

Full joins

Yes

No

Constraints

Yes

No

Windows support

Yes

Yes

Vacuum (cleanup)

Yes

No

ODBC

Yes

Yes

JDBC

Yes

Yes

Different table types

No

Yes

* source : www.sraapowergres.com/en/ whitepapers/postgres%20vs%20mysql.pdf

David Kim

23

April 28, 2005



What is a Database?



File System vs. DBMS



Database Models



Database Modeling



Databases



SQL



DBC



Choosing a database



IA & DB



Conclusion



References

Index

David Kim

24

April 28, 2005

IA & DB

Database Modeler
-

Coder
-

Information


Architect




IA on the whole project




Front
-
end UI based on the DB Modeling




Your opinion…

David Kim

25

April 28, 2005



What is a Database?



File System vs. DBMS



Database Models



Database Modeling



Databases



SQL



DBC



Choosing a database



IA & DB



Conclusion



References

Index

David Kim

26

April 28, 2005

Conclusion


DBMS is now essential
:


“Information Architects should know about it!”


David Kim

27

April 28, 2005



What is a Database?



File System vs. DBMS



Database Models



Database Modeling



Databases



SQL



DBC



Choosing a database



IA & DB



Conclusion



References

Index

David Kim

28

April 28, 2005

References


Database Models: Hierarcical, Network, Relational, Object
-
Oriented, Semistructured,
Associative and Context.


Database Models



Normalization



eXtropia | Tutorials | Introduction to Databases for the Web | Database Models



Data Modeling: Overview of the Relational Model



DBMS
-

April 1998
-

Modeling Object/Relational Databases



Object
-
Relational Model



Object
-
relational database articles and products



Java Database



Overview of ADO.NET



Welcome to FKII



PowerGres



ZEUS/UniSQL
-

Korea Telecom Data,Inc

David Kim

29

April 28, 2005

Appendix


David Kim

30

April 28, 2005

Flat Model


Spreadsheet (e.g. Excel)


David Kim

31

April 28, 2005

Hierarchical Model

A

B

C

D

E

F

C1

D1

F1

F2

C2



David Kim

32

April 28, 2005

Network Model

Company

Customer

Product

Invoice

Payment

InvoiceLine

1

M

M

1

1

M

M

1

M

1



David Kim

33

April 28, 2005


Relational Model 1

Class ID

Class Name

1

IA&D2

2

ClassA

3

ClassB

Student
ID

Name

Address

1

John

Far West

2

Tom

Riverside

3

David

Lake Austin

Student
ID

Class
ID

Name

Address

1

2

John

Far West

2

3

Tom

Riverside

3

1

David

Lake
Austin

David Kim

34

April 28, 2005

Relational Model 2

Student
ID

Name

Address

1

John

Far West

2

Tom

Riverside

3

David

Lake Austin

Class ID

Class
Name

1

IA&D2

2

ClassA

3

ClassB

Class ID

Student ID

1

3

2

1

3

2



David Kim

35

April 28, 2005

Object
-
Oriented Model

Student

Grad
Student

Class

Department

studentsInClass()

studentsInDept()

profInThisDept()

inherit

printTranscript()



David Kim

36

April 28, 2005

Object/Relational Model

Relational
Model

Object
-
oriented
Model

Object
-
relational
Model



* material from: http://www.ktdata.co.kr/ktdata/kthome7/zeus_5.htm

David Kim

37

April 28, 2005

ANSI SQL

SQL Data Manipulation Language (DML)




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


SQL Data Definition Language (DDL)



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



David Kim

38

April 28, 2005

Jet SQL


IIF(InStr(si.GEOG_UNIT,',')<>0,MID([si.GEOG_UNIT],1,InStr([si.GE
OG_UNIT],',')
-
1))



$query0 .= ($state && $state != 'state_undet') ? "AND
((si.GEOG_RANK='county' AND
IIF(InStr(si.GEOG_UNIT,',')<>0,MID([si.GEOG_UNIT],1,InStr([si.GE
OG_UNIT],',')
-
1)) $sql_op '$state') OR (si.GEOG_RANK='province'
AND
IIF(InStr(si.GEOG_UNIT,',')<>0,MID([si.GEOG_UNIT],InStr([si.GEO
G_UNIT],',')+1)) $sql_op ' $state') OR (si.GEOG_RANK='province'
AND
IIF(InStr(si.GEOG_UNIT,',')=0,MID([si.GEOG_UNIT],InStr([si.GEO
G_UNIT],',')+1)) $sql_op '$state'))":"";



David Kim

39

April 28, 2005

Stored Procedure

CREATE PROCEDURE prGet_CustomerRowSet



@sCustomerID NCHAR(5)


AS




SELECT CompanyName, ContactName, City


FROM Customers


WHERE CustomerID = @sCustomerID


GO




David Kim

40

April 28, 2005

PL/SQL

DECLARE



qty_on_hand NUMBER(5);

BEGIN



SELECT quantity INTO qty_on_hand FROM inventory


WHERE product = 'TENNIS RACKET'


FOR UPDATE OF quantity;


IF qty_on_hand > 0 THEN
--

check quantity


UPDATE inventory SET quantity = quantity
-

1


WHERE product = 'TENNIS RACKET';


ELSE


INSERT INTO purchase_record


VALUES ('Out of tennis rackets', SYSDATE);


END IF;


COMMIT;

END;




David Kim

41

April 28, 2005

ADO

<%

set conn=Server.CreateObject("ADODB.Connection")

conn.Provider="Microsoft.Jet.OLEDB.4.0"

conn.Open(Server.Mappath("/db/northwind.mdb"))

set rs = Server.CreateObject("ADODB.recordset")

rs.Open "Select * from Customers", conn


do until rs.EOF



for each x in rs.Fields



Response.Write(x.name)



Response.Write(" = ")



Response.Write(x.value & "<br />")



next



Response.Write("<br />")



rs.MoveNext

loop


rs.close

conn.close

%>




David Kim

42

April 28, 2005

ADO.NET


Visual Basic


Dim nwindConn As SqlConnection = New
SqlConnection("Data Source=localhost;" & _ "Integrated
Security=SSPI;Initial Catalog=northwind")



C#


SqlConnection nwindConn = new SqlConnection("Data
Source=localhost;Integrated Security=SSPI;Initial
Catalog=northwind");




David Kim

43

April 28, 2005

ODBC


VB

Dim nwindConn As OdbcConnection = New
OdbcConnection("Driver={SQL
Server};Server=localhost;" & _
"Trusted_Connection=yes;Database=northwind")




C#

OdbcConnection nwindConn = new
OdbcConnection("Driver={SQL
Server};Server=localhost;" +
"Trusted_Connection=yes;Database=northwind");



David Kim

44

April 28, 2005

OLEDB


VB

Dim nwindConn As OleDbConnection = New
OleDbConnection("Provider=SQLOLEDB;Data
Source=localhost;" & _ "Integrated Security=SSPI;Initial
Catalog=northwind")



C#

OleDbConnection nwindConn = new
OleDbConnection("Provider=SQLOLEDB;Data
Source=localhost;Integrated Security=SSPI;Initial
Catalog=northwind");




David Kim

45

April 28, 2005

JDBC

Class.forName("oracle.jdbc.driver.OracleDriver")

Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@dbaprod1:1521:SHR1_PRD",
username, passwd);