没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
中南大学
大型数据库报告
学生姓名
学 院 信息科学与工程学院
专业班级 计科
120 x
《大型数据库技术》实验一
1.创建一个本地位图管理表空间 CAP_ts,表空间对应一个数据文件 CAP_ts.dbf,该数据
文件初始大小为 20M,可以自动扩展。
CREATE TABLESPACE CAP_ts datafile 'CAP_ts.dbf' size 20M reuse extent management local
autoallocate;
ALTER DATABASE DEFAULT TABLESPACE CAP_ts;
2.在表空间 CAP_ts 中创建表 Customers、Products 和 Agents,其中列 cid、pid、aid 分别为
这 3 张表的主键。向表中添加如下数据 (可首先将表中数据放入 EXCEL 表,然后在 SQL
Developer 中导入数据库)。
Customers
cid cname city discnt
C001 TipTop Duluth 10.00
C002 Basics Dallas 12.00
C003 Allied Dallas 8.00
C004 ACME Duluth 8.00
C005 Oriental Kyoto 6.00
C006 ACME Kyoto 0.00
Products
pid pname city quantity price
P01 comb Dallas 111400 0.50
P02 brush Newark 203000 0.50
P03 razor Duluth 150600 1.00
P04 Pen Duluth 125300 1.00
P05 pencil Dallas 221400 1.00
P06 folder Dallas 123100 2.00
P07 case Newark 100500 1.00
Agents
aid aname city percent
A01 smith New York 6
A02 Jones Newark 6
A03 Brown Tokyo 7
A04 Gray New York 6
A05 Otasi Duluth 5
A06 Smith Dallas 5
CREATE TABLE Customers(cid VARCHAR(5) PRIMARY KEY NOT NULL,cnaem
VARCHAR(10),city VARCHAR(10),discount REAL);
CREATE TABLE Products(pid VARCHAR(5) PRIMARY KEY NOT NULL,pnaem
VARCHAR(10),ity VARCHAR(10),quantity NUMBER(10),price REAL);
CREATE TABLE Agents(aid VARCHAR(5) PRIMARY KEY NOT NULL,anaem
VARCHAR(10),city VARCHAR(10),percent NUMBER(3));
insert into Customers values('C001','TipTop','Duluth',10.00);
insert into Customers values('C002','Basics','Dallas',12.00);
insert into Customers values('C003','Allied','Dallas',8.00);
insert into Customers values('C004','ACME','Duluth',8.00);
insert into Customers values('C005','Oriental','Kyoto',6.00);
1
insert into Customers values('C006','ACME','Kyoto',0.00);
insert into Products values('P01','comb','Dallas',111400,0.50);
insert into Products values('P02','brush','Newark',203000,0.50);
insert into Products values('P03','razor','Duluth',150600,1.00);
insert into Products values('P04','Pen','Duluth',125300,1.00);
insert into Products values('P05','pencil','Dallas',221400,1.00);
insert into Products values('P06','folde','Dallas',123100,2.00);
insert into Products values('P07','case','Newark',100500,1.00);
insert into Agents values('A01','smith','New York',6);
insert into Agents values('A02','Jones','Newark',6);
insert into Agents values('A03','Brown','Tokyo',7);
insert into Agents values('A04','Gray','New York',6);
insert into Agents values('A05','Otasi','Duluth',5);
insert into Agents values('A06','smith','Dallas',5);
3.通过数据字
典视图查看是否
已 创 建 表
Customers 、 Pro
ducts 和
Agents,以及每个表的存储参数设置。
SELECT table_name,tablespace_name, initial_extent,next_extent
FROM user_tables
4.在表空间 CAP_ts 中创建分区表 orders,该表以列 ordno 为主键,列 cid、aid、pid 为外
键。列 month 作为分区关键字,数据按照季度分区,即将一个季度的订单数据放到一个分
区中。例如一月份、二月份、三月份为第一季度,这三个月的订单记录放在一个分区中。
向表 orders 中添加如下数据:
Orders
ordno month cid aid pid qty dollars
1011 Jan C001 A01 P01 1000 450.00
1012 Jan C001 A01 P01 1000 450.00
1019 Feb C001 A02 P02 400 180.00
2
1017 Feb C001 A06 P03 600 540.00
1018 Feb C001 A03 P04 600 540.00
1023 Mar C001 A04 P05 500 450.00
1022 Mar C001 A05 P06 400 720.00
1025 Apr C001 A05 P07 800 720.00
1013 Jan C002 A03 P03 1000 880.00
1026 May C002 A05 P03 800 704.00
1015 Jan C003 A03 P05 1200 1104.00
1014 Jan C003 A03 P05 1200 1104.00
1021 Feb C004 A06 P01 1000 460.00
1016 Jan C004 A01 P01 1000 500.00
1020 Feb C005 A03 P07 600 600.00
1024 Mar C006 A06 P01 800 400.00
create table orders(
orderno number(4) primary key not null,
month varchar(3),
cid varchar(5),
aid varchar(5),
pid varchar(5),
qty number(4),
dollars real,
constraint cid_fk foreign key (cid) references Customers(cid),
constraint aid_fk foreign key (aid) references Agents(aid),
constraint pid_fk foreign key (pid) references Products(pid)
)
partition by list(month)(
PARTITION season1 VALUES ('Jan', 'Feb', 'Mar'),
PARTITION season2 VALUES ('Apr', 'May')
);
insert into orders values(1011,'Jan','C001','a01','P01',1000,450.00);
insert into orders values(1012,'Jan','C001','a01','P01',1000,450.00);
insert into orders values(1019,'Feb','C001','a02','P02',400,180.00);
insert into orders values(1017,'Feb','C001','a06','P03',600,540.00);
insert into orders values(1018,'Feb','C001','a03','P04',600,540.00);
insert into orders values(1023,'Mar','C001','a04','P05',500,450.00);
insert into orders values(1022,'Mar','C001','a05','P06',400,720.00);
insert into orders values(1025,'Apr','C001','a05','P07',800,720.00);
insert into orders values(1013,'Jan','C002','a03','P03',1000,880.00);
insert into orders values(1026,'May','C002','a05','P03',800,704.00);
insert into orders values(1015,'Jan','C003','a03','P05',1200,1104.00);
insert into orders values(1014,'Jan','C003','a03','P05',1200,1104.00);
insert into orders values(1021,'Feb','C004','a06','P01',1000,460.00);
insert into orders values(1016,'Jan','C004','a01','P01',1000,500.00);
3
剩余15页未读,继续阅读
资源评论
- wumingshi54682022-12-12资源简直太好了,完美解决了当下遇到的难题,这样的资源很难不支持~
老帽爬新坡
- 粉丝: 82
- 资源: 2万+
下载权益
C知道特权
VIP文章
课程特权
开通VIP
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功