Oracle DDL for Byom ERD

From ClassDBI

 /*==============================================================*/
 /* Database name:  byomkesh_proj                          */
 /* DBMS name:      ORACLE Version 9i                            */
 /* Created on:     2/18/2004 9:22:37 PM                         */
 /*==============================================================*/


 alter table ARTIST_GROUP
   drop constraint FK_ARTISTGR_REFERENCE_ARTIST;

 alter table ARTIST_GROUP
   drop constraint FK_ARTISTGR_REF_PERSO_ARTIST;

 alter table BAND
   drop constraint FK_BAND_REFERENCE_ARTIST;

 alter table CD
   drop constraint FK_CD_REFERENCE_ARTIST;

 alter table PERSON
   drop constraint FK_PERSON_REFERENCE_ARTIST;

 alter table TRACK
   drop constraint FK_TRACK_REFERENCE_CD;

 alter table TRACK
   drop constraint FK_TRACK_REFERENCE_SONG;

 drop table ARTIST cascade constraints;

 drop table ARTIST_GROUP cascade constraints;

 drop table BAND cascade constraints;

 drop table CD cascade constraints;

 drop table PERSON cascade constraints;

 drop table SONG cascade constraints;

 drop table TRACK cascade constraints;

 /*==============================================================*/
 /* Table: ARTIST                                                */
 /*==============================================================*/


 create table ARTIST  (
   ARTISTID             NUMBER                           not null,
   NAME                 VARCHAR(50)                      not null,
   POPULARITY           VARCHAR(20),
   constraint PK_ARTIST primary key (ARTISTID)
 );

 /*==============================================================*/
 /* Table: ARTISTGROUP                                           */
 /*==============================================================*/


 create table ARTIST_GROUP  (
   PERSONARTISTID       NUMBER                           not null,
   BANDARTISTID         NUMBER                           not null,
   constraint PK_ARTISTGROUP primary key (PERSONARTISTID, BANDARTISTID)
 );

 /*==============================================================*/
 /* Table: BAND                                                  */
 /*==============================================================*/


 create table BAND  (
   ARTISTID             NUMBER                           not null,
   CREATIONDATE         date,
   ENDDATE              date,
   constraint PK_BAND primary key (ARTISTID)
 );

 /*==============================================================*/
 /* Table: CD                                                    */
 /*==============================================================*/


 create table CD  (
   CDID                 NUMBER                           not null,
   ARTISTID             NUMBER                           not null,
   TITLE                VARCHAR(50)                      not null,
   PUBLISHDATE          DATE                             not null,
   constraint PK_CD primary key (CDID)
 );

 /*==============================================================*/
 /* Table: PERSON                                                */
 /*==============================================================*/


 create table PERSON  (
   ARTISTID             NUMBER                           not null,
   GENDER               VARCHAR(1),
   HAIRCOLOR            VARCHAR(10),
   BIRTHDATE            DATE,
   constraint PK_PERSON primary key (ARTISTID)
 );

 /*==============================================================*/
 /* Table: SONG                                                  */
 /*==============================================================*/


 create table SONG  (
   SONGID               NUMBER                           not null,
   NAME                 VARCHAR(50)                      not null,
   constraint PK_SONG primary key (SONGID)
 );

 /*==============================================================*/
 /* Table: TRACK                                                 */
 /*==============================================================*/


 create table TRACK  (
   CDID                 NUMBER                           not null,
   SONGID               NUMBER                           not null,
   TRACKNUMBER          NUMBER                           not null,
   constraint PK_TRACK primary key (CDID, SONGID)
 );

 alter table ARTIST_GROUP
   add constraint FK_ARTISTGR_REFERENCE_ARTIST foreign key (BANDARTISTID)
      references ARTIST (ARTISTID);

 alter table ARTIST_GROUP
   add constraint FK_ARTISTGR_REF_PERSO_ARTIST foreign key (PERSONARTISTID)
      references ARTIST (ARTISTID);

 alter table BAND
   add constraint FK_BAND_REFERENCE_ARTIST foreign key (ARTISTID)
      references ARTIST (ARTISTID);

 alter table CD
   add constraint FK_CD_REFERENCE_ARTIST foreign key (ARTISTID)
      references ARTIST (ARTISTID);

 alter table PERSON
   add constraint FK_PERSON_REFERENCE_ARTIST foreign key (ARTISTID)
      references ARTIST (ARTISTID);

 alter table TRACK
   add constraint FK_TRACK_REFERENCE_CD foreign key (CDID)
      references CD (CDID);

 alter table TRACK
   add constraint FK_TRACK_REFERENCE_SONG foreign key (SONGID)
      references SONG (SONGID);