270 likes | 399 Views
Digital Media Technology. Week 10. foreign key. primary key. 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),
E N D
Digital Media Technology Week 10
foreign key primary key
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 );
PERSON P_ID NAME E-MAIL PHONE DATE_OF_BIRTH Attributes PK is underlined
COMPANY BOOK PERSON
Cardinality: how many instances of the entity can be associated with one instance of the related entity? BOOK COMPANY
STUDENT COURSE ENROLMENT
COMPANY PERSON P_ID C_ID many many one one EMPLOYMENT E_ID P_ID many C_ID many
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
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 );
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 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 ;