BIT320 Remix: Databases Archives

Helpful Hint

Requirement #6 asks us to generate a “show all tables and columns” report. At first we weren’t quite sure what this meant. After some exploration, we discovered the list_column_info_for_all.sql in our SQL directories. This should pull together all the tables and views.

Hope this helps!

In Kevin's Blog, 12/19/2005 | Original | Archive | Post to del.icio.us | Technorati

RSS - Team Goes for Bonus

Matt and I have been working on the RSS feed all night and we think we finally got it working! The feed is located at the following URL: http://elab-linux4.bus.umich.edu:8080/%7Ekevlers/RSS/testRSS.xsql (Bud - can you tell us if we’re all set with the feed)

The purpose of the feed is to notify customers of new store additions to the database. This is especially important to customers who do not have any stores in their immediate area. They can use the feed as a watch-list to see if any stores start carrying Denali Flavors. The xsql file pulls the information directly from the database, so it should be a valid feed, as opposed to just a well-formed document.

If any other teams are attempting to get the RSS feed working, I would recommend looking at this web site.

In Kevin's Blog, 12/19/2005 | Original | Archive | Post to del.icio.us | Technorati

Access to MySQL

Article explaining how to migrate a database from Microsoft Office Access to MySQL. Also explains the benefits of each

In del.icio.us/kevlers, 12/11/2005 | Original | Archive

Error Codes and what they mean

Programming can be difficult, especially when errors occur. Luckily, when they do happen, XSLT produces a number of different error codes to help troubleshoot the issue. The error codes produced by Shady Waters’ code helped me determine what the cause of the problem is.

The first error code, ORA-06550, gives the line and column location of the SQL block where there was an error.

The second error code, ORA-00947, was a little more helpful. This code occurs when an SQL statement requires two sets of values equal in number, and the second set has fewer values than the first set.

By typing these error codes into Google (or if you want to make me happy: search.msn.com), you can see the cause as well as the solution.

In Kevin's Blog, 12/08/2005 | Original | Archive | Post to del.icio.us | Technorati

Dynamically Generated Lists… not good enough

Dynamically generated lists are all well and good, but is there a way to alphabetize the list? Especially once the list of stores or ZIP codes starts increasing in size, it can become difficult to find specific values. In an effort to make it as easy as possible for the user, there must be someway to better organize the list. Any suggestions Bud? Thanks.

In Kevin's Blog, 12/08/2005 | Original | Archive | Post to del.icio.us | Technorati

Form Validation

Our team has successfully implemented automatically generating primary keys as well as drop down lists, as shown here. Ensuring accurate data entry through forms is critical. Dynamically generated lists provide a way to avoid the risk of collisions. However, there is still the problem of users submitting incorrect values in other fields. To eliminate this problem, we have experimented with form validation techniques. For example, in the telephone number field we have applied maxlength=”10″ to limit the number of integers a user can enter. With JavaScript, it is possible to verify the type of data entered and to check for blank fields. This prevents the user from submitting the form until all data is entered accurately.

In Kevin's Blog, 12/08/2005 | Original | Archive | Post to del.icio.us | Technorati

JB’s problems in her ice cream cone

JB’s group has an error. We are trying to create a customer input form. We can get the page up with the source. check it out here. But the problem we have is when we input information on a customer and we get an error that looks like this!

This is the code we used to insert the info. We pretty much took the examples from class and changed it to our specific case.

bind-params=”customerID zip flavor”>
BEGIN
INSERT INTO customer VALUES(?,?,?);
COMMIT;
END;

p>

Help us please…otherwise JB can’t eat her ice cream

In Shady Waters, 12/07/2005 | Original | Archive | Post to del.icio.us | Technorati

Google Base

http://www.storyhost.com/kevin/googleBase.gif alt=”Google Base”>
The November 24th issue of Nature has an interesting article on Google Base, which prompted me to search for it on the web. Google Base is described as “a place where you can add all types of information that we’ll host and make searchable online.” All types of information includes jobs, products, recipes, reviews, wanted ads, etc. Based on how relevant a listing is, Google may decide to include these listing in their search index or on Froogle. This link has a more detailed description of the service. Of course, Microsoft is following Google with the release of Fremont, which will focus on classified announcements of products and services for sale, by either individuals or commercial entities. Both of these services harness the power of databases and allow users to enter details of personal items. The database is then searchable by the general public or a restricted group of personal contacts.

In Kevin's Blog, 12/02/2005 | Original | Archive | Post to del.icio.us | Technorati

Data Mining Resources

Starting point for information on data mining.

In del.icio.us/kevlers, 11/26/2005 | Original | Archive

Database Foundry

Project descriptions, news, guides, forums, and articles.

In del.icio.us/kevlers, 11/26/2005 | Original | Archive

Kevin’s Notes: XML and Databases

After two weeks of eagerly exploring XML, I’ve started to notice several similarities between XML and databases. Although XML is not relational, like Oracle databases, there are still many similarities. In Oracle, the database is structured with tables containing fields; fields containing data. In the same way, XML stores data using a tag hierarchy. The tags store other tags (like tables containing fields) and then additional sub-tags contain data (like fields containing data). An XML document can contain hundreds of records by repeating tags and changing the data between the tags.

For more detailed information about XML, I would highly recommend looking at Tigerlily’s contribution to the class knowledge.

In Kevin's Blog, 11/13/2005 | Original | Archive | Post to del.icio.us | Technorati

Assuming the Test Away

Kevin brought up two different questions regarding ambiguities in the the SQL Guide and also in the example problem solutions. I think its about the assumptions one uses in approching the problems.

In the SQL Guide, the real question is what does the book mean by “update”. I thought at first that it was that you could change a specific value in a column but that not might not be the meaning behind the book. It might have meant that you can put more information in. Actually, it does because I just looked at the section heading and it says “Adding New Rows to an Existing Table” I think it says it all

Also, for the problems solutions, is it possible that there is more than one solution. When I go and compare my answers, i rarely have a exactly similar answer because once again, my assumptions of what is and isn’t possible shapes how I view the problems and the lines I draw. Makes me remember, when I’m doing a problem, to really consider what I’m assuming. I think Bud will try and make the test as understandable as possible, but he’s going to be slightly specific in regards to problem set up ;D

In Shady Waters, 10/25/2005 | Original | Archive | Post to del.icio.us | Technorati

Having Clause

In response to Pink Footsie’s question about the HAVING clause:

I think of the having class as being almost identical to the where clause, except for the condition that follows, as illustrated below:

WHERE condition
HAVING condition involving groups

The HAVING clause indicates a condition for groups. Only groups where the condition is true are returned in the query results. The condition can refer to any attribute, including non-primary key attributes.

As an example, say there is a table called CUSTOMERS with the following attributes: CUSTOMER_ID, DIVISION, GROUP_ID

You might want to run the following query:

SELECT CUSTOMER_ID, GROUP_ID
FROM CUSTOMERS
GROUP BY CUSTOMER_ID
HAVING GROUP_ID = 0;

In this case, the having clause includes a non-primary key attribute. I hope this answers your question.

In Kevin's Blog, 10/24/2005 | Original | Archive | Post to del.icio.us | Technorati

Normalization Problem 8

In response to zee124’s question about normalization problem 8:

With the invoice number, you can determine the customer. With the customer number, you cannot determine the invoice number, as a customer may have multiple invoices. While the data does not show it, it is still our objective to model reality. It makes sense that a customer may have placed many orders in the past, which would all have different invoice numbers.

The table will look as follows in 3NF:

[Invoice #, part #] > # used
Invoice # > [date, custID, tax rate]
part # > [desc, price]
custID > cust_name

In Kevin's Blog, 10/24/2005 | Original | Archive | Post to del.icio.us | Technorati

A Guide to SQL

On page 143, in the seventh edition of A Guide to SQL, the text says “you can also use the INSERT command to update table data.” Yet, in the example, we only add new data to the table, just like we’ve been doing all along. Can the INSERT command be used to update data or is this just an error in the book?

In Kevin's Blog, 10/24/2005 | Original | Archive | Post to del.icio.us | Technorati

The brain is still smarter than the computer

As I was repracticing some commands in oracle I noticed that you can’t uniquely identify several aggregate functions in one command (ie. identify by customer_name, sum(credit_limit), ave(credit_limit) and so on even when you try to split up each command on a unique line with customer_name repeated each time). So it’s best to do these individually by either retyping the command (if short) in oracle or saving it in jEdit and then augmenting it to get new info. By the way can we link somehow to jEdit files or Oracle since they’re not webbased? All the sql assignments are found in the sql book and chapter covered are found here.

In Pink Footsie, 10/22/2005 | Original | Archive | Post to del.icio.us | Technorati

Is Oracle About to Get Netscaped?

The article is a little dated but nevertheless its about databases, Oracle and the future.

In del.icio.us/tigerlily23, 10/20/2005 | Original | Archive

Data Types

When creating a table in Oracle that contains more than one column as the primary key, where do we specify the data type of the key? In the premiere database order_line table, the combination of order_num and part_num create the primary key. When creating this table, we did not specify a data type. However, when I run the command “describe order_line” Oracle appears to have assigned a data type. Is this because the table is an associative entity and Oracle can, as a result, determine the data type?

In Kevin's Blog, 10/18/2005 | Original | Archive | Post to del.icio.us | Technorati

Advertising is bothering me too

Like both Pink Footsie and William, I am becoming ever more irritated by irrelevant and valueless communications from companies. As a matter of fact, I’m starting to ignore most advertisements altogether. With cutting edge database platforms and sophisticated analytics, marketers have all the tools they need to develop more relevant and targeted communications… so please stop the shot gun approach.

In Kevin's Blog, 10/18/2005 | Original | Archive | Post to del.icio.us | Technorati

Creating SQL Files

Like Blogstar and TigerLily, I was having trouble connecting to the server, even after installing the plugin. So I began editing the SQL files in my favorite editor for Mac, SubEthaEdit, and then uploading them to the server via FTP. However, I realized I could edit files directly from the Oracle server. While logged into SQLPLUS, I typed “edit ‘filename’ “. It created a new file for me, I editied my SQL statements, saved it and exited. The files were saved in my SQL directory and were immediately available for running. Right now, this will probably be my preferred way of creating/editing files. The only downside is that PICO doesn’t provide any syntax hints.

In Matt's Musings, 10/12/2005 | Original | Archive | Post to del.icio.us | Technorati

Data Modeling: Entity-Relationship Model

Discussion of ER modeling

In del.icio.us/mridge, 10/08/2005 | Original | Archive

Literary Databases

In one of my other classes this semester we are focusing on the fusion of Technology & the Humanities. One of the more interesting parts of the class so far focused on databases and digital text. With the advent of databases and sophisticated optical character recognition, thousands of documents have been digitized and stored in databases.

This has opened up new ways to examine data. For example, through the resources of U of M’s very own Humanities Text Initiative, we can now look for every single instance where the words “heart” and “mind” appear less then 120 characters apart in every single work of a specific author. This opens up vast new areas of research possibilities where it was too time-consuming before. You can try a search like this here.

In Matt's Musings, 10/05/2005 | Original | Archive | Post to del.icio.us | Technorati

Visio

A few days ago JB asked:

If I were to map out (in a drawing of some sort) the information that will be included in my database, does Visio have the ability to turn it into entity types and such? In other words, can it do what we do in our BIT classes when we take our diagrams and turn them into tables?

Supriya followed up by saying:

I think it’s a great program to make flow diagrams. It gives you several options to choose between block diagrams, 3-Ds etc. Moreover, it’s extremely user friendly, and easy to learn. I am actually surprised that we haven’t used it this far for any of our classes, given its excellent features. I definitely recommend it to anyone trying to make flow diagrams.

Supriya summed up Visio well. Visio is mainly for drawing layouts. It only helps provide a map of how the database should be designed; it does not actually convert the diagram in to a database.

In Kevin's Blog, 10/03/2005 | Original | Archive | Post to del.icio.us | Technorati

Reportizer

A database reporting tool

In del.icio.us/tigerlily23, 09/30/2005 | Original | Archive

Apple Recognizes the Right-Click, Finally

Discusses Apple's slick new multibutton mouse and freeform database.

In del.icio.us/tigerlily23, 09/30/2005 | Original | Archive

Microsoft Visio

Kevin put in the following plug for Microsoft Visio:

Given that I’m most likely going back to work full-time at Microsoft next year, I had to throw in an endorsement for Microsoft Visio. Microsoft Visio is a very good tool to document databases. The program includes drawing shapes that can be used to define tables, fields, relationships, etc. Visio provides database designers an easy way to map out the basic structure of a database.

I checked out the website and the program does sound helpful, especially to businesses. However, I still have the following question: If I were to map out (in a drawing of some sort) the information that will be included in my database, does Visio have the ability to turn it into entity types and such? In other words, can it do what we do in our BIT classes when we take our diagrams and turn them into tables? Or, is the shape tool just an easy way to plan but not a shortcut to creating the actual database? In addition, does Visio consider Normal Form? I might take a closer look at the Visio website, but I thought that Kevin (being the Microsoft guru that he is) might be able to provide some quick answers.

In jb's blog, 09/27/2005 | Original | Archive | Post to del.icio.us | Technorati

Analyzing a Database’s Design

Since I’m already on a roll tonight, I decided to post one more entry to my blog. Once we create our database and establish all of the relationships, are there any available tools in Oracle that can analyze our database’s design? I know Microsoft Access has a table analyzer which actually goes through all of the tables and recommends any changes that would result in a better structure. This would make me feel better knowing that there is some backup in place to check my design

In Kevin's Blog, 09/26/2005 | Original | Archive | Post to del.icio.us | Technorati

Microsoft Visio

Given that I’m most likely going back to work full-time at Microsoft next year, I had to throw in an endorsement for Microsoft Visio. Microsoft Visio is a very good tool to document databases. The program includes drawing shapes that can be used to define tables, fields, relationships, etc. Visio provides database designers an easy way to map out the basic structure of a database.

In Kevin's Blog, 09/26/2005 | Original | Archive | Post to del.icio.us | Technorati

All about relationships

No, this isn’t gossip; it’s all about relational databases! From what I understand from class, tables in a relational database can have three different types of relationships. These include one-to-one, one-to-many, and many-to-many. Tables in a many-to-many relationship create a third table, or a junction table. Each table should only focus on one subject and the relationships should bring related information together. Sounds easy enough… now hopefully the planning and designing of our database project goes this smoothly!

In Kevin's Blog, 09/26/2005 | Original | Archive | Post to del.icio.us | Technorati

John Nardini’s Upcoming Class Visit

I am really excited about John Nardini’s upcoming class visit. It is great that John is taking the time to visit our class to discuss the database project with us. Also, working on a real-life problem makes this assignment all the more interesting.

John Nardini is the Senior VP of Denali Flavors.

In Kevin's Blog, 09/26/2005 | Original | Archive | Post to del.icio.us | Technorati

Managing Next-Generation IT Infrastructure - Forbes.com

I forgot to link this to my IT Infrastructure blog. This is the article I was talking about if anyone wants to refer to it!

In del.icio.us/zeenah, 09/22/2005 | Original | Archive

Normalizing my life

I like this website for Normalization. It seems like an idiot’s guide but those are my favorite books, of which there are many on all kinds of subjects, btw. I think the exercise we did in class was very helpful. I’m not much for definitions so when I actually get to see what it means I’m a very happy camper. I love puzzles. I’m weird like that.

In Pink Footsie, 09/16/2005 | Original | Archive | Post to del.icio.us | Technorati

Addition to “Electronic Medical Records”

I was listening to the news and they were saying how beneficial it would have to have electronic medical records, as a result of Katrina. A doctor made copies of parts of medical records so that patients that were transported to another hospital would receive the correct medicine and dosage without having to be seen and diagnosed again by another doctor. If the records were in electronic format, this whole process would have been easier. Just another benefit to this idea.

In Tigerlily's Blog, 09/16/2005 | Original | Archive | Post to del.icio.us | Technorati

Electronic Medical Records

I was reading this article about the possibility of physicians using electronic medical records. I think this is such a great idea. Having your medical records in an online database would save money and time. Just think, if you had to visit a doctor other than your regular doctor, they could just search online and find your information instantly. Although, as with any good idea there are cons. There would need to be a serious security system that would provent hackers from getting information. Also, it would cost a lot to update the technology so that doctors would be able to do their paperwork electronically. This would also take a long time to set up. However, I think using an online database to store medical records is an idea that should be pursued because it is a convenience for everyone invovled and it will probably happen at some point in the future.

In Tigerlily's Blog, 09/16/2005 | Original | Archive | Post to del.icio.us | Technorati

Physician, wire thyself

"computerizing" the way you see a doctor may be what the medical field needs

In del.icio.us/tigerlily23, 09/15/2005 | Original | Archive

SuperKey Explained

In response to this post.

From what I understand, a super key is a column or set of columns that can be used to identify a unique record in a table. So, for instance, if you had a table with employee ID, name, job title, and salary, you could use the employee ID together with the name to identify any record in the table. Alternatively, you can also use all the columns (employee ID, name, job title, salary) to identify any record. These are both superkeys.

In Kevin's Blog, 09/15/2005 | Original | Archive | Post to del.icio.us | Technorati

Candidate Key

In response to the following post.

Each table in a database must have a unique identifier, or a primary key. When searching for a primary key, it is the best to use the simplest data that provides a unique value. You should almost always be able to find a field or some combination of fields whose values are unique across all rows – these are called candidate keys. You should then consider the simplest combination of fields as the best candidate to be your primary key.

In Kevin's Blog, 09/15/2005 | Original | Archive | Post to del.icio.us | Technorati

Thoughts on Normalization

I think of normalization as a way of minimizing the amount of duplicate data in a database. In the end there may be more tables, but it certainly makes retrieving records and updating databases much easier and also helps manage the accuracy and consistency of the data. I’m starting to realize the importance of normalization and how crucial it is in laying the foundation for optimal database and application performance.

Btw… is there ever a need to denormalize a database? It seems that with especially large amounts of data, performance would be better with fewer tables and links. Let me know your thoughts on this.

In Kevin's Blog, 09/15/2005 | Original | Archive | Post to del.icio.us | Technorati

SQL, XML, & Ice Cream

A remixable web learning project at Michigan's Ross School of Business

BIT320 Remix — Databases

Of Special Note

Search

Browse By Topic