8/9/2019 DSS & MIS 06 - DBMS

1/18

6. DBMS

Sources of data, Data file environment,

Database environment, Data models,Relevance of relational database

design in DSS

8/9/2019 DSS & MIS 06 - DBMS

2/18

Datasource

Datasource is a name given to theconnection set up to a database froma server.

A DataSource object is therepresentation of a data source inthe Java programming language. In

basic terms, a data source is afacility for storing data.

8/9/2019 DSS & MIS 06 - DBMS

3/18

Sources of Data

From another Database or storage location

From Web/ Network/ User/Groups

8/9/2019 DSS & MIS 06 - DBMS

4/18

Data File Environment

Storing and organizing files on HardDisk.

Low file Integrity.

Both Logically and Physicallyconnected files.

Less secured. Low sharing integrity.

8/9/2019 DSS & MIS 06 - DBMS

5/18

Database Environment

Storing and organizing files in aDatabase.

High file Integrity.

Logically connected Data Files,Physically scattered.

Very secure due to access rights. Highly sharable.

8/9/2019 DSS & MIS 06 - DBMS

6/18

Data Models

Flat model

Hierarchical model

Network model

Relational model

8/9/2019 DSS & MIS 06 - DBMS

7/18

Data Models

Object Relational model

Concept Oriented

Star Schema

http://en.wikipedia.org/wiki/File:Star-schema.pnghttp://en.wikipedia.org/wiki/File:Company_codm.gif

8/9/2019 DSS & MIS 06 - DBMS

8/18

ACID Property of a Database

Atomicity All-or-nothing rule

Consistency Referential Integrity

Isolation Concurrent transactions Durability Survival to system failure

8/9/2019 DSS & MIS 06 - DBMS

9/18

DBMS Concepts

8/9/2019 DSS & MIS 06 - DBMS

10/18

Keys in DBMS

Primary key

Foreign Key

Composite key Candidate key

Alternate Key

Secondary key

8/9/2019 DSS & MIS 06 - DBMS

11/18

DBMS Terminologies

Database

SQL

Data Warehouse Data marts

Entities

Relationships Attributes

8/9/2019 DSS & MIS 06 - DBMS

12/18

Relevance of RelationalDesign in DSS

Multidimensional problem solving

Critical queries

Referentially integrated inputs Data warehousing support

Data mart support

Sharability and scalability ofinformation

8/9/2019 DSS & MIS 06 - DBMS

13/18

DATABASENORMALIZATION

1NF, 2NF, 3NF, BCNF

8/9/2019 DSS & MIS 06 - DBMS

14/18

Normalization

ECODE DEPT DEPTHE

AD

PROJCOD

E

HOURS

E101 Systems E901 P27

P51P20

90

10160

E305 Sales E906 P27

P22

109

98

E508 Admin E908 P51

P27

NULL

72

8/9/2019 DSS & MIS 06 - DBMS

15/18

1NF

A table is said to be in 1NF if eachcell of the table contains preciselyone value.ECODE DEPT DEPTHEA

D

PROJCODE HOURS

E101 Systems E901 P27 90

E101 Systems E901 P51 101

E101 Systems E901 P20 60

E305 Sales E906 P27 109

E305 Sales E906 P22 98

E508 Admin E908 P51 NULL

E508 Admin E908 P27 72

8/9/2019 DSS & MIS 06 - DBMS

16/18

2NF

Every attribute in the row isfunctionally dependent on the wholekey.ECODE DEPT DEPTHEAD

E101 Systems E901

E305 Sales E906

E508 Admin E908

ECODE PROJCODE HOURS

E101 P27 90

E101 P51 101

E101 P20 60

E305 P27 109

E305 P22 98

E508 P51 NULL

E508 P27 72

8/9/2019 DSS & MIS 06 - DBMS

17/18

8/9/2019 DSS & MIS 06 - DBMS

18/18

BCNF

ECODE NAME PROJCODE HOURS

E1 Veronica P2 48

E2 Anthony P5 100

E3 Mac P6 15

E4 Susan P2 250

E4 Susan P5 75E1 Veronica P5 40

ECODE NAME

E1 Veronica

E2 AnthonyE3 Mac

E4 Susan

ECODE PROJCODEHOURS

E1 P2 48E2 P5 100

E3 P6 15

E4 P2 250

E4 P5 75

E1 P5 40