Can you guys show me how to get this ERD in 5th normal form? We did a class assignment yesterday where we had a real client come in and explain to us his need my group came up with this model I am trying to take it a step further and see if I can get it to 5th normal form so that I can understand it better as next week we will be doing another group ERD that will have to be in 5nf the thing is I don't know where to start and the book is not getting through to me. can you guys help me.Erd Model http://img534.imageshack.us/img534/2118/capturekk.jpg
你能告诉我如何以第5范式获得这个ERD吗?昨天我们做了一个班级任务,我们有一个真正的客户进来并向我们解释他的需要我的小组想出了这个模型我试图更进一步,看看我能不能把它变成第5范式以便我可以更好地理解它,因为下周我们将做另一组ERD,必须在5nf,事情是我不知道从哪里开始,这本书没有通过我。你能帮助我吗.Erd Model http://img534.imageshack.us/img534/2118/capturekk.jpg
New information "Fake Clients Needs"
新信息“虚假客户需求”
The client has 2 departments "Lab 1, Lab 2" that order medical items from him, like gloves and pi pets etc.. the departments have grown and he wants to keep track of what is in inventory what the vendors are selling who has the best price and what cost center it came from.
客户有两个部门“实验室1,实验室2”从他那里订购医疗用品,如手套和π宠物等。各部门已经成长,他想跟踪库存*应商销售的商品。最优惠的价格和成本中心。
The way he explained it the item is ordered is that an individual goes to him and asks for supplies he then writes down what lab the person is from the persons name and what cost center to bill. but he also wants to track what has been purchased by date and what is in stock and what has to be bought. He also gave an example that some users want items from a specific vendor that might not have the best prices and he also receives some catalogs in the mails with prices too that he wants to track.
他解释这个项目的方式是订购的是一个人去找他并要求提供物品,然后他写下这个人从人名到什么实验室的费用。但他也希望跟踪按日期购买的商品以及库存和必须购买的商品。他还举了一个例子,一些用户希望来自特定供应商的商品可能没有最优惠的价格,并且他还收到邮件中的一些目录,其价格也是他想跟踪的。
I apologize if its jumbled but this is all from memory.
如果它混乱,我道歉,但这都是记忆。
2 个解决方案
#1
4
I'm going to stick my neck out and say your current schema doesn't really have any eligibility for 5NF. Taking a quote from wikipedia, it says most 4NF tables already conform to 5NF:
我要坚持下去,说你当前的架构并没有5NF的任何资格。从*引用,它说大多数4NF表已经符合5NF:
Usage Only in rare situations does a 4NF table not conform to 5NF. These are situations in which a complex real-world constraint governing the valid combinations of attribute values in the 4NF table is not implicit in the structure of that table. If such a table is not normalized to 5NF, the burden of maintaining the logical consistency of the data within the table must be carried partly by the application responsible for insertions, deletions, and updates to it; and there is a heightened risk that the data within the table will become inconsistent. In contrast, the 5NF design excludes the possibility of such inconsistencies.
用法仅在极少数情况下,4NF表不符合5NF。在这些情况下,管理4NF表中属性值的有效组合的复杂现实约束不会隐含在该表的结构中。如果这样的表没有标准化为5NF,那么维护表中数据的逻辑一致性的负担必须部分由负责插入,删除和更新的应用程序承担;并且表格中的数据变得不一致的风险更高。相比之下,5NF设计排除了这种不一致的可能性。
http://en.wikipedia.org/wiki/Fifth_normal_form
Perhaps the purpose is not to make your database 5NF, but to understand it's structure compared to 5NF and make an argument for why it might already be in 5NF.
也许目的不是让你的数据库成为5NF,而是要理解它与5NF相比的结构,并为它为什么已经在5NF中进行论证。
Update: reading some form of consensus from the comments, it seems your design may already be in 5NF, whether it was by design or by accident from a previous normalization pass.
更新:从评论中读取某种形式的共识,似乎你的设计可能已经在5NF,无论是设计还是意外的前一次规范化传递。
#2
1
Disclaimer: Without knowing your business logic I could be completely wrong with some of the following suggestions.
免责声明:在不了解您的业务逻辑的情况下,我可能完全错误地提出以下一些建议。
Ok a few things I saw in your database schema.
好的,我在数据库模式中看到了一些东西。
- Your cost center table looks to be both a lookup and association table. So if you have a set amount of cost centers lets say 5 for instance but a 1000 users then this table will have 5000 records which all store the cost center name (this is assuming that the cost center assignment is not unique to each user). You might want to split off the user association to a separate table which has it's own PK and FKs of costcenterId and userId.
- You inventory table, which I assume stores amount of stock on hand for an item, should only need an onhand quantities not sure what the description is for. If the description isn't unique to each item inventory you might want to split that off into it's own lookup table and reference it in the inventory table.
- You are storing "lineQuantity" in the vendor table. What is this column for. It sounds like it relates to a specific order in some way (quantity of item purchased?) If so you will want to split it off into it's own table and associate it with the order not the vendor. without knowing exactly what lineQuantity is though this suggestion could be completely wrong.
- Last suggestion not sure if this matters for your class. You are defining all your datatypes to be CHAR(10) you might want to change these to be the actual datatypes you would use for the database.
您的成本中心表看起来既是查找表,也是关联表。因此,如果您有一定数量的成本中心,例如5个但是1000个用户,则此表将有5000条记录,这些记录都存储成本中心名称(假设成本中心分配对每个用户而言并非唯一)。您可能希望将用户关联拆分为单独的表,该表具有自己的PK以及costcenterId和userId的FK。
您的库存表,我假设存储库存的库存量,应该只需要一个不确定描述的数量。如果描述对于每个项目清单不是唯一的,您可能希望将其拆分为自己的查找表并在清单表中引用它。
您在供应商表中存储“lineQuantity”。这个专栏是什么?这听起来像是以某种方式与特定订单相关(购买的商品数量?)如果是这样,您将需要将其拆分为自己的表并将其与订单而非供应商相关联。虽然这个建议可能完全错误,但却不知道究竟是什么lineQuantity。
最后的建议不确定这对你的班级是否重要。您将所有数据类型定义为CHAR(10),您可能希望将这些数据类型更改为将用于数据库的实际数据类型。
#1
4
I'm going to stick my neck out and say your current schema doesn't really have any eligibility for 5NF. Taking a quote from wikipedia, it says most 4NF tables already conform to 5NF:
我要坚持下去,说你当前的架构并没有5NF的任何资格。从*引用,它说大多数4NF表已经符合5NF:
Usage Only in rare situations does a 4NF table not conform to 5NF. These are situations in which a complex real-world constraint governing the valid combinations of attribute values in the 4NF table is not implicit in the structure of that table. If such a table is not normalized to 5NF, the burden of maintaining the logical consistency of the data within the table must be carried partly by the application responsible for insertions, deletions, and updates to it; and there is a heightened risk that the data within the table will become inconsistent. In contrast, the 5NF design excludes the possibility of such inconsistencies.
用法仅在极少数情况下,4NF表不符合5NF。在这些情况下,管理4NF表中属性值的有效组合的复杂现实约束不会隐含在该表的结构中。如果这样的表没有标准化为5NF,那么维护表中数据的逻辑一致性的负担必须部分由负责插入,删除和更新的应用程序承担;并且表格中的数据变得不一致的风险更高。相比之下,5NF设计排除了这种不一致的可能性。
http://en.wikipedia.org/wiki/Fifth_normal_form
Perhaps the purpose is not to make your database 5NF, but to understand it's structure compared to 5NF and make an argument for why it might already be in 5NF.
也许目的不是让你的数据库成为5NF,而是要理解它与5NF相比的结构,并为它为什么已经在5NF中进行论证。
Update: reading some form of consensus from the comments, it seems your design may already be in 5NF, whether it was by design or by accident from a previous normalization pass.
更新:从评论中读取某种形式的共识,似乎你的设计可能已经在5NF,无论是设计还是意外的前一次规范化传递。
#2
1
Disclaimer: Without knowing your business logic I could be completely wrong with some of the following suggestions.
免责声明:在不了解您的业务逻辑的情况下,我可能完全错误地提出以下一些建议。
Ok a few things I saw in your database schema.
好的,我在数据库模式中看到了一些东西。
- Your cost center table looks to be both a lookup and association table. So if you have a set amount of cost centers lets say 5 for instance but a 1000 users then this table will have 5000 records which all store the cost center name (this is assuming that the cost center assignment is not unique to each user). You might want to split off the user association to a separate table which has it's own PK and FKs of costcenterId and userId.
- You inventory table, which I assume stores amount of stock on hand for an item, should only need an onhand quantities not sure what the description is for. If the description isn't unique to each item inventory you might want to split that off into it's own lookup table and reference it in the inventory table.
- You are storing "lineQuantity" in the vendor table. What is this column for. It sounds like it relates to a specific order in some way (quantity of item purchased?) If so you will want to split it off into it's own table and associate it with the order not the vendor. without knowing exactly what lineQuantity is though this suggestion could be completely wrong.
- Last suggestion not sure if this matters for your class. You are defining all your datatypes to be CHAR(10) you might want to change these to be the actual datatypes you would use for the database.
您的成本中心表看起来既是查找表,也是关联表。因此,如果您有一定数量的成本中心,例如5个但是1000个用户,则此表将有5000条记录,这些记录都存储成本中心名称(假设成本中心分配对每个用户而言并非唯一)。您可能希望将用户关联拆分为单独的表,该表具有自己的PK以及costcenterId和userId的FK。
您的库存表,我假设存储库存的库存量,应该只需要一个不确定描述的数量。如果描述对于每个项目清单不是唯一的,您可能希望将其拆分为自己的查找表并在清单表中引用它。
您在供应商表中存储“lineQuantity”。这个专栏是什么?这听起来像是以某种方式与特定订单相关(购买的商品数量?)如果是这样,您将需要将其拆分为自己的表并将其与订单而非供应商相关联。虽然这个建议可能完全错误,但却不知道究竟是什么lineQuantity。
最后的建议不确定这对你的班级是否重要。您将所有数据类型定义为CHAR(10),您可能希望将这些数据类型更改为将用于数据库的实际数据类型。