Relational Databases and SQLite

Relational Databases and SQLite

Relational Databases and SQLite Charles Severance Python for Everybody www.py4e.com/lectures3/ SQLite Browser

http://sqlitebrowser.org/ OLD Sorted Sequential Master Update 1970s

NEW Sorted Merg e Transactio ns

Sorted https://en.wikipedia.org/wiki/IBM_729 Random Access When you can randomly access data... How can you layout data to be most efficient?

Sorting might not be the best idea https://en.wikipedia.org/wiki/Hard_disk_drive_platter Relational Databases Relational databases model data by storing rows and columns in tables. The power of the relational database lies in its ability to efficiently retrieve data from those tables and in particular where there are

multiple tables and the relationships between those tables involved in the query. http://en.wikipedia.org/wiki/Relational_database Terminology Database - contains many tables Relation (or table) - contains tuples and attributes Tuple (or row) - a set of fields that generally represents an object like a person or a music track

Attribute (also column or field) - one of possibly many elements of data corresponding to the object represented by the row A relation is defined as a set of tuples that have the same attributes. A tuple usually represents an object and information about that object. Objects are typically physical objects or concepts. A relation is usually described as a table, which is organized into rows and columns. All the data referenced by an attribute are in the same domain and conform to the same constraints. (Wikipedia)

Columns / Attributes Rows / Tuples Tables / Relations SQL Structured Query Language is the language we use to issue commands to the database

- Create data (a.k.a Insert) - Retrieve data - Update data - Delete data http://en.wikipedia.org/wiki/SQL Input Files

Python Programs R Excel Database File

SQL Output Files You D3.js SQL

SQLite Browser Web Applications w/ Databases Application Developer - Builds the logic for the application, the look and feel of the application - monitors the application for problems Database Administrator - Monitors and adjusts the database as the program runs in production

Often both people participate in the building of the Data model End User Application Software SQL

Database Data Server SQL Developer DBA Database

Tools Database Administrator A database administrator (DBA) is a person responsible for the design, implementation, maintenance, and repair of an organizations database. The role includes the development and design of database strategies, monitoring and improving database performance and capacity, and planning for future expansion requirements. They may also plan, coordinate, and

implement security measures to safeguard the database. http://en.wikipedia.org/wiki/Database_administrator Database Model A database model or database schema is the structure or format of a database, described in a formal language supported by the database management system. In other words, a database model is the application of a data model when used in conjunction with a database

management system. http://en.wikipedia.org/wiki/Database_model Common Database Systems Three major Database Management Systems in wide use - Oracle - Large, commercial, enterprise-scale, very very tweakable - MySql - Simpler but very fast and scalable - commercial open source - SqlServer - Very nice - from Microsoft (also Access) Many other smaller projects, free and open source

- HSQL, SQLite, Postgres, ... SQLite is in Lots of Software... http://www.sqlite.org/famous.html SQLite Browser SQLite is a very popular database - it is free and fast and small SQLite Browser allows us to directly manipulate SQLite files

http://sqlitebrowser.org/ SQLite is embedded in Python and a number of other languages http://sqlitebrowser.org/ Lets Make a Database https://www.py4e.com/lectures3/Pythonlearn-15-Database-Handout.txt Start Simple - A Single Table

CREATE TABLE Users( name VARCHAR(128), email VARCHAR(128) ) Our table with four rows SQL Structured Query Language is the language we use to issue

commands to the database - Create data (a.k.a Insert) - Retrieve data - Update data - Delete data http://en.wikipedia.org/wiki/SQL SQL: Insert The Insert statement inserts a row into a table

INSERT INTO Users (name, email) VALUES ('Kristin', '[email protected]') SQL: Delete Deletes a row in a table based on selection criteria DELETE FROM Users WHERE email='[email protected]' SQL: Update

Allows the updating of a field with a where clause UPDATE Users SET name='Charles' WHERE email='[email protected]' Retrieving Records: Select The select statement retrieves a group of records - you can either retrieve all the records or a subset of the records with a WHERE clause

SELECT * FROM Users SELECT * FROM Users WHERE email='[email protected]' Sorting with ORDER BY You can add an ORDER BY clause to SELECT statements to get the results sorted in ascending or descending order SELECT * FROM Users ORDER BY email

SELECT * FROM Users ORDER BY name DESC SQL Summary INSERT INTO Users (name, email) VALUES ('Kristin', '[email protected]') DELETE FROM Users WHERE email='[email protected]' UPDATE Users SET name="Charles" WHERE email='[email protected]' SELECT * FROM Users SELECT * FROM Users WHERE email='[email protected]' SELECT * FROM Users ORDER BY email

This is not too exciting (so far) Tables pretty much look like big fast programmable spreadsheets with rows, columns, and commands The power comes when we have more than one table and we can exploit the relationships between the tables Complex Data Models and Relationships

http://en.wikipedia.org/wiki/Relational_model Database Design Database design is an art form of its own with particular skills and experience Our goal is to avoid the really bad mistakes and design clean and easily understood databases Others may performance tune things later Database design starts with a picture...

Building a Data Model Drawing a picture of the data objects for our application and then figuring out how to represent the objects and their relationships Basic Rule: Dont put the same string data in twice - use a relationship instead When there is one thing in the real world there should be one copy of that thing in the database

Track Len Artist Album Genre Rating Count For each piece of info...

Is the column an object or an attribute of another object? Once we define objects, we need to define the relationships between objects Len Album Genre

Artist Track Rating Count Track Album

Artist Genre Rating Len Count Track Album Artist

Genre Track Artist belongs-to Album belongs-to

Rating Len Count Rating Len Count Genre

belongs-to Track Artist belongs-to Album belongs-to

Genre Rating Len Count belongs-to Representing Relationships

in a Database Database Normalization (3NF) There is *tons* of database theory - way too much to understand without excessive predicate calculus Do not replicate data - reference data - point at data Use integers for keys and for references Add a special key column to each table which we will make references to. By convention, many programmers call this

column id http://en.wikipedia.org/wiki/Database_normalization We want to keep track of which band is the creator of each music track... What album does this song belong to?? Which album is this song related to? Integer Reference Pattern Artist

We use integers to reference rows in another table Album Three Kinds of Keys Primary key - generally an integer autoincrement field Logical key - What the outside world uses for lookup

Foreign key - generally an integer key pointing to a row in another table Album id title artist_id ...

Key Rules Best practices Never use your logical key as the primary key Logical keys can and do change, albeit slowly Relationships that are based on matching string fields are less efficient than integers

User id login password name email created_at modified_at login_at

Foreign Keys A foreign key is when a table has a column that contains a key which points to the primary key of another table. When all primary keys are integers, then all foreign keys are integers - this is good - very good

Artist id name ... Album id title

artist_id ... Relationship Building (in tables) Track Rating Len Count

Artist belongs-to Album belongs-to Genre

belongs-to belongs-to Album Album Table Primary key Logical key

Foreign key id title Track Title Rating Len

Count Track id title rating len count album_id

Artist id name Table Primary key Logical key Foreign key Naming FK artist_id is a

convention Track Album id title artist_id Genre id

name id title rating len count album_id genre_id

CREATE TABLE Genre ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, name TEXT ) CREATE TABLE Album (

id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, artist_id INTEGER, title TEXT ) CREATE TABLE Track ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, title TEXT, album_id INTEGER,

genre_id INTEGER, len INTEGER, rating INTEGER, count INTEGER ) insert into Artist (name) values ('Led Zepplin') insert into Artist (name) values ('AC/DC') insert into Artist (name) values ('Led Zepplin') insert into Artist (name) values ('AC/DC')

insert into Genre (name) values ('Rock') insert into Genre (name) values ('Metal') insert into Album (title, artist_id) values ('Who Made Who', 2) insert into Album (title, artist_id) values ('IV', 1) insert into values

insert into values insert into values insert into values Track (title, rating, len, count, album_id, ('Black Dog', 5, 297, 0, 2, 1)

Track (title, rating, len, count, album_id, ('Stairway', 5, 482, 0, 2, 1) Track (title, rating, len, count, album_id, ('About to Rock', 5, 313, 0, 1, 2) Track (title, rating, len, count, album_id, ('Who Made Who', 5, 207, 0, 1, 2) genre_id) genre_id)

genre_id) genre_id) Track Album Genre Artist

Using Join Across Tables http://en.wikipedia.org/wiki/Join_(SQL) Relational Power By removing the replicated data and replacing it with references to a single copy of each bit of data we build a web of information that the relational database can read through very quickly - even

for very large amounts of data Often when you want some data it comes from a number of tables linked by these foreign keys The JOIN Operation The JOIN operation links across several tables as part of a select operation You must tell the JOIN how to use the keys that make the connection between the tables using an ON clause

Album Artist select Album.title, Artist.name from Album join Artist on Album.artist_id = Artist.id What we want to see

The tables that hold the data How the tables are linked select Album.title, Album.artist_id, Artist.id,Artist.name from Album join Artist on Album.artist_id = Artist.id

SELECT Track.title, Track.genre_id, Genre.id, Genre.name FROM Track JOIN Genre Joining two tables without an ON clause gives all possible combinations of rows.

select Track.title, Genre.name from Track join Genre on Track.genre_id = Genre.id What we want to see The tables that hold the data

How the tables are linked select Track.title, Artist.name, Album.title, Genre.name from Track join Genre join Album join Artist on Track.genre_id = Genre.id and Track.album_id = Album.id and Album.artist_id = Artist.id What we want to see The tables which hold

the data How the tables are linked Many-To-Many Relationships https://en.wikipedia.org/wiki/Many-to-many_(data_model) belongs-to

Track Title Rating Len Count Album One

Table Primary key Logical key Foreign key Many Album

id title Review: One to Many Track id

title One https://en.wikipedia.org/wiki/One-to-many_(data_model) Many rating

len count album_id One One Many

https://en.wikipedia.org/wiki/One-to-many_(data_model) Many Many to Many Sometimes we need to model a relationship that is many-to-many We need to add a "connection" table with two foreign keys

There is usually no separate primary key https://en.wikipedia.org/wiki/Many-to-many_(data_model) member-of Course title Course

id title Many Many User name

email User Member Many One user_id

course_id Many https://en.wikipedia.org/wiki/Many-to-many_(data_model) One id

name email CREATE TABLE User ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, name TEXT UNIQUE, email TEXT

) CREATE TABLE Course ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, title TEXT UNIQUE ) CREATE TABLE Member ( user_id INTEGER,

course_id INTEGER, role INTEGER, PRIMARY KEY (user_id, course_id) ) Start with a Fresh Database

Insert Users and Courses INSERT INTO User (name, email) VALUES ('Jane', '[email protected]'); INSERT INTO User (name, email) VALUES ('Ed', '[email protected]'); INSERT INTO User (name, email) VALUES ('Sue', '[email protected]'); INSERT INTO Course (title) VALUES ('Python'); INSERT INTO Course (title) VALUES ('SQL'); INSERT INTO Course (title) VALUES ('PHP');

INSERT INTO Member (user_id, course_id, role) VALUES (1, 1, 1); INSERT INTO Member (user_id, course_id, role) VALUES (2, 1, 0); INSERT INTO Member (user_id, course_id, role) VALUES (3, 1, 0); INSERT INTO Member (user_id, course_id, role) VALUES (1, 2, 0); INSERT INTO Member (user_id, course_id, role) VALUES (2, 2, 1); INSERT INTO Member (user_id, course_id, role) VALUES (2, 3, 1); INSERT INTO Member (user_id, course_id, role) VALUES (3, 3, 0); SELECT User.name, Member.role, Course.title

FROM User JOIN Member JOIN Course ON Member.user_id = User.id AND Member.course_id = Course.id ORDER BY Course.title, Member.role DESC, User.name www.tsugi.org Complexity Enables Speed Complexity makes speed possible and allows you to get very fast

results as the data size grows By normalizing the data and linking it with integer keys, the overall amount of data which the relational database must scan is far lower than if the data were simply flattened out It might seem like a tradeoff - spend some time designing your database so it continues to be fast when your application is a success Additional SQL Topics

Indexes improve access performance for things like string fields Constraints on data - (cannot be NULL, etc..) Transactions - allow SQL operations to be grouped and done as a unit Summary Relational databases allow us to scale to very large amounts of data The key is to have one copy of any data element and use relations

and joins to link the data to multiple places This greatly reduces the amount of data which much be scanned when doing complex operations across large amounts of data Database and SQL design is a bit of an art form Acknowledgements / Contributions These slides are Copyright 2010- Charles R. Severance ( www.dr-chuck.com) of the University of Michigan School of Information and open.umich.edu and made available under a

Creative Commons Attribution 4.0 License. Please maintain this last slide in all copies of the document to comply with the attribution requirements of the license. If you make a change, feel free to add your name and organization to the list of contributors on this page as you republish the materials. Initial Development: Charles Severance, University of Michigan School of Information Insert new Contributors here

...

Recently Viewed Presentations

  • Bonds - Mrs. Storer Science Website

    Bonds - Mrs. Storer Science Website

    If it is +2 it is a (II) after the name of the metal. If it is a +3, you put a (III) after the name of the metal. You include this roman numeral in the name ONLY for metals...
  • Back to School Night - Jenks Public Schools

    Back to School Night - Jenks Public Schools

    year of teaching. Before moving to Jenks . I . taught kindergarten at an elementary school in Owasso. I attended Northeastern State University and graduated with a Bachelor's in Early Childhood Education. I enjoy running, cooking, and spending time with...
  • Points Lines and Planes Unit 1 Lesson 2

    Points Lines and Planes Unit 1 Lesson 2

    Points Lines and Planes. In geometry, some words, such as point, line, and plane, are undefined terms.Although these words are not formally defined, it is important to have general agreement about what each word means.
  • Microsoft Outlook - Samuel Merritt University

    Microsoft Outlook - Samuel Merritt University

    Outlook is a personal information manager from Microsoft, available as a part of the Microsoft Office suite. Although often used mainly as an email application, it also includes a calendar, task manager, contact manager, note taking, journal, and web browsing...
  • MISSION TO SUPPORT THE FIGHT AGAINST CORRUPTION AND

    MISSION TO SUPPORT THE FIGHT AGAINST CORRUPTION AND

    Additionally, worked on guidelines and recommendations for a mechanism for business regulation and certification of "anti-corruption compliance" in Honduras and negotiations were facilitated with the main Honduran business associations for their implementation. Follow up on initiatives presented in 2017-2018 that...
  • Edkaagmik Nbiizh Neyaashiinigamiingninwag Edbendaagzijig Trust

    Edkaagmik Nbiizh Neyaashiinigamiingninwag Edbendaagzijig Trust

    The proposal application process was reviewed and further improvements were made with the aid of a subject matter expert (Marilyn Struthers, Ontario Trillium Foundation) The Trustees provided the community with a proposal workshop and webinar
  • Cancer de Maman

    Cancer de Maman

    My name is Maria de Lourdes Alana Chavez and I'm 63 years old, from Tepic. I found out about the clinic from staff where I've been getting chemotherapy and soon will take radiation therapy.
  • Economic Systems of the Middle East - Henry County School ...

    Economic Systems of the Middle East - Henry County School ...

    Economic Systems of the Middle East There are many different types of economic systems in Southwest Asia. Many countries have mixed economies with different levels of government control. Some countries are less developed than others in the region. Southwest Asian...