Apress - Pro SQL Server 2008 Relational Database Design and Implementation (2008)03

Apress - Pro SQL Server 2008 Relational Database Design and Implementation (2008)03
Nội dung trích xuất từ tài liệu:
Apress - Pro SQL Server 2008 Relational Database Design and Implementation (2008)03 CHAPTER 1 s INTRODUCTION TO DATABASE CONCEPTS 3 A thorough database design process will undergo four distinct phases, as follows: • Conceptual: This is the “sketch” of the database that you will get from initial requirements gathering and customer information. During this phase, you attempt to identify what the user wants. You try to find out as much as possible about the business process for which you are building this data model, its scope, and, most important, the business rules that will gov- ern the use of the data. You then capture this information in a conceptual data model consisting of a set of “high-level” entities and the interactions between them. • Logical: The logical phase is a refinement of the work done in the conceptual phase, trans- forming what is often a loosely structured conceptual design into a full-fledged relational database design that will be the foundation for the implementation design. During this stage, you fully define the required set of entities, the relationships between them, the attrib- utes of each entity, and the domains of these attributes (i.e., the sort of data the attribute holds and the range of valid values). • Implementation: In this phase, you adapt the logical model for implementation in the host relational database management system (RDBMS; in our case, SQL Server). • Physical: In this phase, you create the model where the implementation data structures are mapped to physical storage. This phase is also more or less the performance tuning/optimiza- tion phase of the project because it is important that your implementation should function in the same way no matter what the physical hardware looks like. It might not function very fast, but it will function. It is during this phase of the project that indexes, disk layouts, and so on, come into play, and not before this. The first four chapters of this book are concerned with the conceptual and logical designphases, and I make only a few references to SQL Server. Generally speaking, the logical model of anyrelational database will be the same, be it for SQL Server, Oracle, Informix, DB2, MySQL, or any-thing else based, in some measure, on the relational model.s Note A lot of people use the name physical to indicate that they are working on the SQL Data DefinitionLanguage (DDL) objects, rather than the meaning I give, where it is the layer “below” the SQL language. But lump-ing both the DDL and the tuning layers into one “physical” layer did not sit well with some readers/reviewers, andI completely agree. The implementation layer is purely SQL and doesn’t care too much about tuning. The physicallayer is pure tuning, and nothing done in that layer should affect the meaning of the data.ConceptualThe conceptual design phase is essentially a process of analysis and discovery, the goal being todefine the organizational and user data requirements of the system. Note that there are other partsto the overall design picture beyond the needs of the database design that will be part of the con-ceptual design phase (and all follow-on phases), but for this book, the design process will bediscussed in a manner that may make it sound as if the database is all that matters. (As a reader ofthis book who is actually reading this chapter on fundamentals, you probably feel that way already.) Two of the core activities that make up this stage are as follows: • Discovering and documenting a set of entities and the relationships between them • Discovering and documenting the business rules that define how the data can and will be used and also the scope of the system that you are designing4 CHAPTER 1 s INTRODUCTION TO DATABASE CONCEPTS Your conceptual design should capture, at a high level, the fundamental “sets” of data that are required to support the business processes and users’ needs. Entity discovery is at the heart of this process. Entities correspond to nouns (people, places, and things) that are fundamental to the busi- ness processes you are trying to improve by creating software. Consider a basic business statement such as the following: People place orders in order to buy products. Immediately, you can identify three conceptual entities (in bold) and begin to understand how they interact. Note too, phrases such as “in order” can be confusing, and if the writer of this spec were writing well, the phrase would have been “People place orders to buy products.” s Note An entity is not the same thing as a table. A table is an implementation-specific SQL construct. Sometimes an entity will map directly to a table in the implementation, but often it won’t. Some conceptual entities will be too abstract to ever be implemented, and sometimes they will map to two or more tables. It is a major (if somewhat unavoidable because of human nature) mistake at this point of the process to begin thinking about how the final database will look. The primary point of this note is simply that you should not rush the design process by worrying about implemen- tation details until you start to flip bits on the SQL Server. The next section of this chapter will establish the terminology in more detail. In the end, one section had to come first, and this one won. During this conceptual phase, you need to do the requisite planning and analysis so that the requirements of the business and its customers are met. The conceptual design should focus stead- fastly on the broader view of the system, and it may not correspond to the final, implemented ...