SQL Server 2012数据库设计与实现精要

需积分: 3 128 浏览量更新于2024-07-19 收藏 21.62MB PDF 举报

"《Pro SQL Server 2012 Relational Database Design and Implementation》是Louis Davidson和Jessica M. Moss合著的一本关于SQL Server 2012数据库设计的专业书籍，旨在提供数据库设计和实现的深入指导。" 本书涵盖了数据库设计的多个关键方面，包括但不限于以下内容： 1. **基础概念** (Chapter 1: The Fundamentals): 这一章为读者提供了SQL Server 2012关系数据库的基础知识，涉及数据库的基本原理、SQL语言的基础以及数据库系统的核心概念。 2. **需求分析** (Chapter 2: Introduction to Requirements): 在设计数据库之前，了解业务需求至关重要。这一章讨论如何收集和分析用户需求，以便创建满足业务逻辑的数据模型。 3. **数据建模的语言** (Chapter 3: The Language of Data Modeling): 作者深入探讨了数据建模的术语和方法，包括实体关系图（ER图）的绘制，以及如何用这些图形化工具来表达复杂的业务关系。 4. **初步数据模型制作** (Chapter 4: Initial Data Model Production): 这一章介绍了如何从需求分析中提炼出初步的数据模型，包括实体、属性和关系的设计。 5. **规范化** (Chapter 5: Normalization): 正规化是数据库设计中的关键步骤，用于减少数据冗余和提高数据一致性。本章将详细解释不同级别的正规化原则，如1NF、2NF、3NF等，并展示如何应用这些原则。 6. **物理模型实现案例研究** (Chapter 6: Physical Model Implementation Case Study): 作者通过实例展示了如何将逻辑数据模型转化为物理数据库结构，包括表设计、索引策略和存储考虑。 7. **数据保护** (Chapter 7: Data Protection with Check Constraints and Triggers): 讨论了使用检查约束和触发器来确保数据完整性和安全性的技术。 8. **模式与反模式** (Chapter 8: Patterns and Anti-Patterns): 介绍了数据库设计中常见的良好实践和应避免的陷阱，帮助读者提高设计质量。 9. **数据库安全与安全模式** (Chapter 9: Database Security and Security Patterns): 深入探讨SQL Server 2012的权限管理、角色分配以及如何实施安全的最佳实践。 10. **表结构与索引** (Chapter 10: Table Structures and Indexing): 阐述了如何优化表结构以提升查询性能，以及如何有效地使用索引来加速数据访问。 11. **并发控制编码** (Chapter 11: Coding for Concurrency): 详细讲解在多用户环境下如何处理并发问题，包括事务管理、死锁预防和并发控制机制。 12. **可重用的标准数据库组件** (Chapter 12: Reusable Standard Database Components): 探讨如何创建和使用如存储过程、视图和函数等数据库组件，以提高代码复用性和维护性。 13. **数据访问策略的考虑** (Chapter 13: Considering Data Access Strategies): 分析不同的数据访问方式，如ORM（对象关系映射）和直接SQL查询，以及如何选择最适合项目的方法。 14. **报告设计** (Chapter 14: Reporting Design): 介绍如何设计高效、直观的报表，以满足业务分析和决策支持的需求。 15. **附录** (Appendix A 和 B): 提供了补充信息和可能需要的参考材料。此书适合对SQL Server 2012数据库设计有深入了解需求的IT专业人员，包括数据库管理员、软件开发者和系统架构师。通过阅读这本书，读者可以掌握从需求分析到实施的完整数据库设计流程，以及如何优化和保护数据库系统的各种技巧。

CHAPTER 1 ■ THE FUNDAMENTALS

Applications must work using the same syntax, even when changes are made to the way in which the database

internally implements data storage and access methods. is rule basically states that the way the data is stored

must be independent of the manner in which it’s used and the way data is stored is immaterial to the users. is

rule will play a big part of our entire design process, because we will do our best to ignore implementation details

and design for the data needs of the user.

Rule 9: Logical Data Independence

Application programs and terminal activities remain logically unimpaired when information

preserving changes of any kind that theoretically permit unimpairment are made to the base tables.

While rule eight was concerned with the internal data structures that interface the relational engine to the ﬁle

system, this rule is more centered with things we can do to the table deﬁnition in SQL. Say you have a table that

has two columns, A and B. User X makes use of A; user Y uses A and B. If the need for a column C is discovered,

adding column C should not impair users X’s and Y’s programs at all. If the need for column B was eliminated,

and hence the column was removed, it is acceptable that user Y would then be aected, yet user X, who only

needed column A, would still be unaected.

As a quick aside, there is a construct known as star (*) that is used as a wildcard for all of the columns in the

table (as in SELECT * FROM table). e principals of logical data independence are largely the reason why we

avoid getting all of the columns like this for anything other than nonreusable ad hoc access (like a quick check

to see what data is in the table to support a user issue). Using this construct tightly couples the entire table to

the user, whether or not a new column is added. is new column may in fact be unneeded (and contain a huge

amount of data!) or a unnecessary column might be removed but then break your code unexpectedly. Declaring

exactly the data you need and expect is a very good plan in your code that you write for reuse.

Rule 10: Integrity Independence

Integrity constraints speciﬁc to a particular relational database must be deﬁnable in the

relational data sublanguage and storable in the catalog, not in the application programs.

Another of the truly fundamental concepts stressed by the founder of the relational database concepts was

that data should have integrity; in other words, it’s important for the system to protect itself from data issues.

Predicates that state that data must ﬁt into certain molds were to be implemented in the database. Minimally, the

RDBMS must internally support the deﬁnition and enforcement of entity integrity (primary keys) and referential

integrity (foreign keys). We also have unique constraints to enforce keys that aren’t the primary key, NULL

constraints to state whether or not a value must be known when the row is created, as well as check constraints

that are simply table or column conditions that must be met. For example, say you have a column that stores

employees’ salaries. It would be good to add a condition to the salary storage location to make sure that the value

is greater than or equal to zero, because you may have unpaid volunteers, but I can only think of very few jobs

where you pay to work at your job.

is rule is just about as controversial at times as the concept of NULLs. Application programmers don’t like

to give up control of the management of rules because managing the general rules in a project becomes harder.

On the other hand, many types of constraints you need to use the engine for are infeasible to implement in the

application layer (uniqueness and foreign keys are two very speciﬁc examples, but any rule that reaches outside

of the one row of data cannot be done both quickly and safely in the application layer because of the rigors of

concurrent user access).

www.it-ebooks.info

CHAPTER 1 ■ THE FUNDAMENTALS

e big takeaway for this particular item should be that the engine provides tools to protect data, and in the

least intrusive manner possible, you should use the engine to protect the integrity of the data.

Rule 11: Distribution Independence

e data manipulation sublanguage of a relational DBMS must enable application programs

and terminal activities to remain logically unimpaired whether and whenever data are

physically centralized or distributed.

is rule was exceptionally forward thinking in 1985 and is still only getting close to being realized for anything

but the largest systems. It is very much an extension of the physical independence rule taken to a level that spans

the containership of a single computer system. If the data is moved to a dierent server, the relational engine

should recognize this and just keep working.

Rule 12: Nonsubversion Rule

If a relational system has or supports a low-level (single-record-at-a-time) language, that low-

level language cannot be used to subvert or bypass the integrity rules or constraints expressed

in the higher-level (multiple-records-at-a-time) relational language.

is rule requires that alternate methods of accessing the data are not able to bypass integrity constraints, which

means that users can’t violate the rules of the database in any way. Generally speaking, at the time of this writing,

most tools that are not SQL based do things like check the consistency of the data and clean up internal storage

structures. ere are also row-at-a-time operators called cursors that deal with data in a very nonrelational

manner, but in all cases, they do not have the capability to go behind or bypass the rules of the RDBMS.

A common big cheat is to bypass rule checking when loading large quantities of data using bulk loading

techniques. All of the integrity constraints you put on a table generally will be quite fast and only harm

performance an acceptable amount during normal operations. But when you have to load millions of rows, doing

millions of checks can be very expensive, and hence there are tools to skip integrity checks. Using a bulk loading

tool is a necessary evil, but it should never be an excuse to allow data with poor integrity into the system.

Nodding at SQL Standards

In addition to Codd’s rules, one topic that ought to be touched on brieﬂy is the SQL standards. Rules ﬁve, six, and

seven all pertain to the need for a high level language that worked on data in a manner encapsulated the nasty

technical details from the user. Hence, the SQL language was born. e language SQL was initially called SEQUEL

(Structured English Query Language), but the name was changed to SQL for copyright reasons. However, it is still

often pronounced “sequel” today (sometimes, each letter is pronounced separately). SQL had its beginnings in

the early 1970s with Donald Chamberlin and Raymond Boyce (see http://en.wikipedia.org/wiki/SQL), but the

path to get us to the place where we are now was quite a trip. Multiple SQL versions were spawned, and the idea

of making SQL a universal language was becoming impossible.

In 1986, the American National Standards Institute (ANSI), created a standard called SQL-86 for how the

SQL language should be moved forward. is standard took features that the major players at the time had

been implementing in an attempt to make code interoperable between these systems, with the engines being

www.it-ebooks.info

CHAPTER 1 ■ THE FUNDAMENTALS

the part of the system that would be specialized. is early speciﬁcation was tremendously limited and did not

even include referential integrity constraints. In 1989, the SQL-89 speciﬁcation was adopted, and it included

referential integrity, which was a tremendous improvement and a move toward implementing Codd’s twelfth rule

(see Handbook on Architectures of Information Systems by Bernus, Mertins, and Schmidt [Springer 2006]).

Several more versions of the SQL standard have come and gone, in 1992, 1999, 2003, 2006, and 2008. For the

most part, these documents are not exactly easy reading, nor do they truly mean much to the basic programmer/

practitioner, but they can be quite interesting in terms of putting new syntax and features of the various database

engines into perspective. e standard also helps you to understand what people are talking about when they talk

about standard SQL. e standard also can help to explain some of the more interesting choices that are made by

database vendors.

is brief history lesson was mostly for getting you started to understand why relational database are

implemented as they are today. In three papers, Codd took a major step forward in deﬁning what a relational database

is and how it is supposed to be used. In the early days, Codd’s 12 rules were used to determine whether a database

vendor could call itself relational and presented sti implementation challenges for database developers. As you will

see by the end of this book, even today, the implementation of the most complex of these rules is becoming achievable,

though SQL Server (and other RDBMSs) still fall short of achieving their objectives. Plus, the history of the SQL

language has been a very interesting one as standards committees from various companies come together and try to

standardize the stu they put into their implementations (so everyone else gets stuck needing to change).

Obviously, there is a lot more history between 1985 and today. Many academics including Codd himself, C. J.

Date, and Fabian Pascal (both of whom contribute to their site http://www.dbdebunk.com), Donald Chamberlin,

Raymond Boyce (who contributed to one of the Normal Forms, covered in Chapter 6), and many others have

advanced the science of relational databases to the level we have now. Some of their material is interesting

only to academics, but most of it has practical applications even if it can be very hard to understand, and it’s

very useful to anyone designing even a modestly complex model. I deﬁnitely suggest reading as much of their

material, and all the other database design materials, as you can get your hands on after reading this book (after,

read: after). In this book, we will keep everything at a very practical level that is formulated to cater to the general

practitioner to get down to the details that are most important and provide common useful constructs to help you

start developing great databases quickly.

Recognizing Relational Data Structures

is section introduces the following core relational database structures and concepts:

Database and schema•

Tables, rows, and columns•

Missing values (nulls)•

Uniqueness constraints (keys)•

As a person reading this book, this is probably not your ﬁrst time working with a database, and therefore,

you are no doubt somewhat familiar with some of these concepts. However, you may ﬁnd there are at least a few

points presented here that you haven’t thought about that might help you understand why we do things later—for

example, the fact that a table consists of unique rows or that within a single row a column must represent only a

single value. ese points make the dierence between having a database of data that the client relies on without

hesitation and having one in which the data is constantly challenged.

Note, too, that in this section we will only be talking about items from the relational model. In SQL Server,

you have a few layers of containership based on how SQL Server is implemented. For example, the concept of a

server is analogous to a computer, or a virtual machine perhaps. On a server, you may have multiple instances

of SQL Server that can then have multiple databases. e terms “server” and “instance” are often misused as

www.it-ebooks.info

CHAPTER 1 ■ THE FUNDAMENTALS

synonyms, mostly due to the original way SQL Server worked allowing only a single instance per server (and

since the name of the product is SQL Server, it is a natural mistake). For most of this book, we will not need to

look at any higher level than the database, which I will introduce in the following section.

Introducing Databases and Schemas

A database is simply a structured collection of facts or data. It needn’t be in electronic form; it could be a card catalog

at a library, your checkbook, a SQL Server database, an Excel spreadsheet, or even just a simple text ﬁle. Typically, the

point of any database is to arrange data for ease and speed of search and retrieval—electronic or otherwise.

e database is the highest-level container that you will use to group all the objects and code that serve a

common purpose. On an instance of the database server, you can have multiple databases, but best practices suggest

using as few as possible for your needs. is container is often considered the level of consistency that is desired that all

data is maintained at, but this can be overridden for certain purposes (one such case is that databases can be partially

restored and be used to achieve quick recovery for highly available database systems.) A database is also where the

storage on the ﬁle system meets the logical implementation. Until very late in this book, in Chapter 10, really, we will

treat the database as a logical container and ignore the internal properties of how data is stored; we will treat storage

and optimization primarily as a post-relational structure implementation consideration.

e next level of containership is the schema. You use schemas to group objects in the database with

common themes or even common owners. All objects on the database server can be addressed by knowing the

database they reside in and the schema, giving you what is known as the three-part name (note that you can set

up linked servers and include a server name as well, for a four-part name):

databaseName.schemaName.objectName

Schemas will play a large part of your design, not only to segregate objects of like types but also because

segregation into schemas allows you to control access to the data and restrict permissions, if necessary, to only a

certain subset of the implemented database.

Once the database is actually implemented, it becomes the primary container used to hold, back up, and

subsequently restore data when necessary. It does not limit you to accessing data within only that one database;

however, it should generally be the goal to keep your data access needs to one database. In Chapter 9, we will

discuss in some detail the security problems of managing security of data in separate databases.

Note ■ The term “schema” has other common usages that you should realize: the entire structure for the data-

bases is referred to as the schema, as are the Data Deﬁnition Language (DDL) statements that are used to create

the objects in the database (such as CREATE TABLE and CREATE INDEX). Once we arrive to the point where we are

talking about schema database objects, we will clearly make that delineation.

Understanding Tables, Rows, and Columns

e object that will be involved in almost all your designs and code is the table. e table is used to store information

and will be used to represent some thing that you want to store data about. A table can be used to represent people,

places, things, or ideas (i.e., nouns, generally speaking) about which information needs to be stored.

In a relational database, a table is a representation of data from which all the implementation aspects have

been removed. It is basically data that has a light structure of having instances of some concept (like a person)

and information about that concept (the person’s name, address, etc). e instance is implemented as a row,

and the information implemented in columns, which will be further deﬁned in this section. A table is not to

be thought of as having any order and should not be thought of as a location in some storage. As previously

discussed in the “Taking a Brief Jaunt rough History” section of this chapter, one of the major design concepts

behind a relational database system is that it is to be encapsulated from the physical implementation.

www.it-ebooks.info

CHAPTER 1 ■ THE FUNDAMENTALS

A table is made up of rows of data, which are used to represent a single instance of the concept that the table

represents. So if the table represents people, a row would represent a single person. Each row is broken up into

columns that contain a single piece of information about whatever the row is representing. For example, the ﬁrst

name column of a row might contain “Fred” or “Alice”.

“Atomic,” or “scalar,” which I brieﬂy mentioned earlier, describes the type of data stored in a column. e

meaning of “atomic” is pretty much the same as in physics. Atomic values will be broken up until they cannot

be made smaller without losing the original characteristics. In chemistry molecules are made up of multiple

atoms—H

O can be broken down to two hydrogen atoms and one oxygen atom—but if you break the oxygen

atom into smaller parts, you will no longer have oxygen (and you will probably ﬁnd yourself scattered around the

neighborhood along with parts of your neighbors).

A scalar value can mean a single value that is obvious to the common user, such as a single word or a

number, or it can mean something like a whole chapter in a book stored in a binary or even a complex type,

such as a point with longitude and latitude. e key is that the column represents a single value that resists being

broken down to a lower level than what is needed when you start using the data. So, having a column that is

deﬁned as two independent values, say Column.X and Column.Y, is perfectly acceptable because they are not

independent of one another, while deﬁning a column to deal with values like ‘Cindy,Leo,John’ would likely be

invalid, because that value would very likely need to be broken apart to be useful. However, if you will never need

to programmatically access part of a value, it is, for all intents and purposes, a scalar value.

A very important concept of a table is that it should be thought of as having no order. Rows can be stored and

used in any order, and columns needn’t be in any ﬁxed order either. is fundamental property will ideally steer

your utilization of data to specify the output you need and to ask for data in a given order if you desire rows to be

in some expected order.

Now, we come to the problem with the terms “table,” “row,” and “column.” ese terms are commonly

used by tools like Excel, Word, and so on to mean a ﬁxed structure for displaying data. For table, Dictionary.com

(http://dictionary.reference.com) has the following deﬁnition for “table”:

An orderly arrangement of data, especially one in which the data are arranged in columns and

rows in an essentially rectangular form.

When data is arranged in a rectangular form, it has an order and very speciﬁc locations. A basic example

of this deﬁnition of “table” that most people are familiar with is a Microsoft Excel spreadsheet, such as the one

shown in Figure 1-1.

Figure 1-1. Excel table

www.it-ebooks.info

剩余766页未读，继续阅读

aaronyu1123

粉丝: 0
资源: 1

SQL Server 2012数据库设计与实现精要

[Apress] Pro SQL Server 2012 Relational Database Design and Implementation

SQL SERVER 2012数据库设计与实现

Pro SQL Server 2012 Relational Database Design and Implementation(Apress,2012)

Database Processing Fundamentals, Design, and Implementation (12th Edition).rar

Beginning T-SQL with Microsoft SQL Server 2005 and 2008

Apress.Pro.SQL.Server.2008

Microsoft SQL Server 2008 - T-SQL Querying March 2009

【Advanced Level】Design and Implementation of Distributed Crawler Architecture

【Database Model Design】: The Golden Rules for Building Efficient Structures with Python and MySQL

【The Python and MySQL Interaction Guide】: The Ultimate Guide to Unlocking Database Operations

最新资源