数据库规范化
如何将表从关系模式规范化为 BCNF。有人可以详细解释一下以下关系模式到 BCNF 的规范化吗？
info(id, companyName, contactName, contactTitle, address, city, postalCode, country, phone, fax, email, website)
how to normalize a table from a relational schema upto BCNF. Can someone please explain the normalization of below relational schema upto BCNF with details.
info(id, companyName, contactName, contactTitle, address, city, postalCode, country, phone, fax, email, website)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
仅当定义了函数依赖性时才能进行规范化。给我一组功能依赖关系（例如，id 唯一确定companyName，postalCode 唯一确定Country 等），我们就可以讨论规范化。
让我再次声明：如果您不向我们提供函数依赖关系，任何理性的人都无法尝试回答您的问题。如果您需要帮助为此架构提出一组有意义的功能依赖关系，我们可以疯狂地假设您的数据应遵循哪些规则，但最终由您来告诉我们您的业务规则是什么。
话虽这么说：一旦有了函数依赖关系，在 BCNF 中获取关系就相当简单了。当且仅当每个函数依赖项的左侧都是超级键时，您的关系就属于 BCNF 形式。如果您的函数依赖违反了这一点，只需将其分离为新关系即可。然后你将把你的非 BCNF 关系转换成一组要么是 BCNF 要么不是 BCNF 的关系；如果是这样，请停止；否则，继续。请注意，此过程最终会终止，因为两列的关系在 BCNF 中是微不足道的。
You can only do normalization when you have defined functional dependencies. Give me a set of functional dependencies (e.g., id uniquely determines companyName, postalCode uniquely determines Country, etc.) and we can talk about normalization.
Let me state this again: no rational human being can attempt to answer your question without your giving us the functional dependencies. If you want help coming up with a set of meaningful functional dependencies for this schema, we can wildly hypothesize as to what rules your data should follow, but ultimately it's up to you to tell us what your business rules are.
That being said: once you have the functional dependencies, getting the relation in BCNF is fairly straightforward. Your relation is in BCNF iff the left side of every functional dependency is a superkey. If you have a functional dependency that violates this, simply spin that off as a new relation. Then you'll transform your non-BCNF relation into a set of relations which are either in BCNF or not; if so, stop; otherwise, continue. Note that this process eventually terminates because relations with two columns are trivially in BCNF.
Boyce Codd 范式仅意味着您处于第三范式，此外外键在表之间链接，其他列不会导致不一致。我喜欢下面链接中的示例，该示例显示了描述“在附近购物”的表格。它有一个人的主键，一个商店的主键，然后是另一个显示商店类型的字段。这违反了 BCNF，因为“商店类型”字段可能与商店的主键不一致。
BCNF在这里得到了很好的解释：http://en.wikipedia.org/wiki/Boyce %E2%80%93Codd_normal_form
我做了一些假设，但以下是我如何分割字段。有些人喜欢更进一步，将地址分开，但我觉得这已经超出了第三种形式的规范化。我注意到您有一个国家/地区字段，因此我还将电话字段拆分为国际拨号，因为号码会根据您拨打和拨打的国家/地区而变化。
公司
company_id、companyName、地址、城市、州、邮政编码、country_id、phoneCountryCode、phoneAreaCode、phoneVoiceNumber、phoneExtension、phoneFaxNumber
联系人
contact_id、company_id、contactName、contactTitle、contactEmail
国家
country_id、countryName
Boyce Codd normal form just means that you are in 3rd normal form with the addition that foreign keys are linked between tables, without the ability for other columns to cause inconsistencies. I like the example in the link below showing a table describing "Shop Near Person". It has a primary key to a person, a primary key to a shop, then another field showing the type of shop. This violates BCNF because the "shop type" field may not be consistent which the shop's primary key.
BCNF is explained decently here: http://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form
I'm making some assumptions, but here is how I would split the fields. Some people like to go further and split addresses out, but I feel that is normalizing beyond the 3rd form. I noticed you had a country field, so I also split the phone fields up for international dialing, as the number will change depending on which country you are calling from and to.
Companies
company_id, companyName, address, city, state, postalCode, country_id, phoneCountryCode, phoneAreaCode, phoneVoiceNumber, phoneExtension, phoneFaxNumber
Contacts
contact_id, company_id, contactName, contactTitle, contactEmail
Countries
country_id, countryName