数据库设计——是否尊重第三个NF?
我在关系模型中有以下关系(表)
Person
person_id, first_name, last_name, address
Student
person_id, matr_nr
Teacher
person_id, salary
Lecture
lecture_id, lect_name, lect_description
Attendees
lecture_id, person_id, date
我想知道学生和教师的功能依赖关系。
这些表符合第三范式吗?这些表的主键应该是哪个?
I have the following relations (tables) in a relational model
Person
person_id, first_name, last_name, address
Student
person_id, matr_nr
Teacher
person_id, salary
Lecture
lecture_id, lect_name, lect_description
Attendees
lecture_id, person_id, date
I'm wondering about the functional dependencies of Student and Teacher.
Do these tables respect the 3rd normal form? Which should be the primary keys of these tables?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
使用“表继承”(松散地)和联接表等概念,我会以这种方式进行设置:
其中学生和教师表从人员“继承”,而参加者表是讲座和学生之间的联接表(teacher_id 用于讲座表指定谁在授课。通过连接表最佳实践,该表实际上应命名为 Lecture_Student 或类似名称)
替代设计:(允许一个班级有多名教师)
Using concepts like "table inheritance" (loosely) and join tables I would set things up in this manner:
Where Student and Teacher tables "inherit" from Person and the Attendees table is a Join table between Lecture and Student (teacher_id is used in the Lecture table to specify who's teaching the class. And by Join table best practice the table ought actually be named Lecture_Student or similar)
Alternate Design: (allows for multiple teachers of a class)
据我们所知,我认为它是 3NF 标准化的(密钥、整个密钥,除了密钥什么都没有),但它可能无法解决您的问题域问题。
您的主键将是 _id 列 - 与会者除外。其中两个 _id 列 - 但这不能适应在不同日期参加同一讲座(或者技术上这会是不同的 Lecture_id 吗?)在我构建的课程安排系统中,我们在班级中有部分,但问题域因为这可能比你所给予的要大得多。
学生和老师的问题已经被提出了 - 两者都将是参加者,这就是你所知道的 - 所有老师都可以在教学,或者只有其中一些可以在教学,或者他们可以是同伴(例如研讨会)
我认为这首先是一个领域建模问题,然后是标准化问题......
I think it's 3NF normalized (the key, the whole key, and nothing but the key) as far as what we know, but it might not solve your problem domain issues.
Your primary keys would be the _id columns - except for attendees. where it would be both _id columns - but this would not accommodate attending the same lecture on different dates (or would that technically be a different lecture_id?) In a class scheduling system I built, we had sections in a class, but the problem domain for this could be much larger than what you have given.
The problem with students and teachers has already been raised - both will be attendees, that's about all you will know - all the teachers could be teaching or only some of them could be teaching, or they could be peers (seminar, say)
I think this is more of a domain modeling issue first, then normalization...
“这些表遵循第三范式吗?”
只有当您告诉我们键是什么以及完整的函数依赖关系集是什么时,这个问题才可以回答。
如果没有这一点,任何人给出的任何答案都只能是回答者非零猜测的结果,并且您无法保证该猜测符合您的业务现实。
也就是说,您可以查看与会者。那里很可能有什么东西腐烂了。
(嘿,你声称自己不想被灌输。)
"Do these tables respect the 3rd normal form?"
That question is only answerable if you tell us what the keys are and what the full set of functional dependencies is.
Without that, any answer anyone gives can only be the result of a nonzero amount of guesswork on the part of the answerer, and you have no guarantee that that guesswork corresponds to your business reality.
That said, you might look into the Attendees. It's more than likely that there's something rotten there.
(Hey, you claimed yourself you didn't want to be spoonfed.)
这绝对不是3NF。上述问题的简单规范化设计(假设 person_id 唯一标识教师和学生,并且每个讲座有一名教师)如下:
彼此具有相同键的关系是相同的关系。
It's definitely not 3NF. A simple, normalised design for the above problem (assuming that person_id uniquely identifies both teachers and students, and that there is one teacher per lecture) would be as follows:
Relations that have the same key as each other are the same relation.