Show Menu

Conceptual Database Design Cheat Sheet by

transit to design and create a solution

Step 1: Identify Entities

4 types of entity
If the inform­ation you want to include does not fit in one of these categories then it is probably an attribute, not an entity

Step 2: Identify relati­onships

Three types of relati­onship
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 relati­onship
Solving M:N
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 (assoc­iative entity)
2. The relati­onship 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 attrib­utes. 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 charac­ters, numerals, currency, dates, times, and Boolean entries (a logical value of either true or false).
http:/­/ww­w.c­hea­tog­rap­hy.c­om­/da­vec­hil­d/c­hea­t-s­hee­ts/­mysql/ Refer to this cheatsheet with data types for more assistance

Step 5: Candidate, primary, and alt key attributes

Candidate key
Each key which COULD serve as primary key. Eg: employee # or license #
Candidate keys can never be null
Primary Key
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
Alternate key
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 relati­onships
remove redundant relati­onships
consider the time dimension when assessing redundancy
Duplic­­ation of data, or storing of the same data in more than one place

Step 7: Check model supports user transa­ctions

ER model represents the data requir­ements of the organi­zation
Objective is to check that ER model supports the required transa­ctions
Two possible approaches
Describing the transa­ction
Using transa­ction pathways

Step 8: Review design with users

Review the ER model with the user to ensure that the model is a ‘true’ repres­ent­ation of the data requir­ements of the organi­zation (or the part of the organi­zation) to be supported by the database


I forgot about this cheat sheet. This is going to come back in handy :)

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.

          Related Cheat Sheets

          JAVA keytool Cheat Sheet

          More Cheat Sheets by Natalie Moore