Oracle数据库模式对象管理

1.打开SQL Plus，以system用户登录BOOKSALE数据库。

system/tiger @booksale

2.按下列方式创建一个用户bs，并给该用户授权。

create user bs identified by bs default tablespace users;

grant resource,connect,create view to bs;

3.使用bs用户登录数据库，并进行下面的相关操作。

4.根据图书销售系统关系模式设计，创建表1至表6。

①customers表：

create table customers(

customer_id number(4) primary key,

name char(20) not null,

phone varchar2(50) not null,

email varchar2(50),

address varchar2(200),

code varchar2(10)

);

 

②publishers表：

create table publishers(

publisher_id number(2) primary key,

name varchar2(50),

contact char(10),

phone varchar2(50)

);

③books表：

create table books(

ISBN varchar2(50) primary key,

title varchar2(50),

author varchar2(50),

pubdate date,

publisher_id number(2),

cost number(6,2),

retail number(6,2),

category varchar2(50),

foreign key(publisher_id) references publishers(publisher_id)

);

④orders表：

create table orders(

order_id number(4) primary key,

customer_id number(4),

orderdate date not null,

shipdate date,

shipaddress varchar2(200),

shipcode varchar2(10),

foreign key(customer_id) references customers(customer_id)

);

⑤orderitem表：

create table orderitem(

order_id number(4),

item_id number(4),

ISBN varchar2(50) not null,

quantity number(4),

primary key(order_id,item_id),

foreign key(order_id) references orders(order_id)

);

 

⑥promotion表：

create table promotion(

gift_id number(2),

name char(20) primary key,

minretail number(5,2),

maxretail number(5,2)

);

5.在CUSTOMERS表的name列上创建一个B-树索引，要求索引值为大写字母。

create index cus_name_index on customers(upper(name)) tablespace users;

6.在BOOKS表的title列上创建一个非唯一性索引。

create index book_title_index on books(title) tablespace users;

7.在ORDERitem表的ISBN列上创建一个唯一性索引。

create unique index oitem_isbn_index on orderitem(ISBN) tablespace users;

8.创建一个视图customers_book，描述客户与订单的详细信息，包括客户编号、客户名单、订购图书的ISBN、图书名称、图书数量、订货日期、发货日期等。

create view customers_book(customer_id,name,ISBN,title,quantity,orderdate,shipdate)

as

select c.customer_id,c.name,o.ISBN,b.title,o.quantity,O.orderdate,O.shipdate

from customers c,orderitem o,orders O,books b;

9.创建一个视图customers_gift，描述客户获得礼品的信息，包括客户名称、图书总价、礼品名称。

create view customers_gift(customer_name,book_price,promotion_name)

as

select c.name,(case when oi.quantity<=10 then oi.quantity*b.retail else oi.quantity*b.cost end),p.name

from customers c,orderitem oi,books b,promotion p,orders o

where c.customer_id=o.customer_id and o.order_id=oi.order_id and b.ISBN=oi.ISBN and   

case when oi.quantity<=10 then oi.quantity*b.retail else oi.quantity*b.cost end between minretail and maxretail;

10.定义序列seq_customers，产生客户编号，序列起始值为1，步长为1，不缓存，不循环。

create sequence seq_customers start with 1 increment by 1 nocache nocycle;

11.定义序列seq_orders，产生订单编号，序列起始值为1000，步长为1，不缓存，不循环。

create sequence seq_orders start with 1000 increment by 1 nocache nocycle;

12.定义序列seq_promotion，产生礼品编号，序列起始值为1，步长为1，不缓存，不循环。

create sequence seq_promotion start with 1 increment by 1 nocache nocycle;