Go
back to course outline
Data Independence
Six Steps of Database Design:
-
Requirement Collection / Analysis
What does your boss want you to do?
"Precisely" what you want.
You should be able to narrow down what you need and
verify
if it is right information from users or not.
You must iterate to ask users & make sure it is right.
-
Conceptual database design (capture reality)
Under what user want, specify the requirement and set up the rule for
data and go back to check with users.
At this stage, you do not assume which DB to use.
Only get "structure" what you want.
Thus, it is independent of software. (The DBMS has been changed
often, your design should be independent of the change.)
-
Logical database design (map to DBMS)
Describe the data in English.
The importance of this stage is how to get information correct.
Logical data is independent from storage.
-
Schema Refinement (schema
refinement based on the theory of normalizing relations)
-
Physical database design
How to organize data.
The importance of this stage is how to get information efficiently.-
Application and Security Design
Data Model: Represent data in certain way.
How do you model data in computer?
-
Relational Model - Microsoft Access stores data in
"table".
-
Hierarchical Model - tree structure.
-
Object Oriented Model
Schema: a description of a particular collection of
data, using a given data model.
e.g. Every relation has a schema, which describes the columns,
or fields.
Conceptual Database Design:
Purpose:
Capture reality
Specify data in the way
easy to understand
Get the
structure
Conceptual Data Model: (more precise than
natural language)
e.g. E-R model.
E-R Model (Entity-Relationship Model)
-
Entity
What thing to be stored
-
Relationship What
relationship among them (one to one, one to many, many to many)
-
Constraints Condition,
e.g. domain constraint (0.0< gpa <= 4.0), primary
key constraint (unique s.s.n.)
Entity - it has various properties and its existence
is independent of the other concepts.
- Must have name
- can be described / specified
by attributes. Each attribute has domain.
e.g. each individual
student
each individual class
each individual instructor
each agreement
The instructor possesses
various properties, surname, age, town of birth.....
Entity Set: a set of entities. (e.g.
student is an entity; a group of students is an entity set.)
E-R Diagram