这里写自定义目录标题
根据以下业务要求要求完成数据库应用系统开发1. 新建查询创建数据库2.创建表3. 创建触发器4. 插入数据5. 创建视图6. 创建角色并授权ER图根据以下业务要求要求完成数据库应用系统开发
面向工业领域中商品的进货、销售、库存和记账等业务开发一个数据库应用系统:某客户、供应商以及他们之间往来的商品如汽车、电器等,需有效地记录这些商品进货、库存等账目,并登记客户和供应商的往来款项以便及时收/款。(注意往来款项不需要即时结清。)
以下为系统开发的主要内容(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 mangerCREATE DATABASE manger--创建数据库ON PRIMARY-- 默认文件组主数据文件(NAME='manger_data',-- 逻辑文件名FILENAME='F:\sqlServer\parmary\manger_data.mdf',-- 物理文件名SIZE=5MB,-- 初始大小5MBMAXSIZE=15MB,-- 最大15MBFILEGROWTH=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_idintnot nullidentity,--商品编号非空约束，自增约束commodity_namevarchar(50)not null,--商品名称非空约束commodity_pricedecimal(20,2)not null,--商品价格非空约束commodity_detailvarchar(500),--商品详情commodity_launch_date datetimenot nulldefault(getdate()),--生产日期 默认得到系统时间非空约束，默认值约束commodity_pic_address varchar(100),--生产地址constraint PK_commodity_idprimary key (commodity_id)--表级约束：主键 ); ---客户表if exists(select * from sysobjects where name = 'customer') drop table customer create Table customer ( customer_idintnot nullidentity,--客户编号非空约束，自增约束customer_namevarchar(50)not null,--客户名称非空约束customer_addressvarchar(100),--客户地址customer_contactvarchar(50)not null,--联系方式非空约束constraint PK_customer_idprimary key (customer_id)--表级约束：主键);---供应商表if exists(select * from sysobjects where name = 'supplier') drop table supplier create Table supplier ( supplier_idintnot nullidentity,--供应商编号非空约束，自增约束supplier_namevarchar(50)not null,--供应商名称非空约束supplier_addressvarchar(100),--供应商地址supplier_contactvarchar(50)not null,--联系方式非空约束constraint PK_supplier_idprimary key (supplier_id)--表级约束：主键);---库存表if exists(select * from sysobjects where name = 'stock') drop table stock;create Table stock ( stock_idintnot nullidentity,--库存编号非空约束，自增约束stock_numberintnot nulldefault(0),--库存数量非空约束，默认值约束commodity_idintnot null,--商品编号非空约束customer_idintdefault(null),--客户编号非空约束，默认值约束supplier_idintdefault(null),--供应商编号非空约束，默认值约束constraint PK_stock_idprimary key (stock_id),-- 表级约束：主键constraintFK_commodity_stockforeign key (customer_id) references commodity(commodity_id),-- 表级约束：外键constraintFK_customer_stockforeign key (customer_id) references customer(customer_id),-- 表级约束：外键constraintFK_supplier_stockforeign key (supplier_id) references supplier(supplier_id)-- 表级约束：外键);---付款记录表if exists(select * from sysobjects where name = 'payment') drop table payment create Table payment ( payment_idintnot nullidentity,--付款编号非空约束，自增约束is_paybitnot nulldefault(0),--是否付款非空约束，默认值约束payment_datedatetimedefault(getdate()),--付款日期默认值约束customer_idintnot null,--客户编号非空约束supplier_idintnot null,--供应商编号非空约束，constraint PK_payment_idprimary key (payment_id),-- 表级约束：主键constraintFK_customer_paymentforeign key (customer_id) references customer(customer_id),-- 表级约束：外键constraintFK_supplier_paymentforeign key (supplier_id) references supplier(supplier_id)-- 表级约束：外键);-----------------------------------------------------------------------------规则rule---删除规则drop rule payment_pay_rule;-- 创建规则go-- 支付状态的值只有0和1两种create rule payment_pay_ruleas @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_idintnot nullidentity,--进货编号非空约束，自增约束purchase_datedatetimenot nulldefault(getdate()),--进货日期非空约束，默认值约束purchase_numberintnot null,--进货数量非空约束purchase_final_pricedecimal(20,2)not null,--成交价非空约束commodity_idintnot null,--商品编号非空约束customer_idintdefault(null),--客户编号非空约束，默认值约束supplier_idintdefault(null),--供应商编号非空约束，默认值约束constraint PK_purchase_idprimary key (purchase_id),-- 表级约束：主键constraintFK_commodity_purchaseforeign key (customer_id) references commodity(commodity_id),-- 表级约束：外键constraintFK_customer_purchaseforeign key (customer_id) references customer(customer_id),-- 表级约束：外键constraintFK_supplier_purchaseforeign key (supplier_id) references supplier(supplier_id)-- 表级约束：外键);---销售记录表if exists(select * from sysobjects where name = 'sales') drop table sales create Table sales ( sales_idintnot nullidentity,--销售编号非空约束，自增约束sales_datedatetimenot nulldefault(getdate()),--销售日期非空约束，默认值约束sales_numberintnot null,--销售数量非空约束sales_final_pricedecimal(20,2)not null,--成交价非空约束commodity_idintnot null,--商品编号非空约束 customer_idintdefault(null),--客户编号非空约束，默认值约束supplier_idintdefault(null),--供应商编号非空约束，默认值约束constraint PK_sales_idprimary key (sales_id),-- 表级约束：主键constraintFK_commodity_salesforeign key (customer_id) references commodity(commodity_id),-- 表级约束：外键constraintFK_customer_salesforeign key (customer_id) references customer(customer_id),-- 表级约束：外键constraintFK_supplier_salesforeign key (supplier_id) references supplier(supplier_id)-- 表级约束：外键);----------------------------------------------------------------------索引index-- 以sales表的sales_id与sales_date来建立索引create index Index_sales_id_dateon sales(sales_id,sales_date)--with (drop_existing=on)--在创建时如果已有索引先删除----------------------------------------------------------------------索引index
此时刷新以下数据库的表，就能看见创建的表了
3. 创建触发器
新建查询执行：
use manger;GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONgocreate 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中存放的新idIF EXISTS(SELECT purchase_id FROM purchase WHERE purchase_id=@PerID)--如果记录已存在，执行UPDATE操作BEGINprint(@PerID);UPDATE purchase SET--将临时表inserted中的数据更新到purchase表中purchase_date=sales_date,purchase_number=sales_number,purchase_final_price=sales_final_price,modity_id=modity_id,purchase.customer_id=inserted.customer_id,purchase.supplier_id=inserted.supplier_idFROM inserted WHERE purchase_id=@PerIDENDELSE IF @PerID IS NOT NULL--如果记录不存在且id不为空，执行INSERT操作BEGINprint(@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_idFROM insertedSET 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('-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('-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('-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('-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('-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('-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('-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('-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,'-12-16 19:25:41',1,3);insert into payment(is_pay,payment_date,customer_id,supplier_id)values(1,'-12-16 19:25:41',1,1);insert into payment(is_pay,payment_date,customer_id,supplier_id)values(1,'-12-16 19:25:41',2,1);insert into payment(is_pay,payment_date,customer_id,supplier_id)values(1,'-12-16 19:25:41',2,1);insert into payment(is_pay,payment_date,customer_id,supplier_id)values(1,'-12-16 19:25:41',3,1);insert into payment(is_pay,payment_date,customer_id,supplier_id)values(1,'-12-16 19:25:41',3,2);insert into payment(is_pay,payment_date,customer_id,supplier_id)values(1,'-12-16 19:25:41',2,2);insert into payment(is_pay,payment_date,customer_id,supplier_id)values(1,'-12-16 19:25:41',1,3);insert into payment(is_pay,payment_date,customer_id,supplier_id)values(0,'-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;gocreate view v_pay-- 根据查询结果创建视图asselect * from payment where DATEDIFF(YYYY,payment_date,GETDATE())=0and customer_id = (-- 查询该顾客一年内的付款记录select customer_id from customer where customer_name='胡先生'-- 嵌套查询，查询客户 胡先生 的编号);gocreate view v_sale-- 根据查询结果创建视图asselect * from sales where DATEDIFF(YYYY,sales_date,GETDATE())=0and customer_id = (-- 查询该顾客一年内的购买记录select customer_id from customer where customer_name='胡先生'-- 嵌套查询，查询客户 胡先生 的编号);
6. 创建角色并授权
use manger;gocreate role Select_SalesPerson --创建角色GRANT select ON OBJECT::salesTO Select_SalesPerson;
ER图
Sql Server 使用T-Sql 面向工业领域中商品的进货 销售 库存和记账等业务开发一个数据库应用系统:某客户 供应商以及他们之间往来的商品如汽车 电器等 需有效地记录这些商品进货 库存等账目
