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

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