Ph.D.

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

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

132 εμφανίσεις

Public Management Information Systems


Data Structure &
Databases



Monday, March 18, 2013

Hun Myoung Park,
Ph.D.



Public Management & Policy Analysis Program

Graduate School of International Relations

2

Data Structure


A way of storing and organizing data.
Examples are arrays, lists, trees, hashes,
graphs


Master versus transaction files; for cost
saving (efficiency) and data security


File organization


Sequential file organization


Direct file (random file) organization


Indexed sequential file organization

3

File Formats


ASCII text formats: how are data items
separated in a file?


Free format (space delimited)


Comma delimited format or comma separated
values (CSV)


Fixed format


File formats specific to individual
application software packages (e.g., .xls,
.dbf, .mdb)

4

Why Databases?


One program and its own data sets


Data redundancy, not efficient way of
handling data sets


Lack of data integrity (accurate, consistent,
and up to date), change all data sets if data
need to be changed


Dependency of data on programs, data are
tied to a program.


Data sets shared by many programs


databases

5

Types of Databases


Hierarchical database: tree structure,
Root
-
parent
-
child levels records, one
-
to
-
many relationship


Network database: each child record can
have more than on parent record.


Relational database: key field (identifier) to
link tables (relations)


Object
-
oriented databases: objects (data
and methods)

6

Database Management


Failure in database results in big loss


Data center and data grid (Indiana Univ.)


Database administrator (DBA)


Database management systems


Protect from natural disaster, misuse, or
illegal access (inside and outside)

7

Database Schema 1


Database structure (e.g., tables, views,
procedures, functions) described by a
formal language in DBMS.


Defined in data dictionary in general.


Internal schema consists of physical
schema and logical schema


Physical schema describe how data are
actually stored on storage unit.

8

Database Schema 2


Logical schema, depending on types of
databases, describes the way data are
represented. Tables, records, & fields


Conceptual schemas (external schema)
describe views of users (semantics) in an
organizations not database design.


Many conceptual schemas can exist
depending on purposes (users)

9

Relational Database 1


Relational database is a collection of tables.


A
table

(relation) consists of
record
s (tuples,
entities, or rows), each of which consists of a list
of related
field
s (attributes or columns).


Field


Record


Table (relation)


Database













































n
k
record
record
record
field
field
field
Table
...
..........
2
1
2
1
10


11

Relational Database 2


Attribute name: short, meaningful,
lowercase without special characters and
reserved words


Alias names

12

Relational Database
3


Attribute types: numbers is preferred


Character (string), variable character


Integer (tiny, small): 1
-
2 bytes


Float and double precession: 4
-
8 bytes


Date/time


Logical or boolean (binary)


Text (memo)


Image/video/audio, object, etc

13

Relational Database
4


Length of an attribute is determined by
types of data, and maximum length of data
& systems.


Allowable values and ranges (picture
clause) are considered to improve data
quality and integrity

14

Relational Database
5


Primary key consists of one of more
attributes whose values uniquely identify a
record in a table. No duplicate is allowed.


Normalization simplifies a database to
make it compliant with the concept of the
normal form.


Eliminate duplicate and/or abnormal
records

15

Relational Database
6


Joining combines two or more tables
(relations)


Inner (intersection of A and B) versus
outer join (union of A and B)


Left (first table as a reference) versus right
(second as reference) join


Left outer join of A and B: Include all
entities in A and entities in B matched to
any entity in A.

16

Relational Database 7


17

E
-
R Model and Diagram 1


Entity
-
relationship model (ERM), a modeling DB
method, produces types of conceptual schema
in a relational database.


ER diagrams (ERD) are used to represent the
abstract and conceptual relationships.


http://en.wikipedia.org/wiki/Entity
-
relationship_model


Entity; attributes of an entity; relationships show
how entities are related.


ERD and CASE tools are very useful in system
design and development stage, facilitating
communications among stakeholders.

18

E
-
R Model and Diagram 2


An e
ntity

is represented by a box (rectangular)
and a
relationships

among entities by a
diamond
.


Degree of a relationship is the umber of entity
types that participate in that relationship. Unary
(recursive), binary, ternary relationships.


Cardinality of a relationship is the number of
instances of entity B that can be associated with
each instance of entity A.


Minimum and maximum cardinalities: O, |, or ||
on the line linking an entity and a relationship.

19


20


21

E
-
R Diagram (CaseStudio 2)


22


23

Database Management System


Database management system (DBMS)
controls the structure of a database and
access to the data. RDBMS (Relational)
and OODBMS (Object
-
oriented)


Reduce data redundancy


Improve data integrity and program
independence


how? database schemas


Increase user productivity and security
(only if well managed and maintained)

24

DBMS Software

& Services


Server


Oracle
,
Sybase


DB2
, Informix

(IBM)


SQL Server (Microsoft)


Open source:
MySql, PostgreSQL


Client


dBase III, IV, (Visual) FoxPro


Paradox

(Corel),
Access (Microsoft)


Database as a Service (DaaS)

25

DBMS Components


Data dictionary descries the structure of
data in databases such as name, type,
length, access control.


Utilities to create, modify, delete tables,
records, and fields.


Query languages manipulate databases,
structured query language (SQL)


Report generator


Access security, and system recovery

26

Database Applications


Distributed databases


Objective
-
oriented databases


Datawarehousing


Online transaction processing (OLTP)


Online analytical processing (OLAP)


Data mining, business intelligence (BI)


27

Considerations for Managers


Data dictionary and E
-
R modeling


Attributes for the public sector


Performance measures


Data security: minimize use of private
information when designing conceptual
schemas, each department and
application has its own schemas


Database administrator’s role in ensuring
security in response to evolving threats.

28


29

SQL Examples 1


CREATE DATABASE sales


CREATE TABLE customer…


SELECT id, name FROM customer


SELECT * FROM customer WHERE …


INSERT INTO customer VALUES (…


UPDATE customer SET name=…


DELETE FROM customer WHERE…

30

SQL Examples 2

CREATE TABLE bbs (



sn int(5) not null auto_increment,



no int(5) default 0 not null,



board char(10),



author varchar(20) not null,



email varchar(25),



subject varchar(60) not null,



content text not null,



passwd char(10) not null,



ip char(15),



hit int(5) default 0 not null,



primary key (sn) );