Question

sql 数据库课程设计 通讯录

就是要交给老师的 一个报告 word文档
在线等 急~~~

Answer 1

可以参考：
http://wenku.baidu.com/link?url=1WRORoWZDu2A9I5XEiVJAd1u9DBKLj84j1ulbpITF0c9jjDRQ_RhVCVc-MaVSSHw-CID-i6DOD-D2ZZj3jSwviRjFQgGoRLZ7MdDr6Cidj7

建立数据库模式、视图及索引
create databasea_book
go
use a_book
go

CREATE TABLE list
( list_name CHAR(8) PRIMARY KEY,
list_sex CHAR(4),
list_mobile_number CHAR(16),
list_birthday CHAR(8),
list_address CHAR(80)
)
go
CREATE TABLE family
( family_family_number CHAR(16) PRIMARY KEY,
family_family_address CHAR(80)
)
go
CREATE TABLE office
( office_office_number CHAR(16) PRIMARY KEY,
office_office_address CHAR(80),
office_e_mailCHAR(20)
)
go

CREATE TABLE ListFamily
(list_name CHAR(8),
family_family_number CHAR(16),
PRIMARY KEY (list_name,family_family_number),
FOREIGN KEY (list_name) REFERENCES list(list_name),
FOREIGN KEY (family_family_number) REFERENCES family(family_family_number)
)
go
CREATE TABLE ListOffice
(list_name CHAR(8),
office_office_number CHAR(16),
PRIMARY KEY (list_name,office_office_number),
FOREIGN KEY (list_name) REFERENCES list(list_name),
FOREIGN KEY (office_office_number) REFERENCES office(office_office_number)
)
视图
CREATE VIEW list_woman
AS
SELECT *
FROM list
WHERE list_sex='女'
go
CREATE VIEWlist_man
AS
SELECT *
FROM list
WHERE list_sex='男'
go
4.2 装载数据
insert intolist
values('李勇','男','13407389999','19891203','湖南娄底')
go
insert intolist
values('刘晨','女','15807387777','19900123','湖南娄底')
go
insert intolist
values('王敏','女','15207383333','19901101','湖南娄底')
go
insert intolist
values('张立','男','13807388888','19910706','湖南娄底')
go

insert intofamily
values('07382456777','湖南娄底氐星路号')
go
insert intofamily
values('07312456777','湖南长沙人民路号')
go
insert intofamily
values('07412456777','湖南怀化解放路号')
go

insert intooffice
values('07381111111','湖南娄底','234987600@qq.com')
go
insert intooffice
values('07382222222','湖南娄底','xiaophai@163.com')
go
insert intooffice
values('0103333333','北京东城区','qiqiling@yahoo.cn')
go
insert intooffice
values('07554444444','广东深圳','wo_xing@sina.com')
go

insert intoListFamily
values('李勇','07382456777')
go
insert intoListFamily
values('刘晨','07312456777')
go
insert intoListFamily
values('王敏','07382456777')
go
insert intoListFamily
values('张立','07412456777')
go

insert intoListOffice
values('李勇','07381111111')
go
insert intoListOffice
values('刘晨','07382222222')
go
insert intoListOffice
values('王敏','0103333333')
go
insert intoListOffice
values('张立','07554444444')
go

查询：
select * from list
select * from family
select * from office
select * from ListFamily
select * from ListOffice
select * from list_woman
select list_sex from list
select list_mobile_number from list
select list_birthday from list
select list_address from list

索引：
create index list_index on list (list_mobile_number )
create index office_index on office(office_office_number )

触发器：

create triger list_triger on list for delete
as
declear @list_mobile_number int
select @list_mobile_number from list
if @list_mobile_number = '110'
begin
rollback
raiserror （'不可删除',16,1）
end
过程：
create procedure check_number
@num char(11)
as
select list_mobile_number from list where list_mobile_number = @num
go