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)
);
DanBeckDesign 