Data Model

This page describes how we model data from each form from the MPP project. Each form will be treated in a subsection. Loci forms will be treated together. If you haven't looked at the Database diagram, see: MPP Method Database Diagram v2.pdf. This diagram should give you most of the information that you need!

Synchronization Technique

We use ArchaeologyProject/MergeReplication (see the link to set this up) to synchronize SQL Server Express 2008 to SQL Server 2008. For database wide unique fields like locusIdentity or supplementID, we use Guid values. When you setup ArchaeologyProject/MergeReplication, the database automatically adds its own rowguid column to facilitate merges during the replication. There are a couple of gotcha's that we had:

1. Setting the default to new GUID() in the database doesn't do a thing for you when you are programming. You MUST supply your own Guid with Guid.newGuid().
2. The dataset SHOULD NOT have the rowguid column added by the database for replication. From the programmers standpoint, it doesn't even exist and the dataset shouldn't know about it either!

Loci Forms

Several pieces of each loci form are identical across the three types.

1. Earth Loci (Color: yellow - most common)

2. Architectural Loci (Color: pink)

3. Installation Loci (Color: purple)

Burials (Color: Brown) are not strictly a loci since they do not get a locus number, but are attached to an earth locus. THEREFORE, burial forms are child entities of an earth locus (technically - but see notes on Burial supplements below.)

Identical Sections include the identity, rationale, stratigraphy, levels, Pottery, Objects, Photographs, Biodata samples, Drawings and Interpretation.

Although there exists Locus continuation data sheets, we don't create continuation entities for obvious reasons. Therefore, when printing the data from the program or showing the data in the program, there are never any continuation locus sheets.

Earth Locus

The earth locus sheet is the most common form used. Technically everything under 1. should be identifying (Key). However this is preposterous and we will assign a surrogate key.

• Section	Element Identifier	Element Type	Default	Description
  0.None
  	Cleanup	Boolean	False	Check only if debris is interseasonal erosion and if there are pottery, bones, and other objects that need listing
  	Balk Removal	Boolean	False	Check only if you excavated a locus during the balk removal and you don't have the original locus sheet
  1.Identification
  	A.Locus	Integer	None	IF there exists true in section 0. This is a Cleanup or Removal Excavation NOTE DEV. from Manual due to database constraints.
  	B.Site	Char	None	Single Char identifier of site
  	C.Season	Integer	Year(today)	Convention is last two digits of year $\rightarrow$ Bah make it four digits
  	D.Field	Char	None	Single letter field designation
  	E.Square	varchar(8)	None	Sample: "6L46" or "A6" seems random.
  	F.Start_Date	Date	Today()	Sample: 18 Jul 2009 [dd/mm/yyyy]
  	F.End_Date	Date	NULL	same.
  	G.Sheet	Integer	NULL	NOT USED!} When we print out sheets they will have page numbers
  	H.Supervisor	varchar(25)[]	NOT NULL	If you worked on this sheet add your name to the list!
  	I.BALK	char(1)	NULL	Used for balk removal (seldom used)
  	J.Designation	varchar(80)	NULL	Free form description of the layer
  2.Rational
  	A.Reason	varchar(80)	NULL	Free form reason for change (Color change,...)
  	B.Separability.Top	selection	Average	Selection from Required Entity Separability_Listing(ID,{Very Clear, Clear, Average, Unclear, Very Unclear, Arbitrary})
  	B.Separability.Bot	selection	Average	Bottom of locus is from Separability Entity
  3.Description				Description is significantly different across loci forms
  	A.Color.1.Munsell_Number	varchar(10)	NULL
  	A.Color.2.Verbal	varchar(50)	NULL
  	B.Texture.1-4	Mutli-dep	NONE	Intersection table with Texture Required Entity Texture(Texture_ID,Texture_Description)
  	C.Particle_Shape.1-4	Multi-dep	NONE	Association table with Required Entity Particle_Shape(Particle_Shape_ID,Particle_Description:{Angular, Subangular, Subround, Round) includes percentage
  	D.1.Consistence.Hardness	int	3	values 1-6
  	D.2.Consistence.Compactness	int	0	Use list Entity Earth_Compactness(Compactness_ID, Compactness_Description) {Loose, Crumbly, Friable, Firm, Gravelly, Rubbly}X{Slightly, Moderately, Very}
  	D.3.Consistence.Wetness	int	0	Use list Entry Earth_Wetness(Wetness_ID, Wetness_Description) {Dry, Moist, Wet}X{Slightly, Moderately, Very}
  	D.4.Consistence.Structure	int	0	Use list Entry Earth_Structure(Structure_ID, Structure_Description) {Puddling water, Channeling water, Sheet wash water, Wind, Talus, Random}
  	E.1.a-d.Stone_Inclusion	asc		Each inclusion has a distribution associated with it now (a bit more data) Required Entity Inclusion(InclusionTypeID, Description, Class) Class:{Stone, Earth, Artifact, Organic}
  	E.2.a-f.Earth_Inclusion	asc		Use association table Inclusion include Distribution={Random, Patterned+char, Layered+char}
  	E.3.a-k.Artifact_Inclusion	asc		Use association table Inclusion include: Frequency, Distribution, Dist_Remark
  	E.4.a-f.Organic_Inclusion	asc		Use association table inclusion include: Frequency, Distribution
  	F.Measurements	child		Children will have Locus Identity in Required Entity Locus_Points(OBJECTID, SHAPE, File_Name_Imported, Locus_Identity) Note: OBJECTID and SHAPE are maintained by ArcINFO and we don't mess with them. In this case, it's the key.
  	G.Surface_Material.1-8	asc		Use association table with Required Entity Surface_Material(Surface_Material_Type_ID, Surface_Material_Description)
  4.Stratigraphy.Shape		child		Required Entity Locus_Points(OBJECTID, SHAPE, File_Name_Imported,Locus_Identity
  4.Stratigraphy.A-G.Relation		asc		Use association table Stratigraphy_Relation(This_Locus_Identity, Related_Locus_Idenity, Relationship:{Under, Over, Equals, Contiguous to, Seals Against, Cut by, Over Foundation}, remarks)
  5.Levels				Spatial Object Query - We now collect all spatial information with the Total Station. Therefore this section now is just a reference to the coordinates take at different times. Use Locus_Points Table
  6.Identification				Printed header on subsequent pages - not recorded!
  7.Pottery	child			Requires Entity: Locus_Pottery_Bucket(Locus_Identity, Pail_ID, Date, Total_Debris_Baskets, Location, Comments) see figure on p.26
  8.Bones	child			Locus_Bone_Bucket Requires Entity: identical to Locus_Pottery_Bucket
  9.Seeds	child			Requires Entity: Seeds(Locus_Identity, Pail_ID, Date, Sample#, Location, Reading)
  10.Objects				Similar to Seeds model - Requires Entity: Object(Pail\#,Date,Field\#,Spatial Coordinates Set\#,Reading) : Associated with pictures,loci...
  11.Photographs				List of photo numbers model - intersection table.
  12.Biodata Samples	Bio_Data_Samples	varchar(80)	Pollen	PROGRAMMING: list elements to check, allow additional information to be typed. The combination goes here.
  13.Drawings		Intersection Table	NULL	Requires Entity: Drawing(ID,Image,...)
  14.Interpretation
  	A.Fucntion	varchar(500)	NULL	What was this loci used for in antiquity
  	B.Stratigraphy	varchar(500)	NULL	This is a textual check on the data that we collect with the total station
  	C.Clean_Locus	boolean	false	Did this layer produce pottery from one period only with no mixing of ceramic horizons? Yes$\rightarrow$ true
  	D.Locus_Period_Date	varchar(50)	NULL	Excavators dating of the layer
  	E.Phase	varchar(25)	NULL	Completed later ...
  	F.Stratum	varchar()	NULL	Completed later ...

Architectural Locus

There is an inconsistancy in MPP that allows an architectural locus to be a subsidiary locus to some other architectural locus. This creates a locus sheet with an identical locus number but a different "locus" making a locus or (sublocus) the most basic element in archaeology. We fix this inconsistancy by moving the J. FOUND(ation) and K.Phase out of the identification and put it into the stratigraphy relationship section. A wall locus then has a architectural-foundation relationship with a foundation instead of having a sub-locus. This makes a substantial change in the Handbook on how to use the forms.

HANDBOOK CHANGE: 15.J and 15.K should list the related locus and relationship type and phase number respectively. The locus gets a unique number.

Identification, Rationale = Earth Locus

Notes:

1. Choose {Limestone, Chert, Basalt, Nari, Mudbrick (E), [Allow Additional]}
2. Choose {None, Hard, Soft, Cherty, Fossiliferous, Decayed, Freshly quarried, reused, Oven-backed, Sun-backed, Unbacked, Burned, [Allow Additional]}

3. Choose {Cobble [6-25 cm), Sm Boulder [25-50 cm), Md Boulder [50-75 cm), Lg Boulder[75-100 cm), Vlg Boulder (>=1 m)}

4. Choose {Pebble (.2-6 cm), Cobble [6-25 cm), [Allow Additional]}
5. Choose {Unhewn, Semi-hewn, Dressed, Ashlar, Bossed}
6. Choose {Dry-laid, Clay (E), Mud (E), Cement (E), Plaster (E), Lime (E), [Allow Additional]} 7. Choose {Unfaced, Plaster (E), Mud (E), Paint (draw)}
7. Bit Variables {Unfaced, Plaster, Mud, Paint}

8. Choose {Boulder & Chink, Ashlar Fit, Header-stretcher, Rubble-filled, Rubble, Stacked Bricks, Tied-in Bricks, Quoin & Pier, Orthostat, [Allow Additional]}

9. Choose {Free-standing, Buttressed, Battered, Foundation, [Allow Additional]}
10. Choose {Complete, Partial Superstructure: Most, Partial Superstructure: Half, Partial Superstructure: Little, Foundation Only: Complete, Foundation Only: Partial, Robbed}

(E) - Requires Earth Supplement Sheet. NOTE ALSO, we now associate supplement sheets with a locus including an association field to describe the element of the locus supplemented. (draw) - Requires Facing image NOTE ALSO, we now associate drawings and photos with a locus including an association field to describe the element of the locus drawn or photographed.

Installation Locus

Installation Loci are unique in that they have a type section and a qualifier to identify how certain we are of that type. We also allow new types of installations be be added.

The Material Section is identical to 17.A Material section in the Architectural locus except that we allow more types of materials. SO WE MAKE THE SALOMON LIKE DECISION TO USE THAT TABLE OF MATERIALS.

• Section	Element Identifier	Element Type	Default	Description
  29-30 match sections 1-2 in the Earth locus
  TYPE
  	Installation_Type_ID	int	NULL	References Required Entity Installation_Type_List(Installation_Type_ID:int, Installation_Type_Description:varchar(25))
  	Installation_Qualifier_ID	int	NULL	References Required Entity Installation_Qualifier_List(Certainty_Qualifier_ID:int, Certainty_Qualifier_Description:varchar(25))
  32.A match section 17.A in the Architectural locus. See the notes above.
  B	Plan_ID	int	NULL	references Required Entity Plan_List(Plan_ID, Plan_Description)
  	Plan_Qualifiers	Multi-Dep.	NULL	intersection with Required Entity Plan_Qualifiers
  	Plan_Remarks	varchar(255)	NULL	Plan Remarks
  C	Lining	Multi-Dep	NULL	intersection with Required Entity Lining_List
  D	Length_Longest	int	NULL	longest length measured in meters
  	Width_Min	int	NULL	measured in meters
  	Width_Max	int	NULL	measured in meters
  	Height_Min	int	NULL	measured in meters
  	Height_Max	int	NULL	measured in meters
  	Orientation	int	NULL	Degrees
  E	Description_Remarks	text	NULL	General Description remarks
  33-43 match sections 4-14 in the Earth locus

Supplement Forms

Note: We did not use supplement forms in the 2009 dig, and they are not slated for implementation for the 2010 dig.

Supplement forms collect information about "inclusions" in another locus. Except for the Burial Supplement, all other supplements are a subset of the entities in their related locus form. Since a supplement is related to a single locus, We do not repeat the identity information. We only give the LocusIdentity and SupplementID to find the parts of the supplement. Specifically the ARCH_Supplement table contains a SupplementID and a SupplementType. Since this does not have a LocusIdentity directly associated with it, it becomes necessary to add a view that does include all three together.

Earth Supplement

Uses Sections:

• Description
• Pottery
• Seeds
• Objects

All of these are defined individually in the Earth Locus. So there is no need to create additional entities.

Architectural Supplement

Uses only the Description Section. Since this is defined individually in the Architectural Locus, there is no need to create additional entities.

Installation Supplement

Uses only the Description Section. Since this is defined individually in the Installation Locus, there is no need to create additional entities.

Burial Supplement

This supplement splits the Description section into:

• Container
• Skeletal Remains

This particular supplement needs work in my opinion. Possibly we should consider this a locus? The supplement carries many of the elements found in all Loci. Since this is the most ambiguous of all the supplements, we'll ask! But we are not implementing it now. SO THIS IS LEFT AS A TODO.