IT_Chapter_11B

Database Management Systems

• Database management system (DBMS)

• Store large collections of data

• Organize the data

• Becomes a data storage system

The Database

• Stores a collection of related items

• Collection is arranged in a structure

– Organizes and describes the data

• Often includes helper documents

• Two different types

• Fields

– Hold an individual piece of data

– Are named descriptively

– Often called a column

– Phone book examples

• Name, address, e-mail, phone number

– Fields may contain no data

• Records

– One full set of fields

– Often called a row

– Phone book example

• Smith, Joe, 123 Some Street, 412-555-7777

– Databases may have unlimited rows

• Tables

– One complete collection of records

– Databases may have thousands of tables

Database Helper Documents

• Forms

– Present one record to the user

– Often used to change or view data

• Reports

– Produce printed results from the database

– Includes tools to summarize data

Flat-file Databases

• Typically has only one table

– If multiple, each has a separate file

• Useful for simple data storage needs

• Hard to manage large data needs

• Can waste disk space

Relational Databases

• Made of two or more tables

• Tables are related by a common field

– Called a relationship or join

– Can help organize data

• Most common form of database

• Maintaining data is easier than flat-file

• No wasted disk space

The DBMS

• Programs that control the database

• Allows

– Entering data

– Querying data

– Printing reports

• Supports thousands of users

• Includes tools to protect the data

Working with a Database

• Creating tables

– List the necessary fields

– Steps to define a field

• Descriptively name the field

• Specify the field type

• Determine the field size

• Field types

– Describes the type of data stored

– Most DBMS use the same types

• Text fields store letters and numbers

• Numeric field store numbers

• Date and time field

• Logical field stores yes or no

• Binary field stores images or sounds

• Counter field generates sequential numbers

• Memo fields store large amounts of data

• Entering data into a table

– Users type data into a field

– Data must be entered accurately

• Constraints help to verify data

– Forms are typically used for data entry

• Viewing records

– Datasheet view shows all records

– Filters can limit the records shown

• Display only records matching a criteria

– Forms allow viewing one record

• Sorting records

– Order records based on a field

– Multiple sub sorts resolve ‘ties’

– Several types of sorts

• Alphabetic

• Numeric

• Chronological

• Ascending

• Descending

• Querying a database

– Statement that describes desired data

– List of fields can be modified

– Uses of querying

• Find data

• Calculate values per record

• Delete records

– Most important DBMS skill

• Query languages

– All DBMS use a query language

• Most DBMS modify the language

– Structured Query Language (SQL)

• Most common query language

– xBase

• Query language for dBase systems

– Query by example (QBE)

• Interface to SQL or xBase

• Interactive query design

• SQL

Select FirstName, LastName, Phone

From tblPhoneNumbers

Where LastName=“Norton”;

• xBase

Use tblPhoneNumbers

List FirstName, LastName, Phone

For LastName=“Norton”

• Generating reports

– Printed information extracted from
a database

– Can calculate data

• Calculate data per row

• Calculate for entire table

– Pictures and formatting can be included