BA370 - Database Concepts Ch 1

BA370 - Database Concepts Ch. 1

Database Fundamentals

Databases: Why do MIS people have to know them?

Most ISs employ a database to collect, manage and access data.

Databases offer attractive means to collect, manage and access data.

Database technology is in (some) flux (relational, object-oriented, spatial, distributed, XML-hierarchical).

Databases and Data Base Management Systems (DBMS) come in various shapes and sizes.

In database applications there is much more than meets the eye.

Database administrator and data(base) modeler are career paths.

Kroenke's approach:

Concepts rather than tools or products: "When concepts and products are taught at the same time..., (we) frequently counfound concepts with product features and functions" (xii).

Essential concepts:

Relational model (information represented using tabular relations).

Structured Query Language (SQL).

Data modeling.

Database design.

Database administration.

Relational and nonrelational information: Figures 1.5 through 1.10:

Student information.

Advisor information.

Student-Advisor relationships.

How to represent?

Nonnormalized list or table (Fig 1.5):

Information repeats.

Repeating information is not guaranteed to be entered identically.

Changes must be made multiple times.

Multiple times changing the same information likely introduces error.

More information may be deleted than intended.

Relationships are implicit (hidden).

Relational model:

Relations expresses things and their characteristics; e.g., a student or an instructor.

Relationships express the association between relations ;e.g., an adviser represents a relationship between a student and an instructor.

Relationships are qualified:
- A student has at most one adviser.
- An adviser advises one or more students.
- Relationships are expressed by one relation referencing another relation; e.g.,
Attributes of the different sides of a relationship are expressed through:

Joining of the tables on the referencing column.

Note that by itself, this model has nothing to do with databases or database software:

Relational model is a logical model.

DataBase Management System (DBMS) manages the relationships between the database and the file system.

By letting the DBMS manage the relationships between the logical and the 'physical' model, users and programmers can abstract from the 'physical' and work with the logical.

Don't have to know how the data are ('physically') stored.

Can use generic tools for accessing data.

Can exchange/buy generic database application programs.

Industrial strength databases functions such as:
- Backup & recovery.
- Rollback / commit.
- Multi user support (e.g., locking).

However:

Most applications of significance imply working with the physical model ==> database administration!!

For example:


create table sensor (
sensor_id                      number(5) NOT NULL  ,
sensor_name                    varchar2(64) NOT NULL  ,
description                    varchar2(80) NULL  ,
brand_name                     varchar2(64) NULL  ,
def_sampling_rate              number(5) NOT NULL  ,
manufacturer                   varchar2(64) NULL  ,
)
pctfree 10
pctused 70
tablespace BLT_data
storage (initial   120k
         next      4k
         pctincrease 25);

Database administration is a profession: Oracle Certified Professional Database Administrator.

Have to live within the paradigm of the relational model; e.g., time and space are difficulti (unwieldy) to represent relationally.

Need good API.

Submit to the whims of the DBMS manufacturer.

Kroenke (p. 13) "A database is a self-describing collection of related records."

Metadata or data dictionary

Who or what directly(!) interacts with a DBMS?

Database administrators.

Database application programmers.

Students of databases.

Application software (Fig 1.25).

How to directly interact with a DBMS?

Query By Example (QBE): pick, click and drag; direct manipulation; e.g., Ms. Access (e.g., Fig. 1.20/1.21).

Structured Query Language (SQL) (e.g., Fig 1.24).

Problem: Why use a language if you can pick, click and drag?