Relational Model and Relational Algebra

Vickig

Description

• cours magistral

Relational Model and Relational Algebra CMPSCI 445 – Database Systems Fall 2008 Some slide content courtesy of Zack Ives, Ramakrishnan & Gehrke, Dan Suciu, Ullman & Widom

• relational database
• details of physical storage
• nested tables
• declarative query language
• theory of normalization guides
• specifies name of relation
• tables students
• name

Sujets

• Cours magistral
• Relational
• Physical
• Normalization
• Table
• Nested
• Declarative
• Relation
• Language

Informations

• Publié par: udwi
• Nombre de lectures: 18
• Langue: English
• Poids de l'ouvrage: 2 Mo

Extrait

Lab Answer Key for Module 1: Creating
Databases and Database Files

Table of Contents
Lab 1: Creating Databases and Database Files 1
Exercise 1: Creating a Database 1 se 2: Creating Schemas 4
Exercise 3: Creating a Database Snapshot 7

Information in this document, including URL and other Internet Web site references, is subject to change
without notice. Unless otherwise noted, the example companies, organizations, products, domain names, e-
mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any
real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or
should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting
the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval
system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or
otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
The names of manufacturers, products, or URLs are provided for informational purposes only and Microsoft
makes no representations and warranties, either expressed, implied, or statutory, regarding these
manufacturers or the use of the products with any Microsoft technologies. The inclusion of a manufacturer or
product does not imply endorsement of Microsoft of the manufacturer or product. Links are provided to third
party sites. Such sites are not under the control of Microsoft and Microsoft is not responsible for the contents of
any linked site or any link contained in a linked site, or any changes or updates to such sites. Microsoft is not
responsible for webcasting or any other form of transmission received from any linked site. Microsoft is
providing these links to you only as a convenience, and the inclusion of any link does not imply endorsement of
Microsoft of the site or the products contained therein.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights
covering subject matter in this document. Except as expressly provided in any written license agreement from
Microsoft, the furnishing of this document does not give you any license to these patents, trademarks,
copyrights, or other intellectual property.
© 2007 Microsoft Corporation. All rights reserved.
Microsoft, BizTalk, Internet Explorer, Jscript, MSDN, Outlook, PowerPoint, SQL Server, Visual Basic, Visual C#,
Visual C++, Visual FoxPro, Visual Studio, Windows and Windows Server are either registered trademarks or
trademarks of Microsoft Corporation in the United States and/or other countries.
The names of actual companies and products mentioned herein may be the trademarks of their respective
owners.
Version 1.0
f
f
Lab Answer Key for Module 1: Creating Databases and Database Files 1
Lab 1: Creating Databases and Database Files
Exercise 1: Creating a Database
Task 1: Create a SQL Server Management Studio Scripts project
1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then
click SQL Server Management Studio.
2. In the Connect to Server dialog box, specify the values in the following table, and
then click Connect.
Property Value
Server type Database Engine
Server name MIAMI
Authentication Windows Authentication
3. On the File menu, point to New, and then click Project.
4. In the New Project dialog box, specify the values in the following table, and then
click OK.
Property Value or action
Name AW_IT_Database
Location D:\Labfiles\Starter
Create directory for solution Clear the check box

5. On the Project menu, click New Query.
6. In the Connect to Database Engine dialog box, specify the values in the following
table, and then click Connect.
Property Value
Server name MIAMI
Authentication Windows Authentication
7. If Solution Explorer is not visible, on the View menu, click Solution Explorer.
8. In Solution Explorer, right-click SQLQuery1.sql, click Rename, type
CreateAW_IT_Assets.sql, and then press ENTER.

Task 2: Create the new database with appropriate options and
filegroups
1. If Object Explorer is not visible, on the View menu, click Object Explorer.
2 Lab Answer Key for Module 1: Creating Databases and Database Files
2. In Object Explorer, right-click Databases, and then click New Database.
3. In the New Database dialog box, in the Database name box, type AW_IT_Assets.
4. For the first database file, modify the details to match those in the following table.
Property Value
Logical Name AW_IT_Assets_Data1
File Type Data
Filegroup PRIMARY
Initial Size (MB) 20
5. In the Autogrowth column of the AW_IT_Assets_Data1 entry, click the ellipsis
button (…).
6. In the Change Autogrowth for AW_IT_Assets_Data1 dialog box, clear the Enable
Autogrowth check box, and then click OK.
7. Click Add to create a new data file.
8. For the new database file, type in the details in the following table.
Property Value
Logical Name AW_IT_Assets_Data2
File Type Data
Initial Size (MB) 20
9. In the Autogrowth column of the AW_IT_Assets_Data2 entry, click the ellipsis
button (…).
10. In the Change Autogrowth for AW_IT_Assets_Data2 dialog box, clear the Enable
Autogrowth check box, and then click OK.
11. In the Filegroup column for the AW_IT_Assets_Data2 file, in the drop-down list,
click <new filegroup>.
12. In the New Filegroup for AW_IT_Assets dialog box, type in the details in the
following table, and then click OK.
Property Value or action
Name SECONDARY
Options – Default Select the check box
13. For the AW_IT_Assets_log entry, change the Initial Size setting to 5.
14. In the Autogrowth column of the AW_IT_Assets_log entry, click the ellipsis button
(…).
Lab Answer Key for Module 1: Creating Databases and Database Files 3
15. In the Change Autogrowth for AW_IT_Assets_log dialog box, clear the Enable
Autogrowth check box, and then click OK.
16. In the New Database dialog box, on the toolbar, click the Script drop-down arrow,
and then click Script Action to Clipboard.
17. When the scripting has completed successfully, click OK to create the new database.
18. Right-click in the CreateAW_IT_Assets.sql query window, and then click Paste.
19. On the File menu, click Save All.
20. Keep Microsoft® SQL Server™ Management Studio open. You will use it in the
next exercise.

f
f
4 Lab Answer Key for Module 1: Creating Databases and Database Files
Exercise 2: Creating Schemas
Task 1: Create the TechSupport schema
1. In SQL Server Management Studio, on the Project menu, click New Query.
2. In the Connect to Database Engine dialog box, specify the values in the following
table, and then click Connect.
Property Value
Server name MIAMI
Authentication Windows Authentication
3. In Solution Explorer, right-click SQLQuery1.sql, click Rename, type
CreateAW_IT_Schemas.sql, and then press ENTER.
4. In Object Explorer, expand Databases, expand AW_IT_Assets, expand Security,
right-click Schemas, and then click New Schema.
5. In the Schema – New dialog box, in the Schema name box, type TechSupport but
do not click OK yet.
6. On the toolbar, click the Script drop-down arrow, and then click Script Action to
Clipboard.
7. When the scripting has completed successfully, click OK to create the schema.
8. Right-click in the CreateAW_IT_Schemas.sql query window, and then click Paste.
9. On the File menu, click Save All.

Task 2: Create a new table and login by using the provided script
1. On the Project menu, click Add Existing Item.
2. In the Add Existing Item – AW_IT_Database dialog box, browse to the
D:\Labfiles\Starter folder, click the Create_AW_IT_Table_and_User.sql query file,
and then click Add.
3. In the Connect to Database Engine dialog box, specify the values in the following
table, and then click Connect.
Property Value
Server name MIAMI
Authentication Windows Authentication
4. On the toolbar, click Execute to create the new Asset table and the login for
MIAMI\Katie.

f
f
Lab Answer Key for Module 1: Creating Databases and Database Files 5
Task 3: Verify that the user can select information from the Asset
table only by qualifying the table name with the schema
1. Click Start, click Run, type runas /noprofile /user:MIAMI\Katie cmd, and then
click OK.
2. When prompted for the user password, type Pa$$w0rd, and then press ENTER. This
creates a new Command Prompt window that is running as the MIAMI\Katie user
account.
3. At the command prompt, type sqlcmd -S MIAMI -E, and then press ENTER. This
starts the SQL Server 2005 command prompt, which allows you to execute
statements against a database.
4. At the SQL Server 2005 command prompt, type each line of code, and then press
ENTER. (sqlcmd will insert line numbers automatically.)
USE AW_IT_Assets
SELECT * FROM Asset
GO
An error message is displayed within the Command Prompt window because the dbo
schema does not contain the Asset tabl