Mysql class notes:

Purchased a class frmo www.eduonix.com for $19. Supposedly going to teach me everything there is to know about mysql.

Lessons learned:

Commands:

mysql> show engines

shows the list of supported engines.

mysql_install_db

Configure necessary directory structures

/usr/bin/mysqladmin -u root password ‘new-password’

Sets password for root user

Overview:

Standard overview of the class.

Chapter 1: db generics

• What is a db

• Table example

• Create Read Update Delete (crud) = DML (data manipulation language)

• Overview of db models.

• server process is mysqld

Chapter 2: intro:

• Engines:

  • Default:

    • MyISAM used to be default

      • Good for low insert/updates

      • Good for very high select rates.

      • Uses table level locking.

    • InnoDB is the current default.

      • Uses row level locking

      • Foreign key functionality.

  • Reasons for selection:

    • Amount of data

    • Speed

    • Functionality

    • Max number of rows

    • Data integrity

  • Examples:

    create table table1 (i int) engine = innodb;
    create table table2 (i int) engine = csv;  # interesting.
    

• Data types:

  • Main categories:

    • Numeric

      • tinyint ~ boolian

      • float

    • Strings

      • varchar

      • enum: a list

    • Date/time

      • date

      • datetime

      • timestamp ~ epoch

Chapter 3: Getting started w/mysql:

• Installation: mysql installation on windows appears borked. Got it running but it’s not quite as smooth as the tutorial even though it’s the same version.

• Linux was already installed.

  • mysql-server

  • ubunut: need mysql-client

• xampp:

  • graphical interface for mysql, phymyadmin, and others.

  • downloaded/installed on linux, windows. had some battles.

    • Both systems were runnign previously installed mysql. Doesn’t work.

    • Windows tries t use the same port as skype.

    • Start process in linux is /opt/lampp/lammp [start|stop]

• If I decide I like phymyadmin, it’s available from epel.

• phymyadmin

  • http://locahost/phpmyadmin

  • When creating users to log in via phpmyadmin, ensure localhost is defined in the host entry.

  • Looks like a gui front end. wasn’t overly impressed.

• mysql workbench

  • This one is nice. Provides EER docuemntation and can do all the things

Chapter 3.5: Redo

Reloaded nap a few times until I got it right. Mirrored the mysql repos on my spacewalk server, then built nap a few times until I got everything running.

phpmyadmin took a few times to get right. The default httpd config file was wrong, permissions on the /var/lib/mysql directory were wrong. After getting all that working, I have the kickstart post script fully functional.

Chapter 4: Normalization:

• Seems his narratives are out of order. He’s talking about doing stuff in the future that we’ve already done.

• Relational model rules:

  • Each column value must be a single value.

  • All values of a given column must be the same data type.

  • Each column name must be uniquue

  • column order is insignificant.

  • No two rows in a relation can be identical

  • Order of the rows is insignificant.

• Functinal dependency: describes a relationship between columns within a single realtion

  • A column is dependent on another if one value can be used to determine the value of another.

• Common mistakes:

  • Tables with too many fields or fields that don’t relate to each other.

  • Too many tables with similar data.

  • Repeated rows

  • Using comma sepaarate values or multiple values in a single row

  • Poor naming conventinos

  • Poor or no planning

  • Non-normalized data.

• Normalization:

  • The process of organizing fields and tables to minimize redundancy and dependency.

  • Can involve dividing larger tables into smaller tables with relationships

  • Objective is to isolate data so that actions in a field can be made in one table which then propogates through the rest of the needed tables using properly defined relationships.

• Forms:

  • 1NF:

    • No repeating rows or duplicate fields

    • Each row contains only one value

    • Each row/record is unique

    • Each row is identified through a primnary key

  • 2NF

    • Already in 1NF

    • All non-key fields depend on all components of primary key

    • No partial dependencies.

  • 3NF

    • Alread in 2NF

    • Every non-prime attribute must depend n the primary key

    • eg: addresses should be in ther own table, not in the customers table.

• Design process:

  • Define your purpose: What is the reason for the application?

  • Determine your tables

  • Determine your fields/columns

  • Determine relationships

  • Create an EER diagram

  • Refine as needed.

• databaes mapping:

  • products

    • id: (int(11), auto_increment, primary_key)

    • name (varchar(100))

    • description (text)

    • price (varchar(20))

    • category: (int(11), foreign_key -> products_categories id)

    • image (varchar(100)

  • products_categories

    • id: (int(11), auto_increment, primary_key)

    • name (varchar(100))

    • description (varchar(100))

    • image (varchar(100))

  • customers

    • id: (int(11), auto_increment, primary_key)

    • first (varchar(100))

    • last (varchar(100))

    • email (varchar(100))

    • password (varchar(100))

    • avatar (varchar(100))

    • join_date (timestamp, default- current_date)

  • customer_addresses

    • id: (int(11), auto_increment, primary_key)

    • custmoer: (int(11) foreign key to customers:id)

    • address (varchar(100))

    • address2 (varchar(100))

    • city (varchar(100))

    • state (varchar(100))

    • zipcode (varchar(100))

  • reviews

    • id: (int(11), auto_increment, primary_key)

    • customer: (int(11), foreign key to customers:id)

    • title (varchar(100))

    • body (text)

    • rating (int(2))

    • product: (int(11), foreign key to products:id)

    • review_date (timestamp, default - current_date)

  • pages

    • id: (int(11), auto_increment, primary_key)

    • title (varchar(100))

    • body (TEXT)

    • category: (int(11), foreign key to page_categories:id)

    • create_date (timestamp, default - current_date)

  • page_categories

    • id: (int(11), auto_increment, primary_key)

    • name (varchar(100))

    • descripton (varchar(100))

    • image (varchar(100))

Chapter 5: Creating the database:

• First section was simply typing the above into myphpadmin. I think the command line would have been easier.

• Author suggests exporting the empty database, once the schema’s created, in order to have the empty

Chapter 6: SQL fundamentals:

• Objectives:

  • union

  • concat

  • count

• Hae to correct the sql ‘cause I didn’t follow the same naming convetion. BAD student! BAD!:

  INSERT INTO products
  (name,description,price,category,image)
  VALUES
  ("Surface Tablet","Microsoft Surface Pro with 128GB Memory","499.99",2,"images/surface.jpg"),
  ("Nextbook Tablet","Nextbook 7 inch Tablet 8GB Memory","99.99",2,"images/nextbook.jpg"),
  ("Philips BTM2180/37 Micro Music System","Wireless music streaming via Bluetooth","149.99",2,"images/stereo.jpg"),
  ("Memorex Boombox","Memorex MP3142 - Boombox","45.99",2,"images/boombox.jpg"),
  ("Mossy Oak Infinity Seat Cover","High quality, heavy Mossy Oak Infinity Seat Cover","19.99",6,"images/seatcover1.jpg"),
  ("Remington Floor Mat Set","Remington Best Fashion Carpet Floor Mat Set Tan 4pc","29.99",6,"images/floormat1.jpg"),
  ("Rawlings RBG36 Baseball Glove","Rawlings RBG36 Baseball Glove 12 inch","29.99",4,"images/glove1.jpg"),
  ("Franklin Pro Batting Gloves","Franklin Sports MLB Shok-Sorb Pro Batting Glove Pair","19.99",4,"images/glove2.jpg"),
  ("Golds Gym Trainer 420","NEW Gold's Gym Trainer 420 Treadmill with Bonus Puzzle Mat","377.00",4,"images/treadmill1.jpg"),
  ("Weslo Cadence G 5.9 Treadmill","Weslo Cadence G 5.9 Treadmill","287.00",4,"images/treadmill2.jpg"),
  ("Golds Gym Trainer 315 Treadmill","Gold's Gym Trainer 315 New Model Treadmill","258.00",4,"images/treadmill3.jpg");
  
  INSERT INTO customers
  (first,last,email,password,avatar)
  VALUES
  ("Sandy","Williams","sandy@gmail.com","1234","images/sandy.jpg"),
  ("Ben","Barro","ben@gmail.com","1234","images/ben.jpg"),
  ("William","Terry","william@gmail.com","1234","images/william.jpg"),
  ("Sarah","Thompson","sarah@gmail.com","1234","images/sarah.jpg"),
  ("Donald","Duck","donald@gmail.com","1234","images/donald.jpg"),
  ("Beth","McAdams","beth@gmail.com","1234","images/beth.jpg"),
  ("Harry","Henderson","harry@gmail.com","1234","images/harry.jpg");
  
  
  INSERT INTO customer_addresses
  (customer,address,address2,city,state,zip)
  VALUES
  (1,"10 Main st",NULL,"Boston","MA","01929"),
  (2,"20 Main st","Apt 3","New York","NY","01929"),
  (3,"30 Main st",NULL,"Seaside Heights","NJ","01929"),
  (4,"40 Main st","Unit 10","Baltimore","MD","01929"),
  (5,"50 Main st","Apt 18","Miami","FL","01929"),
  (6,"60 Main st",NULL,"Chicago","IL","01929"),
  (7,"70 Main st",NULL,"Sacramento","CA","01929");
  
  INSERT INTO reviews
  (title,body,rating,customer,product)
  VALUES
  ("Great Product","This product was great",9,1,2),
  ("Okay Product","This product was okay",6,1,3),
  ("Decent Product","This product was decent",8,3,4),
  ("Bad Product","This product was really bad",2,5,4)