根据以下业务要求要求完成数据库应用系统开发
面向工业领域中商品的进货、销售、库存和记账等业务开发一个数据库应用系统:某客户、供应商以及他们之间往来的商品如汽车、电器等,需有效地记录这些商品进货、库存等账目,并登记客户和供应商的往来款项以便及时收/款。(注意往来款项不需要即时结清。)
以下为系统开发的主要内容(100分)
1.(5分)描述该应用系统的业务逻辑(不超过100字)。
2.(10分)设计满足该应用系统业务需求的E-R图;
3.(20分)把E-R图转换成关系模型,说明主键和外键;满足1NF/2NF/3NF。
4.(5分)用T-SQL创建数据库,至少包括一个主数据文件和两个次数据文件,并放置在个不同文件组中。
5.(20分)用T-SQL创建与ER图相符的表,表放在不同文件夹下;建立表之间联系,实现主外键约束;至少建立一个规则并绑定;至少建立一个表级约束并注明功能。
6.(5分)用T-SQL在销售记录表上建立索引
7.(10分)用T-SQL完成向销售记录表或进货记录表中插入数据(注意满足主外键约束,记录不少于10条。
8.(10分)用T-SQL查询某客户在一年内的购买商品记录以及付款记录;并创建为视图。
9.(10分)用T-SQL在销售记录表或进货记录表上创建触发器完成相关表的数据更新。
10.(5分)用T-SQL完成销售人员角色的往来账目查询授权。
1. 新建查询创建数据库
1.1 创建文件夹
1.2 新建查询
输入以下sql然后执行
IF EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME = 'manger')
DROP DATABASE manger
CREATE DATABASE manger --创建数据库
ON PRIMARY -- 默认文件组 主数据文件
(
NAME='manger_data', -- 逻辑文件名
FILENAME='F:\sqlServer\parmary\manger_data.mdf', -- 物理文件名
SIZE=5MB, -- 初始大小5MB
MAXSIZE=15MB, -- 最大15MB
FILEGROWTH=20% -- 每次增加20%
),
FILEGROUP group1 -- 文件组1 次数据文件1
(
NAME='manger_group1',
FILENAME='F:\sqlServer\group1\manger_group1.ndf',
SIZE=1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB
),
FILEGROUP group2 -- 文件组2 次数据文件2
(
NAME='manger_group2',
FILENAME='F:\sqlServer\group2\manger_group2.ndf',
SIZE=1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB
)
LOG ON
( --日志
NAME='manger_log',
FILENAME='F:\sqlServer\log\manger_log.ldf',
SIZE=5MB,
MAXSIZE=10MB,
FILEGROWTH=1MB
)
此时数据库manger就创建好了
2.创建表
同样的，新建查询执行下面的sql
use manger;
go
---商品表
if exists(select * from sysobjects where name = 'commodity')
drop table commodity
create Table commodity
(
commodity_id int not null identity, --商品编号 非空约束，自增约束
commodity_name varchar(50) not null, --商品名称 非空约束
commodity_price decimal(20,2) not null, --商品价格 非空约束
commodity_detail varchar(500), --商品详情
commodity_launch_date datetime not null default(getdate()), --生产日期 默认得到系统时间 非空约束，默认值约束
commodity_pic_address varchar(100), --生产地址
constraint PK_commodity_id primary key (commodity_id) --表级约束：主键
);
---客户表
if exists(select * from sysobjects where name = 'customer')
drop table customer
create Table customer
(
customer_id int not null identity, --客户编号 非空约束，自增约束
customer_name varchar(50) not null, --客户名称 非空约束
customer_address varchar(100), --客户地址
customer_contact varchar(50) not null, --联系方式 非空约束
constraint PK_customer_id primary key (customer_id) --表级约束：主键
);
---供应商表
if exists(select * from sysobjects where name = 'supplier')
drop table supplier
create Table supplier
(
supplier_id int not null identity, --供应商编号 非空约束，自增约束
supplier_name varchar(50) not null, --供应商名称 非空约束
supplier_address varchar(100), --供应商地址
supplier_contact varchar(50) not null, --联系方式 非空约束
constraint PK_supplier_id primary key (supplier_id) --表级约束：主键
);
---库存表
if exists(select * from sysobjects where name = 'stock')
drop table stock;
create Table stock
(
stock_id int not null identity, --库存编号 非空约束，自增约束
stock_number int not null default(0), --库存数量 非空约束，默认值约束
commodity_id int not null, --商品编号 非空约束
customer_id int default(null), --客户编号 非空约束，默认值约束
supplier_id int default(null), --供应商编号 非空约束，默认值约束
constraint PK_stock_id primary key (stock_id), -- 表级约束：主键
constraint FK_commodity_stock foreign key (customer_id) references commodity(commodity_id), -- 表级约束：外键
constraint FK_customer_stock foreign key (customer_id) references customer(customer_id), -- 表级约束：外键
constraint FK_supplier_stock foreign key (supplier_id) references supplier(supplier_id) -- 表级约束：外键
);
---付款记录表
if exists(select * from sysobjects where name = 'payment')
drop table payment
create Table payment
(
payment_id int not null identity, --付款编号 非空约束，自增约束
is_pay bit not null default(0), --是否付款 非空约束，默认值约束
payment_date datetime default(getdate()), --付款日期 默认值约束
customer_id int not null, --客户编号 非空约束
supplier_id int not null, --供应商编号 非空约束，
constraint PK_payment_id primary key (payment_id), -- 表级约束：主键
constraint FK_customer_payment foreign key (customer_id) references customer(customer_id), -- 表级约束：外键
constraint FK_supplier_payment foreign key (supplier_id) references supplier(supplier_id) -- 表级约束：外键
);
-----------------------------------------------------------------------------规则rule
---删除规则
drop rule payment_pay_rule;
-- 创建规则
go
-- 支付状态的值只有0和1两种
create rule payment_pay_rule
as @value in (0,1);
go
-- 查看规则
--EXEC sp_helptext 'payment_pay_rule'
---将规则payment_pay_rule绑定到payment的is_pay列上，支付状态的值只有0，1两种
exec sp_bindrule payment_pay_rule, 'payment.is_pay';
---将规则解除绑定
--exec sp_unbindrule 'payment.is_pay'
------------------------------------------------------------------------------规则rule
---进货记录表
if exists(select * from sysobjects where name = 'purchase')
drop table purchase
create Table purchase
(
purchase_id int not null identity, --进货编号 非空约束，自增约束
purchase_date datetime not null default(getdate()), --进货日期 非空约束，默认值约束
purchase_number int not null, --进货数量 非空约束
purchase_final_price decimal(20,2) not null, --成交价 非空约束
commodity_id int not null, --商品编号 非空约束
customer_id int default(null), --客户编号 非空约束，默认值约束
supplier_id int default(null), --供应商编号 非空约束，默认值约束
constraint PK_purchase_id primary key (purchase_id), -- 表级约束：主键
constraint FK_commodity_purchase foreign key (customer_id) references commodity(commodity_id), -- 表级约束：外键
constraint FK_customer_purchase foreign key (customer_id) references customer(customer_id), -- 表级约束：外键
constraint FK_supplier_purchase foreign key (supplier_id) references supplier(supplier_id) -- 表级约束：外键
);
---销售记录表
if exists(select * from sysobjects where name = 'sales')
drop table sales
create Table sales
(
sales_id int not null identity, --销售编号 非空约束，自增约束
sales_date datetime not null default(getdate()), --销售日期 非空约束，默认值约束
sales_number int not null, --销售数量 非空约束
sales_final_price decimal(20,2) not null, --成交价 非空约束
commodity_id int not null, --商品编号 非空约束
customer_id int default(null), --客户编号 非空约束，默认值约束
supplier_id int default(null), --供应商编号 非空约束，默认值约束
constraint PK_sales_id primary key (sales_id), -- 表级约束：主键
constraint FK_commodity_sales foreign key (customer_id) references commodity(commodity_id), -- 表级约束：外键
constraint FK_customer_sales foreign key (customer_id) references customer(customer_id), -- 表级约束：外键
constraint FK_supplier_sales foreign key (supplier_id) references supplier(supplier_id) -- 表级约束：外键
);
----------------------------------------------------------------------索引index
-- 以sales表的sales_id与sales_date来建立索引
create index Index_sales_id_date
on sales(sales_id,sales_date)
--with (drop_existing=on) --在创建时如果已有索引先删除
----------------------------------------------------------------------索引index
此时刷新以下数据库的表，就能看见创建的表了
3. 创建触发器
新建查询执行：
use manger;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
go
create trigger trigger_sales_purchase --首次创建CREATE，后续修改ALTER：更新、新增触发器
on sales --在sales表中创建触发器
after insert,update --更新、新增事件触发
AS --执行下面的SQL语句
DECLARE @PerID AS INT --定义变量
SELECT @PerID=sales_id FROM inserted --变量赋值：临时表inserted中存放的新id
IF EXISTS(SELECT purchase_id FROM purchase WHERE purchase_id=@PerID) --如果记录已存在，执行UPDATE操作
BEGIN
print(@PerID);
UPDATE purchase SET --将临时表inserted中的数据更新到purchase表中
purchase_date=sales_date,
purchase_number=sales_number,
purchase_final_price=sales_final_price,
purchase.commodity_id=inserted.commodity_id,
purchase.customer_id=inserted.customer_id,
purchase.supplier_id=inserted.supplier_id
FROM inserted WHERE purchase_id=@PerID
END
ELSE IF @PerID IS NOT NULL --如果记录不存在且id不为空，执行INSERT操作
BEGIN
print(@PerID);
SET IDENTITY_INSERT purchase ON --允许主键插入显式值
INSERT INTO purchase( --将临时表inserted中的数据插入purchase表中
purchase_id,purchase_date,purchase_number,purchase_final_price,commodity_id,customer_id,supplier_id
)SELECT
sales_id,sales_date,sales_number,sales_final_price,commodity_id,customer_id,supplier_id
FROM inserted
SET IDENTITY_INSERT purchase OFF --禁止主键插入显式值
END
这个触发器的作用是将sales表与purchase表绑定，当sales发生插入或者更新数据时，会触发触发器而使得purchase同步更新
允许后，刷新即可看到
4. 插入数据
执行下面的sql
use manger;
go
-- 商品
insert into commodity(commodity_name,commodity_price,commodity_detail,commodity_launch_date,commodity_pic_address) values('自行车01',1200.00,'山地自行车，经久耐骑',GETDATE(),'中国·上海')
insert into commodity(commodity_name,commodity_price,commodity_detail,commodity_launch_date,commodity_pic_address) values('自行车02',2400.00,'山地自行车，经久耐骑',GETDATE(),'中国·上海')
insert into commodity(commodity_name,commodity_price,commodity_detail,commodity_launch_date,commodity_pic_address) values('自行车03',899.00,'山地自行车，经久耐骑',GETDATE(),'中国·上海')
insert into commodity(commodity_name,commodity_price,commodity_detail,commodity_launch_date,commodity_pic_address) values('自行车04',6000.00,'山地自行车，经久耐骑',GETDATE(),'中国·上海')
insert into commodity(commodity_name,commodity_price,commodity_detail,commodity_launch_date,commodity_pic_address) values('自行车05',5400.00,'山地自行车，经久耐骑',GETDATE(),'中国·上海')
insert into commodity(commodity_name,commodity_price,commodity_detail,commodity_launch_date,commodity_pic_address) values('自行车06',1199.00,'山地自行车，经久耐骑',GETDATE(),'中国·上海')
insert into commodity(commodity_name,commodity_price,commodity_detail,commodity_launch_date,commodity_pic_address) values('格力空调',1999.00,'一晚只需一度电',GETDATE(),'中国·上海')
insert into commodity(commodity_name,commodity_price,commodity_detail,commodity_launch_date,commodity_pic_address) values('海尔冰箱',10899.00,'节能王',GETDATE(),'中国·上海')
-- 客户
insert into customer(customer_name,customer_address,customer_contact)values('胡先生','湖南省长沙市','123456789')
insert into customer(customer_name,customer_address,customer_contact)values('乐骑车行','山东省济南市','123456789')
insert into customer(customer_name,customer_address,customer_contact)values('xdm自行车连锁店','四川省成都市','123456789')
-- 供应商
insert into supplier(supplier_name,supplier_address,supplier_contact)values('上海浩能','上海市奉贤区','123456789')
insert into supplier(supplier_name,supplier_address,supplier_contact)values('刘先生','宁波市余姚市小曹娥镇','123456789')
insert into supplier(supplier_name,supplier_address,supplier_contact)values('吉利汽车','上海市','123456789')
-- 库存
insert into stock(stock_number,commodity_id,customer_id,supplier_id)values(80000,1,null,1)
insert into stock(stock_number,commodity_id,customer_id,supplier_id)values(2000,5,null,1)
insert into stock(stock_number,commodity_id,customer_id,supplier_id)values(20,4,2,null)
insert into stock(stock_number,commodity_id,customer_id,supplier_id)values(13,4,null,3)
insert into stock(stock_number,commodity_id,customer_id,supplier_id)values(12,7,2,null)
insert into stock(stock_number,commodity_id,customer_id,supplier_id)values(25,5,1,null)
insert into stock(stock_number,commodity_id,customer_id,supplier_id)values(20000,1,null,1)
insert into stock(stock_number,commodity_id,customer_id,supplier_id)values(9,3,2,null)
insert into stock(stock_number,commodity_id,customer_id,supplier_id)values(60000,2,null,2)
-- 销售记录表
insert into sales(sales_date,sales_number,sales_final_price,commodity_id,customer_id,supplier_id) values('1999-12-16 19:25:41',10,12000,1,1,1);
insert into sales(sales_date,sales_number,sales_final_price,commodity_id,customer_id,supplier_id) values('2001-12-16 19:25:41',1,2400,2,1,2);
insert into sales(sales_date,sales_number,sales_final_price,commodity_id,customer_id,supplier_id) values('2006-12-16 19:25:41',100,119900,6,1,3);
insert into sales(sales_date,sales_number,sales_final_price,commodity_id,customer_id,supplier_id) values('2008-12-16 19:25:41',10,12000,1,1,1);
insert into sales(sales_date,sales_number,sales_final_price,commodity_id,customer_id,supplier_id) values('2010-12-16 19:25:41',10,12000,1,2,1);
insert into sales(sales_date,sales_number,sales_final_price,commodity_id,customer_id,supplier_id) values('2012-12-16 19:25:41',10,12000,1,2,1);
insert into sales(sales_date,sales_number,sales_final_price,commodity_id,customer_id,supplier_id) values('2015-12-16 19:25:41',10,12000,1,3,1);
insert into sales(sales_date,sales_number,sales_final_price,commodity_id,customer_id,supplier_id) values('2019-12-16 19:25:41',5,12000,1,3,2);
insert into sales(sales_date,sales_number,sales_final_price,commodity_id,customer_id,supplier_id) values('2021-12-16 19:25:41',1,2400,2,2,2);
insert into sales(sales_date,sales_number,sales_final_price,commodity_id,customer_id,supplier_id) values('2021-12-16 19:25:41',100,119900,6,1,3);
insert into sales(sales_date,sales_number,sales_final_price,commodity_id,customer_id,supplier_id) values(GETDATE(),10,12000,1,1,3);
insert into sales(sales_date,sales_number,sales_final_price,commodity_id,customer_id,supplier_id) values(GETDATE(),10,11990,6,2,3);
-- 付款记录表
insert into payment(is_pay,payment_date,customer_id,supplier_id)values(1,'1999-12-16 19:25:41',1,1);
insert into payment(is_pay,payment_date,customer_id,supplier_id)values(1,'2001-12-16 19:25:41',1,2);
insert into payment(is_pay,payment_date,customer_id,supplier_id)values(1,'2006-12-16 19:25:41',1,3);
insert into payment(is_pay,payment_date,customer_id,supplier_id)values(1,'2008-12-16 19:25:41',1,1);
insert into payment(is_pay,payment_date,customer_id,supplier_id)values(1,'2010-12-16 19:25:41',2,1);
insert into payment(is_pay,payment_date,customer_id,supplier_id)values(1,'2012-12-16 19:25:41',2,1);
insert into payment(is_pay,payment_date,customer_id,supplier_id)values(1,'2015-12-16 19:25:41',3,1);
insert into payment(is_pay,payment_date,customer_id,supplier_id)values(1,'2019-12-16 19:25:41',3,2);
insert into payment(is_pay,payment_date,customer_id,supplier_id)values(1,'2020-12-16 19:25:41',2,2);
insert into payment(is_pay,payment_date,customer_id,supplier_id)values(1,'2021-12-16 19:25:41',1,3);
insert into payment(is_pay,payment_date,customer_id,supplier_id)values(0,'2021-12.31 15:22:34',1,3);
insert into payment(is_pay,payment_date,customer_id,supplier_id)values(0,GETDATE(),2,3);
运行结束后，打开表即可看到数据，这里可以查看以下进货记录表purchase，上面的sql中并没有为其插入数据，但是其中确是有数据的，就是触发器的作用了
5. 创建视图
use manger;
go
create view v_pay -- 根据查询结果创建视图
as
select * from payment where DATEDIFF(YYYY,payment_date,GETDATE())=0 and customer_id = ( -- 查询该顾客一年内的付款记录
select customer_id from customer where customer_name='胡先生' -- 嵌套查询，查询客户 胡先生 的编号
);
go
create view v_sale -- 根据查询结果创建视图
as
select * from sales where DATEDIFF(YYYY,sales_date,GETDATE())=0 and customer_id = ( -- 查询该顾客一年内的购买记录
select customer_id from customer where customer_name='胡先生' -- 嵌套查询，查询客户 胡先生 的编号
);
6. 创建角色并授权
use manger;
go
create role Select_SalesPerson --创建角色
GRANT select ON OBJECT::sales
TO Select_SalesPerson;
ER图
7361
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
