CREATE A DATABASE, name it as booksLASTNAME
ON THE FOLLOWING DATA:
AUTHORS TABLE STRUCTURE:
Column Name Description Data Type Nulls? Keys
au_id Unique author identifier Char(3) PK
au_fname Author first name Varchar(15)
au_lname Author last name Varchar(15)
phone Author telephone number Varchar(12) Yes
address Author address Varchar(20) Yes
city Author city Varchar(15) Yes
state Author state Char(2) Yes
zip Author zip Char(5) Yes
AUTHORS TABLE DATA:
au_id au_fname au_lname phone address city state zip
A01 Sarah Buchman 718-496-7223 75 West 205 St. Bronx NY 10468
A02 Wendy Heydemark 303-986-7020 2922 Baseline Rd. Boulder CO 80303
A03 Hallie Hull 415-549-4278 3800 Waldo Ave, #14F San Francisco CA 94123
A04 Klee Hull 415-549-4278 3800 Waldo Ave, #14F San Francisco CA 94123
A05 Christian Kells 212-771-4680 114 Horatio St. New York NY 10014
A06 Kellsey 650-836-7128 390 Serra Mall Palo Alto CA 94305
A07 Paddy O’Furniture 941-925-0752 1442 Main St. Sarasota FL 34236
PUBLISHERS TABLE STRUCTURE:
Column Name Description Data Type Nulls? Keys
pub_id Unique publisher identifier Char(3) PK
pub_name Publisher name Varchar(20)
city Publisher city Varchar(15)
state Publisher state/province Char(2) Yes
country Publisher country varchar(15)
PUBLISHERS TABLE DATA:
pub_id pub_name city state country
P01 Abatis Publishers New York NY USA
P02 Core Dump Books San Francisco CA USA
P03 Schadenfreude Press Hamburg NULL Germany
P04 Tenterhooks Press Berkeley CA USA
TITLES TABLE STRUCTURE:
Column Name Description Data Type Nulls? Keys
title_id Char(3) PK
title_name Varchar(40)
type Varchar(10)
pub_id char(3)
pages integer Yes
price decimal (5,2) Yes
sales integer Yes
pubdate date Yes
contract smallint
TITLES TABLE DATA:
title_id title_name type pub_id pages price sales pubdate contract
T01 1977! history P01 107 21.99 566 2000-08-01 1
T02 200 Years of history P03 14 19.95 9566 1998-04-01 1
German Humor
T03 Ask Your System computer P02 1226 39.95 25667 2000-09-01 1
Administrator
T04 But I Did It psychology P04 510 12.99 13001 1999-05-31 1
Unconsciously
T05 Exchange of psychology P04 201 6.95 201440 2001-06-01 1
Platitudes
T06 How About biography P01 473 19.95 11320 2000-07-31 1
Never?
T07 I Blame My biography P03 333 23.95 15000200 1999-10-01 1
Mother
T08 Just Wait Until children P04 86 10.00 4095 2001-06-01 1
After School
T09 Kiss My Boo-Boo children P04 22 13.95 5000 2002-05-31 1
T10 Not Without My biography P01 Null Null Null Null 0
Faberge Egg
T11 Perhaps It’s A psychology P04 826 7.99 94123 2000-11-30 1
Grandular
Problem
T12 Spontaneous , biography P01 507 12.99 100001 2000-08-31 1
Not Annoying
T13 What Are The history P03 802 29.99 10467 1999-05-31 1
Civilian
Applications?
title_authors table structure
Column Name Description Data Type Nulls? Keys
title_id Title identifier Char(3) PK
au_id Authors identifier char(3)
au_order Author name order on book cover smallint
royalty_share Author fractional royalty share Decimal(5,2)
title_id au_id au_order royalty_share
T01 A01 1 1.00
T02 A01 1 1.00
T03 A05 1 1.00
T04 A03 1 0.60
T05 A04 1 1.00
T06 A02 1 1.00
T07 A02 1 1.00
T08 A06 1 0.50
T09 A06 1 1.00
T10 A02 1 1.00