ERM to SQL

1. ERM to SQL

2. Abstraction Layers WHAT • Conceptual • What data is held • An Image and its meta-data • Entity-Relationship model (ERM) • Logical • How data is organised in storage • Block and Directory structure • Tables, keys • Physical • How data is stored in bits • JPEG as a stream of bytes • A Database as files and records stored in a DBMS-specific format Realisation (Refinement Reification) (Engineering, Model-Driven development Abstraction (Reverse Engineering) HOW

3. QSEE

4. Case Tool - QSEE • is a free download for individual use • is available in the lab (older version ) • enables a wide range of diagram types to be created • UML • Data flow diagrams … • Entity-relationship diagram • With generation of SQL for creating a database (with some fixes  - use Perl script to clean up)

5. ER Model to RDBMS • QSEE will produce 2 types of schema • XML • DTD • XML schema • SQL Relational Database with different targets: • ORACLE • MySQL • Microsoft SQL server • SQL 2 standard

6. RDBMS target • Each entity becomes a table • Each attribute becomes a column of a table • Each relationship become common fields and values in two tables. • Two cases • One-many • Many-many

7. Primary Key • An attribute which has a different value for every entity instance – ‘Unique’. • May be a combination of several attributes – Composite Primary Key. • Index on this attribute created by the database • May need to be created if no attribute is unique and stable – Auto-increment. • Primary keys are the glue of a relational database.

8. One-many relationship • Each one-many relationship is implemented by adding the primary key of the entity on the one side (the parent) as new columns on the many (the child) side • This added column is known as a ‘foreign key’ • So Image gets • a new column called ‘memberid’ • a foreign key constraint defines the relationship • When an image is added to the database, the appropriate value of memberid for the creator is added to the image record (a common value)

9. Relationship in RDBMS M1 M3 M2 P1 P4 P5 P7 P3 P9 pkM2 pkM1 pkM1 pkM1 null pkM2 Member Photo

10. Generated SQL DDL CREATE TABLE Member( memberid INTEGER NOT NULL, name VARCHAR(20), PRIMARY KEY (memberid) ); CREATE TABLE Photo( photoid INTEGER NOT NULL, image64 MEDIUMBLOB, memberid INTEGER NOT NULL, PRIMARY KEY (photoid), FOREIGN KEY (memberid) references Member(memberid) );

11. Using a foreign key • To find all the Photos belonging to member M2 Select * from photo where memberid=‘M2’ • To find the creator of photo P1 Select member.name from photo natural join member where photoid=‘P1’

12. Foreign Key integrity • What happens when you add a Photo • The memberid on the Photo record must reference an existing Member • What happens when a member is deleted • Don’t allow it if she has any photos : ‘RESTRICT’ • Delete all her photos as well : ‘CASCADE’ • Set the memberid to NULL ALTER TABLE Photo ADD INDEX (memberid), ADD CONSTRAINT fk1_Photo_to_Member FOREIGN KEY(memberid) REFERENCES Member(memberid) ON DELETE RESTRICT ON UPDATE RESTRICT;

13. Many-many relationship

14. Group GroupPhoto Photo G1 G2 G5 pkG1 pkP1 pkG2 pkP1 pkG1 pkP4 pkG5 pkP4 pkG2 pkP5 pkG1 pkP7 P1 P3 P4 P5 P7 Group Photo

15. Adding a link table CREATE TABLE Tag( tagName VARCHAR(40) NOT NULL, PRIMARY KEY (tagName) ); CREATE TABLE Photo_Tag( photoid INTEGER NOT NULL, tagName VARCHAR(40) NOT NULL, PRIMARY KEY (photoid,tagName), INDEX(photoid), FOREIGN KEY(photoid) REFERENCES Photo(photoid) ON DELETE RESTRICT ON UPDATE RESTRICT, INDEX(tagName), FOREIGN KEY(tagName) REFERENCES Tag(tagName) ON DELETE RESTRICT ON UPDATE RESTRICT );

16. QSEE tasks • Handles the addition of foreign keys • Creates link tables for many-many relationships • So let QSEE do the work • If a primary key changes, the database definition will be updated automatically • Integrity Constraints generated for you

17. ‘Weak entities’ • Entities whose existence depends on the existence of one or more ‘parent’ entities’ • Typically this is a one-many relationship • If the parent is deleted, so are the children • The primary key of the parent becomes a column in the child (foreign key) AND this column is part of the primary of the CHILD • Denoted in the ERM by a bar across the relationship

18. Emp-dept is a bit more complicated

19. SQL DDL for emp-dept -- Create a Database table to represent the "Emp" entity. CREATE TABLE Emp( empno INTEGER NOT NULL, ename VARCHAR(12), job VARCHAR(12), hiredate DATE, sal DECIMAL(8,2), comm DECIMAL(8,2), deptno INTEGER NOT NULL, mgr_empno INTEGER NOT NULL, -- Specify the PRIMARY KEY constraint for table "Emp". -- This indicates which attribute(s) uniquely identify each row of data. CONSTRAINT pk_Emp PRIMARY KEY (empno) ); ALTER TABLE Emp ADD CONSTRAINT fk2_Emp_to_Emp FOREIGN KEY(mgr_empno) REFERENCES Emp(empno) ON DELETE RESTRICT ON UPDATE RESTRICT;

20. Find the names of the managers of employees who earn between 2000 and 3000 Select Mgr from Emp where Sal between 2000 and 3000 Select Empno, Ename from Emp Empno Ename 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER EmpnoMgrDeptno 7566783920 7698783930 7782783910 E M

21. Find the names of the managers of employees who earn between 2000 and 3000 Select Ename From Emp E, Emp M Where E.mgr=M.empno and E.sal between 2000 and 3000 Distinct(Ename) Mgr Empno Ename 7839 7839 KING 7839 7839 KING 7839 7839 KING

22. Target is XML

23. Target XML • Here the names of columns and entities are included in the data. • The relationship between member and images is containment – all the images for a member occur immediately after the details of the member herself • Same ER model but different implementations in different technologies.

24. Tutorial • Generate a database for the KML folder/placemark/style • Clean up the generated code • Remove • Use statement • Comments starting ---- • Use a text editor or if possible the Perl script • Use PhpMyAdmin to create this database in your mySQL account