Reference no: EM133158195
Business Data Management and Analytics
Assignment - Data Model
SPECIFICATIONS
Read the following Case study carefully. You will be asked to create a data model, that is free from anomalies and therefore complies with being in third normal form.
CASE STUDY
"Want-2-Work" is a job placement agency. Like many other such agencies, their primary business is bringing applicants and employers together. There are many people with varying skills that are either unemployed and looking for a job or are currently unhappy with their current job who are looking for another job. There are also many employers with various jobs that they have within their company that they need to fill with the right applicants.
The business has these two primary customer groups: employers and applicants.
Employers provide position descriptions, i.e. jobs. This would include all the usual aspects such as the title of the positions, duties, required skills, required qualifications etc. etc.
You may have seen position advertisements in the newspapers that provide these sorts of details.
The job agency also maintains details about the applicants. All the usual personal details of course, plus details normally found in resumes, such as skills and qualifications, interests/hobbies, work experience etc.
At any one time, an employer can have several positions open, and there will naturally be many applicants for each position. Applicants can be assigned to interviews for various positions, and can have first, second and even third interviews before they might be appointed to a position.
The interview information needed by the Job agency includes the interview number (is it the 1st interview or 2nd...etc), who is attending the interview, the date time and location of the interview. The location can be physical or online, with appropriate adherence to the social distancing laws during the COVID-19 isolation. Therefore, location maybe at a physical location or an online invite to a communication medium that must provide audio and video capabilities (like Zoom and Skype). The success of an applicant's application in getting a job is also recorded.
All this information needs to be stored in the database so that the job agency can keep track of employers, positions, applicants, applicant applications and interviews sessions. It is very important to the job agency that they have all this data so they can run regular reports on outstanding jobs and reports on applicants that have not been placed into a job.
REQUIREMENTS - HIGHER LEVEL - SQL IMPLEMENTATION
For additional marks, provide the SQL commands to create the tables and insert a few rows into each table. Also provide several business question and SQL queries to test out the tables, include at least a:
• SELECTION condition query
• GROUP BY query
• JOIN query
• NESTED query
Also create a least one visualisation of the data using Orange, Tableau or Excel.
ASSESSMENT
Assessment of the data model will be based on the following areas (by the tutor) as per the rubric found on the CANVAS submission system:
• Data Model
• No anomalies
• Dependence of all attributes to the whole primary key
• Must be in third normal form (3NF)
• SQL Queries
• Appropriate implementation of data model using SQL (CREATE TABLEs & INSERTs)
• A number of business questions (of your design) + SQL Query solution (SELECTs)
• Visualisation appropriateness to the business question (of your design)
Attachment:- Business Data Management and Analytics.rar