SitePoint Sponsor

User Tag List

Results 1 to 11 of 11

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Please I need your brilliant logic

    Please get me some advised...yuor help much be appreciated!!!
    ------------------------------------------------------------------
    Given the table structure and row data below, I have some questions in my mind, sorry Im just a beginner...
    mysql> explain user_skill;
    +--------------------------+------------------+------+-----+
    | Field | Type | Null | Key |
    +--------------------------+------------------+------+-----+
    | user_skill_id | int(11) | | PRI |
    | user_skill_last_modified | timestamp(14) | YES | |
    | user_skill_date_created | datetime | YES | |
    | user_id | int(11) | YES | |
    | skill_name | char(255) | YES | |
    | skill_level | char(255) | YES | |
    | skill_usage | char(255) | YES | |
    | skill_last_used | char(255) | YES | |
    | user_skill_endorsed | tinyint(1) | YES | |
    +--------------------------+------------------+------+-----+

    mysql> *************
    +--------------------+-------------------+--------------------+
    | user_firstname | user_lastname | skill_name |
    +--------------------+-------------------+--------------------+
    | Kim | Simpson | PHP |
    | Kim | Simpson | Perl |
    | Kim | Simpson | Microsoft Word |
    | Kim | Simpson | Microsoft Access |
    | Kim | Simpson | Accounting/Billing |
    | Kim | Simpson | Java |
    | Kim | Simpson | SQL |
    | Kim | Simpson | CSS |
    | Kim | Simpson | OO Programming |
    | Kim | Simpson | Microsoft Excel |
    +--------------------+-------------------+--------------------+
    10 rows in set (0.00 sec)

    These are my questions:
    1.Assuming that the data stored in skill_name in the user_skill table might be repeated for different users, what changes would I make to the database to normalize the skill_name and reduce repeated storage? what is the structure of the new table(s).

    2.How could I Recreate the query that returned the 10 rows of data supplied. Speculate on tables that would be needed that are not shown here.

    3. Given the following query, how could it be optimized? List all assumptions:

    select c.* FROM companies AS c JOIN users AS u USING(companyid) JOIN jobs AS j USING(userid) JOIN useraccounts AS ua USING(userid) WHERE j.jobid = 123;

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Homework?

  3. #3
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    to be honest yes! can u help me?

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Well, since you're studying normalization, how would you get rid of the skill info in that first table?

  5. #5
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, I found difficult about this problem... nosebleed...well, I need to think ahead about this...If you have suggestion, then please share ur ideas...at this time my mind got crazy thinking about this....THANK YOU IN ADVANCE

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Ok, so you have two entities: USERS and SKILLS
    What relationship is there between the two entities?
    What info is there in the table about the entity SKILLS (USERS has already been normalized --> this is a hint for question number 2).

  7. #7
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    once again thank you! HOPE I can solve this....

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    If you can't solve it on your own, post your anwser to the questions I asked you, and we'll continue from there. I won't just give you the solution though, it's your homework, you should do the thinking

  9. #9
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    based on my understanding, the SKILLS will store the user id to determine the skills information of the user...hmmm just a little bit confusing...until now Im trying to figure it out what is the best explanation about this...

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by noprobz09 View Post
    the SKILLS will store the user id to determine the skills information of the user
    No, that's a bit of information that will stay in the userskills table (which will contain all info about the relation between the USERS and the SKILLS)

  11. #11
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmmm...okay I will try to study this better harder...thank you for the hints...KEEP IT UP!!! IDOL :-)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •