Invalid SQL: INSERT INTO `swsessions` (`sessionid`, `ipaddress`, `lastactivity`, `useragent`, `isloggedin`, `sessiontype`, `typeid`, `dateline`, `status`) VALUES('46b4bce5e852429377ddc26d900c7b96', '54.81.154.223', '1498227753', 'CCBot/2.0 (http://commoncrawl.org/faq/)', '0', '40', '0', '1498227753', '0'); (The table 'swsessions' is full)
Warning: Cannot modify header information - headers already sent by (output started at C:\Inetpub\support.inm.com\includes\functions.php:410) in C:\Inetpub\support.inm.com\includes\functions.php on line 1612
Resolving relations in a V12 database - Support Center
inm logo
Corporate Website | Contact | Store | Support | Login  
     


Knowledgebase
View categorized listing of all common frequently asked questions.
Downloads
View our categorized library of downloads for all necessary manuals, software, etc.
 Article Options
Support Center » Knowledgebase » INM V12 Database for Director » Resolving relations in a V12 database
Support Page Banner
 Resolving relations in a V12 database
Product:Platform:Area:Version:
INM V12 Database for Director Windows, Macintosh General database and table use All

Summary

A relational database stores data in two or more tables and establishes links between records. (In exceptional cases, relations can be established from a table's records onto records of the same table). For example, if you are tracking students' scores, you would have two tables: one to identify students, and one to log tests results.

Example

Here is a typical relational database structure for student tracking:
[TABLE]
Students
[FIELDS]
ID integer indexed
LastName string indexed
FirstName string
Email string
Faculty string

[TABLE]
Scores
[FIELDS]
StudentID integer indexed
TestID integer indexed
Score integer indexed
TimeSpent integer

Table Students and Scores are related to each other through their respective ID and StudentID fields. A shorthand notation to express this is:

Students::ID -> Scores::StudentID
(It is a 1-N relation)

A "1-N" relation means that many records in the Scores table can be related to a single record in the Students table, and that to each record in the Scores table is related to exactly one record in the Students table. This is called a One-To-Many relation. Here is an example of what the tables could look like:

ok

If you look at the relation the other way around, that is starting from the Scores table, you get a N-1, or Many-to-One relation noted:

Scores::StudentID -> Students::ID
(It is a N-1 relation)

Assume that we add a Tests table to the database to store the description of each test.
[TABLE]
Tests
[FIELDS]
ID integer indexed
Title string indexed
Topic string indexed
Author string
CreationDate date
LastUpdateDate date

Field TestID of table Scores refers to field ID of table Tests. This is another Many-to-One relation:

Scores::TestID -> Tests::ID
(N-1 relation)

ok

The structure of the overall database now contains three tables and can be represented by the following Entity-Relation Diagram.

ok

Tables Students and Tests are said to be in a Many-to-Many relation.

In this example, we were lucky enough to have a Scores tables that naturally links Students and Tests, but in many cases, creating a Many-to-Many relation is not obvious: you often need to create a fake table that only contains the IDs of both sides' entities. Such a table is called an Associative Table: It's sole purpose is to put other tables in relation with each other.

INM V12 Database does not contain a language that automatically resolves relations between tables. Instead, it relies on Lingo to do so.

One-to-many relation

In the precedent example, resolving a "One-to-Many" relation from the Students table is something like "for a given student's last name, select all the records in table Scores that a related to it". The following script performs this operation:

-- first locate record of student who's last name is LName mSetCriteria(gTStudents, "LastName", "=", LName)
mSelect(gTStudents)
-- get student's ID
sID = mGetField(gTStudents, "ID")
-- look for score records where StudentID is sID mSetCriteria(gTScores, "StudentID", "=", sID)
mOrderBy(gTScores, "Score") -- sorting is optional
mSelect(gTScores)

At the end of this script, gTScores'selection contains LName's scores sorted lowest to highest.

Example: if LName was assigned the string "Cartman" in the above example, after the execution of the above script, table gTScores' selection would contain:

ok

Many-to-one relation

Resolving a "Many-to-One" relation would be something like "for a given score record, which student does it belong to?". The script that answers this question is:

-- first get sID of Score record
sID = mGetField(gTScores, "StudentID")
mSetCriteria(gTStudents, "ID", "=", sID)
mSelect(gTStudents)

At the end of this script, gTStudents' current record is the one gTScores's current record is related to.

Example: if the current record in table gTScores was

ok

the corresponding record in table gStudents would be

ok

Another more complex Many-to-One relation resolving question would be "list the last names of all the students who scored 75 or higher at test number 1081". It can be answered by the following script:

-- first locate all Scores records that match criteria mSetCriteria(gTScores, "TestID", "=", 1081)
mSetCriteria(gTScores, "AND", "Score", ">=", 75)
mSelect(gTScores)
-- loop through all Student Ids and append them to member "result"
put EMPTY into field "result"

repeat with i = 1 to mSelectCount(gTScores)mGo(gTScores, i)
ID = mGetField(gTScores, "StudentID")
mSetCriteria(gTStudents, "ID", "=", sID)
mSelect(gTStudents)
put mGetField(gTStudents, "LastName")&RETURN after field "result"
end repeat

Example: if we run this script on the above example, we would get:
Cartman
Broslowsky

This script lists the last names of all the students that match the specified criteria in a Director member, in contrast to the previous script which leaves them in an INM V12 Database selection. Although an INM V12 Database selection can be easily dumped to a Director field, the opposite is not true.

Thus, if you need to further manage the list of last names created above, you must set the current record to the one that matches a specific last name's and then perform the required operation. This is sometimes called the lazy approach, whereby a piece of data is accessed only when it is needed (as opposed to processing data before it is actually needed, which may yield faster results, but at a higher pre-processing overhead). INM V12 Database's high-speed data search and retrieval routines enable you to implement the lazy approach without a performance handicap.

Many-to-many relation

Resolving Many-to-Many relations is much more complex than resolving other types of relations. Even powerful query languages such as SQL cannot perform this operation in a simple way.

A typical Many-to-Many relation resolving question in the above example would be "list the last names of all the students who took Mrs. Crabtree's tests". Assuming that table Tests possibly contains zero, one or more tests authored by Mrs. Crabtree, we would run the following script:

-- find all students who took one of Mrs. Crabtree's tests
-- first locate all Tests authored by Mrs. Crabtree
mSetCriteria(gTests, "Author", "=", "Crabtree")
mSelect(gTests) put EMPTY into field "result"
-- loop through each test created by Mrs. Crabtree

repeat with i = 1 to mSelectCount(gTests) mGo(gTests, i)
set tID = mGetField(gTests, "ID")
mSetCriteria(gTScores, "testID", "=", tID)
mSelect(gTScores)
-- loop through student IDs and retrieve name
repeat with j = 1 to mSelectCount(gTScores)
mGo(gTScores, j)
SetCriteria(gTstudents, "ID", "=", ) mSelect(gTstudents)
put mGetField(gTStudents, "LastName")
&RETURN after field "result"
end repeat
end repeat

For example, assuming that Mrs. Crabtree is the author of both the Trigonometry and the Rocket Science courses, running this script on the above example would yield the following result:

Cartman
Broslowsky
McCormick
Broslowsky

A shortcoming of this script is it's inability to sort the results, or to remove duplicates from the results. A possible work-around this limitation consists in creating an additional field named Marker in table Students and, instead of immediately listing all last names in field "result", setting the Marker field of found records to 1. At the end, just find all marked records in table Students. Of course, this requires the database to be on a writeable volume.

-- find all students who took one of Mrs. Crabtree's tests
-- and list their last names in alphabetic order, without duplicates
-- first locate all Tests authored by Mrs. Crabtree
mSetCriteria(gTests, "Author", "=", "Crabtree")
mSelect(gTests) put EMPTY into field "result"
-- loop through each test created by Mrs. Crabtree

repeat with i = 1 to mSelectCount(gTests)
   mGo(gTests, i) set tID = mGetField(gTests, "ID")    
   mSetCriteria(gTScores, "testID", "=", tID)
   mSelect(gTScores)
   -- loop through student IDs and retrieve name
   repeat with j = 1 to mSelectCount(gTScores)
       mGo(gTScores, j)
       sID = mGetField(gTScores, "StudentID")
       mSetCriteria(gTstudents, "ID", "=", )
       mSelect(gTstudents)
       -- mark the found record
       mEditRecord(gTstudents)
       mSetField(gTstudents, "Marker", 1)
       mUpdateRecord(gTstudents)
    end repeat
end repeat



Article Details
Article ID: 110
Created On: 26 Sep 2006 02:37 PM

 This answer was helpful  This answer was not helpful

inm general footer
Services Xtras Go Products Support Gallery Store Download About Us Contact Newsroom