Defining Data Structures

Defining Data Structures

5.1. - Generalities

5.2. - Data Definition Languages (DDL)

5.3. - Graphical representations of Data Structure (Data Structure Tree)

5.4. - Data Definition in X-BASE DBMS

5.4.1. - The representation of simple Data Types

5.4.2. - Operation with Data Structures

5.1. - Generalities

When a relational database is to be implemented for a complex application, designers commonly begin by carefully designing the database schema. This involves deciding which attributes belong in each relation, choosing appropriate names for the relations and their attributes, spec 222j96c ifying the domains and data types of the various attributes, identifying the candidate keys and choosing a primary key for each relation, and specifying all foreign keys. In the next sections we assume that the design of a relational database schema has already been accomplished, and we discuss how a database user may declare the individual relations.

5.2. - Data Definition Languages (DDL)

Each relational DBMS must have a Data Definition Language (DDL) for defining the relation schemas. Most DDL-s are based on the SQL language. Here we discuss the (idealized) language components needed to declare a relation schema. The first step is to give a name to the whole relational database schema so that individual relations can be assigned to it. This is do using a statement such as the following:

DECLARE SCHEMA COMMERCIAL

The next step is to declare the domains needed for the attributes, giving each domain a name and a data type.

DECLARE DOMAIN PART-CODE TYPE CHARACTER ( 10 )

DECLARE DOMAIN PART-NAME TYPE CHARACTER ( 60 )

DECLARE DOMAIN COLOR TYPE CHARACTER ( 15 )

DECLARE DOMAIN WEIGHT TYPE FLOAT ( 6 , 2 )

DECLARE DOMAIN SUPPLIER-CODE TYPE CHARACTER ( 10 )

DECLARE DOMAIN SUPPLIER-NAME TYPE CHARACTER ( 60 )

DECLARE DOMAIN CITY  TYPE CHARACTER ( 25 )

DECLARE DOMAIN QUANTITY TYPE FLOAT ( 12 , 2 )

The declaration of the DOMAINS was considered by a lot of commercial DBMS package as superfluous. The reason can be find only in the luck of SPACE & TIME resources at a given time of data processing technology development.

The role played by the DOMAINS in relational data structure remain essential from the following reason:

it assures the INDEPENDENCE of a given DATA TYPE from its usage as ATTRIBUTE in a given RELATION

it extends the acceptance of the DATA TYPE by introducing the notion of ATTRIBUTES COMPARABILITY (this means that the Part-code and the Supplier-code, having the same DATA TYPE and LIMITS cannot arise from the same domain because it is now sense to compare the values stored in this attributes); in this manner the DATA TYPE incorporates also a deeper meaning associated by the user to the selected attributes as ENTITIES CHARACTERISTICS

it provides the main basis for the LINKAGE between the RELATIONS and in this sense for building COMPLEX RELATIONAL DATA STRUCTURES (this means that the declarations of the LINKAGES between RELATIONS can be viewed as superfluous in a Data Structure with an appropriate DOMAINS DECLARATION SECTION

After the DOMAIN DECLARATION the individuals RELATIONS can now be defined:

DECLARE RELATION PART FOR SCHEMA COMMERCIAL

ATTRIBUTES

CODE DOMAIN PART-CODE

NAME DOMAIN PART-NAME

COLOR  DOMAIN COLOR

WEIGHT  DOMAIN WEIGHT

CONSTRAINTS

PRIMARY-KEY (CODE) REFERENCES PART-CODE

DECLARE RELATION SUPPLIER FOR SCHEMA COMMERCIAL

ATTRIBUTES

CODE  DOMAIN SUPPLIER-CODE

NAME DOMAIN SUPPLIER-NAME

CITY DOMAIN CITY

CONSTRAINTS

PRIMARY-KEY (CODE) REFERENCES SUPPLIER-CODE

CADIDATE-KEY (NAME) REFERENCES SUPPLIER-NAME

DECLARE RELATION ORDER FOR SCHEMA COMMERCIAL

ATTRIBUTES

SUPPLIER-CODE DOMAIN SUPPLIER-CODE

PART-CODE DOMAIN PART-CODE

QUANTITY DOMAIN QUANTITY

CONSTRAINTS

PRIMARY-KEY (SUPPLIER-CODE, PART-CODE)

FOREIGN-KEY (SUPPLIER-CODE) REFERENCES SUPPLIER-CODE

FOREIGN-KEY (PART-CODE) REFERENCES PART-CODE

The Data Definition in term of schemas is do at the logical level of database description and is performed using the data names. The identified relations and attributes can be cataloged in the data dictionary. The data dictionary is itself a database consisting of the critical concepts that the user should know about when working with the DBMS.

Functions provided by the data dictionary include:

definition of fields and files

definition of relationships

definitions of important physical schema concepts such as data locations

definitions of views

These elements can be categorized on the logical level in the following sections:

STRUCTURE

RESTRICTIONS

DOMAINS imposed restrictions

- Data types and values limits

IDENTIFYING restrictions

- The oneness of the tuples

REFERENTIAL restrictions

- The presence of the referred tuples

Originally data dictionaries were static, requiring complete revisions after each update of data definitions. In recent years, dynamic data dictionaries have become available. Dynamic data dictionaries can be updated at any time because they use an interpreted mode of operation within the DBMS. Each access request results in a check of the current description of the data in the data dictionary by the DBMS. Access locking occurs only for the few seconds or minutes that it takes to update definitions.

5.3. - Graphical representations of Data Structure

(Data Structure Tree)

For Data Structure representation in a more suggestive way we can use graphical methods. One of this is offered by the Data Structure Tree. The used symbols in a representation will be presented further:

Examples:

Fig. 3. - Symbols for Data Structure Tree representation

Fig. 4. - COMMERCIAL Data Base

Fig. 5. - PART STRUCTURE Data Base

Data Definition in X-BASE DBMS

Command and Function Syntax Convention:

UPPER-CASE - FoxPro Keywords (reserved words)

< > - Angle brackets surrounding lower-case text indicate information that must be provided by the user

- must be included in syntax

- Square brackets indicate an optional portion of command or function

- A vertical bar separating options indicates that you can enter only one of the options

- An ellipsis indicates that a command or function or a portion of this can be continued in a similar fashion.

5.4.1. - The representation of simple Data Types

A Database File can be created by the command:

CREATE TABLE | DBF < dbf_name >

( < fname1 > < type > [(< precision > [, < scale >])

( < fname2 > . ]])

| FROM ARRAY < array >

Description:

Each new database field is defined with:

- a name

- a type

- a precision

- a scale

These definitions can be obtained from the command itself or from an array. The new database is opened exclusively, regardless of the current setting of SET EXCLUSIVE parameters.

Clauses:

( < fname1 > < type > [( < precision > [, < scale > ])

( < fname2 > . ]])

In this clause:

< fname1 > and < fname2 > are names of fields in the new database. Each < fname > can be a name expression.

Remark: A name expression is of form (name1) and allow performing an indirect address the value stored in the variable with the name name1.

< type > is a single letter indicating the data type for the field. Some data types require that you specify a < precision > ( field width ) and < scale > ( number of decimal places ). The values for the < type > , < precision > and < scale > may be:

FROM ARRAY < array >

In this clause is the name of an existing array whose contents are:

- a name

- a type

- a precision

- a scale

for each field in the database. The content of the array can be defined with the AFIELDS ( ) function.

Example:

CREATE TABLE student ;

( Student-number N(5) , Name C(15) , Surname C(15) , Class N( 1 ) )

CREATE DBF discipline FROM ARRAY discipline

5.4.2. - Operation with Data Structures

The following commands allow you to create, manipulate and monitor database files. These commands are grouped in three categories:

DB Files manipulation:

BROWSE - Opens a BROWSE window so you can view and / or edit database records

CLOSE DATABASE - Closes all open database, index or memo and format file

COPY STRUCTURE - Copies the structure of a database to another file

COPY TO - Copies the contents of one database to a special database

CREATE - Creates a new database file structure

CREATE FROM - Creates a new database file structure from a COPY STRUCTURE EXTENDED database file

CREATE QUERY - Opens the RQBE window so you can create a query

CREATE TABLE Creates a database file

DISPLAY STRUCTURE - Displays the structure of a database file

EXPORT - Copies data from a database file to a file with a different format

IMPORT - Imports data from a different file format to create a new database file

MODIFY QUERY - Modifies ( or creates ) a query

MODIFY STRUCTURE - Allows you to modify a database structure

PACK - Removes all records marked for deletion

SELECT - Activates the specified work area

RECORDS manipulation:

CHANGE or EDIT - Displays database fields for editing

DELETE - Marks record for deletion

DELETED ( ) - Returns true ( .T. ) if the current record is marked for deletion

FLUSH - Flushes all active memory buffers to disk

RECALL - Unmarks records that are marked for deletion

REPLACE - Updates database records with data from an expression

SET AUTOSAVE - Flushes all active memory buffers to disk

SELECT SQL - Retrieves data from one or more database files

SET ANSI - Specifies how SQL string comparison are made

SET DELETED - Enables or disables access to records marked for deletion

SET FIELDS - Specifies access to fields or only those fields included in a field list

SET OPTIMIZE - Enables or disables RUSHMORE procedure

SORT - Sorts data in an existing database file and outputs the sorted data to a new database file

UPDATE - Updates fields in one database file with data from another database file

USE - Opens a database file and associated index files

USED ( ) - Returns true (.T.) if a database file is open in a specified work area

ZAP - Removes all records from a database file

FIELDS manipulation:

APPEND MEMO - Fills a memo field from a file

AVERAGE - Computes the arithmetic mean, optionally storing it in an array or to memory variables

CLOSE MEMO Closes a memo editing window

COPY MEMO - Copies the contents of a memo field to another file

COUNT - Counts the number of specified records, optionally storing the result in a memory variables

MEMLINES ( ) - Returns the number of lines in a memo field

MLINE ( ) - Returns a line from a memo field as a character string

SUM - Totals the numeric field values in a database file

TOTAL - Creates summary records in a new database file, one record for each group of identically-keyed records in the selected database file