Scenarios 1) The low level data model of the Beer database is provided below: DATABASE: BAR (Bar_name[pk], City, Phone, Owner_SSN[pk,fk]) BEER (Beer_name[pk], Type, Country, Proof) CUST (SSN[pk], Name, Birth_year, Fav_beer[fk], Gender) LIKES (SSN[pk,fk], Beer[pk,fk]) SERVES (Bar[fk], Beer[fk]) OWNER (SSN[pk], Name, City, Address, Phone) For this database, all the tables are created and populated in a dump file that you can download it from here. This part must be done individually and NOT in groups. You need to import the dump file to your MySQL as a new database (instructions on how to import a database can be found in page 10 of MySQL tutorial) and then perform the following queries: 7) Customers should like their favorite beer. List the SSN and name of all customers who DO NOT like their favorite beer. 8) List the name of owners whose bars serve beer type miller. 9) List of owners who live in the same city that Mr. Lubber (name of one of the owners) lives. 10) List the beers served by at least one bar and liked by at least one customer. 11) List the SSN, name, address, and phone number of all owners who do not serve Coors in their bars. 12) List SSN of the customers who like the same beer. The list should be in form of pairs of SSNs. That is, the SSNs of two persons who like the same beer. 13) Find total number of bars that serve Coors. 14) Find the total number of beers that are liked by customers. Note that if a beer is repeatedly liked by several people, it is just counted as once. 15) An active bar is defined as a bar that serves more than 3 beers (beer names/brands). List the name of active bars. That is, for the bars that serve more than three beers (i.e., the diversity is three or more), list the bar names and the number beers each one of the bars serves. Show the results in the descending order of number of beers. 16) Find the most favored beer (Fav_beer) of customers. 17) Old customers are those who have more than 50 years old. List the number of old customers from each gender, only if they are more than 3. For instance, if the number of old male customers is 2, it should not be shown in the results. 18) Write a stored procedure that for a given customer (SSN is given in form of an input parameter) it shows the list of beers the customer likes, in the ascending order of beer name. 19) Write a stored procedure that accepts an owners phone number and shows the list of information of bars (s)he owns. 20) Write a transaction that adds bars for a given person the list of his/her bars. The transaction includes several (e.g., four) INSERT operations into the BAR table. The transaction acts in a way that if the system crashes in the middle of the registration process, all the registered bars are rolled back. The dumpfile -- MySQL dump 10.13 Distrib 5.7.19, for Win64 (x86_64) -- -- Host: localhost Database: beers -- ------------------------------------------------------ -- Server version 5.7.19-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `bar` -- DROP TABLE IF EXISTS `bar`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `bar` ( `bar_name` varchar(25) NOT NULL, `city` varchar(20) DEFAULT NULL, `phone` varchar(15) DEFAULT NULL, `owner_ssn` int(11) DEFAULT NULL, PRIMARY KEY (`bar_name`), KEY `owner_ssn_idx` (`owner_ssn`), CONSTRAINT `owner_ssn` FOREIGN KEY (`owner_ssn`) REFERENCES `owner` (`SSN`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `bar` -- LOCK TABLES `bar` WRITE; /*!40000 ALTER TABLE `bar` DISABLE KEYS */; INSERT INTO `bar` VALUES ('EastEnders','Miami','134-552-7871',221349364),('Heartbeat','Houston','220-820-4161',275864176),('Queen of the Damned','Houston','128-668-7424',523232050),('Red Dwarf','Boston','945-292-6553',511306765),('Shameless','Los Angeles','128-668-7424',523232050),('Smallville','Lafayette','942-765-6640',650582627); /*!40000 ALTER TABLE `bar` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `beer` -- DROP TABLE IF EXISTS `beer`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `beer` ( `Beer_name` varchar(20) NOT NULL, `Type` varchar(15) DEFAULT NULL, `Country` varchar(15) DEFAULT NULL, `Proof` varchar(15) DEFAULT NULL, PRIMARY KEY (`Beer_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `beer` -- LOCK TABLES `beer` WRITE; /*!40000 ALTER TABLE `beer` DISABLE KEYS */; INSERT INTO `beer` VALUES ('Becks','Ale','Australia','No'),('BlueMoon','Lager','Uk','Yes'),('Budweiser','Lager','China','Yes'),('Coors','Ale','USA','No'),('Corona','Miller','Brazil','No'),('Dos Equis','Stout','UK','Yes'),('Fat Tire','Miller','Australia','No'),('Guinness','Ale','USA','Yes'),('Michelob','Stout','China','yes'),('Heineken','Gruit','Dutch','Yes'),('Stella','Sahti','Belgium','No'); /*!40000 ALTER TABLE `beer` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `cust` -- DROP TABLE IF EXISTS `cust`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `cust` ( `SSN` int(11) NOT NULL, `Name` varchar(45) DEFAULT NULL, `BirthYear` year(4) DEFAULT NULL, `fav_beer` varchar(20) DEFAULT NULL, `Gender` char DEFAULT NULL, PRIMARY KEY (`SSN`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `cust` -- LOCK TABLES `cust` WRITE; /*!40000 ALTER TABLE `cust` DISABLE KEYS */; INSERT INTO `cust` VALUES (96986805,'Chesley',1952,'Coors','F'),(222066861,'Vella',1955,'Fat Tire','F'),(304013933,'Keaton',1980,'Guinness','M'),(327562676,'Graham',1984,'Guinness','F'),(332648695,'Jerrell',1984,'BlueMoon','M'),(369662021,'Danyka',1945,'Budweiser','F'),(521848178,'Sheldon',1952,'Budweiser','M'),(542861762,'Sherwood',1980,'Dos Equis','M'),(574545373,'Russel',1970,'Coors','M'),(677032327,'Ena',1981,'Corona','F'),(699019648,'Bella',1960,'Budweiser','F'); /*!40000 ALTER TABLE `cust` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `likes` -- DROP TABLE IF EXISTS `likes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `likes` ( `SSN` int(11) NOT NULL, `Beer` varchar(20) NOT NULL, PRIMARY KEY (`SSN`,`Beer`), KEY `beer_idx` (`Beer`), CONSTRAINT `SSN` FOREIGN KEY (`SSN`) REFERENCES `cust` (`SSN`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `beer` FOREIGN KEY (`Beer`) REFERENCES `beer` (`Beer_name`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `likes` -- LOCK TABLES `likes` WRITE; /*!40000 ALTER TABLE `likes` DISABLE KEYS */; INSERT INTO `likes` VALUES (332648695,'BlueMoon'),(332648695,'Budweiser'),(304013933,'Corona'),(304013933,'Dos Equis'),(542861762,'Dos Equis'),(222066861,'Fat Tire'),(574545373,'Guinness'),(369662021,'Michelob'),(96986805,'Coors'),(304013933,'BlueMoon'); /*!40000 ALTER TABLE `likes` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `owner` -- DROP TABLE IF EXISTS `owner`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `owner` ( `SSN` int(11) NOT NULL, `Name` varchar(20) DEFAULT NULL, `City` varchar(20) DEFAULT NULL, `Address` varchar(30) DEFAULT NULL, `phone` varchar(15) DEFAULT NULL, PRIMARY KEY (`SSN`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `owner` -- LOCK TABLES `owner` WRITE; /*!40000 ALTER TABLE `owner` DISABLE KEYS */; INSERT INTO `owner` VALUES (221349364,'Kiara McCullough','South Odessa','Johnston Str aptC 79','134-552-7871'),(221421823,'Norberto Wiegand','Gilbertfort','200 Oak crest dr aptD','729.755.0144'),(275864176,'Thad Wilderman','Port Alizemouth','1100 Robley Dr 481','1-220-820-4161'),(420320544,'Kirsten Baumbach V','Port Alizemouth','1100 Robley Dr 882','1-502-956-7294'),(507303584,'Rosalia Heaney','West Monicastad','1100 Robley Dr 708','979-133-3774'),(511306765,'Fabian Pagac','North Jaidatown','110 E Martial Ave 901','945-292-6553'),(523232050,'Lubber','Port Alizemouth','Johnston Str aptC 400','128-668-7424'),(601528621,'Gloria Fritsch','East Marcelino','110 E Martial Ave 469','1-771-872-4409'),(608524482,'Parker Considine','Barrowsberg','111 Banister C','326.660.9581'),(650582627,'Agustina Hettinger','East Roman','411 Dulles Dr','942-765-6640'); /*!40000 ALTER TABLE `owner` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `serves` -- DROP TABLE IF EXISTS `serves`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `serves` ( `bar` varchar(25) NOT NULL, `beers` varchar(20) NOT NULL, PRIMARY KEY (`bar`,`beers`), KEY `beer_idx` (`beers`), CONSTRAINT `bar` FOREIGN KEY (`bar`) REFERENCES `bar` (`bar_name`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `beers` FOREIGN KEY (`beers`) REFERENCES `beer` (`Beer_name`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `serves` -- LOCK TABLES `serves` WRITE; /*!40000 ALTER TABLE `serves` DISABLE KEYS */; INSERT INTO `serves` VALUES ('EastEnders','Coors'),('Smallville','Coors'),('Smallville','Corona'),('EastEnders','Fat Tire'),('Shameless','Guinness'),('EastEnders','Stella'), ('Heartbeat','Michelob'),('EastEnders','Heineken'),('Smallville','Budweiser'),('Red Dwarf','Coors'),('EastEnders','BlueMoon'),('Smallville','Dos Equis'); /*!40000 ALTER TABLE `serves` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2018-03-13 14:12:20 Beer_Database.sql Displaying Beer_Database.sql.