--    This SQL DDL script was generated by Microsoft Visual Studio (Release Date: LOCAL BUILD).

--    Driver Used : Microsoft Visual Studio - Microsoft Access Driver.
--    Document    : E:\clients\CSIRO\ClassingDB\db_sheepindustry\data\EC396A.vsd.
--    Time Created: 24 August 2005 8:49 AM.
--    Operation   : From Visio Update Database Wizard.
--    Connected data source : No connection.
--    Connected server      : No connection.
--    Connected database    : Not applicable.



-- Create new table `AnimalComment`.
-- `AnimalComment` : Table of AnimalComment
-- 	Namespace: All
-- 	`actPrimaryId` : actPrimaryId identifies AnimalComment
-- 	`actDate` : The date the comment is recorded
-- 	`actComment` : Comments recorded about the animal  
create table `AnimalComment` ( 
	`actPrimaryId` CHAR(10),
	`actDate` DATETIME,
	`actComment` CHAR(10), constraint `AnimalComment_PK` primary key (`actPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`AnimalComment`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'AnimalComment' :
-- 	`actPrimaryId`
-- 	`actDate`
-- 	`actComment`


-- Create new table `LKTagColor`.
-- `LKTagColor` : Table of LKTagColor
-- 	Namespace: Animal
-- 	A lookup table of tag colors and their descriptions.
-- 	START
-- 	ltgCode	ltgDescription
-- 	BLACK	Black colored tag
-- 	DARKBLUE	Dark Blue colored tag
-- 	GREEN	Green colored tag
-- 	LIGHTBLUE	Light Blue colored tag
-- 	ORANGE	Orange colored tag
-- 	PINK	Pink colored tag
-- 	PURPLE	Purple colored tag
-- 	RED	Red colored tag
-- 	WHITE	White colored tag
-- 	YELLOW	Yellow colored tag
-- 	END
-- 	`ltgCode` : The color code of a tag
-- 	`ltgDescription` : A description of the color code  
create table `LKTagColor` ( 
	`ltgCode` VARCHAR(50),
	`ltgDescription` VARCHAR(100), constraint `LKTagColor_PK` primary key (`ltgCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKTagColor`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKTagColor' :
-- 	`ltgCode`


-- Create new table `MobHistory`.
-- `MobHistory` : Table of MobHistory
-- 	Namespace: All
-- 	This table tracks changes of animals to amob and paddocks to a mob.
-- 	`mhyMobPrimaryId` : The id of the mob to which the history is attributed 
-- 	`mhyPrimaryID` : mhyPrimaryID identifies MobHistory
-- 	`mhyAmlPrimaryId` : The id of the animal it belonged to in its previous mob
-- 	`mhyPdkPrimaryId` : The id of the previous paddock to which the mob belonged  
create table `MobHistory` ( 
	`mhyMobPrimaryId` CHAR(15),
	`mhyPrimaryID` CHAR(10),
	`mhyAmlPrimaryId` CHAR(15),
	`mhyPdkPrimaryId` CHAR(15), constraint `MobHistory_PK` primary key (`mhyPrimaryID`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`MobHistory`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'MobHistory' :
-- 	`mhyMobPrimaryId`
-- 	`mhyPrimaryID`


-- Create new table `GeneticManagementGroupHistory`.
-- `GeneticManagementGroupHistory` : Table of GeneticManagementGroupHistory
-- 	Namespace: Animal, Genetics
-- 	A history table relating an animal to its genetic management group.
-- 	`gmgAmlPrimaryId` : The id of the animal in the genetic management group
-- 	`gmgPrimaryId` : gmgPrimaryId identifies GeneticManagementGroupHistory
-- 	`gmgLgmCode` : The code of the genetic management group (see lkGeneticManagementGroup)  
create table `GeneticManagementGroupHistory` ( 
	`gmgAmlPrimaryId` CHAR(15),
	`gmgPrimaryId` CHAR(15),
	`gmgLgmCode` TEXT(10), constraint `GeneticManagementGroupHistory_PK` primary key (`gmgPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`GeneticManagementGroupHistory`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'GeneticManagementGroupHistory' :
-- 	`gmgAmlPrimaryId`
-- 	`gmgPrimaryId`
-- 	`gmgLgmCode`


-- Create new table `LKAnimalStatus`.
-- `LKAnimalStatus` : Table of LKAnimalStatus
-- 	Namespace: Animal, Genetics
-- 	Gives the staus of the animal:
-- 	START
-- 	lasCode	lasDescription
-- 	ALIVE	Animal is alive
-- 	DEAD	Animal is dead
-- 	CULL	Animal has been culled (removed from flock)
-- 	PREGNANT	Ewe is pregnant
-- 	JOINED	Ewe has been joined (mated)
-- 	WEANED	Animal has been weaned from its mother
-- 	WET	Ewe is pregnant and carrying at least one lamb
-- 	DRY	Ewe is not carrying any lambs (is not pregnant)
-- 	UNKNOWN	Unknown
-- 	END
-- 	`lasCode` : The code identifies the status of the animal
-- 	`lasDescription` : Full description of the status of the animal  
create table `LKAnimalStatus` ( 
	`lasCode` VARCHAR(50),
	`lasDescription` VARCHAR(50), constraint `LKAnimalStatus_PK` primary key (`lasCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKAnimalStatus`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKAnimalStatus' :
-- 	`lasCode`
-- 	`lasDescription`


-- Create new table `Breed`.
-- `Breed` : Table of Breed
-- 	Namespace: All
-- 	Stores the breed of an animal.  An animal may consist of multiple breeds.  For example:
-- 	00 50%   
-- 	01 25%
-- 	04 25%
-- 	`brdPrimaryId` : brdPrimaryId identifies Breed
-- 	`brdPercentage` : The percentage of that breed in the animal
-- 	`brdAmlPrimaryId` : The id of the animal 
-- 	`brdLbyCode` : brdLbyCode is of Breed  
create table `Breed` ( 
	`brdPrimaryId` CHAR(10),
	`brdPercentage` SHORT,
	`brdAmlPrimaryId` CHAR(15),
	`brdLbyCode` TEXT(12), constraint `Breed_PK` primary key (`brdPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`Breed`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'Breed' :
-- 	`brdPrimaryId`
-- 	`brdPercentage`
-- 	`brdAmlPrimaryId`
-- 	`brdLbyCode`


-- Create new table `AnimalGeneticIndex`.
-- `AnimalGeneticIndex` : Table of AnimalGeneticIndex
-- 	Namespace: Genetics
-- 	Stores the calaculated genetic index value of an animal trait. 
-- 	Refer to list of indexes in LKGeneticIndexType
-- 	`gixPrimaryId` : gixPrimaryId identifies AnimalGeneticIndex
-- 	`gixLgiCode` : The type of genetic index (see LKGeneticIndexType)
-- 	`gixValue` : The value of the genetic index
-- 	`gixDate` : The date the genetic index was calculated
-- 	`gixAmlPrimaryId` : The primary id of the animal for the genetic index value
-- 	`gixGiaPrimaryId` : The primary id of the genetic index group of animals used in the generation of the index values  
create table `AnimalGeneticIndex` ( 
	`gixPrimaryId` CHAR(15),
	`gixLgiCode` TEXT(10),
	`gixValue` LONG,
	`gixDate` DATETIME,
	`gixAmlPrimaryId` CHAR(15),
	`gixGiaPrimaryId` INTEGER, constraint `AnimalGeneticIndex_PK` primary key (`gixPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`AnimalGeneticIndex`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'AnimalGeneticIndex' :
-- 	`gixPrimaryId`
-- 	`gixLgiCode`
-- 	`gixValue`
-- 	`gixDate`


-- Create new table `LKGeneticManagementGroup`.
-- `LKGeneticManagementGroup` : Table of LKGeneticManagementGroup
-- 	Namespace: Animal, Genetics
-- 	A genetic management group is a group of animals that are run together under the same environmental conditions for 12 months and can be grouped together for genetic analysis. For example, mixed age stud ewes, maiden ewes, hogget rams.
-- 	These are these user defined. 
-- 	`lgmCode` : The code for the genetic management group
-- 	`lgmDescription` : The description of the code  
create table `LKGeneticManagementGroup` ( 
	`lgmCode` TEXT(10),
	`lgmDescription` TEXT(250), constraint `LKGeneticManagementGroup_PK` primary key (`lgmCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKGeneticManagementGroup`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKGeneticManagementGroup' :
-- 	`lgmCode`
-- 	`lgmDescription`


-- Create new table `LKPackTare`.
-- `LKPackTare` : Table of PackTare
-- 	Namespace: Wool
-- 	Pack tares in kg defined by AWEX Data Dictionary
-- 	START
-- 	lptCode	lptDescription
-- 	NY	Nylon 2 kg
-- 	H1	High density 1 2 kg
-- 	H5	High density 5 3 kg	
-- 	END
-- 	`lptCode` : The code for pack tare
-- 	`lptDescription` : Full description of the pack tare  
create table `LKPackTare` ( 
	`lptCode` TEXT(2),
	`lptDescription` TEXT(35), constraint `LKPackTare_PK` primary key (`lptCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKPackTare`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKPackTare' :
-- 	`lptCode`
-- 	`lptDescription`


-- Create new table `LKSheepDisease`.
-- `LKSheepDisease` : Table of SheepDiseases
-- 	Namespace: Animal
-- 	This is a table that lists sheep diseases in Australia.
-- 	They are based on the groupings made in the course notes for Sheep Health and Production. A course for Veterinary Science V students by KA Abbott & WMC Maxwell Faculty of Veterinary Science University of Sydney.
-- 	The course notes can be viewed at the following website: http://vein.library.usyd.au/sheephealth/index.html
-- 	VEIN = Veterinary Education and Information Network
-- 	START
-- 	lsdCode	lsdType	lsdDescription
-- 	DIARHEA	ALITRACT	Diarrhoea in sheep
-- 	ECOLI	ALITRACT	E. coli infection in lambs
-- 	GRAINPOI	ALITRACT	Grain poisoning
-- 	INTCARC	ALITRACT	Intestinal carcinoma
-- 	NUTRDIAR	ALITRACT	Nutritional diarrhoea caused by lush feed
-- 	OVJOHNE	ALITRACT	Ovine Johnes Disease (OJD)
-- 	PESPETRUM	ALITRACT	Pestes des petits ruminants (PPR)
-- 	REDGUT	ALITRACT	Red gut (haemorrhagic enteritis)
-- 	SALMON	ALITRACT	Salmonellosis
-- 	SCOUR	ALITRACT	Scouring in sheep
-- 	TOXPLALI	ALITRACT	Alimentary tract diseases caused by toxic plants
-- 	VIRALDIAR	ALITRACT	Viral diarrhoea (enteritis) in young sheep
-- 	CASLYMPH	BLOODLYM	Caseous lymphadenitis (CLA) - abscessation of lymph nodes
-- 	ANNRYETOX	CENNERSYS	Annual ryegrass toxicity (ARGT)
-- 	BOTISM	CENNERSYS	Botulism
-- 	BSPABSC	CENNERSYS	Brain and spinal cord abscessation
-- 	CHEMCNS	CENNERSYS	Diseases of the CNS caused by ingestion of chemicals
-- 	COONSTAG	CENNERSYS	Coonabarabran staggers
-- 	DARLPCNS	CENNERSYS	Darling pea CNS
-- 	ERGO	CENNERSYS	Ergotism
-- 	FOCSYMENC	CENNERSYS	Focal symmetrical encephalomalacia (FSE).
-- 	HUMPBCK	CENNERSYS	Humpy back disease
-- 	INHCNS	CENNERSYS	Various inherited diseases of the CNS
-- 	LISTER	CENNERSYS	Listeriosis (abortion in ewes and septicaemia in lambs)
-- 	PHALSTAG	CENNERSYS	Phalaris staggers
-- 	PLANTCNS	CENNERSYS	Lesions of CNS caused by plant intoxications
-- 	POLIO	CENNERSYS	Polioencephalomalacia (PEM) or cerebrocortical necrosis (CCN) or polio
-- 	RYESTAG	CENNERSYS	Perennial ryegrass staggers
-- 	TETANUS	CENNERSYS	Tetanus
-- 	ACTINBAC	EYESKIN	Actinobacillosis (leather lips)
-- 	BURNS	EYESKIN	Burns from fires
-- 	CARCIN	EYESKIN	Squamous cell carcinoma
-- 	PHOTO	EYESKIN	Photosensitization
-- 	PINKEYE	EYESKIN	Pinkeye.
-- 	SCABBYMTH	EYESKIN	"Scabby mouth, contagious pustular dermatitus (CPD)"
-- 	BARBER	HELMINTH	Barbers pole worm (nematode)
-- 	BLADDER	HELMINTH	Bladder worm (cestode)
-- 	BLKSCOUR	HELMINTH	Black scour worm (nematode)
-- 	BOWELL	HELMINTH	Large bowel worm (nematode)
-- 	HOOK	HELMINTH	Hookworm (nematode)
-- 	HYDATID	HELMINTH	Hydatid (cestode)
-- 	INTESTSM	HELMINTH	Small intestinal worm (nematode)
-- 	LIVFLUKE	HELMINTH	Liver fluke (trematode)
-- 	LUNGL	HELMINTH	Large lungworm (nematode)
-- 	LUNGSM	HELMINTH	Small lungworm (nematode)
-- 	MEASLES	HELMINTH	Sheep measles (cestode)
-- 	MOUTHLBOW	HELMINTH	Large mouthed bowel worm (nematode)
-- 	NODULE	HELMINTH	Nodule worm (nematode)
-- 	STOMFLUKE	HELMINTH	Stomach fluke (trematode)
-- 	TAPE	HELMINTH	Tapeworm (cestode)
-- 	THINNECK	HELMINTH	Thin necked intestinal worm (nematode)
-- 	THREAD	HELMINTH	Threadworm (nematode)
-- 	WHIP	HELMINTH	Whipworm (nematode)
-- 	BDYSTR	INTEGUMENT	Body strike (flystrike)
-- 	BREECHSTR	INTEGUMENT	Breech strike (flystrike)
-- 	DERMO	INTEGUMENT	Dermatitus (lumpy wool)
-- 	FLCROT	INTEGUMENT	Fleece rot (wool)
-- 	ITCH	INTEGUMENT	Itchmite
-- 	KED	INTEGUMENT	Sheep ked
-- 	LICEBITE	INTEGUMENT	Biting lice infestation (Bovicola ovis)
-- 	LICEFACE	INTEGUMENT	Face lice (Linognathus ovillus)
-- 	LICEFOOT	INTEGUMENT	Foot lice (Linognathus pedalis)
-- 	MITES	INTEGUMENT	Tarsonomid mites
-- 	PIZZSTR	INTEGUMENT	Pizzle strike (flystrike)
-- 	POLLSTR	INTEGUMENT	Poll strike (flystrike)
-- 	SCROTDERM	INTEGUMENT	Scrotum dermatitus
-- 	TICKS	INTEGUMENT	Sheep ticks
-- 	WOUNDSTR	INTEGUMENT	Wound strike (flystrike)
-- 	ABSCFOOT	LAME	Foot abscess
-- 	ARTHRIT	LAME	Arthritis
-- 	BENFOOT	LAME	Benign footrot (BFR)
-- 	BLUETGUE	LAME	Blue tongue
-- 	BOWIE	LAME	Bent leg or bowie disease. From ingestion of wild parsnip
-- 	CONPUSDER	LAME	Contagious pustular dermatitus (CPD). A viral disease
-- 	FOOTMTH	LAME	Foot and mouth disease
-- 	INTFOOT	LAME	Intermediate footrot (IFR)
-- 	OSTDYST	LAME	Osteodystrophies - all diseases for bone development failure or bone maintenance
-- 	OVINTDERM	LAME	Ovine interdigital dermatitus (OID).
-- 	POSTDIP	LAME	Post dipping lameness (minor bacterial dermatosis)
-- 	STRAWFOOT	LAME	Strawberry footrot (dermatitus)
-- 	TOEABSC	LAME	Toe abscess
-- 	ACUTEPAST	RESPIRY	Acute pasteurellosis
-- 	MELIO	RESPIRY	Melioidosis
-- 	PNEUMON	RESPIRY	Pneumonia - various forms
-- 	TUBERC	RESPIRY	Tuberculosis
-- 	ANTHRAX	SUDDEATH	Anthrax
-- 	BLKDIS	SUDDEATH	Black disease - infectious necrotic hepatitus
-- 	BLKLEG	SUDDEATH	Black leg
-- 	BRAXY	SUDDEATH	"Braxy - Bradsot, a fatal toxaemia"
-- 	MALIGOED	SUDDEATH	Malignant oedema
-- 	POISON	SUDDEATH	Poisoning from plant toxicity or chemicals
-- 	PULPKID	SUDDEATH	Pulpy kidney
-- 	GOITRE	TRACE	Goitre. Iodine deficiency
-- 	ILLTHRIFT	TRACE	Illthrift due to cobalt deficiency
-- 	OSTEOLMB	TRACE	Osteoporosis in lambs (copper deficiency)
-- 	SERESPUN	TRACE	Selenium Responsive Unthriftiness (SRU). Infertility of ewes (selenium deficiency)
-- 	STEEL	TRACE	Steely wool (copper deficiency)
-- 	SWAYLMB	TRACE	Swaying lambs - Enzootic ataxia (copper deficiency).
-- 	WHLIVDIS	TRACE	White Liver Disease (WLD). Vitamin B12 deficiency
-- 	WHMUSCDIS	TRACE	"White Muscle Disease (WMD). Myopathy of lambs, also known as muscular dystrophy (selenimum deficiency)"
-- 	WNRNUTMY	TRACE	Weaner Nutritional Myopathy (WNM). Vitamin E deficiency
-- 	ZINCDEF	TRACE	Zinc deficiency.
-- 	ENLGLAND	URINARY	Enlargment of bulbo-urethral glands in wethers
-- 	KIDNEY	URINARY	Renal diseases in sheep
-- 	PIZZROT	URINARY	Pizzle rot
-- 	UROLITH	URINARY	Urolithiasis - obstruction of urethra
-- 	COCCID	WEANER	Coccidiosis
-- 	EPERYZOON	WEANER	Eperythrozoonosis
-- 	YERSIN	WEANER	Yersiniosis (diarrhoea)
-- 	 END
-- 	`lsdCode` : The sheep disease code
-- 	`lsdDescription` : The description of the sheep disease
-- 	`lsdType` : lsdType is of LKSheepDisease  
create table `LKSheepDisease` ( 
	`lsdCode` TEXT(10),
	`lsdDescription` TEXT(250),
	`lsdType` TEXT(10), constraint `LKSheepDisease_PK` primary key (`lsdCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKSheepDisease`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKSheepDisease' :
-- 	`lsdCode`
-- 	`lsdDescription`
-- 	`lsdType`


-- Create new table `LKGeneticIndexType`.
-- `LKGeneticIndexType` : Lookup Table of GeneticIndex
-- 	Namespace: Animal, Genetics
-- 	START
-- 	lgiCode	lgiDescription
-- 	RP3	Rampower 3%
-- 	RP6	Rampower 6%
-- 	RP8	Rampower 8%
-- 	RP10	Rampower 10%
-- 	RP12	Rampower 12%
-- 	RP15	Rampower 15%
-- 	RP20	Rampower 20%
-- 	RP30	Rampower 30%
-- 	RP3BW	Rampower 3% + BWT
-- 	RP3SS	Rampower 3% + SS
-- 	RP3FEC50	Rampower 3% + FEC50
-- 	RP3FEC70	Rampower 3% + FEC70
-- 	RP6BW	Rampower 6% + BWT
-- 	RP6SS	Rampower 6% + SS
-- 	RP6FEC50	Rampower 6% + FEC50
-- 	RP6FEC70	Rampower 6% + FEC70
-- 	RP8BW	Rampower 8% + BWT
-- 	RP8SS	Rampower 8% + SS
-- 	RP8FEC50	Rampower 8% + FEC50
-- 	RP10BW	Rampower 10% + BWT
-- 	RP10SS 	Rampower 10% + SS
-- 	RP10FEC50	Rampower 10% +FEC50
-- 	RP12BW 	Rampower 12% + BWT
-- 	RP12FEC50	Rampower 12% + FEC50
-- 	RP15BW 	Rampower 15% + BWT
-- 	RP15SS	Rampower 15% + SS
-- 	RP15FEC50	Rampower 15% + FEC50
-- 	RP20BW	Rampower 20% + BWT
-- 	RP20SS	Rampower 20% + SS
-- 	RP20FEC50	Rampower 20% + FEC50
-- 	RP30BW	Rampower 30% + BWT
-- 	RP30SS	Rampower 30% + SS
-- 	RP30FEC50	Rampower 30% + FEC50
-- 	SG12	SelectGene 12%
-- 	SG15	SelectGene 15%
-- 	SG20	SelectGene 20%
-- 	SG30	SelectGene 30%
-- 	MLA8	MLA 8% Micron
-- 	MLA8DP	MLA 8% Micron + Dual Purpose
-- 	MLA15	MLA 15% Micron
-- 	MLA20	MLA 20% Micron
-- 	MLAWLR	MLA Wool & Reproduction
-- 	MLACRSPL	MLA Carcass Plus
-- 	MLA811	MLA 80:10:10
-- 	MLACOR25	MLA Corriedale $25
-- 	MLACORDP	MLA Corriedale DP
-- 	MLABORD	MLA Border $
-- 	MLACOOP	MLA Coopworth
-- 	MLADPMUSC	MLA Dual Purpose + Muscle
-- 	MLA622	MLA 60:20:20
-- 	MLASAM	MLA SAMM index
-- 	DOHSI	DOHNE Standard Index (High BWT + 12%FW/FD)
-- 	USER1	User defined index 1
-- 	USER2	User defined index 2
-- 	USER3	User defined index 3
-- 	END
-- 	`lgiCode` : Code to identify the genetic index type
-- 	`lgiDescription` : Description of the genetic index type  
create table `LKGeneticIndexType` ( 
	`lgiCode` TEXT(10),
	`lgiDescription` TEXT(250), constraint `LKGeneticIndexType_PK` primary key (`lgiCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKGeneticIndexType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKGeneticIndexType' :
-- 	`lgiCode`
-- 	`lgiDescription`


-- Create new table `LKPostCode`.
-- `LKPostCode` : Lookup Table LKPostCode
-- 	Namespace: All
-- 	Contains the postcodes for all australian post offices - for complete listing refer to Excel table
-- 	lpcCode	lpcLocality	lpcState
-- 	200	AUSTRALIAN NATIONAL UNIVERSITY	ACT
-- 	221	BARTON	ACT
-- 	800	DARWIN	NT
-- 	801	DARWIN	NT
-- 	804	PARAP	NT
-- 	810	ALAWA	NT
-- 	`lpcCode` : pscCode is the 4 digit Australian PostCode
-- 	`lpcLocation` :  Is the location name that represents the PostCode
-- 	`lpcState` : lpcState is of LKPostCode  
create table `LKPostCode` ( 
	`lpcCode` CHAR(4),
	`lpcLocation` VARCHAR(100),
	`lpcState` VARCHAR(20), constraint `LKPostCode_PK` primary key (`lpcCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKPostCode`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKPostCode' :
-- 	`lpcCode`
-- 	`lpcLocation`


-- Create new table `LKState`.
-- `LKState` : Lookup Table LKState
-- 	Namespace: All
-- 	Contains the 2 to 3 letter state codes for Australia:
-- 	START
-- 	lstCode	lstDescription
-- 	ACT	Australian Capital Territory
-- 	NSW	New South Wales
-- 	NT	Northern Territory
-- 	QLD	Queensland
-- 	SA	South Australia
-- 	TAS	Tasmania
-- 	VIC	Victoria
-- 	END
-- 	`lstCode` : sttCode the 2-3 letter code for the Australian state
-- 	`lstDescription` : sttDescription is the state name  
create table `LKState` ( 
	`lstCode` TEXT(3),
	`lstDescription` TEXT(35), constraint `LKState_PK` primary key (`lstCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKState`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKState' :
-- 	`lstCode`
-- 	`lstDescription`


-- Create new table `LKLineType`.
-- `LKLineType` : Table of LKLineType
-- 	Namespace: Wool
-- 	Stores the type of  line  made by the classer. It matches the bale description. The line codes and description are taken from the AWEX Code of Practice. Codes and descriptions for superfine wool are not included as there is not a single standard available. Further information can be obtained from the Australian Superfine wool growers association or Ausfine Direct.
-- 	START
-- 	lltCode     	lltDescription
-- 	AAAM	Main fleece line
-- 	AAM	Short fleece line
-- 	BBB	Broad fleece line
-- 	AAAE	Tender fleece line
-- 	AAAC	Unscourable color line
-- 	AAC	Water stain line
-- 	AAAB	Vegetable Matter fleece line
-- 	A COM	Cotted wool line
-- 	B COM	Doggy line
-- 	DER	Dermatitus line
-- 	BKS	Backs line
-- 	Y	Suffix used after main description to denote risk of contamination from pigmented or medullated fibres
-- 	AAA NKS	Best necks line
-- 	NKS	Necks line
-- 	OG	Overgrown fleece line
-- 	BLK	Black wool line
-- 	KEMP	Kempy wool line
-- 	DD	Dead fleece line
-- 	PLK	Plucked from fleece line
-- 	BKN	Broken line ( skirtings - best line)
-- 	PCS	Pieces line
-- 	BLS	Bellies line
-- 	STN PCS	Stained pieces line
-- 	LKS	Locks line
-- 	AAACRT	Combing length crutchings line
-- 	AACRT	Carding length crutchings line
-- 	STN CRT	Stain crutchings
-- 	BRANDS	Unscourable brands line
-- 	JOWLS	Matted pieces line
-- 	SHANKS	Kempy fibres line from lower part of sheep legs
-- 	SKN PCS	Skin pieces line
-- 	DAG	Dag line - dung coated wool from breech area of sheep
-- 	AAA WNS	Weaners fleece line
-- 	AA WNS	Shorter weaners fleece line
-- 	AAA LMS	Main lambs fleece line
-- 	AA LMS	Short lambs fleece line
-- 	LMS PCS	Lambs pieces line
-- 	STN LMS	Stained lambs wool line
-- 	AAA F	Main fine comeback line - 23 microns or finer
-- 	AAA CBK	Main comeback fleece line - around 25 microns
-- 	AA CBK	Short comeback line
-- 	AAA FX	Main fine crossbred line - around 27 microns
-- 	AA FX	Short fine crossbred line
-- 	AAA MX	Main medium crossbred line - around 30 microns
-- 	AA MX	Short medium crossbred line
-- 	AAA X	Main crossbred line - around 34 microns
-- 	AA X	Short corssbred line
-- 	AAA CX	Coarse crossbred line - around 38 microns
-- 	AA CX	Short coarse crossbred line
-- 	E	Tender crossbred fleece line
-- 	C	Unscourable color crossbred fleece line
-- 	K	Cotted crossbred wool line
-- 	B	Doggy crossbred fleece line
-- 	DERX	Dermatitus crossbred fleece line
-- 	X BRANDS	Crossbred brands line
-- 	CBK PCS	Comeback pieces line
-- 	FX PCS	Fine crossbred pieces line
-- 	MX PCS	Medium crossbred pieces line
-- 	X PCS	Crossbred pieces line
-- 	CX PCS	Coarse crossbred pieces line
-- 	CBK BLS	Comeback bellies line
-- 	FX BLS	Fine crossbred bellies line
-- 	MX BLS	Medium crossbed bellies line
-- 	X BLS	Crossbred bellies line
-- 	CX BLS	Coarse crossbred bellies line
-- 	X STN PCS	Crossbred stain pieces line
-- 	X DAG	Crossbred dag wool line
-- 	CBK LKS	Comeback locks line
-- 	FX LKS	Fine crossbred locks line
-- 	MX LKS	Medium crossbred locks line
-- 	X LKS	Crossbred locks line
-- 	CX LKS	Coarse crossbred locks
-- 	CBK CRT	Comeback crutchings line
-- 	FX CRT	Fine crossbred crutchings line
-- 	MX CRT	Medium crossbred crutchings line
-- 	X CRT	Crossbred crutchings line
-- 	CX CRT	Coarse crossbred crutchings line
-- 	CBK WNRS	Comeback weaners fleece line
-- 	FX WNRS	Fine crossbred weaners fleece line
-- 	MX WNRS	Medium crossbred weaners fleece line
-- 	X WNRS	Crossbred weaners fleece line
-- 	CX WNRS	Coarse crossbred weaners fleece line
-- 	AAA CBK LMS	Main line of comeback lambs fleece
-- 	AAA FX LMS	Main line of fine crossbred lambs fleece
-- 	AAA MX LMS	Main line of medium crossbred lambs fleece
-- 	AAA X LMS	Main line of crossbred lambs fleece
-- 	AAA CX LMS	Main line of coarse crossbred lambs fleece
-- 	AA CBK LMS	Shorter line of comeback lambs fleece
-- 	AA FX LMS	Shorter line of fine crossbred lambs fleece
-- 	AA MX LMS	Shorter line of medium crossbred lambs fleece
-- 	AA X LMS	Shorter line of crossbred lambs fleece
-- 	AA CX LMS	Shorter line of coarse crossbred lambs fleece
-- 	CBK LMS PCS	Comeback lambs pieces and bellies line
-- 	FX LMS PCS	Fine crossbred lambs pieces and bellies line
-- 	MX LMS PCS	Medium crossbred lambs piecesand bellies line
-- 	X LMS PCS	Crossbred lambs pieces and bellies line
-- 	CX LMS PCS	Coarse crossbred lambs pieces and bellies line
-- 	X STN LMS	Crossbred lambs stained wool line
-- 	X SKN PCS	Crossbred skin pieces line
-- 	AAA DN	Main downs character fleece line
-- 	AA DN	Short downs character fleece line
-- 	DN C	Water stained downs character fleece line
-- 	OG DN	Overgrown downs character fleece line
-- 	BLK DN	Black wool downs character fleece line
-- 	DN PCS	Downs character pieces line
-- 	DN BLS	Downs character bellies line
-- 	DN CRT	Downs character crutchings line
-- 	DN BRANDS	Downs character fleece brands line
-- 	DN DAG	Downs character dag wool line
-- 	DN SKN PCS	Downs character skin pieces line
-- 	WNS DN	Downs character weaners fleece line
-- 	AAA DN LMS	Main line of downs character lambs fleece
-- 	AA DN LMS	Short downs character lambs fleece line
-- 	DN LMS PCS	Downs character lambs pieces and bellies line
-- 	DN STN LMS	Downs character staind lambs fleece line
-- 	AAA CW	Main line of carpet wool fleece
-- 	AA CW	Secondary line of carpet wool fleece
-- 	BLK CW	Black carpet wool fleece line
-- 	OG CW	Overgrown carpet wool fleece line
-- 	A CW PCS	Best line of carpet wool pices
-- 	CW PCS	Secondary line of carpet wool pieces
-- 	A CW BLS	Best line of carpet wool bellies
-- 	CW BLS	Secondary line of carpet wool bellies
-- 	CW LKS	Carpet wool locks line
-- 	CW CRT	Carpet wool crutchings line
-- 	CW BRANDS	Carpet wool fleece brands line
-- 	CW SKN PCS	Carpet wool skin pieces line
-- 	CW LMS	Carpet wool lambs fleece line
-- 	CW LMS PCS	Carpet wool lambs pieces line
-- 	CW LMS BLS	Carpet wool lambs bellies line
-- 	CW LMS SKN PCS	Carpet wool lambs skin pieces line
-- 	END
-- 	`lltCode` : The code for the line type
-- 	`lltDescription` : A description of the line  
create table `LKLineType` ( 
	`lltCode` TEXT(20),
	`lltDescription` TEXT(250), constraint `LKLineType_PK` primary key (`lltCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKLineType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKLineType' :
-- 	`lltCode`
-- 	`lltDescription`


-- Create new table `LKDeliveryCentre`.
-- `LKDeliveryCentre` : Table of LKDeliveryCentre
-- 	Namespace: Wool
-- 	This table contains a list of wool delivery centres and their codes (as defined by AWEX) and denotes where the wool is actually stored.
-- 	Descriptions in capitals refer to delivery centres that are sale centres as well.
-- 	START
-- 	ldcCode	ldcDescription
-- 	A	ADELAIDE
-- 	AR	Armidale
-- 	B	BRISBANE
-- 	BA	Benalla
-- 	BK	Bourke
-- 	BL	Ballarat
-- 	BM	Barham
-- 	BT	Bathurst
-- 	C	Cooma
-- 	CB	Coonabarabran
-- 	CN	Condoblin
-- 	CR	Corowa
-- 	D	Bendigo
-- 	DO	Dubbo
-- 	DR	Canowindra
-- 	E	Echuca
-- 	ER	Euroa
-- 	ES	Esperance
-- 	F	FREMANTLE
-- 	G	GEELONG
-- 	GR	Griffiths
-- 	H	Hobart
-- 	HB	Holbrook
-- 	HM	Hamilton
-- 	HO	Horsham
-- 	I	Narribri
-- 	J	Parkes
-- 	K	Warracknabeal
-- 	KT	Katanning
-- 	KW	Wagga Wagga
-- 	KY	Kyneton
-- 	L	LAUNCESTON
-- 	M	MELBOURNE
-- 	MO	Moree
-- 	MU	Mudgee
-- 	N	NEWCASTLE
-- 	NA	Narrandera
-- 	ND	Goondiwindi
-- 	OB	Oberon
-- 	OR	Orange
-- 	P	Portland
-- 	PA	Cowra
-- 	Q	Yarrawonga
-- 	QU	Queenbeyan
-- 	R	GOULBURN
-- 	S	SYDNEY
-- 	SY	Seymour
-- 	T	Inverell
-- 	TE	Temora
-- 	U	Auburn
-- 	V	Forbes
-- 	W	Tamworth
-- 	WA	Warren
-- 	WG	Walgett
-- 	WL	Walcha
-- 	WN	Wellington
-- 	WR	Wangaratta
-- 	WW	West Wyalong
-- 	X	Ararat
-- 	Y	Albury
-- 	YA	Yass
-- 	YO	Young
-- 	Z	Albany
-- 	END
-- 	`ldcCode` : ldcCode identifies LKDeliveryCentre
-- 	`ldcDescription` : ldcDescription is of LKDeliveryCentre  
create table `LKDeliveryCentre` ( 
	`ldcCode` TEXT(10),
	`ldcDescription` TEXT(35), constraint `LKDeliveryCentre_PK` primary key (`ldcCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKDeliveryCentre`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKDeliveryCentre' :
-- 	`ldcCode`
-- 	`ldcDescription`


-- Create new table `LKWoolPackType`.
-- `LKWoolPackType` : Table of LKWoolPackType 
-- 	Namespace: Wool
-- 	Wool Pack Type  Codes as per the AWEX Classer Specification
-- 	START
-- 	lwpCode	lwpDescription
-- 	NEW	Wool pack that is being used for the first time
-- 	USED	A wool pack that has been previously used to store wool
-- 	RECONY	AWEX Approved reconditioned pack
-- 	RECONN	Non AWEX Approved
-- 	END
-- 	`lwpCode` : The code for identifying the type of wool pack (according to use)
-- 	`lwpDescription` : The full description of the wool pack type (new or used)  
create table `LKWoolPackType` ( 
	`lwpCode` TEXT(10),
	`lwpDescription` TEXT(250), constraint `LKWoolPackType_PK` primary key (`lwpCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKWoolPackType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKWoolPackType' :
-- 	`lwpCode`
-- 	`lwpDescription`


-- Create new table `LKOfferingInstruction`.
-- `LKOfferingInstruction` : Table of LKOfferingInstruction
-- 	Namespace: Wool
-- 	This table describes the instructions given by the grower on the classer specification as to how the broker is to handle the sale of the wool once it is received into store.
-- 	START
-- 	loiCode	loiDescription
-- 	FIRST	1st available sale
-- 	HOLD	Test and hold for offering instructions
-- 	PARTOFFER	Part offer clip lines as complete
-- 	FUTURE	Future sale
-- 	END
-- 	`loiCode` : The code representing the offering instruction
-- 	`loiDescription` : A decription of the offering instruction code  
create table `LKOfferingInstruction` ( 
	`loiCode` TEXT(10),
	`loiDescription` TEXT(250), constraint `LKOfferingInstruction_PK` primary key (`loiCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKOfferingInstruction`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKOfferingInstruction' :
-- 	`loiCode`
-- 	`loiDescription`


-- Create new table `LKClipType`.
-- `LKClipType` : Table of LKClipType
-- 	Namespace: Wool
-- 	Clip Type as per the AWEX Classer Specification
-- 	START
-- 	lcpCode	lcpDescription
-- 	CBK	Wool is from comeback bred sheep
-- 	MER	Wool is from merino sheep
-- 	XBD	Wool is from crossbred sheep
-- 	END
-- 	`lcpCode` : Code for the clip type (type of sheep, e.g. merino)
-- 	`lcpDescription` : Description of the clip type  
create table `LKClipType` ( 
	`lcpCode` TEXT(10),
	`lcpDescription` TEXT(35), constraint `LKClipType_PK` primary key (`lcpCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKClipType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKClipType' :
-- 	`lcpCode`
-- 	`lcpDescription`


-- Create new table `LKPaddockFertilizerType`.
-- `LKPaddockFertilizerType` : Table of PaddockFertilizerType
-- 	Namespace: Animal
-- 	This table is a lookup table of various fertilizer types and their codes.  This list is user defined.
-- 	`lftCode` : The user defined code for the fertiliser type
-- 	`lftDescription` : The description of the fertiliser type  
create table `LKPaddockFertilizerType` ( 
	`lftCode` TEXT(10),
	`lftDescription` TEXT(35), constraint `LKPaddockFertilizerType_PK` primary key (`lftCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKPaddockFertilizerType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKPaddockFertilizerType' :
-- 	`lftCode`
-- 	`lftDescription`


-- Create new table `PaddockFertilizerHistory`.
-- `PaddockFertilizerHistory` : Table of PaddockFertilizerHistory
-- 	Namespace: Animal
-- 	This table keeps a history of fertilizer applications to a paddock.
-- 	`pfhPrimaryId` : pfhPrimaryId identifies PaddockFertilizerHistory
-- 	`pfhDate` : The date the fertiliser was applied to the paddock.
-- 	`pfhPdkPrimaryId` : The paddock to which the fertiliser was applied
-- 	`pfhLmuCode` : The rate of the fertiliser application (see LKMeasurementUnit)
-- 	`pfhLftCode` : The type of fertiliser applied (see LKFertiliserType)  
create table `PaddockFertilizerHistory` ( 
	`pfhPrimaryId` CHAR(15),
	`pfhDate` DATETIME,
	`pfhPdkPrimaryId` CHAR(15),
	`pfhLmuCode` TEXT(10),
	`pfhLftCode` TEXT(10), constraint `PaddockFertilizerHistory_PK` primary key (`pfhPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`PaddockFertilizerHistory`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'PaddockFertilizerHistory' :
-- 	`pfhPrimaryId`
-- 	`pfhDate`
-- 	`pfhPdkPrimaryId`
-- 	`pfhLmuCode`
-- 	`pfhLftCode`


-- Create new table `LKTreatmentType`.
-- `LKTreatmentType` : Table of PaddockChemicalType
-- 	Namespace: Animal
-- 	This table contains a list of chemicals that may be used on farm for paddock applications or grain/fodder storage applications. This list contains the chamical names and their codes for herbicides, fungicides and pesticides.  The list is sourced from the Australian Pesticides and Veterinary Medicines Authority (APVMA) database PUBCRIS which is a register of all agricultural and veterinary chemicals registered for use in Australia. The listing is a subset of PUBCRIS. Details about chemicals can be found by searching the PUBCRIS database which is located on the following website: http://www.apvma.gov.au/
-- 	Withholding periods for chemicals have to be entered by the sheep producer as they are not listed on the PUBCRIS database. Details about withholding periods are found on the labels of the chemicals. The database stores a picture of each label as a reference.
-- 	Each chemical has a unique code on the PUBCRIS database, which we have used in this look up table.
-- 	`ltyCode` : The chemical code
-- 	`ltyDescription` : The description of the type of treatment applied to the paddock
-- 	`ltyWitholdingPeriod` : The number of days for the witholding period of the chemical
-- 	`ltyProduct` : The name of the product
-- 	`ltyManufacturer` : The manufacturer of the product
-- 	`ltyNRCISNumber` : The NRCIS number of the product  
create table `LKTreatmentType` ( 
	`ltyCode` TEXT(10),
	`ltyDescription` TEXT(35),
	`ltyWitholdingPeriod` SHORT,
	`ltyProduct` TEXT(35),
	`ltyManufacturer` TEXT(35),
	`ltyNRCISNumber` CHAR(5), constraint `LKTreatmentType_PK` primary key (`ltyCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKTreatmentType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKTreatmentType' :
-- 	`ltyCode`
-- 	`ltyDescription`
-- 	`ltyWitholdingPeriod`
-- 	`ltyProduct`
-- 	`ltyManufacturer`
-- 	`ltyNRCISNumber`


-- Create new table `PaddockTreatmentHistory`.
-- `PaddockTreatmentHistory` : Table of PaddockChemicalHistory
-- 	Namespace: Animal
-- 	Is a history table linking paddocks to to the application of chemicals to the paddock.
-- 	It is  based on MLA's Livestock Production Assurance (LPA) onfarm food safety certification program.
-- 	`pthPrimaryId` : pthPrimaryId identifies PaddockTreatmentHistory
-- 	`pthDate` : The date the chemical was applied
-- 	`pthPdkPrimaryId` : The paddcok to which the chemical was applied
-- 	`pthLptCode` : The chemical type applied to the paddock (see LKPaddockChemicalType)
-- 	`pthLmuCode` : The measurement units for the application of the chemical (see LKMeasurementunit)
-- 	`pthAreaTreated` : The area (measured in hectares) of the paddock that was treated
-- 	`pthBatchNumber` : The batch number of the chemical treatment used on the paddock
-- 	`pthApplicationRate` : The application rate of the chemical treatment applied to the paddock
-- 	`pthLpaCode` : The code for the method of application of a chemical treatment to a crop, pasture or paddock
-- 	`pthExpirydate` : The expiry date of the chemical used to treat the paddock
-- 	`pthSafeGrazingDate` : The date the paddock is safe for grazing by sheep after chemical treatment  
create table `PaddockTreatmentHistory` ( 
	`pthPrimaryId` CHAR(15),
	`pthDate` DATETIME,
	`pthPdkPrimaryId` CHAR(15),
	`pthLptCode` TEXT(10),
	`pthLmuCode` TEXT(10),
	`pthAreaTreated` LONG,
	`pthBatchNumber` CHAR(10),
	`pthApplicationRate` CHAR(10),
	`pthLpaCode` VARCHAR(10),
	`pthExpirydate` DATETIME,
	`pthSafeGrazingDate` DATETIME, constraint `PaddockTreatmentHistory_PK` primary key (`pthPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`PaddockTreatmentHistory`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'PaddockTreatmentHistory' :
-- 	`pthPrimaryId`
-- 	`pthDate`
-- 	`pthPdkPrimaryId`
-- 	`pthLptCode`
-- 	`pthLmuCode`
-- 	`pthAreaTreated`
-- 	`pthBatchNumber`
-- 	`pthApplicationRate`
-- 	`pthLpaCode`
-- 	`pthExpirydate`
-- 	`pthSafeGrazingDate`


-- Create new table `LKAnimalEventType`.
-- `LKAnimalEventType` : Table of AnimalEventType
-- 	Namespace: Animal
-- 	An animal event occurs to a sheep as an action and is not a trait or a chemical treatment of the animal.
-- 	START
-- 	laeCode	laeDescription
-- 	MULES	Mulesing
-- 	PREGSCAN	Pregnancy scanning
-- 	SHEAR	Shearing
-- 	CRUTCH	Crutching
-- 	END
-- 	`laeCode` : The code for the animal event
-- 	`laeDescription` : The description of the animal event  
create table `LKAnimalEventType` ( 
	`laeCode` CHAR(10),
	`laeDescription` TEXT(35), constraint `LKAnimalEventType_PK` primary key (`laeCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKAnimalEventType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKAnimalEventType' :
-- 	`laeCode`
-- 	`laeDescription`


-- Create new table `AnimalEventHistory`.
-- `AnimalEventHistory` : Table of AnimalEventHistory
-- 	Namespace: Animal
-- 	This table is a history linking animals to their events.
-- 	`aevAmlPrimaryId` : The id of the animal to which the event occurred
-- 	`aevPrimaryId` : aevPrimaryId identifies AnimalEventHistory
-- 	`aevDate` : The date which the event occurred
-- 	`aevLaeCode` : The animal event type (see LKAnimalEvent)
-- 	`aevComment` : A general comment about the event  
create table `AnimalEventHistory` ( 
	`aevAmlPrimaryId` CHAR(15),
	`aevPrimaryId` CHAR(15),
	`aevDate` DATETIME,
	`aevLaeCode` CHAR(10),
	`aevComment` TEXT(64), constraint `AnimalEventHistory_PK` primary key (`aevPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`AnimalEventHistory`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'AnimalEventHistory' :
-- 	`aevAmlPrimaryId`
-- 	`aevPrimaryId`
-- 	`aevDate`
-- 	`aevLaeCode`


-- Create new table `LKBurrSeed`.
-- `LKBurrSeed` : Lookup Table LKBurrSeed
-- 	Namespace: Wool
-- 	BurrSeed status as per the AWEX Classer Specification
-- 	START
-- 	lbsCode	lbsDescription
-- 	LIGHT	Light burr/seed
-- 	MEDIUM	Medium burr/seed
-- 	HEAVY	Heavy burr/seed
-- 	END
-- 	`lbsCode` : The code for the burr/seed status
-- 	`lbsDescription` : The full description of the burr/seed status  
create table `LKBurrSeed` ( 
	`lbsCode` TEXT(6),
	`lbsDescription` CHAR(35), constraint `LKBurrSeed_PK` primary key (`lbsCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKBurrSeed`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKBurrSeed' :
-- 	`lbsCode`
-- 	`lbsDescription`


-- Create new table `LKWoolQuality`.
-- `LKWoolQuality` : Lookup Table  LKWoolQuality
-- 	Namespace: Wool
-- 	Wool quality as per the AWEX  Classer Specification
-- 	START
-- 	lwqCode	lwqDescription
-- 	FINE	Fine wool quality
-- 	MEDIUM	Medium wool quality
-- 	STRONG	Strong wool quality
-- 	END
-- 	`lwqCode` : The Wool Quality code
-- 	`lwqDescription` : The full description of the Wool Quality Code  
create table `LKWoolQuality` ( 
	`lwqCode` TEXT(6),
	`lwqDescription` CHAR(35), constraint `LKWoolQuality_PK` primary key (`lwqCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKWoolQuality`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKWoolQuality' :
-- 	`lwqCode`
-- 	`lwqDescription`


-- Create new table `LKWoolResidualRisk`.
-- `LKWoolResidualRisk` : Lookup Table of LKWoolResidualRisk
-- 	Namespace: Wool
-- 	Wool Residual Risk Codes as per the AWEX Classer Specification
-- 	START
-- 	lwrCode	lwrDescription
-- 	NA	Not available
-- 	NONE	None
-- 	LOW	Low
-- 	HIGH	High
-- 	END
-- 	`lwrCode` : The  Wool Residual Risk code
-- 	`lwrDescription` : The description of the Wool Residual Risk  
create table `LKWoolResidualRisk` ( 
	`lwrCode` VARCHAR(10),
	`lwrDescription` VARCHAR(50), constraint `LKWoolResidualRisk_PK` primary key (`lwrCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKWoolResidualRisk`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKWoolResidualRisk' :
-- 	`lwrCode`
-- 	`lwrDescription`


-- Create new table `LKBreedType`.
-- `LKBreedType` : Lookup Table  LKBreedType
-- 	Namespace: All
-- 	2 digit breed codes for sheep as defined by MLA Lamb Plan
-- 	START
-- 	lbyCode	lbyMLACode	lbyDescription
-- 	STUDOTHER	0	STUD OTHER
-- 	STAND	1	STANDARD
-- 	BORDERLSTER	2	BORDER LEICESTER
-- 	CORRDALE	3	CORRIEDALE
-- 	DORSETHORN	4	DORSET HORN
-- 	COOLALEE	5	COOLALEE
-- 	BOND	6	BOND
-- 	GROMARK	7	GROMARK
-- 	HYFER	8	HYFER
-- 	WILTHORN	9	WILTSHIRE HORN
-- 	POLWARTH	10	POLWARTH
-- 	HAMPDOWN	11	HAMPSHIRE DOWN
-- 	LINCOLN	12	LINCOLN
-- 	ALTMERINO	13	MERINO
-- 	SOUTHDOWN	14	SOUTHDOWN
-- 	COOPWORTH	15	COOPWORTH
-- 	POLLDOR	16	POLL DORSET
-- 	TEXEL	17	TEXEL
-- 	ROMNEY	18	ROMNEY
-- 	SUFFOLK	19	SUFFOLK
-- 	TUKIDALE	20	TUKIDALE
-- 	STHSUFFOLK	21	SOUTH SUFFOLK
-- 	AUSFINN	22	AUSTRALIAN FINNSHEEP
-- 	WHSUFFOLK	23	WHITE SUFFOLK
-- 	LONGWOOL	24	LONGWOOL
-- 	SHORTWOOL	25	SHORTWOOL
-- 	RESWILT	26	RESEARCH & WILTIPOLL
-- 	SHROP	27	SHROPSHIRE
-- 	CHEVIOT	28	CHEVIOT
-- 	STHDORSET	29	SOUTH DORSET DOWN
-- 	CARPET	30	CARPETWOOL
-- 	ENGLEIC	31	ENGLISH LEICESTER
-- 	PERENDALE	32	PERENDALE
-- 	DORSET	33	DORSET DOWN
-- 	RYELAND	34	RYELAND
-- 	STHHAMP	35	SOUTH HAMPSHIRE
-- 	BOERGOAT	36	BOER GOATS Australia
-- 	TEXDOWN	37	TEXEL DOWNS
-- 	EASTFRSN	38	EAST FRIESIAN
-- 	CORRBOOR	39	CORRIEDALE BOOROOLA
-- 	NZROMNEY	41	NZ ROMNEY
-- 	CANSUFFOLK	42	SUFFOLK CANADA
-- 	FINNLEIC	43	FINN LEICESTERS
-- 	ELLIOT	44	ELLIOTDALES
-- 	REDDEER	45	RED DEER
-- 	ELKDEER	46	ELK DEER
-- 	DORPER	47	DORPER
-- 	SAMM	48	SAMM
-- 	DAMARA	49	DAMMARA
-- 	MERINO	50	MERINO
-- 	DOHNE	51	DOHNE
-- 	ARRAMBLT	56	ARENA RAMBOULIET
-- 	ARMERI	57	ARENA MERIDALE
-- 	POLLMERINO	60	POLL MERINO
-- 	SFINEMERINO	70	SUPERFINE MERINO
-- 	NZMERINO	74	NZ MERINO
-- 	NZCOOP	75	NZ COOPWORTH
-- 	NZLINCOLN	76	NZ LINCOLN
-- 	NZBORDER	77	NZ BORDERDALE
-- 	NZOXDOWN	78	NZ OXFORD DOWN
-- 	NZWHHEAD	79	NZ WHITE HEAD M
-- 	NZDRYSDALE	80	NZ DRYSDALE
-- 	NZLINCWORTH	81	NZ LINCWORTH
-- 	NZBORDLEIC	82	NZ BORDER LEICESTER
-- 	NZCORRIE	83	NZ CORRIEDALE
-- 	NZDORHORN	84	NZ DORSET HORN
-- 	NZPOLLMER	85	NZ POLL MERINO
-- 	NZPOLLDOR	86	NZ POLL DORSET
-- 	NZTEXEL	87	NZ TEXEL
-- 	NZSTHSUFF	88	NZ STH SUFFOLK
-- 	NZSUFFOLK	89	NZ SUFFOLK
-- 	NZPOLWORTH	90	NZ POLWARTH
-- 	NZHAMP	91	NZ HAMPSHIRE
-- 	NZFINN	92	NZ FINNSHEEP
-- 	NZDORDOWN	93	NZ DORSET DOWN
-- 	NZRYELAND	94	NZ RYELAND
-- 	NZENGLEI	95	NZ ENGLISH LEICESTER
-- 	NZHALFBRED	96	NZ HALFBRED
-- 	NZSHROP	97	NZ SHROPSHIRE
-- 	NZGOAT	98	NZ GOTLAND PELT
-- 	NZSTHDOWN	99	NZ SOUTHDOWN
-- 	CLUNFOREST	CF	CLUN FOREST
-- 	COMMMAT	CM	Commercial Maternal
-- 	COMMTERM	CT	Commercial Terminal
-- 	PROGTEST	CP	Central Progeny Test sites
-- 	XBD	XB	Cross Breeds
-- 	END
-- 	`lbyMLACode` : Code to identify the sheep breed
-- 	`lbyDescription` : Description of the sheep breed
-- 	`lbyCode` : lbyCode partly identifies LKBreedType  
create table `LKBreedType` ( 
	`lbyMLACode` CHAR(2),
	`lbyDescription` TEXT(35),
	`lbyCode` TEXT(12), constraint `LKBreedType_PK` primary key (`lbyCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKBreedType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKBreedType' :
-- 	`lbyMLACode`
-- 	`lbyDescription`
-- 	`lbyCode`


-- Create new table `LKAge`.
-- `LKAge` : LookupTable Age
-- 	Namespace: Wool
-- 	Contains the age as per the AWEX Classer Specification
-- 	START
-- 	laeCode	laeDescription
-- 	1	0-12 months
-- 	2	13-24 months
-- 	3	25-36 months
-- 	4	37-48 months
-- 	5	49-60 months
-- 	1-2	mixed 0-24 months
-- 	2-4	mixed 13-48 months
-- 	END
-- 	`laeCode` : Represents the code for the age
-- 	`laeDescription` : A full description of the age code  
create table `LKAge` ( 
	`laeCode` TEXT(5),
	`laeDescription` TEXT(35), constraint `LKAge_PK` primary key (`laeCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKAge`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKAge' :
-- 	`laeCode`
-- 	`laeDescription`


-- Create new table `LKSheepAgeCode`.
-- `LKSheepAgeCode` : Lookup Table LKSheepCode
-- 	Namespace: Animal, Genetics
-- 	Sheep age codes: As per MLA definitions
-- 	START
-- 	lsaCode	lsaFromDays	lsaToDays 	lsaDescription
-- 	B 	0	0	Birth
-- 	L	1	40	Lamb
-- 	WN   	40	120	Weaning
-- 	EP	80	240	Early Post Weaning
-- 	P	160	340	Post Weaning
-- 	Y	290	430	Yearling
-- 	H	410	550	Hogget
-- 	A 	530	2345	Adult 
-- 	A2	530	1000	Adult 2 years
-- 	A3 	2.4yrs	3.6yrs	Adult 3 years
-- 	A4 	3.4yrs	4.6yrs	Adult 4 years
-- 	A5 	4.4yrs	5.6yrs	Adult 5 years
-- 	END
-- 	`lsaCode` : The Young Sheep Code
-- 	`lsaDescription` : A full description of the Young Sheep Code
-- 	`lsaFromDays` : The minimum number of days from which the code is valid
-- 	`lsaToDays` : The maximum number of days to which the code is valid  
create table `LKSheepAgeCode` ( 
	`lsaCode` TEXT(2),
	`lsaDescription` CHAR(250),
	`lsaFromDays` TEXT(8),
	`lsaToDays` TEXT(8), constraint `LKSheepAgeCode_PK` primary key (`lsaCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKSheepAgeCode`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKSheepAgeCode' :
-- 	`lsaCode`
-- 	`lsaDescription`
-- 	`lsaFromDays`
-- 	`lsaToDays`


-- Create new table `LKSexCode`.
-- `LKSexCode` : Table of SexCodes as per the AWEX Classer Specification
-- 	Namespace: All
-- 	START
-- 	lscCode	lscDescription
-- 	E	Ewe
-- 	W	Wethers
-- 	R	Rams
-- 	M	Mixed E/W/R
-- 	U	Unknown
-- 	END
-- 	`lscCode` : The sex code
-- 	`lscDescription` : The full description of the sex code  
create table `LKSexCode` ( 
	`lscCode` CHAR(1),
	`lscDescription` CHAR(250), constraint `LKSexCode_PK` primary key (`lscCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKSexCode`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKSexCode' :
-- 	`lscCode`
-- 	`lscDescription`


-- Create new table `LKPastureType`.
-- `LKPastureType` : Lookup Table of PastureType
-- 	Namespace: Animal
-- 	A lookup table of pasture types.  Pasture species are listed from the Australian Grasslands Society database.
-- 	Pasture types are:
-- 	START
-- 	lptCode                   	lptDescription	lptGrouping
-- 	ARRCLOVER           	Arrowleaf clover 	Legume
-- 	BALACLOVER          	Balansa clover 	Legume
-- 	BARRMEDIC            	Barrel medic 	Legume
-- 	BERCLOVER           	Berseem clover 	Legume
-- 	BIRDCLOVER            	Birdsfoot trefoil 	Legume
-- 	CAUCCLOVER         	Caucasian clover 	Legume
-- 	CHICORY             	Chicory 	Forage
-- 	COCKSKFOOT               	Cocksfoot 	Grass
-- 	GRAZBROME            	Grazing brome 	Grass
-- 	GRLOTUS              	Greater lotus 	Legume
-- 	GAMAMEDIC            	Gama medic 	Legume
-- 	HYRYE                  	Hybrid ryegrass 	Grass
-- 	HYSERA                  	Hybrid seradella 	Legume
-- 	ITALYRYE                   	Italian ryegrass 	Grass
-- 	KIKUYA                 	Kikuya 	Grass
-- 	LUCERNE             	Lucerne 	Legume
-- 	PASPALUM                     	Paspalum 	Grass
-- 	PASBROME                	Pasture brome 	Grass
-- 	PERSCLOVER              	Persian clover 	Legume
-- 	PRAIRGRASS                	Prairie grass 	Grass
-- 	PHALARIS             	Phalaris 	Grass
-- 	PLANTAIN             	Plantain 	Forage herb
-- 	PERENRYE                 	Perennial ryegrass 	Grass
-- 	PINKSERRA                	Pink Serradella 	Legume
-- 	PUCCIN                     	Puccinellia 	Grass
-- 	REDCLOVER               	Red clover 	Legume
-- 	SPBURRMEDIC            	Spineless burr medic 	Legume
-- 	SUBCLOVER                	Subterranean clover 	Legume
-- 	SNAILMEDIC             	Snail medic 	Legume
-- 	STRAWCLOVER          	Strawberry clover 	Legume
-- 	STRMEDIC                  	Strand medic 	Legume
-- 	TIMOTHY                        	Timothy 	Grass
-- 	TALLFESCUE                	Tall fescue 	Grass
-- 	TALLWHEAT                	Tall wheatgrass 	Grass
-- 	WHCLOVER                 	White clover 	Legume
-- 	WIMMRYE               	Wimmera ryegrass 	Grass
-- 	YELSERRA                	Yellow serradella 	Legume
-- 	END
-- 	`lptCode` : lptCode identifies LKPastureType
-- 	`lptDescription` : A full Description of the pasture type
-- 	`lptGrouping` : The grouping the pasture type belongs to (for example, legume)  
create table `LKPastureType` ( 
	`lptCode` VARCHAR(50),
	`lptDescription` VARCHAR(50),
	`lptGrouping` VARCHAR(50), constraint `LKPastureType_PK` primary key (`lptCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKPastureType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKPastureType' :
-- 	`lptCode`
-- 	`lptDescription`


-- Create new table `ContactBankAccount`.
-- `ContactBankAccount` : Table of ContactrBankAccount
-- 	Namespace: Animal, Wool
-- 	Contains details of contact bank accounts. 
-- 	`cbaPrimaryId` : cbaPrimaryId identifies ContactBankAccount
-- 	`cbaBSB` : The bank state branch number of the bank account
-- 	`cbaAccountNumber` : The account number of the bank account
-- 	`cbaAccountName` : The name on the account
-- 	`cbaBankName` : Name of the bank
-- 	`cbaIsEFT` : Is true if the contact accepts EFT transactions
-- 	`cbaBranchLocation` : The bank branch location
-- 	`cbaCntPrimaryId` : Identifies the contact to which the account details belong  
create table `ContactBankAccount` ( 
	`cbaPrimaryId` CHAR(15),
	`cbaBSB` CHAR(6),
	`cbaAccountNumber` TEXT(10),
	`cbaAccountName` TEXT(35),
	`cbaBankName` CHAR(10),
	`cbaIsEFT` BIT,
	`cbaBranchLocation` CHAR(10),
	`cbaCntPrimaryId` CHAR(15), constraint `ContactBankAccount_PK` primary key (`cbaPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`ContactBankAccount`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'ContactBankAccount' :
-- 	`cbaPrimaryId`
-- 	`cbaBSB`
-- 	`cbaAccountNumber`
-- 	`cbaAccountName`
-- 	`cbaBankName`
-- 	`cbaIsEFT`
-- 	`cbaBranchLocation`


-- Create new table `LKRearType`.
-- `LKRearType` : Look up table of RearType
-- 	Namespace: Animal, Genetics
-- 	START
-- 	lrtCode	lrtDescription
-- 	0	Unknown
-- 	1	Single
-- 	2	Twin
-- 	3	Triplet
-- 	4	Quad
-- 	5	Quintuplet
-- 	6	Sextuplet
-- 	7	More than 6
-- 	SEXT	Sextuplets
-- 	MANY	More than 6
-- 	END
-- 	`lrtCode` : The code for the rear type
-- 	`lrtDescription` : A description for the rear type code  
create table `LKRearType` ( 
	`lrtCode` CHAR(10),
	`lrtDescription` VARCHAR(40), constraint `LKRearType_PK` primary key (`lrtCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKRearType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKRearType' :
-- 	`lrtCode`
-- 	`lrtDescription`


-- Create new table `LKContactTitle`.
-- `LKContactTitle` : Lookup Table LKContactTitle
-- 	Namespace: All
-- 	This is a lookup table containing various titles a contact may have.  Contact titles are:
-- 	START
-- 	lctCode	lctDescription
-- 	DR	Doctor
-- 	MISS	Miss
-- 	MR	Mister
-- 	MRS	Missers
-- 	MS	Miss or Mrs
-- 	PROF	Professor
-- 	END
-- 	`lctCode` : Code for the producer's title.
-- 	`lctDescription` : The full description of the title code.  
create table `LKContactTitle` ( 
	`lctCode` VARCHAR(10),
	`lctDescription` TEXT(35), constraint `LKContactTitle_PK` primary key (`lctCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKContactTitle`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKContactTitle' :
-- 	`lctCode`
-- 	`lctDescription`


-- Create new table `LKAnimalType`.
-- `LKAnimalType` : Table of AnimalType
-- 	Namespace: Animal, Genetics
-- 	This is a lookup table of animal type:
-- 	START
-- 	layCode	layDescription
-- 	SIREON	Sire - on farm
-- 	SIREOFF	Sire - off farm
-- 	DAMON	Dam - on farm
-- 	DAMOFF	Dam - off farm
-- 	WETHER	Wether
-- 	EWE	Ewe
-- 	BACKUP	Backup sire
-- 	HOGGET	Hogget
-- 	LAMB	Lamb
-- 	WEANER	Weaner
-- 	END
-- 	`layCode` : This denotes the type of an animal (whether it is on farm or not)
-- 	`layDescription` : Description of the animal type code  
create table `LKAnimalType` ( 
	`layCode` TEXT(10),
	`layDescription` VARCHAR(35), constraint `LKAnimalType_PK` primary key (`layCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKAnimalType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKAnimalType' :
-- 	`layCode`
-- 	`layDescription`


-- Create new table `LKConceptionMethod`.
-- `LKConceptionMethod` : Table of MatingType
-- 	Namespace: Animal, Genetics
-- 	Look up table of conception methods.
-- 	Values for matType are:
-- 	START
-- 	lcmCode	lcmDescription
-- 	NATURAL	Naturally born
-- 	ET	Embryo Transfer
-- 	AI	Artificial Insemination
-- 	JIVET	Juvenile In Vitro Embryo Transfer
-- 	END
-- 	`lcmCode` : The code for the method of conception
-- 	`lcmDescription` : The description of the conception method code  
create table `LKConceptionMethod` ( 
	`lcmCode` VARCHAR(10),
	`lcmDescription` VARCHAR(40), constraint `LKConceptionMethod_PK` primary key (`lcmCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKConceptionMethod`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKConceptionMethod' :
-- 	`lcmCode`
-- 	`lcmDescription`


-- Create new table `Mating`.
-- `Mating` : Table of Mating
-- 	Namespace: Animal, Genetics
-- 	Identify dam and sires but an animal will need to be identified by its gestation period.
-- 	`mtgPrimaryId` : mtgPrimaryId identifies Mating
-- 	`mtgStartDate` : The date that mating starts
-- 	`mtgDamId` : Foreign key damId is from table Animal
-- 	`mtgEndDate` : The date mating stops
-- 	`mtgLcmCode` : The type of conception method (see LKConceptionMethod)
-- 	`mtgPregnancyTested` : Is true if ewe has been scanned to determine pregnancy
-- 	`mtgPregnant` : Is true if ewe is pregnant
-- 	Is yes if ewe is pregnant
-- 	`mtgPregnancyTestDate` : Date pregnancy test occurred
-- 	Date pregnancy test occurred
-- 	`mtgEmbryoNumber` : Number of embryos present at pregnancy testing ( 0 - 6)
-- 	`mtgPregnancyTestingJobNumber` : Job number allocated by licenced pregnancy testing operator
-- 	`mtgBackupSireId` : ID of ram used as a back up sire
-- 	`mtgBackupSireSuccessful` : Is yes if ewe is pregnant from back up sire (determined at pregnancy testing).
-- 	`mtgRecipientEweTagNumber` : Unique Tag Number  of recipient ewe in ET program (can only be used for one mating). Requirement of ADBA
-- 	`mtgAmlPrimaryId` : The animal id of the animal conceived  
create table `Mating` ( 
	`mtgPrimaryId` CHAR(15),
	`mtgStartDate` DATETIME,
	`mtgDamId` CHAR(15),
	`mtgEndDate` DATETIME,
	`mtgLcmCode` VARCHAR(10),
	`mtgPregnancyTested` BIT,
	`mtgPregnant` BIT,
	`mtgPregnancyTestDate` DATETIME,
	`mtgEmbryoNumber` SHORT,
	`mtgPregnancyTestingJobNumber` TEXT(10),
	`mtgBackupSireId` CHAR(15),
	`mtgBackupSireSuccessful` BIT,
	`mtgRecipientEweTagNumber` TEXT(16),
	`mtgAmlPrimaryId` CHAR(15), constraint `Mating_PK` primary key (`mtgPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`Mating`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'Mating' :
-- 	`mtgPrimaryId`
-- 	`mtgStartDate`
-- 	`mtgDamId`
-- 	`mtgEndDate`
-- 	`mtgLcmCode`


-- Create new table `Measurement`.
-- `Measurement` : Table of Measurement
-- 	Namespace: All
-- 	Stores all the measurements that are recorded for the animal or its fleece or carcase
-- 	`msrLmtCode` : The code of the measurement type lookup table (see LKMeasurementType)
-- 	`msrPrimaryId` : msrPrimaryId identifies Measurement
-- 	`msrDateTime` : The time that the measurement was taken
-- 	`msrValue` : The value of the measurement
-- 	`msrLsaCode` : The sheep age code (see LKSheepAgeCode)
-- 	`msrLgmCode` : The genetic management group code to which the trait measurement belong (see LKGeneticManagementGroup)
-- 	`msrIsCurrent` : Is true if the measurement is current
-- 	`msrMevPrimaryId` : The primary ID of the measurement event
-- 	`msrAmlPrimaryId` : The primary ID of the animal for that measurement  
create table `Measurement` ( 
	`msrLmtCode` VARCHAR(50),
	`msrPrimaryId` CHAR(15),
	`msrDateTime` DATETIME,
	`msrValue` VARCHAR(100),
	`msrLsaCode` TEXT(2),
	`msrLgmCode` TEXT(10),
	`msrIsCurrent` BIT,
	`msrMevPrimaryId` INTEGER,
	`msrAmlPrimaryId` CHAR(15), constraint `Measurement_PK` primary key (`msrPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`Measurement`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'Measurement' :
-- 	`msrLmtCode`
-- 	`msrPrimaryId`
-- 	`msrDateTime`
-- 	`msrValue`
-- 	`msrLsaCode`
-- 	`msrLgmCode`
-- 	`msrIsCurrent`


-- Create new table `LKBirthTypeCode`.
-- `LKBirthTypeCode` : Table of LKBirthTypeCode
-- 	Namespace: Animal, Genetics
-- 	START
-- 	lbtCode	lbtDescription
-- 	0	Unknown
-- 	1	Single
-- 	2	Twin
-- 	3	Triplet
-- 	4	Quad
-- 	5	Quintuplet
-- 	6	Sextuplet
-- 	7	More than 6
-- 	END
-- 	`lbtDescription` : A desription of the birth type.
-- 	`lbtCode` : Is a numeric Id of the birth type  
create table `LKBirthTypeCode` ( 
	`lbtDescription` VARCHAR(35),
	`lbtCode` CHAR(1), constraint `LKBirthTypeCode_PK` primary key (`lbtCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKBirthTypeCode`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKBirthTypeCode' :
-- 	`lbtDescription`
-- 	`lbtCode`


-- Create new table `PaddockEventHistory`.
-- `PaddockEventHistory` : Table of PaddockEventHistory
-- 	Namespace: Animal
-- 	Is a history table linking  paddock events to a paddock.
-- 	`pehLpeCode` : The paddock event that occurred (see LKPaddockEvent)
-- 	`pehPdkPrimaryId` : Identifies the paddock on which the paddock event occurred
-- 	`pehEventDate` : The date that the event occurred
-- 	`pehPrimaryID` : pehPrimaryID identifies PaddockEventHistory
-- 	`pehComment` : A general comment on the paddock event  
create table `PaddockEventHistory` ( 
	`pehLpeCode` CHAR(10),
	`pehPdkPrimaryId` CHAR(15),
	`pehEventDate` DATETIME,
	`pehPrimaryID` CHAR(15),
	`pehComment` TEXT(64), constraint `PaddockEventHistory_PK` primary key (`pehPrimaryID`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`PaddockEventHistory`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'PaddockEventHistory' :
-- 	`pehLpeCode`
-- 	`pehPdkPrimaryId`
-- 	`pehEventDate`
-- 	`pehPrimaryID`


-- Create new table `LKContactType`.
-- `LKContactType` : Lookup Table LKContactType
-- 	Namespace: All
-- 	This table is a look up table of contact types.   Contact types are:
-- 	START
-- 	lcyCode  	lcyDescription
-- 	ABBATOIR	The abbatoir (for direct sales of livestock)
-- 	ACCOUNTANT	The farm accountant
-- 	BUSMANAGER	The business manager of the farm
-- 	FARMHAND	A farm hand
-- 	MGRFARM	The Manager of the farm
-- 	OTHER	User defined
-- 	OWNER	Farm owner
-- 	STOCKAGENT	Stock Agent
-- 	TRANSCO	The transport company
-- 	WOOLCLASSER	Wool Classer (professional)
-- 	END
-- 	`lcyCode` : The type of  contact
-- 	`lcyDescription` : The full description of the contact type  
create table `LKContactType` ( 
	`lcyCode` VARCHAR(50),
	`lcyDescription` TEXT(250), constraint `LKContactType_PK` primary key (`lcyCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKContactType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKContactType' :
-- 	`lcyCode`
-- 	`lcyDescription`


-- Create new table `LKCountryCode`.
-- `LKCountryCode` : Lookup Table LKCountryCode
-- 	Namespace: All
-- 	This is a lookup table containing the 2 letter ISO country codes.
-- 	START
-- 	lccCode	lccDescription
-- 	AD	Andorra, Principality of
-- 	AE	United Arab Emirates
-- 	AF	Afghanistan, Islamic State of
-- 	AG	Antigua and Barbuda
-- 	AI	Anguilla
-- 	AL	Albania
-- 	AM	Armenia
-- 	AN	Netherlands Antilles
-- 	AO	Angola
-- 	AQ	Antarctica
-- 	AR	Argentina
-- 	AS	American Samoa
-- 	AT	Austria
-- 	AU	Australia
-- 	AW	Aruba
-- 	AZ	Azerbaidjan
-- 	BA	Bosnia-Herzegovina
-- 	BB	Barbados
-- 	BD	Bangladesh
-- 	BE	Belgium
-- 	BF	Burkina Faso
-- 	BG	Bulgaria
-- 	BH	Bahrain
-- 	BI	Burundi
-- 	BJ	Benin
-- 	BM	Bermuda
-- 	BN	Brunei Darussalam
-- 	BO	Bolivia
-- 	BR	Brazil
-- 	BS	Bahamas
-- 	BT	Bhutan
-- 	BV	Bouvet Island
-- 	BW	Botswana
-- 	BY	Belarus
-- 	BZ	Belize
-- 	CA	Canada
-- 	CC	Cocos (Keeling) Islands
-- 	CF	Central African Republic
-- 	CD	The Democratic Republic of the Congo
-- 	CG	Congo
-- 	CH	Switzerland
-- 	CI	Ivory Coast (Cote D Ivoire)
-- 	CK	Cook Islands
-- 	CL	Chile
-- 	CM	Cameroon
-- 	CN	China
-- 	CO	Colombia
-- 	COM	Commercial
-- 	CR	Costa Rica
-- 	CS	Former Czechoslovakia
-- 	CU	Cuba
-- 	CV	Cape Verde
-- 	CX	Christmas Island
-- 	CY	Cyprus
-- 	CZ	Czech Republic
-- 	DE	Germany
-- 	DJ	Djibouti
-- 	DK	Denmark
-- 	DM	Dominica
-- 	DO	Dominican Republic
-- 	DZ	Algeria
-- 	EC	Ecuador
-- 	EDU	Educational
-- 	EE	Estonia
-- 	EG	Egypt
-- 	EH	Western Sahara
-- 	ER	Eritrea
-- 	ES	Spain
-- 	ET	Ethiopia
-- 	FI	Finland
-- 	FJ	Fiji
-- 	FK	Falkland Islands
-- 	FM	Micronesia
-- 	FO	Faroe Islands
-- 	FR	France
-- 	FX	France (European Territory)
-- 	GA	Gabon
-- 	GB	Great Britain
-- 	GD	Grenada
-- 	GE	Georgia
-- 	GF	French Guyana
-- 	GH	Ghana
-- 	GI	Gibraltar
-- 	GL	Greenland
-- 	GM	Gambia
-- 	GN	Guinea
-- 	GOV	USA Government
-- 	GP	Guadeloupe (French)
-- 	GQ	Equatorial Guinea
-- 	GR	Greece
-- 	GS	S. Georgia & S. Sandwich Isls.
-- 	GT	Guatemala
-- 	GU	Guam (USA)
-- 	GW	Guinea Bissau
-- 	GY	Guyana
-- 	HK	Hong Kong
-- 	HM	Heard and McDonald Islands
-- 	HN	Honduras
-- 	HR	Croatia
-- 	HT	Haiti
-- 	HU	Hungary
-- 	ID	Indonesia
-- 	IE	Ireland
-- 	IL	Israel
-- 	IN	India
-- 	INT	International
-- 	IO	British Indian Ocean Territory
-- 	IQ	Iraq
-- 	IR	Iran
-- 	IS	Iceland
-- 	IT	Italy
-- 	JM	Jamaica
-- 	JO	Jordan
-- 	JP	Japan
-- 	KE	Kenya
-- 	KG	Kyrgyz Republic (Kyrgyzstan)
-- 	KH	Cambodia, Kingdom of
-- 	KJ	Kiribati
-- 	KM	Comoros
-- 	KN	Saint Kitts & Nevis Anguilla
-- 	KP	North Korea
-- 	KR	South Korea
-- 	KW	Kuwait
-- 	KY	Cayman Islands
-- 	KZ	Kazakhstan
-- 	LA	Laos
-- 	LB	Lebanon
-- 	LC	Saint Lucia
-- 	LI	Liechtenstein
-- 	LK	Sri Lanka
-- 	LR	Liberia
-- 	LS	Lesotho
-- 	LT	Lithuania
-- 	LU	Luxembourg
-- 	LV	Latvia
-- 	LY	Libya
-- 	MA	Morocco
-- 	MC	Monaco
-- 	MD	Moldavia
-- 	MG	Madagascar
-- 	MH	Marshall Islands
-- 	MIL	USA Military
-- 	MK	Macedonia
-- 	ML	Mali
-- 	MM	Myanmar
-- 	MN	Mongolia
-- 	MO	Macau
-- 	MP	Northern Mariana Islands
-- 	MQ	Martinique (French)
-- 	MR	Mauritania
-- 	MS	Montserrat
-- 	MT	Malta
-- 	MU	Mauritius
-- 	MV	Maldives
-- 	MW	Malawi
-- 	MX	Mexico
-- 	MY	Malaysia
-- 	MZ	Mozambique
-- 	NA	Namibia
-- 	NC	New Caledonia (French)
-- 	NE	Niger
-- 	NET	Network
-- 	NF	Norfolk Island
-- 	NG	Nigeria
-- 	NI	Nicaragua
-- 	NL	Netherlands
-- 	NO	Norway
-- 	NP	Nepal
-- 	NR	Nauru
-- 	NT	Neutral Zone
-- 	NU	Niue
-- 	NZ	New Zealand
-- 	OM	Oman
-- 	ORG	Non-Profit Making Organisations (sic)
-- 	PA	Panama
-- 	PE	Peru
-- 	PF	Polynesia (French)
-- 	PG	Papua New Guinea
-- 	PH	Philippines
-- 	PK	Pakistan
-- 	PL	Poland
-- 	PM	Saint Pierre and Miquelon
-- 	PN	Pitcairn Island
-- 	PR	Puerto Rico
-- 	PT	Portugal
-- 	PW	Palau
-- 	PY	Paraguay
-- 	QA	Qatar
-- 	RE	Reunion (French)
-- 	RO	Romania
-- 	RU	Russian Federation
-- 	RW	Rwanda
-- 	SA	Saudi Arabia
-- 	SB	Solomon Islands
-- 	SC	Seychelles
-- 	SD	Sudan
-- 	SE	Sweden
-- 	SG	Singapore
-- 	SH	Saint Helena
-- 	SI	Slovenia
-- 	SJ	Svalbard and Jan Mayen Islands
-- 	SK	Slovak Republic
-- 	SL	Sierra Leone
-- 	SM	San Marino
-- 	SN	Senegal
-- 	SO	Somalia
-- 	SR	Suriname
-- 	ST	Saint Tome (Sao Tome) and Principe
-- 	SU	Former USSR
-- 	SV	El Salvador
-- 	SY	Syria
-- 	SZ	Swaziland
-- 	TC	Turks and Caicos Islands
-- 	TD	Chad
-- 	TF	French Southern Territories
-- 	TG	Togo
-- 	TH	Thailand
-- 	TJ	Tadjikistan
-- 	TK	Tokelau
-- 	TM	Turkmenistan
-- 	TN	Tunisia
-- 	TO	Tonga
-- 	TP	East Timor
-- 	TR	Turkey
-- 	TT	Trinidad and Tobago
-- 	TV	Tuvalu
-- 	TW	Taiwan
-- 	TZ	Tanzania
-- 	UA	Ukraine
-- 	UG	Uganda
-- 	UK	United Kingdom
-- 	UM	USA Minor Outlying Islands
-- 	US	United States
-- 	UY	Uruguay
-- 	UZ	Uzbekistan
-- 	VA	Holy See (Vatican City State)
-- 	VC	Saint Vincent & Grenadines
-- 	VE	Venezuela
-- 	VG	Virgin Islands (British)
-- 	VI	Virgin Islands (USA)
-- 	VN	Vietnam
-- 	VU	Vanuatu
-- 	WF	Wallis and Futuna Islands
-- 	WS	Samoa
-- 	YE	Yemen
-- 	YT	Mayotte
-- 	YU	Yugoslavia
-- 	ZA	South Africa
-- 	ZM	Zambia
-- 	ZR	Zaire
-- 	ZW	Zimbabwe
-- 	END
-- 	`lccCode` : The 2 letter ISO country code 
-- 	`lccDescription` : The name of the country  
create table `LKCountryCode` ( 
	`lccCode` TEXT(4),
	`lccDescription` VARCHAR(250), constraint `LKCountryCode_PK` primary key (`lccCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKCountryCode`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKCountryCode' :
-- 	`lccCode`
-- 	`lccDescription`


-- Create new table `LKMeasurementType`.
-- `LKMeasurementType` : Table of MeasurementType
-- 	Namespace: Animal, Genetics
-- 	Look up table for the codes and descriptions for the types of measurements done on an animal
-- 	START
-- 	lmtCode	lmtGrouping	lmtLowerbound	lmtUpperbound	lmtMeasurementUnit	lmtMeasurementDataType	lmtIsDerived	lmtDescription	lmtDefinition	lmtReference
-- 	CEMD	Meat	11	59	mm	Integer	0	Carcass Eye Muscle Depth		
-- 	CFAT	Meat	5	30	mm	Integer	0	Carcass Fat 	Fat depth measured in mm at the GR site of the carcass	
-- 	CWT	Meat	5	75	kg	Integer	0	Carcase weight. Hot Standard Carcase Weight (HSCW). 	"Defined as the weight of the carcase within 2 hours of slaughter, with no deduction for shrinkage and once the head, thick skirt, kidney fat and channel fat have been removed."	
-- 	EMD	Meat	11	59	mm	Integer	0	Eye Muscle Depth 		
-- 	FAT	Meat	5	30	mm	Integer	0	Fat depth  	Fat depth measured in mm at the GR site of the live animal	
-- 	SCANFAT	Meat	0.1	30	mm	Integer	0	Scanned Fat 	Scanned measurement of fat at the C site of the live animal	
-- 	BDW	Animal	1	130	kg	Integer	0	Body weight 	The liveweight of the animal	
-- 	FEC	Animal	0	35	"1,000 eggs/gm"	Integer	0	Fecal Egg Count 		
-- 	NLB	Fertility	0	5	ratio	Integer	0	Number of lambs born	Ratio of number of lambs born to number of ewes joined	
-- 	NLW	Fertility	0	5	ratio	Integer	0	Number of lambs weaned	Ratio of number of lambs weaned to number of ewes joined	
-- 	SCIRC	Fertility	15	59	mm	Integer	0	Scrotal Circumference 	The circumference of the scrotum measured in mm	
-- 	TESTES	Fertility	0	2	count	Integer	0	Count of testicles 	Number of testicles in the male 	
-- 	CFW	Fleece	0.5	9	kg	Integer	-1	Clean fleece weight 	The clean fleece weight of the fleece measured in kg and calculated by multiplying the greasy fleece weight by the yield % divided by 100	AMSEA 
-- 	COL	Fleece	7	18	Y-Z units	Integer	0	Wool fleece color 	Wool fleece color is the unscourable color portion of the fleece as measured by AWTA standard for yellowness	AWTA Glossary of Terms
-- 	CURV	Fleece	0	200	Degrees per mm	Integer	0	Curvature measurement of the fleece 	A measure of crimp reported as degrees/mm	AWTA Glossary of Terms
-- 	CURVSD	Fleece	0	200	Degrees per mm	Integer	-1	Standard deviation of curvature	A measure of the dispersion of individual results for curvature measured in degrees/mm	AWTA Glossary of Terms
-- 	FD	Fleece	1	40	microns	Integer	0	Fibre Diameter of the fleece	The thickness of individual fibres; it is customary to quote an average value (mean fibre diameter) in micrometres.	AWTA Glossary of Terms
-- 	CEDGE	Fleece	1	40	microns	Integer	0	Fibre Diameter Coarse Edge 	Fibre Diameter Coarse Edge is the difference in microns between the mean diameter and the broadest 5 percent of fibres	AWTA Glossary of Terms
-- 	CEDGE%	Fleece	0	100	%	Integer	-1	Fibre Diameter Coarse Edge Percentage	It is the percentage of microns 10 microns above the mean	AWTA Glossary of Terms
-- 	FDCV	Fleece	10	35	%	Integer	-1	Coefficient of Variation of Fibre Diameter	"A statistical measure for the variation of fibre diameter within a range of values. It expresses the standard deviation as a percentage of the mean. The higher the CV , the greater the variability."	AWTA Glossary of Terms
-- 	FDMIN	Fleece	1	40	microns	Integer	0	Minimum Fibre Diameter 	It is the minimum fibre diameter measured in microns of fibres tested	AWTA Glossary of Terms
-- 	FDMAX	Fleece	10	40	microns	Integer	0	Maximum Fibre Diameter	It is the maximum fibre diameter measured in microns of fibres tested	AWTA Glossary of Terms
-- 	FDSD	Fleece	0	50	microns	Integer	-1	Standard deviation of fibre diameter	A measure of the dispersion of individual results for fibre diameter measured in microns	AWTA Glossary of Terms
-- 	COMFORT	Fleece	80	100	%	Integer	-1	Comfort Factor of the fleece	The percentage of fibres finer than 30 microns in a sample	AWTA Glossary of Terms
-- 	GFW	Fleece	1	11	kg	Integer	0	Greasy fleece weight	The weight in kg of the whole fleece (including belly) prior to skirting	AMSEA 
-- 	HAUTEUR	Fleece	40	100	mm	Integer	-1	Hauteur length	Hauteur is the average of the length-biased distribution of fibre length in a top.	AWTA Glossary of Terms
-- 	FIBRLESS15	Fleece	0	100	%	Integer	-1	Percentage of fibres less than 15 microns	It is the percentage of fibres less than 15 microns in the sample tested	AWTA Glossary of Terms
-- 	POBTIP	Fleece	0	100	%	Integer	0	Position of break tip	It is the point of break at the tip of the staple as a percentage of fibres breaking	AWTA Glossary of Terms
-- 	POBMID	Fleece	0	100	%	Integer	0	Position of break middle	It is the point of break of the midpoint of the staple as a percentage of fibres breaking	AWTA Glossary of Terms
-- 	POBBASE	Fleece	0	100	%	Integer	0	Position of break base	It is the point of break of the base of the staple as a percentage of fibres breaking	AWTA Glossary of Terms
-- 	SPINFINE	Fleece	1	40	microns	Integer	-1	Spinning Fineness	Spinning Fineness is a number which attempts to combine the Mean Fibre Diameter (MFD) and the Coefficient of Variation of Diameter (CVD) into a single measure of fineness to estimate the performance of wool when it is spun into yarn	AWTA Glossary of Terms
-- 	SSTR	Fleece	1	95	N/ktex	Integer	0	Staple Strength of the fleece	The maximum force required to rupture a single staple per unit of average linear density	AWTA Glossary of Terms
-- 	SLEN	Fleece	1	200	mm	Integer	0	Staple Length of the fleece	The length of a staple projected along its axis obtained by measuring the staple without stretching or disturbing the crimp of the fibres	AWTA Glossary of Terms
-- 	SLENCV	Fleece	5	40	%	Integer	-1	Coefficient of Variation of Staple Length	"A statistical measure for the variation of staple length within a range of values. It expresses the standard deviation as a percentage of the mean. The higher the CV , the greater the variability."	AWTA Glossary of Terms
-- 	YLDSCH	Fleece	40	95	%	Integer	0	Schlumberger dry combing fleece yield	Fleece Yield as calculated by Schlumberger Combed Dry (1 percent TFM)	AWTA Glossary of Terms
-- 	YLDSCD	Fleece	40	95	%	Integer	0	Scoured fleece yield	Fleece Yield as calculated by IWTO Scoured Yield at 16 percent Regain	AWTA Glossary of Terms
-- 	FINETIP	Fleece	0	300	microns	Integer	0	Finest point from the tip 		OFDA
-- 	FIBREEND	Fleece	4	80	microns	Integer	-1	Mean fibre ends 		OFDA
-- 	SKINDIAM	Fleece	4	80	microns	Integer	0	Skin diameter 		OFDA
-- 	SKINDIAMSD	Fleece	1	50	microns	Integer	-1	Skin diameter standard deviation 		OFDA
-- 	SKINDIAMCV	Fleece	1	50	%	Integer	-1	Skin diameter coefficient of variation 		OFDA
-- 	FDHIST	Fleece	1	300	microns	Histogram	-1	Fibre diameter histogram	Histogram derived from measurements of Fibre Diameter	OFDA
-- 	HISTCURV	Fleece	0	500	microns	Histogram	-1	Fibre curvature histogram	Histogram derived from measurements of Curvature	OFDA
-- 	NOMEASCURV	Fleece	1	"100,000"	count	Integer	0	Number of fibres measured to calculate curvature 	A count of the number of fibres measured to calculate the curvature	OFDA
-- 	FDSEG	Fleece	4	300	microns	Integer	0	Mean Fibre Diameter Along Segment		OFDA
-- 	FDSEGSD	Fleece	1	50	microns	Integer	-1	Standard deviation of mean fibre diameter along segment 		OFDA
-- 	SMBLOBSEG	Fleece	0	10	%	Integer	-1	Small Blob percentage Along Segment 		OFDA
-- 	SMBLOBSEGSD	Fleece	0	10	%	Integer	-1	Standard deviation Small Blob percentage Along Segment 		OFDA
-- 	LBLOBSEG	Fleece	0	10	%	Integer	-1	Large Blob percentage Along Segment 		OFDA
-- 	LBLOBSEGSD	Fleece	0	10	%	Integer	-1	Standard deviation of Large Blob percentage Along Segment 		OFDA
-- 	MINDIAMSEG	Fleece	4	80	microns	Integer	0	Minimum Diameter Along Segment 		OFDA
-- 	MINDIAMSEGSD	Fleece	1	50	microns	Integer	-1	Standard deviation of minimum Diameter Along Segment 		OFDA
-- 	BLOBAREA	Fleece	0	10	%	Integer	-1	Blob Area percentage 		OFDA
-- 	BLOBAREASD	Fleece	0	10	%	Integer	-1	Standard deviation of Blob Area percentage 		OFDA
-- 	NOMEASSEG	Fleece	1	"100,000"	count	Integer	0	Number Of Fibres Measured Along Segment		OFDA
-- 	FDACSTHIST	Fleece	4	80	microns	Integer	0	Mean Fibre Diameter Across Staple Histogram 		OFDA
-- 	FDACSTHISTSD	Fleece	1	50	microns	Integer	-1	Standard deviation of Mean Fibre Diameter Across Staple Histogram 		OFDA
-- 	FDACST	Fleece	4	80	microns	Integer	0	Fibre Diameter across staple 		OFDA
-- 	FDACSTSD	Fleece	1	50	microns	Integer	-1	Standard Deviation Fibre Diameter Across Staple 		OFDA
-- 	FDACSTFDCV	Fleece	1	50	%	Integer	-1	Coefficient of Variation of Fibre Diameter Across Staple 		OFDA
-- 	FDALST	Fleece	4	80	microns	Integer	0	Fibre Diameter Along Staple 		OFDA
-- 	FDALSTSD	Fleece	1	50	microns	Integer	-1	Standard Deviation of Fibre Diameter Along Staple 		OFDA
-- 	FDALSTCV	Fleece	1	50	%	Integer	-1	Coefficient of Variation of Fibre Diameter Along Staple 		OFDA
-- 	MINFDALST	Fleece	4	80	microns	Integer	0	Minimum fibre diameter along the staple	Minimum fibre diameter in microns measured along the fibre staple 	OFDA
-- 	MAXFDALST	Fleece	4	80	microns	Integer	0	Maximum fibre diameter along the staple	Maximum fibre diameter in microns measured along the fibre staple 	OFDA
-- 	THINPALST	Fleece	0	300	mm	Integer	0	Thinnest point along the staple	Distance measured in mm from tip of staple to thinnest point	OFDA
-- 	LAMBEASE	Fertility	0	5	score	Integer	0	Lambing Ease score		MLA
-- 	FATSCORE	Animal	1	5	score	Integer	0	Fat score		MLA
-- 	BODYWRINKLE	Animal	1	5	score	Integer	0	Body wrinkle score	The quantity and quality of wrinkle on the body with 1 being very little wrinkle and 5 a lot.	AMSEA 
-- 	FACECOVER	Animal	1	5	score	Integer	0	Face cover score	The amount of wool cover on the face with 1 being very open and 5 being very muffled	AMSEA 
-- 	FLEECEROT	Fleece	0	5	score	Integer	0	Fleece rot score	The degree of fleece rot present on the fleece with 0 being no fleece rot and 5 being a high level of fleece rot	AMSEA 
-- 	NECKWRINKLE	Animal	1	5	score	Integer	0	Neck Wrinkle score	The quantity and quality of wrinkle on the apron and back of the neck with 1 being very little wrinkle and 5 a lot.	AMSEA 
-- 	DOHNEPRODGR	Animal	AA	C	score	Text	-1	Dohne Product Grade	Index values are summarised into 4 measured performance grades	Australian Dohne Breeders Association
-- 	DOHNEEFFGR	Animal	AA	C	score	Text	0	Dohne Efficience Grade	Dohne inspectors grade each registered animal visual performance	Australian Dohne Breeders Association
-- 	DOHNEFINALGR	Animal	AA	C	score	Text	-1	Dohne Final Grade	Combined Product and Efficience Grade	Australian Dohne Breeders Association
-- 	FRAMESIZE	Animal	A	E	score	Text	0	Frame size score	The frame size of the sheep is scored according to the height in cm at the withers	Ausmeat National Livestock Language Sheep Ovine
-- 	EARSTATUS	Animal	INTACT	OWISE	score	Text	0	Ear status score	Ears are recorded as to whether any blemishes are present	Ausmeat National Livestock Language Sheep Ovine
-- 	WEIGHTSCORE	Meat	1	40	kg	Integer	0	Carcase weight score	The weight of the carcase in kg based on HSCW	Ausmeat National Livestock Language Sheep Ovine
-- 	PREGSTATUS	Animal	UNKNOWN	PREGTESLM	score	Text	0	Pregnancy status score	The pregnancy status of the sheep as determined by a vet 	Ausmeat National Livestock Language Sheep Ovine
-- 	HORNSTATUS	Animal	HORNED	POLL	score	Text	0	Horn status score	"The status of the sheep with respect to horns i.e. horned, dehorned or poll"	Ausmeat National Livestock Language Sheep Ovine
-- 	TAILSTATUS	Animal	INTACT	DOCKED	score	Text	0	Tail status score	Whether the tail is docked or intact	Ausmeat National Livestock Language Sheep Ovine
-- 	SKINWRINKLE	Animal	PLAIN	HEAVY	score	Text	0	Sheep skin wrinkle score	The degree of wrinkle on the skin	Ausmeat National Livestock Language Sheep Ovine
-- 	CRUTCHSTATUS	Animal	FULL	MARKET	score	Text	0	The crutching status score	Whether a sheep has been crutched or not and the type of crutch performed	Ausmeat National Livestock Language Sheep Ovine
-- 	DENTITION	Animal	NIL	IN8	score	Text	0	The stage of dentition score	Score based on the number of incisor teeth present in the sheep	Ausmeat National Livestock Language Sheep Ovine
-- 	CONDITION	Animal	1	5	score	Integer	0	The condition of the animal score	The condition of the sheep based on the estimated tissue depth in mm at the GR site	Ausmeat National Livestock Language Sheep Ovine
-- 	MULESSTATUS	Animal	NOMULES	MULES	score	Text	0	The mulesing status score	Whether the sheep has been mulesed or not	Ausmeat National Livestock Language Sheep Ovine
-- 	END
-- 	`lmtCode` : The code for the measurement
-- 	`lmtGrouping` : The grouping classification for the trait
-- 	`lmtDescription` : Description of the animal trait type
-- 	`lmtDefinition` : Definition of the animal trait type
-- 	`lmtReference` : Reference for the definition and/or description of the animal trait type
-- 	`lmtLowerBound` : The lower bound value of the trait
-- 	`lmtUpperBound` : The upper bound value of the trait
-- 	`lmtMeasurementUnit` : The measurement unit used for the trait value
-- 	`lmtMeasurementDataType` : The data type for the type of measurement. For example, numeric or character (text) or histogram
-- 	`lmtIsDerived` : Is true if the measurement type is derived from another measurement. For example, standard deviation of fibre diameter is derived from the fibre diameter measurement.  
create table `LKMeasurementType` ( 
	`lmtCode` VARCHAR(50),
	`lmtGrouping` VARCHAR(50),
	`lmtDescription` VARCHAR(250),
	`lmtDefinition` VARCHAR(250),
	`lmtReference` VARCHAR(250),
	`lmtLowerBound` VARCHAR(10),
	`lmtUpperBound` VARCHAR(10),
	`lmtMeasurementUnit` VARCHAR(15),
	`lmtMeasurementDataType` VARCHAR(10),
	`lmtIsDerived` BIT, constraint `LKMeasurementType_PK` primary key (`lmtCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKMeasurementType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKMeasurementType' :
-- 	`lmtCode`
-- 	`lmtDescription`
-- 	`lmtLowerBound`
-- 	`lmtUpperBound`
-- 	`lmtIsDerived`


-- Create new table `AnimalTreatmentHistory`.
-- `AnimalTreatmentHistory` : Table AnimalTreatmentHistory
-- 	Namespace: Animal
-- 	This table is a history of treatment of diseases with sheep and any associated chemical treatment.
-- 	It forms part of MLA's Livestock Production Assurance (LPA) onfarm food safety certification program.
-- 	`athtPrimaryID` : athtPrimaryID identifies AnimalTreatmentHistory
-- 	`athDate` : The date of the application of the chemical to the animal
-- 	`athWeight` : The weight of the animal at treatment
-- 	`athAmlPrimaryId` : The id of the treated animal
-- 	`athLmuCode` : The unit of measurement for the treatment
-- 	`athLsdCode` : The sheep disease code (see LKSheepDiseaseCode)
-- 	`athLatCode` : The chemical type for the treatment (see LKAnimalChemicalType)
-- 	`athBatchNo` : The batch number of the chemical used to treat the animal
-- 	`athExpiryDate` : The expiry date of the chemical used to treat the animal
-- 	`athSafeSlaughterDate` : The date the animal is safe for slaughter after chemical treatment
-- 	`athRate` : Th rate at which the treatment is applied  
create table `AnimalTreatmentHistory` ( 
	`athtPrimaryID` CHAR(15),
	`athDate` DATETIME,
	`athWeight` LONG,
	`athAmlPrimaryId` CHAR(15),
	`athLmuCode` TEXT(10),
	`athLsdCode` TEXT(10),
	`athLatCode` INTEGER,
	`athBatchNo` CHAR(10),
	`athExpiryDate` DATETIME,
	`athSafeSlaughterDate` DATETIME,
	`athRate` TEXT(5), constraint `AnimalTreatmentHistory_PK` primary key (`athtPrimaryID`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`AnimalTreatmentHistory`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'AnimalTreatmentHistory' :
-- 	`athtPrimaryID`
-- 	`athDate`
-- 	`athWeight`
-- 	`athAmlPrimaryId`
-- 	`athLmuCode`
-- 	`athLsdCode`
-- 	`athLatCode`
-- 	`athBatchNo`
-- 	`athExpiryDate`
-- 	`athSafeSlaughterDate`
-- 	`athRate`


-- Create new table `LKPaddockEvent`.
-- `LKPaddockEvent` : Table of PaddockEvent
-- 	Namespace: Animal
-- 	A Paddock Event is an event that occurs on or to a paddock.   It however excludes such events as chemical and fertiliser application.  Events are:
-- 	START
-- 	lpeCode	lpeDescription
-- 	CROPLANT	Planting of a crop
-- 	FALLOW	Paddock left fallow
-- 	HARVEST	Harvesting a crop
-- 	INSTOCK	A movement of stock into the paddock
-- 	OUTSTOCK	A movement of stock out of the paddock
-- 	PASTSOW	Sowing pasture
-- 	PLOUGH	Ploughing of the paddock
-- 	END
-- 	`lpeCode` : Event that occurs in a paddock.
-- 	`lpeDescription` : The decription of the event  
create table `LKPaddockEvent` ( 
	`lpeCode` CHAR(10),
	`lpeDescription` VARCHAR(250), constraint `LKPaddockEvent_PK` primary key (`lpeCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKPaddockEvent`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKPaddockEvent' :
-- 	`lpeCode`
-- 	`lpeDescription`


-- Create new table `Line`.
-- `Line` : Table of Line
-- 	Namespace: Wool
-- 	Stores the attributes of a line of wool
-- 	`inePrimaryId` : inePrimaryId identifies Line
-- 	`ineClpPrimaryId` : Identifies the clip to which the line belongs
-- 	`ineClasserComments` : Classer comments on the line
-- 	`ineDMFR` : Is true if there is DMFR contamination - need to put  this in a glossary
-- 	`ineLS` : Is true if length and strength (L/S) testing is required for that line of wool
-- 	`ineLltCode` : The line type (see LKLineType)
-- 	`ineNumberOfBales` : The number of bales in the line
-- 	`ineBinCode` : The id of the bin that made up the line
-- 	`ineBrokerReferenceNumber` : The reference number of the line as supplied by the broker  
create table `Line` ( 
	`inePrimaryId` CHAR(15),
	`ineClpPrimaryId` CHAR(15),
	`ineClasserComments` CHAR(10),
	`ineDMFR` BIT,
	`ineLS` BIT,
	`ineLltCode` TEXT(20),
	`ineNumberOfBales` LONG,
	`ineBinCode` CHAR(10),
	`ineBrokerReferenceNumber` CHAR(10), constraint `Line_PK` primary key (`inePrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`Line`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'Line' :
-- 	`inePrimaryId`
-- 	`ineClpPrimaryId`
-- 	`ineClasserComments`
-- 	`ineDMFR`
-- 	`ineLS`
-- 	`ineLltCode`
-- 	`ineNumberOfBales`


-- Create new table `Clip`.
-- `Clip` : Table of Clip
-- 	Namespace: Wool
-- 	Stores the information regarding a clip.  See the AWEX Classer Specification.
-- 	`clpCntPrimaryId` : clpCntPrimaryId partly identifies Clip
-- 	`clpPrimaryId` : clpPrimaryId identifies Clip
-- 	`clpLcpCode` : The clip type (see LKClipType)
-- 	`clpLoiCode` : The clip offering instructions (see LKOfferingInstruction).   If this field has value 4 then clpSaleNumber should be non null containing a valid sale number.
-- 	`clpIsShearingComplete` : Is true if the shearing is finished for this clip
-- 	`clpTotalBalesThisClip` : The number of bales in the clip
-- 	`clpCompletedDate` : The date the shearing of the clip was completed
-- 	`clpSpecialInstructions` : Any special instructions associated with the clip
-- 	`clpEstimatedBaleslThisShearing` : The estimated bales for the shearing
-- 	`clpLdcCode` : The delivery centre for the clip (see LKDeliveryCentre)
-- 	`clpSaleNumber` : The sale number for which the clip is to be offered (Should be non null only if clpLoiCode = 4)
-- 	`clpLwsCode` : The sale centre for the clip (see LKSaleCentre)
-- 	`clpStudramsUsed` : A space separated list of stud rams used in the clip  
create table `Clip` ( 
	`clpCntPrimaryId` CHAR(15),
	`clpPrimaryId` CHAR(15),
	`clpLcpCode` TEXT(10),
	`clpLoiCode` TEXT(10),
	`clpIsShearingComplete` BIT,
	`clpTotalBalesThisClip` LONG,
	`clpCompletedDate` DATETIME,
	`clpSpecialInstructions` TEXT(50),
	`clpEstimatedBaleslThisShearing` LONG,
	`clpLdcCode` TEXT(10),
	`clpSaleNumber` TEXT(10),
	`clpLwsCode` TEXT(10),
	`clpStudramsUsed` TEXT(128), constraint `Clip_PK` primary key (`clpPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`Clip`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'Clip' :
-- 	`clpCntPrimaryId`
-- 	`clpPrimaryId`
-- 	`clpLcpCode`
-- 	`clpLoiCode`
-- 	`clpIsShearingComplete`
-- 	`clpTotalBalesThisClip`
-- 	`clpCompletedDate`
-- 	`clpSpecialInstructions`
-- 	`clpEstimatedBaleslThisShearing`
-- 	`clpLdcCode`


-- Create new table `Bale`.
-- `Bale` : Table of Bale
-- 	Namespace: Wool
-- 	Defines the attributes of a bale
-- 	`bleMcpPrimaryId` : The id of  mob clip that the bale belongs 
-- 	`blePrimaryId` : blePrimaryId identifies Bale
-- 	`bleDate` : The date the bale was created
-- 	`bleDescription` : The description that is stencilled onto the bale. For example AAAM
-- 	`bleBaleNo` : The bale number
-- 	`blePtyPrimaryId` : The id of the property at which the bale was created
-- 	`bleLwpCode` : The wool pack type of the bale (see LKWoolPackType)
-- 	`bleElectronicBaleId` : Unique Electronic ID assigned to a bale (is there an AWEX definition ???)
-- 	`bleGrossKg` : Gross weight of Bale measured in kilograms after pressing in the shearing shed 
-- 	`bleLptCode` : The weight of the bale packing (see LKPackTare)
-- 	`bleInePrimaryId` : The id of the line of wool to which the bale belongs  
create table `Bale` ( 
	`bleMcpPrimaryId` CHAR(15),
	`blePrimaryId` CHAR(15),
	`bleDate` DATETIME,
	`bleDescription` VARCHAR(30),
	`bleBaleNo` CHAR(15),
	`blePtyPrimaryId` CHAR(15),
	`bleLwpCode` TEXT(10),
	`bleElectronicBaleId` CHAR(16),
	`bleGrossKg` SHORT,
	`bleLptCode` TEXT(2),
	`bleInePrimaryId` CHAR(15), constraint `Bale_PK` primary key (`blePrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`Bale`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'Bale' :
-- 	`bleMcpPrimaryId`
-- 	`blePrimaryId`
-- 	`bleDate`
-- 	`bleDescription`
-- 	`bleBaleNo`
-- 	`blePtyPrimaryId`
-- 	`bleLwpCode`
-- 	`bleGrossKg`
-- 	`bleLptCode`
-- 	`bleInePrimaryId`


-- Create new table `FleeceClassing`.
-- `FleeceClassing` : Table of Fleece
-- 	Namespace: Wool
-- 	This table stores the attributes of a fleece collected at shearing time.
-- 	`flcPrimaryId` : flcPrimaryId identifies FleeceClassing
-- 	`flcVisualQuality` : The estimated wool count for example 70's or 64's
-- 	`flcSubStapleStrength` : The subjective measurement of the fleece staple strength
-- 	`flcSubStapleLength` : The subjective measurement of the fleece staple length in millimetres
-- 	`flcShearDate` : The date the fleece was shorn
-- 	`flcComment` : Classer comments on the fleece
-- 	`flcBlackWool` : Is true if any portion of the fleece is deemed black and false otherwise
-- 	`flcManagementYear` : ManagementYear is the nominated annual period of a grown fleece.  For example, a fleece sample may be taken in November and the animal may be shorn in January the following year.
-- 	`flcAmlPrimaryId` : The id of the animal to which the fleece belongs
-- 	`flcWoolGrowth` : The wool growth of the fleece since last shearing
-- 	`flcInePrimaryId` : The line to which the fleece belongs
-- 	`flcFleeceRot` : Fleece Rot is true if the wool classer determines that fleece rot is present in the fleece.
-- 	`flcDermatitus` : Dermatitus is true if the wool classer dertermines that dermatitus is present in the fleece.
-- 	`flcSoftRollingSkinType` : Soft Rolling Skin Type is true if the wool classe determines that the fleece represents a soft rolling skin type.  
create table `FleeceClassing` ( 
	`flcPrimaryId` CHAR(15),
	`flcVisualQuality` CHAR(4),
	`flcSubStapleStrength` CHAR(15),
	`flcSubStapleLength` CHAR(15),
	`flcShearDate` DATETIME,
	`flcComment` CHAR(10),
	`flcBlackWool` BIT,
	`flcManagementYear` CHAR(4),
	`flcAmlPrimaryId` CHAR(15),
	`flcWoolGrowth` CHAR(10),
	`flcInePrimaryId` CHAR(15),
	`flcFleeceRot` BIT,
	`flcDermatitus` BIT,
	`flcSoftRollingSkinType` BIT, constraint `FleeceClassing_PK` primary key (`flcPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`FleeceClassing`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'FleeceClassing' :
-- 	`flcPrimaryId`
-- 	`flcAmlPrimaryId`


-- Create new table `Animal`.
-- `Animal` : Table of Animal
-- 	Namespace: All
-- 	`amlLayTypeCode` : The animal type code (see LKAnimalType)
-- 	`amlLrtTypeCode` : The animal rearing type code (see LKRearType)
-- 	`amlPdkPrimaryId` : The id of the current paddock that the animal is in
-- 	`amlPrimaryId` : amlPrimaryId identifies Animal
-- 	`amlMobPrimaryId` : The id of the mob that the animal is currently in
-- 	`amlBirthDate` : The date the animal is born
-- 	`amlName` : The common name of the animal
-- 	`amlElectronicId` : The electronic id of an animal
-- 	`amlVisualId` : The visual id of the animal
-- 	`amlSireId` : The id of the father of an animal and would be either the vid or the eid
-- 	`amlDamId` : The id of the mother of an animal and would be either the vid or the eid
-- 	`amlSheepPropertyTag` : The current property of Animal if animal is not on property of birth - size unknown!!! NFIS
-- 	`amlSheepBreederTag` : The property of birth of Animal - NFIS
-- 	`amlLscCode` : The animal sex code (see LKSexCode)
-- 	`amlLbtCode` : The animal birth type code (see LKBirthTypeCode)
-- 	`amlPurchaseDate` : The date the animal was purchased
-- 	`amlPurchaseCostA` : The amount paid for the animal in dollars per head
-- 	`amlPurchaseCostB` : The amount paid for the animal in cents/kilogram
-- 	`amlBreedCode` : The breed code of the animal (e.g. 50 for Merino and 60 for Poll Merino)
-- 	`amlLasCode` : The status of the animal (see LK AnimalStatus)
-- 	`amlLtgCode` : The color code of the ear tag (see LKTagColor)
-- 	`amlHasBrokenMouth` : The sheep has evidence of incomplete dentition due to the loss of one or more permanent incisor teeth from a full set of 8 permanent incisor teeth  - as defined by Ausmeat National Livestock Language Sheep:Ovine
-- 	`amlPurchaseNVDNumber` : The National Vendor Declaration (NVD) number for the animal purchased and brought onto the farm (refer to MLA LPA scheme)
-- 	`amlCntVendorAgent` : The agent of the vendor that the animal was purchased from (refer to MLA LPA scheme)
-- 	`amlHornTag` : The horn tag number/identification (often used by studs on their stud rams)
-- 	`amlActPrimaryId` : The primary id of comments recorded about the animal
-- 	`amlCntVendor` : The vendor of the sale consignment
-- 	`amlFlockPrimaryId` : The id of the flock the animal is currently in.  
create table `Animal` ( 
	`amlLayTypeCode` TEXT(10),
	`amlLrtTypeCode` CHAR(10),
	`amlPdkPrimaryId` CHAR(15),
	`amlPrimaryId` CHAR(15),
	`amlMobPrimaryId` CHAR(15),
	`amlBirthDate` DATETIME,
	`amlName` BIT,
	`amlElectronicId` CHAR(16),
	`amlVisualId` CHAR(16),
	`amlSireId` CHAR(16),
	`amlDamId` CHAR(16),
	`amlSheepPropertyTag` CHAR(10),
	`amlSheepBreederTag` CHAR(10),
	`amlLscCode` CHAR(1),
	`amlLbtCode` CHAR(1),
	`amlPurchaseDate` DATETIME,
	`amlPurchaseCostA` LONG,
	`amlPurchaseCostB` LONG,
	`amlBreedCode` LONG,
	`amlLasCode` VARCHAR(50),
	`amlLtgCode` VARCHAR(50),
	`amlHasBrokenMouth` BIT,
	`amlPurchaseNVDNumber` CHAR(10),
	`amlCntVendorAgent` CHAR(15),
	`amlHornTag` CHAR(10),
	`amlActPrimaryId` CHAR(10),
	`amlCntVendor` CHAR(15),
	`amlFlockPrimaryId` CHAR(15), constraint `Animal_PK` primary key (`amlPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`Animal`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'Animal' :
-- 	`amlLayTypeCode`
-- 	`amlLrtTypeCode`
-- 	`amlPdkPrimaryId`
-- 	`amlPrimaryId`
-- 	`amlMobPrimaryId`
-- 	`amlLscCode`


-- Create new table `Mob`.
-- `Mob` : Table of Mob
-- 	Namespace: All
-- 	This table stores details of a mob.  It also allows for sub-mobs within mobs.
-- 	`mobPrimaryId` : mobPrimaryId identifies Mob
-- 	`mobPtyPrimaryId` : Identifies the property on which the mob belongs.
-- 	`mobName` : The common name of the mob
-- 	`mobDescription` : Description of the  mob
-- 	`mobParentMobPrimaryId` : Self referencing to cater for mobs within mobs.  The root mob is identifed with mobPrimaryId of 0.
-- 	`mobCount` : The number of animals in the mob
-- 	`mobId` : Producers Id for the mob  
create table `Mob` ( 
	`mobPrimaryId` CHAR(15),
	`mobPtyPrimaryId` CHAR(15),
	`mobName` TEXT(30),
	`mobDescription` TEXT(255),
	`mobParentMobPrimaryId` CHAR(15),
	`mobCount` SHORT,
	`mobId` CHAR(10), constraint `Mob_PK` primary key (`mobPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`Mob`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'Mob' :
-- 	`mobPrimaryId`
-- 	`mobPtyPrimaryId`
-- 	`mobParentMobPrimaryId`
-- 	`mobCount`


-- Create new table `Paddock`.
-- `Paddock` : Table of Paddock
-- 	Namespace: All
-- 	Stores the details of a paddock on a property.
-- 	`pdkPrimaryId` : pdkPrimaryId identifies Paddock
-- 	`pdkPtyPrimaryId` : Foreign key to ppyPrimaryId in table  Property
-- 	`pdkArea` : The area of the Paddock in hectares
-- 	`pdkName` : The common name of the paddock
-- 	`pdkLptCode` : The pasture type of the paddock (see LKPastureType)
-- 	`pdkGPSCoordinates` : The Global Positioning Ssystem (GPS) coordinates of a paddock
-- 	`pdkPasturePercentage` : The percentage of a pasture species in a paddock  
create table `Paddock` ( 
	`pdkPrimaryId` CHAR(15),
	`pdkPtyPrimaryId` CHAR(15),
	`pdkArea` VARCHAR(6),
	`pdkName` VARCHAR(35),
	`pdkLptCode` VARCHAR(50),
	`pdkGPSCoordinates` CHAR(10),
	`pdkPasturePercentage` SHORT, constraint `Paddock_PK` primary key (`pdkPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`Paddock`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'Paddock' :
-- 	`pdkPrimaryId`
-- 	`pdkPtyPrimaryId`
-- 	`pdkName`
-- 	`pdkLptCode`
-- 	`pdkPasturePercentage`


-- Create new table `Property`.
-- `Property` : Table of Property
-- 	Namespace: All
-- 	Stores the details of  properties associated with a Contact.
-- 	`ptyPrimaryId` : ptyPrimaryId identifies Property
-- 	`ptyPIC` : Property Identification Code as per NFIS
-- 	`ptyBrandName` : Represents the brand name a property uses to identify wool clip
-- 	`ptyPropertyName` : The common name of the property
-- 	`ptyIsAgistmentFlg` : Is true if the property is agisted by the producer
-- 	`ptyFlockCareNumber` : The FlockCare accreditation number (as per MLA AUS-MEAT definition)
-- 	`ptyFlockPrimaryId` : The id of the flock for the property  
create table `Property` ( 
	`ptyPrimaryId` CHAR(15),
	`ptyPIC` VARCHAR(8),
	`ptyBrandName` VARCHAR(30),
	`ptyPropertyName` VARCHAR(40),
	`ptyIsAgistmentFlg` BIT,
	`ptyFlockCareNumber` TEXT(15),
	`ptyFlockPrimaryId` CHAR(15), constraint `Property_PK` primary key (`ptyPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`Property`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'Property' :
-- 	`ptyPrimaryId`
-- 	`ptyPIC`
-- 	`ptyBrandName`
-- 	`ptyIsAgistmentFlg`


-- Create new table `Contact`.
-- `Contact` : Table of Contact
-- 	Namespace: All
-- 	This table is a generic table where the type of the contact is defined by the definitions in the look up table LKContactType.
-- 	In the case were a contact has one or more ABN's is the producer and the producer has a more than one ABN the a new contact will need to be created for each ABN.
-- 	If the contact is of conatct type "Classer" then the field "cntClassedRegistrationNumber" must be non "null".
-- 	`cntPrimaryId` : cntPrimaryId identifies Contact
-- 	`cntFirstName` : First name of the contact
-- 	`cntLastName` : Last name of the contact
-- 	`cntMiddleInitial` : Middle initial of the contact
-- 	`cntAddress1` : First line of the physical address of the contact
-- 	`cntAddress2` : Second line of the physical address of the contact
-- 	`cntTown` : Town of of physical address of the contact
-- 	`cntPhoneNo1` : The business phone number for the contact
-- 	`cntFaxNo` : The fax number for the contact
-- 	`cntMobileNo` : The mobile phone number for the contact
-- 	`cntEmailAddress` : Email address of the contact
-- 	`cntMemo` : Comments about the contact
-- 	`cntPhone2` : Alternate  phone number of contact
-- 	`cntABN` : The company ABN to which the contact is associated
-- 	`cntRegisteredGST` : Is true if the contact is registered for GST
-- 	`cntTradingName` : Trading name of the  contact
-- 	`cntPostalAddress1` : The first line of the postal address for the contact
-- 	`cntPostalAddress2` : The second line of the postal address of the contact
-- 	`cntPostalAddressTown` : The postal address town of the contact
-- 	`cntLcyCode` : The contact type of the contact (see LKContactType)
-- 	`cntLctCode` : The title of the contact (see LKContactTitle)
-- 	`cntLccCode` : The country code of the physical address for the contact (see LKCountryCode)
-- 	`cntLstCode` : The state of the physical address of the  contact (see LKState)
-- 	`cntLpcCode` : The postcode of the physical address of the Contact (see LKPostCode)
-- 	`cntLpcPostalAdressCode` : The postcode of the postal address of the Contact (see LKPostCode)
-- 	`cntLccPostalAddressCountryCode` : The country code of the postal address for the contact (see LKCountryCode)
-- 	`cntLstPostalAddressCode` : The state of the postal address of the  contact (see LKState)
-- 	`cntClasserRegistrationNumber` : The classer registration number - should be non null if the contact type is "classer"  
create table `Contact` ( 
	`cntPrimaryId` CHAR(15),
	`cntFirstName` VARCHAR(20),
	`cntLastName` VARCHAR(20),
	`cntMiddleInitial` CHAR(1),
	`cntAddress1` VARCHAR(35),
	`cntAddress2` VARCHAR(35),
	`cntTown` VARCHAR(30),
	`cntPhoneNo1` VARCHAR(15),
	`cntFaxNo` VARCHAR(15),
	`cntMobileNo` VARCHAR(15),
	`cntEmailAddress` VARCHAR(128),
	`cntMemo` VARCHAR(255),
	`cntPhone2` VARCHAR(15),
	`cntABN` VARCHAR(15),
	`cntRegisteredGST` BIT,
	`cntTradingName` TEXT(35),
	`cntPostalAddress1` CHAR(10),
	`cntPostalAddress2` CHAR(10),
	`cntPostalAddressTown` CHAR(10),
	`cntLcyCode` VARCHAR(50),
	`cntLctCode` VARCHAR(10),
	`cntLccCode` TEXT(4),
	`cntLstCode` TEXT(3),
	`cntLpcCode` CHAR(4),
	`cntLpcPostalAdressCode` CHAR(4),
	`cntLccPostalAddressCountryCode` TEXT(4),
	`cntLstPostalAddressCode` TEXT(3),
	`cntClasserRegistrationNumber` CHAR(10), constraint `Contact_PK` primary key (`cntPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`Contact`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'Contact' :
-- 	`cntPrimaryId`
-- 	`cntFirstName`
-- 	`cntLastName`
-- 	`cntAddress1`
-- 	`cntTown`
-- 	`cntPhoneNo1`
-- 	`cntABN`
-- 	`cntRegisteredGST`
-- 	`cntTradingName`
-- 	`cntPostalAddress1`
-- 	`cntPostalAddressTown`
-- 	`cntLcyCode`
-- 	`cntLctCode`
-- 	`cntLccCode`
-- 	`cntLstCode`
-- 	`cntLpcCode`


-- Create new table `LKMeasurementUnit`.
-- `LKMeasurementUnit` : Table of MeasurementUnit
-- 	Namespace:  All
-- 	This table is a look up list of various measurement units.  Measurement units are:
-- 	START
-- 	lmuCode	lmuDescription
-- 	um	microns
-- 	mm	millimetres
-- 	cm	centimetres
-- 	m	metres
-- 	km	kilometres
-- 	ha	hectares
-- 	l	litres
-- 	ml	millilitres
-- 	g	grams
-- 	kg	kilograms
-- 	t	tonnes
-- 	h	hours
-- 	mi	minutes
-- 	s	seconds
-- 	d	days
-- 	w	weeks
-- 	mo	months
-- 	y	year
-- 	kmh	kilometres per hour
-- 	n/ktex	newtons per kilotex
-- 	l/hr	litres per hour
-- 	g/l	grams per litre
-- 	ml/ha	millilitres per hectare (spraying rate)
-- 	l/ha	litres per hectare (spraying rate)
-- 	ml/kg	millilitres per kilogram (dose rate based on live bodyweight of animal)
-- 	g/kg	grams per kilogram (dose rate based on live bodyweight of animal)
-- 	mg/ml	milligrams per millilitre (units of active chemical)
-- 	END
-- 	`lmuCode` : Codes for various measurement units
-- 	`lmuDescription` : Description of the measurement unit  
create table `LKMeasurementUnit` ( 
	`lmuCode` TEXT(10),
	`lmuDescription` TEXT(250), constraint `LKMeasurementUnit_PK` primary key (`lmuCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKMeasurementUnit`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKMeasurementUnit' :
-- 	`lmuCode`
-- 	`lmuDescription`


-- Create new table `LKAnimalChemicalType`.
-- `LKAnimalChemicalType` : Table of AnimalChemicalType
-- 	Namespace: Animal
-- 	This is a looktable containing the chemical types and their codes for the treatment of animal diseases. It also contains information regarding the registration and expiry dates of the chemicals.
-- 	The list is sourced from the Australian Pesticides and Veterinary Medicines Authority (APVMA) database PUBCRIS which is a register of all agricultural and veterinary chemicals registered for use in Australia. The listing is a subset of PUBCRIS. Details about chemicals can be found by searching the PUBCRIS database which is located on the following website: http://www.apvma.gov.au/
-- 	Withholding periods for chemicals have to be entered by the sheep producer as they are not listed on the PUBCRIS database. Details about withholding periods are found on the labels of the chemicals. The database stores a picture of each label as a reference.
-- 	Each chemical has a unique code on the PUBCRIS database, which we have used in this look up table.
-- 	See table in Microsoft Excel for full listing
-- 	latCode	latNCRISNumber	latProductName	latManufacturer	latProductDescription	latCommonName	latAmount	latUnits
-- 	1	37626	ACID-PAK 4-WAY W.S.	ALLTECH BIOTECHNOLOGY PTY LTD	"ORAL POWDER, PRE-MIX"	ENTEROCOCCUS FAECIUM	0.	CFU/g
-- 	2	37626	ACID-PAK 4-WAY W.S.	ALLTECH BIOTECHNOLOGY PTY LTD	"ORAL POWDER, PRE-MIX"	LACTOBACILLUS ACIDOPHILUS	0.	CFU/g
-- 	3	37626	ACID-PAK 4-WAY W.S.	ALLTECH BIOTECHNOLOGY PTY LTD	"ORAL POWDER, PRE-MIX"	AMYLASE	11.	AU/g
-- 	4	37626	ACID-PAK 4-WAY W.S.	ALLTECH BIOTECHNOLOGY PTY LTD	"ORAL POWDER, PRE-MIX"	PROTEASE	66.	HUT/G
-- 	5	37626	ACID-PAK 4-WAY W.S.	ALLTECH BIOTECHNOLOGY PTY LTD	"ORAL POWDER, PRE-MIX"	POTASSIUM	1.3	%
-- 	6	37626	ACID-PAK 4-WAY W.S.	ALLTECH BIOTECHNOLOGY PTY LTD	"ORAL POWDER, PRE-MIX"	SODIUM	2.5	%
-- 	7	56875	"ELANCO AH0492 EXTINOSAD LICE, FLY AND MAGGOT ELIMINATOR"	ELANCO ANIMAL HEALTH A DIV OF ELI LILLY AUSTRALIA P/L	TOPICAL SOLUTION/SUSPENSION	SPINOSAD	25.	g/L
-- 	8	56875	"ELANCO AH0492 EXTINOSAD LICE, FLY AND MAGGOT ELIMINATOR"	ELANCO ANIMAL HEALTH A DIV OF ELI LILLY AUSTRALIA P/L	TOPICAL SOLUTION/SUSPENSION	SPINOSAD	25.	g/L
-- 	9	52227	ELANCO AH0492 EXTINOSAD JETTING FLUID/FLYSTRIKE DRESSING FOR SHEEP IN LONG WOOL	ELANCO ANIMAL HEALTH A DIV OF ELI LILLY AUSTRALIA P/L	TOPICAL SOLUTION/SUSPENSION	SPINOSAD	25.	g/L
-- 	10	50005	CLIK SPRAY-ON SHEEP BLOWFLY TREATMENT	NOVARTIS ANIMAL HEALTH AUSTRALASIA PTY LIMITED	TOPICAL SOLUTION/SUSPENSION	DICYCLANIL	50.	g/L
-- 	`latCode` : The autogenerated code for the animal chemical
-- 	`latProductName` : The name of the product
-- 	`latManufacturer` : The manufacturer of the product
-- 	`latNCRISNumber` : The NCRIS number of the product
-- 	`latProductDescription` : The product description
-- 	`latCommonName` : The common name for the active chemical in the product
-- 	`latAmount` : The amount of active ingredient in the product
-- 	`latUnits` : The unit of measurement for the active ingredient  
create table `LKAnimalChemicalType` ( 
	`latCode` COUNTER,
	`latProductName` VARCHAR(250),
	`latManufacturer` VARCHAR(250),
	`latNCRISNumber` CHAR(5),
	`latProductDescription` VARCHAR(250),
	`latCommonName` VARCHAR(250),
	`latAmount` CHAR(30),
	`latUnits` CHAR(10), constraint `LKAnimalChemicalType_PK` primary key (`latCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKAnimalChemicalType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKAnimalChemicalType' :
-- 	`latCode`
-- 	`latProductName`
-- 	`latManufacturer`
-- 	`latNCRISNumber`


-- Create new table `LKWoolSaleCentre`.
-- `LKWoolSaleCentre` : Table of LKWoolSaleCentre
-- 	Namespace: Wool
-- 	This table contains a list of wool sales centres and their codes - as defined by AWEX
-- 	It denotes where the wool is sold (but is not necessarily the storage location of the wool - seeLKDeliveryCentre)
-- 	START
-- 	lwsCode	lwsDescription
-- 	A	Adelaide
-- 	B	Brisbane
-- 	F	Fremantle
-- 	G	Geelong
-- 	L	Launceston
-- 	M	Melbourne
-- 	N	Newcastle
-- 	R	Goulburn
-- 	S	Sydney
-- 	END
-- 	`lwsCode` : The code identifying the sale center
-- 	`lwsDescription` : The description of the sale centre code  
create table `LKWoolSaleCentre` ( 
	`lwsCode` TEXT(10),
	`lwsDescription` TEXT(35), constraint `LKWoolSaleCentre_PK` primary key (`lwsCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKWoolSaleCentre`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKWoolSaleCentre' :
-- 	`lwsCode`
-- 	`lwsDescription`


-- Create new table `MobClip`.
-- `MobClip` : Table of MobClip
-- 	Namespace: Wool
-- 	This table captures the realtionship between a mob and a clip.  Much of this information is gathered by the Classer at shearing and fullfills the needs of the AWEX classer specification.
-- 	`mcpPrimaryId` : mcpPrimaryId identifies MobClip
-- 	`mcpBreakFrom` : The bale number at which the mob starts
-- 	`mcpBreakTo` : The bale number at which the mob finishes
-- 	`mcpExoticBreedContact` : Is true if the mob has had contact with exotic breeds of sheep
-- 	`mcpCrutched` : Is true if the sheep has been crutched
-- 	`mcpIsChemicalLog` : Is true if a chemical log has been kept for the treatment of sheep
-- 	`mcpWoolGrowth` : The growth of the wool on the mob in months
-- 	`mcpIStenderWool` : Is true if the wool is considered tender
-- 	`mcpAverageWoolLength` : The average wool length of the fleece in the mob measured in mm
-- 	`mcpLwrCode` : The wool residual risk code (see LKWoolResidualRisk)
-- 	`mcpLwqCode` : The wool quality code (see LKWoolQuality)
-- 	`mcpLscCode` : The sex code for this mob in the clip (see LKSexCode)
-- 	`mcpLaeCode` : The code for the age of the mob (see LKAge)
-- 	`mcpMobPrimaryId` : The id of the mob that is associated with the clip
-- 	`mcpClpPrimaryId` : The id of the clip associated with the mob
-- 	`mcpLbsCode` : The code for the bur/seed status of the fleece(see LKBurrSeed)  
create table `MobClip` ( 
	`mcpPrimaryId` CHAR(15),
	`mcpBreakFrom` LONG,
	`mcpBreakTo` LONG,
	`mcpExoticBreedContact` BIT,
	`mcpCrutched` BIT,
	`mcpIsChemicalLog` BIT,
	`mcpWoolGrowth` LONG,
	`mcpIStenderWool` BIT,
	`mcpAverageWoolLength` LONG,
	`mcpLwrCode` VARCHAR(10),
	`mcpLwqCode` TEXT(6),
	`mcpLscCode` CHAR(1),
	`mcpLaeCode` TEXT(5),
	`mcpMobPrimaryId` CHAR(15),
	`mcpClpPrimaryId` CHAR(15),
	`mcpLbsCode` TEXT(6), constraint `MobClip_PK` primary key (`mcpPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`MobClip`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'MobClip' :
-- 	`mcpPrimaryId`


-- Create new table `LKSeedType`.
-- `LKSeedType` : Table of LKSeedType
-- 	Namespace: Animal
-- 	START
-- 	lstType	lstDescription
-- 	CLOVER	Clover
-- 	BARLEY	Barley Grass
-- 	CORK	Corkscrew
-- 	END                                               
-- 	`lstType` : The type of seed
-- 	`lstDescription` : The description of the seed type  
create table `LKSeedType` ( 
	`lstType` TEXT(10),
	`lstDescription` TEXT(20), constraint `LKSeedType_PK` primary key (`lstType`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKSeedType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKSeedType' :
-- 	`lstType`
-- 	`lstDescription`


-- Create new table `LKBurrType`.
-- `LKBurrType` : Table of LKBurrType
-- 	Namespace: Animal
-- 	START
-- 	lbtType	lbtDescription
-- 	CLOVBURR	Clover Burr
-- 	BATHBURR	Bathurst Burr  
-- 	END                            
-- 	`lbtType` : The type of burr
-- 	`lbtDescription` : The description of the burr type  
create table `LKBurrType` ( 
	`lbtType` TEXT(10),
	`lbtDescription` TEXT(20), constraint `LKBurrType_PK` primary key (`lbtType`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKBurrType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKBurrType' :
-- 	`lbtType`
-- 	`lbtDescription`


-- Create new table `LKSeedBurrContaminationStatus`.
-- `LKSeedBurrContaminationStatus` : Table of LKSeedBurrContaminationStatus
-- 	Namespace: Animal
-- 	These are taken from Ausmmeat National Livestock Language for Sheep Ovine
-- 	This is a mob based status
-- 	START
-- 	lscStatusCode	lscDescription
-- 	LIGHT	Belly only
-- 	MEDIUM	Legs and Belly
-- 	HEAVY	Legs, belly neck and sides
-- 	END
-- 	                                               
-- 	`lscStatusCode` : The seed/burr contamination status
-- 	`lscDescription` : The description of the level of seed/burr contamination status  
create table `LKSeedBurrContaminationStatus` ( 
	`lscStatusCode` TEXT(10),
	`lscDescription` TEXT(250), constraint `LKSeedBurrContaminationStatus_PK` primary key (`lscStatusCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKSeedBurrContaminationStatus`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKSeedBurrContaminationStatus' :
-- 	`lscStatusCode`
-- 	`lscDescription`


-- Create new table `LKDustContaminationStatus`.
-- `LKDustContaminationStatus` : Table of LKDustContaminationStatus
-- 	Namespace: Animal
-- 	These are taken from Ausmmeat National Livestock Language for Sheep Ovine
-- 	START
-- 	ldcStatus	ldcDescription
-- 	TIP	Surface contamination only
-- 	LIGHT	Contamination to 1/3 of staple length
-- 	MEDIUM	Contamination to 2/3 of staple length
-- 	HEAVY	Contamination to the skin
-- 	END
-- 	`ldcStatus` : The status of the dust contamination
-- 	`ldcDescription` : The description of the dust contamination status  
create table `LKDustContaminationStatus` ( 
	`ldcStatus` TEXT(25),
	`ldcDescription` TEXT(50), constraint `LKDustContaminationStatus_PK` primary key (`ldcStatus`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKDustContaminationStatus`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKDustContaminationStatus' :
-- 	`ldcStatus`
-- 	`ldcDescription`


-- Create new table `Consignment`.
-- `Consignment` : Table of Consignment
-- 	Namespace: Animal
-- 	This table details information about animals sold from the property. It treats the sale of a group of animals as a consignment.
-- 	It forms part of MLA's Livestock Production Assurance (LPA) onfarm food safety certification program.
-- 	Estimated weights refer to definitions from Ausmmeat National Livestock Language for Sheep Ovine.
-- 	They relate to weight descriptions for a lot or consignment and are specified by average and range.
-- 	`cstCntName` : cstCntName partly identifies Consignment
-- 	`cstWeight` : Is the estimated live weight of the sheep measured in kg. The range is also reported and condition of animals prior to weighing is also noted (eg off feed or on feed and water).
-- 	`cstDate` : Date estimated liveweight is recorded
-- 	`cstPrimaryID` : cstPrimaryID identifies Consignment
-- 	`cstAmlPrimaryId` : The animal's id for estimated liveweight
-- 	`cstHotStandardCarcaseWeight` : The estimated hot standard carcase weight (EHSCW) is calculated from the live weight
-- 	`cstLstCode` : The type of sale for the consignment of sheep (see LKSaleType)
-- 	`cstSalePriceA` : The average sale price for the consignment in dollars per head
-- 	`cstSalePriceB` : The average sale price for the consignment in cents per kilogram
-- 	`cstDateYarded` : The data and time the consignment was yarded on the property
-- 	`cstNvdNumber` : The National Vendor Declaration (NVD) Number for the livestock sold
-- 	`cstCntTransportCo` : The transport company used to load and deliver the sold livestock (see Contact table and LKContactType)
-- 	`cstLscCode` : The code for the livestock sale centre if sold at a sale yard(see LKLivestockSaleCentre
-- 	`cstYardTime` : The date and time of day the consignment was yarded on the property ready for transport
-- 	`cstDestination` : The destination of the consignment (eg abbatoir, saleyard, another property), (to be recorded on Flockcare transport consignment note)
-- 	`cstDeliveryInstructions` : Special delivery instructions (to be recorded on Flockcare transport consignment note)
-- 	`cstDepartureTime` : The date and time of day the consignment departed the property (to be recorded on Flockcare transport consignment note)
-- 	`cstTransportCoRegNumber` : cstTransportCoRegNumber is of Consignment  
create table `Consignment` ( 
	`cstCntName` CHAR(15),
	`cstWeight` TEXT(10),
	`cstDate` CHAR(10),
	`cstPrimaryID` CHAR(10),
	`cstAmlPrimaryId` CHAR(15),
	`cstHotStandardCarcaseWeight` CHAR(10),
	`cstLstCode` TEXT(25),
	`cstSalePriceA` CHAR(10),
	`cstSalePriceB` CHAR(10),
	`cstDateYarded` DATETIME,
	`cstNvdNumber` CHAR(10),
	`cstCntTransportCo` CHAR(15),
	`cstLscCode` TEXT(10),
	`cstYardTime` DATETIME,
	`cstDestination` TEXT(15),
	`cstDeliveryInstructions` TEXT(40),
	`cstDepartureTime` DATETIME,
	`cstTransportCoRegNumber` CHAR(6), constraint `Consignment_PK` primary key (`cstPrimaryID`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`Consignment`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'Consignment' :
-- 	`cstCntName`
-- 	`cstWeight`
-- 	`cstDate`
-- 	`cstPrimaryID`
-- 	`cstAmlPrimaryId`
-- 	`cstHotStandardCarcaseWeight`
-- 	`cstLstCode`
-- 	`cstSalePriceA`
-- 	`cstSalePriceB`
-- 	`cstDateYarded`
-- 	`cstNvdNumber`
-- 	`cstCntTransportCo`
-- 	`cstYardTime`
-- 	`cstTransportCoRegNumber`


-- Create new table `SeedBurrContaminationStatus`.
-- `SeedBurrContaminationStatus` : Table of LKSeedBurrContaminationStatus
-- 	Namespace: Animal
-- 	These are taken from Ausmmeat National Livestock Language for Sheep Ovine
-- 	This is a mob based status
-- 	Status                  Description
-- 	LIGHT                   Belly only
-- 	MEDIUM               Legs and Belly
-- 	HEAVY                 Legs, belly neck and sides
-- 	                                               
-- 	`sbsPercentage` : Estimated amount of seed/burr contamination expressed as a percentage for the degree, location and major types 
-- 	`sbsLbtType` : Type of burr present in the fleece(see LKBurrType)
-- 	`sbsLstType` : Type of seed present in the fleece(see LKSeedType)
-- 	`sbsLscStatusCode` : Description of the level of seed/burr contamination in the fleece(see LKSeedBurrContaminationStatus)
-- 	`sbsPrimaryId` : sbsPrimaryId identifies the status of Seed/Burr contamination
-- 	`sbsLdcStatus` : Description of the level of dust contamination in the fleece(see LKDustContaminationStatus)
-- 	`sbcPrimaryId` : sbcPrimaryId identifies that Seed/BurrContamination is present in the fleece  
create table `SeedBurrContaminationStatus` ( 
	`sbsPercentage` CHAR(10),
	`sbsLbtType` TEXT(10),
	`sbsLstType` TEXT(10),
	`sbsLscStatusCode` TEXT(10),
	`sbsPrimaryId` CHAR(10),
	`sbsLdcStatus` TEXT(25),
	`sbcPrimaryId` CHAR(10), constraint `SeedBurrContaminationStatus_PK` primary key (`sbsPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`SeedBurrContaminationStatus`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'SeedBurrContaminationStatus' :
-- 	`sbsPercentage`
-- 	`sbsPrimaryId`


-- Create new table `SeedBurrContamination`.
-- `SeedBurrContamination` : Table of LKSeedBurrContaminationStatus
-- 	Namespace: Animal
-- 	These are taken from Ausmmeat National Livestock Language for Sheep Ovine
-- 	This is a mob based status. It estimates the degree, location, and the major types of visible seed or burr contamination within a mob. If there is more than one level of contamination within the mob, the percentage of each is stated. For example: SEED mob 50% medium 50% heavy
-- 	Status                  Description
-- 	LIGHT                   Belly only
-- 	MEDIUM               Legs and Belly
-- 	HEAVY                 Legs, belly neck and sides
-- 	                                               
-- 	`sbcPrimaryId` : sbcPrimaryId identifies SeedBurrContamination in the fleece
-- 	`sbcMobPrimaryId` : sbcMobPrimaryId identifies the mob the SeedBurrContamination relates to
-- 	`sbcDate` : Date on which the Seed/Burr contamination of the fleece is assessed   
create table `SeedBurrContamination` ( 
	`sbcPrimaryId` CHAR(10),
	`sbcMobPrimaryId` CHAR(15),
	`sbcDate` CHAR(10), constraint `SeedBurrContamination_PK` primary key (`sbcPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`SeedBurrContamination`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'SeedBurrContamination' :
-- 	`sbcPrimaryId`
-- 	`sbcMobPrimaryId`
-- 	`sbcDate`


-- Create new table `ContactToProperty`.
-- `ContactToProperty` : Table of ContactToProperty
-- 	Namespace: All
-- 	This is an intersection table as a property may have multiple contacts and a contact may have multiple properties.
-- 	`ctpPrimaryId` : ctpPrimaryId identifies ContactToProperty
-- 	`ptyPrimaryId` : The property the contact belongs to. A contact may belong to multiple properties
-- 	`cntPrimaryId` : The contact name for the property. A property may have a number of contacts  
create table `ContactToProperty` ( 
	`ctpPrimaryId` CHAR(15),
	`ptyPrimaryId` CHAR(15),
	`cntPrimaryId` CHAR(15), constraint `ContactToProperty_PK` primary key (`ctpPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`ContactToProperty`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'ContactToProperty' :
-- 	`ctpPrimaryId`
-- 	`ptyPrimaryId`
-- 	`cntPrimaryId`


-- Create new table `LKSaleType`.
-- `LKSaleType` : Table of LKSaleType
-- 	Namespace: Animal
-- 	These are taken from Ausmmeat National Livestock Language for Sheep Ovine
-- 	START
-- 	lstCode	lstDescription
-- 	AUCTION	Sale at auction
-- 	DIRECT	Sale direct to abbatoir
-- 	END
-- 	`lstCode` : The status of the sale type (auction or direct sale)
-- 	`lstDescription` : The description of the sale type  
create table `LKSaleType` ( 
	`lstCode` TEXT(25),
	`lstDescription` TEXT(50), constraint `LKSaleType_PK` primary key (`lstCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKSaleType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKSaleType' :
-- 	`lstCode`
-- 	`lstDescription`


-- Create new table `PropertyContamination`.
-- `PropertyContamination` : Table of PropertyContamination
-- 	Namespace: Animal
-- 	This table records the details of the risk assessment of contaminated sites on a property.
-- 	It forms part of MLA's Livestock Production Assurance (LPA) onfarm food safety certification program.
-- 	`pycPrimaryId` : pycPrimaryId identifies PropertyContamination
-- 	`pycReason` : The reason the site on the property is or may be contaminated
-- 	`pycResults` : The results of the soil testing or animal fat sample testing for contamination
-- 	`pycManagementDescription` : Description of how the site is managed to eliminate the risk of livestock contamination
-- 	`pycPtyPrimaryId` : The property to which the contaminated site belongs
-- 	`pycSite` : The site on the property which is contaminated (should contain a reference to the property map)  
create table `PropertyContamination` ( 
	`pycPrimaryId` CHAR(15),
	`pycReason` DATETIME,
	`pycResults` CHAR(15),
	`pycManagementDescription` TEXT(10),
	`pycPtyPrimaryId` CHAR(15),
	`pycSite` CHAR(10), constraint `PropertyContamination_PK` primary key (`pycPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`PropertyContamination`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'PropertyContamination' :
-- 	`pycPrimaryId`
-- 	`pycReason`
-- 	`pycResults`
-- 	`pycManagementDescription`
-- 	`pycPtyPrimaryId`
-- 	`pycSite`


-- Create new table `LKPaddcokApplicationType`.
-- `LKPaddcokApplicationType` : Table of LKPaddcokApplicationType
-- 	Namespace: Animal
-- 	This is a lookup table of methods of application (spreading, spraying, etc) of chemicals to a paddock, pasture or crop. It is  based on MLA's Livestock Production Assurance (LPA) onfarm food safety certification program.
-- 	It is a user defined table.
-- 	`lpaCode` : The code for the method of application ofa  chemical treatment to a crop, pasture or paddock
-- 	`lpaDescription` : The decription of the code for the application method  
create table `LKPaddcokApplicationType` ( 
	`lpaCode` VARCHAR(10),
	`lpaDescription` VARCHAR(35), constraint `LKPaddcokApplicationType_PK` primary key (`lpaCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKPaddcokApplicationType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKPaddcokApplicationType' :
-- 	`lpaCode`
-- 	`lpaDescription`


-- Create new table `GrainFodderTreatment`.
-- `GrainFodderTreatment` : Table of GrainFodderTreatment
-- 	Namespace: Animal
-- 	It is a table to record grain and fodder chemical treatments. 
-- 	It is  based on MLA's Livestock Production Assurance (LPA) onfarm food safety certification program.
-- 	`gftPrimaryId` : gftPrimaryId identifies GrainFodderTreatment
-- 	`gftDate` : The date the chemical was applied
-- 	`gftSiloId` : The silo/storage location to which the chemical was applied
-- 	`gftAmount` : The amount of feed (grain/fodder) that the chemical treatment was applied to
-- 	`gftProductBatchNumber` : The measurement units for the application of the chemical (see LKMeasurementunit)
-- 	`gftExpirydate` : The expiry date of the chemical treatment used
-- 	`gftSafeDate` : The date that the grain/fodder is safe to use after chemical treatment
-- 	`gftLgrCode` : The type of stored grain or fodder that was chemically treated (see LKGrainType)
-- 	`gftLtyCode` : The type of chemical treatment applied to the paddock or stored grain/fodder (see LKTreatmentType)
-- 	`gftLftCode` : gftLftCode is of GrainFodderTreatment  
create table `GrainFodderTreatment` ( 
	`gftPrimaryId` CHAR(15),
	`gftDate` DATETIME,
	`gftSiloId` TEXT(10),
	`gftAmount` TEXT(10),
	`gftProductBatchNumber` TEXT(10),
	`gftExpirydate` DATETIME,
	`gftSafeDate` DATETIME,
	`gftLgrCode` TEXT(10),
	`gftLtyCode` TEXT(10),
	`gftLftCode` TEXT(10), constraint `GrainFodderTreatment_PK` primary key (`gftPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`GrainFodderTreatment`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'GrainFodderTreatment' :
-- 	`gftPrimaryId`
-- 	`gftDate`
-- 	`gftSiloId`
-- 	`gftAmount`
-- 	`gftProductBatchNumber`
-- 	`gftExpirydate`
-- 	`gftSafeDate`
-- 	`gftLgrCode`
-- 	`gftLtyCode`
-- 	`gftLftCode`


-- Create new table `LKGrainType`.
-- `LKGrainType` : Table of LKGrainType
-- 	Namespace: Animal
-- 	This table is a look uptable of various grains and fodder types stored on farm and their codes.  
-- 	START
-- 	lgrCode	lgrDescription
-- 	BARLEY	Feed Barley
-- 	OATS	Feed Oats
-- 	PADRICE	Feed rice
-- 	RAPE	Fodder rape (canola)
-- 	SBEET	Fodder beet
-- 	TRICALE	Feed Triticale
-- 	WHEAT	Feed wheat
-- 	END
-- 	`lgrCode` : The  code for the grain or fodder type that is stored on farm
-- 	`lgrDescription` : The description of the grain or fodder type  
create table `LKGrainType` ( 
	`lgrCode` TEXT(10),
	`lgrDescription` TEXT(35), constraint `LKGrainType_PK` primary key (`lgrCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKGrainType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKGrainType' :
-- 	`lgrCode`
-- 	`lgrDescription`


-- Create new table `FeedingHistory`.
-- `FeedingHistory` : Table of FeedingHistory
-- 	Namespace: Animal
-- 	This table stores details of feeding records for mobs of animals.
-- 	It is  based on MLA's Livestock Production Assurance (LPA) onfarm food safety certification program.
-- 	`fhyPrimaryId` : fhyPrimaryId identifies FeedingHistory
-- 	`fhyCVDNumber` : This is the Commodity Vendor Declaration (CVD) that should accompany all introduced stock feeds
-- 	`fhyAmount` : The amount of the feedstuff purchased
-- 	`fhyStartdate` : The date the feeding commenced
-- 	`fhyFinishDate` : The date the feeding finished
-- 	`fhyMobPrimaryId` : The name of the mob the animal  belongs to for the feeding 
-- 	`fhyFtyCode` : The code for the feed type used (see LKFeedType)  
create table `FeedingHistory` ( 
	`fhyPrimaryId` CHAR(15),
	`fhyCVDNumber` CHAR(15),
	`fhyAmount` SHORT,
	`fhyStartdate` DATETIME,
	`fhyFinishDate` DATETIME,
	`fhyMobPrimaryId` CHAR(15),
	`fhyFtyCode` TEXT(10), constraint `FeedingHistory_PK` primary key (`fhyPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`FeedingHistory`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'FeedingHistory' :
-- 	`fhyPrimaryId`
-- 	`fhyCVDNumber`
-- 	`fhyAmount`
-- 	`fhyStartdate`
-- 	`fhyFinishDate`
-- 	`fhyMobPrimaryId`
-- 	`fhyFtyCode`


-- Create new table `LKFeedType`.
-- `LKFeedType` : Table of FeedType
-- 	Namespace: Animal
-- 	This table is a lookup table of various feed types and their codes.  This list is user defined.
-- 	`lftCode` : The chemical code
-- 	`lftDescription` : The description of the chemical for paddock application  
create table `LKFeedType` ( 
	`lftCode` TEXT(10),
	`lftDescription` TEXT(35), constraint `LKFeedType_PK` primary key (`lftCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKFeedType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKFeedType' :
-- 	`lftCode`
-- 	`lftDescription`


-- Create new table `LKLivestockSaleCentre`.
-- `LKLivestockSaleCentre` : Table of LKLivestockSaleCentre
-- 	Namespace: Animal
-- 	This table contains a list of livestock sales centres and their codes. It is based on sale centres listed by National Livestock Reporting Service (NLRS) and the Saleyards Operators Information Centre http://www.saleyards.info/ 
-- 	START
-- 	lscCode 	lscState	lscDescription 
-- 	ARMIDALE	NSW	Armidale
-- 	BATHURST	NSW	Bathurst
-- 	BEGA	NSW	Bega
-- 	BINWAY	NSW	Binnaway
-- 	BLAYNEY	NSW	Blayney
-- 	BRAID	NSW	Braidwood
-- 	CAMDEN	NSW	Camden
-- 	CASINO	NSW	Casino
-- 	COOLAH	NSW	Coolah
-- 	COOMA	NSW	Cooma
-- 	COONAMB	NSW	Coonamble
-- 	COOTMUN	NSW	Cootamundra
-- 	COROWA	NSW	Corowa
-- 	COWRA	NSW	Cowra
-- 	DENQUIN 	NSW	Deniliquin
-- 	DORRIGO	NSW	Dorrigo
-- 	DUBBO	NSW	Dubbo
-- 	DUNEDOO	NSW	Dunedoo
-- 	FINLEY	NSW	Finley
-- 	FORBES	NSW	Forbes
-- 	GLENINN	NSW	Glen Innes
-- 	GLOUCEST	NSW	Gloucester
-- 	GOULBURN	NSW	Goulburn
-- 	GRAFTON	NSW	Grafton
-- 	GRIFFITH	NSW	Griffith
-- 	GUNDAGUI	NSW	Gundagai
-- 	GUNNEDAH	NSW	Gunnedah
-- 	GUYRA	NSW	Guyra
-- 	HARDEN	NSW	Harden
-- 	INVERELL 	NSW	Inverell
-- 	JERILD	NSW	Jerilderie
-- 	KEMPSEY	NSW	Kempsey
-- 	LISMORE	NSW	Lismore
-- 	MACKVILL	NSW	Macksville
-- 	MAITLAND	NSW	Maitland
-- 	MOREE	NSW	Moree
-- 	MORUYA	NSW	Moruya
-- 	MOSSVALE	NSW	Moss Vale
-- 	MUDGEE	NSW	Mudgee
-- 	NARRABRI	NSW	Narrabri
-- 	NARRMINE	NSW	Narromine
-- 	NOWRA	NSW	Nowra
-- 	ORANGE	NSW	Orange
-- 	QUIRINDI	NSW	Quirindi
-- 	SCONE	NSW	Scone
-- 	SINGLE	NSW	Singleton
-- 	TAMWORTH	NSW	Tamworth
-- 	TAREE	NSW	Taree
-- 	TEMORA	NSW	Temora
-- 	TENTER	NSW	Tenterfield
-- 	TULLIB	NSW	Tullibigeal
-- 	TUMUT	NSW	Tumut
-- 	URANA	NSW	Urana
-- 	WAGGA	NSW	Wagga Wagga
-- 	WALCHA	NSW	Walcha
-- 	WALGETT	NSW	Walgett
-- 	WAUCHOPE	NSW	Wauchope
-- 	WESTWYAL	NSW	West Wyalong
-- 	WINDSOR	NSW	Windsor
-- 	YASS	NSW	Yass
-- 	YOUNG	NSW	Young
-- 	BOHNING	NT	Bohning Yards Roe Creek Alice Springs
-- 	BEAUDES	QLD	Beaudesert
-- 	BELYANDO	QLD	Belyando
-- 	BIGGENDEN	QLD	Biggenden
-- 	BLACKALL	QLD	Blackall
-- 	CHINCHIL	QLD	Chinchilla
-- 	CHURCHILL	QLD	Churchill
-- 	CLONCURRY	QLD	Cloncurry
-- 	COOLOOLA	QLD	Cooloola
-- 	DALBY	QLD	Dalby
-- 	DALRYMPLE	QLD	Dalrymple
-- 	EMERALD	QLD	Emerald
-- 	GRACEMERE	QLD	Gracemere
-- 	GYMPIE	QLD	Gympie
-- 	HARRISVIL	QLD	Harrisville
-- 	KINGAROY	QLD	Kingaroy
-- 	LONGREACH	QLD	Longreach
-- 	MAREEBA	QLD	Mareeba
-- 	MARYBOR	QLD	Maryborough
-- 	MORETON	QLD	Moreton
-- 	MURGON	QLD	Murgon
-- 	NEBOBRAE	QLD	Nebo Braeside
-- 	OAKEY	QLD	Oakey
-- 	ROCKHAMP	QLD	Rockhampton
-- 	ROMA	QLD	Roma-Bungil
-- 	TOOGOOL	QLD	Toogoolawah
-- 	TOOWOOMBA	QLD	Toowoomba
-- 	WANDOAN	QLD	Wandoan
-- 	WARWICK	QLD	Warwick
-- 	COMPASS	SA	Mount Compass
-- 	GAMBIER	SA	Mount Gambier
-- 	MILLICENT	SA	Millicent
-- 	NARRCRTE	SA	Narracoorte
-- 	SAEXCH	SA	South Australian Livestock Exchange Dublin
-- 	LAUNCEST	TAS	Launceston
-- 	POWRANNA	TAS	Powranna
-- 	BALLARAT	VIC	Ballarat
-- 	BENDIGO	VIC	Bendigo
-- 	CAMPDOWN	VIC	Camperdown
-- 	CASTERTON	VIC	Casterton
-- 	COLAC	VIC	Colac
-- 	CORRYONG	VIC	Corryong
-- 	ECHUCA	VIC	Echuca
-- 	EUROA	VIC	Euroa
-- 	HAMILTON	VIC	Hamilton
-- 	HORSHAM	VIC	Horsham
-- 	KERANG	VIC	Kerang
-- 	KORBURRA	VIC	Korumburra
-- 	KYNETON	VIC	Kyneton
-- 	PAKHAM	VIC	Pakenham
-- 	SALE	VIC	Sale
-- 	SHEPPART	VIC	Shepparton
-- 	SWANHILL	VIC	Swan Hill
-- 	WANGARAT	VIC	Wangaratta
-- 	WARNBOOL	VIC	Warnambool
-- 	WARRACK	VIC	Warracknabeal
-- 	WARRAGUL	VIC	Warragul
-- 	WODONGA	VIC	Wodonga
-- 	KATANING	WA	Katanning
-- 	MIDLAND	WA	Midland
-- 	END
-- 	`lscCode` : The code identifying the livestock sale center
-- 	`lscDescription` : The description of the livestock sale centre code
-- 	`lscState` : The state in which the sale centre belongs  
create table `LKLivestockSaleCentre` ( 
	`lscCode` TEXT(10),
	`lscDescription` TEXT(250),
	`lscState` TEXT(3), constraint `LKLivestockSaleCentre_PK` primary key (`lscCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKLivestockSaleCentre`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKLivestockSaleCentre' :
-- 	`lscCode`
-- 	`lscDescription`
-- 	`lscState`


-- Create new table `LKTraitTypeScore`.
-- `LKTraitTypeScore` : Table ofLKTraitTypeScore
-- 	Namespace: Animal, Genetics
-- 	After: LKMeasurementType
-- 	Look up table of the scores and descriptions for scored traits
-- 	START
-- 	ltslmtCode             	ltsDescription	ltsCode	ltsDefinition
-- 	LambEase	Lambing ease	0	Unobserved
-- 	LambEase	Lambing ease	1	No assistance
-- 	LambEase	Lambing ease	2	Some assistance
-- 	LambEase	Lambing ease	3	Hard Assistance
-- 	LambEase	Lambing ease	4	Abnormal presentation
-- 	LambEase	Lambing ease	5	Other
-- 	FatScore	Fat score	1	0-5mm of fat (MLA definition) - leanest
-- 	FatScore	Fat score	2	6-10mm of fat (MLA definition)
-- 	FatScore	Fat score	3	11-15mm of fat (MLA defintion)
-- 	FatScore	Fat score	4	16 - 20mm of fat (MLA definition)
-- 	FatScore	Fat score	5	>= 21mm of fat (MLA definition) - fattest
-- 	BodyWrinkle	Body wrinkle score	1	No wrinkles (refer to AMSEA standard)
-- 	BodyWrinkle	Body wrinkle score	2	refer to AMSEA standard
-- 	BodyWrinkle	Body wrinkle score	3	refer to AMSEA standard
-- 	BodyWrinkle	Body wrinkle score	4	refer to AMSEA standard
-- 	BodyWrinkle	Body wrinkle score	5	Heavy wrinkles (refer to AMSEA standard)
-- 	FaceCover	Face cover score	1	No face cover (refer to AMSEA standard)
-- 	FaceCover	Face cover score	2	refer to AMSEA standard
-- 	FaceCover	Face cover score	3	refer to AMSEA standard
-- 	FaceCover	Face cover score	4	refer to AMSEA standard
-- 	FaceCover	Face cover score	5	Muffled face - refer to AMSEA standard
-- 	FleeceRot	Fleece rot score	0	No fleece rot present - refer to AMSEA standard
-- 	FleeceRot	Fleece rot score	1	refer to AMSEA standard
-- 	FleeceRot	Fleece rot score	2	refer to AMSEA standard
-- 	FleeceRot	Fleece rot score	3	refer to AMSEA standard
-- 	FleeceRot	Fleece rot score	4	refer to AMSEA standard
-- 	FleeceRot	Fleece rot score	5	Heavy infestation of fleece rot - refer to AMSEA standard
-- 	NeckWrinkle	Neck wrinkle score	1	No wrinkling on the neck - refer to AMSEA standard
-- 	NeckWrinkle	Neck wrinkle score	2	refer to AMSEA standard
-- 	NeckWrinkle	Neck wrinkle score	3	refer to AMSEA standard
-- 	NeckWrinkle	Neck wrinkle score	4	refer to AMSEA standard
-- 	NeckWrinkle	Neck wrinkle score	5	Large wrinkles and folds on the neck - refer to AMSEA standard
-- 	DOHNEPRODGR	Dohne Product Grade	AA	Highest grade. Dohne Product grade is the index value summarised into 1 of 4 measured performance grades
-- 	DOHNEPRODGR	Dohne Product Grade	A	Second highest product grade
-- 	DOHNEPRODGR	Dohne Product Grade	B	Third highest product grade
-- 	DOHNEPRODGR	Dohne Product Grade	C	Lowest product grade
-- 	DOHNEEFFGR	Dohne Efficience Grade	AA	Highest grade. Efficience grade is the grade given by Dohne inspectors for visual performance of the animal.
-- 	DOHNEEFFGR	Dohne Efficience Grade	A	Second highest efficience grade
-- 	DOHNEEFFGR	Dohne Efficience Grade	B	Third highest efficience grade
-- 	DOHNEEFFGR	Dohne Efficience Grade	C	Lowest efficience grade
-- 	DOHNEFINALGR	Dohne Final Grade	AA	Highest final grade. It is the combined grade of the product and efficience grades.
-- 	DOHNEFINALGR	Dohne Final Grade	A	Second highest final grade
-- 	DOHNEFINALGR	Dohne Final Grade	B	Third highest final grade
-- 	DOHNEFINALGR	Dohne Final Grade	C	Lowest final grade
-- 	FrameSize	Frame size score	A	71 cm or more height at the withers (description "Large")
-- 	FrameSize	Frame size score	B	66-70 cm height at the withers (description "Medium")
-- 	FrameSize	Frame size score	C	61-65 cm height at the withers (description "Medium")
-- 	FrameSize	Frame size score	D	56-60 cm height at the withers (description "Medium")
-- 	FrameSize	Frame size score	E	55 or less cm height at the withers (description "Small")
-- 	EarStatus	Ear status 	INTACT	No blemishes
-- 	EarStatus	Ear status 	OWISE	Where ears have been subject to marking, tagging or damage due to natural causes
-- 	WeightScore	Liveweight score	8	up to 8 kg carcase based on HSCW
-- 	WeightScore	Liveweight score	10	8 up to 10 kg carcase based on HSCW
-- 	WeightScore	Liveweight score	12	10 up to 12 kg carcase based on HSCW
-- 	WeightScore	Liveweight score	14	12 up to 14 kg carcase based on HSCW
-- 	WeightScore	Liveweight score	16	14 up to 16 kg carcase based on HSCW
-- 	WeightScore	Liveweight score	18	16 up to 18 kg carcase based on HSCW
-- 	WeightScore	Liveweight score	20	18 up to 20 kg carcase based on HSCW
-- 	WeightScore	Liveweight score	22	20 up to 22 kg carcase based on HSCW
-- 	WeightScore	Liveweight score	24	22 up to 24 kg carcase based on HSCW
-- 	WeightScore	Liveweight score	26	24 upto 26 kg carcase based on HSCW
-- 	WeightScore	Liveweight score	28	28 and above kg carcase based on HSCW
-- 	PREGSTATUS	Pregnancy status	PREGTESLM	Pregancy tested in lamb
-- 	PREGSTATUS	Pregnancy status	PREGTESNLM	Pregancy tested not in lamb
-- 	PREGSTATUS	Pregnancy status	STATMAT	Where Ewe(s) have been exposed to ram(s) in the previous 6 months (nominate the length of time in months)
-- 	PREGSTATUS	Pregnancy status	NOTSTATMAT	Not station mated, where the ewe(s) have not been intentianlly exposed to ram(s)
-- 	PREGSTATUS	Pregnancy status	UNKNOWN	Pregancy status of ewe(s) is unknown or declared
-- 	HornStatus	Horn status	HORNED	Where the horn(s) is/are either intact or partially removed
-- 	HornStatus	Horn status	DEHORNED	Where the horn has been removed leaving little or no protrusion of horn from the skull
-- 	HornStatus	Horn status	POLL	Where the sheep is hornless as a genetic attribute (may show some evidence of scurs)
-- 	HornStatus	Horn status	SCURS	Scurs
-- 	TailStatus	Tail status	INTACT	Considered natural length
-- 	TailStatus	Tail status	DOCKED	Part of the tail has been removed
-- 	SKINWRINKLE	Skin wrinkle score	PLAIN	No wrinkles
-- 	SKINWRINKLE	Skin wrinkle score	VLIGHT	Very light wrinkles
-- 	SKINWRINKLE	Skin wrinkle score	LIGHT	Light wrinkles
-- 	SKINWRINKLE	Skin wrinkle score	MEDIUM	Medium wrinkles
-- 	SKINWRINKLE	Skin wrinkle score	HEAVY	Very wrinkled
-- 	CrutchStatus	Crutching status	FULL	A crutch that extends over and above the tail
-- 	CrutchStatus	Crutching status	KEYHOLE	A crutch under the tail and on the tip of tail
-- 	CrutchStatus	Crutching status	MARKET	A very light crutch removing only dags under the tail
-- 	Dentition	Dentition	NIL	No permanaent incisor teeth - Birth to approx 15 months of age
-- 	Dentition	Dentition	MU0	No permanaent upper molar teeth - Birth to approx 5 months of age
-- 	Dentition	Dentition	MU1	Evidence of eruption of first permanaent upper molar teeth - Erupts at approx 3-5 months of age
-- 	Dentition	Dentition	IN2	Evidence of eruption of one but not more than two permanaent incisor teeth - First tooth erupts at approx 12-20 months of age
-- 	Dentition	Dentition	IN4	Evidence of eruption of three but not more than four permanaent incisor teeth - Third tooth erupts at approx 18-32 months of age
-- 	Dentition	Dentition	IN6	Evidence of eruption of five but not more than six permanaent incisor teeth - Fifth tooth erupts at approx 30-45 months of age
-- 	Dentition	Dentition	IN8	Evidence of eruption of seven or more permanaent incisor teeth - Seventh tooth erupts at approx 42 months of age
-- 	Condition	Condition score	1	0-5 mm estimated tissue depth at the GR site
-- 	Condition	Condition score	2	6-10 mm estimated tissue depth at the GR site
-- 	Condition	Condition score	3	11-15 mm estimated tissue depth at the GR site
-- 	Condition	Condition score	4	16-20 mm estimated tissue depth at the GR site
-- 	Condition	Condition score	5	21mm and over mm estimated tissue depth at the GR site
-- 	MulesStatus	Mulesing status	NOMULES	Sheep has not been mulesed
-- 	MulesStatus	Mulesing status	MULES	Sheep has been mulesed (full)
-- 	MulesStatus	Mulesing status	PARTMULES	Sheep has been mulesed using a modified procedure (partial)
-- 	END
-- 	`ltslmtCode` : ltslmtCode partly identifies LKTraitTypeScore
-- 	`ltsCode` : The code for the scored trait type
-- 	`ltsDescription` : The description of the scored  trait type
-- 	`ltsID` : Primary key, required for model to validate, but unused
-- 	`ltsDefinition` : The definition of the scored trait type  
create table `LKTraitTypeScore` ( 
	`ltslmtCode` VARCHAR(50),
	`ltsCode` VARCHAR(10),
	`ltsDescription` VARCHAR(250),
	`ltsID` COUNTER,
	`ltsDefinition` VARCHAR(250), constraint `LKTraitTypeScore_PK` primary key (`ltsID`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKTraitTypeScore`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKTraitTypeScore' :
-- 	`ltslmtCode`
-- 	`ltsCode`
-- 	`ltsDescription`
-- 	`ltsID`


-- Create new table `Flock`.
-- `Flock` : Table of Flock
-- 	Namespace: All
-- 	This table stores details of a flock. It allows a property to have more than one flock.
-- 	`flkPrimaryId` : flkPrimaryId identifies Flock
-- 	`flkPtyPrimaryId` : Identifies the property on which the flock belongs.
-- 	`flkName` : The common name of the flock
-- 	`flkDescription` : Description of the  flock
-- 	`flkCount` : The number of animals in the flock
-- 	`flkCode` : Registered or unregistered code for the flock (4 digits)  
create table `Flock` ( 
	`flkPrimaryId` CHAR(15),
	`flkPtyPrimaryId` CHAR(15),
	`flkName` TEXT(30),
	`flkDescription` TEXT(255),
	`flkCount` SHORT,
	`flkCode` CHAR(10), constraint `Flock_PK` primary key (`flkPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`Flock`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'Flock' :
-- 	`flkPrimaryId`
-- 	`flkPtyPrimaryId`
-- 	`flkCount`


-- Create new table `MeasurementEvent`.
-- `MeasurementEvent` : Table of MeasurementEvent
-- 	Namespace: All
-- 	The measurement event for an animal is grouped for easy reference. For example "2002 drop rams fleece test"
-- 	`mevPrimaryId` : mevPrimaryId identifies MeasurementEvent
-- 	`mevDescription` : Description of the measurement event. For example, 2002 drop hogget bodyweights.
-- 	`mevDateTime` : The date and time the measurement event occurred  
create table `MeasurementEvent` ( 
	`mevPrimaryId` INTEGER,
	`mevDescription` VARCHAR(250),
	`mevDateTime` DATETIME, constraint `MeasurementEvent_PK` primary key (`mevPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`MeasurementEvent`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'MeasurementEvent' :
-- 	`mevPrimaryId`
-- 	`mevDescription`
-- 	`mevDateTime`


-- Create new table `LKEBVType`.
-- `LKEBVType` : Table of LKEBVType
-- 	Namespace: Animal, Genetics
-- 	Look up table for the type of EBV
-- 	START
-- 	letCode	letGrouping	letUnit	letDescription	letDefinition	letReference
-- 	BWT	Liveweight traits	kg	Birth weight	Estimates the genetic difference between animals in weight at birth	Merino Genetics Services
-- 	WWT	Liveweight traits	kg	Weaning weight	Estimates the genetic difference between animals in liveweight at 100 days of age	Merino Genetics Services
-- 	MWWT	Liveweight traits	kg	Maternal weaning weight	Estimates the ewe's potential for milk production and ability to provide a better maternal environment.	Merino Genetics Services
-- 	PWWT	Liveweight traits	kg	Post weaning weight	Estimates the genetic difference between animals in liveweight at 225 days of age	Merino Genetics Services
-- 	YWT	Liveweight traits	kg	Yearling weight	Estimates the genetic difference between animals in liveweight at 360 days of age	Merino Genetics Services
-- 	HWT	Liveweight traits	kg	Hogget weight	Estimates the genetic difference between animals in liveweight at 450 days of age	Merino Genetics Services
-- 	AWT	Liveweight traits	kg	Adult weight	Estimates the genetic difference between animals in liveweight at 540 days of age	Merino Genetics Services
-- 	FAT	Carcase traits	mm	Fat Depth	Estimates the genetic difference between animals in fat depth at the GR site	Merino Genetics Services
-- 	PFAT	Carcase traits	mm	Post weaning fat depth	Estimates the genetic difference in GR fat depth at 45kg liveweight	Merino Genetics Services
-- 	YFAT	Carcase traits	mm	Yearling fat depth	Estimates the genetic difference in GR fat depth at 60kg liveweight	Merino Genetics Services
-- 	HFAT	Carcase traits	mm	Hogget fat depth	Estimates the genetic difference in GR fat depth at 70kg liveweight	Merino Genetics Services
-- 	EMD	Carcase traits	mm	Eye muscle depth	Estimates the genetic difference between animals in eye muscle depth at the C site	Merino Genetics Services
-- 	PEMD	Carcase traits	mm	Post weaning eye muscle depth	Estimates the genetic difference between animals in eye muscle depth at the C site in a 45kg liveweight animal	Merino Genetics Services
-- 	YEMD	Carcase traits	mm	Yearling eye muscle depth	Estimates the genetic difference between animals in eye muscle depth at the C site in a 60kg liveweight animal	Merino Genetics Services
-- 	HEMD	Carcase traits	mm	Hogget eye muscle depth	Estimates the genetic difference between animals in eye muscle depth at the C site in a 70kg liveweight animal	Merino Genetics Services
-- 	CWT	Carcase traits	kg	Carcase weight	Estimates the genetic difference between animals in carcase weight at 300 days of age	Merino Genetics Services
-- 	NLB	Fertility traits	%	Number of lambs born	Estimates the genetic difference between animals for number of lambs born each lambing opportunity	Merino Genetics Services
-- 	NLW	Fertility traits	%	Number of lambs weaned	Estimates the genetic difference between animals for number of lambs weaned each lambing opportunity	Merino Genetics Services
-- 	SC	Fertility traits	cm	Scrotal Circumference	Estimates the genetic difference between animals for scrotal circumference	Merino Genetics Services
-- 	YSC	Fertility traits	cm	Yearling scrotal circumference	Estimates the genetic difference between animals for scrotal circumference at 360 days of age	Merino Genetics Services
-- 	HSC	Fertility traits	cm	Hogget scrotal circumference	Estimates the genetic difference between animals for scrotal circumference at 450 days of age	Merino Genetics Services
-- 	GFW	Wool traits	kg	Greasy fleece weight	Estimates the genetic difference between animals for greasy fleece weight	Merino Genetics Services
-- 	CFW	Wool traits	kg	Clean fleece weight	Estimates the genetic difference between animals for clean fleece weight	Merino Genetics Services
-- 	YGFW	Wool traits	kg	Yearling greasy fleece weight	Estimates the genetic difference between animals for greasy fleece weight at 360 days of age	Merino Genetics Services
-- 	YCFW	Wool traits	kg	Yearling clean fleece weight	Estimates the genetic difference between animals for clean fleece weight at 360 days of age	Merino Genetics Services
-- 	HGFW	Wool traits	kg	Hogget greasy fleece weight	Estimates the genetic difference between animals for greasy fleece weight at 450 days of age	Merino Genetics Services
-- 	HCFW	Wool traits	kg	Hogget clean fleece weight	Estimates the genetic difference between animals for clean fleece weight at 450 days of age	Merino Genetics Services
-- 	AGFW	Wool traits	kg	Adult greasy fleece weight	Estimates the genetic difference between animals for greasy fleece weight at 540 days of age	Merino Genetics Services
-- 	ACFW	Wool traits	kg	Adult clean fleece weight	Estimates the genetic difference between animals for clean fleece weight at 540 days of age	Merino Genetics Services
-- 	FD	Wool traits	micron	Fibre Diameter	Estimates the genetic difference between animals for fibre diameter	Merino Genetics Services
-- 	YFD	Wool traits	micron	Yearling fibre diamter	Estimates the genetic difference between animals for fibre diameter at 360 days of age	Merino Genetics Services
-- 	HFD	Wool traits	micron	Hogget fibre diameter	Estimates the genetic difference between animals for fibre diameter at 450 days of age	Merino Genetics Services
-- 	AFD	Wool traits	micron	Adult fibre diameter	Estimates the genetic difference between animals for fibre diameter at 540 days of age	Merino Genetics Services
-- 	FDCV	Wool traits	%	Fibre Diameter coefficient of variation	Estimates the genetic difference between animals for fdcv	Merino Genetics Services
-- 	YFDCV	Wool traits	%	Yearling Fibre Diameter coefficient of variation	Estimates the genetic difference between animals for fdcv at 360 days of age	Merino Genetics Services
-- 	HFDCV	Wool traits	%	Hogget Fibre Diameter coefficient of variation	Estimates the genetic difference between animals for fdcv at 450 days of age	Merino Genetics Services
-- 	AFDCV	Wool traits	%	Adult Fibre Diameter coefficient of variation	Estimates the genetic difference between animals for fdcv at 540 days of age	Merino Genetics Services
-- 	SS	Wool traits	N/ktex	Staple Strength	Estimates the genetic difference between animals for staple strength	Merino Genetics Services
-- 	YSS	Wool traits	N/ktex	Yearling Staple Strength	Estimates the genetic difference between animals for staple strength at 360 days of age	Merino Genetics Services
-- 	HSS	Wool traits	N/ktex	Hogget Staple Strength	Estimates the genetic difference between animals for staple strength at 450 days of age	Merino Genetics Services
-- 	ASS	Wool traits	N/ktex	Adult Staple Strength	Estimates the genetic difference between animals for staple strength at 540 days of age	Merino Genetics Services
-- 	SL	Wool traits	mm	Staple Length	Estimates the genetic difference between animals for staple length	Merino Genetics Services
-- 	YSL	Wool traits	mm	Yearling Staple Length	Estimates the genetic difference between animals for staple length at 360 days of age	Merino Genetics Services
-- 	HSL	Wool traits	mm	Hogget Staple Length	Estimates the genetic difference between animals for staple length at 450 days of age	Merino Genetics Services
-- 	ASL	Wool traits	mm	Adult Staple Length	Estimates the genetic difference between animals for staple length at 540 days of age	Merino Genetics Services
-- 	FEC	Worm resistance	eggs/gram	Faecal Egg Count	Describes the value of an animal's genes for carrying worm burdens- a combination of being genetically less likely to pick up worms and being able to cope immunologically with the worm burden	Merino Genetics Services
-- 	WFEC	Worm resistance	eggs/gram	Weaning Faecal Egg Count	Estimates the genetic difference between animals in worm burden at 100 days of age	Merino Genetics Services
-- 	PWFEC	Worm resistance	eggs/gram	Post Weaning Faecal Egg Count	Estimates the genetic difference between animals in worm burden at 225 days of age	Merino Genetics Services
-- 	YFEC	Worm resistance	eggs/gram	Yearling Faecal Egg Count	Estimates the genetic difference between animals in worm burden at 360 days of age	Merino Genetics Services
-- 	END
-- 	`letCode` : The code for the EBV
-- 	`letDescription` : The Description of the EBV
-- 	`letDefinition` : The definition for the EBV
-- 	`letReference` : The reference source for the EBV definition
-- 	`letUnit` : The measurement unit for the EBV value
-- 	`letGrouping` : The grouping that the EBV is classified in  
create table `LKEBVType` ( 
	`letCode` VARCHAR(10),
	`letDescription` VARCHAR(250),
	`letDefinition` VARCHAR(250),
	`letReference` VARCHAR(250),
	`letUnit` VARCHAR(15),
	`letGrouping` VARCHAR(50), constraint `LKEBVType_PK` primary key (`letCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKEBVType`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKEBVType' :
-- 	`letCode`
-- 	`letDescription`


-- Create new table `AnimalEBV`.
-- `AnimalEBV` : Table of AnimalEBV
-- 	Namespace: Genetics
-- 	Stores the Estimated Breeding Value (EBV) of an animal trait.
-- 	`abvPrimaryId` : abvPrimaryId identifies AnimalEBV
-- 	`abvValue` : The value of the EBV
-- 	`abvDate` : The date the EBV was calculated
-- 	`abvAccuracy` : The accuracy value of the EBV as a percenatge
-- 	`abvBvaPrimaryId` : links to EBV analysis group
-- 	`abvLetCode` : abvLetCode partly identifies AnimalEBV
-- 	`abvAmlPrimaryId` : The primary ID of the animal for that EBV value  
create table `AnimalEBV` ( 
	`abvPrimaryId` CHAR(15),
	`abvValue` LONG,
	`abvDate` DATETIME,
	`abvAccuracy` LONG,
	`abvBvaPrimaryId` INTEGER,
	`abvLetCode` VARCHAR(10),
	`abvAmlPrimaryId` CHAR(15), constraint `AnimalEBV_PK` primary key (`abvPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`AnimalEBV`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'AnimalEBV' :
-- 	`abvPrimaryId`
-- 	`abvValue`
-- 	`abvDate`
-- 	`abvAccuracy`
-- 	`abvBvaPrimaryId`
-- 	`abvLetCode`


-- Create new table `EBVAnalysis`.
-- `EBVAnalysis` : Table of EBVAnalysis
-- 	Namespace: Genetics
-- 	When the analysis was done to generate the EBVs. Groups together all EBVs generated from an analysis.
-- 	`bvaPrimaryId` : bvaPrimaryId identifies EBVAnalysis
-- 	`bvaDescription` : Description of the group of animals for the  EBV analysis. For example, EBV analysis for the 2002 drop rams yearling test
-- 	`bvaDate` : Date when EBV analysis was performed  
create table `EBVAnalysis` ( 
	`bvaPrimaryId` INTEGER,
	`bvaDescription` VARCHAR(10),
	`bvaDate` DATETIME, constraint `EBVAnalysis_PK` primary key (`bvaPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`EBVAnalysis`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'EBVAnalysis' :
-- 	`bvaPrimaryId`
-- 	`bvaDescription`
-- 	`bvaDate`


-- Create new table `GeneticIndexAnalysis`.
-- `GeneticIndexAnalysis` : Table of GeneticIndexAnalysis
-- 	Namespace: Genetics
-- 	When the analysis was done to generate the EBVs. Groups together all EBVs generated from an analysis.
-- 	`giaPrimaryId` : giaPrimaryId identifies GeneticIndexAnalysis
-- 	`giaDescription` : Description of the group of animals for the genetic index analysis. For example, genetic index values for the 2002 drop rams yearling test
-- 	`giaDate` : Date when genetic index analysis was performed  
create table `GeneticIndexAnalysis` ( 
	`giaPrimaryId` INTEGER,
	`giaDescription` VARCHAR(250),
	`giaDate` DATETIME, constraint `GeneticIndexAnalysis_PK` primary key (`giaPrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`GeneticIndexAnalysis`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'GeneticIndexAnalysis' :
-- 	`giaPrimaryId`
-- 	`giaDescription`
-- 	`giaDate`


-- Create new table `MeasurementEventEnvironment`.
-- `MeasurementEventEnvironment` : Table of MeasurementEventEnvironment
-- 	Namespace: All
-- 	Records the values for the "environment" type measuement events
-- 	`meePrimaryId` : meePrimaryId identifies MeasurementEventEnvironment
-- 	`meeValue` : The measurement value for the environment of the measurement event
-- 	`mevPrimaryId` : mevPrimaryId is of MeasurementEventEnvironment
-- 	`meeLemCode` : The code for environment measure (see LKEnvironmentMeasure)  
create table `MeasurementEventEnvironment` ( 
	`meePrimaryId` INTEGER,
	`meeValue` CHAR(10),
	`mevPrimaryId` INTEGER,
	`meeLemCode` CHAR(15), constraint `MeasurementEventEnvironment_PK` primary key (`meePrimaryId`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`MeasurementEventEnvironment`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'MeasurementEventEnvironment' :
-- 	`meePrimaryId`


-- Create new table `LKEnvironmentMeasure`.
-- `LKEnvironmentMeasure` : Lookup Table  LKEnvironmentMeasure
-- 	Namespace: Animal, Genetics
-- 	START
-- 	lemCode	lemDescription
-- 	MACHINEID	The ID of the machine used for the measurement
-- 	OPERATORID	The ID of the operator of the machine used for the measurement
-- 	TEMP	The temperature at the time the measurement was taken 
-- 	HUMIDITY	The humidity at the time the measurement was taken 
-- 	GREASECF	The grease correction factor used in OFDA fleece testing
-- 	HUMIDITYCF	The humidity correction factor used in OFDA fleece testing
-- 	FLCSAMPOFDA	OFDA machine used to take measurements from the fleece sample
-- 	FLCSAMPLASER	Laserscan machine used to take measurements from the fleece sample
-- 	FLCSAMPMID	Fleece sample for testing taken from the midside location on the sheep
-- 	FLCSAMPPIN	Fleece sample for testing taken from the pinbone(hip) location on the sheep
-- 	WHOLEFLC	Whole fleece from the sheep used for testing
-- 	END
-- 	`lemCode` : Code to identify the environment measure
-- 	`lemDescription` : Description of the environment measure  
create table `LKEnvironmentMeasure` ( 
	`lemCode` CHAR(15),
	`lemDescription` VARCHAR(100), constraint `LKEnvironmentMeasure_PK` primary key (`lemCode`) ); 

-- Note: Access does not support the mandatory (not null) constraint in SQL DDL.
-- The mandatory constraints for table '`LKEnvironmentMeasure`' will be created in the database using DAO
-- only if there is a connection to the Access MDB file during DDL generation.
-- Not nullable columns for table 'LKEnvironmentMeasure' :
-- 	`lemCode`
-- 	`lemDescription`


-- Add foreign key constraints to table `MobHistory`.
alter table `MobHistory`
	add constraint `Mob_MobHistory_FK1` foreign key (
		`mhyMobPrimaryId`)
	 references `Mob` (
		`mobPrimaryId`); 

alter table `MobHistory`
	add constraint `Animal_MobHistory_FK1` foreign key (
		`mhyAmlPrimaryId`)
	 references `Animal` (
		`amlPrimaryId`); 

alter table `MobHistory`
	add constraint `Paddock_MobHistory_FK1` foreign key (
		`mhyPdkPrimaryId`)
	 references `Paddock` (
		`pdkPrimaryId`); 

-- Add foreign key constraints to table `GeneticManagementGroupHistory`.
alter table `GeneticManagementGroupHistory`
	add constraint `Animal_GeneticManagementGroupHistory_FK1` foreign key (
		`gmgAmlPrimaryId`)
	 references `Animal` (
		`amlPrimaryId`); 

alter table `GeneticManagementGroupHistory`
	add constraint `LKGeneticManagementGroup_GeneticManagementGroupHistory_FK1` foreign key (
		`gmgLgmCode`)
	 references `LKGeneticManagementGroup` (
		`lgmCode`); 

-- Add foreign key constraints to table `Breed`.
alter table `Breed`
	add constraint `LKBreedType_Breed_FK1` foreign key (
		`brdLbyCode`)
	 references `LKBreedType` (
		`lbyCode`); 

alter table `Breed`
	add constraint `Animal_Breed_FK1` foreign key (
		`brdAmlPrimaryId`)
	 references `Animal` (
		`amlPrimaryId`); 

-- Add foreign key constraints to table `AnimalGeneticIndex`.
alter table `AnimalGeneticIndex`
	add constraint `LKGeneticIndexType_AnimalGeneticIndex_FK1` foreign key (
		`gixLgiCode`)
	 references `LKGeneticIndexType` (
		`lgiCode`); 

alter table `AnimalGeneticIndex`
	add constraint `Animal_AnimalGeneticIndex_FK1` foreign key (
		`gixAmlPrimaryId`)
	 references `Animal` (
		`amlPrimaryId`); 

alter table `AnimalGeneticIndex`
	add constraint `GeneticIndexAnalysis_AnimalGeneticIndex_FK1` foreign key (
		`gixGiaPrimaryId`)
	 references `GeneticIndexAnalysis` (
		`giaPrimaryId`); 

-- Add foreign key constraints to table `PaddockFertilizerHistory`.
alter table `PaddockFertilizerHistory`
	add constraint `Paddock_PaddockFertilizerHistory_FK1` foreign key (
		`pfhPdkPrimaryId`)
	 references `Paddock` (
		`pdkPrimaryId`); 

alter table `PaddockFertilizerHistory`
	add constraint `LKMeasurementUnit_PaddockFertilizerHistory_FK1` foreign key (
		`pfhLmuCode`)
	 references `LKMeasurementUnit` (
		`lmuCode`); 

alter table `PaddockFertilizerHistory`
	add constraint `LKPaddockFertilizerType_PaddockFertilizerHistory_FK1` foreign key (
		`pfhLftCode`)
	 references `LKPaddockFertilizerType` (
		`lftCode`); 

-- Add foreign key constraints to table `PaddockTreatmentHistory`.
alter table `PaddockTreatmentHistory`
	add constraint `Paddock_PaddockTreatmentHistory_FK1` foreign key (
		`pthPdkPrimaryId`)
	 references `Paddock` (
		`pdkPrimaryId`); 

alter table `PaddockTreatmentHistory`
	add constraint `LKTreatmentType_PaddockTreatmentHistory_FK1` foreign key (
		`pthLptCode`)
	 references `LKTreatmentType` (
		`ltyCode`); 

alter table `PaddockTreatmentHistory`
	add constraint `LKMeasurementUnit_PaddockTreatmentHistory_FK1` foreign key (
		`pthLmuCode`)
	 references `LKMeasurementUnit` (
		`lmuCode`); 

alter table `PaddockTreatmentHistory`
	add constraint `LKPaddcokApplicationType_PaddockTreatmentHistory_FK1` foreign key (
		`pthLpaCode`)
	 references `LKPaddcokApplicationType` (
		`lpaCode`); 

-- Add foreign key constraints to table `AnimalEventHistory`.
alter table `AnimalEventHistory`
	add constraint `Animal_AnimalEventHistory_FK1` foreign key (
		`aevAmlPrimaryId`)
	 references `Animal` (
		`amlPrimaryId`); 

alter table `AnimalEventHistory`
	add constraint `LKAnimalEventType_AnimalEventHistory_FK1` foreign key (
		`aevLaeCode`)
	 references `LKAnimalEventType` (
		`laeCode`); 

-- Add foreign key constraints to table `ContactBankAccount`.
alter table `ContactBankAccount`
	add constraint `Contact_ContactBankAccount_FK1` foreign key (
		`cbaCntPrimaryId`)
	 references `Contact` (
		`cntPrimaryId`); 

-- Add foreign key constraints to table `Mating`.
alter table `Mating`
	add constraint `LKConceptionMethod_Mating_FK1` foreign key (
		`mtgLcmCode`)
	 references `LKConceptionMethod` (
		`lcmCode`); 

alter table `Mating`
	add constraint `Animal_Mating_FK1` foreign key (
		`mtgBackupSireId`)
	 references `Animal` (
		`amlPrimaryId`); 

alter table `Mating`
	add constraint `Animal_Mating_FK2` foreign key (
		`mtgDamId`)
	 references `Animal` (
		`amlPrimaryId`); 

alter table `Mating`
	add constraint `Animal_Mating_FK3` foreign key (
		`mtgAmlPrimaryId`)
	 references `Animal` (
		`amlPrimaryId`); 

-- Add foreign key constraints to table `Measurement`.
alter table `Measurement`
	add constraint `LKMeasurementType_Measurement_FK1` foreign key (
		`msrLmtCode`)
	 references `LKMeasurementType` (
		`lmtCode`); 

alter table `Measurement`
	add constraint `LKSheepAgeCode_Measurement_FK1` foreign key (
		`msrLsaCode`)
	 references `LKSheepAgeCode` (
		`lsaCode`); 

alter table `Measurement`
	add constraint `LKGeneticManagementGroup_Measurement_FK1` foreign key (
		`msrLgmCode`)
	 references `LKGeneticManagementGroup` (
		`lgmCode`); 

alter table `Measurement`
	add constraint `MeasurementEvent_Measurement_FK1` foreign key (
		`msrMevPrimaryId`)
	 references `MeasurementEvent` (
		`mevPrimaryId`); 

alter table `Measurement`
	add constraint `Animal_Measurement_FK1` foreign key (
		`msrAmlPrimaryId`)
	 references `Animal` (
		`amlPrimaryId`); 

-- Add foreign key constraints to table `PaddockEventHistory`.
alter table `PaddockEventHistory`
	add constraint `Paddock_PaddockEventHistory_FK1` foreign key (
		`pehPdkPrimaryId`)
	 references `Paddock` (
		`pdkPrimaryId`); 

alter table `PaddockEventHistory`
	add constraint `LKPaddockEvent_PaddockEventHistory_FK1` foreign key (
		`pehLpeCode`)
	 references `LKPaddockEvent` (
		`lpeCode`); 

-- Add foreign key constraints to table `AnimalTreatmentHistory`.
alter table `AnimalTreatmentHistory`
	add constraint `Animal_AnimalTreatmentHistory_FK1` foreign key (
		`athAmlPrimaryId`)
	 references `Animal` (
		`amlPrimaryId`); 

alter table `AnimalTreatmentHistory`
	add constraint `LKMeasurementUnit_AnimalTreatmentHistory_FK1` foreign key (
		`athLmuCode`)
	 references `LKMeasurementUnit` (
		`lmuCode`); 

alter table `AnimalTreatmentHistory`
	add constraint `LKSheepDisease_AnimalTreatmentHistory_FK1` foreign key (
		`athLsdCode`)
	 references `LKSheepDisease` (
		`lsdCode`); 

alter table `AnimalTreatmentHistory`
	add constraint `LKAnimalChemicalType_AnimalTreatmentHistory_FK1` foreign key (
		`athLatCode`)
	 references `LKAnimalChemicalType` (
		`latCode`); 

-- Add foreign key constraints to table `Line`.
alter table `Line`
	add constraint `Clip_Line_FK1` foreign key (
		`ineClpPrimaryId`)
	 references `Clip` (
		`clpPrimaryId`); 

alter table `Line`
	add constraint `LKLineType_Line_FK1` foreign key (
		`ineLltCode`)
	 references `LKLineType` (
		`lltCode`); 

-- Add foreign key constraints to table `Clip`.
alter table `Clip`
	add constraint `LKClipType_Clip_FK1` foreign key (
		`clpLcpCode`)
	 references `LKClipType` (
		`lcpCode`); 

alter table `Clip`
	add constraint `LKOfferingInstruction_Clip_FK1` foreign key (
		`clpLoiCode`)
	 references `LKOfferingInstruction` (
		`loiCode`); 

alter table `Clip`
	add constraint `LKDeliveryCentre_Clip_FK1` foreign key (
		`clpLdcCode`)
	 references `LKDeliveryCentre` (
		`ldcCode`); 

alter table `Clip`
	add constraint `LKWoolSaleCentre_Clip_FK1` foreign key (
		`clpLwsCode`)
	 references `LKWoolSaleCentre` (
		`lwsCode`); 

alter table `Clip`
	add constraint `Contact_Clip_FK1` foreign key (
		`clpCntPrimaryId`)
	 references `Contact` (
		`cntPrimaryId`); 

-- Add foreign key constraints to table `Bale`.
alter table `Bale`
	add constraint `Property_Bale_FK1` foreign key (
		`blePtyPrimaryId`)
	 references `Property` (
		`ptyPrimaryId`); 

alter table `Bale`
	add constraint `MobClip_Bale_FK1` foreign key (
		`bleMcpPrimaryId`)
	 references `MobClip` (
		`mcpPrimaryId`); 

alter table `Bale`
	add constraint `LKWoolPackType_Bale_FK1` foreign key (
		`bleLwpCode`)
	 references `LKWoolPackType` (
		`lwpCode`); 

alter table `Bale`
	add constraint `LKPackTare_Bale_FK1` foreign key (
		`bleLptCode`)
	 references `LKPackTare` (
		`lptCode`); 

alter table `Bale`
	add constraint `Line_Bale_FK1` foreign key (
		`bleInePrimaryId`)
	 references `Line` (
		`inePrimaryId`); 

-- Add foreign key constraints to table `FleeceClassing`.
alter table `FleeceClassing`
	add constraint `Animal_FleeceClassing_FK1` foreign key (
		`flcAmlPrimaryId`)
	 references `Animal` (
		`amlPrimaryId`); 

alter table `FleeceClassing`
	add constraint `Line_FleeceClassing_FK1` foreign key (
		`flcInePrimaryId`)
	 references `Line` (
		`inePrimaryId`); 

-- Add foreign key constraints to table `Animal`.
alter table `Animal`
	add constraint `Mob_Animal_FK1` foreign key (
		`amlMobPrimaryId`)
	 references `Mob` (
		`mobPrimaryId`); 

alter table `Animal`
	add constraint `Paddock_Animal_FK1` foreign key (
		`amlPdkPrimaryId`)
	 references `Paddock` (
		`pdkPrimaryId`); 

alter table `Animal`
	add constraint `LKAnimalType_Animal_FK1` foreign key (
		`amlLayTypeCode`)
	 references `LKAnimalType` (
		`layCode`); 

alter table `Animal`
	add constraint `LKRearType_Animal_FK1` foreign key (
		`amlLrtTypeCode`)
	 references `LKRearType` (
		`lrtCode`); 

alter table `Animal`
	add constraint `LKSexCode_Animal_FK1` foreign key (
		`amlLscCode`)
	 references `LKSexCode` (
		`lscCode`); 

alter table `Animal`
	add constraint `LKBirthTypeCode_Animal_FK1` foreign key (
		`amlLbtCode`)
	 references `LKBirthTypeCode` (
		`lbtCode`); 

alter table `Animal`
	add constraint `LKAnimalStatus_Animal_FK1` foreign key (
		`amlLasCode`)
	 references `LKAnimalStatus` (
		`lasCode`); 

alter table `Animal`
	add constraint `LKTagColor_Animal_FK1` foreign key (
		`amlLtgCode`)
	 references `LKTagColor` (
		`ltgCode`); 

alter table `Animal`
	add constraint `Contact_Animal_FK1` foreign key (
		`amlCntVendorAgent`)
	 references `Contact` (
		`cntPrimaryId`); 

alter table `Animal`
	add constraint `AnimalComment_Animal_FK1` foreign key (
		`amlActPrimaryId`)
	 references `AnimalComment` (
		`actPrimaryId`); 

alter table `Animal`
	add constraint `Contact_Animal_FK2` foreign key (
		`amlCntVendor`)
	 references `Contact` (
		`cntPrimaryId`); 

alter table `Animal`
	add constraint `Flock_Animal_FK1` foreign key (
		`amlFlockPrimaryId`)
	 references `Flock` (
		`flkPrimaryId`); 

-- Add foreign key constraints to table `Mob`.
alter table `Mob`
	add constraint `Property_Mob_FK1` foreign key (
		`mobPtyPrimaryId`)
	 references `Property` (
		`ptyPrimaryId`); 

alter table `Mob`
	add constraint `Mob_Mob_FK1` foreign key (
		`mobParentMobPrimaryId`)
	 references `Mob` (
		`mobPrimaryId`); 

-- Add foreign key constraints to table `Paddock`.
alter table `Paddock`
	add constraint `Property_Paddock_FK1` foreign key (
		`pdkPtyPrimaryId`)
	 references `Property` (
		`ptyPrimaryId`); 

alter table `Paddock`
	add constraint `LKPastureType_Paddock_FK1` foreign key (
		`pdkLptCode`)
	 references `LKPastureType` (
		`lptCode`); 

-- Add foreign key constraints to table `Property`.
alter table `Property`
	add constraint `Flock_Property_FK1` foreign key (
		`ptyFlockPrimaryId`)
	 references `Flock` (
		`flkPrimaryId`); 

-- Add foreign key constraints to table `Contact`.
alter table `Contact`
	add constraint `LKContactType_Contact_FK1` foreign key (
		`cntLcyCode`)
	 references `LKContactType` (
		`lcyCode`); 

alter table `Contact`
	add constraint `LKContactTitle_Contact_FK1` foreign key (
		`cntLctCode`)
	 references `LKContactTitle` (
		`lctCode`); 

alter table `Contact`
	add constraint `LKCountryCode_Contact_FK1` foreign key (
		`cntLccCode`)
	 references `LKCountryCode` (
		`lccCode`); 

alter table `Contact`
	add constraint `LKState_Contact_FK1` foreign key (
		`cntLstCode`)
	 references `LKState` (
		`lstCode`); 

alter table `Contact`
	add constraint `LKPostCode_Contact_FK1` foreign key (
		`cntLpcCode`)
	 references `LKPostCode` (
		`lpcCode`); 

alter table `Contact`
	add constraint `LKPostCode_Contact_FK2` foreign key (
		`cntLpcPostalAdressCode`)
	 references `LKPostCode` (
		`lpcCode`); 

alter table `Contact`
	add constraint `LKCountryCode_Contact_FK2` foreign key (
		`cntLccPostalAddressCountryCode`)
	 references `LKCountryCode` (
		`lccCode`); 

alter table `Contact`
	add constraint `LKState_Contact_FK2` foreign key (
		`cntLstPostalAddressCode`)
	 references `LKState` (
		`lstCode`); 

-- Add foreign key constraints to table `MobClip`.
alter table `MobClip`
	add constraint `LKWoolResidualRisk_MobClip_FK1` foreign key (
		`mcpLwrCode`)
	 references `LKWoolResidualRisk` (
		`lwrCode`); 

alter table `MobClip`
	add constraint `LKWoolQuality_MobClip_FK1` foreign key (
		`mcpLwqCode`)
	 references `LKWoolQuality` (
		`lwqCode`); 

alter table `MobClip`
	add constraint `LKSexCode_MobClip_FK1` foreign key (
		`mcpLscCode`)
	 references `LKSexCode` (
		`lscCode`); 

alter table `MobClip`
	add constraint `LKAge_MobClip_FK1` foreign key (
		`mcpLaeCode`)
	 references `LKAge` (
		`laeCode`); 

alter table `MobClip`
	add constraint `Mob_MobClip_FK1` foreign key (
		`mcpMobPrimaryId`)
	 references `Mob` (
		`mobPrimaryId`); 

alter table `MobClip`
	add constraint `Clip_MobClip_FK1` foreign key (
		`mcpClpPrimaryId`)
	 references `Clip` (
		`clpPrimaryId`); 

alter table `MobClip`
	add constraint `LKBurrSeed_MobClip_FK1` foreign key (
		`mcpLbsCode`)
	 references `LKBurrSeed` (
		`lbsCode`); 

-- Add foreign key constraints to table `Consignment`.
alter table `Consignment`
	add constraint `Animal_Consignment_FK1` foreign key (
		`cstAmlPrimaryId`)
	 references `Animal` (
		`amlPrimaryId`); 

alter table `Consignment`
	add constraint `LKSaleType_Consignment_FK1` foreign key (
		`cstLstCode`)
	 references `LKSaleType` (
		`lstCode`); 

alter table `Consignment`
	add constraint `Contact_Consignment_FK1` foreign key (
		`cstCntTransportCo`)
	 references `Contact` (
		`cntPrimaryId`); 

alter table `Consignment`
	add constraint `LKLivestockSaleCentre_Consignment_FK1` foreign key (
		`cstLscCode`)
	 references `LKLivestockSaleCentre` (
		`lscCode`); 

alter table `Consignment`
	add constraint `Contact_Consignment_FK2` foreign key (
		`cstCntName`)
	 references `Contact` (
		`cntPrimaryId`); 

-- Add foreign key constraints to table `SeedBurrContaminationStatus`.
alter table `SeedBurrContaminationStatus`
	add constraint `LKBurrType_SeedBurrContaminationStatus_FK1` foreign key (
		`sbsLbtType`)
	 references `LKBurrType` (
		`lbtType`); 

alter table `SeedBurrContaminationStatus`
	add constraint `LKSeedType_SeedBurrContaminationStatus_FK1` foreign key (
		`sbsLstType`)
	 references `LKSeedType` (
		`lstType`); 

alter table `SeedBurrContaminationStatus`
	add constraint `LKSeedBurrContaminationStatus_SeedBurrContaminationStatus_FK1` foreign key (
		`sbsLscStatusCode`)
	 references `LKSeedBurrContaminationStatus` (
		`lscStatusCode`); 

alter table `SeedBurrContaminationStatus`
	add constraint `LKDustContaminationStatus_SeedBurrContaminationStatus_FK1` foreign key (
		`sbsLdcStatus`)
	 references `LKDustContaminationStatus` (
		`ldcStatus`); 

alter table `SeedBurrContaminationStatus`
	add constraint `SeedBurrContamination_SeedBurrContaminationStatus_FK1` foreign key (
		`sbcPrimaryId`)
	 references `SeedBurrContamination` (
		`sbcPrimaryId`); 

-- Add foreign key constraints to table `SeedBurrContamination`.
alter table `SeedBurrContamination`
	add constraint `Mob_SeedBurrContamination_FK1` foreign key (
		`sbcMobPrimaryId`)
	 references `Mob` (
		`mobPrimaryId`); 

-- Add foreign key constraints to table `ContactToProperty`.
alter table `ContactToProperty`
	add constraint `Property_ContactToProperty_FK1` foreign key (
		`ptyPrimaryId`)
	 references `Property` (
		`ptyPrimaryId`); 

alter table `ContactToProperty`
	add constraint `Contact_ContactToProperty_FK1` foreign key (
		`cntPrimaryId`)
	 references `Contact` (
		`cntPrimaryId`); 

-- Add foreign key constraints to table `PropertyContamination`.
alter table `PropertyContamination`
	add constraint `Property_PropertyContamination_FK1` foreign key (
		`pycPtyPrimaryId`)
	 references `Property` (
		`ptyPrimaryId`); 

-- Add foreign key constraints to table `GrainFodderTreatment`.
alter table `GrainFodderTreatment`
	add constraint `LKGrainType_GrainFodderTreatment_FK1` foreign key (
		`gftLgrCode`)
	 references `LKGrainType` (
		`lgrCode`); 

alter table `GrainFodderTreatment`
	add constraint `LKTreatmentType_GrainFodderTreatment_FK1` foreign key (
		`gftLtyCode`)
	 references `LKTreatmentType` (
		`ltyCode`); 

alter table `GrainFodderTreatment`
	add constraint `LKFeedType_GrainFodderTreatment_FK1` foreign key (
		`gftLftCode`)
	 references `LKFeedType` (
		`lftCode`); 

-- Add foreign key constraints to table `FeedingHistory`.
alter table `FeedingHistory`
	add constraint `Mob_FeedingHistory_FK1` foreign key (
		`fhyMobPrimaryId`)
	 references `Mob` (
		`mobPrimaryId`); 

alter table `FeedingHistory`
	add constraint `LKFeedType_FeedingHistory_FK1` foreign key (
		`fhyFtyCode`)
	 references `LKFeedType` (
		`lftCode`); 

-- Add foreign key constraints to table `LKTraitTypeScore`.
alter table `LKTraitTypeScore`
	add constraint `LKMeasurementType_LKTraitTypeScore_FK1` foreign key (
		`ltslmtCode`)
	 references `LKMeasurementType` (
		`lmtCode`); 

-- Add foreign key constraints to table `AnimalEBV`.
alter table `AnimalEBV`
	add constraint `EBVAnalysis_AnimalEBV_FK1` foreign key (
		`abvBvaPrimaryId`)
	 references `EBVAnalysis` (
		`bvaPrimaryId`); 

alter table `AnimalEBV`
	add constraint `LKEBVType_AnimalEBV_FK1` foreign key (
		`abvLetCode`)
	 references `LKEBVType` (
		`letCode`); 

alter table `AnimalEBV`
	add constraint `Animal_AnimalEBV_FK1` foreign key (
		`abvAmlPrimaryId`)
	 references `Animal` (
		`amlPrimaryId`); 

-- Add foreign key constraints to table `MeasurementEventEnvironment`.
alter table `MeasurementEventEnvironment`
	add constraint `MeasurementEvent_MeasurementEventEnvironment_FK1` foreign key (
		`mevPrimaryId`)
	 references `MeasurementEvent` (
		`mevPrimaryId`); 

alter table `MeasurementEventEnvironment`
	add constraint `LKEnvironmentMeasure_MeasurementEventEnvironment_FK1` foreign key (
		`meeLemCode`)
	 references `LKEnvironmentMeasure` (
		`lemCode`); 


-- This is the end of the Microsoft Visual Studio generated SQL DDL script.