Objective The second milestone will assess your abilities to transit to design and create a solution. This includes re-assessing the business requirements established in the first milestone (making adjustments to them if necessary), and creating complete database system specifications that meet these (revised) requirements. This milestone includes two stages: (I) revise conceptual database design (II) logical database design. Essentially the requirement document contains the following major sections: REVISED CONCEPTUAL DATABASE DESIGN 1. 1. Create Entity Relationship (ER) diagram as the result of conceptual database design that involve these steps: (20 marks) Step 1.1 Identify entities Step 1.2 Identify relationships Step 1.3 Identify and associate attributes with entities or relationships Step 1.4 Determine attribute domains Step 1.5 Determine candidate, primary, and alternate key attributes Step 1.6 Check model for redundancy Step 1.7 Check model supports user transactions Step 1.8 Review conceptual database design with users The ER diagram contains the following details: 1. All entities 2. Relationship - roles and multiplicities 3. All attributes for all entities 4. Primary Key for all strong entities. (refer to figure below) 5. (Note: For weak entities, you will identify primary in during logical database design.) LOGICAL DATABASE DESIGN Conduct Logical database design includes the following steps: Step 2.1 Derive relations for logical data model Step 2.2 Validate relations using normalisation Step 2.3 Validate relations against user transactions Step 2.4 Define integrity constraints Step 2.5 Review logical database design with users Step 2.6 Check for future growth 1. Create table showing all entities, attributes, primary key and one or more foreign keys, as well as with all necessary referential constraints. (40 marks) 2. Create ER diagram showing global logical data model. (40 marks) Word processor and Case tools You will be modelling the problem using DIA or any other suitable modelling software. In the event you are using alternative software modelling tool, please make sure the lecturer will be able to assess your work with the proper software tool. You could use Microsoft word or any word processor as long as the lecturer could open the soft copy of your document. In this project, you will only be developing a database for storing and retrieving information about the AusED Academic Staff. You need to create tables, reports and forms using LibreOffice Base. You also need to create SQL queries and views,normalise tables and create E-R Diagrams. The following are some of the requirements of AusED HR : 1. Forms: a.HR Manager should have the form to enter the information about AusED's employees b.HR Manager should have the form to enter information about the allocation of subject per Academic Staff c.Academic Staff should have the form to enter the weekly timesheet 2. Queries: HR Manager wants to see the following queries: 1. List of all Employees and their contact details (sorted by Last Name, then First Name). 2. List and total number of all full-time Academic Staff and their location. 3. List and total number of all part-time Academic Staff and their location. 4. List and total number of sessional Academic Staff and their location. 5. List the names of all academic staff and the type of work they are doing. 6. List the combined total hours worked of all the academic staff per week, the maximum hours worked, minimum hours worked, the name and subject of the academic staff who had the maximum hours , the name and subject of the academic staff who had the minimum hours worked. 7. List the names and location of all of the academic staff teaching a subject (subject will be inputted by the HR Manager). For example, if the HR Manager entered ISYS216, only names and location of academic staff teaching ISYS216 will be shown in the report. 8. List of how many subjects each academic staff is teaching (show the first name , last name and the number of subjects being taught). 9. List of academic staff, their subject and their total hours worked per week per academic staff. 10. List the names of all employees and how long they have worked for AusED. 3. Ta b l e s : 1. In order for you to create the forms and required reports, you must first create tables that will store all the data about the AusED Academic Staff. 2. The following are the activities you expected to do: I. create the database tables II. normalise the database tables III. update the database tables using SQL statements IV. add at least 10 different data per table 1. The final project should contain normalised tables 3.2.4 Project "Human Resources (HR) Management System" Stage 1: Develop HR Database System for the Academic Staff Stage 2: Extend the Database system to include administrative Staff Stage 3: Develop a prototype data mart for the HR System that will be used as basis for the development of the data warehouse for the entire university. ISYS 216 CASE STUDY AND PROJECT Page 4 of 9YOUR PROJECT Since its inception, AusEd Inc has used a spreadsheet software to maintain 3.2.4 Project "Human Resources (HR) Management System" Stage 1: Develop HR Database System for the Academic Staff Stage 2: Extend the Database system to include administrative Staff Stage 3: Develop a prototype data mart for the HR System that will be used as basis for the development of the data warehouse for the entire university.