Oracle实验四—Oracle数据库对象管理

实验四——Oracle数据库对象管理

–2按下列方式创建一个用户bs，并给其授权【由于bs有冲突，换成了ds】
create user ds identified by ds default tablespace users;
grant resource,connect,create view to ds;

– 3使用bs用户登录数据库，进行相关操作
conn ds;

–4根据图书销售系统关系模型设计，创建表A1-A6【此处表空间是users，可以自己新创建】
–A1表 CUSTOMERS
create table CUSTOMERS(
customer_id number(4) primary key,
name char(20) not null,
phone varcher2(50) not null,
email varcher2(50)
address varcher2(200),
code varcher2(10)
)
Tablespace users;

–A2表 PUBLISHERS
create table PUBLISHERS(
publisher_id number(2) primary key,
name varcher2(50),
contact char(10),
phone varcher2(50)
)
Tablespace users;

–A3表 BOOKS
create table BOOKS(
ISBN varcher2(50)primary key,
title varcher2(50) ,
author varcher2(50),
pubdate Date,
publisher_id number(2) references PUBLISHERS(publisher_id),
cost number(6,2),
retril number(6,2),
category varcher2(50)
)
Tablespace users;

–A4表 ORDERS
create table ORDERS(
order_id number(4) primary key,
customer_id number(4) references CUSTOMERS(customer_id)
orderdate date not null,
shipdate date,
shipaddress vachar2(200),
shipcode varchar2(10)
)
Tablespace users;

–A5表 ORDERITEM
create table ORDERITEM(
order_id number(4) references ORDERS(order_id) primary key,
item_id number(4) primary key,
ISBN vachar2(50) not null,
quantity number(4)
)
Tablespace users;

–A6表 PROMOTION
create table PROMOTION(
gift_id number(2) ,
name char(20) primary key,
minretail number(5,2),
maxretail number(5,2)
)
Tablespace users;

–5在customers表的title列上创建一个非唯一性属性
create index BOOKS_TITLE_NOUNIQUE on books(title);

–7在orderitem表的ISBN列上创建唯一性索引
create unique index orderitem_isbn_unique_index on orderitem{isbn);

–8创建一个customers_book，描述客户与订单的详细信息，包括客户编号，客户名单，
–订购图书的ISBN，图书名称，图书数量，订货日期，发货日期
create view customers_book
as
select dg.customer_id 客户编号,dg.name 客户名称,dbc.ISBN 订购图书ISBN,dbc.title 图书名称,
dbc.quantity 图书数量,dg.orderdate 订货日期, dg.shipdate 发货日期
from
(select cs.customer_id,cs.name, os.orderdate,os.shipdate,os.order_id
from orderos left join customers cs on os.customers cs on os.customer_id=cs.customer_id)dg,
(select ot.ISBN,b.title,ot.quantity.ot.order_id
from orderitem ot left join books b on ot.ISBN=b.ISBN)dbc
where dg.order_id=dbc.order_id;

–9创建一个属兔customers_gif，描述客户获得礼品的信息，包括客户名称，顶偶图书名称，图书总价，礼品名称
create view customers_ gif
as
select * from
(select cs.name , os.order_id
from orders os left join customers cs on os.customer_id = cs.customer_id) dg ,
(select b.title ,ot.quantity ,ot.order_id , b.retail * ot.quantity allprice , po.name
from orderitem ot left join books b on ot.ISBN = b.ISBN
left join promotion po on po.gift_id = ot.item_id) dbc
where dg.order_id = dbc.order_id;

–10定义序列seq_customers,产生订单编号，序列起始值为1步长为1，不缓存，不循环
create sequence seq_customers increment by 1 start with 1;

–11定义序列seq_orders，产生订单编号，序列起始值为1000步长为1，不缓存，不循环
create sequence seq_orders increment by 1 start with 1000;

–12定义序列scq_oderitem，产生订单编号，序列起始值为1，步长为1 ，不缓存，不循环
create sequence scq_oderitem increment by 1 start with 1;