Use MySQL Workbench
Photo_sharing.sql:
use [STUDENT_CSID];
—
— Table structure for table `user`
—
CREATE TABLE `user` (
`idUser` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL
);
—
— Indexes for table `user`
—
ALTER TABLE `user`
ADD PRIMARY KEY (`idUser`);
— ——————————————————–
—
— Table structure for table `photo`
—
CREATE TABLE `photo` (
`idPhoto` int(11) NOT NULL,
`idUser` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
`description` varchar(255) NOT NULL
);
—
— Indexes for table `photo`
—
ALTER TABLE `photo`
ADD PRIMARY KEY (`idPhoto`);
—
— Constraints for table `photo`
—
ALTER TABLE `photo`
ADD CONSTRAINT `user_fk1` FOREIGN KEY (`idUser`) REFERENCES `user`(`idUser`);
— ——————————————————–
—
— Table structure for table `comment`
—
CREATE TABLE `comment` (
`idPhoto` int(11) NOT NULL,
`idUser` int(11) NOT NULL,
`text` varchar(255) NOT NULL
);
—
— Indexes for table `comment`
—
ALTER TABLE `comment`
ADD PRIMARY KEY (`idPhoto`,`idUser`);
—
— Constraints for table `comment`
—
ALTER TABLE `comment`
ADD CONSTRAINT `photo_fk1` FOREIGN KEY (`idPhoto`) REFERENCES`photo` (`idPhoto`),
ADD CONSTRAINT `user_fk2` FOREIGN KEY (`idUser`) REFERENCES `user`(`idUser`);
— ——————————————————–
—
— Dumping data for table `user`
—
INSERT INTO `user` (`idUser`, `name`, `password`) VALUES
(1, ‘Albert’, ‘passwordForAlbert’),
(2, ‘Max’, ‘passwordForMax’),
(3, ‘Sarah’, ‘passwordForSarah’),
(4, ‘John’, ‘passwordForJohn’),
(5, ‘Albert’, ‘passwordForAlbert’),
(6, ‘Sarah’, ‘passwordForSarah’),
(7, ‘Julia’, ‘passwordForJulia’),
(8, ‘Michael’, ‘passwordForMichael’),
(9, ‘Rose’, ‘passwordForRose’);
— ——————————————————–
—
— Dumping data for table `photo`
—
INSERT INTO `photo` (`idPhoto`, `title`, `description`,`idUser`) VALUES
(1, ‘Max’s Photo’, ‘This is a photo of Max with his cat’,2),
(2, ‘Sarah’s Photo’, ‘This is a photo of Sarah in her suit.’,3),
(3, ‘John’s Photo’, ‘This is a photo of John in front of HenryHicks building in Dalhousie University.’, 4),
(4, ‘Albert’s Photo’, ‘This is a photo of Albert with his dog.’,1),
(5, ‘Albert’s Photo’, ‘This is a photo of Albert in front ofGoldberg Computer Science building in Dalhousie University.’,5);
— ——————————————————–
—
— Dumping data for table `comment`
—
INSERT INTO `comment` (`idPhoto`, `idUser`, `text`) VALUES
(1, 1, ‘You have a cool cat max!! :)’),
(1, 2, ‘Thank You! Albert.’),
(2, 2, ‘Black Suit suits you Sarah!’),
(2, 3, ‘Thank You! Max.’),
(3, 8, ‘Good luck with your courses John!’),
(3, 9, ‘Michael, I know that John is graduated from Dal ;)’),
(3, 4, ‘Rose is right 🙂 The photo is old!!’),
(5, 7, ‘Nice building!’);
Lab Task By selecting from related tables and using a WHERE clause based on the foreign key, you can view related data between tables. Question 1: Create a query that lists all the photos that have the word ‘Thank you’ in their comments. “comment’ table has ‘idPhoto’ as foreign key that references the ‘idPhoto’ attribute in the ‘photo’ table. Tip: Both photo and comment tables have idPhoto attribute. So you must specify the table the attribute is from (e.g. by using photo.idPhoto). Question 2: List vendors that supply less than 3 products? This question puts a condition upon the resulting groups. This is a perfect place to use Having clause. Try executing the query and take the screenshot of the results. Show transcribed image text Lab Task By selecting from related tables and using a WHERE clause based on the foreign key, you can view related data between tables. Question 1: Create a query that lists all the photos that have the word ‘Thank you’ in their comments. “comment’ table has ‘idPhoto’ as foreign key that references the ‘idPhoto’ attribute in the ‘photo’ table. Tip: Both photo and comment tables have idPhoto attribute. So you must specify the table the attribute is from (e.g. by using photo.idPhoto). Question 2: List vendors that supply less than 3 products? This question puts a condition upon the resulting groups. This is a perfect place to use Having clause. Try executing the query and take the screenshot of the results.
Expert Answer
Answer to Use MySQL Workbench Photo_sharing.sql: use [STUDENT_CSID]; — — Table structure for table `user` — CREATE TABLE `user`…