Lab1

Lab1 SQL Server EnvironmentAIM1.Familiar with SQL Server Environment2.Familiar with SQL Server Components.Basic Knowledge1.General Introduction to SQL Server, including SQL tools, system databases and system tables. SeePPT for more info.2.Primary Key/Foreign KeyTo Do1、Start/close the server with Service Manager and with Enterprise Manager.2、Create new database with Enterprise Manager3、Create new table with Enterprise Manager4、Create new relationship with Enterprise Manager.Please follow the steps for Lab11.Start/close the server with Service Manager1.1Click start -Program－Microsoft SQL Server－Service Manager1.2Click Start/Continue2.Start/close the server with Enterprise Manager2.1 Click Start->Programs->Microsoft SQL Server->Enterprise Manager.2.2 Right clicking the SQL server’s icon in the tree view it will pop up a menu with options thatallows you to start/stop the server.Server paused.Server running, you are currently connected to the server.Server running, you are not currently connected to the server.Server Stopped.3.Creating a database with Microsoft SQL server 2000System Database Introduction PartWhen you install SQL Server several databases are created automatically.-Northwind and pubs are sample user databases and can be used as examples or you can delete them safely.- master, model, msdb, and tempdb are used by the system and should not be removed.Create new database3.1Start by expanding the tree view until you see the “Databases” folder of the server.3.2Right Click the Databases folder and select “New Database”3.3In the Name textbox enter a table name “firstDB”3.4Click the “Data Files” tab and leave it as the defaults.3.5Click the “Transaction Log” tab and leave it as the defaults.3.6The new database now appears in the Database folder.4.Creating a table4.1Start by expanding the server and database you would like to add a table.4.2Select “Table” and then click “*”.4.3The New Table window appears and look like the following:Colum Name is used to enter the name of the column.Data Type sets the type of data the column will contain.Length specifies the length of the columnAllow Nulls determines whether a column can be left blankDescription is a space for comment about what the column is used for.Default Value is the value that will be entered if no other value is specified when a row is added to the table.Identity is similar to the AutoNumber option in MS Access:“Yes”, SQL Server will automatically generate a new number for each row added to the table in this column.Identity Seed specifies what number SQL Server should start at.Identity Increment specifies the number that should be added to the Seed value to determine successiveidentity number.Ex. A table that holds Employee Info may look like this:4.4To set the primary key, select the appropriate row and then click the “Set primary key” icon inthe toolbar.4.5Click the save icon on the toolbar and enter a name for this table, then click OK.4.6After refreshing the Table list(F5) the table we just created appears in the SQL Server EnterpriseManager:4.7You can always go back and edit a table by right clicking it in Enterprise Manger and selecting“Design Table”:5.Creating Relationship5.1Click “relationship design” and right click create new “relationship design”, then follow theguide.5.2 Save the diagram.5.3 After you have done the above operations as descripted, you will get the following relation:To enforce referential integrity between tables it is possible to setup a foreign key Constraint, or “relationship”, in the SQL Server.This database diagram above shows the two tables(Parent and Child) – ParentID in the Child table is a foreign key used to reference the Child’s parent.5.4To setup a relationship and enforce referential integrity:5.4.1 Open one of the tables in the table designer that is going to be part of therelationship(Parent in this example) and click “Manage relationships…” in the toolbar , then the following screen appears:5.4.2 Click New. Set Parent as the “Primary key table” and Child as the “Foreign key table” Under Parent select ID in the first field. Under Child select ParentID. The screen looks like this:Note: Explanation（Translation）创建中检查现存数据：Check existing data on creation对复制强制关系：Enforce relationship for replication对INSERT and UPDATE 强制关系：Enforce relationship for INSERT and UPDATE级联更新相关的字段：Cascade Update Related Fields级联删除相关的记录：Cascade Delete related Records5.4.3Close and save the table.5.4.4The database diagram now shows a one-to-many relation with integrity constrains.6.Discussions：。