Last week you saw how to create sequential files of objects in Java, and you could also have been introduced to random access files in Java. Rather than learning how to use random access files, you will learn about databases. Database systems provide file-processing capabilities that allow more than one user to access data in the same file and yet maintain data integrity. This means that if more than one user tries to change the same data one has to wait until the other has completed their changes. Database systems also provide ways to find patterns in data and to organize data in various ways.
It is possible to create a database that contains a single table from either a text file or table in a relational database such as one created in MS Access. You may click here to see how to create the text file database. While a database created from a text file gives maximum portablity since it is a pure text file that does not depend on software on any platform, databases created using a Database Management System (DBMS) that allow queries written in Structured Query Language (SQL) allow programs to filter data to find relationships. You will learn some basic SQL this week and see more as the semester progresses. You will now create a program that accesses the data in a database that contains one table. Get the file by clicking here . Next follow the steps for creating a single table database . <\P>
Last week you created a database for the Professor class and a program to maintain that database. This week you will consider the problem of designing a database for more than one interrelated classes and how to create a program that maintains the interrelationships. It will be convenient for the user to be able to see both lists and to edit each from the window containing the list. Get a copy of the database you will use by clicking here .
If you are writing a problem that simply has many non-interacting classes, you have no need to do anything different than we did in week 1. You can store each class as a table in one database and not worry about relations between table elements. Applications that fall into this category are very rare. By nature most application classes are tightly related to one another. The first step in solving a large problem is to design classes with the relationship between classes specified.
If you were asked to add a course list to the problem in week one, you could add this list as if it were part of a catalog and there would be no relationship between the professor list and the course list. On the other hand if you were asked to add the list of courses for a semester, you would find that course offerings have professors and professors have courses. What types of relationships between classes are possible?
First, you will want to know how many instances of one class are associated with each instance of a given class. In the semester course list you have multiple instances of course class associated with each professor and multiple professors class instances associated with each course class instance. The following is a simplified version of the different multiplicity relations a class can have. You can also require that there be at least one or more, a specific range of items associated (for example in the case of the professor you might require 2 to 4 instances of classes) and you can also require that there be an order amoung the multiple instances.
If you assume that neither the number of courses associated with a Professor nor the number of Professors associated with a course has no limitations, you would get the following diagram.
How does this translate into the design of the two classes? At the analysis stage you might just add a collection of one class to each class. For example the class Professor might now be given as:
public class Professor
{
String _lastName;
String _firstName;
String _initial;
String _office;
String _phone;
int _rank;
ArrayList sections;
};
Similarly you could add an array of Professors to the Course class. While this takes care of the multiplicity it doesn't truly represent the relationship between the classes in this problem. First, there is no connection that directly associates a course with a professor. Close inspection of the name of the array shows that you actually have one more class derived from course offerings - these are often called sections. Now your new class Section has the following multiplicity relationships.
This implementation is particularly well suited to relational databases like Access where you cannot store arrays in a row of a table, but can create links between items using their primary key. The relationship is entirely created in the class Section, so the only place the primary keys will appear in the class design is Section. The primary keys are maintained by the data base, so you do not want them to appear in your Professor or Course classes. Similary, the primary key for Section should not appear in the class Section. Section can be defined as follows:
public class Section
{
String _sectionNumber;
int _professor; // primary key of the professor
int _course; //primary key of the course
};
These class relations are created using Access.
Implement a Java program to show the list of course taught by a professor selected from a database table. This information is displayed in another table on the same page. In this case when a professor is selected in the course sections that professor is teaching show up on the list below. This is a second generation Master/Detail table. You will first build a simple Master/Detail table and then this table is constructed from the first.
In the design phase of this project, you created a section table that contained the CourseId and the ProfId. The first step in retrieving all of the sections the professor is teaching is to create two single tables in a JSplitPane.
| Select the JSplitPane component from the Swing Container tab and place it in the design tree. Next add two TableScollPanes to the JSplitPane (droping them on the JSplitPane in the tree). Finally add two jdbTables (one in each of the TableScollPanes) using the tree. | |
| Click on the JSplitPane in the tree and edit its properties as shown in the red ovals. | |
| Create queries SELECT * FROM Professor and SELECT * FROM Sections. You should see this window if you run your program now. There is no connection between the top and the bottom table. Notice that for a professor id there may be more than one entry in the sections table. The Professor table is known as the Master table in this case and the Sections table is the Detail. The ProfId is the primary key in the Professor table, while it is a foreign key in the Section table. | |
| You need to change the query that retrieves all sections from the section table to establish the control of the Professor table on the Section Table. In the information pane of the query that selects everything from the Section table, find the master link entry. Click on the ellipse (...) and you will see the Master Link Dialog. Because the tables share the column name ProfId, the dialog shows the master link and detail link as ProfId. If you had two different names for these identifiers, you would have to add them by choosing from the second tier of listboxes. Make sure none of the checkboxes are checked and test to see if this link is possible. | |
| If all is ok, run the program and you should see this window. | |
The query SELECT * FROM Sections gives you a list of sections, but is not very informative since most of what appears are database identifiers. This query (which is actually a table) can act as the base for a second query that will display the section number, course number, course name and course description.
| Since most of you are not experts in SQL, you can use Access to generate your SQL. Create a new query from the query pane of access. After clicking on new, choose the Design View for creating your query. The table dialog appears, and you should select the Sections and Course tables. |  |
| At this point you will see that the two tables are already joined by the keys that link them. In the grid below enter the columns you want to appear in the table you are creating. Notice you will not put restrictions on these items. The first column should be Sections, number, but this can be fixed later.You will notice that even though you will not display the ProfId, it is included in the query. This is because it is the detail key you need to connect this new query table with the Professor table. |  |
| Next get the SQL, by selecting SQL from the view menu of Access. Copy this code and return to JBuilder. There create a new query and when you get the Query dialog, just paste the SQL from Access. You may always modify the code here. For example if you want the section number first, just add it right after select (see code in example project). |  |
| Now when you click on the master link, you again want the master table to be the professor table, so select the Master Data set to be the same query you used in the first part of this project. Because you included the ProfId in the new query, it is automatically selected as the master and detail key. Link this query to the bottom table and you will now have the program you set out to build. |  |
Click here to get the complete code for this program.
Complete code for the Master/Detail Java program.