Lec 3 Conceptual Data Modeling

Requirements analysis

What does the organisation need to know?

Conceptual design

Entities, attributes, relationships.

Logical design

Turn the model into tables

Physical design

Storage, indexes, and tuning

Implementation and testing

Create the database and load it

Maintenance

Change it as the organisation changes

Structured interview

What it is good for, rules that live in someone’s head

What is misses, what people forgot to mention

Document and form analysis

What it's good for, the data actually recorded today

What it misses, rules never written down

Direct observation

What it's good for, what people really do, not what they say

What it misses, rare cases and exceptions

Questionnaire

What it’s good for, reaching many user quickly

What it misses, depth, and follow-up questions

Review of existing reports

What it’s good for, what the organisation already measures

What it misses, what it wishes it could

The Entity-Relationship Model

Entity type, Entity instance, Entity set

Entity type

The kind of thing you are modeling. Is the design

Entity instance

One particular thing of that kind. Is one row

Entity set

All the instances stored right now. Is all of them right now

Seven kinds of attribute

Simple, Composite, Single-valued, Multivalued, Derived, Key, Optional

Simple

Cannot be broken into parts. Example: purok

Composite

Splits into meaningful parts. Example: full name into first and last

Single-valued

One value per instance. Example: date of birth

Multivalued

Several values for one instance. Example: a customer's phone number

Derived

Calculated, not stored. Example: amount owed, from sales minus payments

Key

Identifies the instance uniquely. Example: customer_id

Optional

May be absent for some instances. Example: mobile number