MS Office Access Microsoft Access Ease of use

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 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. 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 files 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. Data Independence

Attributes 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, ...... HEAD_OF:

Relationships: 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 N 1 module M N

enrols 1 M teaches 1 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

Recently Viewed Presentations

  • Prezentace aplikace PowerPoint - Masaryk University

    Prezentace aplikace PowerPoint - Masaryk University

    Kinanthropology. Sports facilities. Courses in English for exchange students. Sociology of Sports. 2D and 3D Motion Analyses. Multimedia in Sport. Case Studies in Sport Management. Management in Sport. First Aid. Applied Sport Nutrition. Physiologyof Sport and Exercise.
  • Mesoamerica: Aztec Empire - Coyne: World History

    Mesoamerica: Aztec Empire - Coyne: World History

    Chac - god of rain, thunder, and lightning. Both adored and feared by the Maya He was portrayed as both a benefactor and a violent warrior. Religion: Major Gods cont. Gukumatz - Feathered snake god Taught the Maya the arts...
  • Archaea and Eubacteria Kingdoms

    Archaea and Eubacteria Kingdoms

    ARCHAEA VS. EUBACTERIA. Prokaryotes were originally under one domain: Bacteria. They originally were categorized under the Monera kingdom. However, in 1977, Carl Woese, a microbiologist and physicist discovered significant differences between some of the prokaryotes.
  • LEGAL STUDIES Unit 3  Overview towards the Exam

    LEGAL STUDIES Unit 3 Overview towards the Exam

    UNIT 3 - ANOVERVIEW. At least 35 - 40 marks of the70 • 2011, 2012, 2014 = 10 markquestion. Look carefully at the study design - key skills give a good indication of the knowledge you need to focus on!
  • Henri Matisse - Weebly

    Henri Matisse - Weebly

    Piet Mondrian Composition, 1921 Piet Mondrian Composition with Red, Yellow, Blue, and Black, 1921 Piet Mondrian Tableau No. 1, 1921 Piet Mondrian Composition, 1922 Piet Mondrian Composition II, 1922 Piet Mondrian Composition with Red, Yellow, and Blue, 1928 Piet Mondrian...
  • Vocabulary Week 16

    Vocabulary Week 16

    Please take care with your education and prepare for the future so that your prospects of getting a good job aren't bleak. Please guess the definition… lacking in charm or comfort, empty or dreary. bleak is an adjective. synonyms: desolate,...
  • Physicians Assistant Staffing and Best Practice Analysis in

    Physicians Assistant Staffing and Best Practice Analysis in

    Observations. Surveys. Beeper Study. ... Prior IOE 481 Projects. PA/NP Utilization Project in the Cancer Center. PA Utilization Project in Radiation Oncology. Neuro Sleep PA Utilization study. Government resources on wRVUdata. Published literature. ... PowerPoint Presentation
  • Office of Pipeline Safety Pipeline and Hazardous Materials

    Office of Pipeline Safety Pipeline and Hazardous Materials

    Julie Halliday: 202-389-2039 . Deployment Criteria. A release of product and one or more of the following: Death Personal injury necessitating hospitalization Property damage exceeding $500K Hazardous liquid spill of 500 or more barrels