Software Carpentry logo

Databases

April 24, 2010: We are pleased to announce that Version 4 of this course is now under development. For updates and an early peek at the content, please check out the Software Carpentry blog at http://www.software-carpentry.org/blog/.

1) Introduction

2) You Can Skip This Lecture If...

3) History

4) When To Use A Database

5) Getting Started

Database Tables

Figure 17.1: Database Tables

6) Using SQL

Interacting with a DBMS

Figure 17.2: Interacting with a DBMS

7) Creating Tables

CREATE TABLE Person(
	Login		TEXT,
	LastName	TEXT,
	FirstName	TEXT
);

INSERT INTO Person VALUES("skol",   "Kovalevskaya", "Sofia");
INSERT INTO Person VALUES("mlom",   "Lomonosov",    "Mikhail");
INSERT INTO Person VALUES("dmitri", "Mendeleev",    "Dmitri");
INSERT INTO Person VALUES("ivan",   "Pavlov",       "Ivan");

8) Simple Queries

SELECT Person.FirstName, Person.LastName, Person.Login FROM Person;

Sofia|Kovalevskaya|skol
Mikhail|Lomonosov|mlom
Dmitri|Mendeleev|dmitri
Ivan|Pavlov|ivan

9) Sorting

SELECT Person.FirstName, Person.LastName, Person.Login
FROM Person
ORDER BY Person.Login;

Dmitri|Mendeleev|dmitri
Ivan|Pavlov|ivan
Mikhail|Lomonosov|mlom
Sofia|Kovalevskaya|skol

10) Selection

SELECT Experiment.ProjectId, Experiment.ExperimentId, Experiment.Hours
FROM Experiment
WHERE Experiment.Hours < 0;

1737|1|-1.0
1737|2|-1.5

11) Joins

Inner Joins

Figure 17.3: Inner Joins

12) Example: Translating IDs

SELECT Project.ProjectName, Experiment.ExperimentId, Experiment.Hours
FROM Project INNER JOIN Experiment
WHERE (Project.ProjectId = Experiment.ProjectId)
  AND (Experiment.Hours < 0);

Time Travel|1|-1.0
Time Travel|2|-1.5

13) Keys and Constraints

CREATE TABLE Person(
	Login		TEXT		NOT NULL,
	LastName	TEXT	        NOT NULL,
	FirstName	TEXT	        NOT NULL,
	PRIMARY KEY (Login)
);

CREATE TABLE Experiment(
	ProjectId	INTEGER		NOT NULL,
	ExperimentId	INTEGER		NOT NULL,
	NumInvolved	INTEGER		NOT NULL,
	ExperimentDate	DATE,
	Hours		REAL		NOT NULL
	CONSTRAINT Experiment_Key PRIMARY KEY (ProjectId, ExperimentId)
);

14) Eliminating Duplicates

SELECT Project.ProjectName, Involved.Login
FROM   Project, Involved
WHERE  Project.ProjectId = Involved.ProjectId;

Antigravity|mlom
Antigravity|mlom
Teleportation|dmitri
Teleportation|skol
Teleportation|ivan
Teleportation|mlom
Time Travel|skol
Time Travel|skol
Time Travel|ivan

SELECT DISTINCT Project.ProjectName, Involved.Login
FROM            Project, Involved
WHERE           Project.ProjectId = Involved.ProjectId;

Antigravity|mlom
Teleportation|dmitri
Teleportation|skol
Teleportation|ivan
Teleportation|mlom
Time Travel|skol
Time Travel|ivan

15) Aggregation

SELECT SUM(Experiment.Hours)
FROM   Involved INNER JOIN Experiment
WHERE  (Involved.Login = "mlom")
  AND  (Involved.ProjectId = 1214)
  AND  (Involved.ProjectId = Experiment.ProjectId)
  AND  (Involved.ExperimentId = Experiment.ExperimentId);

15.8

16) Grouping

SELECT   Involved.Login, SUM(Experiment.Hours)
FROM     Involved INNER JOIN Experiment
WHERE    (Involved.ProjectId = Experiment.ProjectId)
  AND    (Involved.ExperimentId = Experiment.ExperimentId)
GROUP BY Involved.Login;

dmitri|7
ivan|5.5
mlom|23.0
skol|4.5

17) Self Joins

SELECT DISTINCT Person.Login
FROM            Person INNER JOIN Involved
WHERE           (ProjectId = 1214) AND (ProjectId = 1709);


  
  
SELECT DISTINCT Person.Login
FROM            Person INNER JOIN Involved
WHERE           (ProjectId = 1214) OR (ProjectId = 1709);


  
skol
mlom
dmitri
ivan


  
  
skol
mlom
dmitri
ivan







  
18) Using Self Joins

  
  
SELECT DISTINCT A.Login
FROM            Involved A CROSS JOIN Involved B
WHERE           (A.Login = B.Login)
AND             (A.ProjectId != B.ProjectId);


  
mlom
skol
ivan







  
19) Who Has Worked Together?

  
  
SELECT DISTINCT A.Login, B.Login
FROM            Involved A CROSS JOIN Involved B
WHERE           (A.ProjectId = B.ProjectId)
AND             (A.ExperimentId = B.ExperimentId)
AND             (A.Login != B.Login);







  
20) Null

  





  
21) Operations on Nulls

  





  
22) Managing Nulls

  





  
23) Database Design

  





  
24) First Normal Form

  





  
25) Second Normal Form

  





  
26) Nested Queries

  
  
SELECT DISTINCT Involved.Login
FROM            Involved
WHERE           (Involved.ProjectId != 1737);


  
mlom
dmitri
skol
ivan


  





  
27) Nested Query Example

  
  
SELECT DISTINCT Login
FROM            Involved
WHERE Login NOT IN
  (SELECT DISTINCT Login
   FROM            Involved
   WHERE           Involved.ProjectId = 1737);


  
mlom
dmitri







  
28) More Uses for Nested Queries

  
  
SELECT DISTINCT Login
FROM            Involved
WHERE Login NOT IN
  (SELECT DISTINCT A.Login
   FROM Involved A INNER JOIN Involved B
   WHERE (A.Login = B.Login)
   AND   (A.ProjectId != B.ProjectId));


  
dmitri







  
29) Using Other Languages

  
  

    Using Databases from Programs
    
Figure 17.5: Using Databases from Programs

  






  
30) Example: Database Access from Python

  
  
from pysqlite2 import dbapi2 as sqlite

connection = sqlite.connect("example.db")
cursor = connection.cursor()
cursor.execute("SELECT FirstName, LastName FROM Person ORDER BY LastName;")
results = cursor.fetchall();
for r in results:
    print r
cursor.close();
connection.close();


  
("Sofia", "Kovalevskaya")
("Mikhail", "Lomonosov")
("Dmitri", "Mendeleev")
("Ivan", "Pavlov")







  
31) Concurrency

  





  
32) Transactions

  





  
33) Example: Changing User ID

  
  
BEGIN TRANSACTION;

  UPDATE Person
  SET    Login = "kovalev"
  WHERE  Login = "skol";

  UPDATE Involved
  SET    Login = "kovalev"
  WHERE  Login = "skol";

END TRANSACTION;

SELECT *
FROM   Person
WHERE  (Login = "kovalev") OR (Login = "skol");

SELECT *
FROM   Involved
WHERE  (Login = "kovalev") OR (Login = "skol");


  
kovalev|Kovalevskaya|Sofia
1709|1|2|kovalev
1737|1|1|kovalev
1737|2|1|kovalev


  





  
34) Using Transactions

  





  
35) Testing

  





  
36) Advanced Topics

  





  
37) Summary

  





  

  
Copyright © 2005-09 Python Software Foundation.
Created Thu Aug  6 21:56:06 2009 UTC