The Complete Book Database System by Garsia-Molino?

|
kiteoomoru 2021-04-19 21:50:29
mysql_en-9260.jpg

kiteoomoru 2021-04-19 21:50:30
Hello everyone.
Im kite. I’m new to MySQL. I’ve been trying to create a trigger that updates an attribute(quantity in stock) on a table when orders are placed and also gives an error message when the quantity in stock is not enough.
I’ve written a statement but I keep getting a syntax error near END IF;.
Can someone tell me what I’m doing wrong. I will highly appreciate.
MasterZiv 2021-04-19 22:35:34
kiteoomoru 2021-04-19 21:50:30
Hello everyone.
Im kite. I’m new to MySQL. I’ve been trying to create a trigger that updates an attribute(quantity in stock) on a table when orders are placed and also gives an error message when the quantity in stock is not enough.
I’ve written a statement but I keep getting a syntax error near END IF;.
Can someone tell me what I’m doing wrong. I will highly appreciate.

Send code as text, please.

kiteoomoru 2021-04-19 22:37:42
mysql> DELIMITER $$
mysql> CREATE TRIGGER quantity_update AFTER INSERT ON orderdetails
-> FOR EACH ROW
-> BEGIN
-> UPDATE products
-> SET products.quantityInStock=products.quantityInStock-NEW.quantityOrdered
-> WHERE products.productCode=NEW.productCode;
->
-> IF NEW.quantityOrdered > quantityInStock THEN
-> SIGNAL SQLSTATE ‘45000’
-> SET MESSAGE_TEXT = ‘not enough quantity in stock’
-> END IF;
-> END$$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘END IF;
END’ at line 11
MasterZiv 2021-04-19 22:38:22
kiteoomoru 2021-04-19 21:50:30
Hello everyone.
Im kite. I’m new to MySQL. I’ve been trying to create a trigger that updates an attribute(quantity in stock) on a table when orders are placed and also gives an error message when the quantity in stock is not enough.
I’ve written a statement but I keep getting a syntax error near END IF;.
Can someone tell me what I’m doing wrong. I will highly appreciate.

This IF, you can’t do it like this, you must do it in a query.
In particular, I recommend doing it in your UPDATE
OR in a SELECT after the UPDATE

MasterZiv 2021-04-19 22:41:50
kiteoomoru 2021-04-19 22:37:42
mysql> DELIMITER $$
mysql> CREATE TRIGGER quantity_update AFTER INSERT ON orderdetails
-> FOR EACH ROW
-> BEGIN
-> UPDATE products
-> SET products.quantityInStock=products.quantityInStock-NEW.quantityOrdered
-> WHERE products.productCode=NEW.productCode;
->
-> IF NEW.quantityOrdered > quantityInStock THEN
-> SIGNAL SQLSTATE ‘45000’
-> SET MESSAGE_TEXT = ‘not enough quantity in stock’
-> END IF;
-> END$$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘END IF;
END’ at line 11

quantityInStock here is a field in the PRODUCTS table record, you can’t mention it on the trigger level, for this it should be a variable.
But it is not a variable , it’s a field.

END IF you should write together, ENDIF, I suppose, re-check it in the manual

rodrigo_tc7 2021-04-19 23:08:30
kiteoomoru 2021-04-19 22:37:42
mysql> DELIMITER $$
mysql> CREATE TRIGGER quantity_update AFTER INSERT ON orderdetails
-> FOR EACH ROW
-> BEGIN
-> UPDATE products
-> SET products.quantityInStock=products.quantityInStock-NEW.quantityOrdered
-> WHERE products.productCode=NEW.productCode;
->
-> IF NEW.quantityOrdered > quantityInStock THEN
-> SIGNAL SQLSTATE ‘45000’
-> SET MESSAGE_TEXT = ‘not enough quantity in stock’
-> END IF;
-> END$$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘END IF;
END’ at line 11

You missed a ;

rodrigo_tc7 2021-04-19 23:09:35
After:

… In stock’

kiteoomoru 2021-04-20 00:24:24
rodrigo_tc7 2021-04-19 23:09:35
After:

… In stock’

Thank you!
It worked

Chika Stephnie 2021-04-20 00:39:45
Someone should help me with this confusion as a budding data scientist do I need to know about ER diagram design
piterden 2021-04-20 02:11:02
Chika Stephnie 2021-04-20 00:39:45
Someone should help me with this confusion as a budding data scientist do I need to know about ER diagram design

ER-diagram is just visual presentation of your DB structure.

Anyway you should make a choice what to learn by yourself because it is very easy to make mistake.

piterden 2021-04-20 02:14:38
rodrigo_tc7 2021-04-19 23:08:30
You missed a ;

MySQL has amazing error handler )))

MasterZiv 2021-04-20 05:44:58
Chika Stephnie 2021-04-20 00:39:45
Someone should help me with this confusion as a budding data scientist do I need to know about ER diagram design

ER diagrams is a very simple thing to learn, it is a weak of study at maximum.
If you are a scientist, there will be no problem for you to learn them.

Chika Stephnie 2021-04-20 06:50:30
MasterZiv 2021-04-20 05:44:58
ER diagrams is a very simple thing to learn, it is a weak of study at maximum.
If you are a scientist, there will be no problem for you to learn them.

Thanks but can you help me with a good material/article for ER diagram

piterden 2021-04-20 08:49:14
MasterZiv 2021-04-20 05:44:58
ER diagrams is a very simple thing to learn, it is a weak of study at maximum.
If you are a scientist, there will be no problem for you to learn them.

Week

Chika Stephnie 2021-04-20 09:17:21
piterden 2021-04-20 02:11:02
ER-diagram is just visual presentation of your DB structure.

Anyway you should make a choice what to learn by yourself because it is very easy to make mistake.

Please can someone help me with a good material for ER diagrams

piterden 2021-04-20 09:23:39
Chika Stephnie 2021-04-20 09:17:21
Please can someone help me with a good material for ER diagrams

Google

Chika Stephnie 2021-04-20 09:26:52
piterden 2021-04-20 09:23:39
Google

Since you are familiar with it you would know which is better

piterden 2021-04-20 09:27:13
Chika Stephnie 2021-04-20 09:26:52
Since you are familiar with it you would know which is better

Yes

piterden 2021-04-20 09:27:16
Google
Chika Stephnie 2021-04-20 09:27:40
piterden 2021-04-20 09:27:16
Google

That’s my point

Chika Stephnie 2021-04-20 09:28:08
piterden 2021-04-20 09:27:16
Google

Or at least give me a link/url

piterden 2021-04-20 09:30:55
Chika Stephnie 2021-04-20 09:28:08
Or at least give me a link/url

https://lmgtfy.app/#gsc.tab=0&gsc.q=er%20diagram

LMGTFY – Let Me Google That For Youlmgtfy.app
For all those people who find it more convenient to bother you with their question rather than to Google it for themselves.
Chika Stephnie 2021-04-20 09:32:39
piterden 2021-04-20 09:30:55
https://lmgtfy.app/#gsc.tab=0&gsc.q=er%20diagram

Haha… thanks

piterden 2021-04-20 09:42:00
Chika Stephnie 2021-04-20 09:32:39
Haha… thanks

The knowledge about ER-diagrams is not as important as the knowledge about how to find this information quickly. You anyway can never learn everything! Also, I sure if you will read this material today and will not practice it, after a few years your memory will keep only common image of information you read. So I suggest you to learn how to googling neither than asking in chats

MasterZiv 2021-04-20 09:53:56
Chika Stephnie 2021-04-20 09:17:21
Please can someone help me with a good material for ER diagrams

I’ll try to find some, later…

MasterZiv 2021-04-20 10:36:08
Chika Stephnie 2021-04-20 09:17:21
Please can someone help me with a good material for ER diagrams

I don’t know what is better for you, I found some articles on Google and thrown
complete garbage away.
Here it is:

https://www.visual-paradigm.com/guide/data-modeling/what-is-entity-relationship-diagram/

http://www.inf.unibz.it/~franconi/teaching/2000/ct481/er-modelling/

https://www.smartdraw.com/entity-relationship-diagram/

There is another flavor of ER-diags like described here

Ultimate Entity Relationship Diagram Tutorial (ER Diagrams)

But probably it is better not to read WEB articles but to read some decent book by Christopher Date or Garsia-Molino.

What is Entity Relationship Diagram (ERD)?Visual-Paradigm
Learn Entity Relationship Diagram (ERD). Read this ERD guide for everything you need to know about data modeling and database design with ERD.
MasterZiv 2021-04-20 10:37:36
Chika Stephnie 2021-04-20 09:32:39
Haha… thanks

Don’t flame !
☝️

smlkw 2021-04-20 10:40:33
MasterZiv 2021-04-20 10:37:36
Don’t flame !
☝️

Don’t be serious 😉

smlkw 2021-04-20 10:41:33
MasterZiv 2021-04-20 10:36:08
I don’t know what is better for you, I found some articles on Google and thrown
complete garbage away.
Here it is:

https://www.visual-paradigm.com/guide/data-modeling/what-is-entity-relationship-diagram/

http://www.inf.unibz.it/~franconi/teaching/2000/ct481/er-modelling/

https://www.smartdraw.com/entity-relationship-diagram/

There is another flavor of ER-diags like described here

Ultimate Entity Relationship Diagram Tutorial (ER Diagrams)

But probably it is better not to read WEB articles but to read some decent book by Christopher Date or Garsia-Molino.

A great sentence at the end

Chika Stephnie 2021-04-20 10:50:07
smlkw 2021-04-20 10:41:33
A great sentence at the end

Thank you so much 👏this is what motivated me to ask this questions here and some persons who think they have the moral justification to dictate what should) shouldn’t be asked in this forum discouraging others from asking theirs even if the question appears stupid beginners here can always learn

smlkw 2021-04-20 10:57:14
Chika Stephnie 2021-04-20 10:50:07
Thank you so much 👏this is what motivated me to ask this questions here and some persons who think they have the moral justification to dictate what should) shouldn’t be asked in this forum discouraging others from asking theirs even if the question appears stupid beginners here can always learn

It’s okay, please, ask whatever you want here but you also must try to find answers by yourself first

Before asking people, do the following:

– search similar questions in the web (people probably already asked similar questions)
– look into books you can get (authors write not just to earn money but to give useful and important information)

then if you could not find any answer – ask people, it’s the best way to learn any topic and not to look “a noob” here

ranasharif 2021-04-20 11:14:25
smlkw 2021-04-20 10:41:33
A great sentence at the end

👍❤️❤️❤️❤️❤️

ranasharif 2021-04-20 11:16:28
MasterZiv 2021-04-20 10:36:08
I don’t know what is better for you, I found some articles on Google and thrown
complete garbage away.
Here it is:

https://www.visual-paradigm.com/guide/data-modeling/what-is-entity-relationship-diagram/

http://www.inf.unibz.it/~franconi/teaching/2000/ct481/er-modelling/

https://www.smartdraw.com/entity-relationship-diagram/

There is another flavor of ER-diags like described here

Ultimate Entity Relationship Diagram Tutorial (ER Diagrams)

But probably it is better not to read WEB articles but to read some decent book by Christopher Date or Garsia-Molino.

The Complete Book Database System 2nd Edition.

Do you know about new edition of this
The Complete Book Database System by Garsia-Molino?

MasterZiv 2021-04-20 11:16:58
ranasharif 2021-04-20 11:16:28
The Complete Book Database System 2nd Edition.

Do you know about new edition of this
The Complete Book Database System by Garsia-Molino?

No, I don’t

|