Transcript entities - Pravin Shetty > Resume

Lecture 2
Project Planning and Control
Database Design
Logical Data Modelling
and - Mulga Bill
CSE3180 Summer 2005 Lect 2 / 1
Lecture 2
• Yesterday, we touched on the concept of a Project Plan
• This leads to Project Planning and Control
• So for the next few minutes, we will be overviewing this
important feature of ANY project.
• And then we will move onto Database Design
CSE3180 Summer 2005 Lect 2 / 2
Project Planning
•
•
•
•
•
Planning
Scheduling
Cost Tracking
Resource Management
Reporting
Software Functions
Real time interactive operation
Add / Delete items
Change logic dependencies
Adjust calendar and calendar dependencies
Change resource and availability levels and dates
Mouse driven : Multiple platforms
(Microsoft, MacIntosh)
CSE3180 Summer 2005 Lect 2 / 3
Project Planning and Control
Part of the ‘project’ you will complete will be subject to Project
Planning.
One member of each group will become responsible for
administering the resources - time and personnel skills.
This person will be responsible that the 2 components are
completed in the timescale and that the associated
documents are complete
The following overheads give you some idea of the scope of
this very important component of ANY project
CSE3180 Summer 2005 Lect 2 / 4
Project Planning
1. Project Manager’s Controls
2.
3.
4.
5.
6.
7.
8.
- Schedule
- Costs
- Technical
- Resources
Goals, Objectives, Targets, Disciplines
Development of Project Definition / Scope
Performance Assessment Criteria
Level of Detail in Planning
Feed back and Controls
Replanning and Rescheduling
Budgeting
CSE3180 Summer 2005 Lect 2 / 5
Project Planning - Gannt Chart
Task / Week 1
Analysis
Design
Review
Programming
Implementation
- Plan
- Manuals
System Testing
Review
Acceptance Test
Review Results
Handover
2
3
4
5
6
7
CSE3180 Summer 2005 Lect 2 / 6
PERT Diagram
Get
ingredients
Mix
Ingredients
Pour into
cake pan
Place
cake in
oven
start
Bake
cake
Remove
cake from
oven
Preheat
oven
CSE3180 Summer 2005 Lect 2 / 7
Data Base Design
CSE3180 Summer 2005 Lect 2 / 8
Data Base Design
· Reduce data redundancy.
· Provide stable data structures that can be readily changed
with changing user requirements.
· Allow users to make ad hoc requests for data.
· Maintain complex relationships between data elements.
· Support a large variety of decision needs
Hawryszkiewycz I J (1984) Database Analysis and Design p 2
CSE3180 Summer 2005 Lect 2 / 9
End Product of Data Base Design
A database which will:
• Accurately reflect the ‘real world’ data in all
required aspects
• Be responsive to Management information demands
• Reflect Business Rules and Controls
• Be capable of modification to meet changes
in Management needs
• Be an asset to the Organisation/Enterprise
CSE3180 Summer 2005 Lect 2 / 10
Business Functions and Processes
• Business Functions :
Broad groups of closely related activities and decisions which
contribute to a product or service like cycle. (e.g. planning,
materials management, production planning, quality
assurance).
• Business Processes : Decision related activities which
occur within a function. They are related to management of
people, money, material and information.
Materials Management (Business Function)could be
subdivided into: requirements planning, purchasing, goods
received, material accounting, stockkeeping
Business Processes should reflect related activity groupings
CSE3180 Summer 2005 Lect 2 / 11
Business Activities
Business Activities : Specific operations or transactions
required to carry out a process
Some guidelines:
An activity should produce some clearly defined (identifiable)
result - a product, a decision, a plan ......
An activity has clear boundaries - a clear beginning and end.
Activities do not overlap.
An activity is carried out as a unit, by a single agent or a team
Once initiated, an activity proceeds independently of and from
other activities.
CSE3180 Summer 2005 Lect 2 / 12
Business Entities
Are persons, objects or events about which Information is,
or will be, recorded in the Information Data Base
Many of these Entities can be identified with Business
Activities (e.g. suppliers, purchase orders, customer)
CSE3180 Summer 2005 Lect 2 / 13
Critical Success Factors
* Key factors which must be performed well to ensure the
success of an organisation
* Also known as Key Performance Indicators (KPIs)
e.g. production failure rate < 0.01% of total production units
production cost increases <= c.p.i. increases
customer service complaints < 1% of all customer
transactions (relates to later lecture)
absenteeism < 1% of staff in any 24 hour period
product quality => advertised standards (water, power)
% of students successful in their Degree course
% spread of results - %HD, %D,
%N
CSE3180 Summer 2005 Lect 2 / 14
Design Criteria
- data availability
- data reliability
- data currency
- data consistency
- data flexibility
- data efficiency
Verify these criteria are satisfied via the technical review
CSE3180 Summer 2005 Lect 2 / 15
Database Design
- the process of developing database structures from
user requirements for data
- a structured methodology
Structured Methodology - a number of ordered formal
processes with known inputs and expected outputs
Objectives
1. derive relationships
2. evolve to meet user requirements
3. user requests are met within reasonable time limits
CSE3180 Summer 2005 Lect 2 / 16
3 Schema Architecture
The Primary Objectives of a DBMS are to provide
facilities for :
1. Definition of Database Logical Structures
2. Definition of Physical Structures
3. Access to the Database
4. Definition of Storage Structures to store
user data
These components are known as the
‘database architecture’
CSE3180 Summer 2005 Lect 2 / 17
3 Schema Architecture ANSI - SPARC Architecture
logical
logical
EXTERNAL
SCHEMAS
user 1
user 2
Global
physical
SCHEMA: Describes data in the
data base - also sub-schema
user..n
CONCEPTUAL
SCHEMA
(as seen by database analysts,
designers, programmers and the
Data Base Administrator)
INTERNAL
SCHEMA
(Storage Views)
(as seen by the Operating System
and the Data Base Administrator)
CSE3180 Summer 2005 Lect 2 / 18
3 Schema Architecture
external
view A
Cobol
+ DML
external
view B
C
+ DML
external
view C
DML
external
view X
Assembler
+ DML
External
Schemas
user
interface
dbms
Conceptual Model
Internal Model
operating
system
Conceptual
Schema
Internal
Schema
logical
record
interface
stored
record
interface
Physical Level
physical
record
interface
CSE3180 Summer 2005 Lect 2 / 19
Database Design
Functional
Requirements
Other matters
Domains, Nulls,
Derived Data,
Encoded Data.
Information Level
Design Processes
Final Information
Level Design
Data Base Model
Physical
Constraints
Physical
Level Design
Final Database
Structure
CSE3180 Summer 2005 Lect 2 / 20
Database Design
4th Generation Environment - User Perception
user terminal
teleprocessing
monitor
report
writer
query
language
electronic
mail
application
programs
e-mail
files
data dictionary
DBMS
data
base
structured and non-structured data
images, graphics, video,voice
CSE3180 Summer 2005 Lect 2 / 21
Data Modelling
Some Terms and Considerations
MEDIA : Magnetic Disks, Optical Disks, CD Roms, Other devices (Smart
Cards)
TERMS : Seek Time, Rotational Delay, Cylinder, Track, Sector, Block, Page,
Device
ACCESS TIMES ~ 400ms or less - floppy disk
23 to 30 ms or less large fast disk
AIM OF Storage Structures and DBMS : To reduce the number of I/O’s
STORAGE STRUCTURE : An arrangement of data on a storage medium
Data Access Software
1. Disk Manager
2. File Manager
(page level)
(record level)
CSE3180 Summer 2005 Lect 2 / 22
Some More Terms
Types of Tables
Base Table : A stored table. A physically persistent table,
stored as a file on disk when implemented in a relational
database management system
Derived Table : A temporary table produced as a result of a
query on one or more base tables, or the invocation of a
view. A derived table exists for the duration of the operation
which creates it . ( a question : where does it exist ?)
View : A virtual table - stored as a query which when invoked
generated a derived table. This view can then be queried as
if it were another base table. (we will discuss views later in
the semester)
CSE3180 Summer 2005 Lect 2 / 23
Data Modelling
Data Modelling is about - Structuring and Organising Data
Leads to the emergence of
Records
Attributes
Data Types
Constraints
Semantics e.g. age relates to ?
person ? building ? isotope ? galaxy ?
Hierarchical
Commercial Data Base Models
Specific organisation and relationships between
records held in the database
Network
Relational
Constraints, domains, conditions of update,
inserts, deletes, modifications, access
CSE3180 Summer 2005 Lect 2 / 24
Entities
• The designation of a ‘thing’ about which data is to be
collected
stored
processed
• Typical Entities : persons, items, buildings, stocks,
vehicles, jobs, books, libraries
CSE3180 Summer 2005 Lect 2 / 25
Entity Relationships
Or, The Relationships between Entities
Type
Shown As
1. One to One
Example
Driver -----> Licence
1:1
Watch For :
OPTIONAL
2. One to Many
1:M
Subject ---------> Students
MANDATORY
3. Many to Many
M:N
Order No.
Order <------------> Part Number
Part No.
1
A
2
B
3
C
4
D
Create Order-Part record
CSE3180 Summer 2005 Lect 2 / 26
Logical Data Modelling
• 3 types of data objects:
– Entities
– Attributes
– Relationships
• ENTITIES: Are persons, places, or things about which data
is to be, or is, gathered
• ATTRIBUTES : Are the properties of entities
Examples are Names, Tax Numbers, Age, Status
• RELATIONSHIPS : Describe how entities relate to each other
e.g. Customers BUY Products
Persons WORK_ON Jobs
CSE3180 Summer 2005 Lect 2 / 27
Logical Data Modelling
Entity-Type : An entity type represents the class of Objects
which share some common aspect - such as
Jobs, Persons, Materials, Houses
Entity-Occurrence : Also called ‘entity instance’
Example : “3 bedroom, brick veneer, 10 years
old” are some of the actual values of an entity
instance of the Entity Type ‘House’
A common term for this is ‘real values’
CSE3180 Summer 2005 Lect 2 / 28
Logical Data Modelling
CARDINALITY
Premise: If an entity ‘A’ relates to another entity ‘B’, then the
conditions of occurrences of ‘A’ and ‘B’ need to be known.
CARDINALITY : The specification of the number of occurrences
of 1 entity type which can be related to the
number of occurrences of another entity type
Cardinality is expressed as ‘one’ or ‘many’
CSE3180 Summer 2005 Lect 2 / 29
Logical Data Modelling
2 entities can be related as :
One to One : (1 : 1)
The interpretation of this is that an occurrence of an entity ‘A’
can relate to ONE and ONLY ONE occurrence of entity ‘B’,
and an occurrence of entity ‘B’ can relate to ONE and ONLY
ONE occurrence of entity ‘A’
One to Many : (1: M) or (1 : N)
The interpretation is that ONE occurrence of entity ‘A’ can
relate to ONE or MORE occurrences of entity ‘B’, but an
occurrence of entity ‘B’ can relate to ONE ONLY occurrence
of entity ‘A’
CSE3180 Summer 2005 Lect 2 / 30
Logical Data Modelling
Many to Many (M : N) - a common business relationship
The interpretation is that ONE or MORE occurrences of entity
‘A’ can relate to ONE or MORE occurrences of entity ‘B’ and
ONE or MORE occurrences of entity ‘B’ can relate to ONE or
MORE occurrences of entity ‘A’
Is this the relationship which you are dealing with in Part 2 of
your assignment ?
CSE3180 Summer 2005 Lect 2 / 31
Entity Relationships
One to One
One to Many
Many to One
Many to Many
CSE3180 Summer 2005 Lect 2 / 32
Entity Relationships
• Students have a M : 1 relationship with a Course
• A Course has a 1 : M relationship with prescribed units
• A Unit has a 1 : M relationship with Students
CSE3180 Summer 2005 Lect 2 / 33
Logical Data Modelling
MODALITY
An extension of relationship
Indicates whether an occurrence MUST PARTICIPATE in a
relationship
Cardinality indicates the maximum number of entity
occurrences which can participate in a relationship
Modality indicates the minimum number of occurrences
Modality = 0 if an occurrence is not needed or is optional
Modality = 1 if an entity occurrence is required or mandatory
CSE3180 Summer 2005 Lect 2 / 34
Logical Data Modelling
DEGREE
Relates to the number of entity types associated in a
relationship
UNARY - The entity type is related only to itself (also called
recursive)
BINARY - Two entity types are related (most common case)
TERNARY - A relationship involving more than two entity types
CSE3180 Summer 2005 Lect 2 / 35
Logical Data Modelling
ATTRIBUTE TYPES and ATTRIBUTE VALUES
An attribute type is an occurrence of a named set of values
e.g. ‘size’, ‘name’, ‘mass’, ‘person_id’
An attribute value is an occurrence (or instance) of an attribute
type
An attribute value is a characteristic of, or a fact about an entity
occurrence
e.g. Attribute ‘size’ Attribute value ‘25.4mm’
Entities/Attributes are often referred to as ‘meta-data’, that is
‘data about data values’
CSE3180 Summer 2005 Lect 2 / 36
Logical Data Modelling
DOMAINS
A ‘Domain’ is a set of possible values which an attribute set can
adopt
e.g. dates, integers, person name constructions, post codes
3 Main Types of Domains:
Data Type : Numeric, Text, Integer, Date .....
Ranges : Values between sets
‘Employee numbers must be between 10156
and 10987’
Acceptable Values : Specific Post Codes, Area Codes,
Names of Prominent People, Building Names
They indicate the only values an attribute can have.
All of these features contribute to ‘Database Integrity’
CSE3180 Summer 2005 Lect 2 / 37
Logical Data Modelling
SalesRep
seamen
represents
Customer
Order
Associated with
Part
Relationships Representation
CSE3180 Summer 2005 Lect 2 / 38
Logical Data Modelling
Cardinality
Course
Units
Meaning : ONE
Meaning : MANY
CSE3180 Summer 2005 Lect 2 / 39
Logical Data Modelling
Cardinality
Course
Units
Meaning : ONE
Meaning : AT
or
LEAST ONE
MANY
Many means more than One
CSE3180 Summer 2005 Lect 2 / 40
Logical Data Modelling
Mandatory - Optional Relationship
Artists
(Binary
Relationship)
Pictures
paint
Unary Relationship
Ternary Relationship
customers
buy
cars
dealers
CSE3180 Summer 2005 Lect 2 / 41
Logical Data Modelling
ENTITIES
RELATIONSHIPS
membership class
cardinality
degree
modality
recursive
binary
1:1
1:N
M:N
mandatory
optional
n-ary
ATTRIBUTES
domains
values
CSE3180 Summer 2005 Lect 2 / 42
Entity Relationships
Each entity may be a ‘one’ or
‘many’ participant
vendor
part
ships
warehouse
quantity
Quantity is the amount of a particular ‘part’ delivered from a particular
‘vendor’ to a particular ‘warehouse’.
CSE3180 Summer 2005 Lect 2 / 43
The Monash Gallery
Exhibition
Carrier
Contact
Contract
Collection
Artist
History
Artist
Gallery
Exhibition
Category
Artist
Category
Item
Buyer
Invoice
Payment
Category
CSE3180 Summer 2005 Lect 2 / 44
Model Interpretation
What do these models indicate ? Are they accurate ?
Student
?
Course
Library
Texts,Periodicals
Borrowers
Aircraft
?
Flight
Passenger
Maintenance
CSE3180 Summer 2005 Lect 2 / 45
Data Modelling Thoughts
Entities
Nation
nation code
nation name
exchange rate
Stock
stock code
firm name
stock price
stock qty
stock divdnd
Attributes
A 1:M relationship
CSE3180 Summer 2005 Lect 2 / 46
Data Modelling Thoughts
Nation
Name
Population
Area
State
Statename
Statepop
State area
City
Cityname
Citypop
City Area
Questions : Is the Nation ‘Population’ necessary ?
Is the State ‘Population’ necessary ?
CSE3180 Summer 2005 Lect 2 / 47
Data Modelling Thoughts
Library
Book
libname
callno
ISBN
booktitle
duedate
Borrower
borrower ID
Library
Book
Copy
Borrower
libname
callno
ISBN
title
bookno
due date
borrowerID
Physical Objects
CSE3180 Summer 2005 Lect 2 / 48
Data Modelling Thoughts
An interesting data model can be constructed which deals with aircraft
leasing;
An aircraft broker will ‘own’ many aircraft which will be leased to a variety
and number of airline companies (including of course Virgin Blue and
Qantas)
When a lease expires, the broker leases the plane to another airline
company, which explains why there are some aircraft still flying which
are 20 to 30 years old.
In this scenario, an aircraft can be leased many times, and an airline
company can lease many aircraft.
Normally there is an agent handling each such deal. An agent can lease
many aircraft and deal with many airlines. A transaction occurs when a
deal is reached with an airline to lease an aircraft or a number of aircraft.
CSE3180 Summer 2005 Lect 2 / 49
Modelling the Aircraft Scenario
Aircraft
AircraftAgent
aircraft code
AirlineAircraft
Agent
Agent ID
Airline
AirlineAgent
airlinename
An Aircraft - Airline - Agent data model
CSE3180 Summer 2005 Lect 2 / 50
Modelling the Aircraft Scenario
There is a bit of a problem here.
Where is the data relevant to the lease to be stored ( and how
many times, and therefore how many versions ? - and which
of these is the correct current value ?)
It could be in Airline-Aircraft.
If so, the agent responsible for the deal is ignored. Should it be
in Agent-Airline ?
What is the problem here ?
CSE3180 Summer 2005 Lect 2 / 51
Modelling the Aircraft Scenario
• A solution:
Aircraft
aircraftcode
Lease
start date
Agent
agentid
Airline
Airlinename
CSE3180 Summer 2005 Lect 2 / 52
Business Systems
Many Business Information Systems incorporate a database
or many databases in their construction
The database(s) must be able to respond to ‘normal’
processes such as update, regular reports, regular queries as
well as support new queries, drill down information requests
as well as being robust enough to accommodate
organisational, reporting and ‘emergency’ conditions.
The ‘main’ database is often imaged onto another server for
access by organisational departments, functional managers
and for special planning. Such database ‘copies’ are rarely
updatable or have feedback capability to the main enterprise
database(s)
CSE3180 Summer 2005 Lect 2 / 53
CSE3180 Summer 2005 Lect 2 / 54
Database Development Processes
Project Identification
Selection
Project Initiation
and Planning
Plus Reviews
Analysis
Logical Design
Physical Design
Implementation
Maintenance
CSE3180 Summer 2005 Lect 2 / 55
Have you heard this before ?
A relation is a named table with columns and rows
An attribute is a named column of a relation
The domain of an attribute is the set of values the attribute
may take
A tuple is an alternative term for a row of a relation
The number of attributes is known as the degree of a relation
The number of rows is known as the cardinality of a relation
CSE3180 Summer 2005 Lect 2 / 56
Mulga Bill’s Bicycle - Banjo
Patterson
Twas Mulga Bill, from Eaglehawk, that caught the cycling craze;
He turned away the good old horse that served him many days;
He dressed himself in cycling clothes, resplendent to be seen;
He hurried off to town and bought a shining new machine;
And as he wheeled it through the door, with air of lordly pride,
The grinning shop assistant said, “excuse me, can you ride ?”
CSE3180 Summer 2005 Lect 2 / 57
Mulga Bill’s Bicycle - Banjo
Patterson
“See here young man” said Mulga Bill “from Walget to the sea,
From Conroy’s Gap to Castlemaine, there’s none can ride like
me.
I’m good all round at everything, as everybody knows,
Although I’m not the one to talk - I hate a man that blows.
But riding is my special gift, my chiefest, sole delight;
Just ask a wild duck can it swim, a wild cat can it fight.
There’s nothing clothed in hair or hide, or built of flesh or steel,
There’s nothing walks or jumps or runs, on axle, hoof or wheel,
But what I’ll sit, while hide will hold and girths and straps are
tight
I’ll ride this here two-wheeled concern right straight away at
sight”
CSE3180 Summer 2005 Lect 2 / 58
Mulga Bill’s Bicycle - Banjo
Patterson
T’was Mulga Bill, from Eaglehawk, that sought his own abode,
That perched above the Dead Man’s Creek, beside the
mountain road.
He turned the cycle down the hill and mounted for the fray
But ere he’d gone a dozen yards it bolted clean away.
It left the track, and through the trees, just like a silver streak,
It whistled down the awful slope, towards the Dead Man’s
Creek.
CSE3180 Summer 2005 Lect 2 / 59
Mulga Bill’s Bicycle - Banjo
Patterson
It shaved a stump by half an inch, it dodged a big white-box
The very wallaroos in fright went scrambling up the rocks
The wombats hiding in their caves dug deeper underground,
As Mulga Bill, as white as chalk, sat tight to every bound.
It struck a stone and gave a spring that cleared a fallen tree,
It raced beside a precipice as close as close could be:
And then as Mulga Bill let out one last despairing shriek
It made a leap of twenty feet into the Dead Man’s Creek
CSE3180 Summer 2005 Lect 2 / 60
Mulga Bill’s Bicycle - Banjo
Patterson
T’was Mulga Bill, from Eaglehawk, that slowly swan ashore:
He said “I’ve had some narrer shaves and lively rides before
I’ve rode a wild bull round a yard to win a five pound bet,
But this was the most awful ride that I’ve encountered yet.
I’ll give that two-wheeled outlaw best; it’s shaken all me nerve
To feel it whistle through the air and plunge and buck and
swerve.
It’s safe at rest in Dead Man;s Creek, we’ll leave it lying still;
A horse’s back is good enough henceforth for Mulga Bill”
CSE3180 Summer 2005 Lect 2 / 61
Mulga Bill’s Bicycle - Banjo
Patterson
CSE3180 Summer 2005 Lect 2 / 62
That should be enough for today - see you tomorrow.
CSE3180 Summer 2005 Lect 2 / 63