Recent Pages

Commands at a glance
edittable: trivial tabl ...
indextable: generate ta ...
NoSQL Utilities
Text Formatting Rules
Site SandBox
Change Management
Wiki Editor's How-To
Site Terms of Use
Site Copyright


NoSQL Home
Table of Contents
Wiki Editor's How-To
Text Formatting Rules
Wiki SandBox on Twitter


Google Ads

Please Support NoSQL !


User ID

Campaigns petition banner

Database Schema

A Database Schema is a set of two files containing certain pieces of meta-information regarding the tables that comprise a NoSQL database. Such two files can take any names, but I will refer to them as the file schema and the field schema. Such schema files are just like any other NoSQL table, and can be manipulated with all the standard NoSQL commands and operators. In the explanations that follow I will make use of an hypothetical "school management" database. Our school has courses, and each course is followed by a certain number of students. We will therefore have three tables in the database:

  • the course table, containing the list of available courses:
  Course  Credit  Room   Day         Time   Teacher
  ------  ------  -----  ----------  -----  -------
  chem-1       2  HA-18  2003-10-12  14:00        1
  cs-101       3  DB-1   2003-10-11  10:00        2
  econ-1       1  RB-8   2003-10-10  09:00        2
  his-10       3  HA-18  2003-11-12  15:00        3
  • the student table, that lists the students in our school:
  Student  First    Last        Year    Phone
  -------  -------  ----------  ----  -------
        1  Thomas   Jefferson      1     9876
        2  George   Washington     1    87654
        3  Ronald   Reagan         1  7654210
        4  William  Clinton        1  6542109
  • the course_student table, that tells us which students are enrolled into which courses:
  Course  Student
  ------  -------
  chem-1        1
  chem-1        2
  chem-1        3
  cs-101        2
  cs-101        4
  econ-1        1
  econ-1        2
  econ-1        3
  his-10        1

And here's what a file schema table for our "school management" database will look like, as displayed by the NoSQL justify command:

  Table           Path              Edit
  --------------  ----------------  ----------------------
  course          ./course
  course_student  ./course.student  ./course.student-edits
  student         ./student

Such a schema shows a typical many-to-many database relation (one student can take many courses and one course involves many students). Here's what the table columns mean:

this is a symbolic name that is given to the associated table, and it is often the same name that is given to that particular database entity when the database is designed. The Table field must be unique across the whole database.
this is the actual path -- either relative or absolute -- in the file-system, leading to the physical file that corresponds to the associated Table entry. In the sample schema above, relative paths are used, and to make clear that they are paths they were prepended by ./, which is optional for those files that are located in the current working directory, like in this particular example.
path -- either relative or absolute -- of the associated edit buffer, if any. See Big tables for more information.

The second Database Schema file is the field schema. It contains the list of all column names that comprise a database, associated with the table names they belong to. The table names are those listed in the file schema previously explained. Here is the field schema table of our sample school management database:

  Column   Table           Flags  Match         Parent  Insert     Delete
  -------  --------------  -----  ------------  ------  ---------  --------
  Course   course          K                                       Restrict
  Course   course_student  k                    course  Dependent
  Credit   course
  Day      course          h      ^[1-3][0-9]$
  First    student
  Last     student
  Phone    student
  Room     course
  Student  course_student  k                    student Dependent
  Student  student         Kb                                      Restrict
  Teacher  course
  Time     course          h      ^[0-9:]+$
  Year     student

As you can see, fields that occur in multiple tables are listed multiple times, one time per table, and thus the fieldlist table key is the concatenation of Column and Table values.

The Database Schema files can contain additional, application-specific columns beside the basic ones explained above (i.e. to add annotations or anything else); such columns will not be read by NoSQL schema-related operators but their presence will be tolerated.

The Flags field in the fieldlist table provides further properties concerning each field, namely:

hidden field. Defining a field as hidden means that that field must be dropped from the result of NoSQL Brokering Language queries. The input tables may contain sensitive information in some of their fields, like passwords or other data that we do not want to expose. In the above example the fields Time and Day are flagged as hidden, meaning that we do not want them to appare on output.
partial key field. The full table key is made by the concatenation of multiple (usually two) such fields.
primary key field.
the field may not be null. This is implied for key fields, both primary and partial.
linear search on first (leftmost) column.
linear search by column name.
binary search on first (leftmost) column (the table is assumed to be sorted on such field).
indexed search on single-column index.
indexed search on multiple-column indexes; all fields having the same number are assumed to be part of the same multiple-column index file (e.g. table._x.Column1.Column2...). At most 9 multicolumn index files are supported for each table, but each index can be on as many columns as desired.

Each Flags field may contain multiple flags, such as kh, Kh, etc.

The Match field in the fieldlist table provides a crude method of datatype enforcement. The field is optional and may contain any valid AWK regular expression that the content of the associated table column must match to be considered valid. If any flags in the Flags column imply any form of data-typing they take precedence over the Match field. For instance, if one of the flags is N then NoSQL operators that read the database schema table MUST check for the field not to be null first, and then apply the pattern-matching specified by Match, if any. In case of conflicts between Flags and Match the former MUST take over and the second MUST be ignored by the relevant operators. Likewise, if N is not listed in Flags the field content MUST be considered valid if either it is null or it matches the specified pattern.

The Parent field indicates that the column is a foreign-key of the specified table.

The Insert field, which also applies only to foreign-keys, specifies whether the presence of the associated primary key is mandatory or not when a new value is inserted into the column being described. If the field contains the keyword "Dependent" then the associated primary key MUST exist, or an insert declaation will trigger an exception in the 'constraint' operator.

The Delete field, which applies only to primary keys, tells what to do if the primary key being described is deleted. If the field contains the keyword "Restrict" then the primary key may not be deleted until there are foreign keys referring back to it from other tables.

The Parent, Insert and Delete schema fields are used by the 'constraint' NoSQL operator. See contraint's help text for more information.

A copy of the sample database used in the above examples can be downloaded from here.

Trackbacks (2) | New trackback | Comments (0) | Print

This Web Site is Copyright © 2007,2008,2009,2010 Carlo Strozzi, Some Rights Reserved
site map | recent changes | terms of use