Meeting 2 :: Databases :: 041108

On April 11th 2008, we had our second EMAWD meeting. While the highlight was definitely the baklava, Brian gave a presentation on databases. It was intended to help folks be comfortable with how to create and use databases. Below is an outline from the meeting. Please additionally, note that they refer to:

• Database Diagram
• Database Creation Script

Meeting notes, including challenges:

What is a database?

• Way to store data
• Consists of Tables - like Excel sheets
• We use relational databases

Technologies / Tools

• Databases: MySQL / MS SQL Server / Oracle
• Integrating: PHP, AMFPHP, FlashRemoting
• vs. Excel
• vs. Access / FileMaker
• Terminal, GUIs
• DB Design Tool - Eclipse with Clay Database Modeling UI

When use database?

• Lots of data
• Need to save data
• Need to share data across users

Database Design- Facebook Example

• Identify structure of your data
• set up tables for items
  • fields (columns)
  • datatypes - int, char, varchar, text, bit, etc...
  • primary key
  • auto increment
  • create relationships - primary key / foreign key
    • 1 to many
    • many to many
• Nomalization

Working with the Data - Facebook Example

Challenges:

1. Get all users
   1. Order by first name
   2. Order by first name then last name
   3. Get all 'Matthews'
   4. Get all with a first name that begins with 'M'
   5. Get all with a first name or last name that begins with 'M'
2. Update a user
3. Insert a user
4. Quizzes
   1. Get all Quizzes
   2. Get all Quiz Questions
   3. Get all Quiz Question Answers
   4. Get all Quiz Question Answers for an Individual
5. Interests
   1. Users and their Interests
   2. Get all users with similar interests
6. Games
   1. Get all high scores
   2. Generate a high score list with just the top scores for each game
7. Friends
   1. Get a user's friends

URLs

• MySQL
• PHP MySQL Commands
• CocoaMySQL
• Eclipse
• Azzurri Clay Database Modeling

Challenge Answers

1. Get all users - SELECT * FROM user
   1. Order by first name - SELECT * FROM user ORDER BY firstName
   2. Order by first name then last name - SELECT * FROM user ORDER BY firstName, lastName
   3. Get all 'Matthews' - SELECT * FROM user WHERE firstName = 'Matthew'
   4. Get all with a first name that begins with 'M' - SELECT * FROM user WHERE firstName LIKE 'M%'
   5. Get all with a first name or last name that begins with 'M' - SELECT * FROM user WHERE firstName LIKE 'M%' OR lastName LIKE 'M%'
   6. Get all with a first name or last name that begins with 'M', return only first name and last name - SELECT firstName, lastName FROM user WHERE firstName LIKE 'M%' OR lastName LIKE 'M%'
2. Update a user - UPDATE user SET firstName = 'Michael' WHERE userID = 12
3. Insert a user - INSERT INTO User (userID, userName, firstName, lastName) VALUES (NULL, 'garyg', 'Gary', 'Goldberger');
4. Quizzes
   1. Get all Quizzes - SELECT * FROM quiz
   2. Get all Quiz Questions - SELECT * FROM quiz INNER JOIN quizQuestion on quiz.quizID = quizQuestion.quizID
   3. Get all Quiz Question Answers - SELECT * FROM Quiz INNER JOIN QuizQuestion on Quiz.quizID = QuizQuestion.quizID INNER JOIN QuestionAnswer ON QuizQuestion.quizQuestionID = QuestionAnswer.quizQuestionID
      Better - SELECT * FROM Quiz q INNER JOIN QuizQuestion qq on q.quizID = qq.quizID INNER JOIN QuestionAnswer a ON qq.quizQuestionID = a.quizQuestionID ORDER BY q.quizID, qq.questionNumber, a.answerLetter
   4. Get all Quiz Question Answers for an Individual - SELECT * FROM Quiz q INNER JOIN QuizQuestion qq on q.quizID = qq.quizID INNER JOIN QuestionAnswer a ON qq.quizQuestionID = a.quizQuestionID INNER JOIN UserAnswer UA ON UA.questionAnswerID = a.questionAnswerID INNER JOIN User u ON u.userID = ua.userID WHERE u.userName = 'briang' ORDER BY q.quizID, qq.questionNumber, a.answerLetter
5. Interests
   1. Users and their Interests - SELECT * FROM User u INNER JOIN User_Interest_Linker uil ON u.userID = uil.userID INNER JOIN Interest i ON i.interestID = uil.interestID
   2. Get all users with similar interests - SELECT * FROM User_Interest_Linker uil INNER JOIN user u ON u.userID = uil.userID INNER JOIN interest i ON i.interestID = uil.interestID WHERE uil.interestID IN (SELECT i.interestID FROM User u INNER JOIN User_Interest_Linker uil ON u.userID = uil.userID INNER JOIN Interest i ON i.interestID = uil.interestID WHERE u.userName = 'briang') AND u.userName != 'briang'
6. Games
   1. Get all high scores - SELECT * FROM user u INNER JOIN HighScore hs ON u.userID = hs.userID INNER JOIN Game g ON g.gameID =hs.gameID
   2. Generate a high score list with just the top scores for each game - SELECT g.name, u.firstName, u.lastName, max(hs.highScore) FROM user u INNER JOIN HighScore hs ON u.userID = hs.userID INNER JOIN Game g ON g.gameID =hs.gameID GROUP BY g.gameID
7. Friends
   1. Get a user's friends - SELECT u2.firstName, u2.lastName FROM user u1 INNER JOIN Friend f on f.userID1 = u1.userID INNER JOIN user u2 ON f.userID2 = u2.userID WHERE u1.userName = 'briang' ORDER BY u2.firstname, u2.lastName