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

《数据库的设计实现和管理健身房数据库系统》由会员分享，可在线阅读，更多相关《数据库的设计实现和管理健身房数据库系统（14页珍藏版）》请在装配图网上搜索。

1、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, 2009ABSTRACTDatabase Design, Implementation, and Ma

2、nagement 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

3、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 in

4、formation 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 satisfac

5、tory 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 t

6、he 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 tak

7、en 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世

8、纪以来，很多企业开始利用数据库对与企业运营相关的各项数据进行高效的操作和管理。一个合适的数据库管理系统可以提供一系列功能帮助企业进行数据管理。建立数据库的过程是一个数据库应用周期，其特点是耗时较长，并且需要大量的时间、精力和资源来完成。一个优秀的数据库管理员应该不断完善企业的数据库系统，以满足公司不断增长的数据处理需求。另外，随着科技的不断更替，数据库系统应及时融入新的软件应用程序，使其可以提供更多高端的服务。利用一个结构清晰的数据库应用周期可以使开发出的数据库系统提供稳定而高效的服务。本文的研究重点为数据库应用周期。全文通过利用周期内各个阶段可以使用的技术和操作设计并实现了一个小型健身房数据

9、库管理系统。本文前两部分介绍了和数据库环境相关的基本概念；后面的四个章节分别研究了数据库应用周期中最核心的阶段需求分析、设计、实现、和管理并介绍了每一阶段的目的、步骤、以及所使用的技术。最后提出了一个合理的数据库备份和还原策略。【关键词】数据库管理系统；数据库应用周期；数据库设计；数据库备份与还原；健身房数据库系统；Microsoft SQL Server 2005Contents1. Introduction12. Database Management System22.1 Database and Database Management System22.2 Database Appli

10、cation 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.

11、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错误！未定义书

12、签。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 .

13、 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 .24F

14、igure 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 ty

15、pes .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 nowada

16、ys 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 de

17、fine 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

18、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

19、, 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 diff

20、erent 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

21、 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 str

22、ucturing 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 applicatio

23、n”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,

24、 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 prog

25、ram 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 existi

26、ng system. References1. Best Practices for Backup and Recovery in SQL Server 2005 (n.d.). Retrieved February 20, 2009 from p.1 2. Best Practices for Backup and Recovery in SQL Server 2005 (n.d.). Retrieved February 20, 2009 from p.33. Best Practices for Backup and Recovery in SQL Server 2005 (n.d.).

27、 Retrieved February 20, 2009 from p.4 4. Best Practices for Backup and Recovery in SQL Server 2005 (n.d.). Retrieved February 20, 2009 from 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 Feb

28、ruary 23, 2009 from http:/www.progressivetech.org/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-pla

29、nning.htm 9. Database Planning Workbook (n.d.). Retrieved February 15, 2009, from : http:/www.techsoup.org/binaries/files/DatabasePlanningWorkbook.pdf 10. Database Testing: How to Regression Test a Relational Database (n.d.), retrieved February 17,2009, from http:/www.agiledata.org/essays/databaseTe

30、sting.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 : 13. Gilmo

31、re, An Introduction to Database Normalization (November 27, 2000). Retrieved February 15, 2009, from 14. John Archibald, Our Approach toDatabaseDesign and Development - A road map to better information management. Retrieved February 23, 2009 from S:15. Microsoft SQL Server, Retrieved February 13, 20

32、09, from Wikipedia: http:/en.wikipedia.org/wiki/SQL_Server_200516. Primary Key (n.d.). Retrieved March 15 from TechTarget: 17. Relational Model (n.d.). Retrieved February 13, 2009, from Wikipedia: http:/en.wikipedia.org/wiki/Relational_model18. Thomas Connolly & Carolyn Begg, Database Systems A Prac

33、tical 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 Electron

34、ics 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, Impleme

35、ntation, 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 Mo

36、deling Language (n.d.). Retrieved February 13, 2009, from Wikipedia: http:/en.wikipedia.org/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 i

37、nstructor, 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

38、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 problem

39、s. 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 s

40、upplier.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

41、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 trainer.- A member can purchase commodity.CardThe card a member ha

42、d/has.- One card is held by one member.PhysicalTestTests for members physical condition.- A physical test is for one member.SupplierSuppliers for the facilities- Supplier provides equipments.- Supplier provides commodity.- Supplier is contacted by staff.EquipmentThe equipments - Equipment is supplie

43、d by suppliers.CommodityThe commodity for purchase- Commodity can be purchased.- Commodity is supplied by supplier.ClassAll class- Class can registered by members.- Class takes room.- Class is taught by one instructor.RoomThe room for class- Each room is for at least one class.PurchaseThe commodity

44、a member purchased.- A member can have a purchase.- Each purchase is for one commodity.RegistrationThe registration of a member for a class.- Registration is issued by a member.- Each registration is for one class.2. Relationship TypesEntity nameMultiplicityEntityMultiplicityEntity NameStaff1.1Manag

45、es0.1DepartmentStaff1.1Belongs to1.1DepartmentStaff 1.1Has 1.*SalaryTrainer1.1Is a1.1StaffInstructor1.1Is a 1.1StaffStaff1.1Receives0.*MemberMember1.1Has 1.*CardMember1.1Has1.*PhysicalTestTrainer 1.1Trains1.*MemberClass1.1Takes 1.1RoomInstructor 1.1Teaches1.*ClassSupplier1.1Provides1.*EquipmentSupplier1.1Provides1.*CommodityStaff 1.1Contacts 0.*SupplierMember1.1Makes0.*RegistrationRegistration1.1Registers1.1ClassMember1.1Issues0.*PurchasePurchase1.1Contains1.1Commodity