Home > Articles

  • Print
  • + Share This

Course Preparation Notes

The following items will aid you in preparing for teaching the material in this chapter.

Class Time Range Estimate

The material in this chapter should take approximately five 50-minute class periods to cover, or a total of 4 hours and 10 minutes. Topics should break down approximately as shown in the following table:

Topic

Minutes Required

Creating and Altering Databases

90 minutes

Creating and Altering Database Objects

80 minutes

Multiple Server Implementations

30 minutes

Troubleshooting SQL Server Objects

50 minutes


Software/Web Requirements

To do the exercises for this chapter in the Training Guide, one of the following operating systems is required:

Microsoft Windows NT Server 4.0 with Service Pack 5 or later
Microsoft Windows NT Server 4.0 Enterprise Edition with Service Pack 5 or later
Microsoft Windows 2000 Server
Microsoft Windows 2000 Advanced Server
Microsoft Windows 2000 DataCenter Server
Microsoft Windows XP Professional
Microsoft Windows 98
Microsoft Windows Me

Microsoft SQL Server 2000 (any edition) must be installed and running to do the step-by-step exercises and the "Apply Your Knowledge" exercises at the end of the chapter.

No Internet connection is required for this chapter.

Hardware Requirements

To efficiently run Microsoft SQL Server 2000, the following system requirements are extant:

  • PC with an Intel or compatible Pentium 166MHz or higher processor

  • Minimum of 64MB of RAM (128MB or more recommended)

  • 95–270MB of disk drive space for SQL Server database; approximately 250MB for a typical installation

  • CD-ROM

  • VGA or higher resolution monitor

  • Microsoft Mouse or compatible pointing device

Key Terms

Cascading Actions—Activity that occurs in regard to update or delete activity when an existing Foreign Key value is changed or removed. A cascading update reflects changes in the Parent Table's key column to the corresponding Foreign Key column value in its Child table. A cascading delete ensures all subsidiary records in other tables are also deleted if a key value is deleted.

CHECK Constraints—Represent data design validation rules for maintaining data integrity. Data entered must follow the applied rule, which is constructed as a comparison expression, such as Vol > 10 or State in('MI','IL'). Check constraints may be applied to a column (Column Level Constraint) or to multiple columns (Table Level Constraint).

Clustered Index—An index is a structure that provides rapid access to the rows of a table based on the values of one or more columns. With a clustered index this rapid access is gained through the physical ordering of the data.

Collation Sequence—A set of rules governing the character sets that are used within a database, and the means by which characters are sorted and compared.

Constraint—Additional integrity checks associated with a column or table, that go beyond those implied by that column's datatype. There are five classes of constraints supported by SQL Server 2000: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY.

Defaults—Allow the Database Designer to specify a value that SQL Server inserts if no value is explicitly entered in a particular field.

Encryption—A mechanism that can be used to secure data, communications, procedures, and other sensitive information. This information is transformed into a non-readable form that must be decrypted to be viewed. While encryption is generally used in the transferring of sensitive data, SQL Server 2000 also uses encryption to disable the viewing of code from the syscomments table with the WITH ENCRYPTION command.

File groups—An administrative mechanism for grouping files within a database so they can be handled as a single unit.

FILLFACTOR—A server configuration option that specifies how full the server should make each page when it creates a new index using existing data. The FILLFACTOR is only used at the time when each index is created, and has no further affect on the database.

Foreign Keys—In a normalized database, relationships exist between different entities. A primary or unique key enforces a situation in which duplicate data is minimized. In order to provide multiple relationships with a particular primary or unique piece of data, a foreign key is implemented. This helps maintain domain integrity by ensuring that all rows of data in a child entity have a matching row of data in the parent entity.

Identity—A property or rule that can be assigned to, or turned on, for one column in a table to ensure that each value will increment (or decrement) by a specified amount from a specified starting point.

Indexes—Allow data to be organized in a way that allows optimal performance when the user retrieves data. In addition to database configuration, the implementation of indexes is a key method of optimizing performance on a database. Indexes do hamper performance when data is modified. For each new entry, an index entry must be built. For each update, an index key requires updating. See also Clustered Index, Non-clustered Index, and UNIQUE Index.

Integrity—Various means of ensuring the quality of data in a database. Entity integrity ensures that a table has a unique way to identify each record. Domain integrity is concerned with the validity of entries for a given column. Referential integrity enforces the defined relationships between two tables when records are added or deleted.

Log—A file that contains the information necessary to recover all transactions in a database.

Merge Replication—Replication Type where the Publishing server may receive and accept changes to its data from its subscribing servers. Changes in the data can be made in the Subscribing server while offline from the Publishing server, and then sent in batch to the Publisher.

Non-clustered Index—An index is a structure that provides rapid access to the rows of a table based on the values of one or more columns. With a non-clustered index, this rapid access is gained through an ordered list that has pointers to the physical location of the data.

Partitioned Views—Let you separate disjointed sets of data into separate tables on separate physical machines, while providing access to all of the tables simultaneously through the view. A view is considered partitioned if

  • The columns in each SELECT list are in the same order, of the same type, and have the same collation.

  • One column in each table has a CHECK constraint that is mutually exclusive with the corresponding column in the other tables. This column is called the partitioning column.

  • The partitioning column is not a computed column.

  • Indexes are not allowed on computed columns in the view.

  • The same underlying table does not appear more than once in the view.

Primary Key—A column or combination of columns whose values uniquely identify each row in a table, providing a means of relating the table to other tables in the database. Primary keys enforce entity integrity by not permitting any two rows in a table to have the same key value.

RAID—Redundant Array of Independent/Inexpensive Disks. A software or hardware technology where two or more disk drives can be configured in such a way as to provide larger volumes, increased performance, or data storage redundancy. Although there are six different RAID levels, 0 through 5, only levels 0, 1, and 5 are typically used with SQL Server. RAID level 0 is known as Data Striping, and allows for the highest performance, but has a high cost for failure because there is no redundancy. RAID level 1, or Disk Mirroring, has high redundancy, and reasonable performance. RAID level 5, or striping with parity, has the highest redundancy, but the slowest performance. A common installation technique for relational database management systems is to configure the database on a RAID 0 drive and then place the transaction log on a mirrored drive (RAID 1). In this manner you can get the best disk I/O performance for the database, and maintain data recoverability (assuming regular database backups). This configuration is generally referred to as RAID 10 (1 & 0), even though that is not a defined RAID level.

Recompile—An action that occurs to a stored procedure when an index is created or altered, or the server notes a change in the distribution of the data that has rendered its cached plan obsolete. Recompilation can be forced by using the sp_recompile system stored procedure, or by adding the WITH RECOMPILE option when creating or executing a stored procedure.

Roles—SQL Server has server, database, and application roles defined to identify the functions that different administrators have in regards to those objects. Server roles dictate the permissions that a given user in that role has to affect SQL Server. Database roles indicate the permissions granted or denied a user in relationship to a given database. An application role is a generalized role usually granted to an application or stored procedure that has assigned permissions to particular objects within a database.

Rules—Included in SQL Server mainly for backward compatibility. A rule is equivalent in functionality to a CHECK constraint, but must be defined separately, and then bound to a single column or user-defined datatype. Rules cannot access SQL Server functions that reference database objects.

Schema Binding—Indexed views require that you don't change the definition of any underlying object. Using the WITH SCHEMABINDING option ensures that tables used in an indexed view are not dropped or altered while participating in a view, unless the view is dropped, or changed to no longer require schema binding.

Snapshot Replication—Type of replication that distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. Can also be called "Read Only" replication.

Stored Procedures—A set of Transact-SQL statements that can be saved as a database object for future and repeated executions. Stored procedures benefit database users by providing basic development building blocks, speeding up processing, securing data, and reducing bandwidth usage.

Transactional Replication—Type of replication where an initial snapshot of the data is made and published to the subscriber. Data changes are propagated to the subscriber based on a stipulated number of transactions committed on the Publishing database, in the order they were originally committed.

Triggers—A special type of stored procedure that is automatically invoked by SQL Server. Triggers automatically execute whenever a specified change (INSERT, UPDATE, or DELETE) to a data object is attempted, and can be of two types: "after" triggers, and "instead-of" triggers. After triggers are fired after an operation completes. Instead-of triggers define actions to be carried out instead of a requested data modification.

UNIQUE Constraint—Similar to a primary key in that values contained in a particular column, or composite columns, may uniquely define a record. A UNIQUE constraint is generally applied when a column or group of columns within a table is required by design to be unique with the table. Inclusion of a UNIQUE constraint builds a Unique index on the table.

UNIQUE Constraints—Used to ensure that no duplicate values are entered in specific columns. Two different constraints can be used to ensure uniqueness, a PRIMARY KEY, or a UNIQUE constraint. While there can only be one primary key on a table, there can be multiple unique constraints. In addition, unique constraints can be defined on columns that allow null values, whereas a primary key cannot.

UNIQUE Index—An index that is built when a primary key constraint or unique constraint is placed on a column or group of columns in a table. This index helps maintain the domain integrity of the table.

User-defined Functions—Like any function, a user-defined function is a routine that return a value. User-defined functions can return a scalar value, an updateable table of data, or a non-updateable table of data.

Views—A virtual table created by using a select statement, referencing one or more base tables, whose TSQL script is saved and given a name within the database. Views are implemented as a security mechanism, as well as a means of reducing query complexity.

  • + Share This
  • 🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020