CIS612Homework 8 – Developing Database Solutions with Oracle & .NET Due. We. 8-Nov-2006

Fall 2006

V. Matos

Consider the ORACLE company database whose schema is provided below.

EMPLOYEE (fmane, minit, lname, ssn, birthdate, address, sex, salary, superssn, dno) KEY: ssn

DEPARTMENT (dname, dnumber, mgrssn, mgrstartdate) KEY: dnumber.

PROJECT (pname, pnumber, plocation, dnum) KEY: pnumber.

WORKS_ON (essn, pno, hours) KEY: (essn, pno)

DEPENDENT (essn, dependent-name, sex, bdate, relationship) KEY: (essn, dependent-name)

Write a Windows-based C#.NET or VB.NET application to communicate with the Oracle versionof the COMPANY database and accomplish the following tasks

  1. The .NET application will use ADO.NET controls (Connection, Command, DataReader, DataAdapter, DataSet,…).
  2. The app will consist of a main form where employees will be displayed one at the time.
  3. The form includes:
  4. Textboxes showing ssn, full-name, address, dno, dname, sex, salary, SuperSsn for the current employee.
  5. Two DataGrids, one displays all the assignments in which the employee works and the second shows his/her family members.
  6. A NEXT command button to navigate from one employee to the other (forward only!) and correspondingly update the DataGrids.
  7. An Update button to change the employee’s salary field.
  8. A Print button to create a report showing all of the current employee’s data (personal values held in the EMPLOYEE table, all of his/her assignments stored in WORKS_ON, all of his/her family members as indicated in the DEPENDENT table).
  9. An Exit button to close the form.

NOTE:

  1. Preparation of Test Data. Use the database instance provided in Elmasri-Navathe Fig. 5.6.
  2. You must use ADO.NET objects.
  3. Please DO NOT USE the DataForm wizard to assemble the GUI.
  4. The report requested on step e should be made using the CrystalReport facility.
  5. Use an ODBC data source (create a set called “myOracleODBC” for CsuPerson/Euclid) to reach the (XE) ORACLE database.
  6. You will use the GetFullName (aSSN )PL/SQL function to return the (salutation + employee’s full name). For instance if the current input SSN is 123456789 your output should be a string such as “Mr. John Smith”. You must create and use the server-side function.
  7. Create a trigger to guarantee that salary modifications do not violate thefollowing business rule “the NEW salary of an employee must be higher than his/her OLD salary”
  8. (Try to) Change the salary of employee 123456789 to $1M.
  9. (Try to) Change the salary of employee 123456789 to $0.5 M.
  10. Print the PL/SQL and MS-Access code, provide screen-shots of the form & data