数据库实验报告

数据库实验报告

2010211122班 李济汉 10210737 20号

实验一：用SQL Server实现数据库设计

建立数据库：

CREATE DATABASE teaching ON PRIMARY

(NAME='teaching_data',

FILENAME='e:\\李济汉\\teaching.mdf', SIZE=1MB, MAXSIZE=5MB, FILEGROWTH=10% ); LOG ON (

NAME='teaching_log',

FILENAME='e:\\李济汉\\teaching.ldf', SIZE=1MB, MAXSIZE=2MB, FILEGROWTH=10% );

建立数据类型：

CREATE TYPE student_no

FROM CHAR(10) NOT NULL; FROM INTEGER; FROM VARCHAR(15); CREATE TYPE member_no CREATE TYPE shortstring

建立表： 班级表

CREATE TABLE class ( );

CLname CHAR(10) PRIMARY KEY, CLmajor CHAR(20), CLgrade CHAR(4), CLnumber INTEGER, CLteacher CHAR(10)

学生表

CREATE TABLE student (

Sno CHAR(8) PRIMARY KEY, Sname CHAR(20) UNIQUE, Sclass CHAR(10) NOT NULL, Sgender CHAR(2) DEFAULT '男', Sbirthday DATETIME, Sface CHAR(10), Splace CHAR(6),

Stime DATETIME,

Sphone CHAR(15) DEFAULT 'unknown', Sexp VARCHAR(200),

FOREIGN KEY (Sclass) REFERENCES class(CLname) );

CREATE DEFAULT Sphone AS 'unknown'

课程表

CREATE TABLE lesson ( );

Lnum CHAR(10) PRIMARY KEY, Lname CHAR(20) UNIQUE, Lkind CHAR(10), Lmajor CHAR(10), Lgrade CHAR(4), Ltime DATETIME, Lscore CHAR(2),

Lperiod CHAR(3)