| ## picoSQL - Test database | Corso Italia, 178 - 56125 Pisa Telefono/fax 050 46380 e-mail picosoft@picosoft.it

- Description
- Tables
  - ARTICOLO
  - AUTORE
  - EDITORE
  - GENERE
  - LIBRO
  - NUMERORIVISTA
  - RIVISTA
  - LIBROCOMPLETO

Description

The test database represents the relation between publishing company (EDITORE), book writers (AUTORE) and books (LIBRO). A publishing company prints different books about different arguments, each book is about an argument (GENERE) and it is written by a writer. A writer can write articles (ARTICOLO) for magazines (RIVISTA). Each article is published in a specific magazine issue (NUMERORIVISTA).

Table ARTICOLO (article)

Column name | Type | Length/precision | Description | Example
ID_ARTICOLO | NUMERIC | 10 | Article identifier | 104
ID_RIVISTA | NUMERIC | 10 | magazine identifier | 23
ID_GENERE | NUMERIC | 10 | argument identifier | 6
ID_AUTORE | NUMERIC | 10 | writer identifier | 546
DATA_PUBBLICAZIONE | DATE | - | pubblication date | 2000-03-11
RUBRICA | CHAR | 15 | column where the article is published | Games
TITOLO | CHAR | 30 | article title | Chess
DESCRIZIONE | CHAR | 50 | Article description | chess strategy

Table AUTORE (writer)

Column name | Type | Length/precision | Description | Example
ID_AUTORE | NUMERIC | 10 | writer identifier | 104
NOME | CHAR | 15 | writer's first name | Marco
COGNOME | CHAR | 15 | writer's surname | Bertacca
DATA_NASCITA | DATE | - | birth day | 1959-03-11
NAZIONE | CHAR | 10 | State | ITALIA

Table EDITORE (publishing company)

Column name | Type | Length/precision | Description | Example
ID_EDITORE | NUMERIC | 10 | publishing company identifier | 104
EDITORE | CHAR | 30 | publishing company's name | McGraw-Hill
INDIRIZZO | CHAR | 30 | publishing ccompany's address | Via Ripamonti 89 MILANO
NAZIONE | CHAR | 10 | state | ITALIA
TELEFONO | CHAR | 15 | telephone number | 02 5357181

Table GENERE (argument)

Column name | Type | Length/precision | Description | Example
ID_GENERE | NUMERIC | 10 | argument's identifier | 104
GENERE | CHAR | 15 | short description | Computer science
DESCRIZIONE | CHAR | 50 | long description | Computers, programming, operating systems

Table LIBRO

Column name | Type | Length/precision | Description | Example
ID_LIBRO | NUMERIC | 10 | book identifier | 104
ID_AUTORE | NUMERIC | 10 | writer identifier | 546
ID_EDITORE | NUMERIC | 10 | publishing company identifier | 34
ID_GENERE | NUMERIC | 10 | argument identifier | 6
PREZZO | NUMERIC | 10 | price | 20
TITOLO | CHAR | 40 | Title | An introduction to Linux
DESCRIZIONE | CHAR | 50 | book's argument description | Open source Unix-like operating system
DATA_PUBBLICAZIONE | DATE | - | publishing date | 1998-01-01

Table NUMERORIVISTA (issue)

Column name | Type | Length/precision | Description | Example
ID_NUMERORIVISTA | NUMERIC | 10 | issue identifier | 104
ID_RIVISTA | NUMERIC | 10 | magazine identifier | 63
DATA_PUBBLICAZIONE | DATE | - | publication date | 2002-03-10
PREZZO | NUMERIC | 10 | price | 20

Table RIVISTA (magazine)

Column name | Type | Length/precision | Description | Example
ID_RIVISTA | NUMERIC | 10 | magazine identifier | 104
ID_EDITORE | NUMERIC | 10 | publishing company | 546
ID_GENERE | NUMERIC | 10 | argument identifier | 6
RIVISTA | CHAR | 15 | magazines's name | Linux Journal

View LIBROCOMPLETO (all about a book)

Column name | Type | Length/precision | Description | Example
GENERE | CHAR | 15 | argument description | Computer science
NOME | CHAR | 15 | Author first name | Marco
COGNOME | CHAR | 15 | Author surname | Bertacca
TITOLO | CHAR | 40 | Book title | An introduction to Linux
EDITORE | CHAR | 30 | Publishing company name | McGraw-Hill
PREZZO | NUMERIC | 10 | Book price | 20
DATA_PUBBLICAZIONE | DATE | - | Publishing date | 1998-01-01

Create Statement:

    create view librocompleto as
       select genere.genere,autore.nome,autore.cognome, libro.titolo,
              editore.editore,libro.prezzo,libro.data_pubblicazione
       from libro inner join autore
          inner join editore
             inner join genere on libro.id_genere = genere.id_genere
          on libro.id_editore = editore.id_editore
       on libro.id_autore = autore.id_autore