Enhancing Relational Database Design: The Spy Database Case Study

1. F27DB Introduction to Database Systems Lecture 2: Using Relational Databases Helen Hastie h.hastie@hw.ac.uk LT2 Student hours: Tuesday 2pm-3pm Twitter: @IntroDBHW and #IntroDBHW Material available on Vision Introduction to Databases

2. Recap • So far you have seen how to: • Create a table • Add some data • Run some queries to extract the data • From the first lab you should know how to: • Start MySQL • Run commands from a text file and by typing • Extract data from a single table • Next • Relational databases • => relationships between multiple tables Introduction to Databases

3. Extending the Spy database • We have a new specification from the MySpy agency. • The agency require to store information about spies, spy masters, and bank accounts • Each spy should have a unique code name, a first name, last name, date of birth, sex, distinguishing mark, and payment due. • A spy may also have several skills and several spies may have the same skill. • A spy master has a unique code name. • A bank account has a unique account number, income to date, and expenditure to date. • A spy master handles several spies. Each spy and each spy master has one bank account. Introduction to Databases

4. The existing spy table • Each spy should have a unique code name, a first name, last name, date of birth, sex, distinguishing mark, and payment due. Introduction to Databases

5. Alterations to be made • A spy might have >1 skill • A spy has a bank account • A spy has a spy master • A spy master has a code, a bank account and supervises several spies • All this complexity does not fit into a single table • Start by identifying entities and drawing an Entity Relationship or E-R diagram Introduction to Databases

6. E-R diagram M Spy codeName firstName dateOfBirth gender mark amountDue M M SpySkillList skillCode skillName has 1 manages SpyMaster mCodeName contact 1 1 has In the E-R diagram, the fact that a spy has other entities - a bank account, a spymaster and skills is ONLY shown by the relationship lines has 1 1 SpyAccount accountNumber Income expenditure Introduction to Databases

7. The final tables NB Many to many relationships need an extra ‘linking’ table M Spy codeName firstName dateOfBirth gender mark amountDue spyMaster bankAccount 1 1 manages SpyWithSkill spyCode skillCode SpyMaster mCodeName bankAccount contact skilled at M M 1 has practised by 1 1 has 1 1 SpySkillList skillCode skillName SpyAccount accountNumber Income expenditure Bold : primary key Italic : foreign key Bold and italic : both Now the relationships are also shown by foreign keys Introduction to Databases

8. Foreign keys • A foreign key is a field in a relational table that matches the primary key column of another table. • It establishes the links between the tables. • Examples • bankAccount in SpyMaster • bankAccount in Spy • spyMaster in Spy Introduction to Databases

9. The final tables M Spy codeName firstName dateOfBirth gender mark amountDue spyMaster bankAccount 1 1 manages SpyWithSkill spyCode skillCode SpyMaster mCodeName bankAccount contact skilled at M M 1 has practised by 1 1 has 1 1 SpySkillList skillCode skillName SpyAccount accountNumber Income expenditure Bold : primary key Italic : foreign key Bold and italic : both Introduction to Databases

10. Spy DB M 1 1 1 skilled at has M manages M 1 practised by 1 has 1 1 Introduction to Databases

11. One to many relationships • One spymaster, several spies • The Spy table contains the code name of the spy’s spymaster, as a link to the details in the bank account table • The spymaster codename in the Spy table is a foreign key • It links to a primary key in the SpyMaster table Introduction to Databases

12. One to one relationships • One bank account, one spymaster • The SpyMaster table contains the bank account number, as a link to the details in the bank account table • The bank account number in the SpyMaster table is a foreign key • It links to a primary key in the Bank Account table Introduction to Databases

13. Many to many relationships • A separate table has been created for skills • In this way, each skill is listed only once and so we get consistency with spelling • A code number has been invented for each skill • Long primary keys waste space • skillName is not very long, but is just used to demonstrate Introduction to Databases

14. Many to many relationships • A separate table is created with a list of links – which spy, which skill • This is essential for many-to-many relationships Introduction to Databases

15. Many to many relationships • Note that the SpyWithSkill table has • 2 foreign keys, linking to 2 different tables • A composite primary key consisting of both fields • BOTH fields are needed to make a row unique Introduction to Databases

16. Creating the tables • In the Create Table command, or as a separate Alter Table command, you can provide the foreign key information showing that one column refers to another • We use the InnoDB engine to make sure that foreign key references are supported CREATE TABLE SpyWithSkill ( spyCode VARCHAR(10), skillCode INT , PRIMARY KEY (spyCode, skillCode), FOREIGN KEY (skillCode) REFERENCES SpySkillsList (skillCode), FOREIGN KEY (spyCode) REFERENCES Spy (codeName) )ENGINE=INNODB; Introduction to Databases

17. AUTO-INCREMENT • In the SpySkillList table, each skill has been given a unique automatically generated sequence number • New entries get the next number • To allocate the next number, add ‘AUTO-INCREMENT’ to the column in the CREATE TABLE command • E.g.skillCode INT AUTO-INCREMENT PRIMARY KEY, Introduction to Databases

18. INSERTING VALUES WITH AUTO-INCREMENT • Use a slightly different format of the INSERT command. Because you are not inserting a value for every column, you must specify which ones: • INSERT INTO SpySkillList (skillName) VALUES (‘explosive expert’); • The skillCode appears magically! Introduction to Databases

19. Foreign key constraints • You must make sure that the table that you are referencing with ‘Foreign Key’ exists • Either create all the tables without foreign key constraints, then add in the foreign keys constraints afterwards using the ALTER TABLE command (This way is taken in Brian’s examples) • Or create tables in suitable order, including foreign key constraints • Note that you must drop the tables in order so that all the ones containing foreign keys are dropped before the table referenced by the foreign key • I have done this in the Spy tables • This way works most of the time, but not if 2 tables are referenced both ways • E.g. Spy has a SpyMaster • SpyMaster has a second-in-command who is a Spy Introduction to Databases

20. Suitable order for creating Spy tables • Have a look back at the Spy tables • What is wrong with this order for table creation? • Spy • SpyAccount • SpyMaster • SpyWithSkill • SpySkillList • What would be a more suitable order? • SpyWithSkill • SpySkillList • Spy • SpyMaster • SpyAccount Introduction to Databases

21. Creating your database from a text file • We recommend that you create your database from a text file containing commands to create the table and to insert the data • In this way, you can • Easily correct errors in your commands and rerun them • Retain a record of what you did • Move db from home to uni, if required • But to rerun the commands, you must start by dropping the tables • DROP TABLE tablename; • Again, order is important. You can’t drop a table which is being referenced. Introduction to Databases

22. Text file - verbose mode • To see your commands listed on the screen as MySQL runs through them from a text file, you need to be in ‘verbose mode’ • So alter the startup command to be • mysql –u username –D username –h mysql-server-1 –vp • The last bit has • v for verbose • p for password Introduction to Databases

23. Retrieving data from >1 table • We’d like details of spy masters and their accounts • How NOT to do it! • SELECT * required columns …FROM SpyMaster, SpyAccount from these tables • This gives you EVERY row from the Spy table joined to EVERY row from the BankAccount table, as shown on the next slide Introduction to Databases

24. Problem • We ONLY want those rows with the matching account numbers • In bold below Introduction to Databases

25. Solution • Restrict the rows to those with matching account numbers • SELECT * FROM SpyMaster, SpyAccountWHERE bankAccount = accountNumber Introduction to Databases

26. Column name uniqueness • Restrict the rows to have matching account numbers • SELECT * FROM SpyMaster, SpyAccountWHERE bankAccount = accountNumber • In the above query, we know which tables the column names come from, because they are unique within the above named tables • E.g.The bankAccount column only exists in the SpyMaster table • BUT, supposing we had called them both accNum? • We need a way of distinguishing them Introduction to Databases

27. Specifying table together with column name • There are 2 ways to specify which table the column belongs to • You can also use the table name as well • SELECT * FROM SpyMaster, SpyAccountWHERE SpyMaster.bankAccount = SpyAccount.accountNumber • Or use an alias for the table name (shorter!) • SELECT * FROM SpyMaster M , SpyAccount AWHERE M.bankAccount = A.accountNumber • It’s quite nice to use the alias all the time, to make it very clear which table the column name belongs to. It is only essential • When the same column name occurs in >1 table • When a query involves using the same table more than once (not covered today) Introduction to Databases

28. Less rows, less columns • As before, you can restrict the number of columns • SELECT mCodeName, income FROM SpyMaster, SpyAccountWHERE bankAccount = accountNumber • And restrict the number of rows on other criteria • SELECT income, expenditureFROM SpyMaster, SpyAccountWHERE bankAccount = accountNumberAND mCodeName = ‘M’; Introduction to Databases

29. Restricting rows • We are now using the WHERE clause for 2 reasons • To specify the foreign key between linked tables • To specify some criteria on the data e.g. • income < 100 • mCodeName = ‘M’ • The order is not important • The DBMS will work out the most efficient way to execute your query • However, I recommend that you put all the foreign key links together, at the start, so they come straight after the table names Introduction to Databases

30. Query 1:N • For each spymaster, list their code name and their contact point, and the code names of each of the spies that they supervise • SELECT mCodeName, contact, codeNameFROM Spy S, SpyMaster MWHERE S.spyMaster = M.mCodeName; Introduction to Databases

31. Query 1:N • There is some duplication in the result • In a real application, you would need to use scripts or tools to lay the results out nicely. +-----------+---------+----------+ | mCodeName | contact | codeName | +-----------+---------+----------+ | M | Drop 5 | freddie | | Q | Jimmy's | 007 | | Q | Jimmy's | 1 | | Q | Jimmy's | bud | +-----------+---------+----------+ Introduction to Databases

32. Using column aliases • You can rename output columns using aliases • SELECT mCodeName as masterCode, contact, codeNameas SpyCodeFROM Spy S, SpyMaster MWHERE S.spyMaster = M.mCodeName; +------------+---------+----------+ | masterCode | contact | spyCode | +------------+---------+----------+ | M | Drop 5 | freddie | | Q | Jimmy's | 007 | | Q | Jimmy's | 1 | | Q | Jimmy's | bud | +------------+---------+----------+ Introduction to Databases

33. Query M:N • List the names of the spies with the skill ‘top shot’ • SELECT firstName, lastNameFROM Spy S, SpyWithSkill W, SpySkillList LWHERE S.codeName = W.spyCodeAND W.skillCode = L.skillCodeAND L.skillName = ‘top shot’; Introduction to Databases

34. Maintaining data integrity • Some issues. E.g. • What if we give a spy an account number which doesn’t exist in the Account table? • This won’t be permitted if the Spy table includes a foreign key referencing the account number in the Account table Introduction to Databases

35. SQL Tutorial • There is a good tutorial on the w3schools site • http://www.w3schools.com/sql/ • Complete reference on the MySQL website Introduction to Databases

36. What next? • Updating and deleting will be covered in Brian’s sql lecture • There’s a separate handout for Lab2 • Try out all the queries and invent your own • Notice the command to log interactions to a text file • Brian’s lectures continue with database material • Helen’s lectures move on to web pages Introduction to Databases