-
Notifications
You must be signed in to change notification settings - Fork 102
Expand file tree
/
Copy pathROCK_SCHEMA_DDL.sql
More file actions
148 lines (139 loc) · 7.3 KB
/
ROCK_SCHEMA_DDL.sql
File metadata and controls
148 lines (139 loc) · 7.3 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
-- DDL for ROCK_USR schema (only "ROCK_" prefixed tables)
--------------------------------------------------------
-- Table: ROCK_ARTISTS
CREATE TABLE "ROCK_USR"."ROCK_ARTISTS"
( "ARTIST_ID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
"ARTIST_NAME" VARCHAR2(200) COLLATE "USING_NLS_COMP" NOT NULL ENABLE,
"FORMED_YEAR" NUMBER(4,0),
"ORIGIN_COUNTRY" VARCHAR2(100) COLLATE "USING_NLS_COMP",
"GENRE" VARCHAR2(100) COLLATE "USING_NLS_COMP",
"STATUS" VARCHAR2(50) COLLATE "USING_NLS_COMP",
"BIOGRAPHY" CLOB COLLATE "USING_NLS_COMP",
"WEBSITE_URL" VARCHAR2(300) COLLATE "USING_NLS_COMP",
"CREATED_DATE" DATE DEFAULT SYSDATE,
PRIMARY KEY ("ARTIST_ID")
USING INDEX PCTFREE 10 INITRANS 20 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA" ENABLE
) DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA"
LOB ("BIOGRAPHY") STORE AS SECUREFILE (
TABLESPACE "DATA" ENABLE STORAGE IN ROW 4000 CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 262144 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;
-- Table: ROCK_ALBUMS
CREATE TABLE "ROCK_USR"."ROCK_ALBUMS"
( "ALBUM_ID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
"ARTIST_ID" NUMBER NOT NULL ENABLE,
"ALBUM_TITLE" VARCHAR2(200) COLLATE "USING_NLS_COMP" NOT NULL ENABLE,
"RELEASE_YEAR" NUMBER(4,0),
"GENRE" VARCHAR2(100) COLLATE "USING_NLS_COMP",
"LABEL" VARCHAR2(100) COLLATE "USING_NLS_COMP",
"CREATED_DATE" DATE DEFAULT SYSDATE,
PRIMARY KEY ("ALBUM_ID")
USING INDEX PCTFREE 10 INITRANS 20 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA" ENABLE,
CONSTRAINT "FK_ROCK_ALBUMS_ARTIST" FOREIGN KEY ("ARTIST_ID")
REFERENCES "ROCK_USR"."ROCK_ARTISTS" ("ARTIST_ID") ENABLE
) DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA" ;
-- Table: ROCK_SONGS
CREATE TABLE "ROCK_USR"."ROCK_SONGS"
( "SONG_ID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
"ALBUM_ID" NUMBER NOT NULL ENABLE,
"SONG_TITLE" VARCHAR2(200) COLLATE "USING_NLS_COMP" NOT NULL ENABLE,
"DURATION" NUMBER(5,0),
"TRACK_NUMBER" NUMBER,
"CREATED_DATE" DATE DEFAULT SYSDATE,
"IS_SINGLE" CHAR(1) COLLATE "USING_NLS_COMP",
PRIMARY KEY ("SONG_ID")
USING INDEX PCTFREE 10 INITRANS 20 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA" ENABLE,
CHECK (is_single IN ('Y','N')) ENABLE,
CONSTRAINT "FK_ROCK_SONGS_ALBUM" FOREIGN KEY ("ALBUM_ID")
REFERENCES "ROCK_USR"."ROCK_ALBUMS" ("ALBUM_ID") ENABLE
) DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA" ;
-- Table: ROCK_CONCERTS
CREATE TABLE "ROCK_USR"."ROCK_CONCERTS"
( "CONCERT_ID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
"ARTIST_ID" NUMBER NOT NULL ENABLE,
"VENUE" VARCHAR2(200) COLLATE "USING_NLS_COMP",
"CITY" VARCHAR2(100) COLLATE "USING_NLS_COMP",
"COUNTRY" VARCHAR2(100) COLLATE "USING_NLS_COMP",
"CONCERT_DATE" DATE,
"TOUR_NAME" VARCHAR2(200) COLLATE "USING_NLS_COMP",
"ATTENDANCE" NUMBER,
"CONCERT_STATUS" VARCHAR2(50) COLLATE "USING_NLS_COMP",
"NOTES" CLOB COLLATE "USING_NLS_COMP",
"CREATED_DATE" DATE DEFAULT SYSDATE,
PRIMARY KEY ("CONCERT_ID")
USING INDEX PCTFREE 10 INITRANS 20 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA" ENABLE,
CONSTRAINT "FK_ROCK_CONCERTS_ARTIST" FOREIGN KEY ("ARTIST_ID")
REFERENCES "ROCK_USR"."ROCK_ARTISTS" ("ARTIST_ID") ENABLE
) DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA"
LOB ("NOTES") STORE AS SECUREFILE (
TABLESPACE "DATA" ENABLE STORAGE IN ROW 4000 CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 262144 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;
-- Table: ROCK_SETLIST_SONGS
CREATE TABLE "ROCK_USR"."ROCK_SETLIST_SONGS"
( "SETLIST_ID" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
"CONCERT_ID" NUMBER NOT NULL ENABLE,
"SONG_ID" NUMBER,
"POSITION" NUMBER,
"NOTES" VARCHAR2(500) COLLATE "USING_NLS_COMP",
PRIMARY KEY ("SETLIST_ID")
USING INDEX PCTFREE 10 INITRANS 20 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA" ENABLE,
CONSTRAINT "FK_ROCK_SETLIST_CONCERT" FOREIGN KEY ("CONCERT_ID")
REFERENCES "ROCK_USR"."ROCK_CONCERTS" ("CONCERT_ID") ENABLE,
CONSTRAINT "FK_ROCK_SETLIST_SONG" FOREIGN KEY ("SONG_ID")
REFERENCES "ROCK_USR"."ROCK_SONGS" ("SONG_ID") ENABLE
) DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA" ;