A one-to-many (1-N) relationship has an employee work for a department, which can have many employees. The database implementation of the one-to-many relationship is given in this slide:
⇓ Transforming (1-N relationship)
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
DROP TABLE employee2 FORCE;
DROP TABLE department2 FORCE;
DROP SEQUENCE department2_seq;
CREATE SEQUENCE department2_seq START WITH 1 INCREMENT BY 1 CACHE 100;
CREATE TABLE department2 (
did INTEGER PRIMARY KEY,
name VARCHAR(32) NOT NULL UNIQUE,
location VARCHAR(32) NOT NULL );
INSERT INTO department2 VALUES( department2_seq.NEXTVAL,
'Computer Science', 'Streibel Hall' );
INSERT INTO department2 VALUES( department2_seq.NEXTVAL,
'Electrical Engineering', 'Harrington Hall' );
INSERT INTO department2 VALUES( department2_seq.NEXTVAL,
'Data Science and Analytics', 'Upson II' );
INSERT INTO department2 VALUES( department2_seq.NEXTVAL,
'Data Science', 'CEC' );
INSERT INTO department2 VALUES( department2_seq.NEXTVAL,
'Chemical Engineering', 'Upson II' );
INSERT INTO department2 VALUES( department2_seq.NEXTVAL,
'Chemistry', 'Starcher Hall' );
INSERT INTO department2 VALUES( department2_seq.NEXTVAL,
'Medical School', 'Columbia Hall' );
DROP SEQUENCE employee2_seq;
CREATE SEQUENCE employee2_seq START WITH 1 INCREMENT BY 1 CACHE 100;
CREATE TABLE employee2 (
eid INTEGER PRIMARY KEY,
did INTEGER NOT NULL,
name VARCHAR(32) NOT NULL,
email VARCHAR(32) NOT NULL,
FOREIGN KEY ( did ) REFERENCES department2( did ) ON DELETE CASCADE );
INSERT INTO employee2
SELECT employee2_seq.NEXTVAL, did, 'Poke Mon', 'poke@gmail.com'
FROM department2 WHERE name='Computer Science';
INSERT INTO employee2
SELECT employee2_seq.NEXTVAL, did, 'Digi Mon', 'digi@und.edu'
FROM department2 WHERE name='Electrical Engineering';
INSERT INTO employee2
SELECT employee2_seq.NEXTVAL, did, 'Sponge Bob', 'sponge@gmail.com'
FROM department2 WHERE name='Chemical Engineering';
INSERT INTO employee2
SELECT employee2_seq.NEXTVAL, did, 'Super Mario', 'mario@cs.und.edu'
FROM department2 WHERE name='Computer Science';
INSERT INTO employee2
SELECT employee2_seq.NEXTVAL, did, 'Luigi', 'luigi@gmail.com'
FROM department2 WHERE name='Computer Science';
INSERT INTO employee2
SELECT employee2_seq.NEXTVAL, did, 'Super Man', 'super@hotmail.com'
FROM department2 WHERE name='Chemical Engineering';
INSERT INTO employee2
SELECT employee2_seq.NEXTVAL, did, 'Spider Man', 'spider@gmail.com'
FROM department2 WHERE name='Electrical Engineering';
|
-- Insert a department
INSERT INTO department2 VALUES(
department2_seq.NEXTVAL, 'Early Education', 'Education' );
-- Insert an employee
INSERT INTO employee2
SELECT employee2_seq.NEXTVAL, d.did, 'Iron Man', 'iron.man@und.edu'
FROM department2 d WHERE d.name='Education';
-- Delete employees
DELETE employee2 WHERE UPPER(email) LIKE '%@GMAIL.COM%';
-- Delete departments
DELETE department2 WHERE UPPER(name) LIKE '%SCIENCE%';
-- Update a location
UPDATE department2 SET location='Upson II'
WHERE UPPER(name) ='ELECTRICAL ENGINEERING';
-- Select employees
SELECT eid, e.name, email, d.name FROM employee2 e, department2 d
WHERE UPPER(e.name) LIKE '%MAN%' AND e.did = d.did
ORDER BY eid;
-- Select departments
SELECT d.did, d.name, location, COUNT(e.name) FROM employee2 e, department2 d
WHERE UPPER(d.name) LIKE '%SCIENCE%' AND e.did = d.did
GROUP BY d.did, d.name, location
ORDER BY d.did;
SELECT DISTINCT e.name FROM employee2 e, department2 d
WHERE e.did = d.did AND d.did = 1;
SELECT d.did, d.name, location, 0 FROM department2 d
WHERE UPPER(d.name) LIKE '%SCIENCE%' AND
NOT EXISTS ( SELECT e.eid FROM employee2 e WHERE e.did=d.did )
GROUP BY d.did, d.name, location
ORDER BY d.did;
|
|
|
#!/usr/bin/bash
# To set up the Oracle environment dynamically
CLASSPATH=.:/usr/lib/oracle/12.1/client64
CLASSPATH=$CLASSPATH:/usr/lib/oracle/12.1/client64/lib/ojdbc7.jar
CLASSPATH=$CLASSPATH:/usr/lib/oracle/12.1/client64/lib/ottclasses.zip
export CLASSPATH
# To set up the Oracle environment for SQL*Plus
export ORACLE_HOME=/usr/lib/oracle/12.2/client64
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib
export PATH=${ORACLE_HOME}/bin:$PATH
export CLASSPATH
# Execute a batch of SQL commands to create the database.
sqlplus C##user.id/password@//65.52.222.73:1521/cdb1 @One2Many_Create.sql
echo "The database is reset."
|
|
|
|
#!/usr/bin/bash # To set up the Oracle environment dynamically CLASSPATH=.:/usr/lib/oracle/12.1/client64 CLASSPATH=$CLASSPATH:/usr/lib/oracle/12.1/client64/lib/ojdbc7.jar CLASSPATH=$CLASSPATH:/usr/lib/oracle/12.1/client64/lib/ottclasses.zip export CLASSPATH # Calling the CGI Perl script /usr/bin/perl One2Many.pl |
|
|
|
#!/usr/bin/perl
use CGI;
$query = new CGI;
$act = $query->param('act');
$radio = $query->param('radio');
if ( $act eq "Submit" ) {
# Print HTML.
print ( "Content-type: text/html\n\n" );
# Use "here-doc" syntax.
print <<EndofHTML;
<html>
<body>
<center>
<table width="90%" height="80%">
<tr>
<td align="center">
<font size="+0" face="Arial Narrow">
EndofHTML
if ( $radio eq "Drop and create" ) {
print ( "sqlplus C##user.id/password@//65.52.222.73:1521/cdb1 \@Create.sql" );
print ( "Use the Create button to execute the above command." );
}
elsif ( $radio eq "Insert a department" ) {
$name = $query->param('dname1');
$location = $query->param('location1');
# Remove leading and trailing spacing.
$name =~ s/^\s*(\S*)\s*$/$1/;
$location =~ s/^\s*(\S*)\s*$/$1/;
# For security, remove some Unix metacharacters.
$name =~ s/;|>|>>|<|\*|\?|\&|\|//g;
$location =~ s/;|>|>>|<|\*|\?|\&|\|//g;
# Compose a Java command.
$cmd = "/usr/bin/java -Djava.security.egd=file:/dev/./urandom One2Many '";
$cmd .= $radio . "' '" . $name . "' '" . $location . "'";
print( $cmd ); system( $cmd );
}
elsif ( $radio eq "Insert an employee" ) {
$name = $query->param('ename1');
$email = $query->param('email1');
$department = $query->param('dname2');
# Remove leading and trailing spacing.
$name =~ s/^\s*(\S*)\s*$/$1/;
$email =~ s/^\s*(\S*)\s*$/$1/;
$department =~ s/^\s*(\S*)\s*$/$1/;
# For security, remove some Unix metacharacters.
$name =~ s/;|>|>>|<|\*|\?|\&|\|//g;
$email =~ s/;|>|>>|<|\*|\?|\&|\|//g;
$department =~ s/;|>|>>|<|\*|\?|\&|\|//g;
# Compose a Java command.
$cmd = "/usr/bin/java -Djava.security.egd=file:/dev/./urandom One2Many '";
$cmd .= $radio . "' '" . $name . "' '" . $email . "' '" . $department . "'";
print( $cmd ); system( $cmd );
}
elsif ( $radio eq "Delete employees" ) {
$email = $query->param('email2');
# Remove leading and trailing spacing.
$email =~ s/^\s*(\S*)\s*$/$1/;
# For security, remove some Unix metacharacters.
$email =~ s/;|>|>>|<|\*|\?|\&|\|//g;
# Compose a Java command.
$cmd = "/usr/bin/java -Djava.security.egd=file:/dev/./urandom One2Many '";
$cmd .= $radio . "' '" . uc($email) . "'";
print( $cmd ); system( $cmd );
}
elsif ( $radio eq "Delete departments" ) {
$name = $query->param('dname3');
# Remove leading and trailing spacing.
$name =~ s/^\s*(\S*)\s*$/$1/;
# For security, remove some Unix metacharacters.
$name =~ s/;|>|>>|<|\*|\?|\&|\|//g;
# Compose a Java command.
$cmd = "/usr/bin/java -Djava.security.egd=file:/dev/./urandom One2Many '";
$cmd .= $radio . "' '" . uc($name) . "'";
print( $cmd ); system( $cmd );
}
elsif ( $radio eq "Update a location" ) {
$name = $query->param('dname4');
$location = $query->param('location2');
# Remove leading and trailing spacing.
$name =~ s/^\s*(\S*)\s*$/$1/;
$location =~ s/^\s*(\S*)\s*$/$1/;
# For security, remove some Unix metacharacters.
$name =~ s/;|>|>>|<|\*|\?|\&|\|//g;
$location =~ s/;|>|>>|<|\*|\?|\&|\|//g;
# Compose a Java command.
$cmd = "/usr/bin/java -Djava.security.egd=file:/dev/./urandom One2Many '";
$cmd .= $radio . "' '" . uc($name) . "' '" . $location . "'";
print( $cmd ); system( $cmd );
}
elsif ( $radio eq "Select employees" ) {
$ename = $query->param('ename2');
# Remove leading and trailing spacing.
$ename =~ s/^\s*(\S*)\s*$/$1/;
# For security, remove some Unix metacharacters.
$ename =~ s/;|>|>>|<|\*|\?|\&|\|//g;
# Compose a Java command.
$cmd = "/usr/bin/java -Djava.security.egd=file:/dev/./urandom One2Many '";
$cmd .= $radio . "' '" . uc($ename) . "'";
print( $cmd ); system( $cmd );
}
elsif ( $radio eq "Select departments" ) {
$dname = $query->param('dname5');
# Remove leading and trailing spacing.
$dname =~ s/^\s*(\S*)\s*$/$1/;
# For security, remove some Unix metacharacters.
$dname =~ s/;|>|>>|<|\*|\?|\&|\|//g;
# Compose a Java command.
$cmd = "/usr/bin/java -Djava.security.egd=file:/dev/./urandom One2Many '";
$cmd .= $radio . "' '" . uc($dname) . "'";
print( $cmd ); system( $cmd );
}
print <<EndofHTML;
<form>
<input type="button" value=" Back " onclick="history.go(-1);return false;" />
</form>
</td>
</tr>
</table>
</body>
</html>
EndofHTML
}
elsif ( $act eq "HTML source" ) {
print ( "Content-type: text/plain\n\n" );
$cmd = "/usr/bin/lynx -dump -source " . $ENV{HTTP_REFERER};
$cmd .= "; echo \n\n\n\n";
system( $cmd );
}
elsif ( $act eq "CGI source" ) {
# Print plain text.
print ( "Content-type: text/plain\n\n" );
system( "/bin/cat One2Many.cgi; echo \n\n\n\n" );
}
elsif ( $act eq "Perl source" ) {
print ( "Content-type: text/plain\n\n" );
system( "/bin/cat One2Many.pl; echo \n\n\n\n" );
}
elsif ( $act eq "Java source" ) {
print ( "Content-type: text/plain\n\n" );
system( "/bin/cat One2Many_java.txt; echo \n\n\n\n" );
}
elsif ( $act eq "SQL source" ) {
print ( "Content-type: text/plain\n\n" );
system( "/bin/cat Create.sql; echo \n\n\n\n" );
system( "/bin/cat Manage.sql; echo \n\n\n\n" );
}
elsif ( $act eq "Help" ) {
print ( "Content-type: text/html\n\n" );
system( "/bin/cat Help.html" );
}
else {
print( "Content-type: text/html\n\n" );
print( "No such option: $act" );
}
|
|
|
|
/*******************************************************************
* *
* This program shows how to process a one-to-many relationship. *
* *
*******************************************************************/
// Import the following packages and class to use JDBC.
import java.sql.*;
import java.io.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
class One2Many {
public static void main( String args[ ] ) throws SQLException {
String user = "C##user.id";
String password = "password";
String database = "65.52.222.73:1521/cdb1";
// Open an OracleDataSource and get a connection.
OracleDataSource ods = new OracleDataSource( );
ods.setURL ( "jdbc:oracle:thin:@" + database );
ods.setUser ( user );
ods.setPassword( password );
Connection conn = ods.getConnection( );
Statement stmt1, stmt2;
ResultSet rset1, rset2;
String query;
Integer did;
int i;
try {
stmt1 = conn.createStatement( );
stmt2 = conn.createStatement( );
if ( args[0].equals( "Insert a department" ) ) {
query = "INSERT INTO department2 VALUES( department2_seq.NEXTVAL, '";
query += args[1].trim( ) + "', '" + args[2].trim( ) + "' )";
stmt1.executeUpdate( query );
System.out.println( "The department is inserted." );
stmt1.close( );
}
else if ( args[0].equals( "Insert an employee" ) ) {
query = "INSERT INTO employee2 SELECT employee2_seq.NEXTVAL, d.did, '";
query += args[1].trim( ) + "', '" + args[2].trim( ) + "' ";
query += "FROM department2 d WHERE d.name='" + args[3].trim( ) + "'";
stmt1.executeUpdate( query );
System.out.println( "The employee is inserted." );
stmt1.close( );
}
else if ( args[0].equals( "Delete employees" ) ) {
query = "DELETE employee2 WHERE UPPER(email) LIKE '%" + args[1].trim( ) + "%'";
stmt1.executeUpdate( query );
System.out.println( "The employees are deleted." );
stmt1.close( );
}
else if ( args[0].equals( "Delete departments" ) ) {
query = "DELETE department2 WHERE UPPER(name) LIKE '%" + args[1].trim( ) + "%'";
stmt1.executeUpdate( query );
System.out.println( "The departments/employees are deleted." );
stmt1.close( );
}
else if ( args[0].equals( "Update a location" ) ) {
query = "UPDATE department2 SET location='" + args[2].trim( ) + "' ";
query += "WHERE UPPER(name) ='" + args[1].trim( ) + "'";
stmt1.executeUpdate( query );
System.out.println( "The location is updated." );
stmt1.close( );
}
else if ( args[0].equals( "Select employees" ) ) {
query = "SELECT eid, e.name, email, d.name FROM employee2 e, department2 d ";
query += "WHERE UPPER(e.name) LIKE '%" + args[1].trim( ) + "%' AND ";
query += "e.did = d.did ORDER BY eid";
rset1 = stmt1.executeQuery( query );
if ( !rset1.next( ) )
System.out.println( "No employees are found!" );
else {
// Iterate through the result and print the data.
i = 0;
do {
System.out.println( ++i + ": " );
System.out.println( rset1.getString(2) + ", " );
System.out.print ( rset1.getInt(1) + ", " );
System.out.println( rset1.getString(3) + ", " );
System.out.println( rset1.getString(4) );
} while ( rset1.next( ) );
}
// Close the ResultSet and Statement.
rset1.close( );
stmt1.close( );
}
else if ( args[0].equals( "Select departments" ) ) {
query = "SELECT d.did, d.name, location, COUNT(e.name) FROM employee2 e, ";
query += "department2 d WHERE UPPER(d.name) LIKE '%" + args[1].trim( );
query += "%' AND e.did = d.did GROUP BY d.did, d.name, location ORDER BY d.did";
rset1 = stmt1.executeQuery( query );
if ( !rset1.next( ) )
System.out.println( "No departments are found!" );
else
// Iterate through the result and print the data.
do {
System.out.println( rset1.getString(2) + ", " );
did = rset1.getInt(1);
System.out.print ( "ID: " + did + ", " );
System.out.println( rset1.getString(3) + ", " );
System.out.println( "# of employees: " + rset1.getString(4) );
query = "SELECT DISTINCT e.name FROM employee2 e, department2 d ";
query += "WHERE e.did = d.did AND d.did = " + Integer.toString( did );
rset2 = stmt2.executeQuery( query );
// Iterate through the result and print the data.
if ( !rset2.next( ) )
System.out.println( "No employees are found!" );
else
do {
System.out.println( rset2.getString(1) );
} while ( rset2.next( ) );
rset2.close( );
} while ( rset1.next( ) );
query = "SELECT d.did, d.name, location, 0 FROM department2 d ";
query += "WHERE UPPER(d.name) LIKE '%" + args[1].trim( ) + "%' AND ";
query += "NOT EXISTS ( SELECT e.eid FROM employee2 e WHERE e.did=d.did ) ";
query += "GROUP BY d.did, d.name, location ORDER BY d.did";
rset1 = stmt1.executeQuery( query );
// Iterate through the result and print the data.
while ( rset1.next( ) ) {
System.out.println( rset1.getString(2) + ", " );
did = rset1.getInt(1);
System.out.print ( "ID: " + did + ", " );
System.out.println( rset1.getString(3) + ", 0 employee" );
}
// Close the ResultSet and Statement.
rset1.close( );
stmt1.close( );
stmt2.close( );
}
}
catch ( SQLException ex ) {
System.out.println( ex );
}
// Close the Connection.
conn.close( );
}
}
|
|
“Never let your sense of morals prevent you from doing what is right.” ― Isaac Asimov, Foundation |