Assignment - Database Developer
Outcome/Task/Result - Build a candidate management database
schema using MYSQL OR MSSQL only.
The schema contains three primary entities -
● Account
● User
● Candidate
Account entity contains following fields:
Column
Name
Description
ID Unique id per Account
Title Name of the Account
Candidates
Created count
Count of candidates created on Account
Candidates
Deleted count
Count of candidates created on Account
User entity contains following fields:
Column
Name
Description
ID Unique id per User record
First Name First Name of User
Last Name Last Name of User
Email Id Email Address of User
AccountID AccountID of the User
Candidates
Created count
Count of candidates created by User
Candidates
Deleted count
Count of candidates created by User
Candidate entity has following fields:
Field Description
ID Unique id per candidate record
Serial Number Unique sequence number (serial number) per
account.
First Name First Name of candidate
Last Name Last Name of candidate
Email Address Email Address of candidate
Gender Id Gender id of candidate
Contact number Contact number of candidate(can be multiple using
comma)
Qualification Id Qualification id of the candidate
Date of Birth Date of birth
Resume File
Link
Link of the Resume File
Resume text Extracted text from resume. Should be searchable
and quick.
Resume File
name
Resume File name
Willing to
relocate
Does the candidate wish to relocate? 0 - no, 1 -yes
Slug Unique string(can be used to mask id in urls)
Linked In Url Linkedin profile url
AccountID AccountID of Candidate record
Created By
User Id
User id who has created the record
Created On Time when record was created
Address Full address of candidate
Latitude Latitude
Longitude Longitude
Skills One candidate can have multiple skills
Instructions & Guidelines to create the database schema in mysql that
satisfies following conditions:
● Design and create tables with suitable columns, data types and normalisation.
Ensure appropriate relationships, such as one-to-one, one-to-many, and
many-to-many, are maintained. If necessary, create additional tables.
● Implement reference tables with relevant values for the dependent fields (shown
in yellow).
● Incorporate indexes on columns that are frequently used in search or join
operations. Identify the columns that are commonly queried or involved in joins,
and create indexes to optimise query performance.
● Utilize triggers, procedures, views and functions where necessary to enhance
functionality and maintain scalability. Document these database objects, including
their purpose, input parameters, and expected output.
● Develop and implement a logging mechanism to track and record changes made
to candidate records.
● Share .sql dump file while submitting the assignment. (mandatory)
Assignment Tasks:
Task 1:
a. Document the logging mechanism, including the structure of the log
table, the logged events and the information captured in the logs.
b. Create comprehensive documentation for the database schema,
including table definitions, column descriptions, relationships, and
indexes. Document any naming conventions, data constraints, and
data types used throughout the schema.
c. Document any specific design decisions, such as the reasoning
behind the choice of normalisation levels, table structures and
relationships.
(Note : You need to write the documentation along with queries, tables etc. for a, b & c to
get maximum scores)
Task 2:
a. Write a query to delete the User record.(Mandatory)
b. Write a query to search candidates based on name, email and
linkedIn URLs.
c. Write an optimized boolean search query to fetch candidate
records using resumetext.
d. Write a query to fetch comma separated User IDs against each
account title.
e. Write a query to fetch counts of Candidates, based on gender, on
each account.