To demonstrate an understanding

Published:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

Introduction

This assignment is to demonstrate an understanding of applying data modeling and data analysis techniques to the design and development of database solutions.

In the assignment, I have been sub-contracted to design the database system supporting the Academic Human Resources (AHR) for a local College. The AHR department is responsible for academic posts. The AHR department keeps information for every academic who has ever been employed by the college.

This assignment includes six tasks.

Task1

Producing an Entity Relationship Data Model which describes the content and structure of the data held by the Academic Human Resource (AHR)Department

Task2

Producing the resulting tables clearly indicating the primary keys and foreign keys

Task3

Normalizing the above resulting tables to ensure that are all in BCNF and showing the steps I have taken in normalizing the tables

Task4

Using Data Base Management System (DBMS) of my choice, setting-up all the above normalized tables and populating them with well-designed test data and providing printouts of all tables

Task5

Setting up and testing the following queries using Structured Query Language (SQL) and providing printouts of SQL code for each query and the output produced when I run the query in the database I have developed.

Query (a) is for displaying the name of all the Heads of Department together with other roles they occupy and the current post they hold

Query (b) is for displaying the name of all the current Senior Lecturers along with the name of the department they belong to and their full salary including the salary paid for the post plus any additional payments that an employee might receive for roles they occupy.

Query (c) is for displaying all the Heads of the Mathematics department from 2000 to the present date. The result should return their name, current post and qualifications.

Task6

Explaining any assumptions I have made when analyzing, designing and implementing the above database justifying my taken approach and explaining any alternative approach I could have taken to any of the above Tasks. Next is for discussing any changes I would make to improve my work.

Analysis

Task 1 is for drawing and producing an Entity Relationship Data Model (ERD) which describes the content and structure of the data held by the Academic Human Resource department. The AHR department holds information for every academic who has ever been employed by the college. Every Academic has Personal Information and Work Related Information. The AHR department keeps information for every academic's post, role and salary including additional Payment.

Assumption

When I am considering about the task 1, there are four entities in the database. They are academic, role, post and department. The academic has many to many relationship with post entity because the rules of the AHR department state that an academic may hold only one post at any time. The academic has many to many relationship with the role entity as an academic may be responsible for one or more role at any time. In a particular department of the college there may be many posts so department has one to many relationship with post. But for role a department can has many roles for academic and the identical role can be responsible for different departments. So role and department has many to many relationship.

Logical Entity Relationship Data Model for the Academic Human Resources department

Relationship Details

In logical ERD, there are four logical entities depending on the AHR department nature. Academic Entity has many to many relationship with both role and post entities. Department entity has many to many relationship with role entity and department entity has one to many relationship with post entity.

Physical Entity Relationship Data Model for the Academic Human Resource department

Relationship Details

In physical Entity Relationship Diagram, as Academic entity has many to many relationship with Role entity so I use Academic Role Detail as link entity for getting relational schema. Moreover Academic entity has many to many relationship with Post thus Academic Post Detail is used as dummy between Academic and Post. Department Detail entity is becomes link entity between Department and Role entity because these two entities have many to many relationship. Department joins Post directly because they have one to many relationship. The Academic Post Role Detail is the link entity of Academic Post Detail and Role entities.

Analysis

Task 2 is for producing the resulting tables clearly indicating the primary and foreign keys. There are four entities in logical model but in physical model there are eight entities resulting from ERD task 1. So there are eight resulting tables.

Assumption

Eight resulting tables are got from physical entity relationship diagram for database implementation. They are department table, department detail table, academic table, academic post detail table, academic role detail table, role table, academic post role detail table and post. Each table has primary key for entity integrity and the transaction tables have foreign keys and the foreign key is the same domain which is in the table the key is primary key for referential integrity constraint. The resulting tables and their primary key and foreign key constraints are mentioned in below.

Analysis

Task 3 is normalizing the above resulting tables to ensure that are all in BCNF and showing the steps I have taken in normalizing the tables. There are three form designs based on the above eight resulting tables. They are Academic Form, Department Form, and Previous Post Role Detail Form.

Assumption

There are three forms design resulting from analyzing the resulting tables. Each form is normalized to the BCNF. In the Department form, there are three entities after third normalization. For academic form, the seven entities are included. There are six entities in Previous Post Role Detail form after third normalization.

Form Design- Project Form

Analysis

Task-4 is using a Data Base Management System (DBMS) of my choice and setting-up all the normalized tables. Then it needs to populate the tables with well-designed test-data (minimum 5 records per table). The printouts of all tables need to be provided.

Assumption

I choose Microsoft Access as my choice of Data Base Management System (DBMS). I drew the file design or table design of each table in task 2. Then I drew physical database design. After that I set up the well-designed test data in each database. I also make relationship diagram for relationship between each table.

Analysis

Task-5 is setting-up and testing queries using Structured Query Language (SQL). It needs to provide printouts of SQL code for each query and output produced when I run the query in the database I have developed.

Assumption

There are three queries I need to display by using SQL code. The first query is needed to display the name of all the Heads of Department together with other roles they occupy and the current post they hold.

For the second query, it is needed to display the name of all the current Senior Lecturers along with the name of the department they belong to and their full salary. The full salary should include the salary paid for the post plus additional payments that an employee might receive for roles they occupy.

The third query is needed to display all the Heads of Mathematics department from 2000 to the present date. The result table should include the specific academics' names, current post and qualifications.

Query (a) Displaying the name of all the Heads of Department together with other roles they occupy and the current post the academics hold

SQL Statement

Analysis

Task-6 is to explain any assumptions I have made when analyzing, designing and implementing the above database, justify my taken approach and explain any alternative approaches I could have taken to above Tasks. I also need to discuss any changes I would make to improve my work.

Assumption

In task 6, I explain about my implemented database and assumption when I implemented database. I also mention my taken approach for making the database and I also write requirements of my database and other alternative approaches I can use for implementing database.

Criticisms of overall assignment

This assignment is to demonstrate an understanding of applying data modeling and data analysis techniques to the design and development of database solutions for Academic Human Resources (AHR) department for a local college.

Assumption for overall assignment

In the assignment, I have been sub-contracted to design the database system supporting the Academic Human Resources (AHR) department for local College. The AHR department is responsible for academic posts and the department keeps information for every academic who has ever been employed by the college including personal information and work related information.

For task1, I need to draw ERD which describes the content and data structure of the AHR department.

In the assignment scenario, the AHR department keeps information for every academic who has ever been employed by the college. An academic may hold various roles in the college. So I considered Academic has relationship with role. When I continued reading, there are rules for the AHR department that an academic may hold only one post at any time but they may occupy more than one role. So I assume academic has many to many relationship with both role and post entities. The college has 5 departments. Each department has more than one roles and so I considered the department has many to many relationship with role. For link entities to get relational schema, I use

For task-2, the task is about producing the resulting tables clearly indicating the primary and foreign keys. Firstly, I read the scenario thoroughly again. First I marked the resulting tables. There are eight tables. Then I distinguished primary key, foreign key and non-key attributes of each tables.

By referring the scenario and ERD, for academic table the primary key is Academic No and this is master table and store only for personal information of academic such as academic name, age, address, nationality, NRC and qualification as non-key attribute. The primary key is Role No and there is no foreign key for role table. Non-key attribute is Role Name and Additional Payment. The primary key of post table is Post No and the foreign key is Department No. There is only a non-key attribute- Post Name. For Department table, Department No is primary key and there is no foreign key and Department Name is only one non-key attribute. Department Detail table is dummy table between the Department and Role table. The composite primary key of Department Detail is Department No and Role No. So the foreign keys of that table are Department No and Role No. The Academic Role Detail table is dummy table between Academic and Role tables. The composite primary key of Academic Role Detail is Academic No and Role No. The foreign keys are Academic No and Role No. The other fields, the non-key attributes of Academic Role Detail table are Start Date and End Date. For Academic Post Detail table which is the link table between Academic and Post tables, primary key is Academic No and Post No as composite key. So it can be said that Academic No and Post No are foreign key of Academic Post Detail table. The other fields are Current Post Name, Starting Date, and Current Salary. The next table is Academic Post Role Detail table and it is the dummy table between Academic Post Detail table and Role table. The composite primary key is Academic No, Post No and Role No. Foreign keys are Academic No, Post No and Role No. The non-key attributes or other keys are Date, Post Start Date, Post End Date, Salary and Role Name..

Task-3 is to normalize these tables to ensure that are all in BCNF. By referring above result tables, I designed forms which are concerned with the database. There are three forms, Academic form, Department form, and Previous Post Role Detail form. Then I normalized the forms to the third normalized form. After that I mapped the results (relational schema) after third normalized form and physical ERD drawn in task 1 whether they are consistent.

For task-4, the requirement is for setting-up all the above normalized tables and populating them with well-designed test data (minimum 5 records per table). I first drew file design by referring result tables task 2 and above normalization. I chose Microsoft Access (2007) for DBMS. Then I set-up all the above normalized table in design view of Microsoft Access and I gave foreign key and primary key constraint , field size and data type of each table . I drew a relationship diagram of tables by referring ERD and Normalization.

Task-5 is to set-up and test queries using SQL language. Query for task-5 (a) is to display the name of all the Heads of Department together with other roles they occupy and the current post they hold. Firstly I use SELECT and FROM statement to get required entities Academic Name, Role, Current Post from associated tables and then I use WHERE statement to join the tables and check condition for the Role Name 'Head of Department'.

Query for task 5-b is to display the name of all the current Senior Lecturers along with the name of the department they belong to and their full salary including salary paid for the post plus additional payment for roles each academic occupy. Similarly, I use SELECT and FROM statement to get Academic Name, Department Name and Full Salary and WHERE to link the tables and check condition for the 'Senior Lecturer' of Current Post.

Query for task 5-c is to display all the Heads of the Mathematics department from 2000 to the present date. The result table should contain the name of specific academic, current post and qualifications. Therefore I use SELECT and FROM statement to get Academic Name, Current Post and Qualification fields and WHERE statement to join the relations and check condition for the Role Name 'Head of Department', Department Name 'Mathematics Department' and Role Start Date from '2000'. I use ">=" signs to get the date from 2000 to present date. Here I check condition after "1/1/2000" because the required date is from 2000 to present date means including 2000 so I check condition using first date of 2000.

By using the assumption and opinion for the assignment, I explain details for the steps I have taken in the assignment for task 6.

Approaches and alternative approaches I could have taken

I used Requirement specification approaches by reading Scenario. I drew Entity Relationship Diagram (ERD) and top-down approach first. Then I design tables and I used Normalization (bottom-up) approach. As an alternative I can make Normalization first and after that I can draw ERD. There are nine tables in my database. I can add other tables such as academic qualification table, work related information table and I can divide the post table into two: senior post and junior post tables in my assignment.

For query (a), I consider the given requirement Heads of Department is one of the role that the academic take now or earlier so I drew that required field from Academic Post Role Detail table and I assume an academic can take one or more roles at one time so I take other roles that academic takes from Academic Role Detail table. But otherwise I can assume that requirement is that the academic is currently taking and I drew that field from Academic Role Detail table only.

For query (b), I assume the current salary is full salary of the academic and post salary is not same with current salary so I put the current salary field in the Academic Post Role Detail table by adding post salary and additional payment. In otherwise, I can also assume the current salary is the same as the post salary and therefore I can use SUM aggregate function by adding post salary and additional payment for full salary.

For query (c), I assume the post of the Heads of Department may be the past role or current role. Moreover the academic can have two roles at one time. I store every role that is previous or current role of specific academic in the academic role detail table and current role in the academic post role detail table. So when I search academic with Head of Department role from 2000 to present date by using Start Date field of Academic Role Detail table. I use ">=" keyword for getting date. In other approaches, I can assume the role is the current role and can also search from by using "between" keyword.

Requirement

In the database, I made Academic Qualification and Personal Information tables as optional tables. So I neglected to draw the tables Moreover I use so many dummy tables Academic Role Detail Table, Academic Post Table and Academic Post Role Detail Table. So I think it is confused between the table name and it is not clearly which table is stored for which requirement. I used Work Related Information table as Academic Post Role Detail Table. There is some conflict and miss between Post No and I didn't fill every Academic and every department. So I think there may be requirement that is for Art Department there is no academic and matched Post and Role. This is because I didn't fill full data of AHR department. The data in the table are actual data but it is only for sample and not full data. I The system should also include Entity Life History (ELH) of all entities included in ERD in order to be more clear how the entity work for all the whole life and how they related.

Future Trends

I want to fill full data for AHR department and I want to build Qualification Detail table. I also make more queries for Senior Post, Grade and Resulting Outcome for each department. My database is only for AHR department. So I would like to enhance my database for all whole college database and I want to make the database can be used for all distributed systems not only for standalone system.

Writing Services

Essay Writing
Service

Find out how the very best essay writing service can help you accomplish more and achieve higher marks today.

Assignment Writing Service

From complicated assignments to tricky tasks, our experts can tackle virtually any question thrown at them.

Dissertation Writing Service

A dissertation (also known as a thesis or research project) is probably the most important piece of work for any student! From full dissertations to individual chapters, we’re on hand to support you.

Coursework Writing Service

Our expert qualified writers can help you get your coursework right first time, every time.

Dissertation Proposal Service

The first step to completing a dissertation is to create a proposal that talks about what you wish to do. Our experts can design suitable methodologies - perfect to help you get started with a dissertation.

Report Writing
Service

Reports for any audience. Perfectly structured, professionally written, and tailored to suit your exact requirements.

Essay Skeleton Answer Service

If you’re just looking for some help to get started on an essay, our outline service provides you with a perfect essay plan.

Marking & Proofreading Service

Not sure if your work is hitting the mark? Struggling to get feedback from your lecturer? Our premium marking service was created just for you - get the feedback you deserve now.

Exam Revision
Service

Exams can be one of the most stressful experiences you’ll ever have! Revision is key, and we’re here to help. With custom created revision notes and exam answers, you’ll never feel underprepared again.