Next Meeting :: 050908 :: The Flex Challenge

Our next EMAWD meeting will be May 9th @ 2PM at FableVision (308 Congress Street).

The Flex Challenge

The topic of the meeting will be Flex. Matt Bargar will be presenting, but he needs your help.

To prove (or disprove) Flex’s abilities to be used for rapid development of rich internet applications, Matt will attempt to build a Flex application from scratch in our meeting. Please help by suggesting an application you’d like to see Matt build during the meeting. Suggestions can be useful, clever, or just plain silly.

So far, suggestions include:

• A Blog Aggregator – culls entries from your favorite blogs in one place
• Power Hour Music Mixer – see Power Hour
  
• Montage Maker – Input text, the Montage Maker will pull images from flickr to create a photographic montage

Please post your suggestions as comments here or send your suggestions directly to me (brian@fablevision.com).

-Brian

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