ITC114 Database Management Systems

1
ITC114 Database Management Systems

• Lecturer John Atkinson
• Location Thurgoona campus
• Contact details
• - 02 6051 9854 (work)
• 0412 516 551 (mobile number)
• jatkinson_at_csu.edu.au
• Consultation times
• Monday 900am - 10.00am Tuesday 1200pm
  1pm Wednesday 900 1000amplus other
  convenient times

2
ITC114 Database Management Systems

• Procedure for ITC114
• 2 hour lecture expected you attend
• 1 hour tutorial compulsory
• 1 hour workshop - compulsory

3
Location of additional support material

• The following site will contain lecture notes
  and workshop informationhttp//csusap.csu.edu.a
  u/jatkinso/itc114/200340/
• Please visit this site on a regular basis

4
Tutorials and Workshops

• You must select ONE tutorial and ONE workshop to
  attend in this subject
• TutorialsTA 100pm 150pm - WednesdayTB
  1.00pm 1.50pm Thursday
• Workshops
• WA 2.00pm 2.50pm WednesdayWB 2.00pm
  2.50pm - ThursdayWC 3.00pm 3.50pm - Thursday

5
Assessment in this subject

• Assignment one due 2 April 2003 20
• Assignment two due 14 May 2003 20
• Examination three hour closed book 50
• Tutorials ongoing 5
• Workshops ongoing 5
• YOU MUST PASS EACH ASSESSMENT ITEM TO OBTAIN AT
  LEAST A PASS IN THIS SUBJECT

6
Subject Outline

• Introduction to database management.
• SQL - theoretical and practical application
• Database modelling
• ERD modeling
• Normalisation
• Database Management
• Further database topics

7

• A general introduction to the field of database
  management .
• Introduce basic terminology
• Describe the advantages / disadvantages of
  database processing
• Briefly explain the characteristics of
  Hierarchical, Network Relational database models

8

• What is data?Known facts that can be recorded
  and that have some implicit meaning.
• What is information?
• What is a requirement of data?
• It is crucial to the organisation.
• Should be able to present it in multiple number
  of formats.
• Allow multiple users to be able to access the
  data simultaneously.
• Hopefully it will result in increased
  productivity.

9

• More specifically information / data must also
  be
• current
• timely
• relevant
• consistent
• presented in a usable form

10

• In the beginning.....
• The Computer file system

Employees
PERSONNEL DEPT
Customers
Sales
SALES DEPT
Inventory
ACCOUNT. DEPT
Accounts
11
Another representation ..
12
Basic file system definitions .....

• Data raw facts
• Field character/s that describe some
  meaningful characteristics e.g. name, age
• Record a logically connected set of fields
• File a collection of related records
  usually on only one topic / area.

13
File, fields and records..
14

• The Computer file system and its problems?
• Data stored in independent, unrelated files on
  disk.
• Sharing, security and integrity of data is very
  difficult to control.
• Data dependence and data redundancy.

15
The problems with using File systems

• traditionally only manual files- making sharing
  of information every difficult.
• islands of automation
• cumbersome to work with - problems
• require substantial programming
• complex system administration.
• security features are complex to program.
• duplication

16
Example of islands of automation .
17
The problems with file systems cont.

• Flat Files
• data stored in the one file - difficult to
  process the data - resulting in
• data redundancy
• data anomalies e.g. same data is stored in
  different ways.

18
What is a database?

• a collection of data this is a simplistic
  definition
• contains information about many kinds of entities
  and the relationships between entities plus the
  associated data.
• includes meta data - data about data
• What is a Database Management System (DBMS)?
• DBMS
• intermediary between the user database
• used to establish, maintain, process a database

19
Typical representation of a DBMS
20
Customer and order processing using a Database
customer application programs
Database (file definitions Customer order Order
data)
DBMS
order process application programs
A software package that is designed to manipulate
the data in a database
21
DBMS
logical I/O request
Definitiontoolssubsystem
logical I/O request
Processing Interfacesubsystem
User or Application Program
logical I/O request
Application Developmentsubsystem
Operating System
DBMS Engine
logical I/O request
Data Administrationsubsystem
Database
logical I/O request
DataDictionarysubsystem
DBMS
22

• DBMS
• A program or collection of software products
  whose functions is/are to manipulate a database
  on behalf of its users.
• Removes the need to issues complex instructions
  to process the data in the database.
• It greatly simplifies the task of manipulating
  and accessing the data in a database.
• Many different commercial DBMS programs exist
  e.g. ORACLE, InterBase, InfoMix etc.
• Mainframe DBMS have been around since the 60s
  and PCs from the mid 80s.

23
Basic database terms ..

• Terminology associated with fundamental database
  terms are
• Entity basically a noun possibly a person,
  place, thing or event.
• Attribute is a property of an entity e.g.
  Stud_Id, Age, Sex address etc
• Relationship the association between the
  entities the relationship is express in terms
  of how two entities are related.

24
Entities and attributes
25
So what is a relationship?
26
(No Transcript)
27
Advantages of a database processing system

• Getting more information from the same amount of
  data
• When all the data for various systems are stored
  in a single database, the information becomes
  available, as well as the process of retrieving
  the information can be quick and easy
• 2. Sharing of data
• Several users can have access to the same piece
  of data.

28
Advantages of a database processing system cont .

• 3. Balancing conflicting requirements
• Database Administration/Administrator (DBA) can
  structure the database in such a way that it
  benefits the entire organization, not just a
  single group greater control is possible ..
• 4. Controlling redundancy
• Duplicating data is called redundancy
• Not longer necessary to duplicate data in
  separate files
• Note redundancy is not totally eliminated.

29
Advantages of a database processing system cont .

• 5. Consistency
• Problems of consistency can be as a result of
  data redundancy
• So by reducing redundancy .. there is much less
  potential for inconsistency using the database
  approach.
• However .. remember that redundancy is not
  totally eliminated in a relational database so we
  may still experience problems with consistency.

30
Advantages of a database processing system cont .

• 6. Integrity
• An integrity constraint is a rule that must be
  followed by data in the database . Examples
• Not allowing a persons age to be lower than zero
• A student can not be added to a subject unless
  they are a valid student.
• 7. Security
• The prevention of access to the database by
  unauthorized users

31
Advantages of a database processing system cont .

• 8. Increasing productivity
• Simple to extract required data gives some
  user independence relieves programmers of
  having to write database access programs.
• 9. Data independence
• A property that allows the structure of a
  database to be changed without the programs that
  access the database having to change

32
(No Transcript)
33
Disadvantages of a database processing system

• 1. DBMS size
• DBMSs are large programs that occupy a large
  amount of disk space as well as internal memory
• 2. DBMS complexity
• May be too complex for the average user .
  resulting in ineffective use of the DBMS
• Critical to have a properly designed database in
  the first place.

34
Disadvantage of a database processing system

• 3. Greater impact of a failure
• A failure on the part of any one user that
  damages the database in some way may affect all
  the other users on the system
• 4. More difficult recovery
• If the database is being updated by a large
  number of users, all updates must be redone since
  the time of its restoration

35
Database Design

• DATABASE DESIGN yields a detailed database
  blueprint. The blueprint contains enough
  information to build the database - it is very
  detailed.
• Database design is a crucial activity
• Modelling resources available to assist the
  design process.
• Good models yield good database designs that
  yield good applications
• Schema - structure of a given database.

36

• Database model a means of representing the data
  structures and the data relationships in a
  database.
• Two main categories of database models
• 1. Conceptual model - the WHAT model. (ERD,
  OOD) logical representation
• 2. Implementation model - the HOW model
  physical representation.
• Main examples include
• - hierarchical model
• - network model
• - relational model
• - object-oriented model

37
Data models

• All models require that the relationships be
  defined before implementation.
• Adding new relationships is more difficult for
  hierarchical and network models than with the
  relational model.
• For hierarchical and network models the
  relationships are expressed in terms of data
  structures this imposes a strict structure on
  the model before implementation.

38

• Hierarchical Database model
• Its structure may be represented by an
  upside-down tree (a hierarchy of segments/nodes)
  to emulate the hierarchical nature of
  organisations
• It is perceived by the users as a collection of
  hierarchies.
• No data duplication as the data is only stored
  once with all references made to the data using
  pointers
• Use two main types of data structures
  recordsand parent-child relationships. based
  on 1M relationships

A
B
C
D
E
H
F
G
39
Hierarchical Database model

• The most common example of this model is the
  Information Management System (IMS) by IBM.
• IMS provides no inbuilt query language a real
  disadvantage.
• DL/1 is the associated data definition and
  manipulation language of IMS

40

• Network Database model
• Also resembles the hierarchical model, but uses
  terms SET, OWNER, MEMBER. Also a member can
  belong to more than one set.
• Supports 1M relationships and MN(when they are
  are converted into 1M)
• Typically such diagram will have arrows to
  represent the relationships
• To locate information youbasically have to
  follow these arrows.
• Most importantnetwork data model is the CODASYL
  DBTG

A
B
C
D
E
H
F
G
41
The Relational Database Model

• Implemented by Relational Database Management
  System. Perceived by user to be a collection of
  tables in which data is stored.
• RELATIONAL data stored in rows and columns within
  a TABLE.
• RDBMS takes care of complex physical details.
• Components of a Relational database
• a. entities
• b. attributes
• c. entity set

42
More definitions ........

• Tables - a group of related entities. Alternate
  names include
• - an entity set
• - a relation
• Tables include
• - rows or tuples or records
• - columns or attributes
• Try not to confuse the relational and the
  traditional file terminologies.

43
Tables - relational links
Primary Key
Name Town Age
Jill Albury 21 Jenny Wodonga
27 Fred Lavington 19
Foreign Key
Order Date Name Amt
A178 29-1-95 Fred 50.43 S213 2-12-95
Jenny 2.34 F767 11-2-96 Fred 99.11
44
Types of data in a table

• a. numeric
• b. character
• c. date (Julian date)
• d. logical
• e. memo columns.

45
Next week

• We will be looking at SQL .