小型系統資料庫設計

❶ SQL小型資料庫系統設計

--------------創建資料庫--------------------------
create database bankDB
on primary
(
name = 'bankDB',
filename = 'e:\bank\bankDB.mdf',
size = 3MB,
maxsize = 100MB,
filegrowth = 15%
)
log on
(
name = 'bankDB_log',
filename = 'e:\bank\bankDB_log.ldf',
size = 2MB,
filegrowth = 1MB
)
go
--------------------創建數據表---------------------
use bankDB
gocreate table userInfo
(
customerID int identity(1,1) not null,
customerName varchar(10) not null,
PID char(18) not null,
telphone char(13) not null,
adress varchar(100)
)alter table userInfo
add constraint PK_customerID primary key(customerID)
alter table userInfo
add constraint CK_PID check(PID like '[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or
PID like '[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]X' or
PID like '[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
alter table userInfo
add constraint CK_telphone check(telphone like '13[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' or telphone like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
alter table userInfo
add constraint DF_telphone default('地址不詳') for adress
alter table userInfo
add constraint UQ_PID unique(PID)create table cardInfo
(
cardID char(19) not null,
curType varchar(5) not null,
savingType varchar(6) not null,
openDate datetime not null,
openMoney money not null,
balance money not null,
pass char(6) not null,
IsReportLoss char(2) not null,
customerID int not null
)alter table cardInfo
add constraint FK_customerID foreign key(customerID) references userInfo(customerID)
alter table cardInfo
add constraint PK_cardID primary key(cardID)
alter table cardInfo
add constraint DF_openDate default(getdate()) for openDate
alter table cardInfo
add constraint DF_pass default('888888') for pass
alter table cardInfo
add constraint DF_curType default('RMB') for curType
alter table cardInfo
add constraint CK_openMoney check(openMoney > 1)
alter table cardInfo
add constraint CK_balance check(balance > 1)
alter table cardInfo
add constraint CK_pass check(pass like '[0-9][0-9][0-9][0-9][0-9][0-9]')
alter table cardInfo
add constraint DF_cardID default('1010 3576 ' + convert(varchar(4),convert(int,rand()*10000)) + ' ' +convert(varchar(4),convert(int,rand()*10000))) for cardID
alter table cardInfo
add constraint DF_IsReportLoss default('否') for IsReportLoss
alter table cardInfo
add constraint CK_IsReportLoss check(IsReportLoss like '是' or IsReportLoss like '否')
alter table cardInfo
add constraint CK_savingType check(savingType like '定期' or savingType like '活期' or savingType like '定活期')create table transInfo
(
transDate datetime not null,
cardID char(19) not null,
transType char(4) not null,
transMoney money not null,
remark varchar(100)
)alter table transInfo
add constraint DF_transDate default(getdate()) for transDate
alter table transInfo
add constraint FK_cardID foreign key(cardID) references cardInfo(cardID)
alter table transInfo
add constraint CK_stransType check(transType like '存入' or transType like '支取')
alter table transInfo
add constraint CK_transMoney check(transMoney > 0)go
--------------------創建開戶用戶卡信息視圖---------
create view v_cus
as
select customerName, cardID,curType,savingType,telphone,adress
from cardInfo inner join userInfo on cardInfo.customerID = userInfo.customerID
where datediff(wk,openDate,getdate()) = 0-----------------------修改密碼--------------------
create proc changePWD
@cardID char(19),
@newpwd char(6),
@oldpwd char(6) = '888888'
as
declare @temppwd int
if((select count(*) from cardInfo where cardID = @cardID) = 0)
begin
print '您輸入的卡號不存在！'
return
end
select @temppwd = pass from cardInfo where cardID = @cardID
if(@temppwd = @oldpwd)
begin
update cardInfo set pass = @newpwd where cardID = @cardID
if(@@error = 0)
print '密碼修改成功！'
else
print '密碼修改失敗！'
end
else
print '您輸入的原始密碼不對，不能修改密碼！'----------------------銀行卡掛失-------------------
create proc ReportLoss
@cardID char(19),
@PID char(18)
as
if((select count(*) from cardInfo where cardID = @cardID) = 0)
begin
print '您輸入的卡號不存在！'
return
end
if((select IsReportLoss from cardInfo where cardID = @cardID) = '是')
begin
print '該卡已掛失！'
return
end
if((select PID from userInfo where customerID = (select customerID from cardInfo where cardID = @cardID)) = @PID)
begin
update cardInfo set IsReportLoss = '是' where cardID = @cardID
if(@@error = 0)
print '掛失成功！'
else
print '掛失失敗！'
end
else
begin
print '您輸入的身份證錯誤！不能掛失！'
return
end----------------------取消銀行卡掛失-------------------
create proc CancelReportLoss
@cardID char(19),
@PID char(18)
as
if((select count(*) from cardInfo where cardID = @cardID) = 0)
begin
print '您輸入的卡號不存在！'
return
end
if((select IsReportLoss from cardInfo where cardID = @cardID) = '否')
begin
print '該卡沒有掛失！不能進行此項操作！'
return
end
if((select PID from userInfo where customerID = (select customerID from cardInfo where cardID = @cardID)) = @PID)
begin
update cardInfo set IsReportLoss = '否' where cardID = @cardID
if(@@error = 0)
print '取消掛失成功！'
else
print '取消掛失失敗！'
end
else
begin
print '您輸入的身份證錯誤！不能取消掛失！'
return
end----------------------統計銀行的資金流通余額和盈利結算--------------------create proc StatMoney
as
select ((select sum(transMoney) as 資金流通余額 from transInfo where transType like '存入') - (select sum(transMoney) as 資金流通余額 from transInfo where transType like '支取'))
as 資金流通余額 select ((select sum(transMoney) as 資金流通余額 from transInfo where transType like '存入')*0.008
- (select sum(transMoney) as 資金流通余額 from transInfo where transType like '支取')*0.003)
as 盈利結算 ---------------------查詢本周開戶的卡號，顯示該卡的信息-------------------
create proc SearchNewCus
as
print '本周開戶卡號信息'
select * from v_cus -----------------------查詢本月交易金額最高的卡號-------------------------
create proc SearchHigh
as
select top 1 cardID, sum(transMoney) as 交易金額 from transInfo
group by cardID------------------------------查詢掛失帳號的客戶信息----------------------
create proc SearchLossCus
as
print '掛失帳號客戶信息'
select * from userInfo where customerID = (select customerID from cardInfo where IsReportLoss = '是')--------------------------催款提醒業務------------------------------------
create proc Awoke
as
select telphone, balance, customerName, cardID
from cardInfo inner join userInfo on cardInfo.customerID = userInfo.customerID
where balance < 200-------------------------銀行開戶-----------------------------------------
create proc OpenUser
@curType varchar(5),
@savingType varchar(6),
@openMoney money,
@PID char(18),
@telphone char(13),
@customerName varchar(10)
as
begin tran OpenU
declare @tag int
declare @tempID varchar(10)
set @tag = 0 insert into userInfo (customerName,PID,telphone) values (@customerName, @PID, @telphone)
set @tag = @tag + @@error
if(@tag = 0)
set @tempID = (select customerID from userInfo where PID = @PID)
insert into cardInfo (curType, savingType, openMoney, balance, customerID) values (@curType, @savingType, @openMoney, @openMoney, @tempID)
set @tag = @tag + @@error
if(@tag = 0)
begin
print '開戶成功！'
commit tran
end
else
begin
print '開戶失敗！'
rollback tran
end-----------------------------客戶存款-------------------------------------
create proc SaveMoney
@cardID char(19),
@transMoney money,
@transType char(4) = '存入'
as
if((select IsReportLoss from cardInfo where cardID = @cardID) = '是')
begin
print '該卡已掛失!無法對其進行操作!'
return
end
begin tran Saving
declare @tag int
set @tag = 0 insert into transInfo (cardID, transType, transMoney) values (@cardID, @transType, @transMoney)
set @tag = @tag + @@error
update cardInfo set balance = balance + @transMoney where cardID = @cardID
set @tag = @tag + @@error
if(@tag = 0)
begin
print '存款成功！'
commit tran
end
else
begin
print '存款失敗!'
rollback tran
end ------------------------------客戶取款------------------------------------
create proc DrawMoney
@cardID char(19),
@transMoney money,
@transType char(4) = '支取'
as
if((select IsReportLoss from cardInfo where cardID = @cardID) = '是')
begin
print '該卡已掛失!無法對其進行操作!'
return
end
begin tran Drawing
declare @tag int
set @tag = 0 insert into transInfo (cardID, transType, transMoney) values (@cardID, @transType, @transMoney)
set @tag = @tag + @@error
update cardInfo set balance = balance - @transMoney where cardID = @cardID
set @tag = @tag + @@error
if(@tag = 0)
begin
print '取款成功！'
commit tran
end
else
begin
print '取款失敗!'
rollback tran
end----------------------------轉帳------------------------------------------
create proc Transfer
@cardID_out char(19),
@cardID_in char(19),
@tranMoney money
as
if((select count(*) from cardInfo where cardID = @cardID_out or cardID = @cardID_in) <> 2)
begin
print '請確認兩張卡是否都存在！'
return
end
if((select IsReportLoss from cardInfo where cardID = @cardID_out) = '是')
begin
print @cardID_out + '該卡已掛失!無法對其進行操作!'
return
end
if((select IsReportLoss from cardInfo where cardID = @cardID_in) = '是')
begin
print @cardID_in + '該卡已掛失!無法對其進行操作!'
return
end
begin tran TransMoney
declare @tag int
set @tag = 0 update cardInfo set balance = balance - @tranMoney where cardID = @cardID_out
set @tag = @tag + @@error
update cardInfo set balance = balance + @tranMoney where cardID = @cardID_in
set @tag = @tag + @@error
if(@tag = 0)
begin
print '轉帳成功！'
commit tran
end
else
begin
print '轉帳失敗!'
rollback tran
end

❷ 求高人設計一個小型的資料庫管理系統

卧槽。老師是叫你自己寫的吧。

❸ 小型資料庫管理系統設計

系統演示視頻

❹ 小型資料庫系統設計

#include<stdio.h>
#include<stdlib.h>

char* month_str[]={"January","February","March","April","May","June","July","August","September","October","November","December"};
char* week[]={"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"};

int IsLeapYear(int year) /*find out the year is leap year or not*/
{
if((year%4==0&&year%100!=0)||(year%400==0))
return 1;
else
return 0;

}
int month_day(int year,int month)
{
int mon_day[]={31,28,31,30,31,30,31,31,30,31,30,31};
if(IsLeapYear(year)&&month==2)
return 29;
else
return(mon_day[month-1]);

}
int DaySearch(int year,int month,int day) /*search what day this day is*/
{
int c=0;
float s;
int m;
for(m=1;m<month;m++)
c=c+month_day(year,m);
c=c+day;
s=year-1+(float)(year-1)/4+(float)(year-1)/100+(float)(year-1)/400-40+c;
return ((int)s%7);
}

int PrintAllYear(int year)/*print the all year*/
{
int temp;
int i,j;
printf("\n\n%d Calander\n",year);
for(i=1;i<=12;i++)
{
printf("\n\n%s(%d)\n",month_str[i-1],i);
printf("0 1 2 3 4 5 6 \n");
printf("S M T W T F S \n\n");
temp=DaySearch(year,i,1);
for(j=1;j<=month_day(year,i)+temp;j++)
{
if(j-temp<=0)
printf(" ");
else if(j-temp<10)
printf("%d ",j-temp);
else
printf("%d ",j-temp);

if(j%7==0)
printf("\n");
}
}
return 0;
}

int main()
{
int option,da;
char ch;
int year,month,day;
printf("Copyright @ 2005 TianQian All rights reserved!:):):)");
printf("\n\nWelcome to use the WanNianLi system!\n");

while(1)
{
printf("\nPlease select the service you need:\n");
printf("\n1 Search what day the day is");
printf("\n2 Search whether the year is leap year or not");
printf("\n3 Print the calander of the whole year");
printf("\n4 Exit\n");
scanf("%d",&option);

switch(option)
{
case 1:
while(1)
{
printf("\nPlease input the year,month and day(XXXX,XX,XX):");
scanf("%d,%d,%d,%c",&year,&month,&day);
da=DaySearch(year,month,day);
printf("\n%d-%d-%d is %s,do you want to continue?(Y/N)",year,month,day,week[da]);
fflush(stdin);
scanf("%c",&ch);
if(ch=='N'||ch=='n')
break;
}
break;
case 2:
while(1)
{
printf("\nPlease input the year which needs searched?(XXXX)");
scanf("%d",&year);
if(IsLeapYear(year))
printf("\n%d is Leap year,do you want to continue?(Y/N)",year);
else
printf("\n%d is not Leap year,do you want to continue(Y/N)?",year);
fflush(stdin);
scanf("%c",&ch);
if(ch=='N'||ch=='n')
break;
}
break;
case 3:
while(1)
{
printf("\nPlease input the year which needs printed(XXXX)");
scanf("%d",&year);
PrintAllYear(year);
printf("\nDo you want to continue to print(Y/N)?");
fflush(stdin);
scanf("%c",&ch);
if(ch=='N'||ch=='n')
break;
}
break;
case 4:
fflush(stdin);
printf("Are you sure?(Y/N)");
scanf("%c",&ch);
if(ch=='Y'||ch=='y')
exit(0);
break;
default:
printf("\nError:Sorry,there is no this service now!\n");
break;
}

}

return 0;
}

❺ 用資料庫做一個小型設備管理系統

課題1. 《設備管理信息系統》包括的實體類型有：n 固定資產（資產編號，資產名稱，型號規格，計量單位，價值，製造廠，出廠日期）n 部門（部門編號，部門名稱，負責人）n 折舊單（折舊單編號，年折舊率，年折舊額，開始使用日期，全部使用年限，已使用年數，尚可使用年限）n 大修理單（修理單編號，修理日期，修理時限，修理費用，經手人）n 內部轉移單（轉移單編號，轉移日期，轉出部門，轉入部門），即該設備從一個部門調撥到另一個部門n 報廢單（報廢單編號，報廢日期，經手人），指該設備已經到了使用年限，扔到廢品倉庫里n 清理單（清理單編號，資產原值，累計折舊金額，清理金額），從廢品倉庫里清理掉。n 《設備管理信息系統》包括的具體操作：n 自行補充實體之間的聯系n 輸入數據，每個表不少於10行數據，數據必須是有意義的n 實現設備報廢過程n 實現設備的內部轉移n 統計固定資產的已進行的大修費用n 列出兩個基本表的插入、更新和刪除記錄的操作（各舉1例）n 自行補充8至10個SQL操作，如信息分院的固定資產價值，列出所有的「計算機」設備清單等等

❻ 設計一個資料庫系統要怎麼設計

資料庫系統只有一種語言,這種語言叫SQL.
你可以使用PowerDesigner來進行資料庫PDM和CDM設計,支持當前各種主流的資料庫數據格式.

❼ 小型資料庫管理系統的設計

小規模的資料庫關系系統

❽ 如何用prontpage和SQL設計一個小型的資料庫系統(學生綜合測評排名系統)

首先在SQL中利用企業管理器或向導建立一個資料庫，命名為學生管理系統， 啟動SQL Sever服務，運行企業管理器，單擊要創建資料庫的伺服器左邊的加號圖標，展開樹形目錄，在「資料庫」節點上右擊滑鼠，在彈出的快捷菜單中選則「新建資料庫」命令，然後按照提示一步步建立資料庫，不再詳細敘述。 假設學生管理系統下有三個表，分別為學生表、課程表、修課表，表的結構