我是否正确规范了此数据 [英] Am I Properly Normalizing this Data

问题描述

我正在完成网上的标准化练习，以测试我对数据进行标准化的能力。在以下位置找到此特定问题：

可以从该表创建的非规范化表为：

要符合第一范式形式，我必须通过将visitdate，procedure_no和procedure_name移至表中来消除表中的重复字段各自的表：

这也符合2NF和3NF，这使我怀疑我是否正确执行了标准化过程。如果我没有正确地从UNF转到1NF，请提供反馈。

解决方案

第一步，您可以创建以下表格（假设 pet_id 在表中是唯一的）：

 宠物：pet_id，pet_name，pet_type，pet_age，所有者
访问次数：pet_id ，visit_date，过程
  

进一步，您可以拆分程序由于重复说明：

 宠物：pet_id，pet_name，pet_type，pet_age，所有者
访问次数：pet_id ，visit_date，procedure_id 
程序：procedure_id，说明
  

尽管可以有多个在相同的访问日期对于相同的 pet_id 的过程，我认为没有理由进一步拆分：将日期（理论上）存储在2个字节中，拆分该数据将产生更多开销（加上额外的索引）。

也想将 pet_age 更改为 pet_birth_date 年龄会随着时间而改变。

由于这是您列表中的第一项练习，因此上述内容可能绰绰有余。

更进一步：

一个所有者可以有多个宠物，因此可以创建另一个表：

 宠物所有者：owner_id，所有者名称
  

，然后仅在宠物表中使用 owner_id 。在真实的系统中，会有 customer_id，姓名，地址，电话，电子邮件等-因此应该始终在单独的表中。

您甚至可以对 pet_type 进行相同操作，并将 id 存储在1或2中字节，但这全取决于您稍后要对数据进行查询的类型。

I am completing normalization exercises from the web to test my abilities to normalize data. This particular problem was found at: https://cs.senecac.on.ca/~dbs201/pages/Normalization_Practice.htm (Exercise 1)

The table this problem is based of is as follows:

The unnormalized table that can be created from this table is:

To comply with First Normal form, I have to get rid of repeating fields in the table by moving visitdate, procedure_no, and procedure_name to their own respective tables:

This also complies with 2NF and 3NF which makes me question whether I have performed the process of normalization correctly. Please provide feedback if I did not properly move from UNF to 1NF.

解决方案

In a first step you could create the following tables (assuming pet_id is unique in the table):

Pets:   pet_id, pet_name, pet_type, pet_age, owner
Visits: pet_id, visit_date, procedure

Going further you could split procedure since the description is repeating:

Pets:       pet_id, pet_name, pet_type, pet_age, owner
Visits:     pet_id, visit_date, procedure_id
Procedures: procedure_id, description

Although there can be multiple procedures on the same visit_date for the same pet_id, I see no reason to split those further: a date could (in theory) be stored in 2 bytes, and splitting that data would create more overhead (plus an extra index).

You would also want to change pet_age to pet_birth_date since the age changes over time.

Since this is the first exercise in your list, the above will probably be more than enough.

Going even further:

An owner can have multiple pets, so another table could be created:

Pet_owners: owner_id, owner_name

and then only use owner_id in the Pets table. In a real system there would be customer_id, name, address, phone, email, etc. - so that should always be in a separate table.

You could even do the same for pet_type and store the id in 1 or 2 bytes, but it all depends on the type of queries you want to do later on the data.

这篇关于我是否正确规范了此数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！