B. Information Technology Hons. CMPB245: Database Design

1
B. Information Technology (Hons.)CMPB245Databas
e Design

• Introduction to
• Databases

2
Objectives

• Describe common uses of databases
• Identify characteristics and problems of
  file-based system
• Define database and DBMS
• Explain the functions of a DBMS
• List the major components of a DBMS
• List the advantages and disadvantages of a DBMS

3
Introduction

• Have been in use for more than 20 years
• In the US, it fuelled the IT industry 10 billion
  p.a.
• Results in advancement in
• communication systems
• transportation and logistics
• financial management
• access to literature, etc.

4
Introduction

• The most important development in soft-ware
  engineering
• Underlying framework of the information system
• Changes the way organization operate and business
  is done

5
Simple Definitions

• Database
• A collection of related data
• Database Management System, or DBMS
• The software that manages and controls ac-cess to
  the database

6
DatabasesCommon Uses

• Objective 1

7
Common Uses of Databases

• Purchase of goods and groceries from Parkson
  Grand
• Use of bar-coding system
• Use your credit card
• Checks for credit limit
• Not lost or stolen card
• Monthly statements

8
Common Uses of Databases

• When you borrow some books from the Library
• When you enrol in a University
• When you insure your car and renew road tax
• What else?

9
File-based SystemCharacteristics and Problems

• Objective 2

10
Traditional File-based System

• Manual file-based system
• Folders
• Filing cabinets
• Computerised file-based system
• A collection of program that perform services for
  the end users
• Each software manages its own data

11
File-based SystemExample

• Sales Dept. keeps records of Property, Owner and
  Renter
• Contracts Dept. keeps records of Property, Lease
  and Renter
• There is data duplication
• Data maintenance is difficult

12
File-based SystemLimitations

• Separation and isolation of data
• Difficult to access and cross-reference
• Duplication of data
• Sales Dept. holds the same data as the Con-tracts
  Dept.
• Costs time and money and occupies extra space
• Loss of data integrity - data not consistent

13
File-based SystemLimitations

• Data dependence
• Program has to be written to conform to the
  structure of data
• Incompatible file format
• Fixed queries/proliferation of application
  programs

14
File-based SystemLimitations

• File-based limitations are caused by
• Data definition is embedded in the program
• Data access and manipulation is controlled only
  by the application prog-ram

15
Database and Database Management System
Definitions

• Objective 3

16
Database ApproachDefinitions

• Database
• A shared collection of logically related data
  designed to meet the information needs of an
  organization
• A collection of data, a structure, and prog-rams
  that can manage and manipulate the data and its
  structure

17
Database ApproachDefinitions

• Database
• An organized collection of related data
• A file or files which store raw data, i.e. data
  prior to processing

18
Simple DatabasesExamples

• Business Card holder
• Dictionary
• Directory
• Diary
• What else?

19
Data and Information

• What is data?
• The raw material that makes up an infor-mation
• What is information?
• The aggregation of data which makes sense and
  from which a decision could be inferred

20
Data and InformationImportance

• Why is it important to manage data and
  information?
• They have strategic values
• What do we mean by that?
• Something has a strategic value if it brings
  op-portunities, profits, and provide an edge over
  competitors

21
Database ApproachThe Concept

• A single large repository of data
• Data structure is defined once
• Data is shared within an organization
• Data is integrated with minimum duplica-tion

22
Database ApproachThe Concept

• Data is separated from application prog-rams
• There exist relationships between entities in a
  database

23
Database ApproachEntity Attribute

• An entity is something which has infor-mation,
  e.g. Staff, Department, etc.
• Each entity has an attribute which stores data
  about the entity, e.g. Staff No., Dept. Name,
  etc.

24
Database ApproachEntity Relationship
Dept. Entity
Dept.
1
A Staff is assigned to one Dept.
Has
A Dept. can have many Staffs
Relationship
M
Staff
Staff Entity
25
Database Management System (DBMS)

• A software system that
• enables users to define, create and maintain the
  database
• provides controlled access to this database

26
Database Management System (DBMS)

• A software that helps organize data in a way that
  allows fast and easy access to the data
• A system in which data is collected and processed
  to produce information which helps in
  decision-making

27
Database Management SystemMain Functions

• Objective 4

28
Main Function (1)

• Defines a database through a Data Defi-nition
  Language
• Defines the types of data
• Name of data or attributes
• Size of each data

29
Main Function (1) Types of Data

• Character - numbers, letters, etc.
• e.g. Name, Address
• Numeric - signed/unsigned numbers
• Date
• Logical - such as Yes/No, Male/Female
• Text or Memo (long characters)
• Object, e.g. pictures, drawings, etc

30
Main Function (2)

• Add, Update, Delete and Retrieve data through a
  Data Manipulation Language

31
Main FunctionsData Manipulation Language

• Two types of DML
• Procedural - programs, macros
• How the output is to be obtained?
• Non-procedural - query, SQL
• What data is to be obtained?

32
Main Function (3)

• Provide controlled access to the data
• Security system - prevents unauthorised ac-cess
• Data integrity - maintains consistency of stored
  data
• Concurrency control - allows shared access
• Recovery control - returns to previous state
• Catalogs, descriptions of data

33
Main Function (4)

• Provides view mechanism which allows end users to
  have their own view of data
• As a level of security to exclude data some users
  should not see
• To customize appearance of the database
• Present a consistent picture of the database even
  if there are changes

34
Main Functions
Data Definition Language
Data Manipulation Language
Procedural Programs
Non-Procedural Query
View
Database
DBMS Software
35
Database Management SystemMain Components

• Objective 5

36
DBMS Components

• Main components are
• Hardware
• Software
• Data
• Procedure
• People
• See pg. 19, Fig. 1.8

37
DBMS Components
Data
Hardware
Software
People
Procedure
38
DBMS Components Hardware

• Ranges from Personal Computers to Main-frames -
  depends on requirement
• Network using Client-Server Architecture
• Database Servers
• See page 20, Fig. 1-9

39
DBMS Components Software

• Consists of the DBMS itself written in 3GL
• Operating System and Network
• Some systems are written in 4GL
• Report generators
• Application generators
• Form generators

40
DBMS Components Data

• A bridge between the machine and the human
  component
• Operating data and metadata
• Schemas
• Relations or tables
• Entities
• Attributes

41
DBMS ComponentsA Database Table
Attributes
Records
A Database Table
42
DBMS Components Procedures

• Rules and instructions that dictates the use of
  the database
• Logging on
• Use of DBMS functions
• Backing up files
• Handle hardware/software failures
• Maintain files and databases

43
DBMS Components People

• Data Database Administrators
• Database Designers
• Application Programmers
• End-users
• Naïve users
• Sophisticated users

44
DBMS Components Data Administrator

• Data Administrator - responsible for the
  management of data resource
• Database planning
• Development
• Maintenance of standards
• Policies and procedures
• Logical database design

45
DBMS Components Database Administrator

• Database Administrator - responsible for the
  physical realization of the DBMS
• Physical design and implementation
• Security and integrity control
• Maintenance of the system
• Ensure satisfactory performance for users
• More technically-oriented than DA

46
DBMS Components Database Designers

• Logical database designers
• Identifies data (entities, attributes)
• Spot relationships and constraints of data to be
  stored
• Understands the organizations data and busi-ness
  rules and procedures
• What is needed to implement

47
DBMS Components Database Designers

• Physical database designer - decides how the
  database is to be realized
• Creating tables and constraints
• Define storage structures and access methods
• Adopting security measures
• Computing machinery specifications
• How to implement

48
DBMS Components Programmers

• Code the DBMS software from specifica-tion
  produced by the Logical Designers or Systems
  Analyst
• Standard functions
• Maintenance functions
• Reports
• Use 3GL or 4GL

49
DBMS Components End Users

• Naïve users - the DBMS is transparent to them
• Data entry clerks
• Perform simple operations
• Sophisticated users know the system
• Often try to fool the system
• Use SQL to retrieve data
• Write their own application programs

50
Database Systems Advantages Disadvantages

• Objective 6

51
Advantages

• Control of data redundancy
• Data consistency
• More information from the same amount of data
• Sharing of data
• Improved data integrity

52
Advantages

• Improved security
• Enforcement of standards
• Economy of scale
• Balanced conflicting requirements
• Improved data accessibility and responsi-veness

53
Advantages

• Increased productivity
• Improved maintenance through data inde-pendence
• Increased backup and recovery services

54
Disadvantages

• Complex piece of software
• Large software size require megabytes of disk
  space and internal memory
• High costs, additional hardware costs and cost of
  conversion
• Reduced performance in some applications
• Higher impact of failure

55
When Not to Use a DBMS

• The database and applications are simple,
  well-defined, and not expected to change
• Multiple-user access to data is not required
• Why?
• Overhead cost of DBMS is high due to
• High initial investment in hardware,
• software, and training
• Overhead for providing security, concurrency
  control, recovery.