Does anyone have any good scenarios for teaching relational databases and SQL? All the examples I can find are either trivial or have improbable domain constraints (like full name being unique).
有没有人有任何教学关系数据库和SQL的好方案?我可以找到的所有示例都是微不足道的或者具有不可能的域约束(例如全名是唯一的)。
I'm especially trying to find some good examples for normalisation: tables that don't immediately fit 3NF and BCNF. At the moment I'm using a different problem for each level.
我特别想找到一些标准化的好例子:不能立即适合3NF和BCNF的表格。目前我正在为每个级别使用不同的问题。
Of course, I'd also love good examples of badly designed databases, but it's a bit distracting until the fundamentals have been mastered.
当然,我也喜欢设计糟糕的数据库的好例子,但是在掌握了基础知识之前,它会有点分散注意力。
Thanks, some nice examples. I've marked the student/class one as the answer as I think it's the best so far, but if anyone wants to contribute some more, please do.
谢谢,一些很好的例子。我已经将学生/班级标记为答案,因为我认为这是目前为止最好的,但如果有人想要贡献更多,请做。
9 个解决方案
#1
I seem to remember student / class as being a classic, you can put grades in there as well to make it a bit more complex.
我似乎记得学生/班级是一个经典,你可以把成绩放在那里,使它更复杂一点。
-
Student can atted many classes
学生可以上很多课
-
Classes have many students
课程有很多学生
-
For eachclass a student attends they can have a grade
对于每个班级,学生都可以参加,他们可以获得成绩
Initially you can do this in one table and denormalise to three.
最初,您可以在一个表中执行此操作,并将非规范化执行到三个。
#2
off topic
Having taught database classes, I would suggest forgetting about design until the basics of querying are mastered. People understand the need for normalization much better once they understand how to get data out of a database. If you start with normalization and design, you will lose most of the students for the rest of the class. Design should be the very last module of a database course, but all the textbooks I ever reviewed started with it.
在教过数据库课程后,我建议在掌握查询基础知识之前忘记设计。一旦人们理解了如何从数据库中获取数据,人们就会更加理解对规范化的需求。如果你从规范化和设计开始,你将失去大部分学生在课堂上的其余部分。设计应该是数据库课程的最后一个模块,但我所审查的所有教科书都是从它开始的。
Even better make them query both good and bad database designs when learning querying and then they will really understand how painful bad design is when it is time to teach design.
在学习查询时,更好地让他们查询好的和坏的数据库设计,然后他们将真正理解在教授设计时糟糕的设计是多么痛苦。
#3
E-commerce/shopping cart design is good because most people understand the concept and you can push it in many different directions.
电子商务/购物车设计很好,因为大多数人都了解这个概念,你可以将它推向许多不同的方向。
You can do simple things like cart, cart_items, users, orders, order_items, etc.
你可以做一些简单的事情,比如cart,cart_items,用户,订单,order_items等。
Then you can go deeper with user_addresses, user_emails, items, item_details, item_history, etc.
然后你可以更深入地了解user_addresses,user_emails,items,item_details,item_history等。
This can provide a lot of good debate because there are lots of judgment calls.
这可以提供很多好的辩论,因为有很多判断调用。
#4
One concept that I will never forget is the whole "plural" versus "singular" thing with naming. A great mentor I had way back once told me that you should design your table names as plural and your column names as singular and that you never create temporal-specific names for column names. Temporal Names examples are NutsSold1998, NutsSold1999, NutsSold2000, etc. Never add a year or month or week number or time, etc to a column name.
我永远不会忘记的一个概念是命名的整个“复数”与“奇异”的东西。我曾经回过头来的一位伟大的导师告诉我,你应该将你的表名设计为复数,你的列名称应该是单数,并且你永远不会为列名创建特定于时间的名称。时间名称示例包括NutsSold1998,NutsSold1999,NutsSold2000等。切勿在列名称中添加年份,月份或周数或时间等。
Table Name Examples: Employees (not Employee) Parts (not Part) Students (Student)
表名示例:员工(非员工)部分(非部分)学生(学生)
Column Name Examples: EmployeeID (not EmployeesID or EmployeeIDS, etc) PartID (not PartsID or PartIDS, etc) StudentID (not StudentsID or StudentIDS, etc)
列名示例:EmployeeID(不是EmployeesID或EmployeeIDS等)PartID(不是PartsID或PartIDS等)StudentID(不是StudentsID或StudentIDS等)
And to pay attention to the correct usage of ID, Code, Key, Number, etc... I was always taught to not use "Key" in the name of a Column unless it was an actual table Key (primary or foreign, but not necessarily alternate).. Most of the time appending "ID" will be a better choice than appending "Number" or "Code", but it all depends on the context.
并注意正确使用ID,代码,密钥,数字等...我总是被教导不要在列的名称中使用“密钥”,除非它是一个实际的表密钥(主要或外国,但但不一定是替代)..大多数时候附加“ID”将是一个比附加“数字”或“代码”更好的选择,但这一切都取决于上下文。
And that comes with time and experience in designing tables, reading good materials such as the book Database Design For Mere Mortals and Data Modeling for Everyone. Also, spending lots of time looking at good designs and taking them apart. Its definitely a craft and you only get better with time and practice.
这需要时间和经验来设计表格,阅读好的材料,例如“为每个人设计数据库设计”和“为每个人设计数据建模”。此外,花费大量时间看好设计并将它们分开。它绝对是一种工艺,你只会随着时间和练习而变得更好。
#5
Here's an example that dates back to my own University days - it was used both as a database design challenge and as an Object Oriented design challenge.
这是一个可以追溯到我自己的大学时代的例子 - 它既被用作数据库设计挑战又被用作面向对象的设计挑战。
Not all information was revealed at once - part of the challenge was to see how to adapt a design to handle new requirements, and how proper normalisation made this easier.
并非所有信息都是立即显示出来的 - 部分挑战是了解如何调整设计以应对新要求,以及正确的规范化如何使这更容易。
Assume you have to design a database for a university/college situation and want to handle enrollments.
假设您必须为大学/大学情况设计数据库并希望处理注册。
You have the Courses taught. Each course has a title and a regular timeslot each week.
你教授的课程。每个课程每周都有一个标题和一个常规时间段。
Each Course has a Lecturer who presents the course.
每门课程都有一名讲师,负责介绍课程。
Each Course has many Students who study the course.
每门课程都有很多学习该课程的学生。
Each Course has one or more Tutors who help students with their study. You don't need to track which tutors help which students.
每门课程都有一名或多名辅导员帮助学生完成学业。您无需跟踪哪些辅导员帮助哪些学生。
Some courses have multiple regular timeslots.
有些课程有多个常规时段。
Some courses have multiple Lecturers.
有些课程有多个讲师。
Lecturers and Tutors are paid, which means we need to track some information for tax purposes. The Tax department doesn't care what they were paid for - they expect us to have a single record per person.
讲师和导师是有偿的,这意味着我们需要跟踪一些信息用于税收目的。税务部门并不关心他们支付的费用 - 他们希望我们每人只能记录一次。
On some courses, the Lecturer works as a Tutor as well, to get a close up view of how some students are handling the material.
在一些课程中,讲师也是一名导师,以近距离了解一些学生如何处理这些材料。
Some Tutors are also Lecturers, on other courses.
一些导师也是其他课程的讲师。
To be a Tutor on a course, you have to have been a Student on that course at an earlier time.
要成为课程的导师,您必须在较早的时间成为该课程的学生。
Not every student will receive credit for passing the course - some are just auditing the course without a need for credit.
并非每个学生都能通过该课程获得学分 - 有些只是在不需要学分的情况下审核课程。
A student who fails a course may attend the course again later on. We need to keep records of every attempt.
未通过课程的学生可以稍后再次参加该课程。我们需要保留每次尝试的记录。
#6
There's always the library example as well (A library has many books, each book has an author and publisher which can be pushed into separate tables as you normalise)
总是有图书馆的例子(一个图书馆有很多书,每本书都有一个作者和出版商,可以在你标准化时推入单独的表)
#7
Re:
I'm especially trying to find some good examples for normalisation: tables that don't immediately fit 3NF and BCNF.
我特别想找到一些标准化的好例子:不能立即适合3NF和BCNF的表格。
You can find normalized database schema samples here: http://www.microsoft.com/sqlserver/2005/en/us/express-starter-schemas.aspx. You can build them from the ground up to show your students the "way of normalization". Particularly look at the Contact Management schema. You can easily denormalize the schema and bring it back on 3NF or deeper.
您可以在此处找到规范化的数据库架构示例:http://www.microsoft.com/sqlserver/2005/en/us/express-starter-schemas.aspx。您可以从头开始构建它们,向学生展示“规范化的方式”。特别要看一下Contact Management架构。您可以轻松地对模式进行非规范化,并将其恢复到3NF或更深层次。
#8
Itzik Ben Gan's new book, Microsoft SQL Server 2008: T-SQL Fundamentals, has a very basic example which you can see is derived from a simplified Northwind database.
Itzik Ben Gan的新书,Microsoft SQL Server 2008:T-SQL Fundamentals,有一个非常基本的例子,你可以看到它来自简化的Northwind数据库。
Microsoft SQL Server 2008: T-SQL Fundamentals author support page
Microsoft SQL Server 2008:T-SQL Fundamentals作者支持页面
#9
Another good model is the invoice-item model, because the "best choice" depends on various factors:
另一个好的模型是发票项目模型,因为“最佳选择”取决于各种因素:
- number of write vs read operations;
- performance;
- are reporting functionalies needed?
写入与读取操作的次数;
报告所需的功能?
Look at this data model:
看看这个数据模型:
Invoice
- ID
- date
InvoiceItem
- invoiceID
- description
- amount
Application funcitonalities are:
应用功能是:
- create new invoice;
- each evening create and send two reports: (a) total amount for each invoice, (b) total amount of the day.
创建新发票;
每晚创建并发送两份报告:(a)每张发票的总金额,(b)当天的总金额。
Say you have an average of 5 items per invoice and 100 invoices per day, you end up doing, for each day:
假设您每天平均有5件物品和每天100张发票,您最终会每天都这样做:
- 5 x 100 writes on InvoiceItem;
- 100 writes on Invoice;
- 5 x 100 + 100 = 600 reads from Invoice and InvoiceItem (report (a));
- 5 x 100 + 100 = 600 reads from Invoice and InvoiceItem (report (b));
在InvoiceItem上写入5 x 100;
在Invoice上写入100;
从Invoice和InvoiceItem中读取5 x 100 + 100 = 600(报告(a));
从Invoice和InvoiceItem中读取5 x 100 + 100 = 600(报告(b));
so total is = 1800 operations / day, assuming reads and writes have the same cost.
因此,假设读取和写入具有相同的成本,则总计= 1800次操作/天。
If you add "TotalAmount" attribute on entity "Invoice", situation is a bit different:
如果在实体“Invoice”上添加“TotalAmount”属性,情况会有所不同:
- 5 x 100 writes on InvoiceItem;
- 100 writes on Invoice (including TotalAmount);
- 100 from Invoice only (report (a));
- 100 from Invoice only (report (b));
在InvoiceItem上写入5 x 100;
在Invoice上写入100(包括TotalAmount);
仅来自发票100(报告(a));
仅来自发票100(报告(b));
with a total of 800 operations :)
共有800个操作:)
#1
I seem to remember student / class as being a classic, you can put grades in there as well to make it a bit more complex.
我似乎记得学生/班级是一个经典,你可以把成绩放在那里,使它更复杂一点。
-
Student can atted many classes
学生可以上很多课
-
Classes have many students
课程有很多学生
-
For eachclass a student attends they can have a grade
对于每个班级,学生都可以参加,他们可以获得成绩
Initially you can do this in one table and denormalise to three.
最初,您可以在一个表中执行此操作,并将非规范化执行到三个。
#2
off topic
Having taught database classes, I would suggest forgetting about design until the basics of querying are mastered. People understand the need for normalization much better once they understand how to get data out of a database. If you start with normalization and design, you will lose most of the students for the rest of the class. Design should be the very last module of a database course, but all the textbooks I ever reviewed started with it.
在教过数据库课程后,我建议在掌握查询基础知识之前忘记设计。一旦人们理解了如何从数据库中获取数据,人们就会更加理解对规范化的需求。如果你从规范化和设计开始,你将失去大部分学生在课堂上的其余部分。设计应该是数据库课程的最后一个模块,但我所审查的所有教科书都是从它开始的。
Even better make them query both good and bad database designs when learning querying and then they will really understand how painful bad design is when it is time to teach design.
在学习查询时,更好地让他们查询好的和坏的数据库设计,然后他们将真正理解在教授设计时糟糕的设计是多么痛苦。
#3
E-commerce/shopping cart design is good because most people understand the concept and you can push it in many different directions.
电子商务/购物车设计很好,因为大多数人都了解这个概念,你可以将它推向许多不同的方向。
You can do simple things like cart, cart_items, users, orders, order_items, etc.
你可以做一些简单的事情,比如cart,cart_items,用户,订单,order_items等。
Then you can go deeper with user_addresses, user_emails, items, item_details, item_history, etc.
然后你可以更深入地了解user_addresses,user_emails,items,item_details,item_history等。
This can provide a lot of good debate because there are lots of judgment calls.
这可以提供很多好的辩论,因为有很多判断调用。
#4
One concept that I will never forget is the whole "plural" versus "singular" thing with naming. A great mentor I had way back once told me that you should design your table names as plural and your column names as singular and that you never create temporal-specific names for column names. Temporal Names examples are NutsSold1998, NutsSold1999, NutsSold2000, etc. Never add a year or month or week number or time, etc to a column name.
我永远不会忘记的一个概念是命名的整个“复数”与“奇异”的东西。我曾经回过头来的一位伟大的导师告诉我,你应该将你的表名设计为复数,你的列名称应该是单数,并且你永远不会为列名创建特定于时间的名称。时间名称示例包括NutsSold1998,NutsSold1999,NutsSold2000等。切勿在列名称中添加年份,月份或周数或时间等。
Table Name Examples: Employees (not Employee) Parts (not Part) Students (Student)
表名示例:员工(非员工)部分(非部分)学生(学生)
Column Name Examples: EmployeeID (not EmployeesID or EmployeeIDS, etc) PartID (not PartsID or PartIDS, etc) StudentID (not StudentsID or StudentIDS, etc)
列名示例:EmployeeID(不是EmployeesID或EmployeeIDS等)PartID(不是PartsID或PartIDS等)StudentID(不是StudentsID或StudentIDS等)
And to pay attention to the correct usage of ID, Code, Key, Number, etc... I was always taught to not use "Key" in the name of a Column unless it was an actual table Key (primary or foreign, but not necessarily alternate).. Most of the time appending "ID" will be a better choice than appending "Number" or "Code", but it all depends on the context.
并注意正确使用ID,代码,密钥,数字等...我总是被教导不要在列的名称中使用“密钥”,除非它是一个实际的表密钥(主要或外国,但但不一定是替代)..大多数时候附加“ID”将是一个比附加“数字”或“代码”更好的选择,但这一切都取决于上下文。
And that comes with time and experience in designing tables, reading good materials such as the book Database Design For Mere Mortals and Data Modeling for Everyone. Also, spending lots of time looking at good designs and taking them apart. Its definitely a craft and you only get better with time and practice.
这需要时间和经验来设计表格,阅读好的材料,例如“为每个人设计数据库设计”和“为每个人设计数据建模”。此外,花费大量时间看好设计并将它们分开。它绝对是一种工艺,你只会随着时间和练习而变得更好。
#5
Here's an example that dates back to my own University days - it was used both as a database design challenge and as an Object Oriented design challenge.
这是一个可以追溯到我自己的大学时代的例子 - 它既被用作数据库设计挑战又被用作面向对象的设计挑战。
Not all information was revealed at once - part of the challenge was to see how to adapt a design to handle new requirements, and how proper normalisation made this easier.
并非所有信息都是立即显示出来的 - 部分挑战是了解如何调整设计以应对新要求,以及正确的规范化如何使这更容易。
Assume you have to design a database for a university/college situation and want to handle enrollments.
假设您必须为大学/大学情况设计数据库并希望处理注册。
You have the Courses taught. Each course has a title and a regular timeslot each week.
你教授的课程。每个课程每周都有一个标题和一个常规时间段。
Each Course has a Lecturer who presents the course.
每门课程都有一名讲师,负责介绍课程。
Each Course has many Students who study the course.
每门课程都有很多学习该课程的学生。
Each Course has one or more Tutors who help students with their study. You don't need to track which tutors help which students.
每门课程都有一名或多名辅导员帮助学生完成学业。您无需跟踪哪些辅导员帮助哪些学生。
Some courses have multiple regular timeslots.
有些课程有多个常规时段。
Some courses have multiple Lecturers.
有些课程有多个讲师。
Lecturers and Tutors are paid, which means we need to track some information for tax purposes. The Tax department doesn't care what they were paid for - they expect us to have a single record per person.
讲师和导师是有偿的,这意味着我们需要跟踪一些信息用于税收目的。税务部门并不关心他们支付的费用 - 他们希望我们每人只能记录一次。
On some courses, the Lecturer works as a Tutor as well, to get a close up view of how some students are handling the material.
在一些课程中,讲师也是一名导师,以近距离了解一些学生如何处理这些材料。
Some Tutors are also Lecturers, on other courses.
一些导师也是其他课程的讲师。
To be a Tutor on a course, you have to have been a Student on that course at an earlier time.
要成为课程的导师,您必须在较早的时间成为该课程的学生。
Not every student will receive credit for passing the course - some are just auditing the course without a need for credit.
并非每个学生都能通过该课程获得学分 - 有些只是在不需要学分的情况下审核课程。
A student who fails a course may attend the course again later on. We need to keep records of every attempt.
未通过课程的学生可以稍后再次参加该课程。我们需要保留每次尝试的记录。
#6
There's always the library example as well (A library has many books, each book has an author and publisher which can be pushed into separate tables as you normalise)
总是有图书馆的例子(一个图书馆有很多书,每本书都有一个作者和出版商,可以在你标准化时推入单独的表)
#7
Re:
I'm especially trying to find some good examples for normalisation: tables that don't immediately fit 3NF and BCNF.
我特别想找到一些标准化的好例子:不能立即适合3NF和BCNF的表格。
You can find normalized database schema samples here: http://www.microsoft.com/sqlserver/2005/en/us/express-starter-schemas.aspx. You can build them from the ground up to show your students the "way of normalization". Particularly look at the Contact Management schema. You can easily denormalize the schema and bring it back on 3NF or deeper.
您可以在此处找到规范化的数据库架构示例:http://www.microsoft.com/sqlserver/2005/en/us/express-starter-schemas.aspx。您可以从头开始构建它们,向学生展示“规范化的方式”。特别要看一下Contact Management架构。您可以轻松地对模式进行非规范化,并将其恢复到3NF或更深层次。
#8
Itzik Ben Gan's new book, Microsoft SQL Server 2008: T-SQL Fundamentals, has a very basic example which you can see is derived from a simplified Northwind database.
Itzik Ben Gan的新书,Microsoft SQL Server 2008:T-SQL Fundamentals,有一个非常基本的例子,你可以看到它来自简化的Northwind数据库。
Microsoft SQL Server 2008: T-SQL Fundamentals author support page
Microsoft SQL Server 2008:T-SQL Fundamentals作者支持页面
#9
Another good model is the invoice-item model, because the "best choice" depends on various factors:
另一个好的模型是发票项目模型,因为“最佳选择”取决于各种因素:
- number of write vs read operations;
- performance;
- are reporting functionalies needed?
写入与读取操作的次数;
报告所需的功能?
Look at this data model:
看看这个数据模型:
Invoice
- ID
- date
InvoiceItem
- invoiceID
- description
- amount
Application funcitonalities are:
应用功能是:
- create new invoice;
- each evening create and send two reports: (a) total amount for each invoice, (b) total amount of the day.
创建新发票;
每晚创建并发送两份报告:(a)每张发票的总金额,(b)当天的总金额。
Say you have an average of 5 items per invoice and 100 invoices per day, you end up doing, for each day:
假设您每天平均有5件物品和每天100张发票,您最终会每天都这样做:
- 5 x 100 writes on InvoiceItem;
- 100 writes on Invoice;
- 5 x 100 + 100 = 600 reads from Invoice and InvoiceItem (report (a));
- 5 x 100 + 100 = 600 reads from Invoice and InvoiceItem (report (b));
在InvoiceItem上写入5 x 100;
在Invoice上写入100;
从Invoice和InvoiceItem中读取5 x 100 + 100 = 600(报告(a));
从Invoice和InvoiceItem中读取5 x 100 + 100 = 600(报告(b));
so total is = 1800 operations / day, assuming reads and writes have the same cost.
因此,假设读取和写入具有相同的成本,则总计= 1800次操作/天。
If you add "TotalAmount" attribute on entity "Invoice", situation is a bit different:
如果在实体“Invoice”上添加“TotalAmount”属性,情况会有所不同:
- 5 x 100 writes on InvoiceItem;
- 100 writes on Invoice (including TotalAmount);
- 100 from Invoice only (report (a));
- 100 from Invoice only (report (b));
在InvoiceItem上写入5 x 100;
在Invoice上写入100(包括TotalAmount);
仅来自发票100(报告(a));
仅来自发票100(报告(b));
with a total of 800 operations :)
共有800个操作:)