Database design: relationship 1:N
Query: keyword AS

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

Layer 1 name surname sex (...) dateOfBirth AUTHOR title genre(...) year BOOK WRITE 1 N alive

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.

select title as novel, name, surname
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.

select title as work, name, surname
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.

select title, year
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.

select distinct a.surname, a.name
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.

select book.*
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?

select surname, surname, surname
from author
where (name = "Fabio")
order by surname

Challenge - 2

What is the result of the select query below?

select b.title, a.surname as author
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?

select b.title
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.