View Poll Results: How do you rate the answers (minimum: 0, Maximum: 3)?

Voters
0. You may not vote on this poll
  • 0

    0 0%
  • 1

    0 0%
  • 2

    0 0%
  • 3

    0 0%
Results 1 to 13 of 13

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

  1. #1
    Join Date
    Mar 2023
    Posts
    8

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

    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!

  2. #2
    Join Date
    Nov 2020
    Posts
    36
    What is library_id for - a foreign key for book_id?

    What is user_name for - is this really store name?
    Last edited by June7; 03-25-2023 at 08:35 PM.

  3. #3
    Join Date
    Mar 2023
    Posts
    8
    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:
    Last edited by Math-IT-Lang; 04-05-2023 at 08:25 AM.

  4. #4
    Join Date
    Mar 2023
    Posts
    8
    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!

  5. #5
    Join Date
    Nov 2020
    Posts
    36
    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.

  6. #6
    Join Date
    Mar 2023
    Posts
    8

    Thumbs up

    Quote Originally Posted by June7 View Post
    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?
    Last edited by Math-IT-Lang; 04-05-2023 at 08:23 AM.

  7. #7
    Join Date
    Nov 2020
    Posts
    36
    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

  8. #8
    Join Date
    Mar 2023
    Posts
    8
    Quote Originally Posted by June7 View Post
    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.

  9. #9
    Join Date
    Feb 2014
    Location
    Riviera Beach, Maryland, USA
    Posts
    86
    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".

  10. #10
    Join Date
    Nov 2020
    Posts
    36
    Allen Browne's articles are well-known and commonly cited references. Unless someone has hacked the host site, pages are not virused.

  11. #11
    Join Date
    Mar 2023
    Posts
    8
    Quote Originally Posted by SpywareDr View Post
    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.
    Last edited by Math-IT-Lang; 04-24-2023 at 10:13 AM.

  12. #12
    Join Date
    Mar 2023
    Posts
    8
    Quote Originally Posted by June7 View Post
    Allen Browne's articles are well-known and commonly cited references. Unless someone has hacked the host site, pages are not virused.
    Thank you.

  13. #13
    Join Date
    Mar 2023
    Posts
    8
    But I still expect targeted answers to the question in the original post ("Poll").
    Thank you in advance!
    Last edited by Math-IT-Lang; 06-05-2023 at 03:31 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •