mydata.sql

学生成绩管理系统开发软件

CREATE DATABASE Arcdata  ON (NAME = 'Arcdata', 
FILENAME = 'E:\exercise\学籍管理\data\Arcdata.MDF' , SIZE = 1, FILEGROWTH = 10%)
 LOG ON (NAME = 'mydata_Log', 
FILENAME = 'E:\exercise\学籍管理\data\Arcdata_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
go

use Arcdata
go

CREATE TABLE Classinfo(
cID    char(6) primary key not null,
cClass char(10)
)
go

CREATE TABLE Course(
cID    char(6)  primary key not null,
cName  char(8)  not null
)
go

CREATE TABLE Arcinfo(
bID        char(8) primary key not null,
cID        char(6) not null,
bGender    char(2) not null,
bNation    char(4),
bPolitic    char(8),
bFamily    char(8),
bNative    char(18),
bFaName    char(8),
bWorkAddr  char(30),
bFamAddr   char(30),
bPost      char(6),
bTel       char(12),
bDate      char(10),
bBirth     char(10),
bHobby     char(100),
bStatus    char(4),
bResume    text,
bMemo      text,
bPhoto     image,
foreign key(cID) references Classinfo(cID)
)
go

CREATE TABLE CourseMark(
bID   char(8) not null,
cID   char(6) not null,
ccID  char(6) not null,
mark  numeric(4,1) not null,
cStyle char(4) not null,
Term char(10) not null,
foreign key(bID) references Arcinfo(bID)
)
go

CREATE TABLE Loginfo(
uID      char(8)  not null,
User     char(10) ,
LogDate  char(10),
LogTime  char(10),
LeftTime char(10)
)
go

CREATE TABLE Reginfo(
bID    char(8) not null,
cID    char(6) not null,
member  char(6),
age    int,
rterm  char(10),
rDate  char(10),
Mem0   text
)
go

CREATE TABLE Userinfo(
uID     char(8)  primary key not null,
User    char(10) not null,
Passwd  char(8) not null,
CreateD char(8)
)
go

CREATE TABLE Statusinfo(
bID     char(8) not null,
school   char(16),
xclass   char(10),
Reasion char(100),
cDate   char(10),
Memo    char(255),
foreign key(bID) reference Arcinfo(bID),
)
go

CREATE TABLE funt(
fID    char(10) primary key not null,
fName  char(16) not null
)
go

CREATE TABLE bgrant(
fID    char(10) not null,
uID    char(8) not null,
rright bit,
foreign key(uID) reference Userinfo(uID),
foreign key(fID) reference funt(fID)
)
go

CREATE VIEW Logintb
as
SELECT uID, User, LogDate, LogTime, LeftTime
FROM Loginfo
ORDER BY LogDate DESC,LeftTime DESC
go

CREATE VIEW regdate(bID,bName,bGender,age,member,bFamily,bNative,bFaName,bWorkAddr,rterm,rDate,Memo,cClass)
as
SELECT a.bID, b.bName, b.bGender, a.age, a.member, b.bFamily, b.bNative, b.bFaName, b.bWorkAddr, a.rterm, a.rDate, a.Memo, c.cClass
FROM Reginfo AS a, Arcinfo AS b, Classinfo AS c
WHERE a.bID=b.bID And b.cID=c.cID
go

CREATE VIEW statusstd(bID,bName,cClass,bStatus,school,xclass,Reasion,cDate,Memo)
as
SELECT b.[bID], b.[bName], a.[cClass], b.[bStatus], c.[school], c.[xclass], c.[Reasion], c.[cDate], c.[Memo]
FROM Classinfo a INNER JOIN (Arcinfo b INNER JOIN Statuinfo c ON b.[bID]=c.[bID]) ON a.[cID]=b.[cID]
ORDER BY c.[bID], b.[bName]
go

CREATE VIEW subject(bID,cID,ccID,bName,cClass,cName,Mark,cStyle,Term)
as
SELECT a.bID, a.cID, a.ccID, b.bName, c.cClass, d.cName, a.Mark, a.cStyle, a.Term
FROM CourseMark AS a, Arcinfo AS b, Classinfo AS c, Course AS d
WHERE a.bID=b.bID AND a.cID=c.cID AND a.ccID=d.cID
go