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);
=============================
=============================
=============================
=============================
=============================
=============================
=============================
=============================
=============================
=============================
=============================
=============================
=============================
=============================
=============================