1. Each time a new state is added or updated in the States table verify that the

Question

1. Each time a new state is added or updated in the States table verify that the StateCode follows this pattern - First 5 character same as the StateName followed by the number of characters in the state name

for example

Washington code will be 'WASHI10'

Alabama code will be 'ALABA10'

California code will be 'CALIF6'

2. Create a function that takes Property name as input and returns a list of all the recipes that have that property.

3. Category Name and Property Name cannot be same. Every time a new category name is added or updated check if the same category name is listed as a property name. Similarly, every time a new property name is added or updated check if the same property name is listed as a category name.

Reclnads RecipePropertv Property Ingds ProplD PropertyName RID 7 Ingdld Ingdld ProplD Mesurld IngdName Qty Persons Person!D FirstName LastName AddressID Recipes RID RName Mesmts RecipeOwners person!D Mesurld RecipeDate MDesc RID Addresses Address!D StAddress City StateCode Zip States RecipeCats RID CID StateCode StateName Categories CatName

Explanation / Answer

1. The best way to do this would be to use a trigger at each update or insert event in states table. The trigger can verify the legitimacy of the state code, and if it violates the constraints, delete the newly inserted row (for insertion), or revert back the change (for updation - which will be deletion followed by insertion).

CREATE TRIGGER after_state_insert ON [States]

FOR INSERT

AS

IF((SELECT [StateCode] FROM inserted)!= (SELECT SUBSTRING(StateName,1,5)+LEN(StateName) FROM inserted))

DELETE FROM [States] WHERE [StateCode] = (SELECT [StateCode] FROM inserted)

GO

CREATE TRIGGER after_state_update ON [States]

FOR UPDATE

AS

IF((SELECT [StateCode] FROM inserted)!= (SELECT SUBSTRING(StateName,1,5)+LEN(StateName) FROM inserted))

UPDATE [States] SET [StateCode] = (SELECT [StateCode] FROM deleted) WHERE STATECODE = (SELECT [StateCode] FROM inserted)

GO

2. The function must select all recipes from [RecipeProperty] table, but needs to join this table with [Property] table as the input is PropertyName, and not PropertyID itself.

CREATE function getRecipes

@PropertyName varchar INPUT

@RecipeID int OUTPUT

returns table as

return (SELECT @RecipeID=RecipeProperty.RID

FROM RecipeProperty JOIN Property ON RecipeProperty.PropID = Property.PropID

   WHERE Property.PropertyName = @PropertyName

)

3.

We can do this again by using triggers on Categories and Property tables

CREATE TRIGGER after_property_update ON [Property]

FOR UPDATE

AS

IF((SELECT [PropertyName] FROM inserted) IN (SELECT [CategoryName] FROM [Categories]))

UPDATE [Property] SET [PropertyName] = (SELECT [PropertyName] FROM deleted) WHERE PropID = (SELECT [PropID] FROM inserted)

GO

CREATE TRIGGER after_property_insert ON [Property]

FOR INSERT

AS

IF((SELECT [PropertyName] FROM inserted) IN (SELECT [CategoryName] FROM [Categories]))

DELETE FROM [Property] WHERE [PropID] = (SELECT [PropID] FROM inserted)

GO

Similarly, triggers can be made for Category table as well.

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.