f27db introduction to database systems lecture 2 using relational databases n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
F27DB Introduction to Database Systems Lecture 2: Using Relational Databases PowerPoint Presentation
Download Presentation
F27DB Introduction to Database Systems Lecture 2: Using Relational Databases

Loading in 2 Seconds...

play fullscreen
1 / 36

F27DB Introduction to Database Systems Lecture 2: Using Relational Databases - PowerPoint PPT Presentation


  • 82 Views
  • Uploaded on

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. Recap. So far you have seen how to: Create a table Add some data

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'F27DB Introduction to Database Systems Lecture 2: Using Relational Databases' - gyan


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
f27db introduction to database systems lecture 2 using relational databases

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

recap
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

extending the spy database
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

the existing spy table
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

alterations to be made
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

e r diagram
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

the final tables
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

foreign keys
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

the final tables1
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

spy db
Spy DB

M

1

1

1

skilled at

has

M

manages

M

1

practised

by

1

has

1

1

Introduction to Databases

one to many relationships
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

one to one relationships
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

many to many relationships
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

many to many relationships1
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

many to many relationships2
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

creating the tables
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

auto increment
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

inserting values with auto increment
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

foreign key constraints
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

suitable order for creating spy tables
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

creating your database from a text file
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

text file verbose mode
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

retrieving data from 1 table
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

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

Introduction to Databases

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

Introduction to Databases

column name uniqueness
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

specifying table together with column name
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

less rows less columns
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

restricting rows
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

query 1 n
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

query 1 n1
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

using column aliases
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

query m n
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

maintaining data integrity
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

sql tutorial
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

what next
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