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.

2. Create ER diagram showing global logical data model.

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.