Database Analysis and Design
This is an individual effort. You may post questions about the project to the class discussion board, but working in teams is NOT Authorized.
Implement a physical schema for the 'Careers" Database. Your career being of prime importance to your job hunting and final selection, you have decided to place all information about your career in a relational database; specifically., Oraclel2C {per the Syllabus). You will create a single text file with the entire Oracle RDEIVIS Schema., Constraints, triggers, Stored Procedures, Packages if you choose to use them, and hard coded Data which will support the queries as defined below.
I will show an enormous amount of flexibility with your entities as long as they are well defined, Normalized to the 3rd normal form, and well commented. I would expect you to have no less than 4 entities as below, 3 levels of entities for each high level entity and 3 attributes for each entity. There also should be relationships defined between entities. A high level set of example entities are listed below, these are examples, you can use these names or create your own as necessary {this list is NOT Exhaustive):
1) The Career {"Database Schema") has
Requirement Note: {Please name Your 'Career schema (First Initial, Middle Initial, Last Initial, Last 3 of your U SF ID_Career. Example: Mine would be 5AH876_CAREER
a) Jobs, which have
(1) Duties
(2) Qualifications I Skills
(3) Benefits
(4) Date Posted
(5) Date Needed
(6) Travel
(7) Contacts HR (ii) Hiring Manager
a. Phone
b. Email
b) Future expectations, which have..
i) ......... Be creative here!
c) Past experiences, which have-
i) ........ try and use real experiences, or create fictional entries if necessary
d) Education
i} Classes
(1) Type
(2) Objectives
(3) Curriculum
(4) Content
ii) Degrees
iii) Skills
(1) Date Last used
Some linkage should exist between Jobs, education and future expectations. One such example: A Job has qualifications; Education has Content Somewhere between these 2, I would expect to see A job having a requirement for 'Entry Level Oracle Database Skills". This class, under education would, would have a type of 'Entry Level Oracle Database Skills"; hence a defined relationship exists. As stated above there should be at least 3 attributes. I would expect both the job qualifications and education type to have some linkage relationship that could be Oracle/ RDBMS /SQL, type skills. These attributes would have a relationship which you will need to define.
I do not expect you to type in 120 credit hours of "Education" for your input data. However, there should at least be 6-10 classes associated with the type of "Job" you are looking for (This class is one such example). There should be a minim urn number of 6 Jobs defined. Three future expectation and 3 Past experiences. Each table should have no less than 3 records un less well commented and for a specific reason.
If you need to create Fictitious data, do so, but "FLAG' it as such using an attribute.
The idea for this assignment is to make it practical to your "Career". Instead of using a spreadsheet to list pros and cons, use this assignment to create a model for your career and use real Jobs and real education where you can.
When complete you should have a single TEXT file with all your DDL and DIAL for the entire Schema. This will include all create Tables, indexes, Constraints, Store Procedures, Triggers, Packages (Should you choose to use them, they are not required, Sequences, and other Oracle Objects as necessary. At the top of the file you should have a complete delete section. This will allow me to drop and recreate your schema easily should I find errors or issues. You will then have a separate section, within the same text file that has your input data for each table.
Finally, you will have 4 Stored Procedures. These can be implemented in Packages if you are feeling adventurous. Label them as follows:
- SP1_add_job: Add all the necessary data for a given job. Insert the data into the appropriate table and create necessary SQL for all parent child relationships.
- SP2_delete_lob; Delete all the necessary data for a given job, preferably by a single input of Job ID. Delete the data from all the appropriate tables and remove parent child relationships.
- SP3_find_match: Add all the necessary data for a given job. Insert the data into the appropriate table and create necessary SQL for all parent child relationships.
- SP4_delete_match: Delete all the necessary data for a given Match between Job and Experience, preferably by a single input of Match ID. Delete the data from all the appropriate tables and remove parent child relationships.
My testing and grading will include creating my own data arid using your Store Procedures to verify your SP has good error handling. I will try and input characters in non-character fields. I will input incorrect date formats. Be sure to "catch"' and deal with SQL errors on insert, updates and deletes.