Fundamentals of Relational Database Design - - r. September 7, 2. 00. By Paul Litwin. Paul Litwin is the owner of Litwin Consulting, a. Microsoft Solution Provider specializing in programming, consulting, mentoring. ASP, ASP. NET, VB, SQL Server, XML, and related technologies. This article was adapted by the author from the design chapter of. Microsoft Access 2 Developer's Handbook. Sybex 1. 99. 4, by Ken Getz, Paul Litwin and Greg Reddick, and is. Don't let the fact that the examples were done for Access 2 fool. Many others attempt to learn it, but give up because of the dry, academic treatment it is usually given by most authors and teachers. But if creating databases is part of your job, then you're treading on thin ice if you don't have a good solid understanding of relational database design theory. This article begins with an introduction to relational database design theory, including a discussion of keys, relationships, integrity rules, and the often- dreaded . Codd in 1. 96. 9, then a researcher at IBM. The model is based on branches of mathematics called set theory and predicate logic. The basic idea behind the relational model is that a database consists of a series of unordered tables (or relations) that can be manipulated using non- procedural operations that return tables. This model was in vast contrast to the more traditional database theories of the time that were much more complicated, less flexible and dependent on the physical storage methods of the data. Note: It is commonly thought that the word relational in the relational model comes from the fact that you relate together tables in a relational database. Although this is a convenient way to think of the term, it's not accurate. Instead, the word relational has its roots in the terminology that Codd used to define the relational model. The table in Codd's writings was actually referred to as a relation (a related set of information). In fact, Codd (and other relational database theorists) use the terms relations, attributes and tuples where most of us use the more common terms tables, columns and rows, respectively (or the more physical—and thus less preferable for discussions of database design theory—files, fields and records). The relational model can be applied to both databases and database management systems (DBMS) themselves. The relational fidelity of database programs can be compared using Codd's 1. Codd's seminal paper on the relational model, the number of rules has been expanded to 3. DBMS products conform to the relational model. When compared with other database management programs, Microsoft Access fares quite well in terms of relational fidelity. Download free ebooks at bookboon.com An Introduction to Relational Database Theory 4 Contents Preface 10 1. Introduction 14 1.1 Introduction 14. A Relational Model of Data for Large Shared Data Banks E. CODD IBM Research Laboratory, San Jose. Finally, the relational view permits a clearer evaluation. 7 Agenda The need for database management systems Brief overview of the relational model Querying relational database directly and through views. Still, it has a long way to go before it meets all twelve rules completely. Fortunately, you don't have to wait until Microsoft Access is perfect in a relational sense before you can benefit from the relational model. The relational model can also be applied to the design of databases, which is the subject of the remainder of this article. Relational Database Design. When designing a database, you have to make decisions regarding how best to take some system in the real world and model it in a database. This consists of deciding which tables to create, what columns they will contain, as well as the relationships between the tables. While it would be nice if this process was totally intuitive and obvious, or even better automated, this is simply not the case. A well- designed database takes time and effort to conceive, build and refine. The benefits of a database that has been designed according to the relational model are numerous. Some of them are: Data entry, updates and deletions will be efficient. Tutorial on Relational Database Design Introduction Relational database was proposed by Edgar Codd (of IBM Research) around 1969. It has since become the dominant. How a relational database works. Learn about fields, records and tables and how they form relational databases. Data Modeling and Relational Database Design.Volume 1 A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as invented by E. Data retrieval, summarization and reporting will also be efficient. Since the database follows a well- formulated model, it behaves predictably. Since much of the information is stored in the database rather than in the application, the database is somewhat self- documenting. Changes to the database schema are easy to make. The goal of this article is to explain the basic principles behind relational database design and demonstrate how to apply these principles when designing a database using Microsoft Access. This article is by no means comprehensive and certainly not definitive. Many books have been written on database design theory; in fact, many careers have been devoted to its study. Instead, this article is meant as an informal introduction to database design theory for the database developer. Note: While the examples in this article are centered around Microsoft Access databases, the discussion also applies to database development using the Microsoft Visual Basic. Each table should represent only one thing. These things (or entities) can be real- world objects or events. For example, a real- world object might be a customer, an inventory item, or an invoice. Examples of events include patient visits, orders, and telephone calls. Tables are made up of rows and columns. The relational model dictates that each row in a table be unique. If you allow duplicate rows in a table, then there's no way to uniquely address a given row via programming. This creates all sorts of ambiguities and problems that are best avoided. You guarantee uniqueness for a table by designating a primary key—a column that contains unique values for a table. Each table can have only one primary key, even though several columns or combination of columns may contain unique values. All columns (or combination of columns) in a table with unique values are referred to as candidate keys, from which the primary key must be drawn. All other candidate key columns are referred to as alternate keys. Keys can be simple or composite. A simple key is a key made up of one column, whereas a composite key is made up of two or more columns. The decision as to which candidate key is the primary one rests in your hands—there's no absolute rule as to which candidate key is best. Fabian Pascal, in his book SQL and Relational Basics, notes that the decision should be based upon the principles of minimality (choose the fewest columns necessary), stability (choose a key that seldom changes), and simplicity/familiarity (choose a key that is both simple and familiar to users). Let's illustrate with an example. Say that a company has a table of customers called tbl. Customer, which looks like the table shown in Figure 1. Figure 1. The best choice for primary key for tbl. Customer would be Customer. Id. Candidate keys for tbl. Customer might include Customer. Id, (Last. Name + First. Name), Phone#, (Address, City, State), and (Address + Zip. Code). Following Pascal's guidelines, you would rule out the last three candidates because addresses and phone numbers can change fairly frequently. The choice among Customer. Id and the name composite key is less obvious and would involve tradeoffs. How likely would a customer's name change (e. Will misspelling of names be common? How likely will two customers have the same first and last names? How familiar will Customer. Id be to users? There's no right answer, but most developers favor numeric primary keys because names do sometimes change and because searches and sorts of numeric columns are more efficient than of text columns in Microsoft Access (and most other databases). Counter columns in Microsoft Access make good primary keys, especially when you're having trouble coming up with good candidate keys, and no existing arbitrary identification number is already in place. Don't use a counter column if you'll sometimes need to renumber the values—you won't be able to—or if you require an alphanumeric code—Microsoft Access supports only long integer counter values. Also, counter columns only make sense for tables on the one side of a one- to- many relationship (see the discussion of relationships in the next section). Note: In many situations, it is best to use some sort of arbitrary static whole number (e. ID, order ID, a counter column, etc.) as a primary key rather than a descriptive text column. This avoids the problem of misspellings and name changes. Also, don't use real numbers as primary keys since they are inexact. Foreign Keys and Domains. Although primary keys are a function of individual tables, if you created databases that consisted of only independent and unrelated tables, you'd have little need for them. Primary keys become essential, however, when you start to create relationships that join together multiple tables in a database. A foreign key is a column in a table used to reference a primary key in another table. Continuing the example presented in the last section, let's say that you choose Customer. Id as the primary key for tbl. Customer. Now define a second table, tbl. Order, as shown in Figure 2. Figure 2. Customer. Id is a foreign key in tbl. Order which can be used to reference a customer stored in the tbl. Customer table. Customer. Id is considered a foreign key in tbl. Order since it can be used to refer to given customer (i. Customer table). It is important that both foreign keys and the primary keys that are used to reference share a common meaning and draw their values from the same domain. Domains are simply pools of values from which columns are drawn. For example, Customer. Id is of the domain of valid customer ID #'s, which in this case might be Long Integers ranging between 1 and 5. Similarly, a column named Sex might be based on a one- letter domain equaling 'M' or 'F'. Domains can be thought of as user- defined column types whose definition implies certain rules that the columns must follow and certain operations that you can perform on those columns. Microsoft Access supports domains only partially. For example, Microsoft Access will not let you create a relationship between two tables using columns that do not share the same datatype (e. On the other hand, Microsoft Access will not prevent you from joining the Integer column Employee. Age from one table to the Integer column Years.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
December 2016
Categories |