Global Bank: Updating an Existing Database to Support New Services
Visual Studio Team System provides fully integrated support for database development in a team environment, including source control features and process guidance. This is the second in a series of articles that will describe a common development scenario at the fictitious Global Bank, and show how you can use Visual Studio Team System 2008 for your next project. If you're new to the series, you can meet the Global Bank team by reading the introductory article Global Bank: A Scenario for Visual Studio Team System 2008.
Introducing the Database Developer
Alice is considered the SQL expert on the Global Bank development team because of her previous job as a SQL database administrator. When she was hired three months ago, she took over the development and maintenance the SQL-backend for the Global Bank Web site. Alice also performs other development tasks, so she has installed both the Team System 2008 Database and Team System 2008 Development editions.
Alice checks the team project database and notices that Ken has assigned a work item to her to implement the database support that he designed for the new CD rate service. For details about Ken’s system design, see the article Global Bank: Adding New Services to an Existing Web Site.
Alice reviews the work item assigned to her in the Global Bank database. She also visits the Project Portal Web site to view the system diagram that Ken created. Because Alice is new to Visual Studio Team System Database Edition, she also looks on the Project Portal Web site for process guidance for database developers who use MSF for Agile Software Development. Specifically, she wants to learn more about database unit testing, because Ken has requested that Alice create a unit test to verify her work prior to check-in.
Figure 1: Database Developer Process Guidance
.png "Process Guidance for Database Developers")
Adding the New Table
When she is through looking at the process guidance, Alice checks out the database project GlobalBankDB, which contains an offline representation of her database schema. She adds a new table, named CertificateofDeposit, to store the CD purchase information. This table contains 12 columns and it tracks information such as the date when the CD will mature and early withdrawal penalty amounts. Alice then creates a new stored procedure to access CD information by customer. She names the stored procedure GetCustomerCDs.
To verify her work, Alice creates a unit test, named GlobalBankUnitTests. Using SQL, she adds a row count test condition for the new table. Alice then uses the data generator feature to create test data on her local server for the unit test to run against. She runs the unit test and verifies that it passes. Alice then changes the test data and reruns the unit test to verify that it fails.
Refactoring the Table Name
Before checking in her work, Alice reviews the company guidelines for naming conventions. She realizes that she needs to rename the new table to ensure compliance. Alice switches to Schema view and uses the Refactor feature to change the table name to CertificatesofDeposit. After she makes this change, the Preview Changes dialog box shows her the impact that renaming the table will have on the rest of the project, including the stored procedure and unit test. After reviewing the information in the Preview Changes dialog box, she applies the changes.
Figure 2: Previewing Refactoring Changes
.png "Preview Refactoring Changes")
Alice then re-runs the unit test to make sure that nothing has broken because of the name change to the new table. After the unit test passes, she checks in the updated database and unit test projects. Alice updates the status of her work item to Fixed. She sends an e-mail to Martin, another developer assigned to this feature, letting him know that he can now begin coding.
Further Reading from MSDN Online
Team Foundation Process Guidance