Index Database Description

Since we operate object-oriented, but Oracle only partially implements this paradigm, we proceeded as follows :

Key of table descriptions:
 
Table, which is defined in the general part of the schema (creategeneral.sql)
Table, which is defined in the special part of the schema (createspecial.sql)
Object table based on a type



 
ADDRESS_TABLE
NAME NULL? TYPE

STREET NOT NULL VARCHAR2(50)
ZIPCODE NOT NULL NUMBER(38)
TOWN CENTER NOT NULL VARCHAR2(50)
NATION NOT NULL VARCHAR2(30)

Contents: addresses (in SMOOTH mainly nationalities) 
TRIGGER(S):
  • pk_address: checks primary key
  • def_address: sets the standard nationality  "Austria" if field is left empty


ALIAS
NAME NULL? TYPE

NAME NOT NULL VARCHAR2(30)
ENGLISH NOT NULL VARCHAR2(50)
GERMAN   VARCHAR2(50)
FRENCH   VARCHAR2(50)
ITALIAN   VARCHAR2(50)

Contents: Translations and alternative names for tables and attributes


ANNOTATION_TABLE
NAME NULL? TYPE

DOCINFOID NOT NULL REF OF DOCUMENTINFO_TYPE
COMPONENTID NOT NULL REF OF STRUCTURECOMPONENT_TYPE
LASTMODIFIERID   REF OF EMPLOYEE_TYPE
TYPE OATH NOT NULL REF OF ANNOTATIONTYPE_TYPE
LASTMODIFIED   DATE

Contents: semantic annotation unit, video data and annotating person 
TRIGGER(S):
  • pk_annotation: checks primary key


ANNOTATIONTYPE_TABLE
NAME NULL? TYPE

SHORTCUT NOT NULL VARCHAR2(2)
DESCRIPTION NOT NULL VARCHAR2(100)

Contents: Type of annotation (Person/Location/Object/Event)


ANSWERJTREE
NAME NULL? TYPE

X   VARCHAR2(256)

Contents: serves for  JTreeData export from the database (Return of a database procedure was not handable in JDBC)


CHANCE_TABLE
NAME NULL? TYPE

ID NOT NULL NUMBER(38)
EVENTWHEN NOT NULL NUMBER(38)
MATCHID NOT NULL REF OF MATCH_TYPE
PLAYERID NOT NULL REF OF PLAYER_TYPE
STYLEID NOT NULL REF OF SCORESTYLE_TYPE
FIELDPOSITION NOT NULL REF OF FIELDPOSITION_TYPE
TEAMID NOT NULL REF OF TEAM_TYPE
MORE DRESSNUMBER   NUMBER(38)

Contents: Data of a "chance " event 
TRIGGER(S):
  • pk_chance: checks primary key
  • id_chid: assigns next valid EventID


COMPONENTTYPE_TABLE
NAME NULL? TYPE

DESCRIPTION NOT NULL VARCHAR2(100)
RANKING NOT NULL NUMBER(38)

Contents: Type of a component (CompoundUnit/Sequence/Scene/Shot)


CONTENT_QUERY_RESULT_TABLE
NAME NULL? TYPE

UNIT_DESCRIPTION NOT NULL VARCHAR2(500)
STREAM_DESCRIPTION NOT NULL VARCHAR2(500)
UNIT_BEGIN NOT NULL FLOAT(126)
UNIT_LENGTH NOT NULL FLOAT(126)
VIDEO FORMAT NOT NULL VARCHAR2(20)
FRAMERATE NOT NULL NUMBER(38)
VIDEO SERVER NOT NULL VARCHAR2(200)
PORT NOT NULL NUMBER(38)
VIDEO FILE NAME NOT NULL VARCHAR2(200)
FILEEXTENSION NOT NULL VARCHAR2(4)

Contents: Results of Querier queries


CORNER_TABLE
NAME NULL? TYPE

ID NOT NULL NUMBER(38)
EVENTWHEN NOT NULL NUMBER(38)
MATCHID NOT NULL REF OF MATCH_TYPE
PLAYERID NOT NULL REF OF PLAYER_TYPE
DIRECTID   REF OF DIRECTION_TYPE
FIELDPOSITION NOT NULL REF OF FIELDPOSITION_TYPE
TEAMID NOT NULL REF OF TEAM_TYPE
MORE DRESSNUMBER   NUMBER(38)

Contents: Data of a "corner" event 
TRIGGER(S):
  • more pk_corner: checks primary key
  • more id_corner: assigns next valid EventID 


DEPARTMENT_TABLE
NAME NULL? TYPE

NAME NOT NULL VARCHAR2(30)
DESCRIPTION   VARCHAR2(255)
CHIEFID   REF OF EMPLOYEE_TYPE

Contents: Data of the department, which the annotating person belongs to (not filled in SMOOTH)


DIRECTION_TABLE
NAME NULL? TYPE

SHORTCUT NOT NULL CHAR(2)
DESCRIPTION NOT NULL VARCHAR2(100)

Contents: Direction of the corner/chance/etc.. (Left/Middle/Right/...)


DOCUMENTINFO_TABLE
NAME NULL? TYPE

DESCRIPTION NOT NULL VARCHAR2(500)
CREATIONDATE NOT NULL DATE
EMPLOYEEID   REF OF EMPLOYEE_TYPE

Contents: Rough data of the video 
Procedure:
  • ins_documentinfo: For consistent inserting (in case of an inconsistent insert no error may be produced as well as the data may not be inserted)


EMPLOYEE_TABLE
NAME NULL? TYPE

ID NOT NULL NUMBER(38)
TITLE   VARCHAR2(20)
FIRST NAME NOT NULL VARCHAR2(20)
SURNAME NOT NULL VARCHAR2(30)
ADDRESSID NOT NULL REF OF ADDRESS_TYPE
MORE TELNUMBER   VARCHAR2(30)
MAILADDRESS   VARCHAR2(60)
JOB POSITION NOT NULL VARCHAR2(30)
DEPARTID NOT NULL REF OF DEPARTMENT_TYPE

Contents: Data of the annotating person 
TRIGGER(S):
  • pk_employee: checks primary key
  • id_employee: assigns next valid person ID 


EVENT_TABLE
NAME NULL? TYPE

ID NOT NULL NUMBER(38)
EVENTWHEN NOT NULL NUMBER(38)
MATCHID NOT NULL REF OF MATCH_TYPE

Contents: Superclass of all events 
TRIGGER(S):
  • id_event: assigns next valid EventID 


EVENTANN_CONTAINS_EVENT_TAB
NAME NULL? TYPE

EVENTID NOT NULL REF OF EVENT_TYPE
ANNOTATIONID NOT NULL REF OF ANNOTATION_TYPE

Contents: Connecting data event < = > annotation 
TRIGGER(S):
  • pk_eventanncontains: checks primary key


EVENTCOMPARE1
NAME NULL? TYPE

ID   NUMBER(38)
EVENTWHEN   NUMBER(38)

Contents: Data Event1 (in the Querier for Event1 after Event2 - search)


EVENTCOMPARE2
NAME NULL? TYPE

ID   NUMBER(38)
EVENTWHEN   NUMBER(38)

Contents: Data Event2 (in the Querier for Event1 after Event2 - search)


FIELDPOSITION_TABLE
NAME NULL? TYPE

SHORTCUT NOT NULL VARCHAR2(2)
DESCRIPTION NOT NULL VARCHAR2(100)

Contents: Field locations (Forward/Defense/Midfield/Goalkeeper)


FOUL_TABLE
NAME NULL? TYPE

ID NOT NULL NUMBER(38)
EVENTWHEN NOT NULL NUMBER(38)
MATCHID NOT NULL REF OF MATCH_TYPE
COMMITTERID NOT NULL REF OF PLAYER_TYPE
VICTIMID NOT NULL REF OF PLAYER_TYPE
YELLOWCARD NOT NULL NUMBER(38)
REDCARD NOT NULL NUMBER(38)
MORE FIELDPOSITIONCOMMITTER NOT NULL REF OF FIELDPOSITION_TYPE
FIELDPOSITIONVICTIM NOT NULL REF OF FIELDPOSITION_TYPE
MORE TEAMIDCOMMITTER NOT NULL REF OF TEAM_TYPE
TEAMIDVICTIM NOT NULL REF OF TEAM_TYPE
MORE DRESSNUMBERCOMMITTER   NUMBER(38)
DRESSNUMBERVICTIM   NUMBER(38)

Contents: Data of a "foul" event 
TRIGGER(S):
  • pk_foul: checks primary key
  • id_foul: assigns next valid EventID 


FREEKICK_TABLE
NAME NULL? TYPE

ID NOT NULL NUMBER(38)
EVENTWHEN NOT NULL NUMBER(38)
MATCHID NOT NULL REF OF MATCH_TYPE
PLAYERID NOT NULL REF OF PLAYER_TYPE
DIRECTID   REF OF DIRECTION_TYPE
DISTANCE   NUMBER(38)
UNITY   VARCHAR2(20)
FIELDPOSITION NOT NULL REF OF FIELDPOSITION_TYPE
TEAMID NOT NULL REF OF TEAM_TYPE
MORE DRESSNUMBER   NUMBER(38)

Contents: Data of a "freekick" event 
TRIGGER(S):
  • pk_freekick: checks primary key
  • id_freekick: assigns next valid EventID 


FUNNYSTUFF_TABLE
NAME NULL? TYPE

ID NOT NULL NUMBER(38)
EVENTWHEN NOT NULL NUMBER(38)
MATCHID NOT NULL REF OF MATCH_TYPE
DESCRIPTION NOT NULL VARCHAR2(255)
PLAYERID   REF OF PLAYER_TYPE

Contents: Data of a "funnystuff" event 
TRIGGER(S):
  • pk_funnystuff: checks primary key
  • id_funnystuff: assigns next valid EventID 


GOAL_TABLE
NAME NULL? TYPE

ID NOT NULL NUMBER(38)
EVENTWHEN NOT NULL NUMBER(38)
MATCHID NOT NULL REF OF MATCH_TYPE
PLAYERID NOT NULL REF OF PLAYER_TYPE
SCORETEAM1 NOT NULL NUMBER(38)
SCORETEAM2 NOT NULL NUMBER(38)
STYLEID NOT NULL REF OF SCORESTYLE_TYPE
DIRECTID   REF OF DIRECTION_TYPE
DISTANCE   NUMBER(38)
UNITY   VARCHAR2(20)
FIELDPOSITION NOT NULL REF OF FIELDPOSITION_TYPE
TEAMID NOT NULL REF OF TEAM_TYPE
MORE DRESSNUMBER   NUMBER(38)

Contents: Data of a "goal" event 
TRIGGER(S):
  • pk_goal: checks primary key
  • id_goal: assigns next valid EventID 


HIERARCHY
NAME NULL? TYPE

SUPEROBJECT NOT NULL VARCHAR2(30)
SUBOBJECT NOT NULL VARCHAR2(30)

Contents: inheritance hierarchy


IDCOLLECTION
NAME NULL? TYPE

ID   NUMBER(38)
CONDNUM   NUMBER(38)
TABLENAME NOT NULL VARCHAR2(255)

Contents: IDs for preselections in the querier


LOCANN_CONTAINS_LOCATION_TAB
NAME NULL? TYPE

LOCATIONID NOT NULL REF OF LOCATION_TYPE
ANNOTATIONID NOT NULL REF OF ANNOTATION_TYPE

Contents: Connecting data location < = > annotations 
TRIGGER(S):
  • pk_locanncontains: checks primary key


LOCATION_TABLE
NAME NULL? TYPE

ID NOT NULL NUMBER(38)
NAME NOT NULL VARCHAR2(200)
ADDRESSID   REF OF ADDRESS_TYPE

Contents: Superclass of all locations 
TRIGGER(S):
  • pk_location: checks primary key
  • id_location: assigns next valid location ID 


LOOKUPJTREE
NAME NULL? TYPE

TABLENAME   VARCHAR2(30)
KEYCOLUMN   VARCHAR2(30)

Contents: meaningful attribute for a certain table 


MATCH_TABLE
NAME NULL? TYPE

I *** TRANSLATION ENDS HERE ***D NOT NULL  NUMBER(38) 
DESCRIPTION  NOT NULL  VARCHAR2(200) 
MATCHDATE  NOT NULL  DATE 
TEAM1ID  NOT NULL  REF OF TEAM_TYPE 
TEAM2ID  NOT NULL  REF OF TEAM_TYPE 
STADIUMID  NOT NULL  REF OF STADIUM_TYPE 
REFEREEID  NOT NULL  REF OF REFEREE_TYPE 
NRSPECTATORS    NUMBER(38) 

CONTENTS: Data of a match (starting table of the SMOOTH application) 
TRIGGER(S)(S): 
  • pk_match: checks primary key
  • id_match: assigns next valid object ID


OBJECT_TABLE 
NAME  NULL?  TYPE 

ID  NOT NULL  NUMBER(38) 
DESCRIPTION  NOT NULL  VARCHAR2(200) 

CONTENTS: superclass of all objects 
TRIGGER(S)(S): 
  • id_object: assigns next valid object ID 


OBJECTANN_CONTAINS_OBJECT_TAB 
NAME  NULL?  TYPE 

OBJECTID  NOT NULL  REF OF OBJECT_TYPE 
ANNOTATIONID  NOT NULL  REF OF ANNOTATION_TYPE 

CONTENTS: connecting data object < = > annotation 
TRIGGER(S)(S): 
  • pk_objectann: checks primary key


PASS_TABLE 
NAME  NULL?  TYPE 

ID  NOT NULL  NUMBER(38) 
EVENTWHEN  NOT NULL  NUMBER(38) 
MATCHID  NOT NULL  REF OF MATCH_TYPE 
SENDERID  NOT NULL  REF OF PLAYER_TYPE 
RECEIVERID  NOT NULL  REF OF PLAYER_TYPE 
DISTANCE    NUMBER(38) 
UNITY    VARCHAR2(20) 
FIELDPOSITIONSENDER  NOT NULL  REF OF FIELDPOSITION_TYPE 
FIELDPOSITIONRECEIVER  NOT NULL  REF OF FIELDPOSITION_TYPE 
TEAMIDSENDER  NOT NULL  REF OF TEAM_TYPE 
TEAMIDRECEIVER  NOT NULL  REF OF TEAM_TYPE 
DRESSNUMBERSENDER    NUMBER(38) 
DRESSNUMBERRECEIVER    NUMBER(38) 

CONTENTS: data of a "pass" event 
TRIGGER(S): 
  • pk_pass: checks primary key
  • id_pass: assigns next valid event ID 


PENALTY_TABLE 
NAME  NULL?  TYPE 

ID  NOT NULL  NUMBER(38) 
EVENTWHEN  NOT NULL  NUMBER(38) 
MATCHID  NOT NULL  REF OF MATCH_TYPE 
PLAYERID  NOT NULL  REF OF PLAYER_TYPE 
FIELDPOSITION  NOT NULL  REF OF FIELDPOSITION_TYPE 
TEAMID  NOT NULL  REF OF TEAM_TYPE 
DRESSNUMBER    NUMBER(38) 

CONTENTS: data of a "penalty" event 
TRIGGER(S): 
  • pk_penalty: checks primary key 
  • id_penalty: assigns next valid event ID 


PERSANN_CONTAINS_PERSON_TAB 
NAME  NULL?  TYPE 

PERSONID  NOT NULL  REF OF PERSON_TYPE 
ANNOTATIONID  NOT NULL  REF OF ANNOTATION_TYPE 

CONTENTS: connecting data for person < = > annotation 
TRIGGER(S): 
  • pk_persann: checks primary key


PERSON_TABLE 
NAME  NULL?  TYPE 

ID  NOT NULL  NUMBER(38) 
TITLE    VARCHAR2(20) 
FIRSTNAME    VARCHAR2(30) 
SURNAME  NOT NULL  VARCHAR2(30) 
ADDRESSID    REF OF ADDRESS_TYPE 

CONTENTS: superclass of all persons 
TRIGGER(S): 
  • pk_person: checks primary key
  • id_person: assigns next valid person ID


PK_TABLE 
NAME  NULL?  TYPE 

EVENTWHEN    NUMBER(38) 
MATCHID    REF OF MATCH_TYPE 
PLAYERID    REF OF PLAYER_TYPE 

CONTENTS: temporary data for pk_ triggers on event tables including one player reference


PK2_TABLE 
NAME  NULL?  TYPE 

EVENTWHEN    NUMBER(38) 
MATCHID    REF OF MATCH_TYPE 
PLAYER1ID    REF OF PLAYER_TYPE 
PLAYER2ID    REF OF PLAYER_TYPE 

CONTENTS: temporary data for pk_ triggers on event tables including two player references


PLAYER_TABLE 
NAME  NULL?  TYPE 

ID  NOT NULL  NUMBER(38) 
TITLE    VARCHAR2(20) 
FIRSTNAME    VARCHAR2(30) 
SURNAME  NOT NULL  VARCHAR2(30) 
ADDRESSID    REF OF ADDRESS_TYPE 

CONTENTS: data of players 
TRIGGER(S): 
  • pk_player: checks primary key
  • id_player: assigns next valid person ID


REFEREE_TABLE 
NAME  NULL?  TYPE 

ID  NOT NULL  NUMBER(38) 
TITLE    VARCHAR2(20) 
FIRSTNAME    VARCHAR2(30) 
SURNAME  NOT NULL  VARCHAR2(30) 
ADDRESSID    REF OF ADDRESS_TYPE 
STYLEID    REF OF REFEREESTYLE_TYPE 

CONTENTS: data of referees 
TRIGGER(S): 
  • pk_referee: checks primary key
  • id_referee: assigns next valid person ID


REFEREESTYLE_TABLE 
NAME  NULL?  TYPE 

SHORTCUT  NOT NULL  VARCHAR2(2) 
DESCRIPTION  NOT NULL  VARCHAR2(100) 

CONTENTS: style of a referee  (Strict/Generous/Normal/...) 


SCORESTYLE_TABLE 
NAME  NULL?  TYPE 

SHORTCUT  NOT NULL VARCHAR2(2) 
DESCRIPTION  NOT NULL  VARCHAR2(100) 

CONTENTS: style of a chance (left/right food, hand, head) 


SHOT_TABLE 
NAME  NULL?  TYPE 

ID  NOT NULL  NUMBER(38) 
EVENTWHEN  NOT NULL  NUMBER(38) 
MATCHID  NOT NULL  REF OF MATCH_TYPE 
PLAYERID  NOT NULL  REF OF PLAYER_TYPE 
DISTANCE    NUMBER(38) 
UNITY    VARCHAR2(20) 
FIELDPOSITION  NOT NULL  REF OF FIELDPOSITION_TYPE 
TEAMID NOT NULL  REF OF TEAM_TYPE 
DRESSNUMBER    NUMBER(38) 

CONTENTS: data of a "chance" event 
TRIGGER(S): 
  • pk_shot: checks primary key
  • id_shot: assigns next valid location ID


STADIUM_TABLE 
NAME  NULL?  TYPE 

ID  NOT NULL  NUMBER(38) 
NAME  NOT NULL  VARCHAR2(100) 
CAPACITY    NUMBER(38) 
ADDRESSID    REF OF ADDRESS_TYPE 

CONTENTS: data of stadium 
TRIGGER(S): 
  • pk_stadium: checks primary key
  • id_stadium: assigns next valid location ID


STARTPOINT 
NAME  NULL?  TYPE 

APPLICATIONNAME  NOT NULL  VARCHAR2(30) 
TABLENAME  NOT NULL  VARCHAR2(30) 

CONTENTS: the table which a applicaton should start from 


STOREDQUERY_TABLE 
NAME  NULL?  TYPE 

KEYWORDSTRING  NOT NULL  VARCHAR2(255) 
QUERYTEXT  NOT NULL  VARCHAR2(2000) 
QUERYCOMMAND  NOT NULL  VARCHAR2(2000) 
USERID    NUMBER(38) 

CONTENTS: query text of already processed querygestellte 
TRIGGER(S): 
  • pk_storedquery: checks primary key


STRUCTURECOMPONENT_TABLE 
NAME  NULL?  TYPE 

DESCRIPTION  NOT NULL  VARCHAR2(500) 
DOCUMENTID    REF OF VIDEODOCUMENT_TYPE 
TIMEINTERVALID  NOT NULL  REF OF TIMEINTERVAL_TYPE 
TYPEID  NOT NULL  REF OF COMPONENTTYPE_TYPE 
PARENTID    REF OF STRUCTURECOMPONENT_TYPE 

CONTENTS: videodata of the annotation 
TRIGGER(S): 
  • pk_structurecomponent: checks primary key


TEAM_TABLE 
NAME  NULL?  TYPE 

ID  NOT NULL  NUMBER(38) 
DESCRIPTION  NOT NULL  VARCHAR2(200) 
NAME  NOT NULL  VARCHAR2(100) 

CONTENTS: team data 
TRIGGER(S): 
  • pk_team: checks primary key
  • id_team: assigns next valid object ID


TIMEINTERVAL_TABLE 
NAME  NULL?  TYPE 

TIBEGIN  NOT NULL  FLOAT(126) 
TIEND  NOT NULL  FLOAT(126) 
UNITY  NOT NULL  VARCHAR2(20) 

CONTENTS: length of the video part (= shot) of an annotation 
Prozedur: 
  • ins_timeinterval: for consistent insertion as no error may be raisend on inconsistency


VIDEODOCUMENT_TABLE 
NAME  NULL?  TYPE 

DOCINFREF    REF OF DOCUMENTINFO_TYPE 
FORMATID  NOT NULL  REF OF VIDEOFORMAT_TYPE 
SERVERID    REF OF VIDEOSERVER_TYPE 
VIDEOFILENAME  NOT NULL  VARCHAR2(50)

CONTENTS: generel video informations, picture dimensions, path/URL, file name 
TRIGGER(S): 
  • pk_videodocument: checks primary key


VIDEOFORMAT_TABLE 
NAME  NULL?  TYPE 

NAME  NOT NULL  VARCHAR2(30) 
EXTENSION  NOT NULL  VARCHAR2(4) 
FRAMERATE  NOT NULL  FLOAT(126) 

CONTENTS: all videoformats available (.mpg, .avi, .mov) 


VIDEOSERVER_TABLE 
NAME  NULL?  TYPE 

INETADDRESS  NOT NULL  VARCHAR2(500) 
PORT  NOT NULL  NUMBER(38) 

CONTENTS: URL of the  video server

go to main page