Digital Media Technology

1

• Digital Media Technology

Week 12
2

• Database
• DBMS
• Tables, Rows, Columns, Records, Fields
• Relational data model
• Data redundancy

3
Applications

• Database on book consumption in early modern
  period, using data from auction catalogues
• Database on 19c Dutch booktrade, using data from
  booktrade correspondence

4

• Implementation
• Data entry

• Database Design

• Data Retrieval

5
(No Transcript)
6

• Cardinality of many is represented by a line
  ending in a crow's foot.

7

• Entity Relationship Diagrams

8
(No Transcript)
9
TREASURE_ID TITLE CREATOR LIBRARY SUBJECT YEAR
1 Sidereus Nuncius 7 7 SCI 1610
2 Requiem KV 626 2 1 MUS 1791
3 Rabbit Hunt, in the lower left Brueghel 1560. 3 3 ART 1560
4 De antiquitate Britanicae Ecclesiae 8 4 ART 1572
5 Vedute di Roma con scene di costume 6 6 HIS 1810
6 Corrected page proofs of 'Les Fleurs du mal' 1 2 HIS 1857
7 Vinegar Hill, charge of the 5th Dragoon Guards 4 5 HIS 1880
8 Poster of "Internationale Ausstellung für Buchgewerbe und Graphik" 5 9 ART 1914
9 Fontana dei Fiumi a Piazza Navona 9 6 ART 1734
foreign key
primary key
CREATOR_ID NAME_LAST NAME_FIRST YEAR_OF_BIRTH YEAR_OF_DEATH COUNTRY_BORN
1 Baudelaire Charles 1821 1867 FR
2 Mozart Wolfgang Amadeus 1756 1791 AT
3 Bruegel The Elder Pieter 1525 1569 BE
4 Sadler William 1782 1839 IE
5 Tiemann Walter 1876 1951 DE
6 Macchiavelli Giacomo 1756 1811 IT
7 Galilei Galileo 1564 1642 IT
8 Parker Matthew 1504 1575 GB
9 Wittel Caspar van 1655 1736 NL
10 Molyneux Daniel 1568 1632 IE
10
A look-up table
code name
uk United Kingdom
fr France
ne The Netherlands
be Belgium
(based on ISO 3166-1993 )
11
Name Country_born
Shelley, Percy Bysshe Great Britain
Swinburne, Algernon Charles Britain
Cowper, William United Kingdom
Coleridge, Samuel Taylor U.K.
12
Name Country_born
Shelley, Percy Bysshe uk
Swinburne, Algernon Charles uk
Cowper, William uk
Coleridge, Samuel Taylor uk
13
FK PK
14
Referential Integrity

• Each foreign key should correspond to an existing
  primary key.
• Most DBMSs take measures to prevents users or
  applications from entering inconsistent data.

15
CREATE TABLE BOOK( B_ID INT NOT NULL
AUTO_INCREMENT, TITLE VARCHAR (50), AUTHOR
INT, LANGUAGE VARCHAR (40), PUBLISHER
INT, EXTENT INT, YEAR INT(4), PRIMARY KEY
(B_ID), FOREIGN KEY (AUTHOR) REFERENCES PERSON
ON DELETE RESTRICT ON UPDATE CASCADE, FOREIGN
KEY (PUBLISHER) REFERENCES COMPANY ON DELETE
RESTRICT ON UPDATE CASCADE )
16
Interpretation continuum
Data relatively unstructured
Information very structured
Source Obrst and Liu, Knowledge representation,
Ontological Engineering and Topic Maps, in XML
Topic Maps, 2003
17
(Very dirty) data
Dirty Data
17
18
Data Pattern Information
Data Patterns Information
18
19
Information Context Knowledge
Data Context Knowledge
..., 1941, 1943,
, 1947, 1949, ...
1939 -
19
20
DIKT a Visualisation
20
21
DIKT A Closer Look (1)

• Data
• discrete, atomistic portions of 'givens' without
  inherent structure or necessary relationship
  between them.
• Data have no meaning in themselves.
• Phonetical level in linguistics
• Information
• Data patterns
• Meaningful data
• Phonological / lexical level in linguistics

21
22
DIKT A Closer Look (2)

• Knowledge
• Information as part of a context and useful in
  this context
• Social or semantic context
• Contextualisation enables (simple!) interpolative
  and deterministic reasoning.
• Syntactic level in linguistics
• Thinking
• Replace the rich but diffuse concept 'wisdom'
  with 'thinking'
• Mental activity we cannot (entirely) confer to
  machines
• Non-deterministic
• Semantic level in linguistics (wisdom would
  probably be on pragmatic level)
• gt Knowledge Information Context

22
23

• SQL Structured Query Language
• Supported by most RDBMSs.
• Makes use of regular English words

24
SELECT TITLE, YEAR FROM TREASURE
TITLE YEAR
Sidereus Nuncius 1610
Requiem KV 626 1791
Rabbit Hunt, in the lower left Brueghel 1560. 1560
De antiquitate Britanicae Ecclesiae 1572
Vedute di Roma con scene di costume 1810
Corrected page proofs of 'Les Fleurs du mal' 1857
Vinegar Hill, charge of the 5th Dragoon Guards 1880
Poster of "Internationale Ausstellung für Buchgewerbe und Graphik" 1914
Fontana dei Fiumi a Piazza Navona 1734
25
SELECT FROM TREASURE
treasure_id title year creator library subject
1 Sidereus Nuncius 1610 7 7 SCI
2 Requiem KV 626 1791 2 1 MUS
3 Rabbit Hunt, in the lower left Brueghel 1560. 1560 3 3 ART
4 De antiquitate Britanicae Ecclesiae 1572 8 4 ART
5 Vedute di Roma con scene di costume 1810 6 6 HIS
6 Corrected page proofs of 'Les Fleurs du mal' 1857 1 2 HIS
7 Vinegar Hill, charge of the 5th Dragoon Guards 1880 4 5 HIS
8 Poster of "Internationale Ausstellung für Buchgewerbe und Graphik" 1914 5 8 ART
9 Fontana dei Fiumi a Piazza Navona 1734 9 6 ART
26
SELECT TITLE, YEARFROM TREASUREORDER BY YEAR
TITLE YEAR
Rabbit Hunt, in the lower left Brueghel 1560. 1560
De antiquitate Britanicae Ecclesiae 1572
Sidereus Nuncius 1610
Fontana dei Fiumi a Piazza Navona 1734
Requiem KV 626 1791
Vedute di Roma con scene di costume 1810
Corrected page proofs of 'Les Fleurs du mal' 1857
Vinegar Hill, charge of the 5th Dragoon Guards 1880
Poster of "Internationale Ausstellung für Buchgewerbe und Graphik" 1914
27
SELECT NAME_LAST, NAME_FIRST,
(YEAR_OF_DEATH - YEAR_OF_BIRTH) AS AGE FROM
CREATOR
NAME_LAST NAME_FIRST AGE
Baudelaire Charles 46
Mozart Wolfgang Amadeus 35
Bruegel The Elder Pieter 44
Sadler William 57
Tiemann Walter 75
Macchiavelli Giacomo 55
Galilei Galileo 78
Parker Matthew 71
Wittel Caspar van 81
Molyneux Daniel 64
28
SELECT TITLE, YEAR FROM TREASURE WHERE YEAR gt
1800
TITLE YEAR
Vedute di Roma con scene di costume 1810
Corrected page proofs of 'Les Fleurs du mal' 1857
Vinegar Hill, charge of the 5th Dragoon Guards 1880
Poster of "Internationale Ausstellung für Buchgewerbe und Graphik" 1914
29
SELECT DISTINCT SUBJECT FROM TREASURE
SUBJECT
ART
HIS
MUS
SCI
30
(No Transcript)
31
PHP
HTML
http
HTML
db
SQL
CLIENT
SERVER