CSSE333 Introduction to Databases – Lab Assignment Creating a Database I. Objective The objective of this lab is to learn how to create and manage databases using both a graphical and a textual interface. II. Required Materials SQL Server Management Studio must be installed on your computer. You should have a connection to the DYKNOW.CS database from a previous lab. III. Assignment Details Task 1–Creating a database using SQL Server Management Studio (1) Open SQL Server Management Studio and login (2) In the servers pane on the left, expand the dyknow.cs.rose-hulman.edu Server (3) Right click on the databases folder and select “New Database” to run the database wizard (4) The Create Database Wizard should now start a. The Database Name should be Lab3-1_<username>, where <username>is you usual username (i.e. if your username was smithj, then the database would be named Lab3-1_smithj ) b. Leave the database and transaction log file locations unchanged. (These paths refer to the file system of the DYKNOW server, not your local hard drive.) c. Make the following changes to the database and transaction log files: i. Database Initial Size: 5MB ii. Database File Growth: 10% iii. Database Maximum File Size: 25MB iv. Log Initial Size: 2MB v. Log File Growth: 15% vi. Log Maximum File Size: 20MB d. Finish creating your database (5) Check to see that your database was successfully created. In Server Management Studio check the properties of your new database. Right-click on your database and select files. The settings should be the same as when you were making the database.CSSE333 Introduction to Databases – Lab Assignment Task 2–Creating a Database using a SQL script You will now create a similar database as above but using a SQL script. Open Server Management Studio and login (1) Write a script to create a database named Lab3-2_<username>. For the correct syntax and examples search CREATE DATABASE in the Server Management Studio Help feature. The database should have the following parameters: i. Database Initial Size: 6MB ii. Database File Growth: 12% iii. Database Maximum File Size: 30MB iv. Log Initial Size: 3MB v. Log File Growth: 17% vi. Log Maximum File Size: 22MB (Note: the database filename has an .MDF extension and the log filename has an .LDF extension. See the examples in the Server Management Studio Help for path and filename conventions.) (2) Use the Server Management Studio help to find the variation of sp_help for looking up database properties. Execute that stored procedure to view the information on the Lab3-2_<username> database and to verify the database was created correctly. (3) Save the script that you used to create your database as lab3_<username>_create.sql Task 3–Database Growth Management In this section you will change the size of your Lab3-2_<username> database using a script. Helpful tip: Within a query editor window you can select part of a script and press F5 to execute just the selected commands. (1) Write and execute a statement that increases the maximum size of your transaction log file to 30MB. (See the related documentation for CREATE DATABASE.) (2) Write and execute a statement that increases the current size of your transaction log file to 10MB. (3) Write and execute a statement that increases the file growth of your transaction log file to 22%. (4) Execute the sp_help system stored procedure variant that you looked up above to view the properties on the Lab3-2_<username> database and verify the changes. (5) Save a script containing all these commands as lab3_<username>_modify.sql IV. Turn-in Instructions Archive your two scripts from tasks 2 and 3 (make them into a .zip or .rar files) and turn in that .zip or .rar file to the lab 3 ANGEL dropbox. Do not delete or modify the databases, as they will be checked too.CSSE333 Introduction to Databases – Lab Assignment V. Feedback Please complete the anonymous lab feedback survey on Angel for this lab. VI. Revision History Dec. 10, 2006: Minor edits by Curt Clifton Dec. 7, 2006: Minor edits by Chris Besier Dec. 6, 2006: Updated by Joshua Burbrink Dec. 6, 2005: Cleaned up by Curt Clifton Nov. 28, 2005: Updated by Curt Clifton, Steve Chenoweth, and Will Mathies Dec. 1, 2004: Created by Pat
View Full Document
Unlocking...