Online Recruitment System

Shri VileParle Kelvani Mandal’s Dwarkadas J. Sanghavi College of Engineering Computer Engineering Department

BEST PACKAGES ONLINE 4 ALL @

ONLINE RECRUITMENT SYSTEM.COM ........WE CARE FOR U

From:03. NISHAD CHAYYA 07. URVISH DESAI

08. VIRAJ DESAI 10. PRANAV GANDHI

Shri VileParle Kelvani Mandal’s

Dwarkadas J. Sanghavi College of Engineering Computer Engineering Department

CERTIFICATE

This to certify that Shri/Kum. ,Roll No., _______ studying in the third year of engineering in the Computer Department has successfully completed the project of Advanced Database Systems under the guidance of Prof.(Mrs.) Shubhangi Gawali. The project topic is

Subject-in-charge: Date:

ADBS Theory DBS: A database management system (DBMS) is a collection of interrelated data and a set of programs to access those data. The primary goal of a DBMS is to provide a way to store and retrieve database information that is both convenient and efficient. Some representative applications of database systems are: Banking Airlines Sales Telecommunication Manufacturing

Instances And Schemas : The overall design of the database is called the database schema. The collection of information stored in the database at a particular moment is called an instance of the database.

Data Models: Underlying the structure of a database is the data model: a collection of conceptual tools for describing data, data relationships, data semantics, and consistency constraints.

The Entity-Relationship Model: The ER model is based on a perception of the real world that consists of a collection of basic objects called entities, and of relationships among these objects. It represents the database schema at the logical level. It is a semantic data model and is very useful in mapping the meanings and interactions of the real world enterprises into a conceptual schema. The ER model employs three basic structures: entity sets, relationship sets, and attributes.

Entities And Entity Sets: An entity is an object in the real world that is distinguishable from all other objects. Examples: a person, a football club. A set of entities of the same type is called an entity set. Examples: the set of all Resumes, set of all Jobs. An entity is represented by a set of attributes, which are descriptive properties possessed by each member of an entity set. Each entity has a value for each of its attributes. For each attribute there is a set of permissible values, called the domain, or value set, of that attribute. Types of attributes: Simple Composite Single-valued Multi-valued

Relationship And Relationship Sets: A relationship is an association among several entities. Examples: relationship associating a player with a club. A relationship set is a set of relationships of the same type. Examples: relationship set consisting of relations associating a club with it’s manager.

Mapping Cardinalities: Mapping cardinalities, or cardinality ratios, express the number of entities to which another entity can be associated via a relationship set. For a binary relation it must be one of the following: One to One One to Many Many to One Many to Many

Participation Constraints: The participation of an entity set E in a relationship R is said to total if every entity in E participates in at least one relationship in R. If this is not true, the participation is said to be partial.

Keys : A super key is a set of one or more attributes that, taken collectively, uniquely identify an entity in an entity set. A super key for which no proper subset is a super key is called a candidate key. The primary key is the candidate key that is chosen by the designer to uniquely identify an entity.

Weak Entity Sets : An entity set that does not have sufficient attributes to form a primary key is called a weak entity set. It must be associated with another entity set, called the identifying or owner entity set. A weak entity set is said to be existence dependent on the identifying set. The relationship associating the weak entity set with the identifying entity set is called the identifying relationship. A set of attributes that provide a means of distinguishing among all those entities in a weak entity set that depend on a particular strong entity is called the discriminator.

Extended E-R Features: Specialization: The process of designating sub groupings within an entity set is called specialization. Example: The specialization of Job details allows us to distinguish among Eligibility details and Interview details according to the job requirements. Generalization: Generalization is a process in which multiple entity sets are synthesized into higher –level entity set on the basis of common features. Generalization can be regarded as a simple inversion of specialization. Aggregation: Aggregation is an abstraction through which relationships are treated as higherlevel entities. It helps to avoid redundancy and reduce execution cost. Example: The relationship sets Officiated and Commented represent aggregation in which the entity sets Referee and Commentator are associated with Matches which itself is a relation.

Table Creation And Insertion MEMBER FUNCTIONS: Type – Date_ty create or replace type date_ty as object (birthdate date, member function age(birthdate date)return number); 1 create or replace type body date_ty as member function age (birthdate date) return number is 2 a number; 3 b number; 4 begin 5 select to_char(birthdate, 'YYYY')into a from dual; 6 select to_char(birthdate, 'YYYY')into b from dual; 7 return(b-a); 8 end; 9 end;

CREATE TYPES: 1. View - Skills create or replace type skills as varray(5) of varchar(20) not final; 2. View – Personal Details create or replace type personaldet as object(gender varchar(5), mstatus varchar(6), dob date_ty) not final; desc personaldet; Name Null? Type ----------------------------------------- -------- ---------------------------GENDER MSTATUS DOB

VARCHAR2(5) VARCHAR2(6) DATE_TY

3. View – Qualification create or replace type qualification as object(degree varchar(5), year varchar(4), univ varchar(20)) not final; desc qualification Name Null? Type ----------------------------------------- -------- ---------------------------DEGREE YEAR UNIV

VARCHAR2(5) VARCHAR2(4) VARCHAR2(20)

4. View – Qualific create or replace type qualific as varray(3) of qualification not final; desc qualific qualific VARRAY(3) OF QUALIFICATION Name Null? Type ----------------------------------------- -------- ---------------------------DEGREE YEAR UNIV

VARCHAR2(5) VARCHAR2(4) VARCHAR2(20)

5. View – Jobdettemp create or replace type jobdettemp as object(post varchar(10), jyear number, area varchar(10), company varchar(10), exp number) not final; desc jobdettemp jobdettemp is NOT FINAL Name Null? Type ----------------------------------------- -------- ---------------------------POST JYEAR AREA COMPANY EXP

VARCHAR2(10) NUMBER VARCHAR2(10) VARCHAR2(10) NUMBER

6. View - Jobdet create or replace type jobdet as varray(3) of jobdettemp not final; desc jobdet jobdet VARRAY(3) OF JOBDETTEMP JOBDETTEMP is NOT FINAL Name Null? Type ----------------------------------------- -------- ---------------------------POST VARCHAR2(10) JYEAR NUMBER AREA VARCHAR2(10) COMPANY VARCHAR2(10) EXP NUMBER

7. View - Account create or replace type account as object(email varchar(40), username varchar(20), pass varchar(20)) not final; desc account account is NOT FINAL Name Null? Type ----------------------------------------- -------- ---------------------------EMAIL USERNAME PASS

VARCHAR2(40) VARCHAR2(20) VARCHAR2(20)

8. View - Phone create or replace type phone as varray(3) of number(10) not final; desc phone phone VARRAY(3) OF NUMBER(10) 9. View - Comp create or replace type comp under account(coid number, cname varchar(20), cperson varchar(20), area varchar(20), phoneno phone) not final; desc comp comp extends SYSTEM.ACCOUNT Name Null? Type ----------------------------------------- -------- ---------------------------EMAIL USERNAME PASS COID CNAME CPERSON AREA PHONENO

VARCHAR2(40) VARCHAR2(20) VARCHAR2(20) NUMBER VARCHAR2(20) VARCHAR2(20) VARCHAR2(20) PHONE

10. View - Name create or replace type name as object(first varchar(20), middle varchar(20), last varchar(20)) not final; desc name Name Null? Type ----------------------------------------- -------- ---------------------------FIRST MIDDLE LAST

VARCHAR2(20) VARCHAR2(20) VARCHAR2(20)

11. View - CResume create or replace type cresume under account(cid number, cname name, addr varchar(20), phonno phone,pdetails personaldet, qdetails qualific, jdetails jobdet, skill skills) not final; desc cresume cresume extends SYSTEM.ACCOUNT Name Null? Type ----------------------------------------- -------- ---------------------------EMAIL USERNAME PASS CID CNAME ADDR PHONNO PDETAILS QDETAILS JDETAILS SKILL

VARCHAR2(40) VARCHAR2(20) VARCHAR2(20) NUMBER NAME VARCHAR2(20) PHONE PERSONALDET QUALIFIC JOBDET SKILLS

12. View - Jobtemp Create or replace type jobtemp as object(jid number, description varchar(20), area varchar(10), location varchar(20)) not final; desc jobtemp jobtemp is NOT FINAL Name Null? Type ----------------------------------------- -------- ---------------------------JID DESCRIPTION AREA LOCATION

NUMBER VARCHAR2(20) VARCHAR2(10) VARCHAR2(20)

13. View - Elig create or replace type elig under jobtemp(degree varchar(5),exp number,skill skills) not final; desc elig elig extends SYSTEM.JOBTEMP elig is NOT FINAL Name Null? Type ----------------------------------------- -------- ---------------------------JID DESCRIPTION AREA LOCATION DEGREE EXP SKILL

NUMBER VARCHAR2(20) VARCHAR2(10) VARCHAR2(20) VARCHAR2(5) NUMBER SKILLS

14. View - Inter create or replace type inter under jobtemp(vdate date, venu varchar(10)) not final; desc inter inter extends SYSTEM.JOBTEMP Name Null? Type ----------------------------------------- -------- ---------------------------JID DESCRIPTION AREA LOCATION VDATE VENU

NUMBER VARCHAR2(20) VARCHAR2(10) VARCHAR2(20) DATE VARCHAR2(10)

CREATE TABLES: 1. Table - Acc create table acc of account(primary key(email,username)); desc acc Name Null? Type ----------------------------------------- -------- ---------------------------EMAIL USERNAME PASS

NOT NULL VARCHAR2(40) NOT NULL VARCHAR2(20) VARCHAR2(20)

2. Table - Company create table company of comp(primary key(coid)); desc company Name Null? Type ----------------------------------------- -------- ---------------------------EMAIL USERNAME PASS COID CNAME CPERSON AREA PHONENO

VARCHAR2(40) NOT NULL VARCHAR2(20) VARCHAR2(20) NOT NULL NUMBER VARCHAR2(20) VARCHAR2(20) VARCHAR2(20) PHONE

3. Table - Candidate create table candidate of cresume(primary key(cid)); desc candidate Name Null? Type ----------------------------------------- -------- ---------------------------EMAIL USERNAME PASS CID CNAME ADDR PHONNO PDETAILS QDETAILS JDETAILS SKILL

VARCHAR2(40) NOT NULL VARCHAR2(20) VARCHAR2(20) NOT NULL NUMBER NAME VARCHAR2(20) PHONE PERSONALDET QUALIFIC JOBDET SKILLS

4. Table - Jobs create table jobs of jobtemp(primary key(jid)); desc jobs Name Null? Type ----------------------------------------- -------- ---------------------------JID DESCRIPTION AREA LOCATION

NOT NULL NUMBER VARCHAR2(20) VARCHAR2(10) VARCHAR2(20)

5. Table - Interview create table interview of inter(jid references jobs(jid), primary key(jid)); desc interview Name Null? Type ----------------------------------------- -------- ---------------------------JID DESCRIPTION AREA LOCATION VDATE VENU

NOT NULL NUMBER VARCHAR2(20) VARCHAR2(10) VARCHAR2(20) DATE VARCHAR2(10)

6. Table - Eligibility create table eligiblity of elig(jid references jobs(jid), primary key(jid)); desc eligiblity Name Null? Type ----------------------------------------- -------- ---------------------------JID DESCRIPTION AREA LOCATION DEGREE EXP SKILL

NOT NULL NUMBER VARCHAR2(20) VARCHAR2(10) VARCHAR2(20) VARCHAR2(5) NUMBER SKILLS

CREATE RELATIONS: 1. Relation - Posts create table posts(coid number references company(coid), jid number references jobs(jid), primary key(coid,jid)) 2. Relation - Calls create table calls(cid number references candidate(cid), jid number references interview(jid), primary key(cid,jid))

CREATE SEQUENCE: 1. Sequence - setcoid create sequence setcoid 2 increment by 1 3 start with 100 4 ; 2. Sequence - setcid create sequence setcid 2 increment by 1 3 start with 1 4 ; 3. Sequence - setjid create sequence setjid 2 increment by 1 3 start with 1 4 ;

CREATE SYNONYMS : create synonym iselected for interview;

CREATE VIEWS create view e_candidate as select cid, qdetails from candidate;

CREATE INDEXES : create index ind1 on company(coid, cname); desc user_indexes; create index ind2 on jobs(area); select table_name, index_name, index_type from user_indexes; desc user_segments; Name Null? Type ----------------------------------------- -------- ---------------------------SEGMENT_NAME VARCHAR2(81) PARTITION_NAME VARCHAR2(30) SEGMENT_TYPE VARCHAR2(18) TABLESPACE_NAME VARCHAR2(30) BYTES NUMBER BLOCKS NUMBER EXTENTS NUMBER INITIAL_EXTENT NUMBER NEXT_EXTENT NUMBER MIN_EXTENTS NUMBER MAX_EXTENTS NUMBER PCT_INCREASE NUMBER FREELISTS NUMBER FREELIST_GROUPS NUMBER BUFFER_POOL VARCHAR2(7) alter table jobs add(annualreport CLOB);

INHERITANCE: 1. Insert into both acc and company insert into acc Values (comp('&email','&username','&pass',setcoid.nextval,'&cname','&cperson','&area', phone(&ph1,&ph2))) select treat (value(p) as comp) from acc p select value(p) from acc p where value(p) is of (comp); 2. Insert into both acc and company insert into acc values(cresume('&email','&username','&pass',setcid.nextval, name('&first','&middle','&last'),'&addr', phone(&ph1,&ph2), personaldet('&gender','&mstatus',date_ty('&birthdate')), qualific(qualification('°ree','&year','&univ'), qualification('°ree','&year','&univ')), jobdet(jobdettemp('&post',&jyear,'&area','&company',&exp),jobdettemp('&post', &jyear,'&area', '&company',&exp)), skills('&sk1','&sk2'))) select treat (value(p) as cresume) from acc p select value(p) from acc p where value(p) is of (cresume); 3. Insert into both jobs and eligibility of that job insert into jobs values (elig(&jid,'&description','&area','&location','°ree',&exp,skills('&s1','&s2'))) select treat (value(p) as elig) from jobs p select value(p) from jobs p where value(p) is of (elig);

CREATE TRIGGRS CREATE OR REPLACE TRIGGER too_young AFTER insert OR update ON candidate FOR EACH ROW BEGIN IF : new.pdetails.dob(new.pdetails.dob.birthdate) <18 THEN RAISE_APPLICATION_ERROR(20001,’APPLICANT MUST BE 18 YEARS OF AGE TO POST HIS/HER RESUM THANK YOU FOR VISITING’); END IF; END;

INSERT QUERIES : insert into acc values('&email','&username','&pass') SQL> insert into company values('&email','&username','&pass',setcoid.nextval,'&cname','&cperson','&area', phone(&ph1,&ph2)); Enter value for email: [email protected] Enter value for username: ACCENTURE Enter value for pass: 123 Enter value for cname: ACCENTURE Enter value for cperson: Judas Enter value for area: IT Enter value for ph1: 28765647 Enter value for ph2: 9898098765 old 1: insert into company values('&email','&username','&pass',setcoid.nextval,'&cname','&cperson','&area', phone(&ph1,&ph2)) new 1: insert into company values('[email protected]','ACCENTURE','123',setcoid.nextval,'ACCENTURE','Ju das','IT',phone(28765647,9898098765)) 1 row created. insert into candidate values('&email','&username','&pass',setcid.nextval, name('&first','&middle','&last'),'&addr', phone(&ph1,&ph2), personaldet('&gender','&mstatus',date_ty('&birthdate')), qualific(qualification('°ree','&year','&univ'), qualification('°ree','&year','&univ')), jobdet(jobdettemp('&post',&jyear,'&area','&company',&exp),jobdettemp('&post', &jyear,'&area', '&company',&exp)), skills('&sk1','&sk2')) insert into jobs values(setjid.nextval,'&dscription','&area','&location') insert into eligiblity values(&jid,'&description','&area','&location','°ree',&exp,skills('&s1','&s2')) insert into interview values(&jid,'&desc','&area','&loc','&date','&venue') SQL> insert into calls values ('&cid', '&jid'); Enter value for cid: 8 Enter value for jid: 1 old 1: insert into calls values ('&cid', '&jid') new 1: insert into calls values ('8', '1') 1 row created.

SQL> insert into posts values ('&coid', '&jid'); Enter value for coid: 120 Enter value for jid: 1 old 1: insert into posts values ('&coid', '&jid') new 1: insert into posts values ('120', '1') 1 row created.

SQL Queries 1. Account SQL> select * from acc; EMAIL USERNAME PASS ------------------------------------------ ------------------------------- [email protected] viraj 12345 [email protected] pranav 12345 [email protected] nishad 12345 [email protected] tapan 12345 [email protected] urvish 12345 [email protected] khyati 12345 [email protected] namrata 12345 [email protected] resha 12345 [email protected] huzaifa 12345 [email protected] ajinkya 12345 [email protected] priyank 12345 11 rows selected. 2. Candidate SQL> select * from candidate; EMAIL USERNAME PASS CID CNAME(FIRST, MIDDLE, LAST) ADDR PHONNO ------------------------------------ -------------------- ------------- --------------------------------------------------------------- [email protected] viraj 12345 8 NAME('Viraj', 'Chetan', 'Desai') Borivali PHONE(28861977, 9920231580) [email protected] resha 12345 11 NAME('Resha', 'Pravin', 'Haria') Borivali PHONE(28334455, 9876538712) [email protected] namrata 12345 12 NAME('Namrata', 'Kumar', 'Damania') Churchgate PHONE(26648899, 9833987654) [email protected] nishad 12345 13 NAME('Nishad', 'Ketan', 'Chhaya') Malad PHONE(28333482, 9819518722) [email protected] pranav 12345 14 NAME('Pranav', 'Kumar', 'Gandhi') Goregaon PHONE(28735467, 9869065481) [email protected] urvish 12345 15 NAME('Urvish', 'Pinakin', 'Desai') Parle PHONE(26145678, 9820173354)

USERNAME PDETAILS(GENDER, MSTATUS, DOB(BIRTHDATE)) ----------------------------------------------------------------------------------------------------viraj PERSONALDET('male', 'single', DATE_TY('22-MAR-88')) resha PERSONALDET('fema', 'marr', DATE_TY('23-FEB-88')) namrata PERSONALDET('fema', 'single', DATE_TY('26-OCT-87')) nishad PERSONALDET('male', 'single', DATE_TY('14-MAY-87')) pranav PERSONALDET('male', 'marr', DATE_TY('22-JAN-87')) urvish PERSONALDET('male', 'single', DATE_TY('14-JUL-87'))

USERNAME QDETAILS(DEGREE, YEAR, UNIV) -------------------------------------------------------------------------------------------------------------viraj QUALIFIC(QUALIFICATION('BE', '2009', 'MU'), QUALIFICATION('MBA', '2011', 'IIM-A')) resha QUALIFIC(QUALIFICATION('BA', '2008', 'MU'), QUALIFICATION('MA', '2010', 'MU')) namrata QUALIFIC(QUALIFICATION('BCom', '2008', 'MU'), QUALIFICATION('Mcom', '2010', 'MU')) nishad QUALIFIC(QUALIFICATION('BE', '2009', 'MU'), QUALIFICATION('MS', '2011', 'CSU')) pranav QUALIFIC(QUALIFICATION('BE', '2009', 'MU'), QUALIFICATION('MBA', '2011', 'NMIMS')) urvish QUALIFIC(QUALIFICATION('BE', '2009', 'MU'), QUALIFICATION('MS', '2011', 'CSU'))

USERNAME

JDETAILS(POST, JYEAR, AREA, COMPANY, EXP)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------viraj JOBDET(JOBDETTEMP('Trainee', 2011, 'IT', 'TCS', 1), JOBDETTEMP('Manager', 2012, 'IT', 'TCS', 3)) resha JOBDET(JOBDETTEMP('Trainee', 2010, 'Finance', 'TCS', 1), JOBDETTEMP('Manager', 2011, 'Finance', 'AIRTEL', 2)) namrata JOBDET(JOBDETTEMP('Accountant', 2010, 'Finance', 'INFOSYS', 1), JOBDETTEMP('Accountant', 2011, 'Finance', 'TCS', 2)) nishad JOBDET(JOBDETTEMP('Trainee', 2011, 'IT', 'AIRTEL', 1), JOBDETTEMP('Manager', 2012, 'Finance', 'TCS', 2)) pranav JOBDET(JOBDETTEMP('Trainee', 2011, 'Finance', 'INFOSYS', 1), JOBDETTEMP('Accountant', 2012, 'Finance', 'TCS', 2)) urvish JOBDET(JOBDETTEMP('Trainee', 2011, 'IT', 'PATNI', 1), JOBDETTEMP('Manager', 2012, 'IT', 'TCS', 2))

USERNAME SKILLS ----------------------- --------------viraj SKILLS('Java', 'C') resha SKILLS('Accounts', 'Java') namrata SKILLS('Java', 'Accounts') nishad SKILLS('Java', 'Accounts') pranav SKILLS('C', 'C++') urvish SKILLS('Oracle', 'HTML') 6 rows selected. 3. Calls SQL> select * from calls; CID JID ---------- ---------8 1 8 2 8 4 8 5 15 1 14 2 13 5 13 6 12 4 11 4 11 2 11 rows selected.

4. Company SQL> select * from company; EMAIL USERNAME PASS COID CNAME ------------------------------------- ---------------------- ----------------- [email protected] ACCENTURE 123 120 ACCENTURE [email protected] TCS 123 121 TCS [email protected] AIRTEL 123 122 AIRTEL [email protected] INFOSYS 123 123 INFOSYS [email protected] PATNI 123 124 PATNI USERNAME CPERSON AREA PHONENO ---------------------------- ----------------------------- -------------- --------------ACCENTURE Judas IT PHONE(28765647, 9898098765) TCS Ratan IT PHONE(26426767, 9876546780) AIRTEL ShahRukh Finance PHONE(24145665, 9920233445) INFOSYS Narayan IT PHONE(24427656, 9920076876) PATNI Yakub Finance PHONE(26678909, 9821345678) 5. Eligibility SQL> select * from eligiblity; JID DESCRIPTION AREA LOCATION DEGRE EXP ------- ------------------------------- ---------- ---------------------------------- -------- ----------1 Manager IT Dadar MBA SKILLS('Java', 'C') 2 Trainee IT Borivali BE SKILLS('C', 'Accounts') 3 Accountant Finance Borivali BA SKILLS('Oracle', 'Java') 4 Trainee Finance Malad BCom SKILLS('Accounts', 'Java')

SKILL 2 1 2 2

6. Interview select * from interview; JID DESCRIPTION AREA LOCATION VDATE VENU ---------- ------------------------------ -------------- ------------------------------------------ ---------1 Trainee IT Dadar 22-APR-09 Dadar 2 Manager IT Borivali 24-APR-09 Dadar 4 Accountant Finance Malad 25-APR-09 Malad 5 Manager Finance Santacruz 22-APR-09 Santacruz 6 Trainee Finance Parle 23-APR-09 Parle 7. Jobs SQL> select * from jobs; JID -------1 2 3 4 5 6

DESCRIPTION AREA LOCATION ------------------------ ------------ -------------------Manager IT Dadar Manager Finance Dadar Trainee IT Borivali Accountant Finance Parle Manager IT Malad Trainee Finance Dadar

6 rows selected.