Step 1: Identify Entities
4 types of entity
If the information you want to include does not fit in one of these categories then it is probably an attribute, not an entity
Step 2: Identify relationships
Three types of relationship
One to one
One to many
Many to one
Many to many can not be done in a database and will need to be solved
Number of elements of each side of the relationship
Many to many means that a number of records in one table belongs to a number of records in another table
1. Remove these records and place them into their own table (associative entity)
2. The relationship between the existing entities and the new entity will be 'one to many'
e.g. One customer can make many sales. One invoice per one sale.
Step 3: Identify and associate attributes
Entity fields are called attributes. Aka metadata
Tables are entities, rows are tuples, and columns are attributes
E.g Each info field on a baseball card. Your name, address and phone number are attributes of you.
Step 4: Determine attribute domains
Each value in the tuple must be of some basic type, like a string or an integer
Attribute domain is the set of values allowed in an attribute.
Helps with data integrity, by insuring the value entered in each field of a table is consistent with its attribute domain
The standard domain types include data values for characters, numerals, currency, dates, times, and Boolean entries (a logical value of either true or false).
Step 5: Candidate, primary, and alt key attributes
Each key which COULD serve as primary key. Eg: employee # or license #
Candidate keys can never be null
1. Next select a candidate as a Primary Key (X). X Should:
have the minimal set of attributes
be least likely to have its values changed
be least likely to lose uniqueness in the future
All columns in the relation must be dependant on X
A candidate key NOT chosen as primary key
Step 6: Check the model for redundancy
Examine the ER model and if redundancy found, remove from model.
The three activities in this step are
re-examine one-to-one relationships
remove redundant relationships
consider the time dimension when assessing redundancy
Duplication of data, or storing of the same data in more than one place
Step 7: Check model supports user transactions
ER model represents the data requirements of the organization
Objective is to check that ER model supports the required transactions
Two possible approaches
Describing the transaction
Using transaction pathways
Step 8: Review design with users
Review the ER model with the user to ensure that the model is a ‘true’ representation of the data requirements of the organization (or the part of the organization) to be supported by the database
Download the Conceptual Database Design Cheat Sheet
Your Download Will Begin Automatically in 5 Seconds.