Conceptual Database Design Cheat Sheet

A cheat sheet in English by Natalie Moore

transit to design and create a solution

Entities Database Design Users Attributes Check Review Model Associate Domains Candidate Primary Alternate Key Redundancy Supports Transactions Conceptual

54,818 views 9,762 downloads 5 Oct 2014 History

PRs

Download

4.9 avg · 2 ratings

Step 1: Identify Entities

4 types of entity
Event
Person
Location
Thing

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	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
Cardinality	Number of elements of each side of the relationship
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 (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

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).
http://www.cheatography.com/davechild/cheat-sheets/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 relationships
2	remove redundant relationships
3	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
1	Describing the transaction
2	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

Badges

Great Cheat Sheet Useful Cheat Sheet Good Cheat Sheet Nice Cheat Sheet Downloaded from Unusual Locations × 2

Comments (1)

NatalieMoore 23:03 31 Aug 2015

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