Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Database Fundamentals

No description
by

ELCC Branch

on 5 June 2014

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Database Fundamentals

Database Fundamentals
data vs. information (vs. knowledge)
This is an extremely important but often overlooked distinction...
First things first...
The first thing you should do is define a
purpose statement
for the database. Ask yourselves who will use the database and how they'll use it, and make sure the final purpose statement addresses all the different needs...
All the data that's fit to keep...
what is a 'database'?
A database (DB) is any organized collection of data. While we often assume only complex, computer-based environments are DBs, DBs are actually quite common and integral to modern living.

Chances are you've already used a DB today!

Here are a few examples...

a telephone book
papers / files in a filing cabinet
an airline reservation system



AGENDA
What is a database (DB)?
Understand the difference between data & information
The purpose of a DB system
First steps in DB development
Options & considerations for selecting a DB system
DB definitions & 'building blocks'
Quality control & data entry considerations
Demonstration: Access DB for entry / storage / reporting
why do we need databases?
Basically to keep records of things and track our activities or interventions with those things...to make sense of the world
Options & considerations 2
Selecting a DBMS
The ultimate purpose?
In a word, the ultimate purpose of a database is
transformation
...
Options & considerations 3
While a needs analysis process will be specific to every organization, at minimum it should answer the following questions:

How many records will be warehoused and for how long?
Who will be using the DBMS and what tasks will they perform?
How often is data modified? Who makes the modifications?
Who will provide IT support for the DBs?
What hardware is available? Is there a budget for additional hardware if required?
Who is responsible for maintaining the data?
Will DB access be required at multiple sites (i.e. network)? If so, what level of access is required? (i.e. read-only or full write privileges?)
Restaurant menus
Your glove compartment (i.e. car registration papers)
the TV Guide
Data is...
Essentially, data is
unprocessed
information
defined in many ways and appearing in multiple forms, including both symbolic and categorical systems (i.e numeric, alphabetic, alphameric, nominal, ordinal, interval, ratio & ad hoc)
VS.
Information is...
Data that has been organized and communicated in a coherent, meaningful way
Converted data becomes information, which is conjoined to create...
Knowledge
, or information that has been organized and evaluated so it can be used purposefully.
Client database
marketing research
sales data / reports
inventory management
Student database
longitudinal tracking (i.e. research)
assessment records
enrolment reports
Data
Information
Knowledge
CHANGE
Generally speaking, DBMSs can be divided into two types:
1. Desktop DBMS - are orientated towards single-user applications and "live" on standard personal computers, hence the term 'desktop DBMS'

2. Server DBMS - include mechanisms to ensure reliability and consistency of data and are geared towards multi-user applications. These DBMSs "live" on servers and access is shared between multiple desktop computers linked through a network.
Ask
WHO
,
WHAT
,
WHEN
,
WHERE
,
WHY
&
HOW
...
Sample Pupose Statement
The ______ database will store information about________ _______________________. The DB will also provide data entry forms for use by _____________ and reports for use by ________________.
Options & considerations 1
Getting specific:
Technically, any organized collection of data is a database. But going forward, we're actually talking about a database management system (DBMS), which is a little different...

A DBMS (like Access, Oracle and SQL Server) provides users with tools to organize their data in highly
flexible
ways.

This
flexibility
allows users of DBMSs to add, modify or delete elements from their database, ask the database questions (queries), and tell the database to summarize data in certain ways (reports).
A good database design helps prevent data duplication. It also helps ensure data is complete, and more importantly, that it is accurate.
Start by listing the data you want to capture. A good place to start is with your existing data. Chances are you are already capturing the data you want, it's just likely not integrated together in a flexible way (i.e. multiple spreadsheets)
One technique is to use existing paper forms to begin to plan the DB you imagine.
Do a 'Needs Analysis'
Why Access?
Access
is included with the
Microsoft Office Professional
packages, therefore no additional software is required if your organization purchases computers with
Office
already installed.
Microsoft is not going away.
Access
likely to be available and supported for years to come.
Since
Access
is the most widely used desktop DBMS in the world, there are also more support and development consultants for it than for any similar product.
Significantly less costly to implement and maintain compared to
Oracle
or
SQL Server.
Fairly complex DBs can be set-up and running in half the time (and half the cost) of other large DBs. The simpler the database, the greater the cost advantage.
Integrates well with other
Office
elements, and other software makers are more likely to provide interfaces to
MS Access
than competitors.
When designed correctly,
Access
DBs can be ported to
SQL Server
or
Oracle
. This is important if you want to start small or develop a pilot database system and then migrate to the larger database management systems as your needs change.
Why NOT
Access
?
You need to store huge amounts of data in single tables (i.e. bigger than 2GBs or roughly 3-5 million records)
You would regularly have more than 15 people actively using the database at the same time
Your database servers experience periodic crashes. In this case, you'll have problems with any server-based or network DBs.
You'll be sharing the database on mixed OS platforms (i.e. some running Windows 98, ME, 2000, XP, etc.)
DB definitions & 'building blocks'
Some definitions:
A field: a singular category of information (i.e. First Name, Last Name, Address, School, Grade level, etc.)
A record: one individual piece of data (i.e. one student's information, one domain of an assessment, etc.)
A table: tables are the fundamental building blocks of DBs. Typically, each column of a table represents a field, and each horizontal row represents a record.
Definitions & 'building blocks' 2
Specifically in Access...
Tables
- all data in Access is stored in tables. When you create them, Access asks you to define fields and give each a unique name. Fields can also be told what type of data they will store (i.e. numeric or text).
Queries
- are used to find and undertake operations on the data in your tables. Queries can be used to find, sort, combine, and even edit data in tables.
Forms
- are screens used to display input information. A form can be designed to assist with data entry, and validation rules can be enacted to ensure data is complete and accurate.
Reports
- are to output what forms are to input. Reports allow you to standardize and customize getting information from tables and queries out of the DB.
Quality Control considerations
The saying
"Garbage in, Garbage out"
has never been truer than in referrence to DBs.
Data quality management encompasses two distinct pieces:
1. Quality planning
This really refers to ensuring the database design is well-planned. Will it meet the needs of the imagined purpose for all users? Is it designed in such a way as to reduce the inclusion of redundant or repeated data?
2. Quality control
Most DBMS give designers multiple tools to control the quality of data at the entry point. Examples of these include
required fields
,
validation rules
, and
pull-down menus
. All of these tools are focused on ensuring records are complete, and those who enter data have to make as few subjective decisions as possible (i.e. more subjective decisions = more potential sources of error / inconsistency).
'FRITZ' demonstration DBMS
The mock database I've constructed ('FRITZ') to demonstrate Access is built on two main tables...
A "Schools" table (tblSchools)...
and
A "Assessments" table (tblAssessments)...
The SchoolCode field is the Primary Key field for the 'Schools' table.
This table consists of 3 fields
The 'Schools' table
The 'Assessments' table
The 'Assessments' table consists of 48 fields. 40 of these fields correspond to the domain items of the assessment (A1 - A8, B1-B8, etc.)
Although a student ID was not provided
or listed on the data sheets I used to construct the database, I've created one and use it as the table's Primary Key. Access will not allow duplicates in this field.
We can see that the relationship between this table and the 'Schools' table is created by including the SchoolCode field...
The 'FRITZ' DBMS contains assessment records for 153 students (83 females & 70 males) in 6 schools in 4 regions...
Schools:

Ubasen PS
Rehoboth PS
Welwitschia PS
Maarssen PS
Niitembo JP
Iindangungu CS
Regions:

Kunene

Oshana

Hardap

Erongo
This Access form enables a user to enter assessment data, while minimizing the sources of potential error...
The student ID
field will not allow
duplicate values
Data entry consists
of drop-down options.
In the case of SchoolCode,
a user would see the name of
the school, as well as the Region
when seleting the code...
A similar menu interface
is available for individual
items on the assessment...
And users can easily save and add new records using command buttons
And queries allow the data to be organized, sorted, operated on, and summarized according to different criteria...
Here we can see a query that averages the sum of all 8 domain scores and calculates an average score out of 100 for each school. It also provides the minimum and maximum cumulative student scores for each school.
And here another query shows us average scores and minimum and maximums by region and gender...
Erongo
Average total
Average (of 100)
Male
Female
Male
Female
Hardap
Kunene
Oshana
128.5
135.8
149.6
150.2
146.6
147.2
142.3
138.0
80.3
84.9
93.9
93.5
91.6
92.0
88.9
86.2
And here is a simple report from the
DBMS...

It shows the ID number, school code, region, school name, total score & overall score (of 100) for each record (student) in the DB.

Reports can also be programmed to run in ways that sort the data according to different fields. In this case, the report sorts the records by school.
Here is the output from another report...


This report shows schools' average overall score (out of 100) as well as the lowest and highest score at each school. It also shows the corresponding region.
Questions?
Full transcript