
Lesson 5
Task
Suppose you want to create a small database for a library.
For each book we want to keep track of its title, author, genre and publication year.
For all authors you also want to keep track of the following details: name, surname, sex,
whether they are alive or not and -if alive - how old they are.
Conceptual data model: ER
Note.
The age attribute is a calculated attribute and, for this reason, it would be an error to introduce it in db. Therefore, we use the dateOfBirth attribute.
Logical data model: relational
author (id, name, surname, sex, dateOfBirth, alive)
book (id, title, genre, year, idAuthor)
Note.
In a logical data model, a relationship 1:N is represented by adding a foreign key to the relation which maps the entity on the N side of the relationship.
AN INSTANCE OF DATABASE
author | |||||
id | name | surname | sex | dateOfBirth | alive |
---|---|---|---|---|---|
1 | Niccolò | Ammaniti | M | 25/09/1966 | true |
2 | William | Shakespeare | M | 26/04/1564 | false |
3 | Fabio | Volo | M | 23/06/1972 | true |
book | ||||
id | title | genre | year | idAuthor |
---|---|---|---|---|
4 | Esco a fare due passi | novel | 2001 | 3 |
5 | Un posto nel mondo | novel | 2006 | 3 |
6 | Il giorno in più | novel | 2007 | 3 |
7 | La strada verso casa | novel | 2013 | 3 |
8 | Hamlet | tragedy | 1603 | 2 |
9 | Midnight's Summer Dream | comedy | 1596 | 2 |
10 | Macbeth | tragedy | 1623 | 2 |
11 | Romeo and Juliet | tragedy | 1597 | 2 |
12 | Othello | tragedy | 1622 | 2 |
13 | Io non ho paura | novel | 2003 | 1 |
14 | Io e te | novel | 2012 | 1 |
15 | Come Dio comanda | novel | 2008 | 1 |
16 | L'ultimo capodanno | novel | 1998 | 1 |
query - 1
Retrieve a list of all the novels stored in the database.
Extract a list of the titles of all the novels and the name and surname of their authors.
from book, author
where (book.idAuthor = author.id) and (book.genre = "novel")
novel | name | surname |
---|---|---|
Esco a fare due passi | Fabio | Volo |
Un posto nel mondo | Fabio | Volo |
Il giorno in più | Fabio | Volo |
La strada verso casa | Fabio | Volo |
Io non ho paura | Niccolò | Ammaniti |
Io e te | Niccolò | Ammaniti |
Come Dio comanda | Niccolò | Ammaniti |
L'ultimo capodanno | Niccolò | Ammaniti |
query - 2
Extract a list of the works by living authors. Display also the name and surname of the authors.
from book, author
where (book.idAuthor = author.id) and (author.alive = true)
work | name | surname |
---|---|---|
Esco a fare due passi | Fabio | Volo |
Un posto nel mondo | Fabio | Volo |
Il giorno in più | Fabio | Volo |
La strada verso casa | Fabio | Volo |
Io non ho paura | Niccolò | Ammaniti |
Io e te | Niccolò | Ammaniti |
Come Dio comanda | Niccolò | Ammaniti |
L'ultimo capodanno | Niccolò | Ammaniti |
query - 3
Retreive a list ordered by year in ascending order of all the books published between 1600 and 2000.
from book
where (year >= 1600) and (year <= 2000)
order by year
title | year |
---|---|
Amleto | 1603 |
Othello | 1622 |
Macbeth | 1623 |
L'ultimo capodanno | 1998 |
query - 4
Extract a list ordered alphabetically of all the authors (name and surname) who wrote a book between 1600 and 2000.
from author as a, book as b
where (a.id = b.idAuthor) and (b.year >= 1600) and (b.year <= 2000)
order by a.surname, a.name
a.surname | a.name |
---|---|
Ammaniti | Niccolò |
Shakespeare | William |
Note.
The keyword as can also be used to rename a table which is involved in a query.
query - 5
Extract all data for women writers.
from book as b, author as a
where (a.id = b.idAuthor) and (a.sex = "F")
id | title | genre | year | idAuthor |
---|
Challenge
Challenge - 1
What is the result of the select query below?
from author
where (name = "Fabio")
order by surname
Challenge - 2
What is the result of the select query below?
from author as a, book as b
where (a.id = b.idAuthor)
order by b.title
Challenge - 3
What is the result of the select query below?
from author as a, book as b
where (a.id = b.idAuthor) and (a.surname = "Ammaniti")
order by b.year
Challenge - 4
Create a query which will match surname, name and date of birth of all the authors in the DB with the publication year of all their books.
Challenge - 5
Find a list of all the genres stored in the DB.
Challenge - 6
Create a query which will generate a table displaying in descending order the publication year of the books by living authors.
Challenge - 7
Create a query which will generate a table displaying the title and author of all the works which are not tragedies.