ie 423 – design of decision support systems data modeling and database development
TRANSCRIPT
By now you should have
Read Chapters 1,2,3, 4, and 5 in Pol and AhujaAlso, 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 problemsThe next step is to turn these models into real databasesRelational 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 definitionDatabase creationDatabase managementDatabase 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 aboutTablesRelationshipsQueriesForms 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 DevelopmentMicrosoft 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 DevelopmentMicrosoft 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 DevelopmentMicrosoft Access – Tables
Switch between Design View and Datasheet View – use View tool
Or View on main menu
DataBase DevelopmentMicrosoft 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 DevelopmentMicrosoft Access – Relationships
This is where we define
One-to-One One-to-Many Many-to-Many Relations that we
discussed under modeling
DataBase DevelopmentMicrosoft Access – Queries
There are several kinds of queries Select Queries Update Queries Insert Queries Delete Queries
DataBase DevelopmentMicrosoft 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 DevelopmentMicrosoft Access – Queries
There are several kinds of queries
Select Queries Update Queries Insert Queries Delete Queries
DataBase DevelopmentMicrosoft 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 DevelopmentMicrosoft Access
Insert Queries – adds instances (records) to a relation or relationship
Add a new product to a databaseAdd a new path to a routing
application
DataBase DevelopmentMicrosoft 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