In this project you have to design a database for a fictional company. Your group will first decide the company and then decide the domain under which you wish to work for this project. Few examples are as follows:
- Employee management of your fictional company.
- Payroll management of your fictional company.
- Patient management system of fictional hospital system
- Account management of fictional bank
- Inventory management of fictional shop
Note: These are just ideas. You can work on any of the above idea or you can work on your own idea.
Once your group will finalize the idea then you have to perform given five tasks. For every task you have to prepare appropriate deliverables and compile a final project report with proper project descriptions, required screenshots and scripts. Final project submission will be in the form of project deliverables and project report. For due date visit SLATE.
- Design database schema for your company.
Number of tables required – 4 to 6
Records required – Not more than 20
- Based on the above design create corresponding .csv files in excel for each table.
- Integrate the data from .csv files to Talend. Create corresponding jobs to read the data in integrated table schema.
- Integrate the generated table schemas from Talend to Oracle.
- Select any one table from your table set and create an audit trail trigger to keep record of insert, update and delete operations performed on that table. You have to record User Name, Operation Performed, Date of Operation and Value Effected in the log table.
|Task#||Deliverable required||Evaluation Criteria|
|1||Shema Design||Proposed tables in the design, keys and constraints|
|2||.csv files||Each .csv file should be properly defined as per schema designed proposed in Task#1|
|3||Screenshots for each table schema and job description in Talend||Integrated table schemas and jobs will be evaluated|
|4||Detailed description of tables in Oracle and associated jobs in Talend||Each integrated table will be evaluated in student’s user id of Oracle.|
|5||PL/SQL Script for required trigger for audit trail and detailed description of log table.||Operation will be performed on the table and corresponding trigger action will be mapped with logged data.|
|Final Deliverable||Compiled Project Report||Thoroughly compiled project report with proper evidences for each task performed by the group.|
Project evaluation rubrics
|Task#1 – Schema Design||15|
|Task#2 – .csv files||15|
|Task#3 – Talend table schema and jobs||15|
|Task#4 – Oracle tables and Talend jobs||15|
|Task#5 – PL/SQL Script||15|
|Final project submission (presentation)||10|