In what normal forms is a simple DB, why, and how can it be maximally normalized?

[Math-IT-Lang]

Please kindly tell me in what normal forms (..., 3NF, BCNF, 4NF, 5NF, 6NF, etc.) (why, and how to maximally normalize) the following example of a "database" formed by solving the many-to-many relationship between the tables tbl_Books and tbl_Bookshops:

The [out_of_stock] field is of logical type (Yes/No).
tbl_Bookshops (bookshop_id, user_name)
tbl_Books (book_id, book_name, out_of_stock, sale_price)
tbl_Distributions (library_id, bookshop_id, cost_price)

Thank you in advance!

[June7]

What is library_id for - a foreign key for book_id?

What is user_name for - is this really store name?

[Math-IT-Lang]

Thank you very much for the competent answer and I apologize for not replying immediately, I thought that this site always gave an error, as it says in the upper right of the screen, and I did not return to the site. I will give the correct statement of the problem soon:

[Math-IT-Lang]

Yes, you guessed right. In tbl_Distributions, book_id and bookstore_id are foreign keys for book_id in tbl_Books, respectively for bookstore_id in tbl_Bookstores.

Please kindly answer a rather theoretical question:

in what normal forms (..., 3NF, BCNF, 4NF, 5NF, 6NF, etc.) (why, and how to maximally normalize) the following example of a simple database formed by solving the many-to-many relationship between the tables tbl_Books and tbl_ Bookstores:

The [out_of_stock] field is of logical type (Yes/No).

The tbl_Books table stores book titles.

tbl_Bookstores (bookstore_id, bookstore_name)
tbl_Books (book_id, book_name, out_of_stock, sale_price)

tbl_Distributions (book_id, bookstore_id, total_cost)

In my opinion, the database is at least in normal form 3, in which case most documentations stop normalizing it, because for normal purposes it works as is. However, I would like us to do a deeper analysis on this simple example, because the mathematical theories involved in the study of the next normal forms, BCNF, NF4, NF5, etc. that the average user like me has access to are a bit too abstract even for a math teacher.
Thank you very much again!

[June7]

I am not formally trained in RDBMS concepts. I don't really give much thought to the 'forms'. My philosophy is it's a balancing act between normalization and ease of data entry/edit and I follow the guideline of "Normalize until it hurts, denormalize until it works."

Having that said, I will agree that in most cases, 3NF is far enough.

An out_of_stock field may be inappropriate as stock balances would normally be calculated from transaction records.

[Math-IT-Lang]

I am not formally trained in RDBMS concepts. I don't really give much thought to the 'forms'. My philosophy is it's a balancing act between normalization and ease of data entry/edit and I follow the guideline of "Normalize until it hurts, denormalize until it works."

Having that said, I will agree that in most cases, 3NF is far enough.

An out_of_stock field may be inappropriate as stock balances would normally be calculated from transaction records.

Thank you for your kind reply.
I'm not formally trained in concepts like normalization in RDBMS either, but I'm just scratching my head with this sort of thing. But it seems that knowing these notions makes the database more functional, leading to elimination of update anomalies, etc. of data and to several possible queries.

In my previous reply I meant to say that I consider the above database in 3NF (and possibly in 4NF etc.)

Also I consider the out_of_stock field appropriate because depending on its value (Yes/No) we can create other queries such as: What is the total amount obtained from the sale of out-of-stock books grouped by bookstores, sorted by bookstores?

[June7]

You can use out_of_stock field if you want but the risk of it getting 'out of sync' with actual data is a consideration. Whether or not a book is out_of_stock could be calculated when needed. Review http://allenbrowne.com/AppInventory.html

[Math-IT-Lang]

You can use out_of_stock field if you want but the risk of it getting 'out of sync' with actual data is a consideration. Whether or not a book is out_of_stock could be calculated when needed. Review http://allenbrowne.com/AppInventory.html

Thanks for the tip. The question was: in what normal forms is the originally proposed database (... NF3, BCNF, NF4, NF5, etc.), why and why not is it in the following normal forms?
It seems to me that the web address you sent is virused.

[SpywareDr]

According to VirusTotal.com, the page "http://allenbrowne.com/AppInventory.html" is Safe. See here: https://www.virustotal.com/

If you add a "s" to the end of "http" you might see "Your connection isn't private".

[June7]

Allen Browne's articles are well-known and commonly cited references. Unless someone has hacked the host site, pages are not virused.

[Math-IT-Lang]

According to VirusTotal.com, the page "http://allenbrowne.com/AppInventory.html" is Safe. See here: https://www.virustotal.com/

If you add a "s" to the end of "http" you might see "Your connection isn't private".

Thank you very much for this detailed information.

[Math-IT-Lang]

Allen Browne's articles are well-known and commonly cited references. Unless someone has hacked the host site, pages are not virused.

Thank you.

[Math-IT-Lang]

But I still expect targeted answers to the question in the original post ("Poll").
Thank you in advance!