Create the BATCH table with a foreign key constraint named FK_DRUG_ID to reference the DRUG table's DRUG_ID column, ensuring each batch is associated with a specific drug. Include a check constraint named CK_QUANTITY to ensure the QUANTITY column has a positive value.
Question
Create the BATCH table with a foreign key constraint named FK_DRUG_ID to reference the DRUG table's DRUG_ID column, ensuring each batch is associated with a specific drug. Include a check constraint named CK_QUANTITY to ensure the QUANTITY column has a positive value.
Solution
Here is the SQL command to create the BATCH table with the foreign key constraint named FK_DRUG_ID and a check constraint named CK_QUANTITY:
CREATE TABLE BATCH
(
BATCH_ID INT PRIMARY KEY,
DRUG_ID INT,
QUANTITY INT,
FOREIGN KEY (DRUG_ID) REFERENCES DRUG(DRUG_ID) ON DELETE CASCADE,
CONSTRAINT CK_QUANTITY CHECK (QUANTITY > 0)
);
Here's a step-by-step breakdown of the command:
-
CREATE TABLE BATCH: This command is used to create a new table named BATCH. -
(BATCH_ID INT PRIMARY KEY, DRUG_ID INT, QUANTITY INT): This part of the command defines the columns in the BATCH table. BATCH_ID is the primary key, and DRUG_ID and QUANTITY are other columns. -
FOREIGN KEY (DRUG_ID) REFERENCES DRUG(DRUG_ID) ON DELETE CASCADE: This part of the command creates a foreign key constraint named FK_DRUG_ID. This constraint ensures that each batch is associated with a specific drug. TheON DELETE CASCADEclause means that if a record in the parent table (DRUG) is deleted, then the corresponding records in the child table (BATCH) will automatically be deleted. -
CONSTRAINT CK_QUANTITY CHECK (QUANTITY > 0): This part of the command creates a check constraint named CK_QUANTITY. This constraint ensures that the QUANTITY column has a positive value.
Similar Questions
Dia is responsible for managing a pharmaceutical production and distribution system. This system tracks information about drugs, production batches, and shipments. She needs to ensure that the data across these tables is accurate and meaningful.Write the DDL statements to create the following tables with the specified constraints:symbol refers to the primary key NN refers to Not NULL Create the DRUG table to store information about different drugs.Create the BATCH table with a foreign key constraint named FK_DRUG_ID to reference the DRUG table's DRUG_ID column, ensuring each batch is associated with a specific drug. Include a check constraint named CK_QUANTITY to ensure the QUANTITY column has a positive value.Create the SHIPMENT table with a foreign key constraint named FK_BATCH_ID to reference the BATCH table's BATCH_ID column, ensuring each shipment is linked to a specific production batch. Include a check constraint named CK_QUANTITY_SHIPPED to ensure the QUANTITY_SHIPPED column has a positive value.Note: The user must write only the query to create and alter the table. The query to display the description of the table is already given.Input format :No console inputOutput format :The output displays the successful table creation status and the structure of all three tablesRefer to the sample output.Sample test cases :Input 1 :Output 1 :Table DRUG created.Table BATCH created.Table SHIPMENT created. Name Null? Type ____________________ ___________ ________________ DRUG_ID NOT NULL NUMBER DRUG_NAME NOT NULL VARCHAR2(100) ACTIVE_INGREDIENT NOT NULL VARCHAR2(100) CATEGORY NOT NULL VARCHAR2(50) Name Null? Type __________________ ___________ _______________ BATCH_ID NOT NULL NUMBER DRUG_ID NOT NULL NUMBER BATCH_NUMBER NOT NULL VARCHAR2(50) PRODUCTION_DATE NOT NULL DATE QUANTITY NOT NULL NUMBER Name Null? Type ___________________ ___________ ________________ SHIPMENT_ID NOT NULL NUMBER BATCH_ID NOT NULL NUMBER SHIPMENT_DATE NOT NULL DATE DESTINATION NOT NULL VARCHAR2(255) QUANTITY_SHIPPED NOT NULL NUMBER TABLE_NAME SEARCH_CONDITION STATUS _____________ __________________________________ __________ BATCH QUANTITY > 0 ENABLED BATCH "DRUG_ID" IS NOT NULL ENABLED BATCH "BATCH_NUMBER" IS NOT NULL ENABLED BATCH "PRODUCTION_DATE" IS NOT NULL ENABLED BATCH "QUANTITY" IS NOT NULL ENABLED DRUG "DRUG_NAME" IS NOT NULL ENABLED DRUG "ACTIVE_INGREDIENT" IS NOT NULL ENABLED DRUG "CATEGORY" IS NOT NULL ENABLED SHIPMENT QUANTITY_SHIPPED > 0 ENABLED SHIPMENT "BATCH_ID" IS NOT NULL ENABLED SHIPMENT "SHIPMENT_DATE" IS NOT NULL ENABLED SHIPMENT "DESTINATION" IS NOT NULL ENABLED SHIPMENT "QUANTITY_SHIPPED" IS NOT NULL ENABLED 13 rows selected.
David is developing a recipe management system for a cooking app. The system tracks users, their recipes, and ingredients. Write the DDL statements to create the following tables with the mentioned constraints to handle the data: symbol refers to the primary key NN refers to Not NULLCreate a USERS table with a unique constraint named Users_Email_uk on the Email column.Create an INGREDIENTS table with a unique constraint named unique_ingredient_id on the IngredientID column.Remove the unique constraint named Users_Email_uk from the USERS table as it is no longer required.Add a new unique constraint named Recipes_RecipeName_uk on the RecipeName column in the RECIPES table.Note: The user must write only the query to create and alter the table. The query to display the description of the table is already given.Input format :No console inputOutput format :The output displays the successful table creation status and the structure of all three tablesRefer to the sample output.Sample test cases :Input 1 :Output 1 :Table USERS created.Table RECIPES created.Table INGREDIENTS created.Table USERS altered.Table RECIPES altered. Name Null? Type ___________ ________ ________________ USERID NUMBER USERNAME VARCHAR2(100) EMAIL VARCHAR2(100) Name Null? Type _____________ ___________ ________________ RECIPEID NOT NULL NUMBER USERID NOT NULL NUMBER RECIPENAME VARCHAR2(100) Name Null? Type _________________ ___________ ________________ INGREDIENTID NOT NULL NUMBER RECIPEID NOT NULL NUMBER INGREDIENTNAME VARCHAR2(100) TABLE_NAME CONSTRAINT_NAME STATUS ______________ ________________________ __________ INGREDIENTS UNIQUE_INGREDIENT_ID ENABLED RECIPES RECIPES_RECIPENAME_UK ENABLED 2 rows selected.
Create the MENUS table with the Price column should have a constraint named chk_price to ensure the value is greater than 0.Create the ORDERS table with the Quantity column should have a constraint named chk_quantity to ensure the value is greater than 0.After creating the tables, Mia needs to:Alter the MENUS table to drop the existing chk_price constraint, as the pricing strategy has been updated. Add a new CHECK constraint named chk_price_new to ensure that the Price is at least $5.Alter the ORDERS table to drop the existing chk_quantity constraint, as the quantity policy has changed. Add a new CHECK constraint named chk_quantity_new to ensure that the Quantity is between 1 and 100.
Given the following table definition for the shipment table, sp:CREATE TABLE sp (spno VARCHAR(4) NOT NULL, sno VARCHAR(3) NOT NULL, pno VARCHAR(3) NOT NULL, qty SMALLINT NOT NULL CHECK (qty>=0), PRIMARY KEY (spno), FOREIGN KEY (sno) REFERENCES s ON DELETE RESTRICT ON UPDATE CASCADE, FOREIGN KEY (pno) REFERENCES p ON DELETE RESTRICT ON UPDATE CASCADE );And the following data on the supplier, s, part, p, and shipment, sp, tables: supplier (s)part (p)shipment (sp)The following update will be allowed on the supplier table, s: update sSET sno = 'S20'WHERE sno='S1';
Nihariya is responsible for managing a smart farming system that monitors various fields, crops, and field conditions. She needs to ensure that the data in these tables is accurate and meaningful.Write the DDL statements to create the following tables with the mentioned constraints to handle the data:symbol refers to the primary key NN refers to Not NULL Create the FIELD table to store information about the fields.Create the CROP table with a foreign key constraint named FK_FIELD_ID to reference the FIELD (FIELD_ID), ensuring each crop is associated with a specific field.Create the FIELD_CONDITION table with a foreign key constraint named FK_FIELD_CONDITION_FIELD_ID to reference the FIELD table's FIELD_ID column and a check constraint named CK_CONDITION_TYPE to ensure the CONDITION_TYPE column contains one of these four values: 'soil moisture', 'temperature', 'humidity', 'pH'.Note: The user must write only the query to create and alter the table. The query to display the description of the table is already given.Input format :No console inputOutput format :The output displays the successful table creation status and the structure of all three tablesRefer to the sample output.Sample test cases :Input 1 :Output 1 :Table FIELD created.Table CROP created.Table FIELD_CONDITION created. Name Null? Type _____________ ___________ ________________ FIELD_ID NOT NULL NUMBER FIELD_NAME NOT NULL VARCHAR2(100) LOCATION NOT NULL VARCHAR2(100) Name Null? Type ________________ ___________ ________________ CROP_ID NOT NULL NUMBER CROP_NAME NOT NULL VARCHAR2(100) PLANTING_DATE NOT NULL DATE FIELD_ID NOT NULL NUMBER Name Null? Type _________________ ___________ _______________ CONDITION_ID NOT NULL NUMBER FIELD_ID NOT NULL NUMBER CONDITION_TYPE NOT NULL VARCHAR2(50) VALUE NOT NULL NUMBER RECORDED_AT NOT NULL TIMESTAMP(6) 0 rows selected. Note :The program will be evaluated only after the “Submit Code” is clicked.Extra spaces and new line characters in the program output will result in the failure of the test case.
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.