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 I am here is 1.1 points step to do.
- Create database, database properties are used here by default (this lazy approach is not recommended)
1--Create DATABASE, use default configuration 2 Create a database bank Customer management System 3 Go
- Create a table of four, respectively: Customer information, bank card information, business type, transaction history
1 use a bank Customer management System 2 3 4--Create a business type table with business number, business name and business Description 5 CREATE TABLE business type 6 (7 Business number int identity () primary key, 8 9 Business Name Call varchar (s) NOT NULL unique,10 11 business description varchar (100) 12) 13 14 15--Create customer information table with customer number, name, residence, mobile number and ID number-CREATE TABLE customer information 17 (18 customer number INT IDENTITY (101,1) PRIMARY KEY, 19 20 name varchar (+) not null,21 22 live in varchar (50), 23 24 Mobile number CHAR (11) Unique not NULL check (mobile number like ' 1[358][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] '), 26 27 ID number CHAR (+) UNIQUE NO T NULL28 Check (left (ID number, s) "[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 card number, 1) like ' X ') 31) 32 33 34--Create a bank card information table "Creating table" bank card information 36 (37 card number CHAR PRIMARY KEY Check (card number like ' 6223 2017 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9] '), 38 39 password BIGINT CHECK (password & gt;99999 and password <=999999) + DEFAULT 11111141 not null,42 43 deposit type int not null,44 45 amount money CHECK (balance ; =10) Not null,46 47 card customer INT Not null,48 49 Registration date datetime NOT NULL default (GETDATE ()), 50 51 loss of report char (2) Default (' No ') check (whether to report loss = ' yes ' or whether to report loss = ' no ') 52) 5 3 54 55--Create a transaction table the record of the creation table transaction 57 (58 record number int identity () primary key,59 60 card number char () not null,61 62 transaction period Datet IME not null,63 64 transaction amount Money not null,65 66 transaction type char (4) NOT NULL check (transaction type = ' revenue ' or transaction type = ' expense '), 67 68 Trade Memo VARCHAR (50) 69 )
1--Establish a FOREIGN Key 2 ALTER TABLE bank card information 3 Add foreign key (deposit type) references business type (business number) 4 5 ALTER TABLE bank card information 6 add foreign key (card holder customer) refer Ences Customer information (customer number) 7 8 ALTER TABLE Transaction 9 add foreign key (card number) references bank card information (card number)
Here, the first step to create a bank database system is to complete, next to set up a number of triggers and stored procedures, as well as inserting some test data, please pay attention to my next blog, thank you.
(reprint) Use Sql-server to create a bank data management system Ⅰ