Digital Media Technology

1. Digital Media Technology Week 10

2. foreign key primary key

3. CREATE TABLE TREASURE ( TREASURE_ID INT (4) NOT NULL AUTO_INCREMENT, TITLE VARCHAR (150), CREATOR INT, LIBRARY CHAR(6), SUBJECT CHAR(3), YEAR INT (4), PRIMARY KEY (TREASURE_ID), FOREIGN KEY (CREATOR) REFERENCES CREATOR ON DELETE RESTRICT ON UPDATE CASCADE, FOREIGN KEY (LIBRARY) REFERENCES LIBRARY ON DELETE RESTRICT ON UPDATE CASCADE, FOREIGN KEY (SUBJECT) REFERENCES SUBJECT ON DELETE RESTRICT ON UPDATE CASCADE );

4. FK = PK

5. Semantic Web

6. Entity Relationship Diagrams

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

8. COMPANY BOOK PERSON

9. Cardinality: how many instances of the entity can be associated with one instance of the related entity? BOOK COMPANY

10. STUDENT COURSE ENROLMENT

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

12. Making an ERD • Identify entities • Consider the cardinality of the relations between these entities • One-to-one relations must be removed – these entitites can be combined in a single entity • Change many-to-many relations into one-to-many relations by making use of linking tables • Add attributes

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

16. 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.

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

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

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

20. SELECT TITLE, YEAR FROM TREASURE ;

21. SELECT * FROM TREASURE ;

22. SELECT TITLE, YEARFROM TREASUREORDER BY YEAR ;

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

24. SELECT TITLE, YEAR FROM TREASURE WHERE YEAR > 1800 ;

25. SELECT DISTINCT SUBJECT FROM TREASURE ;