数据库的设计、实现和管理--健身房数据库系统.doc

database design,implementation, and management gymnasium database system数据库的设计、实现和管理-健身房数据库系统 students name ： zhu wei students id ： 2005141389 college ： overseas chinese college major ： information and management instructor ： joanne zhang march 30, 20096abstractdatabase design, implementation, and management gymnasium database systemzhu weias the world entering into a digital era, most of organizations use different kinds of database environment to achieve efficient management. a suitable database management system provides a set of tools for an organization to efficiently track and manage its data. the development of a database follows a database application lifecycle. it is a rather long process and requires a considerable amount of time, energy, and resources as well. a database administrator is obliged to strive for the perfection of the database to address the increasing amount of information to be dealt with. apart from that, since new technology keeps emerging, suitable software application should be integrated into the database environment to support more advanced features and services. following a well-structured database lifecycle will contribute to a reliable and satisfactory performance of the database system.database system and its management is a quite broad principle. this dissertation focuses on the process of a database application lifecycle. the whole process would be applied to establish a gymnasium management database system. the first two parts introduce the basic concepts concerning database environment. the next four parts present the most important stages in a database lifecycle its requirement identification, design, implementation, and management by showing what shall be done, the process of doing so, as well as the techniques which could be taken advantage to attain the best results. the last part introduces a backup-and-restore strategy for this database system.【key words】 database management system; database application lifecycle; database design; database backup-and-restore; gymnasium database system; microsoft sql server 2005【摘要】进入21世纪以来，很多企业开始利用数据库对与企业运营相关的各项数据进行高效的操作和管理。一个合适的数据库管理系统可以提供一系列功能帮助企业进行数据管理。建立数据库的过程是一个数据库应用周期，其特点是耗时较长，并且需要大量的时间、精力和资源来完成。一个优秀的数据库管理员应该不断完善企业的数据库系统，以满足公司不断增长的数据处理需求。另外，随着科技的不断更替，数据库系统应及时融入新的软件应用程序，使其可以提供更多高端的服务。利用一个结构清晰的数据库应用周期可以使开发出的数据库系统提供稳定而高效的服务。本文的研究重点为数据库应用周期。全文通过利用周期内各个阶段可以使用的技术和操作设计并实现了一个小型健身房数据库管理系统。本文前两部分介绍了和数据库环境相关的基本概念；后面的四个章节分别研究了数据库应用周期中最核心的阶段需求分析、设计、实现、和管理并介绍了每一阶段的目的、步骤、以及所使用的技术。最后提出了一个合理的数据库备份和还原策略。【关键词】数据库管理系统；数据库应用周期；数据库设计；数据库备份与还原；健身房数据库系统；microsoft sql server 2005contents1. introduction12. database management system22.1 database and database management system22.2 database application lifecycle错误！未定义书签。3. database requirement identification错误！未定义书签。3.1 the process of database requirement identification错误！未定义书签。3.2 requirement specification for the target gymnasium错误！未定义书签。4. database design错误！未定义书签。4.1 conceptual database design错误！未定义书签。4.1.1 relation modeling错误！未定义书签。4.1.2 entity-relationship modeling错误！未定义书签。4.1.3 user transactions validation错误！未定义书签。4.2 logical database design错误！未定义书签。4.2.1 validate relations using normalization错误！未定义书签。4.2.2 build and validate global logical data model错误！未定义书签。4.3 physical database design错误！未定义书签。5. database implementation错误！未定义书签。5.1 dbms selection错误！未定义书签。5.2 database implementation错误！未定义书签。5.3 database testing错误！未定义书签。6. database management错误！未定义书签。7. conclusion错误！未定义书签。references4acknowledgement6appendix7appendix a. data dictionary7appendix b. source code错误！未定义书签。list of figuresfigure 1. database application lifecycle . 8figure 2. data collection for gymnasium management database system . 12figure 3. data flow diagram for gymnasiums membership management .13figure 4. example of er diagram .17figure 5. microsoft sql server management studio .24figure 6. report generated using report services in sql server 2005 .24figure 7. example of creating database .25figure 8. example of creating table .25figure 9. example of creating trigger .25figure 10. example of creating procedure .25figure 11. the process of test first development .27figure 12. full&log backup strategy . 31list of tablestable 1. example of entity types .15table 2. example of relationship types .15table 3. example of attribute list 16table 4. example of transaction list 18table 5. example of physical data model 221. introductionfor any organization the collection and storage of data have always been a primary concern. large organizations nowadays use a database system to address this task. however, the earliest solution is a file-based system, and it is still in use in many small firms today. file-based system is a set of application programs that provides services for users to keep and process data. each program is independent and can define and manage its own data. when users need to look up some information, they go to the filing system and search from the first entry until the desired result is found. although the file-based system can be an effective approach sometime, it does suffer from two detrimental problems: isolation of data. when stored in a file-based system, data is kept within separate files in an isolated manner. it would be a rather difficult task when users want to access relevant information from two different files. duplication of data. because a file-based system uses a decentralized approach to keep data, it will inevitably introduce the duplication of data into the system. the ensued data redundancy is a waste of resources, taking up additional storage space and increasing unnecessary costs. more importantly, it can give rise to the loss of data integrity. unless the associated data stored in different files can be altered together when a change in one file occurs, these data will be eventually inconsistent and errors will occur.due to the problems of a file-based approach, a new technique emerged; that is database and database management system. 2. database management system2.1 database and database management systema database (db) is a structured collection of logically related data that is stored in a computer system. the organization of data follows a particular database model so that computer programs can select desired data quickly. a database model is not only an approach of structuring data; it also defines a set of operations that can be performed on raw data to achieve data manipulation. relational model is a most commonly used database model today. it was introduced in the 1970s as a way to “make database management systems more independent of any particular application”1. a relational database has three key terms: relations, attributes, and domains. a relation is a table with columns and rows. a named column of a relation is an attribute of the relation, and the domain of an attribute is the set of values this attribute is allowed to take. along with a database, a user needs a database management system to access information from the database. a database management system (dbms) is a set of software programs that controls the organization, storage, management, and retrieval of data in a database. the dbms accepts requests for data from the application program and instructs the operating system to transfer the appropriate data. as a most important advantage of dbms environment, whenever requirements of the system alter, it can be changed much easily. new categories of data can be added into the database without interference or disruption to the existing system. references1. best practices for backup and recovery in sql server 2005 (n.d.). retrieved february 20, 2009 from /downloads/global/solutions/public/white_papers/sql2005_backup_wp.pdf, p.1 2. best practices for backup and recovery in sql server 2005 (n.d.). retrieved february 20, 2009 from /downloads/global/solutions/public/white_papers/sql2005_backup_wp.pdf, p.33. best practices for backup and recovery in sql server 2005 (n.d.). retrieved february 20, 2009 from /downloads/global/solutions/public/white_papers/sql2005_backup_wp.pdf, p.4 4. best practices for backup and recovery in sql server 2005 (n.d.). retrieved february 20, 2009 from /downloads/global/solutions/public/white_papers/sql2005_backup_wp.pdf, p.4 5. codd, e.f. the relational model for database management: version 2. addison-wesley (1990), p. 2716. database assessment and planning (n.d). retrieved february 23, 2009 from /resources/pdf/database%20assessment%20and%20planning.pdf7. database design (n.d.). retrieved march 19, 2009 from http:/www.dcs.vein.hu/cir/cikkek/conceptual_ design.pdf 8. database planning (n.d.). retrieved february 15, 2009, from : .au/database-planning.htm 9. database planning workbook (n.d.). retrieved february 15, 2009, from : /binaries/files/databaseplanningworkbook.pdf 10. database testing: how to regression test a relational database (n.d.), retrieved february 17,2009, from /essays/databasetesting.html11. david avison & hanifa shah, 1997, information systems series - the information systems development life cycle: a first course in information systems, london; mcgraw-hill12. davor gornik, entity relationship modeling with uml (november 3, 2006). retrieved march 15, 2009 from : /developerworks/rational/library/content/03july/2500/2785/2785_uml.pdf13. gilmore, an introduction to database normalization (november 27, 2000). retrieved february 15, 2009, from /c/a/mysql/an-introduction-to-database-normalization14. john archibald, our approach todatabasedesign and development - a road map to better information management. retrieved february 23, 2009 from s:/products_services/database_solutions_lifecyle.php15. microsoft sql server, retrieved february 13, 2009, from wikipedia: /wiki/sql_server_200516. primary key (n.d.). retrieved march 15 from techtarget: /sdefinition/0,sid87_gci902812,00.html17. relational model (n.d.). retrieved february 13, 2009, from wikipedia: /wiki/relational_model18. thomas connolly & carolyn begg, database systems a practical approach to design, implementation, and management (third edition), beijing: publishing house of electronics industry, p.25319. thomas connolly & carolyn begg, database systems a practical approach to design, implementation, and management (third edition), beijing: publishing house of electronics industry, p.33320. thomas connolly & carolyn begg, database systems a practical approach to design, implementation, and management (third edition), beijing: publishing house of electronics industry, p.34221. thomas connolly & carolyn begg, database systems a practical approach to design, implementation, and management (third edition), beijing: publishing house of electronics industry, p.35322. thomas connolly & carolyn begg, database systems a practical approach to design, implementation, and management (third edition), beijing: publishing house of electronics industry, p.37023. unified modeling language (n.d.). retrieved february 13, 2009, from wikipedia: /wiki/unified_modeling_languageacknowledgementupon completing the writing of my undergraduate dissertation, i would like to give my sincere gratefulness to many people. first of all, i would like to thank my instructor, ms. joanne zhang for giving me guidance and instructions when i was writing this thesis. secondly, i would like to thank all authors of the articles from which i got inspirations or direct help. besides, i would like to thank all staff working in kangbei gymnasium for kindly providing me with not only detailed information but also encouragement needed for my dissertation. finally, i would like to thank my friends in college and workmates in north china power equipment and material central corp where i worked as intern for offering me possible solutions and support when i met problems. zhu weiappendixappendix a. data dictionary1. entity typesentitydescriptionoccurrencestaffall staff- each staff belongs to a department.- some staff manage departments.- each staff has salary.- some staff are trainers.- some staff are instructors.- some staff receive members.- some staff contact supplier.departmentall department- a department is managed by a staff.- department has staff working for.trainertrainer- a trainer is a staff. - a trainer trains at least one member.instructorinstructor- an instructor is a staff. - instructor teaches at least one class.salarysalary information- each salary belongs to one staffmemberperson who registers membership- a member is received by a staff.- a member has at least one card.- a member issues registration for a class.- a member has at least one physical test.- a member have a