Practical Database Programming With Java

2.11 Create Oracle 10g XE Sample Database 77

Figure 2.56. The completed Course table.

Your finished Course, Student, and StudentCourse tables are shown in Figures 2.56–2.58, respectively.

2.11.3 Create the Constraints Between Tables

Now it is the time for us to set up the relationships between our five tables using the Primary and Foreign keys. Since we have already selected the Primary key for each table when we create and build those tables, therefore, we only need to take care of the Foreign keys and connect them with the associated Primary keys in the related tables. Let’s start from the first table, LogIn table.

2.11.3.1 Create the Constraints between the LogIn and Faculty Tables

Now let’s create the constraints between the LogIn and the Faculty tables by using a foreign key. Exactly, create a foreign key for the LogIn table and connect it to the primary key in the Faculty table. The faculty_id is a foreign key in the LogIn table but it is a primary key in the Faculty table. A one-to-many relationship is existed between the faculty_id in the Faculty table and the faculty_id in the LogIn table.

78 Chapter 2 Introduction to Databases

Figure 2.57. The completed Student table.

Figure 2.58. The completed StudentCourse table.

2.11 Create Oracle 10g XE Sample Database 79

Figure 2.59. Create the foreign key between the LogIn and the Faculty table.

Log on the Oracle Database 10g XE using the customer username, CSE_DEPT and the customer database password, and then open the home page of the Oracle Database 10g XE. Click the Object Browser icon and select Browse|Table to list all tables. Select the LogIn table from the left pane to open it, click the Constraints tab, and then click the Create button that is the first button in the second row. Enter LOGIN_FACULTY_FK into the Constraint Name box, and select the Foreign Key from the Constraint Type box, which is shown in Figure 2.59. Check the On Delete Cascade checkbox. Then select the FACULTY_ID from the LogIn table as the foreign key column. Select the FACULTY table from the ReferenceTable Name box as the reference table,and select the FACULTY_ ID from the Reference Table Column List as the reference table column. Your finished Add Constraint window should match one that is shown in Figure 2.59.

Click the Next button to go to the next window, and then click the Finish button to confirm this foreign key’s creation.

2.11.3.2 Create the Constraints between the LogIn and Student Tables

The relationship between the Student table and the LogIn table is a one-to-many relationship. The student_id in the Student table is a primary key, but the student_id in the

LogIn table is a foreign key. Multiple student_id can be existed in the LogIn table, but only one or unique student_id can be found from the Student table.

To create a foreign key from the LogIn table and connect it to the primary key in the Student table, open the LogIn table if it is not opened, and click the Constraints tab, and then click the Create button that is the first button in the second row to open the

80 Chapter 2 Introduction to Databases

Figure 2.60. Create the foreign key between the LogIn and the Student table.

Add Constraint window. Enter LOGIN_STUDENT_FK into the Constraint Name box, and select the Foreign Key from the Constraint Type box, which is shown in Figure 2.60. Check the On Delete Cascade checkbox. Then select the STUDENT_ID from the LogIn table as the foreign key column. Select the STUDENT table from the Reference Table Name box as the reference table, and select the STUDENT_ID from the Reference Table Column List as the reference table column. Your finished Add Constraint window should match one that is shown in Figure 2.60.

Recall that when we created the LogIn table in Section 2.11.2.1, we emphasized that for the blank fields in both faculty_id and student_id columns, don’t place a NULL into these fields and just leave those fields blank.The reason for this is that an ALTER TABLE command will be issued when you create a foreign key for the LogIn table, and the NULL cannot be recognized by this command; therefore, an error ORA-02298 occurs, and your creation of foreign key will fail.

Click the Next button to go to the next window, and then click the Finish button to confirm this foreign key’s creation. Your finished foreign key creation window for the LogIn table should match one that is shown in Figure 2.61.

2.11.3.3 Create the Constraints between the Course and Faculty Tables

The relationship between the Faculty table and the Course table is a one-to-many relationship. The faculty_id in the Faculty table is a primary key, but it is a foreign key in the Course table. This means that only unique faculty_id is existed in the Faculty table but

2.11 Create Oracle 10g XE Sample Database 81

Figure 2.61. The finished foreign key creation window for the LogIn table.

Figure 2.62. Create the foreign key between the Course and the Faculty table.

multiple faculty_id can be existed in the Course table since one faculty can teach multiple courses.

Open the Course table by clicking it from the left pane. Click the Constraints tab and then click the Create button. Enter COURSE_FACULTY_FK into the Constraint Name box, and select the Foreign Key from the Constraint Type box, which is shown in Figure 2.62. Check the On Delete Cascade checkbox. Then select the FACULTY_ID from the Course table as the foreign key column. Select the FACULTY table from the Reference Table Name box as the reference table, and select the FACULTY_ID from the Reference

82 Chapter 2 Introduction to Databases

Figure 2.63. The finished foreign key creation window for the Course table.

Table Column List as the reference table column. Your finished Add Constraint window should match one that is shown in Figure 2.62.

Click the Next button to go to the next window, and then click the Finish button to confirm this foreign key’s creation. Your finished foreign key creation window for the Course table should match one that is shown in Figure 2.63.

2.11.3.4 Create the Constraints between the StudentCourse and Student Tables

The relationship between the Student table and the StudentCourse table is a one-to-many relationship. The primary key student_id in the Student table is a foreign key in the StudentCourse table, since one student can take multiple different courses. In order to create this relationship by using the foreign key, first, let’s open the StudentCourse table.

Click the Constraints tab and then click the Create button that is the first button on the second row. Enter STUDENTCOURSE_STUDENT_FK into the Constraint Name box, and select the Foreign Key from the Constraint Type box, which is shown in Figure 2.64. Check the On Delete Cascade checkbox. Then select the STUDENT_ID from the StudentCourse table as the foreign key column. Select the STUDENT table from the Reference Table Name box as the reference table, and select the STUDENT_ID from the Reference Table Column List as the reference table column. Your finished Add Constraint window should match one that is shown in Figure 2.64.

Click the Next button to go to the next window, and then click the Finish button to confirm this foreign key’s creation.

2.11.3.5 Create the Constraints between the StudentCourse and Course Tables

The relationship between the Course table and the StudentCourse table is one-to-many relationship. The primary key course_id in the Course table is a foreign key in the

2.11 Create Oracle 10g XE Sample Database 83

Figure 2.64. Create the foreign key between the StudentCourse and the Student table.

StudentCourse table, since one course can be taken by multiple different students. By using the StudentCourse table as an intermediate table, a many-to-many relationship can be built between the Student table and the Course table.

To create this relationship by using the foreign key, open the StudentCourse table by clicking it from the left pane. Click the Constraints tab and then click the Create button, which is the first button on the second row. Enter STUDENTCOURSE_COURSE_FK into the Constraint Name box, and select the Foreign Key from the Constraint Type box, which is shown in Figure 2.65. Check the On Delete Cascade checkbox. Then select the COURSE_ID from the StudentCourse table as the foreign key column. Select the COURSE table from the Reference Table Name box as the reference table, and select the COURSE_ID from the Reference Table Column List as the reference table column. Your finished Add Constraint window should match one that is shown in Figure 2.65.

Click the Next button to go to the next window, and then click the Finish button to confirm this foreign key’s creation. Your finished foreign key creation window for the StudentCourse table should match one that is shown in Figure 2.66.

Our customer database creation for Oracle Database 10g Express Edition is completed. A completed Oracle 10g XE sample database CSE_DEPT that is represented by a group of table files can be found from the folder Oracle that is located at the site http:// www.xxxxxx.org/bai/database.

At this point, we have finished developing and creating all sample databases we need to use later. All of these sample databases will be utilized for the different applications we will develop in this book.

84 Chapter 2 Introduction to Databases

Figure 2.65. Create the foreign key between the StudentCourse and the Course table.

Figure 2.66. The finished foreign key creation window for the StudentCourse table.

Since the Oracle Database 10g XE is very different with other databases, such as Microsoft Access and SQL Server 2008, you need to refer to Appendix G to get a clear picture about how to use this CSE_DEPT Oracle database files. Refer to Appendix F to get the knowledge in how to use the Utilities of Oracle Database 10g XE to Unload the five tables to five Text files, and how to Load those five table files into a new customer Oracle database to create a new customer Oracle database easily.

Homework 85

2.12 CHAPTER SUMMARY

A detailed discussion and analysis of the structure and components about databases are provided in this chapter. Some key technologies in developing and designing database are also given and discussed in this part. The procedure and components to develop a relational database are analyzed in detail with some real data tables in our sample database CSE_DEPT. The process in developing and building a sample database is discussed in detailed with the following points:

•Defining relationships

•Normalizing the data

•Implementing the relational database

In the second part of this chapter, three sample databases that are developed with three popular database management systems, such as Microsoft Access 2007, SQL Server 2008, and Oracle Database 10g XE are provided in detail. All of these three sample databases will be used in the following chapters throughout the whole book.

HOMEWORK

I. True/False Selections

_____1. Database development process involves project planning, problem analysis, logical design, physical design, implementation, and maintenance

_____2. Duplication of data creates problems with data integrity.

_____3. If the primary key consists of a single column, then the table in 1NF is automatically in 2NF.

_____4. A table is in first normal form if there are no repeating groups of data in any column.

_____5. When a user perceives the database as made up of tables, it is called a Network Model.

_____6. Entity integrity rule states that no attribute that is a member of the primary (composite) key may accept a null value.

_____7. When creating data tables for the Microsoft Access database, a blank field can be kept as a blank without any letter in it.

_____8. To create data tables in SQL Server database, a blank field can be kept as a blank without any letter in it.

_____9. The name of each data table in SQL Server database must be prefixed by the keyword dbo.

____10. The Sequence object in Oracle database is used to automatically create a sequence of numeric numbers that work as the primary keys.

II. Multiple Choices

1.There are many advantages to using an integrated database approach over that of a file processing approach. These include

a.Minimizing data redundancy

b.Improving security

86Chapter 2 Introduction to Databases

c.Data independence

d.All of the above

2.Entity integrity rule implies that no attribute that is a member of the primary key may accept

_______

a.Null value

b.Integer data type

c.Character data type

d.Real data type

3.Reducing data redundancy will lead to _____

a.Deletion anomalies

b.Data consistency

c.Loss of efficiency

d.None of the above

4.______ keys are used to create relationships among various tables in a database

a.Primary keys

b.Candidate keys

c.Foreign keys

d.Composite keys

5.In a small university, the department of Computer Science has six faculty members. However, each faculty member belongs to only the computer science department. This type of relationship is called _________

a.One-to-one

b.One-to-many

c.Many-to-many

d.None of the above

6.The Client Server databases have several advantages over the File Server databases. These include ________

a.Minimizing chances of crashes

b.Provision of features for recovery

c.Enforcement of security

d.Efficient use of the network

e.All of the above

7.One can create the foreign keys between tables ______

a.Before any table can be created

b.When some tables are created

c.After all tables are created

d.With no limitations

8.To create foreign keys between tables, first, one must select the table that contains a _______

key, and then select another table that has a _______ key.

a.Primary, foreign

b.Primary, primary

c.Foreign, primary

d.Foreign, foreign