Transcript Data files

MS Office
Access
Microsoft Access
• Ease of use for beginners
• Allows for advanced databases to be
implemented
• Easy to modify fields and data after data has
been entered
• Creation of Interfaces
• Ease of reporting
Microsoft Access
• A Microsoft Access database consists of a number
of objects of various types. The different types of
object are :
– Tables (These hold the data and are found in all
databases)
– Queries (These are used to combine and select data
held in tables)
– Forms (These can be used for data entry and provide
user interface features)
– Reports (These are used to layout the output from the
database neatly)
– Macros (These define operations to be carried out)
– Modules (These are Visual Basic programs)
Basic Concepts
• Table
– a collection of records related by subject
• Customer List, Product List
• Record
– Information about a single Item
• a particular customer
• Field
– The individual data items held for each Record.
– Further defined by size and type of information
they contain
Tables
• A view of the table is called the Datasheet
• The menu bar and toolbar change to include
options relevant to working with tables
• communication within the table
• To modify or create a table, design view
must be used
Queries
• Provide a way to collect selected
information from the database
• Criteria may be specified to limit the
number of records selected
• can select, summaries, update, delete, make
new tables and append records to another
table
• Can be looked at in datasheet or design
view
• can be created with a wizard
Forms
• provide a friendlier view of the database
• used to build interface to developed
database
• used to display view, edit and print data
• used to include images and drawings
• additional text, colored font
• based on one or more underlying tables or
queries
• Form wizards make basic form design easy
• Can be time consuming to create and amend
What is a Database?
• A database is a computerised record keeping
system, whose overall purpose is to maintain
information and to make that information
available on demand.
• The information concerned could be anything that
is considered to be of significance to the
individual or organisation the system is intended
to serve.
• The database can be of any size and of varying
complexity.
• Simply, a database is a structured method of
storage and retrieval of information.
Examples
•
•
•
•
•
Telephone Directory
Dictionary
Atlas
A Library Index Card
Usually when people refer to databases,
they are referring to those used in business.
–
–
–
–
–
Stock files
Customer records
Supplier files
Accounting ledgers
Personnel records
Importance of information
• Poorly managed and maintained
information can threaten the existence of an
organisation
• Well managed information systems can
provide a significant edge in the
marketplace, e.g.
– target the right customers and identify their
needs
• By using databases effectively, companies
can become more responsive to customer
needs
Importance of Data
• Accurate and timely data are the backbone
of good decision making
– A manager must decide on the price if a firm’s
product, based on cost factors and market
conditions
– a stockbroker must decide, based on investment
data, how and whether to invest
– A banker must decide, based on credit reports,
whether to approve a loan
– A student must decide what university to enroll
in and what classes to take, based on certain
data.
• In all cases, data is the driving force behind
good decisions.
• Therefore, the ability to gather, store,
process, and retrieve data in a timely
manner is vital
• Well managed data can
– save time and money
– increase productivity
– enhance decision making
Data Vs Information
• meaningless data becomes information
when it is processed and presented to the
decision maker in a meaningful way.
• Only when the the input (data) is
accurate,timely, and reliable will the output
(information be useful) and reliable
• Garbage-In-Garbage-Out
Useful Data
• For data to be useful in decision making it
must:
– be accessible to the people who need it;
– be well organised, cross-referenced, and
efficiently managed
– easy to create, update and maintain
Data Hierarchy
• Good Organisation of data is essential
• Data Hierarchy Chain
–
–
–
–
–
–
bits
bytes
fields
records
files
database
Example
• A library has a file of all books it owns.
This file is composed of records, one for
each book. Each record contains the same
four fields: title, author, publisher, and
ISBN number. Each field contains a number
of characters, and each character is
composed of eight bits.
Types of files
• All computers have two basic kinds of files:
– Program files
– Data files
• Program files contain programs of all kinds
ranging from
– system programs e.g. program used to format disks, to
– application programs e.g. wordprocessing program
• Data files are created to store the data that
programs use.
• Most programs store data in a proprietary file
format, a disk-storage format used only by the
company that makes the program. e.g. Word
creates data files in the Word format
• These files can be read by other word processing
programs only if they are equipped with a special
transaction program, called a conversion utility
Data files - Types
• Data files can be grouped according to the
kind of data they contain:–
–
–
–
–
–
Configuration files
Text files
Graphics files
Database files
Sound files
Backup files
• Configuration files
– contain settings or configuration choices that a
program requires in order to run correctly. You
should never alter or delete a configuration file,
particularly one required by a computers
operating system
• Text files
– contain nothing but standard characters (letters,
punctuation marks, numbers, and special
symbols), such as those of the ASCII character
set. Almost any application program can read a
text file
• Graphics files
– contain pictures in a specific graphics format
used for storing digitally encoded pictures.
Common graphics formats include:
– Joint Photographic Experts Group (JPEG)
– Graphics Interchange Format (GIF)
– To read a graphics file, you must use a program
that recognises the file’s format
• Database files
– contain data that has been stored in the proprietary file
format of a database program
• Sound files
– contain digitised sounds, which can be played back if
your computer is equipped for multimedia
• Backup files
– contain copies of essential data
Types of files
• In traditional data processing applications,
data files are categorised according to the
way the application uses them:
–
–
–
–
–
transaction
master
report
output
backup
Transaction File
• This type of file is used to store input data
until it can be processed.
• In batch processing the data is keyed in and
held until an entire batch can be processed
at once
• In real-time processing the data is processed
as soon as it is entered
Master File
• This type of file contains all the current data
relevant to an application.
• For example, a customer master file
contains a record for each customer of a
business. The master file is updated when
new charges and payments are recorded.
Report file
• This type holds a copy of a report in
computer-accessible form until it is
convenient to print it.
• Sometimes it is advantageous to keep
report files instead of paper documents
because the files are easier to create.
Output file
• Some programs create files that will be used as
input to other programs.
• For example, an accounts payable program
accepts a transaction file of all payments made in
the last week, updates the master accounts payable
file, and produces an accounts payable output file.
The accounts payable output file is then used as
input to the general ledger program.
Backup file
• A backup file is a copy of a file, created as a
safety precaution in case anything should
happen to the original. Backing up data files
regularly is extremely important. With any
storage medium, the one thing you can be
certain of is that it will fail
Traditional Approach to File
management
• Data was processed and stored using file
processing systems
• Files where created for each particular
software application
• Each application or department had its own
set of master and transaction files which are
used for storing, processing and retrieving
data.
• Each functional area of an organisation had its
own set of files and programs for manipulating
data
• Although each application data file is divided into
different items, there is no particular
correspondence between the organisation of one
data file to that of another
• The manner in which data are stored (record
structure) cannot be readily altered
Problems of the Traditional
Approach
• Data Redundancy
• Updating Difficulties
• Data Dependence
• Data Duplication
• Data Dispersion
Data Redundancy
• Storing of identical information in multiple
files
• This practice means that the same data is
recorded by more than one application
• Problems
– Wasted storage
– difficulty in updating and maintaining files
– inconsistency of data values
Data Dependence
• Changing the characteristics of the fields within an
established file is often difficult or impossible
• The programs depend, to some extent, on the data
formats and file organisation methods used
• If the format of the data is altered, programs that
use that data have to be altered
• Increases the cost of maintaining the software
Data Dispersion
• Due to data stored in different
– places
– organisations
– formats
• Difficult for programs to share data
• Hard to tie data together and make crossreferences
Benefits of using a database
• Deals with problems of traditional, approach
• The use of a database enforces a consistent
means of entering information.
• A database represents the collective memory of
an institution or an individual.
• A database handles
– large volume of data,
– data from varied sources,
– data that covers a long period of time,
– and many reports available on the same data.
Database Systems (DBS)
• A database system is
– an integrated set of computer hardware,
software, and human user
– a working combination of a database, database
management software, and the people who use
the database
Application
Programs
End
Users
Database Management System
Database
Database System
Overview of a database system
Database Management System
(DBMS)
• A DBMS is a collection of programs that
enables users to create and maintain a
database.
Database management system (DBMS)
• a layer of software between the physical
database itself and the end user/application
programs
• Support programs that enable users to create
and maintain a database
• Main functions are enable user to:–
–
–
–
–
define, create, and organise a database
input data
Process data
maintain data integrity and security
query database
Database Philosophy
• each piece of information be entered and
stored just once
• every authorised user have quick and easy
access to any of the stored data
• data is entered, maintained and accesses in
ways that they are not dependent on any
particular application program.
Attributes
• Data Independence
– all data needed for a number of applications
stored in one general database
– data can be changed without changing each
program that access the database
• Data Integration
– avoidance of data duplication and inconsistency
and enables the shared use of data
• Updatability
– the DBMS allows for the quick and efficient
addition and deletion of data records and
categories
• Privacy and security
– DBMS can restrict access to only authorised
users with features such as passwords and
access codes.
Disadvantages
•
•
•
•
Complexity
Need for special training for users
Substantial conversion effort
Vulnerability
Classification of DBMS
• The main criterion normally used to classify
database management systems is the data
model on which the the DBMS is based
• A data model is a set of concepts that can
be used to describe the structure of the
database
• That is, the way the DBMS
– structures
– organises, and
– manipulates items
Models
•
•
•
•
The Networked Data Model
The Hierarchical Data Model
The Object Orientated Data Model
The Relational Data Model
The Relational Model
• Organises data in terms of two dimensional
tables, each made up of rows and columns
• Rows represent the data records and
columns represent the fields within those
records
• relates data in any one table to data in
another as long as the two tables share at
least one common attribute
The Relational Model
• Relationships between records are implied
by the data values stored in common fields.
• conceptually quite simple
• easily altered to fit new situations and uses
minimal memory
• Implementation of fixed amount of storage
for each field may result in inefficient
storage utilisation
Department Name
Computer Science
Mathematics
Physics
Biology
Lecturer
Name
King, A
Spencer, S
Chang, L
Example
Department
Code
CS01
MA34
PH61
BY05
Lecturer
Number
KA94
SS87
CL72
Department
Head
Bass, J
Jones, T
McLeigh, O
Ross, V
Department
Code
CS01
BY05
MA34
Database Design - Data Modelling
• Databases contain information about objects
that exist in the real world
• The first step in designing a database is to
determine which objects to represent within
the database and which properties to include
• This process is called data modelling
• The purpose of a data model is to create a
logical representation of the data structure
that is used to create a database
Conceptual Data Models
• Models that deal with objects, rather than
the tables created later from the objects
• Provide a concise description of data
requirements of the user
• Does not require implementation details
• Independent of any particular DBMS
• Easy for ordinary users understand
Entity-Relationship (E-R) Model
• Most popular conceptual data model used to
design a database
• Provides a diagrammatic description of the
database
• Represents relationships between objects
and depicts their behaviour
• Composed of Data Entities, Attributes and
Relationships
Entities
• Represents a object or a thing
• May have a physical existence
– student, car, house, employee
• May be an object with conceptual existence
– company, a job or a course
• May be an event
– birth etc..
• In general it is something about which data
is to be gathered
Attributes
• A particular property that describes an
entity
• choice is quite arbitrary
• Attributes of a job include
– job code, wage class, job title
Relationships
• An association or link between two or more
entities
• Examples
–
–
–
–
Citizenship
Teaches
Offered
Attends
Person, Country
Lecturer, Course
Semester, Course
Student, Course
Functionality of a Relationship
• Specifies the number of relationship
instances that an entity can participate in.
• It may be:
–
–
–
–
one-to-one (1:1)
one-to-many (1:M)
many-to-one (M:1)
many-to-many (N:M)
• 1:1 relationship: the relationship
President_Of between the entity types
Politician and Country
A university database:
• The university database maintains records
of its departments, lecturers, course
modules, and students. The requirements
are summarised as follows:
• The university consists of departments.
Each department has a unique name and
some other descriptive attributes.
A
department must also have a number of
lecturers, one of whom is the head of
department.
• All lecturers have different names (we
assume so anyway). They must teach one
or more modules. A lecturer can only
belong to one department.
• Modules are offered by departments and
taught by lecturers. They must also be
attended by some students. Each module
has a unique module number.
• Students must enrol for a number of
modules. Each student is given a unique
student number.
Entity types and their attributes:
• DEPARTMENT:
– DNAME, LOCATION, FACULTY, ......
• MODULE:
– MDL-NUMBER, TITLE, TERM, ......
• STUDENT:
– SNUMBER, SNAME, ADDRESS, SEX, DOB,
......
• LECTURER:
– LNAME, ROOMNUMBER, PHONE, ......
Relationships:
• HEAD_OF:
– 1:1 between LECTURER and DEPARTMENT.
• IS_IN:
– 1:N between DEPARTMENT and LECTURER.
• OFFER:
– 1:N between DEPARTMENT and MODULE.
• ENROL:
– M:N between STUDENT and MODULE.
• TEACH:
– 1:M between LECTURER and MODULE.
The ER diagram:
Department
1
1
offers
head_of
N
is_in
1
module
N
enrols
1
M
M
teaches
1
N
lecturer
student
ER diagram for a university database
Basic Rules
• Make each record unique
– each table should be allocated a primary key.This is
simply a field or combination of feels which uniquely
identifies a record.
• Make each field unique
– do not repeat similar information in a table
• Make fields functionally dependent
– each field within a table must relate to the subject of the
record. If it does not it belongs in another table
Basic Rules
• Ensure data is in its smallest logical parts
– e.g., it may be useful to keep a customers
postcode as a separate field so you can analyse
sales based on postal region.
• Ensure each field is independent
– you should be able to alter any one field in a
record without altering any other.
Why use an electronic Database
–
–
–
–
–
–
–
–
Increased Speed
Easy to use
Store vast amounts of data
allow for easy editing and updating of data
allow for easy sorting of data
allow for easy searching and selection of data
format, arrange and present information
share the information with other software
applications
– Database can be shared on a network. Avoids
duplication of work
Comparison