Database Development in Microsoft Access Lecture 1 Introduction to Databases and DBMS

1
Database Development in Microsoft Access
Lecture 1 Introduction to Databases and DBMS

• Mr. Mengjie Yu
• M.Yu_at_2001.ljmu.ac.uk
• Room 608

2
Lecture objectives

• In this lecture we will learn about
• Concepts of Data, Information, and Knowledge in
  information system
• The reasons of using database approach
• The meaning of the term Database and Database
  Management System (DBMS).
• The separation of data definition from
  applications.
• The development of data models over time and its
  terminology.

3
Information System and Knowledge Management

• Three essential Concepts
• Data
• represents facts or values of results
• Information
• represents related data in a certain way.
• Knowledge
• all the information come together to achieve a
  certain outcome in a particular situation
• Example a bank saving account

4
Information System and Knowledge Management

• Implementation Technologies
• E-mail
• Intranets and Internets network
• Knowledge-based computing systems
• Database / Data warehouse

5
Database Definition

• Database is a collection of information that is
  organised so that it can easily be accessed,
  managed, and updated
• --
  http//www.whatis.com

• Database can refers to
• Database packages (Informix, dBase IV, Access)
• Application database (Our Main Concern)
  

6
Conventional File-Based System
Management
?
Sales
Purchasing
Production
Stock Control
Delivery
Account
Separate Systems For Separate Functions
7
Purpose of Database System

• In the early days, database applications were
  built on top of file systems
• Drawbacks of using file systems to store data
• Data redundancy and inconsistency
• Multiple file formats, duplication of information
  in different files
• Difficulty in accessing data
• Need to write a new program to carry out each new
  task
• Data isolation multiple files and formats
• Integrity problems
• Integrity constraints (e.g. account balance gt 0)
  become part of program code
• Hard to add new constraints or change existing
  ones

8
Purpose of Database System (Cont.)

• Drawbacks of using file systems (cont.)
• Fixed Queries / Proliferation of application
  programs
• Programs are written to satisfy particular
  functions
• Any new requirement needs a new program
• Concurrent access by multiple users
• Concurrent accessed needed for performance
• Uncontrolled concurrent accesses can lead to
  inconsistencies
• E.g. two people reading a balance and updating it
  at the same time
• Security problems
• Database systems offer solutions to all the above
  problems

9
Example of Database Applications

• Purchases from the supermarket
• Purchases using your credit card
• Booking a holiday at the travel agents
• Using the local library
• Taking out insurance
• Using the Internet
• Studying at university

10
Several Terms

• Database
• a collection of related data / information
• Database Management System (DBMS)
• a software that manages and controls access to
  the database
• Database Application
• a program that interacts with database
• Database System
• a collection of application programs

11
Database Management System (DBMS)
Data held in one central system
End-users

• Definition
• A software system that enables end-users to
  define, create, maintain, and control access to
  the database

12
DBMS Definition Language
Sales
Users Programs
Accounts
Database
DBMS
Research
Data Definition Language
The Definition of database usually using Data
Definition Language (DDL), which allows users /
applications to specify the data types and
structures and the constraints on the data to be
store in the database
13
DBMS Query Language
Sales
Users Programs
Accounts
Database
DBMS
Research
Query Language
The Manipulation of data done usually using Data
Manipulation Language (DML), which provides a
general enquiry facility to the database, called
QL
14
Database Management System (DBMS)

• A collection of programs
• A security system
• A concurrency control system
• Allows shared access of the database
• A recovery control system
• A user-accessible catalogue
• contains descriptions of the data

15
Components of the DBMS Environment

• Hardware
• Can range from a PC to a network of computers.
• Software
• DBMS, operating system, network software (if
  necessary) and also the application programs.
• Data
• Used by the organization and a description of
  this data called the schema.
• Procedure
• Instructions and rules that should be applied to
  the design and use of the database and DBMS
• People

16
Roles in the Database Environment

• Data Administrator (DA)
• Database Planning
• Development and maintenance of standards
• Polices and procedures
• Conceptual/logical database design
• Database Administrator (DBA)
• Physical database design and implementation
• Maintenance of the operational system
• Ensuring the performance of the applications for
  users

17
Roles in the Database Environment

• Database Designers
• Logical database designer
• Identifies the data data relationship and data
  constrains in database
• Apply specific business roles of organizations
• Physical database designer
• Decides how the logical database is to be
  implemented
• Select / adopt appropriate implementation
  approach

18
Roles in the Database Environment

• Application Programmers
• Design sufficient functions to manipulate data
• Apply a third-generation programming language
  (VB)
• Apply a fourth-generation programming language
  (SQL)
• VBA (Visual Basic for Application) SQL
• End Users
• Naïve users
• Sophisticated users

19
Data Models

• To design, specify, explain, use, or document a
  database. We need a mental model of the data and
  the database
• Data model
• A collection of tools for describing
• data
• data relationships
• data constraints
• A language for describing the data and the
  database (an abstract or model of a database).

20
(No Transcript)
21
Entity-Relationship Model

• Example of Entity-Relationship Model

22
Database Design

• Conceptual Database Design
• Models problem data independent of DBMS
• Create data modelling from the problem
  specification
• ER-Modelling
• Logical Database Design
• Defines structure of data for DBMS (Schema)
• Test and validate data modelling by use of
  normalization
• Physical Database Design
• Physical file structures generated from schema
• Create relational tables and constrains from the
  information
• presented in the logical data model.

23
Summary of the Lecture

• DBMS is now the underling frame of the
  information system and has fundamentally changed
  the way organisation operate.
• The database approach emerged to resolve the
  problems with the file-based approach. A database
  is a shared collection of logically related data,
  design to meet the information needs of an
  organisation.
• A data model is a collection of concepts that can
  be used to describe a set of data, the operations
  to manipulate the data, and a set of integrity
  rules for the data.
• Database Development Phases

24
Next Lecture

• Conceptual Database Design
• The concepts of conceptual modelling
• The use of high-level conceptual data models to
  support database design
• Entity-Relationship Diagram