Re: [libreoffice-users] Re: LO 4.0: intrinsic database: relations only between primaries keys?

On 02/11/2013 05:31 PM, Uwe Brauer wrote:
> > > "Dan" == Dan Lewis <elderdanlewis@gmail.com> writes:
>     > `
>     > On 02/11/2013 09:18 AM, Uwe Brauer wrote:
>     >>
>     >> Uwe Brauer
>     >      How do you define an "intrinsic database"? I do not understand
>     >      the term.
>
> Well in my understanding there are two possibilities,
>
>      -  either Base can connect to an external database such as mysql via
>         a number of drivers or
>
>      -  it uses a database it ships (I think it is based in
>         hsql). Because of a lack of a word I denoted this as intrinsic.
>
>     >      Primary-foreign key pairs are created when a table is
>     > normalized. (There several levels of normalized tables.) These pairs
>     > are based upon the relationships between the fields of the original
>     > table.
>     >      So, you probably should put all the fields of the two tables into
>     > a single table with the exception of any fields that belong to both
>     > tables. For example the single table contains only one primary key
>     > ("ID") and one field named "Notes". Then look for any fields (other
>     > than "ID") that determine the values of other fields. (In other words,
>     > begin to normalize the table.) Without knowing all of the fields that
>     > you are using, it would be difficult to know what fields these might
>     > be.
>     >      FYI, very seldom will two tables have a 1:1 relationship (linking
>     > the primary keys of the two tables). Very likely, yours doesn't
>     > either. Most often, these relationships involve a primary key of one
>     > table and a foreign key of the other.
>
>
> I am not entirely sure I understand: I set up a new base, no the primary
> keys are not any existing field of the table (autovalue is On)
>
>
> My tables are the following (they are just toy models in order to
> understand the relation ship concept)
>
> Task:
> Name Type
> Id   (generated by OO as a primary key)
> Task Id Integer
> Description Memo Longchar
> Notes Memo Longchar
>
>
> Authors
> Name Type
> Id (generated by OO as a primary key)
> Author Id Integer
> FirstName Memo Longchar
> Nationality Notes Memo Longchar
>
> Now I try to make a relation between the two tables, and in the GUI I
> select the field Task Id and Author id in the other table, since both
> fields have the same type.
>
> I receive  the following error:
>
>
> SQL Status: 23000
> Error code: -177
>
> Integrity constraint violation - no parent 1, table: Authors in
> statement [ALTER TABLE "Authors" ADD  FOREIGN KEY ("AuthorID")
> REFERENCES "Tasks" ("ID")]
>
> Now the gui allow me to add more fields so I add both primary keys, gain
> an error:
> SQL Status: S0011
> Error code: -170
>
> Primary or unique constraint required on main table: "Tasks" in
> statement [ALTER TABLE "Authors" ADD FOREIGN KEY ("AuthorID","ID")
> REFERENCES "Tasks" ("TaskID","ID")]
>
> The only one which works, using the GUI is between the primaries keys!!!
>
> Did you try it out your self and you are able to set more relations
> between the tables?
>
> As I said, in another mail,  when I connect the data base to mysql via
> ODBC then I can set more relations, but they are not saved..
>
> Uwe
      Some basics about creating a database:
1) You begin with data that you want to use for some purpose or purposes.
2) Then you design the database to organize this data so that it will be 
useful.

For example: an address database:
     In the beginning, the database consists of information such as 
names, addresses, email address, phone numbers, etc.
     The first thing you would want to do is to organize this data so 
that a name is linked to its address, its email address, its phone 
numbers, etc. Now you have rows of data, each one of them containing 
information about a single person (a relationship exists between the 
data for each row). Each of these pieces of information is a field.
     If you look at these rows, you will see that they also contain 
some things in common. Several rows can contain data about your friends, 
others are about your relatives, and others are about companies you do 
business with. You probably know of other possibilities. So, these rows 
can be organized into groups based upon what they have in common. (This 
too is a relationship.)
     The next thing you should do is to further organize these rows in 
each one of these groups to make them more useful. You could form a 
table using these as the column headings: name, address, email address, 
and phone number. ( Now you have a table for each group of rows. So, if 
you want data about a particular contact, you can go to the table that 
contains it. If you want to look at the data for a relative, you go to 
the table containing all of your relatives.
     This is basically how a flat database is created. Relational 
databases begin in the same way.
     At this point, each of these tables are checked to see whether 
they are "normal" or not. For example, a contact is likely to have 
multiple phone numbers or other possible multiple entries. Each of these 
have something in common (another relationship). We can remove the field 
containing multiple entries forming a new table. To keep the 
relationship between this new table with the original table, we create a 
primary-foreign key pair (the primary key is for the new table, and the 
foreign key is added to the original table in place of the fields we 
removed.) This is what is done to make a table "first normal form".
     There are several levels of a table being normal: "first normal 
form" to "fourth normal form" and beyond. "Fourth normal form" is 
considered to be the standard against which a table should be judged.
     The point being that we do not create tables with their fields and 
then define the relationship between them. We begin the the fields that 
we know we will need, combine the fields based upon relationships 
between data, and combine the fields into tables based upon 
relationships between the fields. The the tables are normalized up to 
"fourth normal form" creating new tables and modifying the old ones. 
Primary-foreign key pairs are used to define the relationship of the new 
tables and the modified old table from which the new table came.
     Hopefully this will help some.

--Dan

--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted