Thread: What is this thing that is called a "Data Dictionary?"

Many of the ERD and SQL books that have have read refer to a data dictionary.  However, I seem to
be missing the point of what it acuatally is.  IIRC, some books refer to it as a kind of document
created by a DB designer, and other suggest that it is a kind of RBDMS created metadata.

Can anyone please offer a consise description or provide a link to that describes what it is?
Also, how is this information useful to a DBA?

Thanks for the help!

Regards,
Richard Broersma Jr.

On Wednesday 02 May 2007 15:21, Richard Broersma Jr wrote:
> Many of the ERD and SQL books that have have read refer to a data
> dictionary.  However, I seem to be missing the point of what it acuatally
> is.  IIRC, some books refer to it as a kind of document created by a DB
> designer, and other suggest that it is a kind of RBDMS created metadata.
>
> Can anyone please offer a consise description or provide a link to that
> describes what it is? Also, how is this information useful to a DBA?

Hi, Richard.  I haven't seen this "term" used before.  But here is a link:

http://www.oreillynet.com/pub/a/network/2002/10/28/data_dictionary.html

Sean

AFAIK, data dictionary is just the metadata (read only set) that contains the definitions of data elements like tables, views, trigggers, SPs, roles, general DB structuring, space allocation methodology of the DB etc (i mean any data element inside the DB).

It can be viewed as a simple read only file defining the basic structure of the database actually, in lay men terms.

Also most of the times, i've observed that its hidden from the users even though its read only. Only DBA should have access to such kind of file in secured systems. Thats how we implement it in our projects out here.

Bookkeeping is just synonymous to a data dictionary that would not contain the actual data but would essentially explain the way data would be managed and how.

I hope this helps.

Thanks,
~Jas

Thanks Sean for the link, it is very helpful.  It seems like the PG equievent to Oracles data
dictionary are the pg_systemtables.
--- Sean Davis <sdavis2@mail.nih.gov> wrote:

> On Wednesday 02 May 2007 15:21, Richard Broersma Jr wrote:
> > Many of the ERD and SQL books that have have read refer to a data
> > dictionary.  However, I seem to be missing the point of what it acuatally
> > is.  IIRC, some books refer to it as a kind of document created by a DB
> > designer, and other suggest that it is a kind of RBDMS created metadata.
> >
> > Can anyone please offer a consise description or provide a link to that
> > describes what it is? Also, how is this information useful to a DBA?
>
> Hi, Richard.  I haven't seen this "term" used before.  But here is a link:
>
> http://www.oreillynet.com/pub/a/network/2002/10/28/data_dictionary.html
>
> Sean
>

Thanks Jas for the description,  it is very helpful.

Regards,
Richard Broersma Jr.

--- Jasbinder Singh Bali <jsbali@gmail.com> wrote:

> AFAIK, data dictionary is just the metadata (read only set) that contains
> the definitions of data elements like tables, views, trigggers, SPs, roles,
> general DB structuring, space allocation methodology of the DB etc (i mean
> any data element inside the DB).
>
> It can be viewed as a simple read only file defining the basic structure of
> the database actually, in lay men terms.
>
> Also most of the times, i've observed that its hidden from the users even
> though its read only. Only DBA should have access to such kind of file in
> secured systems. Thats how we implement it in our projects out here.
>
> Bookkeeping is just synonymous to a data dictionary that would not contain
> the actual data but would essentially explain the way data would be managed
> and how.
>
> I hope this helps.
>
> Thanks,
> ~Jas
>
> On 5/2/07, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> >
> > Many of the ERD and SQL books that have have read refer to a data
> > dictionary.  However, I seem to
> > be missing the point of what it acuatally is.  IIRC, some books refer to
> > it as a kind of document
> > created by a DB designer, and other suggest that it is a kind of RBDMS
> > created metadata.
> >
> > Can anyone please offer a consise description or provide a link to that
> > describes what it is?
> > Also, how is this information useful to a DBA?
> >
> > Thanks for the help!
> >
> > Regards,
> > Richard Broersma Jr.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> >                http://www.postgresql.org/docs/faq
> >
>

pg_systemtables is PG equivalent of systables in sql server

~Jas

I just went to wikipedia.  That's what they say - a data dictionary is
metadata, and they say that within a DBMS it's represented as a set of
read-only tables and views.
http://en.wikipedia.org/wiki/Data_dictionary

They seem to indicate it's something that you build, though, not
something generated by the RDBMS.

Innnteresting...

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Richard Broersma
Jr
Sent: Wednesday, May 02, 2007 3:29 PM
To: Jasbinder Singh Bali
Cc: Novice Postgresql-list
Subject: Re: [NOVICE] What is this thing that is called a "Data
Dictionary?"

Thanks Jas for the description,  it is very helpful.

Regards,
Richard Broersma Jr.

--- Jasbinder Singh Bali <jsbali@gmail.com> wrote:

> AFAIK, data dictionary is just the metadata (read only set) that
> contains the definitions of data elements like tables, views,
> trigggers, SPs, roles, general DB structuring, space allocation
> methodology of the DB etc (i mean any data element inside the DB).
>
> It can be viewed as a simple read only file defining the basic
> structure of the database actually, in lay men terms.
>
> Also most of the times, i've observed that its hidden from the users
> even though its read only. Only DBA should have access to such kind of

> file in secured systems. Thats how we implement it in our projects out
here.
>
> Bookkeeping is just synonymous to a data dictionary that would not
> contain the actual data but would essentially explain the way data
> would be managed and how.
>
> I hope this helps.
>
> Thanks,
> ~Jas
>
> On 5/2/07, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> >
> > Many of the ERD and SQL books that have have read refer to a data
> > dictionary.  However, I seem to be missing the point of what it
> > acuatally is.  IIRC, some books refer to it as a kind of document
> > created by a DB designer, and other suggest that it is a kind of
> > RBDMS created metadata.
> >
> > Can anyone please offer a consise description or provide a link to
> > that describes what it is?
> > Also, how is this information useful to a DBA?
> >
> > Thanks for the help!
> >
> > Regards,
> > Richard Broersma Jr.
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> >                http://www.postgresql.org/docs/faq
> >
>


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend


This email and any files transmitted with it are confidential and intended solely for the use of the individual or
entityto whom they are addressed. If you have received this email in error please notify the sender and delete it.
Pleasenote that any views or opinions presented in this email are solely those of the author and do not necessarily
representthose of the company.  
No employee or agent is authorized to conclude any binding agreement on behalf of Visa Lighting with another party by
emailwithout express written confirmation by an authorized representative of the Company. 
Finally, the recipient should check this email and any attachments for the presence of viruses. The company accepts no
liabilityfor any damage caused by any virus transmitted by this email.  

I think data dictionary is automatically build by the DB.
Don't think that its a manual task.
Meta data is always build up automatically by the system intelligently.
Correct me if I'm wrong.

~Jas

 I think you're right.

I also think that the equivalent in PGSQL may be the information_schema.
That's what MySQL is trumpeting that they've added to their structure
that is the same thing as a data dictionary.

It makes sense - information_schema is a standard SQL thing, that was
introduced back with SQL92.  Oracle uses information_schema too but has
their own set of "data dictionary" views that apparently are more
comprehensive ways to access the database's metadata.

Looking at PGSQL's information_schema schema, it does have a lot of that
kind of thing.

I wonder if pg_catalog and pg_toast also are part of the PGSQL "data
dictionary" implementation...

________________________________

From: Jasbinder Singh Bali [mailto:jsbali@gmail.com]
Sent: Wednesday, May 02, 2007 4:00 PM
To: Jim Stalewski
Cc: Richard Broersma Jr; Novice Postgresql-list
Subject: Re: [NOVICE] What is this thing that is called a "Data
Dictionary?"


I think data dictionary is automatically build by the DB.
Don't think that its a manual task.
Meta data is always build up automatically by the system intelligently.
Correct me if I'm wrong.

~Jas


On 5/2/07, Jim Stalewski <JStalewski@visalighting.com> wrote:

    I just went to wikipedia.  That's what they say - a data
dictionary is
    metadata, and they say that within a DBMS it's represented as a
set of
    read-only tables and views.
    http://en.wikipedia.org/wiki/Data_dictionary

    They seem to indicate it's something that you build, though, not
    something generated by the RDBMS.

    Innnteresting...

    -----Original Message-----
    From: pgsql-novice-owner@postgresql.org
    [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Richard
Broersma
    Jr
    Sent: Wednesday, May 02, 2007 3:29 PM
    To: Jasbinder Singh Bali
    Cc: Novice Postgresql-list
    Subject: Re: [NOVICE] What is this thing that is called a "Data
    Dictionary?"

    Thanks Jas for the description,  it is very helpful.

    Regards,
    Richard Broersma Jr.

    --- Jasbinder Singh Bali <jsbali@gmail.com> wrote:

    > AFAIK, data dictionary is just the metadata (read only set)
that
    > contains the definitions of data elements like tables, views,
    > trigggers, SPs, roles, general DB structuring, space
allocation
    > methodology of the DB etc (i mean any data element inside the
DB).
    >
    > It can be viewed as a simple read only file defining the basic
    > structure of the database actually, in lay men terms.
    >
    > Also most of the times, i've observed that its hidden from the
users
    > even though its read only. Only DBA should have access to such
kind of

    > file in secured systems. Thats how we implement it in our
projects out
    here.
    >
    > Bookkeeping is just synonymous to a data dictionary that would
not
    > contain the actual data but would essentially explain the way
data
    > would be managed and how.
    >
    > I hope this helps.
    >
    > Thanks,
    > ~Jas
    >
    > On 5/2/07, Richard Broersma Jr < rabroersma@yahoo.com> wrote:
    > >
    > > Many of the ERD and SQL books that have have read refer to a
data
    > > dictionary.  However, I seem to be missing the point of what
it
    > > acuatally is.  IIRC, some books refer to it as a kind of
document
    > > created by a DB designer, and other suggest that it is a
kind of
    > > RBDMS created metadata.
    > >
    > > Can anyone please offer a consise description or provide a
link to
    > > that describes what it is?
    > > Also, how is this information useful to a DBA?
    > >
    > > Thanks for the help!
    > >
    > > Regards,
    > > Richard Broersma Jr.
    > >
    > > ---------------------------(end of
    > > broadcast)---------------------------
    > > TIP 3: Have you checked our extensive FAQ?
    > >
    > >                 http://www.postgresql.org/docs/faq
<http://www.postgresql.org/docs/faq>
    > >
    >


    ---------------------------(end of
broadcast)---------------------------
    TIP 6: explain analyze is your friend


    This email and any files transmitted with it are confidential
and intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the sender and delete it. Please note that any views or opinions
presented in this email are solely those of the author and do not
necessarily represent those of the company.
    No employee or agent is authorized to conclude any binding
agreement on behalf of Visa Lighting with another party by email without
express written confirmation by an authorized representative of the
Company.
    Finally, the recipient should check this email and any
attachments for the presence of viruses. The company accepts no
liability for any damage caused by any virus transmitted by this email.

pg_catalog is a system SCHEMA where in you can add your own functions, operators and types.
However, you can't add your own tables to it. Funny, don't know why though but that how it is. :)
pg_catalog is indeed a part of PG data dictionary implementation.

~Jas

FWIW, in my (admittedly limited) experience, a Data Dictionary is a
metatdata entity or concept, generated by the RDBMS.  It's essentially a
single source describing the schema - tables, relationships, views,
types, functions, triggers, stored procedures, etc.  The equivalent in
PGSQL *is* the schema, based on looking at the structure of a DB using
PGADMINIII.

The Data Dictionary can be stored as a separate entity within an RDBMS's
structure, or it can be stored within the schema itself, as I believe it
to be in PGSQL.

Please correct me if I'm wrong... ;)

The DBA would more likely use the Data Dictionary as a tool to help
document the database, generate ERD's, etc. as opposed to the DBA
creating the Data Dictionary as one form of documenting the database.

I should go dig up my old dusty SQL97 manual - I think data dictionary
may be mentioned there.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Richard Broersma
Jr
Sent: Wednesday, May 02, 2007 3:29 PM
To: Jasbinder Singh Bali
Cc: Novice Postgresql-list
Subject: Re: [NOVICE] What is this thing that is called a "Data
Dictionary?"

Thanks Jas for the description,  it is very helpful.

Regards,
Richard Broersma Jr.

--- Jasbinder Singh Bali <jsbali@gmail.com> wrote:

> AFAIK, data dictionary is just the metadata (read only set) that
> contains the definitions of data elements like tables, views,
> trigggers, SPs, roles, general DB structuring, space allocation
> methodology of the DB etc (i mean any data element inside the DB).
>
> It can be viewed as a simple read only file defining the basic
> structure of the database actually, in lay men terms.
>
> Also most of the times, i've observed that its hidden from the users
> even though its read only. Only DBA should have access to such kind of

> file in secured systems. Thats how we implement it in our projects out
here.
>
> Bookkeeping is just synonymous to a data dictionary that would not
> contain the actual data but would essentially explain the way data
> would be managed and how.
>
> I hope this helps.
>
> Thanks,
> ~Jas
>
> On 5/2/07, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> >
> > Many of the ERD and SQL books that have have read refer to a data
> > dictionary.  However, I seem to be missing the point of what it
> > acuatally is.  IIRC, some books refer to it as a kind of document
> > created by a DB designer, and other suggest that it is a kind of
> > RBDMS created metadata.
> >
> > Can anyone please offer a consise description or provide a link to
> > that describes what it is?
> > Also, how is this information useful to a DBA?
> >
> > Thanks for the help!
> >
> > Regards,
> > Richard Broersma Jr.
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> >                http://www.postgresql.org/docs/faq
> >
>


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend


This email and any files transmitted with it are confidential and intended solely for the use of the individual or
entityto whom they are addressed. If you have received this email in error please notify the sender and delete it.
Pleasenote that any views or opinions presented in this email are solely those of the author and do not necessarily
representthose of the company.  
No employee or agent is authorized to conclude any binding agreement on behalf of Visa Lighting with another party by
emailwithout express written confirmation by an authorized representative of the Company. 
Finally, the recipient should check this email and any attachments for the presence of viruses. The company accepts no
liabilityfor any damage caused by any virus transmitted by this email.  

am  Fri, dem 04.05.2007, um 12:48:24 +0100 mailte Oliveiros Cristina folgendes:
> Hello, list.
>
> I have a CSV file with two values/row and I would like to import it into a
> table who has two columns as well.
>
> Is there an easy and expedited way to do this with Postgres administration tool
> (PgAdmin)? I can do a program that parses the file and INSERTs the values on
> table, but I suspect that there is an easy way and that it is not worth the
> effort.

I'm not familiar with PGAdmin, but with console you can use a way like
this to insert the contents of a CSV-file into a database table on a
remote host:

cat <file.csv> | ssh <host> "psql -U <user> <database> -c 'copy <table> from stdin;'"



Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

I'm not familiar with PGAdmin, but with console you can use a way like
this to insert the contents of a CSV-file into a database table on a
remote host:

cat <file.csv> | ssh <host> "psql -U <user> <database> -c 'copy <table> from stdin;'"

You can do the same from pgAdmin, you just need to upload the file to the server first, then do something like:
    COPY table1 FROM '/tmp/file1.txt';

Remember to write the path as the SERVER will see it, not as the client sees it!

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.

Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments

Hello, Phillip.
Hello, all.

Thank you for your email, it worked nicely.

Now I have another question not related to this one, and I am not sure if this is the right list or if there is a specialized list.
It has to do with Npgsql, the C# interface to postgres.

I have a table that looks like the following
t_sites(IDSite,......,tName )

Suppose I have a row on this table e.g.
120120 ....... www.record.pt\

If , on PgAdmin, I query
SELECT IDSite
FROM t_sites
WHERE "tName"='www.record.pt\\\\'

I get nothing, as expected, because the string has two final backslashes, not one, as the record on table.

But if I try to do it on C# it returns me the 120120. (??) It shouldn't return nothing as well.
The program is as follows :

strSite = "www.record.pt\\\\"

strQuery = "SELECT \"IDSite\" FROM t_sites " + "WHERE \"tName\" = '" + strSite + "'";


cSqlCommand = new NpgsqlCommand(strQuery, m_cConnectionToDb);
try
{
    i_int_rec.IDSite = (int)cSqlCommand.ExecuteScalar();
}//try
catch
{
/* This code is not executed */
}//catch

i.e. the ExecuteScalar() Method returns one key, the www.record.pt\ one, 120120, but it should return null, because the result should be empty. www.record.pt\ != www.record.pt\\  , is that not true?
Does the ExecuteScalar() method remove additional backslashes or kinda?

Actually I did on C# a small test program that issues the
SELECT IDSite,tName
FROM t_sites
query against my postgres database and then lists the results on screen, and the fact is that www.record.pt\\ does not appear which is correct.
Still, ExecuteScalar() behaves as the record was there and returns the primary key of ...the www.record.pt\ which is not the right one!

Can anyone please kindly indicate what am I doing wrong on the program?

Any help/advice deeply appreciated

Warm Regards,
Oliveiros

Lisboa,
Portugal

"Oliveiros Cristina" <oliveiros.cristina@gmail.com> writes:
> Suppose I have a row on this table e.g.
> 120120 ....... www.record.pt\

> If , on PgAdmin, I query
> SELECT IDSite
> FROM t_sites
> WHERE "tName"='www.record.pt\\\\'

> I get nothing, as expected, because the string has two final backslashes,
> not one, as the record on table.

> But if I try to do it on C# it returns me the 120120. (??) It shouldn't
> return nothing as well.
> The program is as follows :

> strSite = "www.record.pt\\\\"

I don't know C# well, but if it's at all like C then the string that
is represented by this literal has only two backslashes, and when it
gets to the backend that reduces to one backslash, so it matches.

            regards, tom lane

Hello, Tom,

Thank you for your email

 Yes, what you say makes all sense, but I am not 100% sure if the program looks exactly as I wrote, I did it by memory. Anyway, I'll check it out tomorrow at office with my colleague and if more doubts arise, I'll get back to list.

Thank you for your prompt reply

Best,
Oliveiros