大学数据库

Question

我正在为一所假设的大学建立一个数据库。这是我的SQL定义，并添加了生成的数据供我将来使用，例如演示查询：

Create Table Semesters (
    Semester varchar(6) Primary Key Not Null,
);

Insert Into UniversityDatabase.dbo.Semesters
Values ('Spring'),
       ('Summer'),
       ('Fall');

Create Table Grades (
    Grade_Letter char(1) Primary Key,
);

Insert Into UniversityDatabase.dbo.Grades
Values ('A'),
       ('B'),
       ('C'),
       ('D'),
       ('F'),
       ('S'),
       ('N');

Create Table LectureSeries (
    Lecture_Series char(3) Primary Key Not Null,
);

Insert Into UniversityDatabase.dbo.LectureSeries
Values ('001'),
       ('002'),
       ('003'),
       ('E90'),
       ('E91'),
       ('E92');

Create Table Departments (
    Department_Code varchar(4) Primary Key,
    Department_Name varchar(30) Unique Not Null,
);

Insert Into UniversityDatabase.dbo.Departments
Values ('ECON', 'Economics'),
       ('PHYS', 'Physics'),
       ('FIN', 'Finance'),
       ('LEGL', 'Legal'),
       ('CHEM', 'Chemistry'),
       ('CS', 'Computer Science'),
       ('PSY', 'Psychology'),
       ('PHIL', 'Philosophy');

Create Table [Degrees] (
    Degree_ID int Identity(10000,1) Primary Key,
    Department varchar(4) Foreign Key References Departments(Department_Code) Not Null,
    [Description] varchar(200) Not Null,
);

Insert Into UniversityDatabase.dbo.[Degrees]
Values ('ECON', 'Economics BS'),
       ('PHYS', 'Physics BS'),
       ('FIN', 'Business Finance BA'),
       ('LEGL', 'Constitutional Law BA'),
       ('CHEM', 'Chemistry BS'),
       ('CS', 'Computer Engineering BS'),
       ('PSY', 'Psychology BS'),
       ('PHIL', 'Philosophy BA');

Create Table Instructors (
    Instructor_ID int Identity(10000,1) Primary Key,
    FName nvarchar(200) Not Null,
    LName nvarchar(200) Not Null,
    Year_Hired int Not Null,
);

Insert Into UniversityDatabase.dbo.Instructors
Values ('Bill', 'Smith', 1995),
       ('James', 'Peterson', 1997),
       ('Adam', 'Thompson', 1998),
       ('Janetta', 'Oakley', 2001),
       ('Robin', 'Dexter', 2003),
       ('Annie', 'Jackson', 2004),
       ('Philip', 'Petrovsky', 2006),
       ('Anastasia', 'Scott', 2013);

Create Table Students (
    Student_ID int Identity(10000,1) Primary Key,
    FName nvarchar(200) Not Null,
    LName nvarchar(200) Not Null,
    Degree int Foreign Key References [Degrees](Degree_ID) Not Null,
    Current_GPA real,
);

Insert Into UniversityDatabase.dbo.Students
Values ('Jack', 'Johnson', 10005, null),
       ('Dolly', 'Denver', 10000, 2.89),
       ('Helmut', 'Ziegler', 10003, 3.04),
       ('Robert', 'Thompson', 10004, 3.83),
       ('Jeffrey', 'Petersen', 10006, null),
       ('Jack', 'Pirate', 10002, null),
       ('Barb', 'Wire', 10001, null),
       ('Roberta', 'Strong', 10004, null),
       ('Heather', 'Black', 10002, 3.94),
       ('Erik', 'Bjornsen', 10002, 3.57);

Create Table ClassLibrary (
    Department varchar(4) Foreign Key References Departments(Department_Code),
    Class_Number int Not Null,
    Class_Name varchar(200) Not Null,
    Available_Online bit Not Null,
    Credits int Not Null,
    Primary Key (Department, Class_Number),
);

Insert Into UniversityDatabase.dbo.ClassLibrary
Values ('ECON', '1015', 'Microeconomics', 1, 3),
       ('ECON', '1016', 'Macroeconomics', 1, 3),
       ('PHYS', '4364', 'Quantum Mechanics', 0, 4),
       ('CHEM', '1001', 'Introduction to Chemistry', 1, 4),
       ('CS', '3060', 'Algorithms I', 1, 3),
       ('CS', '3061', 'Algorithms II', 1, 3),
       ('PSY', '1009', 'Introduction to Psychology', 1, 3),
       ('PHIL', '1001', 'Introduction to Philosophy', 1, 3);

-- This table says which classes are offered in which semesters
Create Table ClassesSemester (
    Class_ID int Identity(10000,1) Primary Key,
    Department varchar(4),
    Class_Number int,
    Lecture_Series char(3) Foreign Key References LectureSeries(Lecture_Series) Not Null,
    [Year] int Not Null,
    Semester varchar(6) Foreign Key References Semesters(Semester) Not Null,
    Instructor int Foreign Key References Instructors(Instructor_ID) Not Null,
    Seats_Available int Not Null,
    Foreign Key (Department, Class_Number) References ClassLibrary,
    Unique ([Year], Semester, Department, Class_Number, Lecture_Series),
);

Insert Into UniversityDatabase.dbo.ClassesSemester
Values ('ECON', 1015, 'E90', 2015, 'Fall', 10001, 30),
       ('ECON', 1015, '001', 2015, 'Fall', 10001, 30),
       ('ECON', 1016, 'E90', 2015, 'Fall', 10001, 30),
       ('ECON', 1016, 'E91', 2015, 'Fall', 10001, 30),
       ('PHIL', 1001, 'E90', 2015, 'Fall', 10006, 40),
       ('PHIL', 1001, '001', 2015, 'Fall', 10006, 40),
       ('CS', 3060, 'E90', 2015, 'Fall', 10000, 30),
       ('CS', 3061, 'E90', 2015, 'Fall', 10000, 30),
       ('CHEM', 1001, '001', 2015, 'Fall', 10002, 25),
       ('ECON', 1015, '001', 2016, 'Spring', 10001, 30),
       ('ECON', 1016, 'E90', 2016, 'Spring', 10001, 30),
       ('ECON', 1016, 'E91', 2016, 'Spring', 10001, 30),
       ('PHIL', 1001, 'E90', 2016, 'Spring', 10006, 40),
       ('PHIL', 1001, '001', 2016, 'Spring', 10006, 40),
       ('CHEM', 1001, '001', 2016, 'Spring', 10002, 25);

-- This table tells us which classes students have taken/are taking
Create Table StudentClasses (
    Student_ID int Foreign Key References Students(Student_ID),
    Class_ID int Foreign Key References ClassesSemester(Class_ID),
    Grade char(1) Foreign Key References Grades(Grade_Letter),
    Primary Key (Student_ID, Class_ID),
);

Insert Into UniversityDatabase.dbo.StudentClasses
Values (10000, 10000, 'B'),
       (10000, 10001, 'B'),
       (10001, 10000, 'C'),
       (10002, 10000, null),
       (10004, 10000, null),
       (10008, 10006, 'A'),
       (10008, 10007, null);

-- This table tells us which instructors can teach which class
Create Table InstructorClasses (
    Instructor_ID int Foreign Key References Instructors(Instructor_ID),
    Department varchar(4),
    Class_Number int,
    Foreign Key (Department, Class_Number) References ClassLibrary,
);

Insert Into UniversityDatabase.dbo.InstructorClasses
Values (10000, 'CS', 3060),
       (10000, 'CS', 3061),
       (10001, 'ECON', 1015),
       (10001, 'ECON', 1016),
       (10002, 'CHEM', 1001),
       (10006, 'PHIL', 1001),
       (10005, 'CS', 3060),
       (10005, 'CS', 3061),
       (10006, 'PHIL', 1001);

请随意评论其中的任何和全部。我最感兴趣的是对我的设计的评价--我的关系建立得正确吗？我的桌子有太多的责任吗？

Answer 1

小问题

完全引用表

在您的脚本中存在一致的不一致性。

创建表格学期(-.)；插入到UniversityDatabase.dbo.Semesters值中-.

一方面，您正在创建没有任何引用的表，而另一个则是在插入时完全引用它。也许一个更好的方法是声明想要在顶部使用的数据库目录，然后只引用模式和表。Server将始终使用该数据库目录，直到它遇到另一个具有不同数据库的USE语句为止。

Use UniversityDatabase;
GO
Create Table dbo.Semesters (
    --...
);

Insert Into dbo.Semesters
Values --...

这很小，但是如果您有多个模式，这将有助于提高干燥和可能的模糊性，这并不少见。唯一需要完全限定引用的时间是在跨数据库目录(相当常见)或跨链接服务器实例(更少见，通常有更好的解决方案)时。

隐式Inserts

您使用的是隐式Inserts，而没有引用列，这不仅更容易出错，因为它依赖于列if及其所处的顺序，如果列被添加、删除等(在实际环境中可以也确实发生)，这种情况可能会中断。

它还使代码更难阅读，因为您需要返回表定义，以确定数据被插入到哪些列中。所以，不是这样做的：

插入到dbo.Students值('Jack'，'Johnson'，10005，null)，('Dolly'，‘丹佛’，10000，2.89)，-

最好这样做：

Insert Into dbo.Students
    (FName, LName, Degree, Current_GPA)
Values 
    ('Jack', 'Johnson', 10005, null),
    ('Dolly', 'Denver', 10000, 2.89),
    --...

当您开始使用更大的脚本(如存储过程等)时，这一点将变得特别重要，这些脚本可以在数十行中插入数据，而不一定是在一次操作中全部插入数据。

FName，LName

我认为你的命名总体上是相当好的。但我会将这些修改为First_Name和Last_Name，以保持一致性，并使其读起来更容易。当您发送数据集(报表等)时，这将为您的日常查询节省一些烦人的别名。对于非IT人员来说，他们可能认为FName和LName对用户不太友好，或者你总是不得不这样做：

Select 
  FName As First_Name, 
  LName As Last_Name
From dbo.Students
Order By LName Asc, FName Asc;

大问题

自动提交模式

您目前正在自动提交模式下运行脚本，这意味着您将由SQL引擎代表您决定何时开始和提交事务。虽然它通常会做得很好，但是想象一下，如果您在脚本的中间出现了一个错误(例如键违规或数据类型错误)，那么很可能它已经提交了该部分之前的操作。在这样一个小脚本中，在“处女地”数据库中，这可能不是什么大事，只需删除数据库并重新开始。但是在生产环境中，许多表都有触发器，一旦提交了插入事务，就会触发这些触发器，这样做会非常令人沮丧，而且需要花费大量的时间来撤消。

因此，理想情况下，至少将整个脚本包装到一个事务中。更多细节马上就到。

Use UniversityDatabase;
GO
Begin Transaction;
  --do all the work you need
Commit Transaction;

无错误处理

理想情况下，我们希望根据事务过程中是否存在错误/问题，以不同的方式处理事务。好消息是SQL支持TRY/CATCH操作..。

Begin Transaction;
Begin Try
    Insert Into Foo (Bar) Values ('Hello'), ('World');
    --whatever other work you need done
    Commit Transaction;
End Try
Begin Catch
    Rollback Transaction;
    --Get information about the error:
    Select
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;
End Catch

这样，它不仅会在出问题时回滚事务，而且还会给出为什么失败的所有细节。在更大的脚本中，您也可以使用RAISERROR() (不是一个错误，只有一个"E")来定义您自己的错误处理，就像在传统编程中一样。例如：

Begin Try
  If (1=0)
  Begin
    Declare 
        @ErrMessage Varchar(200) = 'Something went wrong',
        @ErrSeverity Int = 16, --Severity from 11 to 20 redirects to Catch block
        @ErrState Int = 1;     --Multiple states from 0-255 can be used in different areas of the script, to help identify errors in specific sections
    Raiserror(
      @ErrMessage, 
      @ErrSeverity, 
      @ErrState
    );
  End
End Try
Begin Catch
  --Goes here after Raiserror is encountered in If statement, with severity 11-20
End Catch

您在生产环境中经常看到的另一件事是，将有一个存储过程(或针对不同用例的多个存储过程)，这些存储过程具有一些内置的错误代码，例如来自技术网来自技术网：

-创建一个检索错误信息的过程。将过程usp_GetErrorInfo创建为选择ERROR_NUMBER()为ErrorNumber，ERROR_SEVERITY()为ErrorSeverity，ERROR_STATE()为ErrorState，ERROR_PROCEDURE()为ErrorProcedure，ERROR_LINE()为ErrorLine，ERROR_MESSAGE()为ErrorMessage；

然后，每次需要它时，只需在catch块中执行它：

开始尝试--生成零除法错误。选择1/0；结束尝试开始捕获-执行错误检索例程。执行usp_GetErrorInfo；

套管上的镐

最后一个小菜一碟，虽然您的SQL关键字大小写是一致的，但是很少会看到Select Foo From Bar Order By Baz Asc，并且有几个原因。除了它看起来像VB之外，如果大小写是一样的话，它主要会让你看关键字和符号更累人一些。SQL最常见的大小写是SHOUT CASE，其次是lower case关键字。

Answer 2

作为在一所大型大学工作的人，我想说这个模式非常简单。根据我的经验，任何关于业务规则的假设都可能是错误的。下面是一些例子：

• 一个暑期学期？不，让我们有多个夏季会议，一些与其他重叠！
• 每门课一节课？不，让我们交叉列出一些课程(比如一些航班是如何在航空公司之间共享代码的)！
• 每个学生一个学位？这就排除了双学位和研究生学位。
• 每门课程都是由部门代码识别的？电气和计算机工程系要求将其部分课程列为EE 123，其他课程为CE 123！当他们修改课程时，他们想重新编号课程！
• 课程编号就是数字？我们需要像ECON 99A这样的后缀！
• 教官只被雇一次？不，有些人来来去去。
• 课程可以有副导师。

不管怎么说，我知道你这么做只是为了好玩，所以别再咆哮了。我只想指出几个想法，而不是把你的工作范围扩大到一个价值数百万美元的项目中。

• Current_GPA作为Students的属性。这使得数据库更像是一个报表数据库，而不是一个记录系统。我希望GPA能从成绩记录中衍生出来。无论如何，您应该让Student表纯粹是关于身份的；您的GPA无助于定义您是谁。
• Semesters将受益于使用ORDER BY的排序属性。Grades可能会使用第二列来表示GPA等价物。
• Student_ID和Instructor_ID号码是否打算用作公共标识符？从人工因素的角度来看，将它们放在同一个名称空间中可能会让人感到困惑。您可以将学生编号设计为比指导员数字更多的数字。或者，统一名称空间，并向每个人颁发一个大学ID，而不管其隶属关系如何。