DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: My First Question

  1. #1
    Join Date
    Oct 2010
    Posts
    2

    My First Question

    I'm looking into becoming an Oracle DBA. I've never done DBA work, but I've worked on Oracle databases via PL/SQL for 7 years. I was recently given the following problem and need help from someone who can show me how to solve the problem. I don't just want an answer...I want to learn how to do this.

    Here's the problem:


    Create a data model to represent and persist an unlimited hierarchy of pinball machine components. Each component can be comprised of an unlimited number of other components. Any component can have an unlimited number of key/value attributes versioned by date and department.

    For example, the "Family Fun Zone" department may use version 5 of the "My Little Pony" graphic theme for the scoreboard, while the "EMEA" department uses the "David Hasselhoff" version 3 graphic theme.

    Write SQL to report on all attributes by pinball machine by department created in the last month

    Bonus: add a trigger to check for circular references.

    Thanks for your help.

    AslanBooks

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    This is a Data Modeling question, doesn't matter if physical implementation would be on an Oracle platform or any other RDBMS platform.

    Are you familar with Entity-Relationship modeling?
    Are you familiar with Data Normalization up to the third normal form?

    If not, better to start there.

    Hope this helps.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Oct 2010
    Posts
    2
    Where would I find the best explanations?

    AslanBooks

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by AslanBooks View Post
    Where would I find the best explanations?
    AslanBooks
    You need to create a self join. The classic example of a self join is the employee manager relationship, where a manager has a manager, except when he is the CEO. Hence the component table has a component_id column and a mast_comp_id column which is nullable.

    http://download.oracle.com/docs/cd/B...queries006.htm

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by AslanBooks View Post
    Where would I find the best explanations?
    Bing "ER Modeling" and "data normalization, 3NF" ... plenty of documents in the net.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool Hierarchy example(s)

    Check out some hierarchical data model examples here.

    Good luck!
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Tags for this Thread

Posting Permissions

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


Click Here to Expand Forum to Full Width