INFO20003 Database Systems
INFO20003 Database Systems 1
INFO20003 Database Systems
Lecture 02
Database Development Process
Semester 2 2018, Week 1
Dr Renata Borovica-Gajic
INFO20003 Database Systems 2
Student representatives
On LMS: Staff Information
Student representatives:
1. Chia Jun Eo: eoc@student.unimelb.edu.au
2. Mayank Tomar: mtomar@student.unimelb.edu.au
3. Omar Ma: omar.ma@student.unimelb.edu.au
© University of Melbourne 2018
INFO20003 Database Systems 3
Coverage
© University of Melbourne 2018
• How database applications are developed
– The development lifecycle
– Focus on database design
• Conceptual design
• Logical design
• Physical design
INFO20003 Database Systems 4
Database Development Lifecycle
Database Planning
Systems Definition
Requirements Definition
and Analysis
Data Conversion and
Loading
Implementation
Application Design
Testing
Operational
Maintenance
Design
Physical Design
Logical Design
Conceptual Design
• Planning how to do the
project.
• How does the
enterprise work
• Enterprise data
model
• Outside scope of the
course
© University of Melbourne 2018
INFO20003 Database Systems 5
Example Enterprise Data Model –
Investment Banking
• A top level perspective
on the data requirements
• Each box (subject area)
would have a data model
© University of Melbourne 2018
INFO20003 Database Systems 6
Database Development Lifecycle
• Specifying scope and
boundaries
• Users
• Application areas
• How does the system
interfere with other
organisational systems
• Outside scope of the
course (slightly)
Database Planning
Systems Definition
Requirements Definition
and Analysis
Data Conversion and
Loading
Implementation
Application Design
Testing
Operational
Maintenance
Design
Physical Design
Logical Design
Conceptual Design
© University of Melbourne 2018
INFO20003 Database Systems 7
Database Development Lifecycle
• Collection and analysis
of requirements for the
new system
• You will be given the
requirements, but you
will need to understand
these!
• You may need to ask
requirement questions
about what you are given
(for the assignment you
state your assumptions)
Database Planning
Systems Definition
Requirements
Definition and Analysis
Data Conversion and
Loading
Implementation
Application Design
Testing
Operational
Maintenance
Design
Physical Design
Logical Design
Conceptual Design
© University of Melbourne 2018
INFO20003 Database Systems 8
Database Development Lifecycle
• Construction of a model
of the data used in the
database – independent
of all physical
considerations
• Data Models
• ER Diagrams
Database Planning
Systems Definition
Requirements Definition
and Analysis
Data Conversion and
Loading
Implementation
Application Design
Testing
Operational
Maintenance
Design
Physical Design
Logical Design
Conceptual Design
© University of Melbourne 2018
INFO20003 Database Systems 9
Analysis of the problem
• An investment bank has a number of branches. Within each branch
a number of departments operate and are structured in a
hierarchical manner. The bank employs around 3000 staff who are
assigned to work in the various departments across the branches.
• We need a database to record staff details including which
department and branch they are assigned…
© University of Melbourne 2018
Business rule
INFO20003 Database Systems 10
Example Conceptual Data Model (ER)
– Investment Banking
© University of Melbourne 2018
INFO20003 Database Systems 11
Database Development Lifecycle
• Construction of a
(relational) model of the
data based on the
conceptual design
• Independent of a
specific database and
other physical
considerations
Database Planning
Systems Definition
Requirements Definition
and Analysis
Data Conversion and
Loading
Implementation
Application Design
Testing
Operational
Maintenance
Design
Physical Design
Logical Design
Conceptual Design
© University of Melbourne 2018
INFO20003 Database Systems 12
Example Logical Data Model –
Investment Banking
Changes from
Conceptual
Model (ER)
© University of Melbourne 2018
INFO20003 Database Systems 13
Example Logical Data Model –
Investment Banking (Complete)
© University of Melbourne 2018
INFO20003 Database Systems 14
Database Development Lifecycle
• A description of the
implementation of the
logical design – for a
specific DBMS.
• Describes:
• Basic relations
(data types)
• File organisation
• Indexes
• Although we don’t do
full physical design, we
discuss some of the
issues (e.g. data types)
Database Planning
Systems Definition
Requirements Definition
and Analysis
Data Conversion and
Loading
Implementation
Application Design
Testing
Operational
Maintenance
Design
Physical Design
Logical Design
Conceptual Design
© University of Melbourne 2018
INFO20003 Database Systems 15
Example Physical Model – Investment
Banking (Staff)
© University of Melbourne 2018
INFO20003 Database Systems 16
Choosing Data Types
• Types help the DBMS store and use information efficiently
– Can make assumptions in computation
– Consistency is guaranteed
• Minimise storage space
• Need to consider
– Can you store all possible values
– Can the type you choose support the data manipulation required
• Selection of types may improve data integrity
© University of Melbourne 2018
INFO20003 Database Systems 17
Example of Data Dictionary
• We do the data dictionary as an ongoing process during analysis and
design of the database
• Example of what is required
Key Attribute Data
Type
Not
Null
Unique Description
Type of
key
Is it a
primary
key or a
foreign
key
(leave
blank if
neither)
Name of
Attribute
Data
type of
attribute
If the
field is
required
or is
optional
Must the
value in
the field
be
unique
for that
field
A description of the
attribute giving any
information that could be
useful to the database
designers or to the
application developers.
This would include things
like attribute sizes, valid
values for an attribute,
information about coding
for this attribute etc.
© University of Melbourne 2018
INFO20003 Database Systems 18
Example of Partial Data Dictionary
Key Attribute Data
Type
Not
Null
Unique Description
PK StaffID Integer Y Y ID number of the staff member, should be 5 in
length. This is the primary identifier (key) of the
table.
FirstName VarChar The first given name of the staff member, up to
100 characters.
LastName VarChar Y The family name of the staff member, up to 100
characters. This must exist for every staff
member
Gender ENUM Y The gender of the staff member, valid values are
only “Male” or “Female” (???). An enumerated
data type should be used if possible. This should
be limited in applications using this field also.
DateOfBirth DateTime Y This is when the staff member was born. Needs
dd/mm/yyyy format.
…
© University of Melbourne 2018
INFO20003 Database Systems 19
MYSQL Data Types (some)
• Character Types
– CHAR(M): A fixed-length string, right-padded with spaces. The range of M is
0 to 255.
– VARCHAR(M): A variable-length string. The range of M is 1 to 65535. (its 255
max. in MySQL 4).
– BIT, BOOL, CHAR: CHAR(1).
– BLOB, TEXT: up to 65535 bytes (for blob) or characters (for text).
– ENUM (‘value1’,’value’,…) up to 65,535 members.
– SET (‘value1’,’value2’, …) up to 64 members.
• Integer Types
– TINYINT[(M)]: Signed (-128 to 127) Unsigned(0 to 255)
– SMALLINT[(M)]: Signed (-32768 to 32767) Unsigned (0 to 65535)
– MEDIUMINT[(M)]: Signed (-8388608 to 8388607) Unsigned (0 to 16777215)
– INT[(M)] / INTEGER[(M)]: Signed (-2147483648 to 2147483647) Unsigned (0
to 4294967295)
– BIGINT[(M)]:Signed(-9223372036854775808 to 9223372036854775807)
Unsigned(0 to 18,446,744,073,709,551,615)
© University of Melbourne 2018
INFO20003 Database Systems 20
MYSQL Data Types (some)
• Real Types
– FLOAT[(M,D)]: single-precision, allowable values: –
3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to
3.402823466E+38. M = display width, D = number of decimals.
– DOUBLE[(M,D)] / REAL[(M,D)]: double-precision, allowable values: –
1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and
2.2250738585072014E-308 to 1.7976931348623157E+308.
– DECIMAL[(M[,D])]: fixed-point type. An unpacked floating-point
number. Stored as string. Good for MONEY!
• Time and Date Types
– DATE 1000-01-01 to 9999-12-31
– TIME -838:59:59 to 838:59:59
– DATETIME 1000-01-01 00:00:00 to 9999-12-31 23:59:59
– TIMESTAMP 1970-01-01 00:00:00 – ~ 2037 Stored in UTC,
converted to local
– YEAR[4] 1901 to 2155 – A useful function in MySQL: NOW();
© University of Melbourne 2018
INFO20003 Database Systems 21
Other Physical Design Decisions
• How to store “Look Up”
– Trade off between speed and space (and possibly integrity of
data)
• Data field integrity (ensure fields only contain correct data)
• Handling missing data (concept of NULL data)
Versus
© University of Melbourne 2018
INFO20003 Database Systems 22
Other Physical Design Decisions (2)
• To De-Normalise or Not (That is the Question)
– Normalisation
• A formal method used to validate and improve upon the logical
design thus far (which attributes should be grouped together),
before proceeding with the physical design.
• Taught later in the semester
– De-Normalisation
• At physical design time need to decide how to implement the
design – including removing some of the normalisation steps…
• Benefits
• Improved database performance
• Costs
• Wasted storage space
– Data integrity / consistency threats
© University of Melbourne 2018
INFO20003 Database Systems 23
Database Development Lifecycle
• Done in conjunction
with design
• Design of the interface
and application
programs that use and
process the database
Database Planning
Systems Definition
Requirements Definition
and Analysis
Data Conversion and
Loading
Implementation
Application Design
Testing
Operational
Maintenance
Design
Physical Design
Logical Design
Conceptual Design
© University of Melbourne 2018
INFO20003 Database Systems 24
Database Development Lifecycle
• The physical realisation
of the database
• Implementation of the
design
• Some of the things in
implementation are
covered as you will be
implementing database
tables
Database Planning
Systems Definition
Requirements Definition
and Analysis
Data Conversion and
Loading
Implementation
Application Design
Testing
Operational
Maintenance
Design
Physical Design
Logical Design
Conceptual Design
© University of Melbourne 2018
INFO20003 Database Systems 25
Database Development Lifecycle
• Transfer existing data
into the database
• Conversion from old
systems
• Non trivial task
• We give you the data.
In a real world situation
you would have to do
this step – very
carefully, very time
consuming… Lots of
issues around this
Database Planning
Systems Definition
Requirements Definition
and Analysis
Data Conversion and
Loading
Implementation
Application Design
Testing
Operational
Maintenance
Design
Physical Design
Logical Design
Conceptual Design
© University of Melbourne 2018
INFO20003 Database Systems 26
Database Development Lifecycle
• Running the database
to find errors in the
design / setup (both at a
physical level and at a
logical level)
• Other issues also
• Performance
• Robustness
• Recoverability
• Adaptability
• Outside scope of the
course (slightly) – but
you need to test your
solutions
Database Planning
Systems Definition
Requirements Definition
and Analysis
Data Conversion and
Loading
Implementation
Application Design
Testing
Operational
Maintenance
Design
Physical Design
Logical Design
Conceptual Design
© University of Melbourne 2018
INFO20003 Database Systems 27
Database Development Lifecycle
• The process of
monitoring and
maintaining the
database system
following its
commissioning
• Handling new
requirements
• Handling changes to
requirements
• Outside scope of the
course
Database Planning
Systems Definition
Requirements Definition
and Analysis
Data Conversion and
Loading
Implementation
Application Design
Testing
Operational
Maintenance
Design
Physical Design
Logical Design
Conceptual Design
© University of Melbourne 2018
INFO20003 Database Systems 29
Summary
• Discussed the lifecycle of
Database Development
• Showed detail of the
Modelling stages
Database Planning
Systems Definition
Requirements Definition
and Analysis
Data Conversion and
Loading
Implementation
Application Design
Testing
Operational
Maintenance
Design
Physical Design
Logical Design
Conceptual Design
© University of Melbourne 2018
INFO20003 Database Systems 30
What’s Examinable?
• Can you discuss the Database Development Lifecycle?
• What is done at each stage of Design?
© University of Melbourne 2018
INFO20003 Database Systems 31
Next Lecture
• Introduction to Database Design
– Conceptual design (ER diagrams)
© University of Melbourne 2018
INFO20003 Database Systems 32