Transcript Database Development Concepts

IE 423 – Design of Decision
Support Systems
Data modeling and database
development
By now you should have
Read Chapters 1,2,3, 4, and 5 in Pol
and Ahuja
Also, read chapter 6 in Pol and Ahuja
DataBase Development
We have looked at ways to model data
oriented problems and databases to help
solve these problems
The next step is to turn these models into
real databases
Relational models give us relational
schemas,…
From these we can create the actual data
base
DataBase Development
We are going to skip an important topic
 Normalization
…but, will get to this topic very
soon
DataBase Development
Microsoft Access

Relational Database Management
System – RDBMS
Database
Database
Database
Database
definition
creation
management
manipulation
DataBase Development
Microsoft Access


Same genre of application as Oracle, DB2, MySQL,
PostGres, MS SQLServer…
We use it because…
 Easy to use
 Popular, widely used particularly in academia, small to
medium sized businesses
 Available
 Well integrated with other MS Office applications
 Supports standard data manipulation language

Maybe not “industrial strength” as some
DataBase Development
Microsoft Access
Conceptually, everything we will learn
with access will apply to other RDBMS
systems…
 …but the techniques may be different
 What we will do with Access will be
very migratable to other RDBMSs

DataBase Development
Microsoft Access Architecture
Access database is a collection of
data objects
 In part, these data objects
correspond to the entities/objects
that we discussed in modeling
 Access has a few more that we did
not discuss

DataBase Development
Microsoft Access Architecture

Some primary objects and concepts
that we will be concerned about
 Tables
 Relationships
 Queries
 Forms and Reports
DataBase Development
Microsoft Access –
Work Environment




Getting Started
The task pane
Lets you open an
existing database,
or
Create a new one
Task
Pane
DataBase Development
Microsoft Access –
Work Environment




Object Panel
Shows types of
Objects in
database
Select an object
type (Tables,
Queries, etc.)…
Access will show
you the objects of
that type in the
DB
DataBase Development
Microsoft Access –
Work Environment



Object Panel
Note that we have
the list of existing
tables in the DB,
but …
We also have
three tools for
creating a new
object of that type
(table)
DataBase Development
Microsoft Access – Work Environment

Just like Excel (and Word, and PowerPoint) we usually have
two or three ways to do anything






Menus (File, Edit, Insert, Format)
Toolbars (standard, formatting, web,…)
Tool icons (DesignView, DatasheetView,…)
Key combos
Function keys
You can customize toolbars
 Go to View on menu bar
 Click on Toolbars…, then Customize
 Check each toolbar that you want to be present
DataBase Development
Microsoft Access – Tables


Most databases in Access (and any other RDBMS) has
multiple tables
A table is a RDBMS representation of a relation
 (remember: 2D grid attributes and instances)


Columns are attributes – we will start calling these - fields
Rows are entity instances – we will start calling them
records
DataBase Development
Microsoft Access – Tables



Work directly with tables
in two modes
Datasheet View or Design
View
Datasheet View
 Shows table as a data
grid
 Contains columns
(attributes), and…
 Rows (instances or
records)
 Good for editing,
entering data
 Not so good for creating
or modifying the
structure of the table
DataBase Development
Microsoft Access –
Tables

Design View
 For the creation of
the table
 Or to view the tables
definition
 Two part window


Top – field
definitions
Bottom – field
properties
DataBase Development
Microsoft Access –
Tables


Switch between Design
View and Datasheet
View – use View tool
Or View on main menu
DataBase Development
Microsoft Access –
Relationships



Defines relationships
(clever name, huh!)
between and among
tables
Should have some
unifying field, a field in
common in tables being
related
This is how Access will
find related information
DataBase Development
Microsoft Access –
Relationships

This is where we define
 One-to-One
 One-to-Many
 Many-to-Many
 Relations that we
discussed under
modeling
DataBase Development
Microsoft Access – Queries

There are several kinds of queries
 Select Queries
 Update Queries
 Insert Queries
 Delete Queries
DataBase Development
Microsoft Access – Queries

Select Queries –
 Select data from other data objects
 These objects can be other tables
 Can be multiple tables
 Can be from other queries
 Can be from combination of tables and queries
DataBase Development
Microsoft Access –
Queries

There are several kinds
of queries
 Select Queries
 Update Queries
 Insert Queries
 Delete Queries
DataBase Development
Microsoft Access
 Select Queries – Select or pull a subset of
data from a dataset
 Subset may be from a single relation or
from a relationship
DataBase Development
Microsoft Access
 Insert Queries – adds instances (records)
to a relation or relationship
 Add a new product to a database
 Add a new path to a routing application
DataBase Development
Microsoft Access
 Append Queries – add sets of data to
other sets of data
 Add this week’s new transaction to
master archive
 Add a set of system access records to a
master log database