ERD entities and attributes – written + Drawn

IMG_0614IMG_0612 IMG_0613

User (Entity)

User_Id Integer, Auto increment Primary Key

User_FirstName Characters

User_Lastname Characters

User_Email Characters

User_Password Characters (20)

User_Bio characters

User_rating Integer

 

User_Persona – composite key

User_Id Integer, Auto increment Foreign Key

Persona_Id Integer Auto increment Foreign Key

 

Persona (entity)

Persona_Id Integer Auto increment Primary Key

Persona_Label characters

Persona_AvergaeAge Number NULL

Persona_Income Integer NULL

Persona_GenderSkew characters NULL

Persona_Goals characters NULL

Persona_Challenges characters NULL

Persona_Objections characters NULL

Persona_Hates  characters NULL

Persona_Notes characters NULL

 

Industry (Entity)

Industry_Id Integer Auto increment Primary Key

Industry_Label characters

CREATE TABLE Industry (

IndustryId MEDIUMINT NOT NULL AUTO_INCREMENT, IndustryLabel TEXT,

PRIMARY KEY (IndustryId)

);

 

 

Role (Entity)

Role_Id  Integer Auto increment Primary Key

Role_Label characters

Role_IndustryId Integer

 

Comment (Entity)

Commet_Id Integer Auto increment Primary Key

Comment_Text characters

Comment_Date date

Comment_UserId Integer

 

 

User_Persona – composite key

User_Id Integer, Auto increment Foreign Key

Persona_Id Integer Auto increment Foreign Key

Advertisements
ERD entities and attributes – written + Drawn

Getting to grips with SQL

Bill Weinman www.Lynda.com MySQL essential training run through

After watching the database essential training videos and creating my first entity relationship diagram models I have moved on to the creation of actual databases. I am running through the MySQL essential training as a way to quickly get a good grasp of how the database is built and how I should go about building it.

In theory after creating my ERD model and then going through the MySQL training I should be able to piece together my database in a working prototype of some form. However for the artefact I will be handing in a working database of some kind and the ERD, not the completed database.

The first step here was setting up the correct environment on the pc I am using, this involved setting up a web server called xampp which is a “a free and open source cross-platform web server solution stack package, consisting mainly of the Apache HTTP Server, MySQL database, and interpreters for scripts written in the PHP and Perl programming languages.” This basically allows me to work with databases without requiring my own server.

After setting up the server I went on to make my the localhost user secure with a password and create a “web” user with minimal permissions (SELECT,INSERT,UPDATE,DELETE,FILE). Along with the SID user for the tutorial. The exercise databases where then imported into MyphpAdmin.

After running through the course I have created my first Database with the individual tables, translated from my ERD. However I am still working on creating the FK and dependencies and also have no actual data to fill them yet. But as I continue to research I will gather more to fill them. Below is some of the code I have put together, It’s still simple but I intend to build it up as I improve.

DROP TABLE IF EXISTS Persona;

 

CREATE TABLE Persona (

PersonaId MEDIUMINT NOT NULL AUTO_INCREMENT, PersonaLabel TEXT NOT NULL, PersonaAverageAge INT, PersonaIncome INT, PersonaGenderSkew TEXT, PersonaGoals TEXT,

PersonaChallenges TEXT, PersonaObjections TEXT, PersonaHates TEXT, PersonaNotes TEXT,

PRIMARY KEY (PersonaId)

);

 

DROP TABLE IF EXISTS User;

CREATE TABLE User (

UserId MEDIUMINT NOT NULL AUTO_INCREMENT, UserFirstname TEXT NOT NULL, UserLastName TEXT, UserEmail TEXT NOT NULL, UserPassword TEXT NOT NULL, UserBio TEXT,

PRIMARY KEY (UserId)

);

 

DROP TABLE IF EXISTS Industry;

CREATE TABLE Industry (

IndustryId MEDIUMINT NOT NULL AUTO_INCREMENT, IndustryLabel TEXT,

PRIMARY KEY (IndustryId)

);

 

CREATE TABLE Role (

RoleId MEDIUMINT NOT NULL AUTO_INCREMENT, RoleLabel TEXT,

PRIMARY KEY (RoleId)

);

Getting to grips with SQL

Entity Relationship Modelling

A short guide to designing Entity Relationship Models using Barker’s notation.

By Frankie Inguanez

Frankies take on ERD is more in-depth that the previous video tutorial, whilst also being slightly harder to understand unless you really break down each section. As I work through the document I aim to write down and record the various key elements as a way of both demonstrating that I have understood the material to myself whilst simultaneously making notes which I can look to in the future when designing my own ERD databases. This material also specifically uses Barkers notations which is a variation of the crows foot notation seen previously.

It was half way through taking notes that I came to the realisation that it was complete overkill and I was essentially just repeating what he had written down first. So I have chosen to use his sheet as my guidelines rather than recreate his work, I included the notes I did take before stopping in this blog post.

General Rules of ERDs

  1. Capture all required information.
  2. Information appears only once.
  3. Model no information that is derivable from other information already modelled.
  4. Information is in a predictable, logical place.

Key elements

Entity – is again the top element, this again represents a table with an entity instance being a single record or “row” of the table.

Attribute – is again the property of an entity which describes a characteristic of particular entity instance. However while the previous work featured primary keys and identifiers as a separate element here they are listed as a type of attribute. These three types are

“a. Unique Identifier: A UID is an attribute whose value uniquely identifies an entity instance. A UID is implemented as a Primary Key.

  1. Mandatory Attribute: A mandatory attribute is one whose value cannot be null.
  2. Optional Attribute: An optional attribute is one whose value can be null.”

Relationships – Links two or more entities (tables) together. Here a relation is implemented as a foreign key, therefore the FK “attribute” is not listed as an attribute in the target entity.

Diagram Showing the entity and attribute drawing rules.

Diagram showing the relationship drawing rules.

When drawing the relationships there are a number of rules to follow which I have copied into the diagram with lines showing where they are being used. But to reiterate a relationship must be optional or mandatory and each relationship must be labelled with its perspective according to the entity it is attached to.

Relation ships can be one of three different types

One to one – Pretty obvious, one entity instance relates to one entity instance. The FK or foreign keys relating them should always be placed where A. there is never going to be a null value so in the “must have” entity. Or in the entity which has the least amount of nulls/or rows or the most relevant to the table.

One to many – Each entity is related to multiple entity instances. The foreign key should be placed in entity touched with the crow’s foot, since many rows in B will be related to only one row in A.

Many to many (Always need to be resolved using an intermediary or transactional table)

 

 

Entity Relationship Modelling

Database design essential training

http://www.lynda.com/Access-tutorials/What-you-need-know/112585/121192-4.html

This tutorial from Lynda.com proved to be far more accessible than the ERD found on folksonomy mainly because it looked at the whole of database design from the beginning while Frankies looked more at how to technically go about designing a database using a specific set of rules. While this is no doubt a useful way of doing it the full knowledge of database design is required I feel before jumping into the technical way of designing them. Just like it more useful to understand how code or programming works before attempting to create something using the code.

Below is my notes from the videos, My ERD will created using these and show in my next blog post.

Key Points

  • It’s ideal to be methodical and take you time.
  • It’s best to start ultra-simple and work your way up.

First what’s the point?

To allow users to create and manage marketing personas and allow them to shared and  contributed to by multiple different users and track the progress and usage of these personas for future development.

What do you already have?

Nothing except theoretical personas information. People, expertise, an existing database? Go over this information and fix any problems you find. I myself had literally nothing as my starting point except the list of information I wanted to be in the database.

What entities (tables) do you have? Generally use singular nouns for these but consistency is key. Picking the most useful or relevant words is the key here.

Users, Personas, Comments, Notes, Updates, Usage, statistics, case studies, Authorship, Industry, Role,.

Next is figure out the attributes of each entity, these will become the columns of each table, while they can be abstract or nonspecific when sketching out first drafts it is helpful to get specific early on. Whilst being granular as possible.

So for my purposes, we have to begin with

The entity’s, work out each ones attributes and then go on to decide the data type with in each attribute should be, as in intergar, character, number ect. Also if they are null or void null

Relationship cardinality

One to one (Rare)

One to many (The vast majority)

Many to many (Requires resolving)

In many ways the database I am building can be compared to a typical learning database ie the customer to order relationship is similar to my user to Persona relationship. A single user can have many personas, but a persona may only be created by a single user (for now)

When and how to use foreign keys

You never change the “one” side you change the “many” side usually by adding the Pk to the many table where it know becomes a foreign key. This acts as the identifier to the table. The FK can be named the same as its primary key original or simply named to showed what it is, like the customer/order example it could be “customerId” or it could be “placed by”

So in my example each Persona (The many table) will have a FK from the user table, identifying who created which persona.

As I develop my ERD while going through the video I am starting to pick out various relation ships which before I thought would be contained within other tables, in particular separating our roles and Industry from personas.

Many to Many

This was where I realised that if I wished to have multiple users able to share and contribute on each persona I would need a linking or bridging table in order to have a many to many relationship for the contributing.

Having just reached the normalisation process in the tutorial I chose this to be a good break point for me to start with the sketching out and early conceptual modelling of the actual data base before I would move onto the more specific and technical areas.

Database design essential training

Entity Relationship diagramming video tutorial

Gina Baldazzi (2013) Folksonomy

“Graphical representation of the data requirements of a database”

After speaking with lecturers I decided that the next element to set about creating for the artefact hand in would be the database from which I will be working. Whether my project changed or altered as I continued the work remains to be seen but databases are generally integral to many pieces of work so it is important that I understand the processes which go into creating one.

I was instructed to look at entity relationship modelling as the first step as this would allow me to learn the fundamentals and also greatly speed up the actual creation of the databases if I had a good structure on which to build. Folksonomy provided and good beginning to this, I found a short instructional video which helped me brush up on what I had learned (having not had a chance to review ERD in a few months). This I will follow with the more in-depth look at it from Frankie Inguanez before designing a prototype data base with some planning help from a lecturer Simon Perkins. After this I will follow some more tutorials curtesy of Lynda.com to help refine and master the ERD before again using Lynda.com to help me create the actual database.

The video while great for someone only really starting to learn ERD did go through a number of the concepts very quickly with little explanation on how they should be created (specifically composite keys) but it did outline the basics very well while also demonstrating how it should be used in a real world scenario.

(EDIT)After reading the YouTube comets on the original post it becomes apparent that there may be one or two mistakes in the tutorial, (but these are youtube video comments) and I still found it useful as a starting point.

Five key elements from the video (Almost word for word)

Entity – represents a person, place or thing you wish to track in a database, This will become a table in the database. Each occurrence of this entity is an entity instance, this will become each record or row in the table.

Attributes – describe various characteristics of an individual entity. These will be the columns in the table, these don’t have to be unique. E.g. first name

Primary Key/identifier – an attribute or group of attributes that uniquely identifies an instance of an entity. This must be unique, as it is used to identify the entity instance. E.g. student number. Sometimes you need more than one attribute to make an instance unique, that’s when you use a composite key

Relationship – describe how one or more entities interact (a verb) with each other. E.g. this entity has a phone number. Essentially a line showing the relationship exists.

Capture

Cardinality – the count of instances that are allowed or are necessary between entity relationships. How many rows we need from one table to link it to another table. Showed with crows feet notation. Broken into two parts minimum(fewest rows needed for a relationship)/maximum(cannot exceed a number of rows for the relationship)

The whole Entity Relationship Diagram is designed to act as a blue print for the database.

Entity Relationship diagramming video tutorial

Activity centred design Toolkit

The hear phase of IDEO’s human centred design toolkit was where I first encountered many of the issues which I have been trying to correct since beginning the project.  I was aware that I would need to alter some of the various aspects of it to better suit my needs. However the more I worked through the hear phase the more I was coming a lot a lot of small problems which I couldn’t solve with just small tweaks. The problems where even more exaggerated the further into the toolkit you delved. While it is a great and very useful piece of kit for particular groups it just didn’t suit my needs. The largest whole was the fact that much of the work is highly team based and collaboration focused. While I would be using other people every now and then the majority of “grunt” work within the project would just be performed by me with on experts and specialists used where they needed to be used.

This is what originally led me to start researching other ways of going about the design process but which kept to the ideals of the IDEO toolkit, designing for the user and doing it well. My next step will be to start placing all of the various pieces of information I have gathered into their own design methodology which I can use for my creative project. I intend on creating a design toolkit in the same vein as the IDEO toolkit but specialised to my needs and other web designers and developers. I now intend to produce one which will fix this weakness.

  • Impractical without a team or dedicated space.(Allowances for smaller teams of solo work by factoring in different phase to make up for lack of specialists.)
  • Some elements are very time consuming.(Using activity theory and core affects to speed up hear and early create phase)
  • Not focused on software design.(Working in phases for IA,UI and UX)
  • No real focus on aesthetics or user experience.(Material design)
  • No aesthetic design elements means I need to find a way of doing it myself.(Material Design)

All I need now is a name for the new design toolkit.

Activity centred design Toolkit