Show Menu
Cheatography
  1. Home >
  2. Cheat Sheets >

Conceptual Database Design Cheat Sheet by

transit to design and create a solution

Step 1: Identify Entities

4 types of entity
Event
Person
Location
Thing
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
1:1
One to many
1:N
Many to one
M:1
Many to many can not be done in a database and will need to be solved
M:N
Cardin­ality
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

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
1
re-examine one-to-one relati­onships
2
remove redundant relati­onships
3
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
1
Describing the transa­ction
2
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
                                                                                   
 

Metadata

  • Languages:
  • Published: 5th October, 2014
  • Last Updated: 12th May, 2016
  • Rated: 5 out of 5 stars based on 2 ratings

Favourited By

Rakai theLand54 lix90 DeeJRoth

Comments

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