PROJECT SPECIFICATION
MAXWELL is a specialist agency that focuses purely on temporary recruitment across the business support functions of the corporate world in Sydney. The agency places temporary workers for various positions in administration, accounting and finance, customer service, call centre, logistics, manufacturing, etc. With the recent growth in the demand for temporary employment, the top management of the agency has realised the need for a database application to assist with the administration of the client companies and potential candidates. The requirements collection and analysis phase of the database design process has been completed and provided the following data requirements specification for the database application.
1. MAXWELL recruits a range of staff members for its day-to-day operations. Staff member details such as name, hire date, address, contact phone number, email and position are recorded in the system. Each staff is assigned a unique identification number within the agency.
2. MAXWELL keeps record of all potential candidates who are willing to work in temporary positions. Each potential candidate is identified by a unique identification number. Candidate details such as name (including first name and last name), date of birth, gender, mailing address, contact phone number and email address are recorded in the system.
3. Each candidate may have earned several qualifications. The system records details of each qualification such as a unique code for identifying the qualification (eg BBA, Certificate II, BSc, etc.), a short description and the level of the qualification (eg undergraduate, Diploma, Certificate, etc.). Each qualification may be earned by more than one candidate. For instance, a candidate might have earned a BBA degree or a Microsoft Network certification. It is also possible that a candidate has earned both qualifications. The other details such as the institute from which the qualification was awarded and the year of completion is also recorded in the system.
4. MAXWELL offers several programs to help candidates improve their qualifications. Each program has a unique program name. The system also records other program details such as program description, fee, duration. Each program has been designed to develop a qualification. However, a candidate may need to complete multiple programs to earn the qualification.
5. Programs are taught during training sessions. MAXWELL runs multiple training sessions for each program. Therefore, the system stores the start date and end date for each training session. However, newly designed programs may not have any training sessions scheduled yet.
6. A candidate enrols in one training session for a program. For each enrolled candidate, the training completion status (eg completed, in-progress etc.) and due payment amount for that training session are also recorded in the system.
7. MAXWELL also keeps track of the client companies that request temporary workers. Details about each company including a unique identification number, name, address, contact person, phone number and email address are stored in the system.
8. Each time a company requests a temporary employee, a new job opening record is created and added to the system including details such as a unique opening number, description of required qualifications, a starting date, a tentative ending date, hours per week and hourly pay.
9. The administrative staff at MAXWELL match the qualification of candidates with the current openings manually and prepare a list including the most suitable candidates for the opening. Each administrative staff member is supervised by a supervisor who finalises the job assignment by choosing the most eligible candidate for the opening. Each assignment record is identified by a unique number and includes the administrative staff who process the assignment. It also includes the assignment date and number of work hours needed for the assignment.
10. Each time the candidate is assigned for a temporary position, one job history record is created for that assignment including actual start date and end date. Naturally, no job history exists if the candidate has never worked with MAXWELL.
PART A: PLANNING
1. Draw an entity relationship diagram (ER diagram) using either crow’s foot or UML notation that models the data requirement for the MAXWELL database. You will need to paste in a screenshot of your ER diagram or incorporate your diagram into your Word document. Please make sure that your diagram is clearly readable. Your diagram should include the following:
a. all entities, attributes and relationships (including names)
b. primary key (underlined)
c. cardinality constraints on the relationships using crow’s feet or UML notation
d. participation (optional/ mandatory) symbols if applicable
e. assumptions you have made, e.g., how you arrived at the cardinality/ participation for those not mentioned or clear in the business description, etc
2. Translate the ER diagram you have drawn into corresponding relational data structures. Relational data structures need to be typed. Create relational data structures that translate your ER diagram that includes:
a. relation names
b. attribute names
c. primary keys and foreign keys which you identify
d. for each relation, the level of normalisation achieved, and justification for any relation not in its third normal form
e. The data structures should be shown using the standards of this program
3. Explain the basic concepts of relational algebra and apply them in queries
a. Based on your relational schema, write SQL queries that will produce the following data:
i. details including last name, address, email and contact phone number of all male candidates
ii. details of all job openings starting in the month of January 2020 or that have a minimum hourly rate of $30
b. Use basic operations of relational algebra such as projection and selection to express the above queries.
PART B: IMPLEMENTATION
To complete this task, you need to create the database using any of the commercial database management systems like Microsoft Access/ XAMPP, etc. You need to translate the relational data structures into a set of base relations and create the database using SQL (Microsoft Access)/ MySQL (XAMPP). Note that you will need to submit the database as well as screenshots to answer the following questions.
1. Name the database as Agency. The database name needs to start with your first name. For example, a student with first name Richard will create a database with the name Richard_Agency.
2. Follow a standard naming convention for tables and attributes. A table name needs to start with your first name. For example, a student with first name Richard will create a table with the name Richard_Staff.
3. Create relationships between tables and enforce the necessary referential integrity and suitable constraints.
4. The database should include suitable validation and integrity checks as well as appropriate referential integrity checks.
5. Insert screenshots of Design view and Datasheet view of MS Access, or export the DDL script of MySQL, into the Word document.
6. Insert sample data records into each of your tables (there should be a minimum of 5 records per table). When inserting records into the tables, make sure that the result of each SQL query in Part B will have at least one record. Paste screenshots of each table’s data into the Word document.
7. Write SQL expressions/ statements for the following queries. Note that these SQL queries must be tested, and the screenshots of the SQL statements together with their results placed as the answer in the Word document. Correctly number your answers.
a. List the first name and last name of all candidates. Sort the output using the last name as the primary sorting order and the first name as the secondary sorting order. Both sorting orders are in ascending order.
b. List the contact details including first name, last name, phone and email address of all candidates who live in the suburb of Leichhardt.
c. List the details of job openings which have been started and currently underway thus not complete yet. (Note: You can use CURRENT_DATE to get the system date for today.)
d. List the details of qualifications (eg qualification code, description and level) that MAXWELL currently offer as a program along with any other qualification that MAXWELL does not offer as a program.
e. List the different job openings which the staff assigned in the month of November 2019. The attributes in the output are job opening number, job description, start date, assignment number, assignment date and number of work hours needed for the assignment.
f. List all the job assignments by each staff member. Attributes to include in the output are staff identification number, staff name, job opening number, assignment number sorted by staff identification number and in descending order of assignment date.
g. List details of each program including name, program description, program fee, duration, number of training sessions for that program.
h. Find the total revenue collected from program fees for programs that have a duration of 10 or more days.
i. List the supervisor identification number, name and the number of staff supervised by those supervisors in ascending order on supervisor identification number.
j. List the name and address of the client companies with more than 5 job openings.
• Name the database file in the following form:
FirstName_Agency, eg Richard_Agency.
• Name the Word file according to the following form:
