The following SQL for a Point of Sale database for a grocery store is not properly constructed. As result, when student attempts to populate the tables, it gives a foreign key constraint fail error. Modify the SQL so that it models the depicted schema and follows the following constraints: They is a one-to-many relationship between the salesdetails.SalesNumber and sales.SalesNumber fields.  There is a one-to-many relationship between the products.prodid and salesdetails.prodid fields.  There is to be a delete constraint on the product.prodid field a record in the product table should not be allowed to be deleted if they exist a matching prodid in the salesdetails table.  Primary and foreign keys should be implemented in the appropriate tables.  Referential Integrity should be enforce at the database level, where appropriate for example, if a sales record is deleted all associated records in the saledetails table should be deleted as well, etc. CREATE TABLE `products` ( `number` int(11) NOT NULL, `prodid` varchar(20) DEFAULT NULL, `prodname` varchar(30) DEFAULT NULL, `price` decimal(10,2) DEFAULT NULL, `onhand` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `sales` -- CREATE TABLE `sales` ( `SalesNumber` int(10) NOT NULL, `SalesDate` date DEFAULT NULL, `SalesTotal` decimal(10,2) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `salesdetails` -- CREATE TABLE `salesdetails` ( `number` int(10) NOT NULL, `SalesNo` int(10) DEFAULT NULL, `prodid` varchar(20) DEFAULT NULL, `price` decimal(7,2) DEFAULT NULL, `qty` int(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Indexes for dumped tables -- -- -- Indexes for table `products` -- ALTER TABLE `products` ADD PRIMARY KEY (`number`), ADD UNIQUE KEY `prodid` (`prodid`); -- -- Indexes for table `sales` -- ALTER TABLE `sales` ADD PRIMARY KEY (`SalesNumber`); -- -- Indexes for table `salesdetails` -- ALTER TABLE `salesdetails` ADD PRIMARY KEY (`number`), ADD UNIQUE KEY `productid` (`prodid`), ADD KEY `fk_salesdetails_sales` (`SalesNo`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `products` -- ALTER TABLE `products` MODIFY `number` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=747; -- -- AUTO_INCREMENT for table `sales` -- ALTER TABLE `sales` MODIFY `SalesNumber` int(10) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `salesdetails` -- ALTER TABLE `salesdetails` MODIFY `number` int(10) NOT NULL AUTO_INCREMENT; -- -- Constraints for dumped tables -- -- -- Constraints for table `products` -- ALTER TABLE `products` ADD CONSTRAINT `fk_salesdetails_product` FOREIGN KEY (`prodid`) REFERENCES `salesdetails` (`prodid`); -- -- Constraints for table `salesdetails` -- ALTER TABLE `salesdetails` ADD CONSTRAINT `fk_salesdetails_products` FOREIGN KEY (`prodid`) REFERENCES `products` (`prodid`) ON DELETE CASCADE, ADD CONSTRAINT `fk_salesdetails_sales` FOREIGN KEY (`SalesNo`) REFERENCES `sales` (`SalesNumber`) ON DELETE CASCADE; COMMIT;

The following SQL for a Point of Sale database for a grocery store is not properly constructed. As result, when student attempts to populate the tables, it gives a foreign key constraint fail error. Modify the SQL so that it models the depicted schema and follows the following constraints: They is a one-to-many relationship between the salesdetails.SalesNumber and sales.SalesNumber fields.  There is a one-to-many relationship between the products.prodid and salesdetails.prodid fields.  There is to be a delete constraint on the product.prodid field a record in the product table should not be allowed to be deleted if they exist a matching prodid in the salesdetails table.  Primary and foreign keys should be implemented in the appropriate tables.  Referential Integrity should be enforce at the database level, where appropriate for example, if a sales record is deleted all associated records in the saledetails table should be deleted as well, etc. CREATE TABLE `products` ( `number` int(11) NOT NULL, `prodid` varchar(20) DEFAULT NULL, `prodname` varchar(30) DEFAULT NULL, `price` decimal(10,2) DEFAULT NULL, `onhand` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `sales` -- CREATE TABLE `sales` ( `SalesNumber` int(10) NOT NULL, `SalesDate` date DEFAULT NULL, `SalesTotal` decimal(10,2) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `salesdetails` -- CREATE TABLE `salesdetails` ( `number` int(10) NOT NULL, `SalesNo` int(10) DEFAULT NULL, `prodid` varchar(20) DEFAULT NULL, `price` decimal(7,2) DEFAULT NULL, `qty` int(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Indexes for dumped tables -- -- -- Indexes for table `products` -- ALTER TABLE `products` ADD PRIMARY KEY (`number`), ADD UNIQUE KEY `prodid` (`prodid`); -- -- Indexes for table `sales` -- ALTER TABLE `sales` ADD PRIMARY KEY (`SalesNumber`); -- -- Indexes for table `salesdetails` -- ALTER TABLE `salesdetails` ADD PRIMARY KEY (`number`), ADD UNIQUE KEY `productid` (`prodid`), ADD KEY `fk_salesdetails_sales` (`SalesNo`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `products` -- ALTER TABLE `products` MODIFY `number` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=747; -- -- AUTO_INCREMENT for table `sales` -- ALTER TABLE `sales` MODIFY `SalesNumber` int(10) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `salesdetails` -- ALTER TABLE `salesdetails` MODIFY `number` int(10) NOT NULL AUTO_INCREMENT; -- -- Constraints for dumped tables -- -- -- Constraints for table `products` -- ALTER TABLE `products` ADD CONSTRAINT `fk_salesdetails_product` FOREIGN KEY (`prodid`) REFERENCES `salesdetails` (`prodid`); -- -- Constraints for table `salesdetails` -- ALTER TABLE `salesdetails` ADD CONSTRAINT `fk_salesdetails_products` FOREIGN KEY (`prodid`) REFERENCES `products` (`prodid`) ON DELETE CASCADE, ADD CONSTRAINT `fk_salesdetails_sales` FOREIGN KEY (`SalesNo`) REFERENCES `sales` (`SalesNumber`) ON DELETE CASCADE; COMMIT;

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

They is a one-to-many relationship between the salesdetails.SalesNumber and
sales.SalesNumber fields.
 There is a one-to-many relationship between the products.prodid and salesdetails.prodid fields.
 There is to be a delete constraint on the product.prodid field a record in the product table should not be allowed to be deleted if they exist a matching prodid in the salesdetails table.

 Primary and foreign keys should be implemented in the appropriate tables.

 Referential Integrity should be enforce at the database level, where appropriate for example, if a sales record is deleted all associated records in the saledetails table should be deleted as well, etc.

CREATE TABLE `products` (
`number` int(11) NOT NULL,
`prodid` varchar(20) DEFAULT NULL,
`prodname` varchar(30) DEFAULT NULL,
`price` decimal(10,2) DEFAULT NULL,
`onhand` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `sales`
--

CREATE TABLE `sales` (
`SalesNumber` int(10) NOT NULL,
`SalesDate` date DEFAULT NULL,
`SalesTotal` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `salesdetails`
--

CREATE TABLE `salesdetails` (
`number` int(10) NOT NULL,
`SalesNo` int(10) DEFAULT NULL,
`prodid` varchar(20) DEFAULT NULL,
`price` decimal(7,2) DEFAULT NULL,
`qty` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `products`
--
ALTER TABLE `products`
ADD PRIMARY KEY (`number`),
ADD UNIQUE KEY `prodid` (`prodid`);

--
-- Indexes for table `sales`
--
ALTER TABLE `sales`
ADD PRIMARY KEY (`SalesNumber`);

--
-- Indexes for table `salesdetails`
--
ALTER TABLE `salesdetails`
ADD PRIMARY KEY (`number`),
ADD UNIQUE KEY `productid` (`prodid`),
ADD KEY `fk_salesdetails_sales` (`SalesNo`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
MODIFY `number` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=747;

--
-- AUTO_INCREMENT for table `sales`
--
ALTER TABLE `sales`
MODIFY `SalesNumber` int(10) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `salesdetails`
--
ALTER TABLE `salesdetails`
MODIFY `number` int(10) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `products`
--
ALTER TABLE `products`
ADD CONSTRAINT `fk_salesdetails_product` FOREIGN KEY (`prodid`) REFERENCES `salesdetails` (`prodid`);

--
-- Constraints for table `salesdetails`
--
ALTER TABLE `salesdetails`
ADD CONSTRAINT `fk_salesdetails_products` FOREIGN KEY (`prodid`) REFERENCES `products` (`prodid`) ON DELETE CASCADE,
ADD CONSTRAINT `fk_salesdetails_sales` FOREIGN KEY (`SalesNo`) REFERENCES `sales` (`SalesNumber`) ON DELETE CASCADE;
COMMIT;

swen2005 sales
SalesNumber: int(10)
SalesDate : date
# Sales Total : decimal(7,2)
V swen2005 salesdetails
number: int(10)
# SalesNumber : int(10)
Ⓒ prodid: varchar(20)
# price : decimal(7,2)
# qty: int(10)
swen2005 products
number : int(11)
prodid: varchar(20)
Ⓒ prodname : varchar(30)
# price : decimal(10,2)
#onhand int(11)

Expert Solution

Step by step

Solved in 4 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.