11 3 / 12 chapter databases mis105 lec15 irfan ahmed ilyas

1111

3 3 / / 12123 3 / / 1212CH

AP

TE

R

Databases

MIS105

Lec15

Irfan Ahmed Ilyas

02/3/2002 Prepared By: Irfan Ilyas 2

1111

Lecture Objectives

• DBMS Main Modules• Some example DBMS packages• Introducing Microsoft Access• Details of DBMS Interface Objects• Table Object Functionalities

– Data Definition– Data Manipulation

• Data Type Details– Character/ Number/ DateTime/ YesNo etc.


1111

DBMS Examples

• Most widely used DBMS packages are– Microsoft Access from Microsoft Corporation.

– Corel Paradox from Corel

– Lotus Approach from Lotus(IBM)

– DB2 from IBM

– Oracle 8 Personal Edition from Oracle Software

– SQL Anywhere Professional from Sybase

– SQL Server from Microsoft Corporation.


1111

Introducing Microsoft Access

• Two main program components– Microsoft Jet Engine

• Responsible for managing the data in the database file

• Controls the structure/ physical details of data on the disk

– User Interface Objects • Provide an easy to use view to the stored data• Forwards the user demands to the Jet Engine in

form of SQL commands


1111

Microsoft Jet Engine

..Introducing Microsoft Access

Access Database File

Data Definition Module

Data Manipulation/ Retrieval Module

Database Creation Module

Database Security Module

Datab

ase A

pp

lication

Database Operators/ Designers/ Programmers

Database.mdb

Microsoft Access User Interface

Table Object

Query Object

Form Object

Report Object

Page Object

Macros/ V

BA

Mod

ules

SQ

L C

omm

and

Interface

End User DBA


1111

Details of DBMS Interface Objects

• All DBMS packages allow the use of SQL commands for database interactions

• Most of the commercially available DBMS packages allow database interactions with graphical interface objects like– Table – Query– Form– Report


1111

…DBMS Interface Objects

• Table Object– A table object enables the users to

• #1: define and create a data file structure inside the database

• #2: populate/ delete/ modify data records in a particular data file

• #3: Analyzing data records using tools like pivot table and pivot chart

• Example: Table object in Microsoft Access


1111

#1.Table object for Data Definition• Table object provides data definition through a design

screen (Microsoft Access: Table Design View)• Data Definition results in Record Structure Definition for

a particular data file (table)• The definition includes

• Name of each field • Data type for each field• Primary key specification• Size of the field (in bytes)• Field displaying format• Field Input Mask• Field entry essentiality• Field default value• Field validation rule• Field indexing

Access Table Design Screen (A STUDENT table is shown here)


1111

..Data Definition Details• Field name

– Every field is assigned with a name– Every DBMS has its own Naming Rules in terms of length, valid characters

usage etc.

• Field Data type– It specifies the type of data values which a particular field will be accepting– Most basic types (available in almost all DBMSes) are

• Character– Text or combinations of text and numbers, as well as numbers that don't require

calculations, such as phone numbers– Microsoft Access

» Text – max size= 255 characters (unused characters are not allocated)» Memo – max size= 65,536 characters (unused characters are not allocated)

– Microsoft SQL Server» char –fixed size = 8KB (unused characters will be allocated)» varchar – variable size, 8Kb max (unused characters are not allocated) » text – sizes > 8Kb


1111

..Basic Data Types in DBMSNumber

• Numeric data used in mathematical calculations. • The max/ min value depends upon the field size allocated.

Microsoft Access• Number/ Byte - Integer (whole number, only positive) data from 0 through 255 (1 byte)• Number/ Integer - Integer (whole number) data from 2^15 through 2^15-1 (2 bytes) • Number/ Long Integer- Integer (whole number) data from 2^31 through 2^31-1 (4 bytes) • Number/ Single –Real (with decimal) data with 7 digit precision (4 bytes)• Number/ Double -Real (with decimal) data with 15 digit precision (8 bytes)Microsoft SQL Server• bigint - Integer (whole number) data from -2^63 through 2^63-1 • int - Integer (whole number) data from -2^31 through 2^31 - 1 • smallint - Integer data from 2^15 through 2^15 - 1• tinyint -Integer data from 0 through 255.• decimal - Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1. • float - Floating precision number data from -1.79E + 308 through 1.79E + 308.• real- Floating precision number data from -3.40E + 38 through 3.40E + 38.


1111

…Basic data types in DBMSes• Date/ Time

– For saving date & time values.– Microsoft Access

• Date/ Time - Date and time values for the years 100 through 9999. (8 bytes) (Example: 1/1/100 12:00:00 PM)

– Microsoft SQL Server• datetime - Date and time data from January 1, 1753, through

December 31, 9999• smalldatetime -Date and time data from January 1, 1900,

through June 6, 2079


1111

…Basic data types in DBMSes

• Yes/ No– To save any one of two possible values.– Microsoft Access

• Yes/No - fields that contain only one of two values (Yes/No, True/False, or On/Off). [1 bit]

– Microsoft SQL Server• bit - Integer data with either a 1 or 0 value.


1111

…Basic data types in DBMSes• Binary Data

– To save huge binary data/ computer files.

– Microsoft Access• OLE Object (Up to 1 giga-byte or disk space available)

– An object (such as a Microsoft Excel spreadsheet, a Microsoft Word document, graphics, sounds, or other binary data)

– Can be linked to a computer file or embedded as a separate object

– Microsoft SQL Server• binary - Fixed-length binary data with a maximum length of 8,000 bytes.• varbinary - Variable-length binary data with a maximum length of 8,000

bytes.• image - Variable-length binary data with a maximum length of 2^31 - 1

(2,147,483,647) bytes.


1111

…Basic data types in DBMSes

• Hyperlink– To save a link to an object on web (URL) or network

(UNC)– Microsoft Access

• Hyperlink – can save http and UNC addresses• Example:

– http://cim-irfan/lecture.htm– \\cim-irfan\mis105\quiz-1.doc

– Microsoft SQL Server• Not available


1111

..Data Definition Details• Primary key specification

– To specify the key field for a particular table

– This declaration results in• Field data to be essentially required

• Field data uniqueness

• Field indexing (for fast searching)

• Example (Microsoft Access)– Entering the same IdNumber (PK in the table) more than once.

Unacceptable Record


1111

..Data Definition Details

• Microsoft Access includes (covered in lab practices)Field Property DescriptionFormat Governs the display of dates and numbers.

Provides a help in data entry by using visual symbols/ blocking un-needed data type.Different input mask codes are used to specify the input requirement. (User F1 for details)

CaptionAppears for the name of the field when creating forms or reports on this particular table.

The value specified will be inserted automatically into the field when a new record is being entered. Saves typing for frequently appearing values.Can be over-written if not required.

Input Mask

Default Value


1111

..Data Definition Details

• …Microsoft Access includes (covered in lab practices)Field Property Description

Validation RuleConditional expression for verifying the data validation during data entry.

Validation TextThe text that is displayed if the Validation Rule is broken.

RequiredIf Yes, users are not allowed to leave the field empty during data entry.

Allow Zero Length Space can be entered for Text/ Memo data.For speed up the searching on the basis of a particular field values.Disadvantage is the slowness in the data entry while arranging records physically on disk.

Indexed


1111

#2.Table object for Data Manipulations

• Table object provides data manipulations through a data entry screen (Microsoft Access: Table Datasheet View)

• Data manipulation involves the following activities– Adding new records

– Deleting existing records

– Modifying existing records

– Searching/ Replacing record values

– Filtering data records


1111

….Table object for Data Manipulations

• Issues related to data manipulation– Indexed fields

• Depending the number of indexed fields, the data entry could be slower

– Abiding Data Integrity Constraints • Data values (entered by the user) must conform to certain

constraints• These constraints are derived from the meaning or semantics

of the data and its environment.• Implementing these constraints results in increased degree of

data validity throughout the database.


1111

#3: Table object for Data Analysis

• Table object provides data analysis feature by making use of pivot table/ chart creations (Microsoft Access: Table Pivot Table View)

• With a pivot table– Users can do different statistical operations like

average, max, min etc. on some specific field value

– The operations can be categorized on the basis of one or more fields.


1111

…. Table object for Data Analysis

• For example,– In a table saving data about

student scores

– A pivot table showing score averages while classifying the records with student majors

– Same table having a data classification on student major and student class basis

ID# MAJOR CLASS SCORE12 MIS SO 1513 ICS FR 1414 MIS FR 1215 COE SO 1016 ICS FR 9

MAJOR AVG-SCOREMIS 13.5ICS 11.5COE 10

MAJORFR SO

MIS 12 15ICS 11.5COE 10

CLASS


1111

11 3 / 12 chapter databases mis105 lec15 irfan ahmed ilyas

Documents