資料庫原理實驗報告（一）

• 答案在后面

一、在studentdb中創建架構Production和Person并比較區別，

create schema Production --架構命名不能以數字開頭

create schema Person AUTHORIZATION st

注意: 在創建Person架構前需要使用下面的三條陳述句先在當前資料庫中添加用戶，并僅僅授予該用戶建表的權限，

CREATE LOGIN st WITH PASSWORD=‘suntao123’

CREATE USER st FOR LOGIN st

GRANT create table to st

然后用戶st以SQL SERVER身份驗證方式登錄服務器，嘗試執行如下的SQL陳述句：

create table Person.t1(id int,name char(10)) --成功

create table Production.t1(id int,name char(10)) --失敗，原因？

二、修改表結構，具體要求如下：

(1) 將表course的cname列的資料型別改為varchar(40).

(2) 為表student增加一個新列: birthday(出生日期), 型別為datetime, 默認為空值.

(3) 將表sc中的grade列的取值范圍改為小于等于150的正數.

(4) 為Student表的“Sex”欄位創建一個預設約束，預設值為’男’

(5)為“Sdept”欄位創建一個檢查約束，使得所在系必須是’CS’、’MA’或’IS’之一，

(6)為Student表的“Sname”欄位增加一個唯一性約束

(7)為SC表建立外鍵,依賴于Student表的fk_S_c約束，

(8)禁止啟用Student表的“Sdept”的CHECK約束ck_student，

三、分別建立以下索引(如果不能成功建立,請分析原因)

(1) 在student表的sname列上建立普通降序索引.

(2) 在course表的cname列上建立唯一索引.

(3) 在sc表的sno列上建立聚集索引.

(4) 在spj表的sno(升序), pno(升序)和jno(降序)三列上建立一個普通索引.

create database spjdb;
create database studentdb;

use studentdb;
create table student
(
    sno   char(9) primary key not null,
    sname char(10)            not null,
    ssex  char(2),
    sage  smallint,
    sdept char(15),
    check (sage >= 12)
);
use studentdb;
create table course
(
    cno     char(4) primary key not null ,
    cname   char(20),
    cpno    char(4),
    ccredit smallint,
);

create table sc
(
    sno   char(9) not null ,
    cno   char(4) not null ,
    grade decimal(5,1),
    foreign key (sno)references student(sno),
    foreign key (cno)references course(cno),
    check (grade between 0 and 100)
);

use spjdb;
create table S(
    sno char(2) primary key not null ,
    sname char(10) not null ,
    status smallint,
    city char(10),
    check (status>0)
);

create table P(
    pno char(2) primary key not null ,
    pname char(10) not null ,
    color char(2),
    weight smallint,
    check (weight>0)
);

create table J(
    jno char(2) primary key not null ,
    jname char(10) not null ,
    city char(10)
);

create table SPJ(
    sno char(2) not null ,
    pno char(2) not null ,
    jno char(2) not null ,
    qty smallint,
    primary key (sno,pno,jno),
    foreign key (sno)references S(sno),
    foreign key (pno)references P(pno),
    foreign key (jno)references J(jno),
    check (qty>0)
);

--use studentdb;
--create schema Production;

--CREATE LOGIN st WITH PASSWORD='本人mysql密碼';

--CREATE USER st FOR LOGIN st;

--GRANT create table to st;

--create schema Person AUTHORIZATION  st;

--create table Person.t1(id int,name char(10));

--修改表
--(1) 將表course的cname列的資料型別改為varchar(40).
--alter table course alter cname varchar(40); mysql
alter table course alter column cname varchar(40);

--(2) 為表student增加一個新列: birthday(出生日期), 型別為datetime, 默認為空值.
alter table student add birthday datetime default null;

--(3) 將表sc中的grade列的取值范圍改為小于等于150的正數.
--洗掉表中原有約束
alter table sc
drop constraint CK__sc__grade__2A4B4B5E;
--創建新約束
alter table sc
add constraint grade_new check (grade<=150);

--(4) 為Student表的“Sex”欄位創建一個預設約束，預設值為’男’
alter table student
add constraint ssex__new default '男' for ssex;

--(5)為“Sdept”欄位創建一個檢查約束，使得所在系必須是’CS’、’MA’或’IS’之一，
alter table student
add constraint ck_student check (sdept in ('CS','MA','IS'));

--(6)為Student表的“Sname”欄位增加一個唯一性約束
alter table student
add unique (sname);

--(7)為SC表建立外鍵,依賴于Student表的fk_S_c約束，
alter table sc
add constraint fk_S_c foreign key (sno) references student(sno);

--(8)禁止啟用Student表的“Sdept”的CHECK約束ck_student，
alter table student
nocheck constraint ck_student;

--6. 分別建立以下索引(如果不能成功建立,請分析原因)
--(1) 在student表的sname列上建立普通降序索引.
create unique index sname on student(sname desc );

--(2) 在course表的cname列上建立唯一索引.
create unique index cname on course(cname);

--(3) 在sc表的sno列上建立聚集索引.
create clustered index 索引名 on sc(sno);

--(4) 在spj表的sno(升序), pno(升序)和jno(降序)三列上建立一個普通索引.
use spjdb;
create unique index sno on SPJ(sno asc,pno asc,jno desc);