SQL SCHEMA AND DATABASE CONTROL FILES
-
Control files with a sample of data loaded in the database
CREATE
TABLE Photo
(
catalogn
INTEGER PRIMARY KEY,
film
VARCHAR2(255),
color
CHAR CHECK (color IN ('C', 'B')),
fstop
NUMBER,
speed
VARCHAR2(255),
resolution
NUMBER,
available
CHAR CHECK (available IN ('Y', 'N')),
image
VARCHAR2(255) UNIQUE,
price
NUMBER CHECK (price > 0),
CHECK
((color = 'C' AND resolution >= 150) OR
(color = 'B' AND resolution >=
75))
);
CREATE
TABLE Transaction
(
idn NUMBER
PRIMARY KEY,
cc_no
VARCHAR2(255) NOT NULL,
cc_type
CHAR CHECK (cc_type IN ('V', 'M', 'A', 'D')),
cc_expd
DATE NOT NULL,
trdate
DATE NOT NULL,
amount
NUMBER
);
CREATE
TABLE Customer
(
idname
VARCHAR2(255) PRIMARY KEY,
passwd
VARCHAR2(255) NOT NULL,
name
VARCHAR2(255) NOT NULL,
st_addr
VARCHAR2(255) NOT NULL,
state
VARCHAR2(255) NOT NULL,
country
VARCHAR2(255) NOT NULL,
st_addr_bill
VARCHAR2(255),
state_bill
VARCHAR2(255),
country_bill
VARCHAR2(255),
CHECK
(st_addr_bill <> st_addr)
);
CREATE
TABLE Location
(
place
VARCHAR2(255),
country
VARCHAR2(255),
description
VARCHAR2(255),
PRIMARY
KEY (place, country)
);
CREATE
TABLE Model
(
name
VARCHAR2(255),
dob
DATE,
sex
CHAR,
nude
CHAR,
bio
VARCHAR2(255),
PRIMARY
KEY (name, dob)
);
CREATE
TABLE Author
(
name
VARCHAR2(255),
dob
DATE,
nationality
VARCHAR2(255),
bio
VARCHAR2(255) UNIQUE,
st_addr
VARCHAR2(255),
state
VARCHAR2(255),
country
VARCHAR2(255),
PRIMARY
KEY (name, dob)
);
CREATE
TABLE Landscape
(
catalogn
INTEGER PRIMARY KEY,
FOREIGN
KEY (catalogn) REFERENCES Photo
);
CREATE
TABLE LocatedIn
(
catalogn
INTEGER PRIMARY KEY,
place
VARCHAR2(255),
country
VARCHAR2(255),
FOREIGN
KEY (catalogn) REFERENCES Photo,
FOREIGN
KEY (place, country) REFERENCES Location
);
CREATE
TABLE Portrait
(
catalogn
INTEGER PRIMARY KEY,
FOREIGN
KEY (catalogn) REFERENCES Photo
);
CREATE
TABLE Models
(
catalogn
INTEGER,
name
VARCHAR2(255),
dob
DATE,
agency
VARCHAR2(255),
PRIMARY
KEY (catalogn, name, dob),
FOREIGN
KEY (catalogn) REFERENCES Photo,
FOREIGN
KEY (name, dob) REFERENCES Model
);
CREATE
TABLE Abstract
(
catalogn
INTEGER PRIMARY KEY,
description
VARCHAR2(255),
FOREIGN
KEY (catalogn) REFERENCES Photo
);
CREATE
TABLE Takes
(
catalogn
INTEGER PRIMARY KEY,
name
VARCHAR2(255) NOT NULL,
dob
DATE NOT NULL,
dtaken
DATE,
history
VARCHAR2(255),
FOREIGN
KEY (catalogn) REFERENCES Photo,
FOREIGN
KEY (name, dob) REFERENCES Author
);
CREATE
TABLE Influences
(
auth1_name
VARCHAR2(255),
auth1_dob
DATE,
auth2_name
VARCHAR2(255),
auth2_dob
DATE,
PRIMARY
KEY (auth1_name, auth1_dob, auth2_name, auth2_dob),
FOREIGN
KEY (auth1_name, auth1_dob) REFERENCES Author(name, dob),
FOREIGN
KEY (auth2_name, auth2_dob) REFERENCES Author(name, dob)
);
CREATE
TABLE Includes
(
catalogn
INTEGER PRIMARY KEY,
idn
NUMBER,
FOREIGN
KEY (catalogn) REFERENCES Photo,
FOREIGN
KEY (idn) REFERENCES Transaction
);
CREATE
TABLE Buys
(
idn
NUMBER PRIMARY KEY,
idname
VARCHAR2(255),
FOREIGN
KEY (idn) REFERENCES Transaction,
FOREIGN
KEY (idname) REFERENCES Customer
);
CREATE
INDEX Iavailable ON Photo (available);
CREATE
INDEX Iloc ON LocatedIn (place, country);
CREATE
INDEX Itakes ON Takes (name, dob);
CREATE
INDEX Iidname ON Buys (idname);
abstract.ctl
=============================
LOAD
DATA
INFILE
*
INTO
TABLE Abstract
FIELDS
TERMINATED BY '|'
(catalogn,
description)
BEGINDATA
4|NULL
5|NULL
10|NULL
17|NULL
21|NULL
22|NULL
26|NULL
27|NULL
author.ctl
=============================
LOAD
DATA
INFILE
*
INTO
TABLE Author
FIELDS
TERMINATED BY '|'
(name,
dob DATE 'dd-mm-yyyy', nationality, bio, st_addr, state, country)
BEGINDATA
Alfred
Stieglitz|1-1-1864|American|American photographer, passionate advocate of
photography as an art, and a pioneer exhibitor of modern art in the United
States.|NULL|NULL|NULL
Andre
Kertesz|2-7-1894|Hungarian|Hungarian-born American photojournalist whose
spontaneous, unposed pictures exerted a strong influence on magazine
photography.|NULL|NULL|NULL
Ansel
Adams|20-2-1902|American|American photographer especially known for technical
innovations and masterly representations of the dramatic sweep of mountainous
terrain. Throughout his career, Adams worked to increase public acceptance of
photography as a fine art. |NULL|NULL|NULLArnold
Newman|3-3-1918|American|American photographer who specialized in portraits of
well-known people posed in settings associated with their work. This approach,
known as "environmental portraiture," greatly influenced portrait
photography in the 20th century.|45th St|NY|USA
Imogen
Cunningham|12-4-1883|American|Her career began with a part time job in the
Seattle studio of Edward S. Curtis.In 1932 Cunningham joined the association of
West Coast photographers which had been founded by Ansel Adams and Willard Van
Dyke in 1934 under the name of Group f/64.|NULL|NULL|NULL
Minor
White|9-7-1908|American|American photographer and editor, whose efforts to
extend photography range of expression made him one of the most influential
creative photographers of the mid-20th century.|NULL|NULL|NULL
buys.ctl
=============================
LOAD
DATA
INFILE
*
INTO
TABLE Buys
FIELDS
TERMINATED BY '|'
(idn,
idname)
BEGINDATA
0|1
1|2
2|3
customer.ctl
=============================
LOAD
DATA
INFILE
*
INTO
TABLE Customer
FIELDS
TERMINATED BY '|'
(idname,
passwd, name, st_addr, state, country, st_addr_bill, state_bill, country_bill)
BEGINDATA
0|0|CustomerName0|CustomerAddrs0|NY|USA|CustomerAddrs0|NY|USA
1|1|CustomerName1|CustomerAddrs1|MI|USA|CustomerAddrs1|MI|USA
2|2|CustomerName2|CustomerAddrs2|TX|USA|CustomerAddrs2|TX|USA
includes.ctl
=============================
LOAD
DATA
INFILE
*
INTO
TABLE Includes
FIELDS
TERMINATED BY '|'
(catalogn,
idn)
BEGINDATA
1|0
7|0
9|1
17|1
29|2
23|3
18|4
influences.ctl
=============================
LOAD
DATA
INFILE
*
INTO
TABLE Influences
FIELDS
TERMINATED BY '|'
(auth1_name,
auth1_dob DATE 'dd-mm-yyyy', auth2_name, auth2_dob DATE 'dd-mm-yyyy')
BEGINDATA
Alfred
Stieglitz|1-1-1864|Minor White|9-7-1908
Ansel
Adams|20-2-1902|Imogen Cunningham|12-4-1883
landscape.ctl
=============================
LOAD
DATA
INFILE
*
INTO
TABLE Landscape
FIELDS
TERMINATED BY '|'
(catalogn)
BEGINDATA
1
2
6
7
8
9
11
12
13
14
15
28
29
locatedin.ctl
=============================
LOAD
DATA
INFILE
*
INTO
TABLE Locatedin
FIELDS
TERMINATED BY '|'
(catalogn,
place, country)
BEGINDATA
1|New
York|USA
2|New
York|USA
6|New
York|USA
7|New
York|USA
8|New
York|USA
9|Paris|France
11|Yosemite|USA
12|Hernandez|USA
13|Lake
MacDonald|USA
14|Yellowstone|USA
15|Sierra
Nevada|USA|California
28|New
York|USA
29|New
York|USA
location.ctl
=============================
LOAD
DATA
INFILE
*
INTO
TABLE Location
FIELDS
TERMINATED BY '|'
(place,
country, description)
BEGINDATA
New
York|USA|NULL
Poughkeepsie|USA|New
York
Paris|France|NULL
Yosemite|USA|Yosemite
National Park
Lake
MacDonald|USA|Glacier National Park
Yellowstone|USA|Yellowstone
National Park
Sierra
Nevada|USA|California
Hernandez|USA|New
Mexico
Cannes|France|NULL
Addis
Ababa|France|NULL
San
Mateo|USA|California
San
Francisco|USA|California
model.ctl
=============================
LOAD
DATA
INFILE
*
INTO
TABLE Model
FIELDS
TERMINATED BY '|'
(name,
dob DATE 'dd-mm-yyyy', sex, nude, bio)
BEGINDATA
Alfred
Stieglitz|1-1-1864|M|N|American photographer
Yasuo Kuniyoshi|1-1-1893|M|N|Painter,
artist
Pablo
Picasso|1-1-1898|M|N|Painter, artist
Haile
Selassie|1-1-1892|M|N|Emperor, Ethiopia
Robert
Moses|1-1-1888|M|N|New York state and municipal official
Minor
White|9-7-1908|M|N|American photographer
Ansel Adams|20-2-1902|M|N|American
photographer
models.ctl
=============================
LOAD
DATA
INFILE
*
INTO
TABLE Models
FIELDS
TERMINATED BY '|'
(catalogn,
name, dob DATE 'dd-mm-yyyy', agency)
BEGINDATA
3|Alfred
Stieglitz|1-1-1864|NULL
16|Yasuo
Kuniyoshi|1-1-1893|NULL
18|Pablo
Picasso|1-1-1898|NULL
18|Haile
Selassie|1-1-1892|NULL
20|Robert
Moses|1-1-1888|NULL
23|Alfred
Stieglitz|1-1-1864|NULL
24|Minor
White|9-7-1908|NULL
25|Ansel
Adams|20-2-1902|NULL
photo.ctl
=============================
LOAD
DATA
INFILE
*
INTO
TABLE Photo
FIELDS
TERMINATED BY '|'
(catalogn,
film, color, fstop, speed, resolution, available, image, price)
BEGINDATA
1|Plate|B|16|1/4|300|N|stieglitz_winter_on_fifth_avenue.jpg|1341
2|Plate|B|5.6|1/30|600|Y|stieglitz_icy_night.jpg|433
3|Plate|B|16|1/2|300|Y|stieglitz_selfportrait.jpg|1000
4|Plate|B|2.8|1/250|75|Y|stieglitz_snapshot_paris.jpg|234
5|Plate|B|2.8|1/500|75|Y|stieglitz_steerage.jpg|300
6|Kodak|B|11|1/4|300|Y|kertesz_white_horse.jpg|400
7|Kodak|B|22|1/4|600|N|kertesz_poughkeepsie.jpg|1200
8|Kodak|B|5.6|1/250|150|Y|kertesz_sixth_avenue.jpg|100
9|Kodak|B|22|1|600|N|kertesz_rue_des_ursins.jpg|344
10|Kodak|B|4|1/60|600|Y|kertesz_distortion.jpg|500
11|Plate|B|2.8|1/15|75|Y|adams_clearing_winter_storm.jpg|150
12|Plate|B|22|1/250|150|Y|adams_moonrise.jpg|333
13|Plate|B|16|1/30|300|Y|adams_lake_mcdonald.jpg|600
14|Plate|B|8|1/500|600|Y|adams_old_faithful_geyser.jpg|700
15|Plate|B|16|1/60|150|Y|adams_winter_sunrise.jpg|120
16|Ilford|B|8|1/250|600|Y|newman_kuniyoshi.jpg|340
17|Kodachrome|C|22|1/8|300|N|newman_red_brick.jpg|456
18|Kodachrome|C|11|1/250|150|N|newman_picasso.jpg|70
19|Kodachrome|C|16|1/60|600|Y|newman_selassie.jpg|340
20|Kodachrome|C|22|1/500|300|Y|newman_moses.jpg|303
21|Plate|B|8|1/60|150|Y|cunningham_agave.jpg|200
22|Plate|C|8|1/4|150|Y|cunningham_amaryllis.jpg|300
23|Ilford|B|5.6|1/2|600|N|cunningham_stieglitz.jpg|400
24|Ilford|C|8|1/125|600|Y|cunningham_white.jpg|300
25|Ilford|B|5.6|1/125|150|Y|cunningham_adams.jpg|320
26|Kodak|B|11|1/4|75|Y|white_surf_vertical.jpg|320
27|Kodak|B|16|1/2|300|Y|white_warehouse.jpg|800
28|Kodak|B|2.8|1/30|75|Y|white_barn_and_clouds.jpg|400
29|Kodak|B|16|1/60|600|N|white_poplar_trees.jpg|200
portrait.ctl
=============================
LOAD
DATA
INFILE
*
INTO
TABLE Portrait
FIELDS
TERMINATED BY '|'
(catalogn)
BEGINDATA
3
16
18
19
20
23
24
25
takes.ctl
=============================
LOAD
DATA
INFILE
*
INTO
TABLE Takes
FIELDS
TERMINATED BY '|'
(catalogn,
name, dob DATE 'dd-mm-yyyy', dtaken DATE 'dd-mm-yyyy', history)
BEGINDATA
1|Alfred
Stieglitz|1-1-1864|1-1-1893|Winter on Fifth Avenue, New York
2|Alfred
Stieglitz|1-1-1864|3-3-1893|Icy Night
3|Alfred
Stieglitz|1-1-1864|7-7-1907|Self-portrait
4|Alfred
Stieglitz|1-1-1864|4-4-1911|Snapshot, Paris
5|Alfred
Stieglitz|1-1-1864|8-8-1907|The Steerage
6|Andre
Kertesz|2-7-1894|6-6-1962|The White Horse, New York
7|Andre
Kertesz|2-7-1894|6-6-1937|Poughkeepsie, New York
8|Andre
Kertesz|2-7-1894|1-1-1959|Sixth Avenue, New York
9|Andre
Kertesz|2-7-1894|5-5-1931|Rue des Ursins
10|Andre
Kertesz|2-7-1894|2-2-1933|Distortion
11|Ansel
Adams|20-2-1902|8-8-1937|Clearing Winter Storm. Yosemite National Park
12|Ansel
Adams|20-2-1902|2-2-1941|Moonrise. Hernandez, New Mexico
13|Ansel
Adams|20-2-1902|2-2-1942|Lake MacDonald, Glacier National Park
14|Ansel
Adams|20-2-1902|3-3-1942|Old Faithful Geyser, Yellowstone National Park
15|Ansel
Adams|20-2-1902|8-8-1944|Winter Sunrise, the Sierra Nevada, from Lone Pine,
California
16|Arnold
Newman|3-3-1918|6-6-1941|Yasuo Kuniyoshi, New York
17|Arnold
Newman|3-3-1918|6-6-1948|Red Brick Wall, New York
18|Arnold
Newman|3-3-1918|3-3-1956|Pablo Picasso, Cannes
19|Arnold
Newman|3-3-1918|3-3-1958|Haile Selassie, Addis Ababa
20|Arnold
Newman|3-3-1918|5-5-1959|Robert Moses, New York
21|Imogen
Cunningham|12-4-1883|7-7-1920|Agave Design 2
22|Imogen
Cunningham|12-4-1883|6-6-1933|Amaryllis
23|Imogen
Cunningham|12-4-1883|4-4-1934|Alfred Stieglitz, Photographer
24|Imogen
Cunningham|12-4-1883|8-8-1963|Minor White, Photographer
25|Imogen
Cunningham|12-4-1883|1-1-1975|Ansel Adams, Photographer
26|Minor
White|9-7-1908|5-5-1947|Surf Vertical, San Mateo County, California
27|Minor
White|9-7-1908|2-2-1949|Warehouse Area, San Francisco
28|Minor
White|9-7-1908|7-7-1955|Barn and Clouds, in the Vicinity of Naples and
Dansville, New York
29|Minor
White|9-7-1908|6-6-1955|Road and Poplar Trees, in the Vicinity of Naples and
Dansville, New York
transaction.ctl
=============================
LOAD
DATA
INFILE
*
INTO
TABLE Transaction
FIELDS
TERMINATED BY '|'
(idn,
cc_no, cc_type, cc_expd DATE 'dd-mm-yyyy', trdate DATE 'dd-mm-yyyy', amount)
BEGINDATA
0|1268680389|V|5-5-2000|2-2-1999||
1|1328525187|A|10-10-2000|7-7-1997||
2|706332892|D|10-10-2000|4-4-2001||
3|456332892|D|1-1-2000|4-8-2001||
4|706565792|D|1-10-2001|8-4-2001||