Digital Media Technology

Digital Media Technology Week 12

Attributes, values and queries Angular brackets, tags and series

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

foreign key primary key

FK = PK

Entity Relationship Diagrams

PERSON P_ID NAME E-MAIL PHONE DATE_OF_BIRTH Attributes PK is underlined

COMPANY BOOK PERSON

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

COMPANY PERSON P_ID C_ID many many one one EMPLOYMENT E_ID P_ID C_ID many many

A look-up table (based on ISO 3166-1993 )

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.

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 );

Implementation • Data entry • Database Design • Data Retrieval

Interpretation continuum Data: relatively unstructured Information: very structured Source: Obrst and Liu, Knowledge representation, Ontological Engineering and Topic Maps, in: XML Topic Maps, 2003

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

SELECT TITLE, YEAR FROM TREASURE ;

SELECT * FROM TREASURE ;

SELECT TITLE, YEARFROM TREASUREORDER BY YEAR ;

SELECT NAME_LAST, NAME_FIRST, (YEAR_OF_DEATH - YEAR_OF_BIRTH) AS AGE FROM CREATOR ;

SELECT TITLE, YEAR FROM TREASURE WHERE YEAR > 1800 ;

SELECT DISTINCT SUBJECT FROM TREASURE ;

Exercise 5

Exercise 6

Digital Media Technology