Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2008
    Posts
    2

    Unanswered: Database Schema-lost, can i retrive it?

    Hi guys,

    I am a newly hired intern at a company and they have had a database for a long time but never actually needed to retrive any information from database . now They are in real need to do some query and this is where i come in.

    They have lost their Database Schema . They dont have ER diagram. It is an oracle database and i was able to connect MS access using ODBC and the database is huge. one table has like 70,000 records and there are more than 100 table with atlease 15 attributes in average in each table.

    Now how do i figure out what table has the field or attribute that i need and how do i know the relationship between tables.

    Is there any way to find relationship between tables or find schema or ER Diagram. is there any command in SQL or Access or anyother database tools that helps to achieve this?

    i need you help. please

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://download.oracle.com/docs/cd/B...b14220/toc.htm
    You'd benefit from reading the manual listed above.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by raju_boise
    I am a newly hired intern at a company and they have had a database for a long time but never actually needed to retrive any information from database . now They are in real need to do some query and this is where i come in.
    From the sound of it, you're probably not qualified to fix this. This is not to say you're at fault: it isn't the sort of job a responsible manager would assign to the FNG.

    They have lost their Database Schema . They dont have ER diagram. It is an oracle database and i was able to connect MS access using ODBC and the database is huge. one table has like 70,000 records and there are more than 100 table with atlease 15 attributes in average in each table.
    They don't have the documentation? So now you're just supposed to guess at what it means? You'd be better off if the whole database was lost.

    Now how do i figure out what table has the field or attribute that i need and how do i know the relationship between tables.
    As the joker above suggested, there are commands and they're explained perfectly well in the manual. But, there's a big problem as I'll explain below.

    Is there any way to find relationship between tables or find schema or ER Diagram. is there any command in SQL or Access or anyother database tools that helps to achieve this?
    In terms of what Oracle knows, yes. There are plenty of tools that will let you document the relationships as the DBMS sees it. Do a search for "oracle database schema reports" or something like that.

    In terms of determining what your business rules are, the answer is that there is absolutely no way it can be done without hours of tedious labor by a skilled practitioner. For any non-trivial table, it's impossible to determine the English predicate by simply inspecting the table header. You have to reverse engineer this by carefully parsing relevant queries, reports and trying to glean patterns from the current data set. If there are custom apps, you'd have to sift through the source to them.

    Now, can you hack a query together without understanding the underlying structure? Sure. But a DBMS is a machine, so if you feed it a garbage query, even if that garbage happens to be correct SQL, it will give you a garbage answer. If you certify that your garbage answer is correct, you might be exposing yourself to liability.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://schemaspy.sourceforge.net/

    SchemaSpy is a Java-based tool (requires Java 1.4 or higher) that analyzes the metadata of a schema in a database and generates a visual representation of it in a browser-displayable format. It lets you click through the hierarchy of database tables via child and parent table relationships. The browsing through relationships can occur though HTML links and/or though the graphical representation of the relationships. It's also designed to help resolve the obtuse errors that a database sometimes gives related to failures due to constraints

    I realize that I just handed a loaded pistol to a child, so be careful what you wish for. Now you'll be expected to produce some useful results.

    The whole database relationships can be seen via all the DBA_% views.
    SELECT VIEW_NAME FROM DBA_VIEWS WHERE VIEW_NAME LIKE 'DBA%';
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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