产品跟踪系统 数据库建设

create database Product_Track
go


use Product_Track
create table LoginUser(
 [ID]int identity(1,1)not null,
 [Username]varchar(10)not null,
 [Password]varchar(10)not null,
 [Authority]int not null,
 [State]int default 0 not null,
 primary key(ID)
);


use Product_Track 
go
insert into LoginUser (Username ,[Password] ,Authority ,[State] )values('admin','123',0,1);
insert into LoginUser (Username ,[Password] ,Authority ,[State] )values('service','234',0,0);
insert into LoginUser (Username ,[Password] ,Authority ,[State] )values('maintain','345',0,0);
insert into LoginUser (Username ,[Password] ,Authority ,[State] )values('market','456',0,0);




use Product_Track 
go
create table Product_Info(
[Product_id] int identity(1,1),
[product_name]varchar(20)not null,
[Product_price] money not null,
[Phy_spec]varchar(100),
[Elec_spec]varchar(100),
[Idling]bit not null,
primary key(Product_id)
);




use Product_Track 
go
create table Product_Lib(
[ID]int identity(1,1),
[Product_id]int not null,
[OperTime]datetime not null,
[Type]varchar(4)not null,
[Operator]varchar(10)not null,
[Ope_number]int not null,
primary key(ID),
foreign key(Product_id)references Product_Info(Product_id)
);


use Product_Track
go
create table Product_Sale(
[ID]int identity(1,1)not null,
[Product_id]int not null,
[Vend_id]int not null,
[Vendtor]varchar(10)not null,
[Sale_date]datetime not null,
[Sale_number]int not null,
[Sale_com]bit not null,
primary key(ID),
foreign key(Product_id)references Product_Info(Product_id),
foreign key(Vend_id)references Vendors_Info(Vend_id) 
);


use Product_Track
go
create table Area(
[Area_name]varchar(20)not null,
[Area_number]int identity(1,1)not null,
primary key(Area_number)
);


use Product_Track
go
create table Vendors_Info(
[Vend_id]int identity(1,1) not null,
[Vend_name]varchar(10)not null,
[Contact_name]varchar(10)not null,
[Email]varchar(30)not null,
[Tel]varchar(20)not null,
[Address]varchar(50)not null,
[Area_number]int not null,
primary key(Vend_id),
foreign key(Area_number)references Area(Area_number)
);


use Product_Track
go 
create table Accessories_Info(
[Access_id]int identity(1,1) not null,
[Access_name]varchar(20)not null,
[Access_function]varchar(50)not null,
primary key(Access_id)
);


use Product_Track
go
create table Product_Service(
[Service_id]int identity(1,1)not null,
[Product_id]int not null,
[Service_date]datetime not null default (getdate()),
[Service_man]varchar(10)not null,
[Fault]varchar(100)not null,
[CompentUse]varchar(100)not null,
[Service_progress]bit not null default ('true'),
primary key(Service_id),
foreign key(Product_id) references Product_Info(Product_id)
);


exec sp_help Product_Service
exec sp_helpconstraint Product_Service
alter table Product_Service drop constraint DF__Product_S__Servi__1CF15040


alter table Product_Service alter column Service_progress bit not null
--查询相关产品的所有维修记录(售后),表Product_Info和表Product_Service关联
--查询显示产品名称product_name,Service_date,Service_man,Fault,CompentUse,Service_progress
select p.product_name,s.Service_date,s.Service_man,s.Fault,s.CompentUse,s.Service_progress
from Product_Info as p inner join Product_Service as s
on p.Product_id=s.Product_id


create proc proc_Product_Service


as
select p.product_name,s.Service_date,s.Service_man,s.Fault,s.CompentUse,s.Service_progress
from Product_Info as p inner join Product_Service as s
on p.Product_id=s.Product_id


exec proc_Product_Service 


exec sp_help Vendors_Info
exec sp_help Product_Sale
exec sp_helpconstraint Product_Sale


--固定销售商的所有产品销售记录(销售),表Product_Sale和表Vendors_Info和表Product_Info
--查询显示销售商的编号(Vend_id),销售商姓名(Vend_name),销售商品编号(Product_id),销售商品名称(product_name),销售员(Vendtor),销售日期(Sale_date),销售数量(Sale_number),销售是否完成(Sale_com),
select v.Vend_id as 销售商编号,v.Vend_name as 销售商姓名,p.Product_id as 销售商品编号,p.product_name as 销售商品名称,s.Vendtor as 销售员,
s.Sale_date as 销售日期,s.Sale_number as 销售数量,s.Sale_com as 销售是否完成
from Product_Sale as s 
inner join Vendors_Info as v on(s.Vend_id=v.Vend_id)
inner join Product_Info as p on(p.Product_id=s.Product_id)


create proc proc_Vendor_Sale
as
select v.Vend_id as 销售商编号,v.Vend_name as 销售商姓名,p.Product_id as 销售商品编号,p.product_name as 销售商品名称,s.Vendtor as 销售员,
s.Sale_date as 销售日期,s.Sale_number as 销售数量,s.Sale_com as 销售是否完成
from Product_Sale as s 
inner join Vendors_Info as v on(s.Vend_id=v.Vend_id)
inner join Product_Info as p on(p.Product_id=s.Product_id)


exec proc_Vendor_Sale


--查询指定区域内的产品的销售记录(销售),表Area和表Product_Sale和表Vendors_Info,Product_Info
--查询显示区域名称(Area_name),销售商品编号(Product_id),商品名称(product_name),销售员(Vendtor),销售日期(Sale_date),销售数量(Sale_number),销售是否完成(Sale_com)


select a.Area_name,p.Product_id as 销售商品编号,p.product_name as 销售商品名称,s.Vendtor as 销售员,
s.Sale_date as 销售日期,s.Sale_number as 销售数量,s.Sale_com as 销售是否完成
((from Area as a 
inner join Vendors_Info as v on(a.Area_number=v.Area_number) b)
inner join Product_Sale as s on(b.Vend_id=s.Vend_id) c)
inner join Product_Info as p on(c.Product_id=p.Product_id)


select Area.Area_name,Product_Info.Product_id,Product_Info.product_name ,Product_Sale.Vendtor ,Product_Sale.Sale_date,Product_Sale.Sale_number,Product_Sale.Sale_com 
from Area , Vendors_Info,Product_Sale,Product_Info
where Area.Area_number=Vendors_Info.Area_number
  and b.Vend_id=s.Vend_id
  and c.Product_id=p.Product_id


exec sp_help Product_Service
exec sp_help Vendors_Info
--查询固定销售商的所有产品维修记录(销售),表Vendors_Info和表Area和表
--区域名称(Area_name),销售商姓名(Vend_name),维修产品编号(Product_id)
--1\商品名字，维修记录(Service_date,Service_man,Fault,CompentUse,Service_progress)
select p.Product_id, p.product_name,s.Service_date,s.Service_man,s.Fault,s.CompentUse,s.Service_progress
from Product_Info as p inner join Product_Service as s on(p.Product_id=s.Product_id)


select v.Vend_name,p.Product_id,s.Service_date,s.Service_man,s.Fault,s.CompentUse,s.Service_progress
from Product_Service as s inner join Vendors_Info as v on(s.Vend_id=v.Vend_id)
inner join Product_Info as p on(s.Product_id=p.Product_id)


http://www.ld0769.com/shantou/yangsbj/f314556.html
http://www.ld0769.com/beijing/baojie/f314560.html
http://www.siyangtv.com/thread-115571-1-1.html
http://www.siyangtv.com/thread-115572-1-1.html
http://www.siyangtv.com/thread-115573-1-1.html
http://bbs.uu.cc/thread-2530657-1-1.html
http://bbs.uu.cc/thread-2530658-1-1.html
http://bbs.uu.cc/thread-2530659-1-1.html
http://nj.58.com/zhongcanyin/20082198695685x.shtml
http://bbs.tianya.cn/post-410-23286-1.shtml
http://blog.tianya.cn/post-5250279-71714238-1.shtml
http://blog.tianya.cn/post-5250279-71713999-1.shtml
http://blog.sina.com.cn/s/blog_bb3715400102v5mw.html
http://bbs.zoomla.cn/showtopic-369231.aspx