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

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

id	name	surname	sex	dateOfBirth	alive
author
1	Niccolò	Ammaniti	M	25/09/1966	true
2	William	Shakespeare	M	26/04/1564	false
3	Fabio	Volo	M	23/06/1972	true

id	title	genre	year	idAuthor
book
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.