数据库的高级设计

一.标准化

数据库的设计中，最小化数据冗余和数据库的效率之间存在一个折衷。

1.第一范式：

定义所需要的数据项，将相关的数据项放置在一个表中。
确保没有重复的数据组。（可以通过将数据划分到多个表中，来删除重复的数据）
确保存在一个主键。

主键：记录的唯一标示符。可以添加一个新列，或者使用现有的一个或者多个列，只要这些列能够组合成一个唯一的主键。

2.第二范式：要求主键中的任意列没有局部相关性。

3.第三范式（可选项，依赖于环境）：符合第二范式，所有非主键字段都依赖主键。

传递相关性。消除传递相关性——把具有传递相关性的数据项单独放在一个表中。

好处是：数据重复量降低；数据完整性，避免重复数据改变时，只更新了某些数据的危险。

不利因素：增加了复杂性并且降低了效率。

二.利用约束确保数据的有效性

1.NOT NULL约束

Create table mytable(Column1 int not null, Column2 varchar(20));
alter table mytable
alter column column2 varchar(20) not null ;   --将一列更改为not null类型。这个更改能够成功的前提是，原数据表中没有null型的记录。对于其他的约束修改也是一样的，不能够和表中的已有数据冲突。

2.UNIQUE约束

 --一种添加unique约束的方式
Create table mytable(Column1 int not null unique, Column2 varchar(20) unique);  

--另一种添加unique约束的方式，在定义的列列举之后添加约束，可以为约束指定一个名称，并且可以使用SQL语句删除该约束.
--可以指定两个或者多个列的组合必须为唯一
Create table anothertable(
  Column1 int,
  Column2 varchar(20),
  Column3 varchar(12),
  CONSTRAINT MyUniqueConstraint UNIQUE(Column1,Column2),
  CONSTRAINT AnotherUniqueConstraint UNIQUE(Column1,Column3)  
  )  ;
Alter table anothertable
drop constraint MyUniqueConstraint;
Alter table anothertable
add CONSTRAINT MyUniqueConstraint UNIQUE(Column1,Column2);

3.CHECK约束

CREATE TABLE NamesAges(
   Name varchar(50),
   Age int CHECK(Age>=0)    --Age列的条件必须为true或者unknown
);

INSERT INTO NamesAges(Name,Age) Values('JIM',30);    --成功
INSERT INTO NamesAges(Name) Values('JIM');     --成功
INSERT INTO NamesAges(Name,Age) Values('JIM',-22);  --失败

delete from NamesAges where Age is null;  
Alter table NamesAges
alter column Age int not null;   

alter table NamesAges
add constraint Age check(age<=40);  --另一种方法添加check约束

CREATE TABLE Employee
(
      EmployeeName varchar(50),
      AvgMonthlyWage decimal(12,2),
      HourlyRate decimal(12,2),
      CONSTRAINT hourlyLess CHECK(AvgMonthlyWage>HourlyRate),  --如果希望CHECK条件子句包含表中的多个列，则需要在列末尾定义它
)

--或者使用alter table和add constraint
alter table Employee
add constraint hourlyLess CHECK(AvgMonthlyWage>HourlyRate);

4.主键和PRIMARY KEY约束

--主键提供了表之间的链接
--主键约束是UNIQUE和NOT NULL的组合
create table HolidayBookings
(
  CustomerId int primary key,  
  BookingId int NOT NULL,
  Destination varchar(50),
)

--组合主键
create table MoreHolidayBookings
(
  CustomerId int NOT NULL,  
  BookingId int NOT NULL,
  Destination varchar(50),
  CONSTRAINT booking_pk PRIMARY KEY(CustomerId,BookingId)  --同一个人不能同时借两本相同的数。一个人可以借多本书，相同的书号的数也可以被多人借。主键约束，不允许CustomerId,BookingId中的任何一个为null
);

--为现有表添加主键约束，前提条件是被设为主键的列已经被定义成了not null型
create table MoreHolidayBookings
(
  CustomerId int NOT NULL,  
  BookingId int NOT NULL,
  Destination varchar(50),
);
alter table MoreHolidayBookings
add constraint more_holiday_pk primary kay(CustomerId,BookingId)

5.外键约束

外键是访问另一个表的主键的列。

假设有2个表Location和Attenedance，在利用内部联接来查找数据时，查找Attenedance.LocationId=Location.LocationId的数据。当往Attenedance中插入了一个记录，其Attenedance.LocationId值在Location中无效的时候，破坏了这种联接性。

外键约束就可以保证这种联接性，即一个表中的一个列是引用另一个表中的记录行的方法。

alter table attendance add constraint location_fk foreign key(LocationId) references Location(LocationId);   --由一列组成的主键和外键
--当创建了外键约束后，如果往attendance中添加的记录，其LocationId在它所依赖的主键中无效，那么添加会出错

alter table SomeTable ADD CONSTRAINT sometable_fkl foreign key (employeename,employeeid,membershipid) references someprimarykeytable(employeename,employeeid,membershipid);  --主键和外键由3列组成

--也可以在创建一个表的时候就添加主键
create table attendence
(
     LocationId integer,
     MeetingDate date,
     MemberAttended char(1),
     MemberId integer,
     CONSTRAINT SomeTable_fk1 FOREIGN KEY (LocationId) REFERENCES Location(LocationId)
);

三.利用索引加速结果查询

CREATE INDEX index_name ON table_name (col1,col2);  --创建索引
select col1,col2 from table_name;    --添加了索引后，输出结果按升序排列。默认情况下，索引按升序排列。
DROP INDEX table_name.index_name;    --删除索引
select col1,col2 from table_name;     --删除索引后，输出结果不再按照顺序排列
create unique index index_name on table_name(col1 desc,col2);   --创建一个唯一的索引，按col1的降序排列然后按col2的升序排列