Note for Database Management System - DBMS By Panda Surya
- Database Management System - DBMS
- Note
- 7 Views
- Uploaded 11 months ago
Text from page-1
Relational Database Concepts for Beginners A database contains one or more tables of information. The rows in a table are called records and the columns in a table are called fields or attributes. A database that contains only one table is called a flat database. A database that contains two or more related tables is called a relational database. There are other more complex kinds of databases, but this paper is going to focus on the what and why of relational databases. Here’s an easy way to understand the benefits of dividing your data into multiple tables: Imagine that you are responsible for keeping track of all the books being checked out of a library. You could use a single table (a flat database) to track all the critical information: First Name Bob Last Name Smith Address 123 Main St. Phone 555-1212 Book Title Don Quixote Due Date 7-14-09 This table meets the basic need to keep track of who has checked out which book, but does have some serious flaws in terms of efficiency, space required, and maintenance time. For example, as voracious reader Bob checks out more books over time, you will have to re-enter all of his contact information for every book. First Name Bob Alicia Last Name Smith Petersohn Address 123 Main St. 136 Oak St. Phone 555-1212 555-1234 Bob Smith 123 Main St. 555-1212 Bob Smith 123 Main St. 555-1212 Zayn Bob Murray Smith 248 Pine Dr. 123 Main St. 555-1248 555-1212 Book Title Don Quixote Three Men in a Boat Things Fall Apart Anna Karenina Heidi The Old Man and the Sea Due Date 7-14-09 7-16-09 8-15-09 8-15-09 8-17-09 9-10-09 To re-enter Bob’s contact information wastes time, and increases the opportunity for error. Moreover, when an update is necessary (e.g. Bob’s phone number changes), each of Bob’s records must be located and corrected. If one of Bob’s records has a different phone number from the rest, is it a correction, a record overlooked during the last update, or a data-entry mistake? 1
Text from page-2
These problems can be decreased by normalizing our data – in other words, dividing the information into multiple tables with the goal of having “a place for everything, and everything in its place.” Each piece of information should appear just once, simplifying data maintenance and decreasing the storage space required. PATRONS TABLE First Name Bob Alicia Zayn Last Name Smith Petersohn Murray Address 123 Main St. 136 Oak St. 248 Pine Dr. Phone 555-1212 555-1234 555-1248 CHECKOUT TABLE Book Title Don Quixote Three Men in a Boat Things Fall Apart Anna Karenina Heidi The Old Man and the Sea Due Date 7-14-09 7-16-09 8-15-09 8-15-09 8-17-09 9-10-09 Now that the data are arranged efficiently, we need a way to show which records in the PATRONS table correspond to which records in the CHECKOUT table – in other words, who checked out which book. Instead of repeating everything we know about a patron whenever he checks out a book, we will instead give each library patron an ID, and repeat only the ID whenever we want to associate that person with a record in a different table. PATRONS TABLE Patron ID 1 2 3 First Name Bob Alicia Zayn Last Name Smith Petersohn Murray Address 123 Main St. 136 Oak St. 248 Pine Dr. Phone 555-1212 555-1234 555-1248 CHECKOUT TABLE Patron ID 1 2 1 1 3 1 Book Title Don Quixote Three Men in a Boat Things Fall Apart Anna Karenina Heidi The Old Man and the Sea 2 Due Date 7-14-09 7-16-09 8-15-09 8-15-09 8-17-09 9-10-09
Text from page-3
Now the PATRONS and CHECKOUT tables can be related (how relationships are formally declared in various database software is beyond the scope of this paper). At this point, we need some new terms to talk about our related tables. The primary key is a field whose values are unique in this table, and so can be used as identifiers for the records (multi-field or composite primary keys are beyond the scope of this paper, and are unlikely in an ArcGIS geodatabase). In table PATRONS, the Patron ID field is the primary key and so its values must remain unique. For example, the value “2” can appear only on one record - Alicia’s - and Alicia can have only one Patron ID - “2.” Is the Patron ID field in table CHECKOUT the primary key? We can see that it contains duplicate values, so the answer is No. If Patron ID were the primary key for CHECKOUT, each person would only be permitted to check out one book, and afterward would be forbidden to check out any more books, ever. So if Patron ID is not the primary key for table CHECKOUT, which field is? We can’t make Book Title the primary key, or we’d have a similar problem – each book could only be checked out once, and afterward no one would be permitted to check it out ever again. We can’t make Due Date the primary key, or else only one book could be due each day. Since none of the existing fields works as a primary key, we will add a new field to hold an identifier for each record. We could name this field Checkout ID, or we could follow ESRI’s convention of giving all primary key fields exactly the same name: ObjectID. PATRONS TABLE ObjectID 1 2 3 First Name Bob Alicia Zayn Last Name Smith Petersohn Murray Address 123 Main St. 136 Oak St. 248 Pine Dr. Phone 555-1212 555-1234 555-1248 CHECKOUT TABLE ObjectID 1 2 3 4 5 6 Patron ObjectID 1 2 1 1 3 1 3 Book Title Don Quixote Three Men in a Boat Things Fall Apart Anna Karenina Heidi The Old Man and the Sea Due Date 7-14-09 7-16-09 8-15-09 8-15-09 8-17-09 9-10-09
Text from page-4
Naming every primary key field “ObjectID” does make it easy to tell at a glance which field uniquely identifies the records in this table. We can also use this naming convention to provide hints about which fields are related. For example, Patron ObjectID in CHECKOUT is related to ObjectID in PATRONS. To further increase efficiency, decrease required space, and improve ease of maintenance, we can separate the book information into its own table. PATRONS TABLE ObjectID 1 2 3 First Name Bob Alicia Zayn Last Name Smith Petersohn Murray Address 123 Main St. 136 Oak St. 248 Pine Dr. Phone 555-1212 555-1234 555-1248 CHECKOUT TABLE ObjectID 1 2 3 4 5 6 Patron ObjectID 1 2 1 1 3 1 Book ObjectID 1 6 3 5 2 4 Due Date 7-14-09 7-16-09 8-15-09 8-15-09 8-17-09 9-10-09 BOOKS TABLE ObjectID 1 2 3 4 5 6 Title Don Quixote Heidi Things Fall Apart The Old Man and the Sea Anna Karenina Three Men in a Boat Author Miguel Cervantes Johanna Spyri Chinua Achebe Earnest Hemingway Leo Tolstoy Jerome K. Jerome Year 1605 1880 1958 1952 1873 1889 Now ObjectID in BOOKS is related to Book ObjectID in CHECKOUT. When two tables have an unequal relationship, we call the independent table the parent and the dependent table the child. You can identify the parent table by determining which table could contain a record without needing a corresponding record in the table on the other side of the relationship. For example, is it possible to have an unpopular library book which never gets checked out? Yes. Is it possible to check out a book that doesn’t exist? No. Since BOOKS can contain 4
Leave your Comments