Milestone #9: A Table with a View Thursday, Nov 9 2006

In Chapter 15 of Snyder he takes a closer look into the world of databases. Here, we learn that relationships are a critical part of database design–relationships are key to associating fields of the physical tables.  Snyder defines a relationship as “a correspondence between rows of one table and the rows of another table.  Relationships are part of the metadata of a database, and because they are critical to building the logical database from the physical database, we give them names and characterize their properties.” 

This is a very important concept to grasp.  The ddifference between the logical and physical database is that the logical, or customized tables, exists virtually.  The logical tables are what makes databases user-friendly and allows a person to choose the type of view that best fits his/her needs.  However, there are technical functions that must be considered in order for personalized viewing to work properly, such as eliminating redundancy and providing the database with the most current and accurate data. 

A standard approach to creating the logical database is the join-then-trim stratgy.  Snyder expalins that this standard approach forms a super table by joining several physical tables, then trimmed down to keep only the information in which the user has interest. 

In addition to this strategy, entity-relationship diagrams (ER Diagrams) are used to make database structure and design clear for administrators.  The diagrams use arrows between boxes, which represent entities, and therefore help to make sense of the relationships.  I found the ER Diagrams a very logical and clear way to understand how information is organized and how database design is structured.  The following two examples are taken from the Information Systems department within the Business Administration School at the University of Missouri: 

In example 1, the shapes around the words have Representative meanings:  the rectangle is an entity, the diamond is a description, and the oval is an attribute. 

This chapter, especially understanding the difference between logical and physical databases and ER Diagrams gives us a good sense about design and structure.  After breaking components down and getting to the core, it becomes much clearer and easy to understand the way that databases are created. 

Leave a Response »

 

Milestone #8: Introduction to Database Concepts Wednesday, Nov 1 2006

In Snyder Chapter 14, we are introduced to databases, which make it possible to apply more organization to information and receive more help from the computer than spreadsheets.  It is important to understand metadata, or information describing properties of other information.  This includes the metadata table’s name, attributes’ names, the types of values that each attribute can have, and the primary key.  These concepts are somewhat straightforward, however understanding howto set up metadata for list to create database tables is not so simple.  Snyder clearly explains the structure, content, and five basic operations of databases, and in this blog entry I will also further analyze these functions using other sources.

The main use of a database is to look up information.  For example, at the nonprofit organization that I work at, we use a contacts management database system called FileMaker Pro.  The organization purchased the software, then an in-house IT person tailored the database to the organizaion’s needs.  Being a nonprofit, it is important that employees were able to look up contacts according to their affiliation with organization—so the “techie” inserted categories such as foundation, government, nonprofit, client, university, corporation,etc.  This was extremely helpful when our employees were trying to find someone in the database or enter someone into the system, as well as important for communication purposes (mailing e-letter and news updates).  The categories were also important for importing attendee lists from numerous annual nationwide events.  Without the File Maker Pro database, the amount of information and lists of contacts could not be managed effectively.  It was also extremely helpful to have someone on-site who could adjust the database as needed, because it was a fairly new program, users found glitches or ways to make it better over time. 

On www.filemaker.com, the information on their database is very helpful and explains how the product provides solutions for businesses, education, government, and nonprofits.  For example, according to the site, Filemaker helps nonprofits to:

• Track volunteer data
• Manage mailing lists
• Maintain donor information
• Manage grants and funding
• Create and manage surveys
• Organize volunteer contacts

As you can see, there are numerous uses for just this one program within one sector.  There are a wide range of ways to utilize databases in order to make an organization more effective and efficient. 

There are five operations of databases that Snyder explains in detail: Select, Project, Union, Difference, and Product.  These are needed to create new tables from a database of tables.  The Select operation takes rows from one table to create a new table, Project builds a new table from the columns of an existing table, Union combines the two tables, Difference removes from one table the rows also listed in the second table (the opposite of Union), and Product creates a supertable combining all he fields from both tables.  In addition to the five major operations is the Join operation, which is like Product but doesn’t necessarily produce all pairings.  Join only combines rows given that match on the field, not all pairing of rows. 

Now that I understand a little bit about the basics for creating databases, I cant help but apply the ongoing “security” theme of our class.  How can we secure databases so that unintended users cant get ahold of information? According to an article on http://searchsecurity.techtarget.com, “The secure operation of a database server requires a cooperative effort among IT professionals responsible for the secure operation of the network and servers, and those developing applications that interact with the database server.”  The article, Five tips for secure database development, by Mike Chapple, has a few very helpful suggestions for securing databases:

• Use database views instead of tables. Developers should create applications that interact with views (basically, predefined queries) rather than interact directly with the underlying table. This allows greater control over access to information, both at the row and column level.
• Make use of stored procedures.Developers should store their SQL code on the server and make it available to applications through the use of stored procedures. This limits the range of actions applications may perform on the database and allows for easy, centralized updates if security requirements change in the future.

• Don’t embed SQL commands in application code. This goes hand-in-hand with the previous step. Developers should neverinclude SQL commands in their applications. This creates a significant vulnerability if malicious users are able to later modify the application.
• Don’t let developers have administrative power over users. Security professionals have long practiced the idea of separation of powers. It’s a good idea to ensure that developers (who often control table structures, stored procedures and the like) are not able to create and/or modify user permissions. This prevents them from succumbing to the temptation of loosening access controls to make a program work “just while we’re testing it.” I’ve seen all too many cases where those “temporary” solutions have remained in place for years. Requiring developers to approach administrators for permission changes limits the likelihood of unnecessary change requests.
• Apply the principle of least privilege. In our last tip, we discussed the importance of only granting users the minimum set of permissions necessary to complete their jobs. This is also true for the administrative accounts used to execute application code. Ensure that these accounts have only the specific permissions they need to execute authorized functions.

Snyder’s introduction coupled with further exploration of database uses and security was very helpful in my understanding of the differences between spreadsheets and databases.  Now, when I am at work using FileMaker Pro, I will better understand how the software is working, as well as more ways to utilize its functions. 

Leave a Response »

 

Digital Nugget #8: Feds Leapfrog RFID Privacy Study Wednesday, Nov 1 2006

After our discussions in class about the U.S. government issuing national ID cards, I was curious to learn more about how far a long it is in the process.  I was so surprised to find out that a member of our class, who is also in the military, already had one of these computer-chipped cards and realized it must just be a matter of time that all citizens will be required to have them as well.  So what is so different about the controversial national ID cards and a driver’s licence? They contain a chip which can hold an abundance of information about an individual–medical history, where they have traveled, criminal record, credit history–basically anything and everything about the person which could be of interest to the government at any given time.

But how safe is it to have all of this information on a computerized chip? And how does the chip work without tracking a person’s every move?  In an article from www.wired.com, Feds Leapfrog RFID Privacy Study, I found that the not only is the progress towards card issuance pretty far along, but so is the fight against it.

“An outside privacy and security advisory committee to the Department of Homeland Security penned a tough report concluding the government should not use chips that can be read remotely in identification documents. But the report remains stuck in draft mode, even as new identification cards with the chips are being announced.”

The chips that can be read remotely are called RFID chips and either have a battery or use the radio waves to send information.  These chips are what are are generally used in tracking inventory or for highway toll payment systems.  So how safe can these chips be when it comes to forgery? According to the draft report from the advisory committee to DHS, not so safe.

“….critics argue that hackers can skim information off the chips and that the chips can be used to track individuals. Hackers have also been able to clone some chips, such as those used for payment cards and building security, as well as passports.

The draft report also concludes that, “RFID appears to offer little benefit when compared to the consequences it brings for privacy and data integrity.”  Although there is good reason for opposing the card, the government has already began issuing them—hence my classmate in the military. 

Responses to the draft report suggest there is not enough factual content and that the reality is that RFID is already here.  In addition, “the State Department announced that it would soon be issuing new cards for visitors to Mexico, Canada and the Bermudas containing a chip that could be read from 20 feet away.  Changes in federal law will require Americans to have either a passport or the new “PASS card” to re-enter the country by air in 2007. Currently a driver’s license will suffice to get an American back inside the country from these neighboring spots, but starting in 2008 that won’t suffice even for quick, cross-border jaunts by car.”

So there you have it, the cards are on their way…whether we like it  or not.  But here is a thought I leave you with, if the government’s argument against this advisory report is that “the cards are already here”, shouldn’t the issue be how to improve them? The article states that, “It’s unclear whether the new cards will have encryption or other measures to prevent skimming or forgery. That decision was left to the State Department, which will produce the card and has thus far remained mum on the privacy issues.”  Is it just me, or should the privacy issues been worked out before they started issuing the cards! Scary thought, (sorry fellow classmate). 

Leave a Response »