135 SQL basics [2.5d]

Updated . Posted . Visible to the public.

SQL is the language used to talk to a database. This lesson covers reading and changing data, joins, indexes and transactions — and the craft of turning prose requirements into a database schema.

Important

Work on this lesson in teacher mode.

Learning goals

  • You can explain what a relational database is and how an application talks to it (server, client, credentials, a console).
  • You can query data with SQL — selecting, filtering, sorting, limiting, grouping with aggregates, joining tables — and explain when to use which kind of join.
  • You can change data with SQL and explain why you'd wrap several statements in a transaction.
  • You can explain what an index is, when a query can use one, and why nearly every read query in our apps should be backed by one.
  • You can check how the database executes a query (e.g. with EXPLAIN ANALYZE).
  • You can read a Rails schema file and recognize ActiveRecord's naming conventions for tables and keys.
  • You can turn prose requirements into a database schema with as few tables as necessary, and present it as an ER diagram or schema definition.
  • You can get a copy of a staging database onto your machine.

Resources

Read what's new to you, skim what's familiar, skip what you already master. Stop when you can meet the learning goals.

Your agent can also generate an overview, a tutorial or an explanation for anything here, tailored to what you already know. Just ask.

Pick one introduction to SQL

Joins, indexes and transactions

Rails and reference

Exercises

ActiveRecord schema conventions

The file db/schema.rb is useful get a first impression of a Rails app's database model. It contains a compact description of all tables and columns in the app's database:

ActiveRecord::Schema[8.0].define(version: 2021_07_05_075620) do

  create_table "bookings", force: :cascade do |t|
    t.bigint "desk_id"
    t.bigint "user_id"
    t.datetime "start_time"
    t.datetime "end_time"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["desk_id"], name: "index_bookings_on_desk_id"
    t.index ["user_id"], name: "index_bookings_on_user_id"
  end

  create_table "desks", force: :cascade do |t|
    t.string "code"
    t.text "notes"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.boolean "bookable"
    t.bigint "room_id"
    t.index ["bookable"], name: "index_desks_on_bookable"
    t.index ["code"], name: "index_desks_on_code"
    t.index ["room_id"], name: "index_desks_on_room_id"
  end

end

Each of the create_table blocks corresponds to a CREATE TABLE Show archive.org snapshot statement that can be used to re-create the database structure.

Tip

Most Rails apps follow ActiveRecord conventions Show archive.org snapshot when designing their table and columns. Because there is a standard way of modelling and naming things, it's easy to guess how a Rails app stores its data.

Browse through the schema.rb of all sample apps that you checked out at the beginning of this curriculum. Just by looking at the table and column names you should get an idea what these applications do.

SQL console

In an earlier lesson you cloned the code for the makandra cards app.

Ask your mentor for a dump of the staging database — trainees don't get server access. Then import it into your local database; there is a geordi task Show archive.org snapshot to help you with that.

Start the server and sign up as a new user.

Now do the following things in a database console:

  • Find the user table
  • Which columns does it have? Which indexes?
  • Change the name of your user
  • Add yourself to another deck
  • Find out how many users there are
  • Find out which user has received how many "thanks" ("tributes") with a single query
  • Now do the same query only for the "makandropedia" deck
  • Does this query use an index? Check with EXPLAIN ANALYZE. If it doesn't, could we add one to speed it up?

Relational modelling

Come up with a database schema for the fictional apps below. If you don't know how the original apps work (e.g. Trello), look it up.

To describe the schema you can use any format that your mentor understands. For instance, an ER diagram Show archive.org snapshot , the format of schema.rb above, or a set of CREATE TABLE statements.

It is sufficient to only use the most essential record properties for this exercise. E.g. in most cases it is enough for a user to have a name, e-mail and password (hash), even though in a real schema there may be many more columns.

A schema is easier to use the fewer tables it has. Try to minimize the number of tables you need.

Trello light

  • Users can sign in.
  • Boards with customizable columns.
  • A board can have many Cards.
  • Users can be invited to boards for access. Board invites must be accepted by the recipient.

Facebook light

  • Users can sign in.
  • Users can become "Facebook friends" with other users. Friendship requests must be accepted by the recipient.
  • Users can write posts.
  • Posts are only visible for friends.

GMail light

  • Users can sign in.
  • Users can read and write e-mails.
  • E-mails can be saved as drafts before sending.
  • E-mails can be assigned tags.
  • Commonly used recipients can be saved in an address book.
Profile picture of Henning Koch
Henning Koch
makandra.de
Say thanks1
Last edit
Michael Leimstädtner
License
Source code in this card is licensed under the MIT License.
Posted by Henning Koch to makandra Curriculum (2015-07-07 15:06)
This website uses short-lived cookies to improve usability.
or learn more