CIST2550 PHP&SQL: Exercise 16-2 / pg 552
How to design a database
Create a diagram for a database of music albums
CREATE TABLE `artists` (
`ArtistID` int PRIMARY KEY AUTO_INCREMENT,
`ArtistName` varchar(255)
);
CREATE TABLE `albums` (
`AlbumID` int PRIMARY KEY AUTO_INCREMENT,
`ArtistID` int,
`AlbumName` varchar(255),
`ReleaseDate` date
);
CREATE TABLE `tracks` (
`TrackID` int PRIMARY KEY AUTO_INCREMENT,
`AlbumID` int,
`TrackName` varchar(255),
`TrackTime` time
);
ALTER TABLE `albums` ADD FOREIGN KEY (`ArtistID`) REFERENCES `artists` (`ArtistID`);
ALTER TABLE `tracks` ADD FOREIGN KEY (`AlbumID`) REFERENCES `albums` (`AlbumID`);
One-to-Many relationship
 |
Create a diagram for a database of members of groups within an association.
CREATE TABLE `members` (
`MemberID` int PRIMARY KEY AUTO_INCREMENT,
`MemberName` varchar(255),
`EmailAddress` varchar(255),
`PhoneNumber` varchar(255)
);
CREATE TABLE `groups` (
`GroupID` int PRIMARY KEY AUTO_INCREMENT,
`GroupName` varchar(255),
`GroupDescription` text
);
CREATE TABLE `member_groups` (
`MemberID` int,
`GroupID` int,
`primary` key(MemberID, GroupID)
);
ALTER TABLE `member_groups` ADD FOREIGN KEY (`MemberID`) REFERENCES `members` (`MemberID`);
ALTER TABLE `member_groups` ADD FOREIGN KEY (`GroupID`) REFERENCES `groups` (`GroupID`);
One-to-Many relationship
 |