db-quizz-320

db-quizz-320

# Quiz correction - DB and ActiveRecord

### Q1 - What’s a relational database?
ensemble de tables reliées par des clés primaires/étrangères

### Q2 - What are the different “table relationships” you know?
1:N One to many
N:N Many to many => table de jointure

### Q3 - Consider this e-library service. An author, defined by his name have several books. A book, defined by its title and publishing year, has one author only. What’s this simple database scheme. Please draw it!


### Q4 - A user, defined by his email, can read several books. A book (e-book!!) can be read by several user. We also want to keep track of reading dates. Improve your e-library DB scheme with relevant tables and relationships.


### Q5 - What’s the language to make queries to a database?
SQL (Structured Query Language)

### Q6 - What’s the query to get books written before 1985?
SELECT * FROM books WHERE publishing_year <= 1985

### Q7 - What’s the query to get the 3 most recent books written by Jules Verne?
SELECT * FROM books b
JOIN authors a ON b.author_id = a.id
WHERE a.name = 'Jules Verne'
ORDER BY b.publishing_year DESC
LIMIT 3;

### Q8 - What’s the purpose of ActiveRecord?
mapping des enregistrement en BDD avec nos modèles
méthodes pour faire des requêtes en ruby


### Q9 - What’s a migration? How do you run a migration?
script en ruby pour modifier le schéma, la structure de la BDD (pas la donnée)

```ruby
rake db:migrate
```

### Q10 - Complete migrations to create your e-library database

```ruby
class CreateAuthors < ActiveRecord::Migration[5.1]
  def change
    create_table :authors do |t|
        t.string :name
        t.timestamps
    end
  end
end
```

```ruby
class CreateBooks < ActiveRecord::Migration[5.1]
  def change
    create_table :books do |t|
        t.string :title
        t.integer :publishing_year
        t.references :author, foreign_key: true, index: true
        t.timestamps
    end
  end
end
```

```ruby
class CreateUsers < ActiveRecord::Migration[5.1]
  def change
    create_table :users do |t|
        t.string :email
        t.timestamps
    end
  end
end
```

```ruby
class CreateReadings < ActiveRecord::Migration[5.1]
  def change
    create_table :readings do |t|
        t.date :date
        t.references :user, foreign_key: true, index: true
        t.references :book, foreign_key: true, index: true
    end
  end
end
```


### Q11 - Write a migration to add a category column to the books table.

```ruby
class AddCategoryToBooks < ActiveRecord::Migration[5.1]
  def change
    add_column :books, :category, :string
  end
end
```


### Q12 - Define an ActiveRecord model for each table of your DB. Add the ActiveRecord associations between models.

```ruby
class Author < ActiveRecord::Base
    has_many :books
end
```

```ruby
class Book < ActiveRecord::Base
    belongs_to :author
    has_many :readings
    # a_book.readings.map { |reading| reading.user}
    has_many :users, through: :readings
    # a_book.users
end
```

```ruby
class User < ActiveRecord::Base
    has_many :readings
    has_many :books, through: :readings
    # a_user.books
end
```

```ruby
class Reading < ActiveRecord::Base
    belongs_to :user
    belongs_to :book
end
```



### Q13 - Complete the following code using the relevant ActiveRecord methods.

```ruby
# 1. Add your favorite author to the DB
zola = Author.new(name: "Emile Zola")
zola.save
# Same as
Author.create(name: "Emile Zola")

# 2. Get all authors
Author.all

# 3. Get author with id=8
Author.find(8)

# 4. Get author with name="Jules Verne", store it in a variable: jules
jules = Author.find_by(name: "Jules Verne")
Author.find_by_name("Jules Verne")

# 5. Get Jules Verne's books
jules.books

# 6. Create a new book "20000 Leagues under the Seas", it's missing in DB.
# Store it in a variable: twenty_thousand
twenty_thousand = Book.new(
    title: "20000 Leagues under the Seas",
    publishing_year: 1900
)

# 7. Add Jules Verne as this book's author
twenty_thousand.author = jules

# 8. Now save this book in the DB!
twenty_thousand.save

# The end
```

### Q14 - Add validations of your choice to the Author class. Can we save an object in DB if its validations do not pass?

```ruby
class Author < ActiveRecord::Base
    has_many :books

    validates :name, presence: true
end
```
bad_author = Author.new
bad_author.name # => nil
bad_author.save # => false
bad_author.save! # => Error
bad_author.valid? # => false
bad_author.errors.messages # => 'name can't be blank'