· 9 years ago · Oct 21, 2016, 09:24 PM
1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
4
5CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
6USE `mydb` ;
7
8-- -----------------------------------------------------
9-- Table `mydb`.`userplaylist`
10-- -----------------------------------------------------
11CREATE TABLE IF NOT EXISTS `mydb`.`userplaylist` (
12 `userplaylistID` INT(11) NOT NULL AUTO_INCREMENT,
13 `userplaylistNAME` VARCHAR(45) NULL DEFAULT NULL,
14 PRIMARY KEY (`userplaylistID`))
15ENGINE = InnoDB
16AUTO_INCREMENT = 3
17DEFAULT CHARACTER SET = utf8;
18
19
20-- -----------------------------------------------------
21-- Table `mydb`.`songalbum`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`songalbum` (
24 `songAlbumID` INT(11) NOT NULL AUTO_INCREMENT,
25 `songAlbumName` VARCHAR(45) NULL DEFAULT NULL,
26 `songAlbumLabel` VARCHAR(45) NULL DEFAULT NULL,
27 `songAlbumYear` VARCHAR(45) NULL DEFAULT NULL,
28 PRIMARY KEY (`songAlbumID`))
29ENGINE = InnoDB
30AUTO_INCREMENT = 3
31DEFAULT CHARACTER SET = utf8;
32
33
34-- -----------------------------------------------------
35-- Table `mydb`.`songartist`
36-- -----------------------------------------------------
37CREATE TABLE IF NOT EXISTS `mydb`.`songartist` (
38 `songArtistID` INT(11) NOT NULL AUTO_INCREMENT,
39 `ArtistName` VARCHAR(45) NULL DEFAULT NULL,
40 `ArtistLabel` VARCHAR(45) NULL DEFAULT NULL,
41 PRIMARY KEY (`songArtistID`))
42ENGINE = InnoDB
43AUTO_INCREMENT = 3
44DEFAULT CHARACTER SET = utf8;
45
46
47-- -----------------------------------------------------
48-- Table `mydb`.`songinfo`
49-- -----------------------------------------------------
50CREATE TABLE IF NOT EXISTS `mydb`.`songinfo` (
51 `idSongInfo` INT(11) NOT NULL AUTO_INCREMENT,
52 `Title` VARCHAR(45) NOT NULL,
53 `Artist` INT NULL DEFAULT NULL,
54 `Genre` VARCHAR(45) NULL DEFAULT NULL,
55 `Album` INT NULL DEFAULT NULL,
56 `Location` VARCHAR(45) NULL DEFAULT NULL,
57 `AlbumTrack` INT(11) NULL DEFAULT NULL,
58 `Year` INT(11) NULL DEFAULT NULL,
59 `Comment` VARCHAR(45) NULL DEFAULT NULL,
60 `PlayCount` INT(11) NULL DEFAULT NULL,
61 `BPM` INT(11) NULL DEFAULT NULL,
62 PRIMARY KEY (`idSongInfo`),
63 INDEX `songAlbumID_idx` (`Album` ASC),
64 INDEX `songArtistID_idx` (`Artist` ASC),
65 CONSTRAINT `songAlbumID`
66 FOREIGN KEY (`Album`)
67 REFERENCES `mydb`.`songalbum` (`songAlbumID`)
68 ON DELETE NO ACTION
69 ON UPDATE NO ACTION,
70 CONSTRAINT `songArtistID`
71 FOREIGN KEY (`Artist`)
72 REFERENCES `mydb`.`songartist` (`songArtistID`)
73 ON DELETE NO ACTION
74 ON UPDATE NO ACTION)
75ENGINE = InnoDB
76AUTO_INCREMENT = 3
77DEFAULT CHARACTER SET = utf8;
78
79
80-- -----------------------------------------------------
81-- Table `mydb`.`playlist_song`
82-- -----------------------------------------------------
83CREATE TABLE IF NOT EXISTS `mydb`.`playlist_song` (
84 `userplaylistID` INT(11) NOT NULL,
85 `idSongInfo` INT(11) NOT NULL,
86 PRIMARY KEY (`userplaylistID`, `idSongInfo`),
87 INDEX `idSongInfo_idx` (`idSongInfo` ASC),
88 CONSTRAINT `playlistID`
89 FOREIGN KEY (`userplaylistID`)
90 REFERENCES `mydb`.`userplaylist` (`userplaylistID`)
91 ON DELETE NO ACTION
92 ON UPDATE NO ACTION,
93 CONSTRAINT `idSong`
94 FOREIGN KEY (`idSongInfo`)
95 REFERENCES `mydb`.`songinfo` (`idSongInfo`)
96 ON DELETE NO ACTION
97 ON UPDATE NO ACTION)
98ENGINE = InnoDB
99DEFAULT CHARACTER SET = utf8;
100
101
102-- -----------------------------------------------------
103-- Table `mydb`.`useraddress`
104-- -----------------------------------------------------
105CREATE TABLE IF NOT EXISTS `mydb`.`useraddress` (
106 `userAddID` INT(11) NOT NULL AUTO_INCREMENT,
107 `userStreet` VARCHAR(45) NULL DEFAULT NULL,
108 `userCity` VARCHAR(45) NULL DEFAULT NULL,
109 `userState` VARCHAR(45) NULL DEFAULT NULL,
110 `userZip` VARCHAR(45) NULL DEFAULT NULL,
111 `userCountry` VARCHAR(45) NULL DEFAULT NULL,
112 PRIMARY KEY (`userAddID`))
113ENGINE = InnoDB
114AUTO_INCREMENT = 3
115DEFAULT CHARACTER SET = utf8;
116
117
118-- -----------------------------------------------------
119-- Table `mydb`.`usercreditcardinfo`
120-- -----------------------------------------------------
121CREATE TABLE IF NOT EXISTS `mydb`.`usercreditcardinfo` (
122 `userCreditCardID` INT(11) NOT NULL AUTO_INCREMENT,
123 `userCardType` VARCHAR(45) NULL DEFAULT NULL,
124 `userCreditCardNumber` VARCHAR(100) NULL DEFAULT NULL,
125 `userCardExpDate` CHAR(7) NULL DEFAULT NULL,
126 `userCardCCV` INT(11) NULL DEFAULT NULL,
127 `userCardName` VARCHAR(100) NULL DEFAULT NULL,
128 PRIMARY KEY (`userCreditCardID`))
129ENGINE = InnoDB
130AUTO_INCREMENT = 3
131DEFAULT CHARACTER SET = utf8;
132
133
134-- -----------------------------------------------------
135-- Table `mydb`.`userinfo`
136-- -----------------------------------------------------
137CREATE TABLE IF NOT EXISTS `mydb`.`userinfo` (
138 `iduserInfo` INT(11) NOT NULL AUTO_INCREMENT,
139 `userFirstName` VARCHAR(45) NULL DEFAULT NULL,
140 `userLastName` VARCHAR(45) NULL DEFAULT NULL,
141 `userAddID` INT(11) NULL DEFAULT NULL,
142 `userPurchasesID` INT(11) NULL DEFAULT NULL,
143 `userEmail` VARCHAR(100) NULL DEFAULT NULL,
144 `userCreditCardID` INT(11) NULL DEFAULT NULL,
145 `userPhoneNumber` VARCHAR(10) NULL DEFAULT NULL,
146 PRIMARY KEY (`iduserInfo`),
147 INDEX `userPurchasesID_idx` (`userPurchasesID` ASC),
148 INDEX `userCreditCardID_idx` (`userCreditCardID` ASC),
149 INDEX `uerAddID_idx` (`userAddID` ASC),
150 CONSTRAINT `uerAddID`
151 FOREIGN KEY (`userAddID`)
152 REFERENCES `mydb`.`useraddress` (`userAddID`)
153 ON DELETE NO ACTION
154 ON UPDATE NO ACTION,
155 CONSTRAINT `userCreditCardID`
156 FOREIGN KEY (`userCreditCardID`)
157 REFERENCES `mydb`.`usercreditcardinfo` (`userCreditCardID`)
158 ON DELETE NO ACTION
159 ON UPDATE NO ACTION)
160ENGINE = InnoDB
161AUTO_INCREMENT = 7
162DEFAULT CHARACTER SET = utf8;
163
164
165-- -----------------------------------------------------
166-- Table `mydb`.`userpurchases`
167-- -----------------------------------------------------
168CREATE TABLE IF NOT EXISTS `mydb`.`userpurchases` (
169 `userPurchasesID` INT(11) NOT NULL AUTO_INCREMENT,
170 `idSongInfo` INT(11) NULL DEFAULT NULL,
171 PRIMARY KEY (`userPurchasesID`),
172 INDEX `idSongInfo_idx` (`idSongInfo` ASC),
173 CONSTRAINT `idSongInfo`
174 FOREIGN KEY (`idSongInfo`)
175 REFERENCES `mydb`.`songinfo` (`idSongInfo`)
176 ON DELETE NO ACTION
177 ON UPDATE NO ACTION)
178ENGINE = InnoDB
179AUTO_INCREMENT = 3
180DEFAULT CHARACTER SET = utf8;
181
182
183SET SQL_MODE=@OLD_SQL_MODE;
184SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
185SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;