Lesson 2 Quiz:
Author Archives: benself
Quiz: Lesson 1
Quiz: Lesson 1
- Why do they call it a relational database?
A relational database in composed of tables. These tables link together to combine rich datasets. These database tables are created with rows and columns like in a spreadsheet. The rows are where your data is stored and the columns are the schema definition for the table itself. The reason the databases are relational is because tables have a unique primary key column that links to a foreign key column on another table. Thus the tables are relating to one another when writing queries with primary key to foreign key table relationship joins. - What is SQL?
SQL refers to the structured query language for relational databases. This language is standardized across many different database brands. This language allows creating – updating – deleting many different views of datasets in the database. - There are two predominant views into a relational database. What are they, and how are they different?
The two main views in a relational database are the schema and the data. Relational databases are composed of tables which are similar to a spreadsheet as a visualization. The schema is the columns in a table. Each of the columns specifies the data type for that column. The data is in the rows of the table. The data can be inserted, updated, viewed and deleted from one or many rows using the standardized SQL query language. - In a table, what do we call the column that serves as the main identifier for a row of data? We’re looking for the general database term, not the column name.
Primary key. - What is a foreign key, and how is it used?In a relational database the foreign key in a table is linked to the primary key of another table in a 1 to many or a many to many table relationship. As an example, the primary key value in say a users table stores a unique id value in a column for that users row. This is usually is an integer and is also a unique value for every row in the users table. In another table say the comments table, can have multiple rows that contain each comment the user creates. To find all the comments that a user creates, the comments table contains a foreign key field that contains the same value of the primary key column of the users table. This is a 1 to many relationship. i.e. each user can have multiple comments using this feature of a relational database.
- At a high level, describe the ActiveRecord pattern. This has nothing to do with Rails, but the actual pattern that ActiveRecord uses to perform its ORM duties.
The Active Record Pattern is a pattern that maps:- Classes to tables
- Objects to rows of data within that table
- Getters/Setters to columns in that table
- If there’s an ActiveRecord model called “CrazyMonkey”, what should the table name be?
# Use the tableize method on the string "CrazyMonkey" "CrazyMonkey".tableize => "crazy_monkeys"So the table name would be:
"crazy_monkeys" - If I’m building a 1:M association between
ProjectandIssue, what will the model associations and foreign key be?
Model Associations:class Project < ActiveRecord::Base has_many :issues end class Issue < ActiveRecord::Base belongs_to : project endThe foreign key for this association is “project_id” in the issues table.
- Given this code
class Zoo < ActiveRecord::Base has_many :animals end- What do you expect the other model to be and what does database schema look like?
class Animal < ActiveRecord::Base belongs_to :zoo end #migration for the animals table class CreateAnimals < ActiveRecord::Migration def change create_table :animals do |t| t.string :species t.string :name end end end # ooops class AddZoo < ActiveRecord::Migration def change add_column :animals, :zoo_id, :integer end end - What are the methods that are now available to a zoo to call related to animals?
zoo = Zoo.create(name: 'San Diego Zoo') animal = zoo.animals.create(species: 'Kangaroo') animal.name = 'jumpster' animal.save zoo.animals.name zoo.animals zoo animal = Animal.create(species: 'Alligator', name: 'Buster') zoo.animals << animal Animal.find_by name: 'jumpster' Animal.all - How do I create an animal called “jumpster” in a zoo called “San Diego Zoo”?
zoo = Zoo.create(name: 'San Diego Zoo') animal = zoo.animals.create(species: 'Kangaroo') animal.name = 'jumpster' animal.save
- What do you expect the other model to be and what does database schema look like?
- What is mass assignment? What’s the non-mass assignment way of setting values?Mass assignment is a way to assign multiple values to attributes via a single assignment operator.
# Mass assignment example animal = Animal.create(species: 'Turtle', name: 'Slow Mo') # Non mass assignment example animal = Animal.new animal.species = 'Turtle' animal.name = 'Slow Mo' animal.save - What does this code do?
Animal.first
It will return the first database row Animal object in the animals database table.
- If I have a table called “animals” with columns called “name”, and a model called
Animal, how do I instantiate an animal object with name set to “Joe”. Which methods makes sure it saves to the database?animal = Animal.new(name: 'Joe) animal.save animal = Animal.create(name: 'Joe') - How does a M:M association work at the database level?
With a Many to Many association the database will have a join table that contains foreign keys of two tables being joined. The foreign keys match the tables being joined, primary keys and creates a M to M relationship - What are the two ways to support a M:M association at the ActiveRecord model level? Pros and cons of each approach?
has_and_belongs_to_many
has_many :through
has_many :through has a join model and a join table added to this method but allows hanging additional attributes on to the join table itself. has_and_belongs_to_many does not require a join model but requires creating a join table in a manual fashion. You cannot add additional attributes to the join table in this method. - Suppose we have a
Usermodel and aGroupmodel, and we have a M:M association all set up. How do we associate the two?class User < ActiveRecord::Base has_many :user_groups, foreign_key: :user_id has_many :groups, through: :user_groups end class UserGroup < ActiveRecord::Base belongs_to :user, foreign_key: :user_id belongs_to :group, foreign_key: :group_id end class Group < ActiveRecord::Base has_many :user_groups, foreign_key: :group_id has_many :users, through: :user_groups end
Active Record
Active Record Models map an object’s attributes to columns in a relational database.
The Active Record Pattern is a pattern that maps:
- Classes to tables
- Objects to rows of data within that table
- Getters/Setters to columns in that table
Active Record uses naming conventions for the columns in database tables, depending on the purpose of these columns.
- Foreign keys – These fields should be named following the pattern
singularized_table_name_id(e.g.,item_id,order_id). These are the fields that Active Record will look for when you create associations between your models. - Primary keys – By default, Active Record will use an integer column named
idas the table’s primary key. When using Active Record Migrations to create your tables, this column will be automatically created.
This is how it works.
By default the model looks for a lower case plural name of the model
users database table:
| id | username | |
|---|---|---|
| 1 | joe | joe@yahoo.com |
| 2 | bob | bob@apple.com |
| 3 | jon | jon@yahoo.com |
user.rb ActiveRecord model:
# ActiveRecord model
class User < ActiveRecord:Base
endWhen we instantiate a user object, we automatically have getters and setters for all columns.
Now run these in the rails console …
# Creates an in memory User object:
user = User.new(username: 'bill')
These methods save to the database:
user.save
user = User.create(username: 'rick')
Retrieve column values:
user = User.find_by username: 'joe'
user.email
users = User.where(username: 'joe')Active Record Associations
Rails supports six types of associations:
- belongs_to
- has_one
- has_many
- has_many :through
- has_one :through
- has_and_belongs_to_many
Database Layer
This is a data view of one table:
This is a schema view of all the tables:
Model Layer
Let's work with the models in a 1 to M relationship
user = User.create(username: 'chip')
(0.1ms) begin transaction
SQL (7.1ms) INSERT INTO "users" ("created_at", "updated_at", "username") VALUES (?, ?, ?) [["created_at", Mon, 23 Mar 2015 20:39:39 UTC +00:00], ["updated_at", Mon, 23 Mar 2015 20:39:39 UTC +00:00], ["username", "chip"]]
(1.8ms) commit transaction
=> #<User id: 6, username: "chip", created_at: "2015-03-23 20:39:39", updated_at: "2015-03-23 20:39:39">
post = user.posts.create(title: 'Rails')
(1.2ms) begin transaction
SQL (5.7ms) INSERT INTO "posts" ("created_at", "title", "updated_at", "user_id") VALUES (?, ?, ?, ?) [["created_at", Mon, 23 Mar 2015 21:05:21 UTC +00:00], ["title", "Rails"], ["updated_at", Mon, 23 Mar 2015 21:05:21 UTC +00:00], ["user_id", 6]]
(1.6ms) commit transaction
=> #<Post id: 5, title: "Rails", url: nil, description: nil, created_at: "2015-03-23 21:05:21", updated_at: "2015-03-23 21:05:21", user_id: 6>
user.posts << post
(0.1ms) begin transaction
(0.1ms) commit transaction
=> #<ActiveRecord::Associations::CollectionProxy [#<Post id: 4, title: "Rails for dummies", url: nil, description: nil, created_at: "2015-03-23 20:45:05", updated_at: "2015-03-23 20:45:05", user_id: 6>, #<Post id: 5, title: "Rails", url: nil, description: nil, created_at: "2015-03-23 21:05:21", updated_at: "2015-03-23 21:05:21", user_id: 6>, #<Post id: 5, title: "Rails", url: nil, description: nil, created_at: "2015-03-23 21:05:21", updated_at: "2015-03-23 21:05:21", user_id: 6>]>
post.save
(0.1ms) begin transaction
(0.1ms) commit transaction
=> true
post.creator.username
=> "chip"
user.posts
=> #<ActiveRecord::Associations::CollectionProxy [#<Post id: 4, title: "Rails for dummies", url: nil, description: nil, created_at: "2015-03-23 20:45:05", updated_at: "2015-03-23 20:45:05", user_id: 6>, #<Post id: 5, title: "Rails", url: nil, description: nil, created_at: "2015-03-23 21:05:21", updated_at: "2015-03-23 21:05:21", user_id: 6>]>
Let's work with the models in a M to M through relationship
post = Post.find 1
Post Load (8.6ms) SELECT "posts".* FROM "posts" WHERE "posts"."id" = ? LIMIT 1 [["id", 1]]
=> <#Post id: 1, title: "Test Post", url: nil, description: "Description 1", created_at: "2015-03-18 23:13:21", updated_at: "2015-03-19 23:54:06", user_id: 5>
post.categories
Category Load (0.2ms) SELECT "categories".* FROM "categories" INNER JOIN "post_categories" ON "categories"."id" = "post_categories"."category_id" WHERE "post_categories"."post_id" = ? [["post_id", 1]]
=> #<ActiveRecord::Associations::CollectionProxy [#<Category id: 1, name: "Python", created_at: "2015-03-18 23:26:21", updated_at: "2015-03-18 23:26:21">]>
cat = Category.create(name: "Ruby Books")
(0.2ms) begin transaction
SQL (6.8ms) INSERT INTO "categories" ("created_at" "name", "updated_at") VALUES (?, ?, ?) [["created_at", Sun, 22 Mar 2015 08:40:06 UTC +00:00], ["name", "Ruby Books"], ["updated_at", Sun, 22 Mar 2015 08:40:06 UTC +00:00]]
(1.0ms) commit transaction
=> #<Category id: 2, name: "Ruby Books", created_at: "2015-03-22 08:40:06", updated_at: "2015-03-22 08:40:06"%gt;
post.categories << cat
(0.1ms) begin transaction
SQL (0.6ms) INSERT INTO "post_categories" ("category_id", "created_at", "post_id", "updated_at") VALUES (?, ?, ?, ?) [["category_id", 2], ["created_at", Sun, 22 Mar 2015 08:40:38 UTC +00:00], ["post_id", 1], ["updated_at", Sun, 22 Mar 2015 08:40:38 UTC +00:00]]
(0.8ms) commit transaction
=> #<ActiveRecord::Associations::CollectionProxy [#<Category id: 1, name: "Python", created_at: "2015-03-18 23:26:21", updated_at: "2015-03-18 23:26:21">, #<Category id: 2, name: "Ruby Books", created_at: "2015-03-22 08:40:06", updated_at: "2015-03-22 08:40:06">]>
cat.name
=> "Ruby Books"
MVC Request Response Diagram
Tableize Method
This is a convenient way to discover database names from model names. This is handy when you create a linking join table in a M to M relationship. Consider this model:
The tableize method will return the database table name to be used. The method uses the pluralize method to name the last word in the table name.
Tealeaf Academy – Finished First Course Today
I finished Tealeaf Academy’s – “Introduction to Ruby and Web Development” today. I really enjoyed the course and it gave me a solid foundation in the Ruby language it self. The instruction was way better than any tutorials I did that are all over the WWW. I will be posting some highlights and code examples I learned in the coming days. Some of my assignments were a tic-tack-toe game, a blackjack game – procedural, and object oriented in the console and a web version using the Sinatra web framework. Check it out here Blackjack.
Chip Self 