DBADesign Class v1.docx

DBADesign Class v1.docx

《DBADesign Class v1.docx》由会员分享，可在线阅读，更多相关《DBADesign Class v1.docx（13页珍藏版）》请在冰豆网上搜索。

DBADesignClassv1

DocumentName:

DatabaseDesignDocument

ProjectName:

OnlineLibraryManagementSystem

Group:

XXX

Author:

XXXXX

STU_NUM:

XXXXXX

Date:

21-11-2011

ModifiedBy:

XXXXX

STU_NUM:

XXXXx

Date:

22-11-2014

Version:

1.0

1.Introduction

1.1Objective

Thisdocumentprovidesguidancefordevelopers,designsthedatabaseoverviewcorrespondingthesystem,andhelpsdeveloperstodeveloptheOnlineLibraryManagementSystem’srelationaldatabase.

1.2Background

a.Softwarename：

OnlineLibraryManagementSystem

b.Databasename：

OLMS_DB

c.DatabasemanagementSystem：

MySQL5.5

d.Usersusingthesystem:

thelibrarymanagersandusersforbusinessandindividual

1.3Decisions

Basedonuserdemandforintegrated,inductionandabstract,forminganindependentofthespecificDBMSconceptmodel,weuseE-Rdiagramrepresentation.

1.4Terms

a.OLMS:

OnlineLibraryManagementSystem

a.

b.ER：

entity-relationalmodel

1.5supportsoftware

a.Supportedoperatingsystem：

Windows2000/WindowsNT/WindowsXP/WindowsVista/Windows7

b.Developmenttools：

MySQL5.5+SQLyog+MicrosoftVisio

2.ConceptDesign

2.1TheglobalERmodel

2.2ThelocalERmodel

2.2.1localERmodel1

Usercancheckpersonalinformation,butcanonlymodifythepassword.

2.2.2localERmodel2

TheAdministratorisdividedintogeneralmanagerandsupermanager.Supermanagermanagergeneralmanager.Generalmanagercanonlymodifyhisownpassword,whilesupermanagercanmodifyalltheinformationwhichincludeallofhisowninformation,alluserinformationandallgeneralmanagerinformation.Ofcourse,theyhavetherighttochecktheirinformation.

2.2.3localERmodel3

Administratormanageuserinformation.Notethat,thegeneralmanagerofallrightjustresetuserpasswords,markwhetherauserisinviolationofstate.

2.2.4localERmodel4

TableBookInfmeansakindofbook.ItsprimarykeyisISBN.TablebookInStockmeanseachbookinthelibrarystacks.ItsprimarykeyisMageticbarcode（bar_code）.Theirrelationshipisone-to-many.Thatis,therearemanyofthesamekindbook.

2.2.5localERmodel5

Generalmanagerentrybookinformation.Thisinformationincludestobeontheshelfofbooksinformationandfromtheshelfoffthebooksinformation.Finally,thesuperadministratorofthisinformationisconfirmed,informationcanbedepositedinthelibrary.Notably,thegeneralmanageradministratorinputoninformation,tohavebeeninthestacksofbookstosearchwhetherthereisaduplicateISBN.Ifno:

twostepstowalk,

（1）thefirstentryofnewbooksinformation.

（2）thenewbarcodebookseditor,editentrybarcodeinformation.Ifthereare:

directlytodepositbookeditingcode,editedaftertheentrybarcodeinformation.

2.2.6localERmodel6

UserbasedonuserTypetablecanreservebooks,borrowbooksandrenewbooks.Theusertypehas3kindtypes,namely,undergraduate,graduateanddoctoralstudents,schoolteachersandadministrators.TheuserTypetableisusedtoexpressthedifferentcategoriesinthetotalnumberofbooks,canfirstlenddays,canrenewfordays,theoddsofbookrecordsandsoon.

2.2.7localERmodel7

Userreturnbooks.

2.2.8localERmodel8

DBMSeverydaycheckstheuserhasnoduethebookalso.Ifauserexceedstheshouldbereturnedtodatewithoutthebook,thentheuserismarkedfortheviolationofstate,atthesametime,recordingthenowillegalinformationusers.

2.2.9localERmodel

Usersreturnedthebooksshouldbeduealsodidnothavethebooks,theadministratorwillprocesstheusercorrespondingtoviolateregulationstherecord,atthesametime,thecontentsofthebooksinthelibraryfromstatetostate.Whentheuserreturnedallthematurityalsodidnothavethebooksaftertheliftingoftheuser'sillegalstatus,theadministrator.

2.2.10localERmodel10

Administratorreleaseinformation.

3.LogicDesign

3.1Overviewofrelationaltables

AccordingtothedesignofERup,wederivetherelationaltableasshownbelow.

3.1.1UserInfTable

UserInftableisusedtorecorduserinformation.Attributeuser_stateisusedtomarkwhethertheuserisnowillegalstate.Attributeborrowed_amountrecordsthenumberthattheuserhasborrowedbooksnow.

FieldName

FieldType

Constraints

Description

user_id

varchar（20）

notnull

Theuser_idis18characters.

user_password

char（6）

notnull

Thepasswordis6characters.

user_type

char

（2）

notnull

Forexample：

A、B、C

user_status

boolean

notnull,defaultvulueisfalse

Onlycanbe（Yorno）.

borrowed_amount

char

（2）

null

Theamountofbookstheuserisborrowing.

user_name

varchar（20）

notnull

user_sex

char

（2）

notnull

birthday

varchar（20）

notnull

telephone

varchar（20）

null

phonecall

varchar（20）

null

E-mail

varchar（30）

notnull

address

varchar（50）

null

degree

varchar（10）

null

comment

text

null

3.1.2UserTable

UsertablesimplyrecordstheuserIDandthepasswordoftheuser.

FieldName

FieldType

Constraints

Description

user_id

varchar（20）

notnull

Theuser_idis18characters.

user_password

char（6）

notnull

Thepasswordis6characters.

3.1.3UserTypeTable

UserTypetableisusedtoexpressthedifferentcategoriesinthetotalnumberofbooks,canfirstlenddays,canrenewfordays,theoddsofbookrecordsandsoon.

FieldName

FieldType

Constraints

Description

user_type

char

（2）

notnull

forexample：

A、B、C

book_amount

char

（2）

notnull

Thebiggestnotmorethan100books,Thetotalnumberofbooksout

first_day_number

char

（2）

notnull

Thebiggestnomorethan100days,Thefirsttimethenumberofdaysout

continue_day_number

char

（2）

notnull

Thebiggestnomorethan100days,Cancontinuetoborrowthenumberofdays

odds

numeric（4,2）

notnull

3.1.4BookInfTable

BookInftablemeansakindofbook.ItsprimarykeyisISBN.

FieldName

FieldType

Constraints

Description

ISBN

char（13）

notnull

Onlygetthenumber.

book_name

varchar（20）

notnull

author

varchar（20）

notnull

publish_company

varchar（20）

notnull

publish_date

date

notnull

price

numeric（6,2）

notnull

book_type

varchar（20）

notnull

book_introduction

text

notnull

check_out_number

int（4）

defaultvalueis0

Thenumberoftimesrecentlycheckedout

look_up_number

int（5）

defaultvalueis0

Thenumberoftimesrecentlylookedup

3.1.5BooksInStockTable

BooksInStocktablemeanseachbookinthelibrarystacks.ItsprimarykeyisMageticbarcode（bar_code）.

FieldName

FieldType

Constraints

Description

bar_code

varchar（20）

notnull

Everybookhastheonlynumber.

ISBN

char（13）

notnull

Onlygetthenumber.

book_status

varchar（20）

notnull

canbeborrewedinthelibraryorcannotbeborrowedinthelibarayorhasbeenborrewedout

3.1.6AdminInfTable

AdminInftableisusedtorecordadministratorinformation.

FieldName

FieldType

Constraints

adminId

varchar（10）

notnull

adminPswd

char（6）

notnull

adminType

char（10）

defaultvalueisordinary

adminName

varchar（20）

notnull

sex

char

（2）

notnull

telephone

varchar（20）

null

Email

varchar（30）

notnull

comment

text

null

3.1.7ReserveRecordTable

ReserveRecordtableisusedtorecordtheuserappointmentbooksrecords.

FieldName

FieldType

Constraints

Description

bar_code

varchar（20）

notnull

Everybookhastheonlynumber.

user_id

varchar（20）

notnull

reserve_date

date

notnull

3.1.8BorrowRecordTable

BorrowRecordtableisusedtorecordtheinformationaboutthebookyouborrowed（Hasbeenreturned）.

FieldName

FieldType

Constraints

bar_code

varchar（20）

notnull

user_id

varchar（20）

notnull

borrow_date

date

notnull

actual_return_date

date

notnull

3.1.9BorrowInfNowTable

BorrowInfNowtableisusedtorecordtheusernowlendbooksinformation.

FieldName

FieldType

Constraints

Description

bar_code

varchar（20）

notnull

user_id

varchar（20）

notnull

borrow_date

date

notnull

should_return_date

date

notnull

renew

boolean

defaultvalueistrue

Itshowswhetherthebookhasberenewed.

due_status

boolean

defaultvalueisfalse

ITshowswhetherthebookisoverdue.

3.1.10ReaderIllegalStateRecordNowTable

ReaderIllegalStateRecordNowtableisusedtorecordthecurrentstateofillegalinformationusers.

FieldName

FieldType

Constraints

Description

user_id

varchar（20）

notnull

Readersnumberiseighteen

bar_code

varchar（20）

notnull

Eachbookmarks

illegal_date

date

notnull

Bookshallbereturnedtodate

money_number

numeric（5,0）

notnull

Befinedmoneyquantity

3.1.11ReaderIllegalStateRecordDealTable

ReaderIllegalStateRecordDealtableisusedtorecordtheuserinviolationoftherelevanthistoricalinformation.

FieldName

FieldType

Constraints

Description

user_id

varchar（20）

notnull

Readersnumberiseighteen

bar_code

varchar（20）

notnull

Eachbookmarks

handlingtime

date

notnull

money_number

numeric（5,0）

notnull

3.1.12TemporayUpBooksTable

TemporayUpBookstableisusedtotemporarilystoretoputthebooksontheshelvesofinformation.

FieldName

FieldType

Constraints

ISBN

char（13）

notnull

book_name

varchar（20）

notnull

author

varchar（20）

notnull

publish_company

varchar（20）

notnull

publish_date

date

notnull

price

numeric（6,2）

notnull

book_type

varchar（20）

notnull

book_introduction

text

notnull

3.1.13TemporayUpStockBooksTable

TemporayUpStockBookstableisusedtotemporarilystoremustbeputontheshelvesofeverybookinformation.

FieldName

FieldType

Constraints

bar_code

varchar（20）

notnull

ISBN

char（13）

notnull

3.1.14TemporayDownBooksT