Create a Bank data management system using Sql-server Ⅰ
Author statement:
Just started to write a blog, inevitably some deficiencies, and then I first involved in software development this industry, is a letter to the small white, the article will certainly appear some wrong place, hope to find the wrong friends can timely point out, insufficient places also please all the way the great God to teach, so I reference and study, thank you.
First of all, to create a complete data management system, not overnight, must be a step-by-step, continuous improvement, the end can achieve their desired results, so the rabbit here is also 1.1 points step to do.
- Create database, database properties are used here by default (this lazy approach is not recommended)
1 -- Create a database using the default configuration 2 Create Database a Bank customer management system 3 Go
- Create a table of four, respectively: Customer information, bank card information, business type, transaction history
1 Usea Bank customer management system2 3 4 --Create a Business type table that contains the business number, business name, and business description5 Create Tabletype of Business6 (7Business Numberint Identity(1,1)Primary Key,8 9Business Namevarchar( -) not NULL Unique,Ten OneBusiness descriptionvarchar( -) A ) - - the --Create customer information sheet with customer number, name, residence, mobile number and ID number - CREATE TABLECustomer Information - ( -Customer numberINT IDENTITY(101,1)PRIMARY KEY, + -NameVARCHAR( -) not NULL, + APlace of residenceVARCHAR( -), at -Mobile phone numberCHAR( One)UNIQUE not NULL - Check(Mobile phone number like '1[358][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'), - -ID numberCHAR( -)UNIQUE not NULL - Check( Left(ID number, -) in 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][0-9][0-9][0-9][0-9][0-9]' - and( Right(ID number,1) like '[0-9]' or Right(ID number,1) like 'X') to ) + - the --Create a bank card information sheet * CREATE TABLEBank card Information $ (Panax NotoginsengCardCHAR( +)PRIMARY KEY CHECK(Card number like '6223 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9]'), - thePasswordBIGINT CHECK(Password>99999 andPassword<=999999) + DEFAULT 111111 A not NULL, the +Deposit typeint not NULL, - $Balance Money CHECK(Balance>=Ten) not NULL, $ -Cardholder's card customerINT not NULL, - theRegistration dateDATETIME not NULL default(getdate()), - WuyiReporting lossChar(2)default('No')Check(Whether or not reporting='is a' orReporting loss='No') the ) - Wu - --Create a transaction table About Create TableTransaction History $ ( -Record numberint Identity(1,1)Primary Key, - -CardChar( +) not NULL, A +Trading Datedatetime not NULL, the -Transaction amount Money not NULL, $ theType of transactionChar(4) not NULL Check(Type of transaction='Income' orType of transaction='expenditure'), the theTrading Notesvarchar( -) the)
1 --Create a foreign key2 Alter TableBank card Information3 Add Foreign Key(Deposit type)ReferencesBusiness Type (business number)4 5 Alter TableBank card Information6 Add Foreign Key(Card holders)ReferencesCustomer information (customer number)7 8 Alter TableTransaction History9 Add Foreign Key(Card number)ReferencesBank card Information (card number)
Here, the first step in creating a bank database system is done, and then you have to build some triggers and stored procedures, and insert some test data, for details, please follow the rabbit's next blog, thank you.
Create a Bank data management system using Sql-server Ⅰ