class: title-slide, center, middle # Computer Aided Archaeology ## 04 - Database 2 ### Martin Hinz #### Institut für Archäologische Wissenschaften, Universität Bern 11/10/23 --- ## Designing a data model ### Entity - Relationship (ER) .pull-left[ **Entity:**^\* Real-world object, distinguishable from other objects. An entity is described using a set of attributes. **Relationship:**^\* Association among two or more entities. E.g., a fibula was found at Münsingen. - relationships can have their own attributes. ] .pull-right[ <div id="htmlwidget-0e28e47b0f12cd7df1d0" style="width:400px;height:100px;" class="grViz html-widget "></div> <script type="application/json" data-for="htmlwidget-0e28e47b0f12cd7df1d0">{"x":{"diagram":"\ndigraph models_diagram {\n rankdir=\"LR\";\n graph[overlap=false, splines=true]\n \"site\" [shape=record, label=\"{\\\n site|name :string\\l\\\n lat :float\\l\\\n lng :float\\l\\\n }\"]\n \n \"artefact\" [shape=record, label=\"{artefact|\\\n material :string\\l\\\n weight :string\\l\\\n }\"]\n \n \"found_at\" [shape=diamond, label=\"\n found_at\\l\\\n date :datetime\n \"]\n \n\n \"site\" -> \"found_at\" [label=\"1\"]\n \"found_at\" -> \"artefact\" [label=\"N\"]\n}","config":{"engine":"dot","options":null}},"evals":[],"jsHooks":[]}</script> ] .footnote[.tiny[^\***Entity Set:** A collection of similar entities. E.g., all employees. - All entities in an entity set have the same set of attributes. (Basically) - Each entity set has a key (!). - Each attribute has a domain, that means, a range of possible values. ^\***Relationship Set:** Collection of similar relationships. ]] --- ## Types of Relationships ### 1:1, 1:n, n:m .pull-left[ #### Examples - potsherds and features (n:m) - the sherds of one pot can be found at 1:n features - a feature can contain 1:n potsherds - sample and measurements (1:n) - 1 sample has 1:n measurements - artefact and find lable (1:1) - 1 Artefact has 1 find label ] .pull-right[  ] --- ## (primary) keys Each record must be uniquely identifiable. Primary key! either - a set of attributes that are already there and make the record unique - example: Lab Code and Lab Number identify a radiocarbon date or - is an explicit (artificial) attribute that is a sequential number - example: an id number from 1...&infin; **The latter is not pure dogma, but most of the time more pratical** --- ## (primary & foreign) keys If a record is uniquely identifiable, this can be used in relation to other entities: .pull-left[ .pull-left[ |sites| |-----------| | Münsingen | | Worb | ] .pull-right[ |burials| |----------| | Burial 1 | | Burial 2 | | Burial 3 | | Burial 1 | | Burial 2 | | Burial 3 | ] ] .pull-right[ .pull-left[ | id | site | |----|-----------| | 1 | Münsingen | | 2 | Worb | ] .pull-right[ | id | burial | site_id | |----|----------|---------| | 1 | Burial 1 | 1 | | 2 | Burial 2 | 1 | | 3 | Burial 3 | 1 | | 4 | Burial 1 | 2 | | 5 | Burial 2 | 2 | | 6 | Burial 3 | 2 | ] ] The identifier of a record is the **primary key**. The identifier of another record in relation to this one is the **foreign key**. --- ## Normalisation > **Database normalization** is the process of structuring a relational database in accordance with a series of so-called normal forms in order **to reduce data redundancy** and **improve data integrity**. -- wikipedia .small[ ### 1NF To satisfy 1NF, the values in each column of a table must be **atomic**. (Meaning one information at the time) ### 2NF Each data record represents only one fact. If there is data in a table that does not represent only 1 fact, this data is subdivided into individual thematic tables. or more formal: It does not have any non-prime attribute that is functionally dependent on any proper subset of any candidate key of the relation. A non-prime attribute of a relation is an attribute that is not a part of any candidate key of the relation. ### 3NF No data in a record should automatically follow from other data in the same record. ] --- ## That's enought There also exists the 4th, 5th and 6th Normal Form (not to mention the Boyce–Codd normal form (BCNF))... In practise, normalising to the 3th Normal Form is absolutely enough. > Informally, a relational database relation is often described as "normalized" if it meets third normal form. Most 3NF relations are free of insertion, update, and deletion anomalies. -- wikipedia Most of that comes naturally if you think about your relations as objects in the 'Real World'^tm. --- ## Let's get practical .pull-left[ We want to design a data base for finds of your site. What **Informations** do we like to record? What **Entities** and **Relations** do we have? What **Attributes** will the **Entities** have? How can we transform that into tables (this usually comes naturally than)? ] .pull-right[ .tiny[ Table: |site |literature |link | |:---------------------------|:--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|:----| |Birmensdorf-Rameren |Mäder, Andreas, Die mittelbronzezeitlichen Gräber von Birmensdorf-Rameren. Zürcher Archäologie, Heft 24. 2008 |[link](https://swisscovery.slsp.ch/permalink/41SLSP_NETWORK/1ufb5t2/alma991070558649705501)| |Chables |Leyla Duvanel, Henri Vigneau, Michel Guélat et Michel Mauvilly, La nécropole de l’âge du Bronze de Châbles/Les Biolleyres 1. Fribourg 2018 |[link](https://doc.rero.ch/record/306538/files/CHA-BI1_Monographie.pdf)| |Singen |Rüdiger Krause: Die endneolithischen und frühbronzezeitlichen Grabfunde auf der Nordstadtterrasse von Singen am Hohentwiel (= Forschungen und Berichte zur Vor- und Frühgeschichte in Baden-Württemberg. Bd. 32 = Die Grabfunde von Singen. Bd. 1) |[link](https://swisscovery.slsp.ch/permalink/41SLSP_NETWORK/1ufb5t2/alma991131257489705501)| |Murten/Löwenberg |Archäologie und Autobahn A1 : 25 Jahre Ausgrabungen im Murtenbiet |[link](https://doc.rero.ch/record/256263/files/06_archaeologie_autobahn_A1_2008_katalog_austellung.pdf)| |Prag-Miškovice |Ernée , Michal. Prag-Miskovice Archäologische und naturwissenschaftliche Untersuchungen zu Grabbau, Bestattungssitten und Inventaren einer frühbronzezeitlichen Nekropole Römisch-Germanische Forschungen Band 72 2016 |[link](https://www.academia.edu/23371514/Prag_Mi%C5%A1kovice_Arch%C3%A4ologische_und_naturwissenschaftliche_Untersuchungen_zu_Grabbau_Bestattungssitten_und_Inventaren_einer_fr%C3%BChbronzezeitlichen_Nekropole)| |Spiez, Einigen, Holleeweg 3 |Gubler, Regula, Spiez-Einigen, Holleeweg 3. Gräber am Übergang zwischen Früh- und Mittelbronzezeit . Archäologie Bern/Archéologie bernoise – 2010, 147 |[link](https://www.e-periodica.ch/digbib/view?pid=akb-002%3A2010%3A0%3A%3A152)| ] ] --- ## Structure of your Database ### Draft You can start as you like, paper, blackboard, mindmap, actual ER design tool... .center[  ] --- ## Structure MiscoviceDB ### ER-Diagramm <div id="htmlwidget-d36e80042a6ed9f35945" style="width:800px;height:400px;" class="grViz html-widget "></div> <script type="application/json" data-for="htmlwidget-d36e80042a6ed9f35945">{"x":{"diagram":"\ndigraph models_diagram {\n rankdir=\"LR\";\n graph[overlap=false, splines=true]\n \"sites\" [shape=record, label=\"{\\\n sites|name :string\\l\\\n lat :float\\l\\\n lng :float\\l\\\n }\"]\n \n \"graves\" [shape=record, label=\"{graves|\\\n grave_number :string\\l\\\n depth :float\\l\\\n length :float\\l\\\n width :float\\l\\\n construction :text\\l\\\n }\"]\n \n \"individuals\" [shape=record, label=\"{individuals|\\\n sex :string\\l\\\n age :string\\l\\\n orientation :float\\l\\\n completeness :float\\l\\\n }\"]\n \n \"artefacts\" [shape=record, label=\"{artefacts|\\\n name :string\\l\\\n type :string\\l\\\n material :string\\l\\\n }\"]\n \n \"c14_dates\" [shape=record, label=\"{c14_dates|\\\n lab_code :string\\l\\\n bp :integer\\l\\\n std :integer\\l\\\n }\"]\n\n \"sites\" -> \"graves\" [label=\"1:n\"]\n \"graves\" -> \"artefacts\" [label=\"1:n\"]\n \"graves\" -> \"individuals\" [label=\"1:n\"]\n \"graves\" -> \"c14_dates\" [label=\"1:n\"]\n \n}","config":{"engine":"dot","options":null}},"evals":[],"jsHooks":[]}</script> --- ## Structure MiscoviceDB ### Primary and foreign keys <div id="htmlwidget-5f1456469dbd63604e1e" style="width:800px;height:400px;" class="grViz html-widget "></div> <script type="application/json" data-for="htmlwidget-5f1456469dbd63604e1e">{"x":{"diagram":"\ndigraph models_diagram {\n rankdir=\"LR\";\n graph[overlap=false, splines=true]\n \"sites\" [shape=record, label=\"{sites|\\\n id :integer\\l\\\n name :string\\l\\\n lat :float\\l\\\n lng :float\\l\\\n }\"]\n \n \"graves\" [shape=record, label=\"{graves|\\\n id :integer\\l\\\n site_id :integer\\l\\\n grave_number :string\\l\\\n depth :float\\l\\\n length :float\\l\\\n width :float\\l\\\n construction :text\\l\\\n }\"]\n \n \"individuals\" [shape=record, label=\"{individuals|\\\n id :integer\\l\\\n grave_id :integer\\l\\\n sex :string\\l\\\n age :string\\l\\\n orientation :float\\l\\\n completeness :float\\l\\\n }\"]\n \n \"artefacts\" [shape=record, label=\"{artefacts|\\\n id :integer\\l\\\n grave_id :integer\\l\\\n name :string\\l\\\n type :string\\l\\\n material :string\\l\\\n }\"]\n \n \"c14_dates\" [shape=record, label=\"{c14_dates|\\\n id :integer\\l\\\n grave_id :integer\\l\\\n lab_code :string\\l\\\n bp :integer\\l\\\n std :integer\\l\\\n }\"]\n\n \"sites\" -> \"graves\" [label=\"1:n\"]\n \"graves\" -> \"artefacts\" [label=\"1:n\"]\n \"graves\" -> \"individuals\" [label=\"1:n\"]\n \"graves\" -> \"c14_dates\" [label=\"1:n\"]\n \n}","config":{"engine":"dot","options":null}},"evals":[],"jsHooks":[]}</script> --- ## Structure MiscoviceDB ### Lookup tables <div id="htmlwidget-dbbbc171ac02543b85c3" style="width:800px;height:400px;" class="grViz html-widget "></div> <script type="application/json" data-for="htmlwidget-dbbbc171ac02543b85c3">{"x":{"diagram":"\ndigraph models_diagram {\n rankdir=\"LR\";\n graph[overlap=false, splines=true]\n \"sites\" [shape=record, label=\"{sites|\\\n id :integer\\l\\\n name :string\\l\\\n lat :float\\l\\\n lng :float\\l\\\n }\"]\n \n \"graves\" [shape=record, label=\"{graves|\\\n id :integer\\l\\\n site_id :integer\\l\\\n grave_number :string\\l\\\n depth :float\\l\\\n length :float\\l\\\n width :float\\l\\\n construction :text\\l\\\n }\"]\n \n \"individuals\" [shape=record, label=\"{individuals|\\\n id :integer\\l\\\n grave_id :integer\\l\\\n sex_id :integer\\l\\\n age_id :integer\\l\\\n orientation :float\\l\\\n completeness :float\\l\\\n }\"]\n \n \"sexes\" [shape=record, label=\"{sexes|\\\n id :integer\\l\\\n name :string\\l\\\n }\"]\n \n \"artefacts\" [shape=record, label=\"{artefacts|\\\n id :integer\\l\\\n grave_id :integer\\l\\\n name :string\\l\\\n type_id :integer\\l\\\n material_id :integer\\l\\\n }\"]\n \n \"types\" [shape=record, label=\"{types|\\\n id :integer\\l\\\n name :string\\l\\\n }\"]\n \n \"materials\" [shape=record, label=\"{materials|\\\n id :integer\\l\\\n name :string\\l\\\n }\"]\n \n \"ages\" [shape=record, label=\"{ages|\\\n id :integer\\l\\\n name :string\\l\\\n }\"]\n \n \"c14_dates\" [shape=record, label=\"{c14_dates|\\\n id :integer\\l\\\n grave_id :integer\\l\\\n lab_code :string\\l\\\n bp :integer\\l\\\n std :integer\\l\\\n }\"]\n\n \"sites\" -> \"graves\" [label=\"1:n\"]\n \"graves\" -> \"artefacts\" [label=\"1:n\"]\n \"graves\" -> \"individuals\" [label=\"1:n\"]\n \"graves\" -> \"c14_dates\" [label=\"1:n\"]\n \"types\" -> \"artefacts\" [label=\"1:n\"]\n \"materials\" -> \"artefacts\" [label=\"1:n\"]\n \"sexes\" -> \"individuals\" [label=\"1:n\"]\n \"ages\" -> \"individuals\" [label=\"1:n\"]\n \n}","config":{"engine":"dot","options":null}},"evals":[],"jsHooks":[]}</script> --- ## Structure reflected in the DB-Program .pull-left[ * Tables -> Tables * Relationships -> primary and foreign keys, relationship using the relationship tool Things look different in eg. MS Access, but work the same ] .pull-right[   ] --- ## To start .pull-left[ * Open LibreOffice Base * Create a new Database * Click on Finish * Save your new Database to a reasonable folder ] .pull-right[   ] --- ## Tables .pull-left[ Store your information on specific items (rows) with defined fields (columns) * Double click shows the stored information * Right Click > Edit lets you change the field definitions (dangerous, if you have already data!!!) ] .pull-right[  ] --- ## Relationsships .pull-left[ Define the relationships between your tables * most important is the reasonable naming of the primary key ('id'!?) and the foreign keys ('site_id'!?) * Defining the relationships in the "Relationship tool" helps to ease things later on (it provides information for the database tool) ] .pull-right[  ] --- ## Forms .pull-left[ Help to structure your inputs * Give the user (you?) a nice entry form * Enable to select values easily with dropdown fields and lookup tables * limit and structure the possible entry values * enable with subforms to enter data to different tables at once ] .pull-right[  ] --- ## Data Entry .pull-left[ Your task within the next two weeks: * Enter data from your sites into the database with the structure you designed * Distribute the work, share your workload!!! ] .pull-right[  .caption[source:https://commons.wikimedia.org/wiki/File:Social_collaboration_at_edge_of_chaos.jpg] ] --- ## Queries .pull-left[ ...are structured ways to receive information with specific conditions * Most desktop DB systems offer "Wizards" and Tools to design a query * In the background it will (nearly always) be SQL * SQL [ˈsiːkwəl]: "Structured Query Language" ] .pull-right[  ] ```sql SELECT "graves"."id", "graves"."construction", "sexes"."name" FROM "individuals", "graves", "sexes" WHERE "individuals"."grave_id" = "graves"."id" AND "individuals"."sex_id" = "sexes"."id" AND "sexes"."name" = 'male'; ``` --- class: inverse, middle, center # Any questions? .footnote[ .right[ .tiny[ You might find the course material (including the presentations) at https://berncodalab.github.io/caa You can contact me at <a href="mailto:martin.hinz@iaw.unibe.ch">martin.hinz@iaw.unibe.ch</a> ] ] ]