程序代写代做代考 database ER INFO20003 Database Systems

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