Ch 1: Your Future and Computer Competency Content

1
3 / 12
CHAPTER
Databases
MIS105 Lec15 Irfan Ahmed Ilyas
2
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.

3
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.

4
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

5
..Introducing Microsoft Access
Database Operators/ Designers/ Programmers
Microsoft Jet Engine
Microsoft Access User Interface
Data Definition Module
Table Object
Database Creation Module
Query Object
Database Application
Data Manipulation/ Retrieval Module
Macros/ VBA Modules
SQL Command Interface
Form Object
Report Object
Database Security Module
Page Object
End User
DBA
Database.mdb
Access Database File
6
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

7
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

8
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)
9
..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 gt 8Kb

10
..Basic Data Types in DBMS

• Number
• 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 215 through 215-1 (2 bytes)
• Number/ Long Integer- Integer (whole number)
  data from 231 through 231-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
  -263 through 263-1
• int - Integer (whole number) data from -231
  through 231 - 1
• smallint - Integer data from 215 through
  215 - 1
• tinyint -Integer data from 0 through 255.
• decimal - Fixed precision and scale numeric
  data from -1038 1 through 1038 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.

11
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
  120000 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

12
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.

13
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 231 - 1 (2,147,483,647) bytes.

14
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

15
..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.

16
..Data Definition Details

• Microsoft Access includes (covered in lab
  practices)

17
..Data Definition Details

• Microsoft Access includes (covered in lab
  practices)

18
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

19
.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.

20
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.

21
. 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

22
(No Transcript)