Databases Management Systems

CIS105

Brain Teaser…

Draw a pyramid using 10 dots (see next slide) After moving to the next slide, don’t click until you are ready to have the answer revealed.

Invert the pyramid so that the peak is at the bottom by moving as few dots as possible.

2

3

Topics

     Explain the differences between File Management Systems and Database Management Systems Describe the concept of data integrity Describe the functions of a database management system Describe some key items to consider when designing and building a database Compare information systems such as TPS, MIS, DSS, and EIS 4

File Management System (FMS)

Using the operating system’s features to manage files and data (ie: Windows Explorer).

5

File Management System - limitations

User manually creates folders and files.

User needs to remember what data is stored in each file.

The operating system does NOT know which files are related to each other.

Will require programming to create relationships and random access.

Ad-hoc queries are almost impossible – need to write a program.

Not too efficient.

6

Data Organization and Access Methods for FMS

Sequential File Organization  Records are stored in the order they were entered.

  If a particular record is desired, all prior records must be read first.

Tape storage uses sequential organization Random File Organization   Go directly to desired record by using a key.

Organization may be...

   Direct File Organization Indexed File Organization (see next slides).

7

Direct File Organization

Must be created using a programming language and must be supported by the operating system.

Location on storage device is usually determine by hashing (randomizing algorithm).

8

Indexed File Organization

Combines elements of sequential and direct methods.

Records stored sequentially, but file contains an index for direct access.

Index is sorted and contains record key.

Data is directly accessed using the key.

May have more than one index (Customer Id, Last Name).

More efficient to use a database management system that incorporates all of these features...

9

Database Management Systems (DBMS)

Software that allows us to     Create a database Maintain data (add, change, delete) Query data (retrieve and sort) Create forms and reports  Access is the software used for this class Relational products are known as Relational Database Management Systems (RDBMS).

Now most DBMS products are relational, so they may be referred to as DBMS or RBDMS.

10

Relational Model

1969, Dr. Edgar Codd developed the Relational Model of Data.

Application of math theory.

Relational databases became popular because they:    can be “easily” modified and expanded.

relationships between tables can “easily” be defined.

data can “easily” be queried 11

Microsoft Access is a RDBMS

Database - a collection of related tables and objects Table – a collection of records for a single subject Query – selects data based on selection criteria Form – simplifies data entry by displaying one record at a time Report – formatted information suitable for distribution (read-only) 12

Hierarchy of Data (DBMS)

Data is organized in layers, and each higher level of data consists of one or more items from the lower level.

File – contains a collection of related tables  Tables – a collection of records (rows) for a single subject  Record (rows) – a group of related fields that describe a person, place, or thing  Fields (columns) – store characters for specific item (acct #,name, address) 

Database Design

Databases must be designed and developed before data can be entered.

During design consider the needs:   What data needs to be collected?

What reports will be needed?

  What queries you may want to make?

We usually begin with the end in mind (Covey).

 If we know the required output, we can identify the required input and processing (computations).

Much time should be spent in the design phase, before anything is developed. It will save time and money.

During the design phase, and ERD is developed… 14

Entity Relationship Diagram (ERD) See next slide… 15

Entity Relationship Diagram (ERD)

Databases designs are depicted in an ERD.

ERD shows:  Tables      Fields (columns) Relationships Primary keys – uniquely identifies record (Id) Foreign keys – used to link from one table back to the primary key of another table.

Index – a field that sorted to facilitate fast searches (Name) The data types of the fields will need to be determined before building (next slide).

16

Data Types

The data type of each field determines what kind of data can be entered into the field.

17

Eliminate Data Redundancy during Design 18

Data Management

Data Management – the procedures used to acquire, access, and maintain data.

Data will be used to provide information that is used to make decisions.

Data integrity – the degree to which data is accurate and reliable.

Defining validation rules increases data integrity (see next slide).

19

Data Validation

Data entered into the felds can be checked by the DBMS by specifying Validation Rules  Alphabetic or numeric check    Completeness check Range and consistency check Required entry 20

Data Maintenance

Three basic operations   Entering new data Updating existing data  Some systems record data before and after change  Deleting data  May need to archive data before removing  Some systems set record to inactive instead of removing it These operations can be perform via  Datasheet View   Forms Custom Programming Datasheet Form 21

Structured Query Language (SQL)

Pronounced by saying the 3 letters S-Q-L, or as see-quell.

SQL is a language included in a RDBMS, and it is used to maintain (insert, update, delete) and query (select) a database.

There is a basic set of SQL commands that are common in all RDBMS, but many vendors then extend the language.

Because of SQL, 3 develop products that will work with many different database such as Crystal Reports (reporting tool) by Business Objects.

Such reporting tools use the data dictionary to get to the data.

rd party companies can 22

Database Query

A query is a request for specific data from the database.

The query would list the columns you want to display. The records display may be limited by entering selection criteria (select all Freshmen).

A Wildcard is a special character (* or ?) inserted in the selection criteria and it used to search for a pattern of characters. For example: lastName Like “Sm*” would select all last names that begin with Sm: Smitts, Smith, etc. Common queries can be saved for future use or to use it to create a report or mailing labels.

The data selected by the query is not saved with the query – many confuse a query with a table.

A query is dynamic – each time a query is executed, it is processed against the data currently stored in the database, so the results are always up-to-date. Query-by-Example (QBE) uses a graphical interface or a form to generate the SQL for users.

23

Mail Merge

A Mail merge is when a form letter and a list of names and addresses are used to create personalized letters.

Form Letter in Word or an Access Report Names and Addresses Personalized letter for each individual in the database.

24

DBMS Key Features

Reduces data redundancy Query capabilities Security – can be set at the table and field levels.

  Read-only privileges Update privileges Backup & recovery tools Concurrent access to data (multi-user) Data dictionary (next slide) 25

Data Dictionary

Data Dictionary contains data or documentation about the tables and fields included in the database.

The data dictionary is automatically created as the tables and columns are defined and documented.

Contains tables names, field names, field sizes, validation rules, input masks, default values, table relationships, etc.

Used by DBMS to automatically generate forms, reports, queries, and SQL based tools.

26

Object-Oriented Databases

Databases now need to be able to store:   Images Video   Audio Other complex formats For example  X-rays, MRI   Audio and video segments Geographical Information Systems (GIS) maps 27

Examples of DBMS Products

Corporate  Oracle     MySql Informix IBM DB2 Microsoft SqlServer Personal  MS Access     MS FoxPro Filemaker Pro (Mac & Win) Paradox Approach Free versions of Oracle, MySql, and MS SqlServer are available via download.

28

Database Administrator (DBA)

Coordinate the creation, maintenance and use of the database.

Database Design and redesign Security administration Performance monitoring Backup and recovery Other duties as assigned.

29

Databases for Information Systems

Information Systems are built with a database at the core.

Information Systems may include:  Operational or Transaction Processing Systems (TPS)     Management Information Systems (MIS) Decision Support Systems (DDS) Executive Information Systems (EIS) See next few slides for details… 30

Operational or Transaction Processing Systems (TPS) Operational systems are designed to support the day-to-day transactions of a business – sales, orders, purchasing, accounts payable, payroll, etc.

Details of all transactions are captured and saved in a database.

The detail is then manipulated and summarized for other systems ( see next few slides ).

31

Management Information Systems

(MIS) MIS is designed to provide accurate, timely, and useful information

from the operational systems

.

Managers received reports as scheduled ( daily, weekly, monthly, etc .) A MIS manager runs the MIS department   Also called Chief Information Officer (CIO) Must be comfortable with computer technology and the organization’s business MIS still exist, but has expanded to…  Decision Support Systems (DSS) – support managers in non-routine decision-making tasks   Executive Information Systems (EIS) – support executives in decisions that affect the entire organization EIS includes information from outside the organization such as from competitors, market, and government 32

Differences Between MIS and DSS (or EIS)

MIS emphasizes planned reports, so reports are standard, structured, scheduled, and routine.

Generating ad-hoc reports is constrained by the design of the operational system.

DSS is designed to support ad-hoc queries, so it is a separate stand-alone system, but most of the data comes from the operational system and other sources.

DSS focuses on supporting decision making 33

Informatics

Informatics – the study of how information is gathered, stored, manipulated, accessed, transferred, secured, given meaning, and presented.

Biomedical Informatics (BMI) – is an emerging discipline that combines the health science knowledge including medicine, dentistry, pharmacy, nursing, radiology and biological sciences with computer science, mathematics, statistics, engineering, information technologies and management.

Good field for those interested in medicine and technology. ASU – School of Computing and Informatics has a program: bmi.asu.edu/index.php

34

MCC’s Database Courses

CIS117DM – Access Level I CIS217AM – Access Level II CIS119DO – Oracle CIS276DA – MySql CIS276DB – MS SqlServer 35

Access Introduction/Overview

Access (application) File: databaseName.accdb

Table A file contains database Objects and the actual data.

Definitions Actual Data Forms Reports Database Dictionary Queries 36