MS Office

1
MS Office

• Access

2
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

3
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)

4
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

5
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

6
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

7
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

8
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.

9
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

10
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

11
Importance of Data

• Accurate and timely data are the backbone of good
  decision making
• A manager must decide on the price if a firms
  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.

12

• 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

13
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

14
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

15
Data Hierarchy

• Good Organisation of data is essential
• Data Hierarchy Chain
• bits
• bytes
• fields
• records
• files
• database

16
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.

17
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

18

• 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

19
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

20

• 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

21

• 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 files format

22

• 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

23
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

24
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

25
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.

26
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.

27
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.

28
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

29
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.

30

• 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

31
Problems of the Traditional Approach

• Data Redundancy
• Updating Difficulties
• Data Dependence
• Data Duplication
• Data Dispersion

32
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

33
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

34
Data Dispersion

• Due to data stored in different
• places
• organisations
• formats
• Difficult for programs to share data
• Hard to tie data together and make
  cross-references

35
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.

36
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

37
(No Transcript)
38
Database Management System (DBMS)

• A DBMS is a collection of programs that enables
  users to create and maintain a database.

39
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

40
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.

41
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

42
Disadvantages

• Privacy and security
• DBMS can restrict access to only authorised users
  with features such as passwords and access codes.

• Complexity
• Need for special training for users
• Substantial conversion effort
• Vulnerability

43
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

44
Models

• The Networked Data Model
• The Hierarchical Data Model
• The Object Orientated Data Model
• The Relational Data Model

45
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

46
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

47
Example
48
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

49
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

50
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

51
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

52
Attributes

• A particular property that describes an entity
• choice is quite arbitrary
• Attributes of a job include
• job code, wage class, job title

53
Relationships

• An association or link between two or more
  entities
• Examples
• Citizenship Person, Country
• Teaches Lecturer, Course
• Offered Semester, Course
• Attends Student, Course

54
Functionality of a Relationship

• Specifies the number of relationship instances
  that an entity can participate in.
• It may be
• one-to-one (11)
• one-to-many (1M)
• many-to-one (M1)
• many-to-many (NM)
• 11 relationship the relationship President_Of
  between the entity types Politician and Country

55
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.

56

• 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.

57
Entity types and their attributes

• DEPARTMENT
• DNAME, LOCATION, FACULTY, ......
• MODULE
• MDL-NUMBER, TITLE, TERM, ......
• STUDENT
• SNUMBER, SNAME, ADDRESS, SEX, DOB, ......
• LECTURER
• LNAME, ROOMNUMBER, PHONE, ......

58
Relationships

• HEAD_OF
• 11 between LECTURER and DEPARTMENT.
• IS_IN
• 1N between DEPARTMENT and LECTURER.
• OFFER
• 1N between DEPARTMENT and MODULE.
• ENROL
• MN between STUDENT and MODULE.
• TEACH
• 1M between LECTURER and MODULE.

59
The ER diagram
60
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

61
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.

62
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

63
Comparison