Transcript Introduction to Data Base Management Systems and MS Access

IE 423 – Design of Decision
Support Systems
Introduction to Data Base
Management Systems and MS
Access
MS Access
Access is a DataBase Management
System (DBMS)

It is conceptually similar to other DBMSs
 Like MySQL, Postgress, Oracle, DB2
At times Access will look like Excel…
…but it is not the same…
…in many important ways
It meets very different application
needs
2
Consider thisImagine that you live in a dorm..
You want to keep track of who has what music
So, if you wanted to borrow one or more pieces of music you would know where to go
You create a stack of note cards where each card contains Id and location information people
on your floor
You create another (bigger) stack of note cards where each one contains info on a specific
song and who owns it
How would you find out who has “Angel” by Sarah McLachlan and how would you find out
how to contact that person?
How would you find all Raggae music on your floor?
Name: Bob
Name:
Sara
Room:
214
Room:
210
Name:
Alan
Phone:
555-1212
Name:
Mary
Phone:
555-1345
Room:
219
IM:
BadBob
Room:
236
Name:
Bill
IM:
Tallsara
Phone:
555-1277
Phone:
555-1234
Room:
255
IM:
insomniac
IM:
ProudMary
Phone:
555-1212
IM: Billygoat
Song: Margaritaville
Artist: Jimmy Buffet
Genre: Pop
Owner: Bill
3
Consider thisWhat are some characteristics of our two
datasets?
Name: Bob
Name:
Sara
Room:
214
Room:
210
Name:
Alan
Phone:
555-1212
Name:
Mary
Phone:
555-1345
Room:
219
IM:
BadBob
Room:
236
Name:
Bill
IM:
Tallsara
Phone:
555-1277
Phone:
555-1234
Room:
255
IM:
insomniac
IM:
ProudMary
Phone:
555-1212
IM: Billygoat
Song: Margaritaville
Artist: Jimmy Buffet
Genre: Pop
Owner: Bill
4
DBMS
So, some problems for data-based
decision support are database problems
…and are best addressed by a data
base management system (DBMS)
So, what do we mean by “database”
DBMS
A Data Base Management System
(DBMS) is a system for keeping,
organizing and enabling the retrieval of
information
An OS file system is a DBMS (by this
definition)
A DBMS has
tools to support the use of data in
tables
DBMS
A DBMS is a software system for
developing, organizing, managing and using
databases
A database is an organized collection of
information




The
The
The
The
data in a database is related in someway
data in a database is structured
term “organized” is loosely defined
term “structured” is loosely defined
8
MS Access
Remember our notecards –






Each notecard represents an entity – in this case a
person or a piece of music
In database parlance this is called a “record”
A record contains one or more discrete pieces of
information on a specific entity
These discrete pieces of information (even if they
are blank) are called “fields”
A group of fields organized together about one
entity makes up a record
A table has a field that makes each record unique –
primary key
9
MS Access - Concepts
Database concepts


A collection of records organized together and
having the same set of fields is a “table”
Depending on how you view it (there are several
ways) a table looks a lot like a worksheet in Excel
 It has rows (records) that represent entities
 It has columns (fields) that represent pieces of
information about those entities

A group of tables on a related topic and stored in
one file is a database
10
MS Access - Concepts
A database has other objects as well





Queries – tools to search for data
Forms – a designed screen for entering,
managing or correcting data in a database
Reports – a tools for organizing and presenting
database contents or results in a useful or
intelligible form
Macros – stored shortcuts of steps that you use
in Access
Modules – stored programs for automating
functionality in Access
11
RDBMS
Relational Data Base Management
System or RDBMS –


A system for keeping, organizing and
enabling the retrieval of data, where…
data is stored in tables made up of
 rows (records),… and
 columns (fields)
DBMS vs Spreadsheet
Programs
DBMS





Large amounts of
data
Relatively static data
Focus on retrieval,
organization and
presentation of data
Good multi-user
support
Good support for
complex relations
among datasets
Spreadsheet





Limited amount of
data
Dynamic data
Focus on modifying
data
Little or no multiuser support
Limited support for
complex relations
among datasets
Some common DBMSs
MySQL
Postgres
Oracle
DB2
MS SQL Server
MS Access
Some common DBMSs
The various DBMS package may look
different,…
…they have different tools and
interfaces,…
…have different capacities and
performance levels,…
…and run on different platforms, but…
Under the hood they are the same, at
least in principle
MS Access
A RDBMS produced by Microsoft
Runs on MS Window
Meant for non-enterprise level
applications, but…
MS Access
Feature
Database size
Objects in db
No. of concurrent users
Fields in table (record)
Open tables (concurrent)
Memo field max size
Relationships in query
Tables in a query
Limit
2 Gigabytes
32,768
255
255
2048 (approx)
65,535
32 per table
32
MS Access
Major Features

Database
 Tables
 Queries
 Forms
 Reports
 Macros
 Modules
MS Access
Tables


one or more tables per DB (254)
can be related
Queries


views of data from tables
single or multiple tables
Forms



on screen displays for data from tables or queries
single records, multiple records
from single or multiple tables
MS Access
Reports



for printed output
complex presentations of data
single or multiple tables
Data Access Pages

web access to DB
Macros and VB modules

programmed responses in DB
MS Access
Extensive design features
Fairly complex
Security control
MS Access
Scalability to Enterprise level system



Access is fairly substantial
Access Projects – Access as a front-end to
SQL Server DB
links to other systems
MS Access – Memory vs
Storage
Excel, Word and lot of other applications do there
work in memory
Your spreadsheet or Word document is held in
memory while you work
Your changes, edits or new information is not
committed to your spreadsheet or document until
you do a save
Not so in Access
While you working on an Access database that
database in on a storage device – disk, USB drive,
etc.
Any changes you make to a record or a table are
immediately committed to the database file
23
MS Access – Memory vs
Storage
One exception – if you currently editing a
record those changes only exist in memory,
not in the database.





You know this by the little pencil icon on the left
side of the row that you are editing
While you are in this state you can Undo
When you move to another row you commit the
changes in the previously edited row
And you can no longer undo them
Remember the changes you make are being
posted to the database file as you work…. Not
just when you save or exit
24
MS Access – Memory vs
Storage
Access files/databases can be shared

Multiple people can be using them at the
same time
Make your only copy of any database
that you using for this class
Change the database file name to have
your name as a prefix
Store it on a device where you can use it,
edit it…
25