F27DB Introduction to Database Systems Using Relational Databases

1
F27DB Introduction to Database Systems Using
Relational Databases

• Monica Farrow
• monica_at_macs.hw.ac.uk
• Room EMG30
• Material available on Vision

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
• gt relationships between multiple tables

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.

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.

code name first name last name date of birth sex mark amount due
007 James Bond 12 December 1972 M Mole on chin 5050
bud Fanny Charleston 31 July 1983 F scar on cheek 25.67
freddie John Smith 05 September 1954 M one finger missing 312.5
5
Alterations to be made

• A spy might have gt1 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 E-R
  diagram

6
E-R diagram
Spy codeName firstName dateOfBirth gender mark amo
untDue
0..
0..
1..
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
7
The final tables
NB Many to many relationships need an extra
linking table
Spy codeName firstName dateOfBirth gender mark am
ountDue spyMaster bankAccount
1..
1
1
manages
SpyWithSkill spyCode skillCode
SpyMaster mCodeName bankAccount contact
skilled at
1..
0..
1
has
practised by
1
1
has
1
SpySkillList skillCode skillName
1
SpyAccount accountNumber Income expenditure
Bold primary key Italic foreign key Both
both
Now the relationships are also shown by foreign
keys
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

9
Spy DB
SpyMaster SpyMaster SpyMaster
mCodeName bankAccount contact
M 12345 Drop 5
P 56789 PO Box 23
Q 13579 Jimmie's
SpyWithSkill SpyWithSkill
spyCode skillCode
007 1
007 4
bud 2
bud 3
bud 4
freddie 4
freddie 5
SpyAccount SpyAccount SpyAccount
account Number in come expend iture
12345 0 0
13579 23567 1345.89
23456 2000 1345
34567 345 56.34
45678 3579.57 5280.45
56789 12678 10345
SpySkillList SpySkillList
skillCode skillName
1 top shot
2 skilled with a knife
3 fast runner
4 quick thinker
5 can pilot a helicopter
Spy Spy Spy Spy Spy Spy Spy Spy Spy
code Name first Name lastName dateOfBirth gender mark amount Due spyMaster bankAccount
007 James Bond 12 December 1972 M Mole on chin 5050 Q 23456
bud Fanny Charleston 31 July 1983 F scar on cheek 25.67 Q 34567
freddie John Smith 05 September 1954 M one finger missing 312.5 M 45678
10
One to many relationships

• One spymaster, several spies
• The Spy table contains the code name of the spys
  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

SpyMaster SpyMaster SpyMaster
code name bank Account contact
M 12345 Drop 5
P 56789 PO Box 23
Q 13579 Jimmie's
Spy Spy Spy Spy Spy Spy
code Name first Name lastName etc spymaster bankAccount
007 James Bond . . . Q 23456
bud Fanny Charleston . . . Q 34567
freddie John Smith . . . M 45678
11
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

Bank Account Bank Account Bank Account
account number In come Expend iture
12345 0 0
13579 23567 1345.89
23456 2000 1345
34567 345 56.34
45678 3579.57 5280.45
56789 12678 10345
SpyMaster SpyMaster SpyMaster
code name bankAccount contact
M 12345 Drop 5
P 56789 PO Box 23
Q 13579 Jimmie's
12
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
• A separate table is created with a list of links
  which spy, which skill
• This is essential for many-to-many relationships

13
Many to many relationships
SpyWithSkill SpyWithSkill
spyCode skillCode
007 1
007 4
bud 2
bud 3
bud 4
freddie 4
freddie 5
SpySkillList SpySkillList
skillCode skillName
1 top shot
2 skilled with a knife
3 fast runner
4 quick thinker
5 can pilot a helicopter
Spy Spy Spy
code name first name etc
007 James . . .
bud Fanny . . .
freddie John . . .

• 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

14
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 SpySkills
  (sCode),
• FOREIGN KEY (spyCode) REFERENCES Spy (codeName)
• )ENGINEINNODB

15
Null fields

• Some of your columns, like names, are compulsory.
  
• Add NOT NULL for these columns in the CREATE
  TABLE command
• E.g. firstName VARCHAR(20) NOT NULL,
• Some columns might be optional
• This is the default
• E.g. dateOfBirth DATE,
• A column which is a primary key is compulsory. So
  if it is not there, MySQL adds the NOT NULL
  constraint for you.
• For the Spy tables, I have made some columns
  compulsory and some optional

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

17
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
  (top shot)
• The skillCode appears magically!

18
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 Brians examples
• Or create tables in suitable order, including
  foreign key constraints
• 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

19
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?

20
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 cant drop a table
  which is being referenced.

21
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 anubis vp
• The last bit has
• v for verbose
• p for password

22
Retrieving data from gt1 table

• Wed 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

23
Problem

• We ONLY want those rows with the matching account
  numbers
• In bold below

Answer Answer Answer Answer Answer Answer
code name Bank Account contact Account Number In come Expend iture
M 12345 Drop 5 12345 0 0
P 56789 PO Box 23 12345 0 0
Q 13579 Jimmie's 12345 0 0
M 12345 Drop 5 13579 23567 1345.89
P 56789 PO Box 23 13579 23567 1345.89
Q 13579 Jimmie's 13579 23567 1345.89
. . . . . . . . . . . . . . . . . .
M 12345 Drop 5 56789 12678 10345
P 56789 PO Box 23 56789 12678 10345
Q 13579 Jimmie's 56789 12678 10345
24
Solution

• Restrict the rows to those with matching account
  numbers
• SELECT FROM SpyMaster, SpyAccountWHERE
  bankAccount accountNumber

Answer Answer Answer Answer Answer Answer
code name bank Account contact account Number In come Expend iture
M 12345 Drop 5 12345 0 0
Q 13579 Jimmie's 13579 23567 1345.89
P 56789 PO Box 23 56789 12678 10345
25
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

26
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
• Its 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 gt1 table
• When a query involves using the same table more
  than once (not covered today)

27
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

28
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 lt 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

29
Query 1N

• 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

30
Query 1N

• 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
• ------------------------------

31
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
• -------------------------------

32
Query MN

• 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

33
Maintaining data integrity

• All sorts of issues. E.g.
• What if we give a spy an account number which
  doesnt exist in the Account table?
• This wont be permitted if the Spy table includes
  a foreign key referencing the account number in
  the Account table
• What if we try to insert a date of birth in the
  future?
• In MySQL, its not possible to add constraints to
  the data. You have to do it in the program or web
  page which uses the database
• You can add constraints to the data in other
  databases (e.g. Oracle, MS Access)

34
SQL Tutorial

• There is a good tutorial on the w3schools site
• http//www.w3schools.com/sql/
• Complete reference on the MySQL website

35
What next?

• Updating and deleting will be covered in Brians
  sql lecture
• Theres a separate handout for Lab2
• Try out all the queries and invent your own
• Notice the command to log interactions to a text
  file
• Brians lectures continue with database material
• Monicas lectures move on to web pages