Database Implementation at Corporation - Essay Example

New Database Specification Report A report on analysis of database implementation at corporate] ____________ Submitted _____ Institute Acknowledgements First of all I want to say thanks to my lecturer ………………..for helping a lot for this project. He has assisted me at each step and help me a very much during this project. Next want to pay my thanks to my friends for helping during this project. Name Table of Contents Acknowledgements 2 1Executive Summery 5 2Introduction 5 3Problem Situation 6 4Proposed Solution 9 5 Proposed Database 9 6Advantages of Excel modeling 10 7Problems of Excel modeling 10 8Benefits of Access Modeling 11 9Problems with Access Modeling 11 10Recommendations 12 11Advantages of Desktop modeling 12 12Problems of Desktop modeling 12 13 Logical Design 13 1Tables 13 2Description of Attributes 16 14Physical Design 18 1Step 1: Table Formatting 18 2 Step 2- Importing data 20 3Step 4: Formatting table Structure 21 4Step 6: forming relationship 23 5Step 7: Setting Queries 24 15Exporting data to Excel 33 16Performing process to Exported data 34 17Tangible Benefits 36 18Intangible Benefits 37 19Conclusion 37 Bibliography 38 1 Executive Summery At the present information is the most valuable resource for any business or corporation. Therefore, the development and implementation of databases to store this valuable information have turned out to be major part of almost every corporation or business sector. In addition, this development and implementation of the databases have offered great advantages to the organizations in effective management of their business activities. This report is about the development and analysis of a database system for a recruitment agency that is operating worldwide and specializing in short contracts for programming and website development work. This report will offer a deep analysis of the main database design and development aspects for the business of recruitment agency. 2 Introduction According to Oz Effy (2008), the processes of decision making and problem solving both are based on information. In addition, the decision making is vital process of everyone’ life, people work, play, communicate or do anything they need and use information. In the same way, in a corporation gathering the correct information efficiently, keeping it properly consequently thus it could be utilized and manipulated when needed, as well as making use of it to facilitate the corporation or individuals to attain personal or business objectives. Consequently, the main intention of database and information systems development and implementation is to have facility and support in the above discussed areas. We can use and apply these understanding and database information for getting success in every department of a business. However, in a corporation information system consists of databases, hardware, data, telecommunications, software, procedures and people. In addition, the use of information systems and databases can be seen in every walk of life. Their capacity to facilitate and resolve problems as well as making decisions makes them fundamental in our lives, society, business and management (Oz, 2008; Whitten, Bentley, & Dittman, 2000; Turban, Leidner, McLean, & Wetherbe, 2005). The recruitment agency has carried out a survey among 30,000 people and taken response against different personnel, job and web development related aspects. This data is available in form of flat file database. In other words, the data is available in form of a single table. In addition, this table contains huge number of rows and columns, which are not providing valuable information to gain knowledge or analysis of information. For this reason we are going to develop a new database application for the overall management and handling the surveyed information and data. This report will provide a comprehensive overview of the step by step data analysis and system designing and development. 3 Problem Situation As I have stated earlier that recruitment agency business has performed a survey that was conducted among 30,000 people. These people have answered the particular questions and responded to the given query. This responded data is stored in a single file flat database. The problem is that the amount of data is huge. Thus, we can’t perceive some valuable information from this data. This data is presently having no worth because the extraction of the valuable facts and figures is really harder. Flat database is comparatively simple database system in which each database encompasses a single table. For instance, there is a single table of whole data. This type of data is used for recording of simple data such as phone number and names (Hoffer, Prescott, & McFadden, 2007). These are used when we have simple entries. For instance, it can be used by small business, house record or as a phone directory. Below I have presented the image of the flat file database; Figure 1- Flat database Business also having problems in following areas of information management Event handling Huge data management Daily operations handling Response and management Resource handling Facility analysis HR management Table is not in normal form. There are a lot of aspects those need to be resolved in order to make this table in normalized form. Here main problems we are having in this table are about the table primary key. There is no primary key. There are also a lot of anomalies in this data. For example in case of adding a new data we have to put a lot of effort. Here we are also having a lot of problems like wastes storage space, wastes data entry time and data inconsistency. Data redundancy is an issue that is related to the data organization that permits the needless duplication of data inside our database. A modification or change, to redundant data, needs that we formulate a change to several fields of a database. As this is the probable behaviour intended for flat file database structural designs as well as spreadsheets, it overcame the principle of relational database designs (Databasedev, 2009). Data anomalies are anything that we try to perform by means of a database that directs to unpredictable or unexpected results. There are three types of data anomalies: insert delete update Data anomalies happened when we are trying to formulate a change like insert into, update or delete from a data table, in this scenario unexpected or undesired side-effects could happen. These could be acknowledged as the data anomalies (Chapple, 2009). 4 Proposed Solution The effective management of above given operations is possible with the new database system. In addition, this will facilitate while developing and planning information for new employments. As Chou, Bindu, & Chou (2005) outlined that corporate information is the base of each critical corporate decision making (Chou, Bindu, & Chou, 2005). In this scenario we are going to develop a database to store this huge information that will be more effective as compared to present data. This database will be an MS Access based database that will offer a more valuable information about the overall system operations and staff information. The new proposed system will offer following facilities; Back-office information management Web based recruitment management Finance management Recruiting areas Management HR management On time Recruiting information New Technology based infrastructure Easy way of working Better data collection Easy management of the data Less conflicts among data formats Easy data sharing among all division of the corporate Less or no dirty data Onetime handling of operations Enhanced decision making 5 Proposed Database The proposed database will be an MS Access based database that will facilitate regarding the management and handling of the overall business data. The main aim of this database is to capture and manage the business operational data and handling the incoming and outgoing business information. Relational database is a based on the two-dimensional table that consists of rows and columns. The data stored in this database is based on the relation among the rows and columns. The object-oriented standard and development environment is new and offering a lot of operations and development facilities nowadays. Here RDBMS offers better data consistency, effective sharing of data, as well as enhanced security as well as integrity. The relational database management systems are the most widespread database technology that offers better data management and storage. The main significance of relational database management systems is its easy development and quick implementation (Connolly & Begg., 2008). 6 Advantages of Excel modeling The management and handling of the data through the Excel based data modelling has a lot of advantages for the present business data and extracted information. Some of the advantages are given below: Simple to use Easy to enter (data can be inserted easily) Less complex structure (simple interface) Single sheet for all data Flat database for easy access 7 Problems of Excel modeling There are a lot of problems associated with the Excel based data modeling for the business. These are the main problems those have placed the need for the development of the database for the business. Some of the problems happened due to excel based modeling are given below: Long search time Huge information hard to manage Huge information hard to edit Huge information hard to analyze Difficult manipulation of data Long time for updating record Impossible to search and asses records 8 Benefits of Access Modeling The main benefits of the Access based modeling are long term benefits for the business. I have outlined some of them below: Better management of the business data Effective preservation of information Easy handling of operations Simple to use interface On time reports Less complex data presentation Easy way to extract the required business data On time and better business decision through effective business reports Simple way to extract business information Better dealing with Business employs and applicants 9 Problems with Access Modeling The implementation of the Access Modeling for the business also encompasses some of problems. I have outlined some of them below: Constant data management problems Virus Attacks Information theft Complex Query management Continues updates required Less flexible for non-computer literates More cost spending for printing and analysis 10 Recommendations In case of establishment of new business database system we need to consider the effective management of the business system and its operational aspects. In addition, we need to manage the security of database system by using the effective antivirus system and password based approach. The main reason is to protect the business data because business information is a most valuable business resource thus, the security of this significant resource is very necessary. 11 Advantages of Desktop modeling In case of the development of the desktop based application we could have a lot of advantages regarding the business. Some of the main advantages are given below; Simple to use All time access Easy handling Less complexity regarding use No extra facilities needed like internet connectivity 12 Problems of Desktop modeling In case of development and implementation of desktop based system for the business we are having some of the problems those can be: No effective data sharing No remote access No direct feeds Limited system 13 Logical Design For the better development of the database initially we need to develop a logical design of database. For this purpose we have performed an entity relationship analysis and produced the entity relationship diagram that is attached with the file. 1 Tables Here I have presented all the tables and overall structure of table and presented the main attributes and primary keys of the table. Here the data type of the data is also outlined. 2 Description of Attributes No is intended for My Number Age is intended for Tell us about yourself. What is your age? Gender is intended for what is your gender? Ethnicity is intended for what is your ethnicity? Job_title is intended for Regardless of your job title Job_Area is intended for Job_Area we are working Competence is intended for areas does your employer expect you to be proficient?" Blog_web_site is intended for have you got a blog or other personal website? Blog_web_site_publish is intended for how long have you published your blog/website? Education is intended for what is your educational background? Studies_ related is intended for where your college studies directly related to your work as a web Info_source is intended for how do you stay current with your craft? Located is intended for where are you located? Organization_employ is intended for what kind of organization do you work? Job_time_web_professional is intended for how many people does your company or organization employ? Hours_per_week is intended for how many years have you been working as a web professional? Time_for_web_design is intended for on average, how many hours per week do you work? Present_job_years is intended for how much of your work revolves around web design? job_Performed is intended for how many years have you been at your present job? Annual_paid_vacation is intended for how many web design jobs have you held? Paid_holidays_per_week is intended for how many paid holidays do you have per year? Earn_in_2006 is intended for what did you earn in 2006? Include bonuses and freelance income. Pay_raise is intended for how big was your last pay raise? Time_Pay_raise is intended for how long ago was your last pay raise? Web_design_profession is intended for do you find web design to be an exciting profession? S0lowed_progress is intended for has your geographical location slowed the progress of your career, Prejudice_age is intended for has employer or client prejudice about your age slowed the Prejudice_gender is intended for has employer or client prejudice about your gender slowed the Prejudice_race is intended for has employer or client prejudice about your race, ethnicity Career is intended for what is your next career move? 14 Physical Design After the development of the logical design we have developed the physical design of the database that is presented in step by step fashion below: 1 Step 1: Table Formatting In this step I have formatted the overall table structure and form the different tables of data according to data types and operational purpose. This table design is extracted from the entity relationship diagram. Figure 2- Formed Table structure 2 Step 2- Importing data Below image shows the processes of importing data from excel to Access database. Figure 3- Importing data Figure 4- Table Imported 3 Step 4: Formatting table Structure After importing data I have formatted the table structure as below: Figure 5- formatting table structure Figure 6- Final Tables 4 Step 6: forming relationship Here we have developed the relationship for the overall database formation. Figure 7- forming relationship 5 Step 7: Setting Queries Below I have presented the different queries that I have formulated for database operations. Other queries are also possible: Figure 8- Query 1 SQL View SELECT Personal.[No], Personal.Age, Personal.Gender, Personal.Located, Education.Education FROM Education INNER JOIN Personal ON Education.[No] = Personal.[No] WHERE (((Education.Education)="Masters")); Repot result People Having Masters Degree No Age Gender Located 8 25-32 Male EU (except the UK) 43 21-24 Male United Kingdom 50 33-38 Male United Kingdom 66 21-24 Male US: Midwest 99 25-32 Male EU (except the UK) 111 25-32 Male EU (except the UK) 121 25-32 Male US: Northwest 134 25-32 Male Australia and Pacific Rim 135 25-32 Male Australia and Pacific Rim 142 33-38 Male EU (except the UK) 152 33-38 Male India 187 25-32 Male US: Southwest 191 25-32 Male US: Southwest 193 25-32 Male Non-EU Europe 195 51-60 Male US: Southwest 203 33-38 Male EU (except the UK) 212 25-32 Male EU (except the UK) 229 25-32 Male EU (except the UK) 242 25-32 Male EU (except the UK) 255 25-32 Male Non-EU Europe 262 25-32 Male EU (except the UK) 269 25-32 Male EU (except the UK) 270 33-38 Male EU (except the UK) 277 39-50 Male EU (except the UK) 282 33-38 Male Asia (except India) 305 33-38 Male US: Northwest 310 21-24 Male EU (except the UK) 335 25-32 Male EU (except the UK) 339 25-32 Male EU (except the UK) 344 25-32 Male EU (except the UK) Thursday, February 11, 2010 Page 1 of 19 Query 2 Figure 9- Query 2 SQL View SELECT Experence.Competence, Personal.Age, Personal.Gender, Personal.Located, Personal.[No] FROM Experence INNER JOIN Personal ON Experence.[No] = Personal.[No] WHERE (((Experence.Competence)="Graphic design") AND ((Personal.Age)="25-32") AND ((Personal.Gender)="female")) OR (((Personal.Located)="Canada")); Report 2 People Having Graphic Designing Experence No Age Gender Located Competence 2 21-24 Male Canada Graphic design 137 33-38 Male Canada Graphic design, Page layouta, Interface Design, Information Architecture, Wireframing, Sitemapping, Markup (HTML, XHTML), CSS coding, Usability testing/knowledge, Front-end programming (e.g. JavaScript), Back-end development (e.g. PHP, Ruby on Rails, ASP), Image editing/production, Writing/Editing 147 33-38 Female Canada Graphic design, Page layout, Interface Design, Markup (HTML, XHTML), CSS coding, Usability testing/knowledge, Accessibility testing/knowledge, Front- end programming (e.g. JavaScript), Back-end development (e.g. PHP, Ruby on Rails, ASP) 150 21-24 Male Canada Graphic design, Page layout, Interface Design, Information Architecture, Wireframing, Sitemapping, Markup (HTML, XHTML), CSS coding, Usability testing/knowledge, Accessibility testing/knowledge, Front-end programming (e.g. JavaScript), Back-end development (e.g. PHP, Ruby on Rails, ASP), Image editing/production, Writing/Editing, Other 168 25-32 Male Canada Graphic design, Page layout, Interface Design, Information Architecture, Wireframing, Sitemapping, Markup (HTML, XHTML), CSS coding, Usability testing/knowledge, Accessibility testing/knowledge, Front-end programming (e.g. JavaScript), Image editing/production 173 33-38 Male Canada Graphic design, Page layout, Interface Design, Markup (HTML, XHTML), CSS coding, Usability testing/knowledge, Accessibility testing/knowledge, Front- end programming (e.g. JavaScript), Image editing/production 219 33-38 Male Canada Page layout, Interface Design, Markup (HTML, XHTML), CSS coding, Usability testing/knowledge, Back-end development (e.g. PHP, Ruby on Rails, ASP) Thursday, February 11, 2010 Page 1 of 25 Query 3 Figure 10- Query 3 SQL View SELECT Personal.[No], Personal.Age, Personal.Gender, Personal.Located, Financial.Earn_in_2006, Financial.perk FROM Financial INNER JOIN Personal ON Financial.[No] = Personal.[No] WHERE (((Financial.Earn_in_2006)="under $10,000") AND ((Financial.perk) Is Null)); Report 3 People Having Less $10,000 Annual Income And Taking No Perk No Age Gender Located Earn_in_2006 35 21-24 Male US: Southwest under $10,000 36 39-50 Male US: Southeast under $10,000 38 21-24 Male US: Southwest under $10,000 47 21-24 Female Australia and Pacific Rim under $10,000 56 Under 21 Male US: Northeast under $10,000 90 21-24 Male US: Northwest under $10,000 143 21-24 Male US: Southwest under $10,000 164 21-24 Male Asia (except India) under $10,000 171 21-24 Male Central America, under $10,000 199 21-24 Male US: Northwest under $10,000 225 Under 21 Male US: Southwest under $10,000 247 Under 21 Male US: Midwest under $10,000 254 Under 21 Male US: Northwest under $10,000 356 21-24 Male Non-EU Europe under $10,000 401 25-32 Male EU (except the UK) under $10,000 472 25-32 Male US: Southwest under $10,000 514 21-24 Male EU (except the UK) under $10,000 541 Under 21 Male US: Northwest under $10,000 591 21-24 Male EU (except the UK) under $10,000 700 21-24 Male EU (except the UK) under $10,000 735 25-32 Male United Kingdom under $10,000 747 21-24 Male EU (except the UK) under $10,000 811 Under 21 Male United Kingdom under $10,000 813 25-32 Male EU (except the UK) under $10,000 877 Under 21 Male Australia and Pacific Rim under $10,000 932 21-24 Male EU (except the UK) under $10,000 972 21-24 Male EU (except the UK) under $10,000 1003 25-32 Male EU (except the UK) under $10,000 1031 21-24 Male EU (except the UK) under $10,000 1051 25-32 Male United Kingdom under $10,000 Thursday, February 11, 2010 Page 1 of 9 Query 4 Figure 11- Query 4 SQL View SELECT Personal.[No], Personal.Gender, Personal.Located, Web_Blog.Blog_web_site, Web_Blog.Blog_web_site_publish FROM Web_Blog INNER JOIN Personal ON Web_Blog.[No] = Personal.[No] WHERE (((Personal.Gender)="female") AND ((Personal.Located)="Canada") AND ((Web_Blog.Blog_web_site)="Yes")); Report 4 All Females Having Website or Blogging Also Located in Canada No Gender Located Blog_web_site_publish 147 Female Canada 1 year or less 628 Female Canada 7-8 years 1689 Female Canada 4-5 years 1928 Female Canada 3-4 years 2015 Female Canada 2-3 years 2207 Female Canada 4-5 years 2297 Female Canada 1 year or less 2380 Female Canada 4-5 years 2462 Female Canada 5-6 years 2509 Female Canada 1-2 years 2552 Female Canada 7-8 years 2592 Female Canada 6-7 years 2632 Female Canada 6-7 years 2657 Female Canada 1 year or less 2693 Female Canada 3-4 years 2779 Female Canada 1-2 years 2840 Female Canada 5-6 years 3013 Female Canada 7-8 years 3226 Female Canada 9-10 years 3963 Female Canada 3-4 years 4001 Female Canada 2-3 years 4068 Female Canada 4-5 years 4176 Female Canada 6-7 years 4185 Female Canada over 10 years 4202 Female Canada 2-3 years 4328 Female Canada 1 year or less 4364 Female Canada 7-8 years 4444 Female Canada 5-6 years 4463 Female Canada 8-9 years 4562 Female Canada 5-6 years Thursday, February 11, 2010 Page 1 of 2 15 Exporting data to Excel Here I have exported the data from the Access Database to Excel Data. Figure 12- Exporting process Figure 13- Exported data to Excel 16 Performing process to Exported data Applying Filter Figure 14- Applying Filter Figure 15- Applying Filter and extracted data 17 Tangible Benefits Better data communication Enhanced data sharing Effective management of operations Reliable communication Better and secure operating environment Rapid user response Centralized data centers No huge burden of complex data Less rigid structure More extendibility features Effective management control Enhanced monitoring 18 Intangible Benefits Better market share Effective market position Rapid response to client Prominent market positions Increased client and application confidence 19 Conclusion This report encompasses the whole process of database development for a recruiting agency. The data was provided in a Excel file, which was containing useless information. And that data was not informative for a general person. Thus, we planed to develop a database using Excel data. A database is a collection of interrelated tables, thus the information retrieval becomes easy in the database. In addition, it also provides useful information. This report has discussed the advantages and disadvantages of both the platforms. This report has also outlined some of the main aspects of the database implementation, operating and deployment. I hope this report will offer deep insight into new database establishment. Bibliography Alam, M., & Wasan, S. K. (2006). Migration from Relational Database into Object Oriented Database. Journal of Computer Science 2 (10):Science Publications ISSN 1549-3636 , 781-784. Bordoloi, B., Agarwal, A., & Sircar, S. (1994). Relational or Object-oriented or Hybrid? International Journal of Operations & Production Management, Vol. 14 No. 9, , 32-44. Chapple, M. (2009). Database Normalization Basics. Retrieved 02 18, 2010, from http://databases.about.com/od/specificproducts/a/normalization.htm Chou, D. C., Bindu, H., & Chou, A. Y. (2005). BI and ERP integration. Information Management & Computer Security: Emerald Group Publishing Limited , 340-349. Connolly, T. M., & Begg., C. E. (2008). Database Systems: A Practical Approach to Design, Implementation and Managemen tthird edition. Databasedev. (2009). Data Redundancy Defined. Retrieved 02 17, 2010, from http://www.databasedev.co.uk/data-redundancy.html Hoffer, J. A., Prescott, M. B., & McFadden, F. R. (2007). Modern Database Management, Eighth Edition. Pearson Education, Inc. Jawadekar, W. S. (2004). Software Engineering: Principles and Practice. New York: McGraw Hill. Oz, E. (2008). Management Information Systems, Sixth Edition . Thomson Cource Teachnolgy. Pratt, P. J., & Adamski, J. J. (2007). Concepts of Database Management, 6th Edition. Course Technology. Pressman, R. S. (2001). Software Engineering: A Practicioners Approach, 5th Edition. London: McGraw Hill. Silver, M. S., Markus, M. L., & Beath, C. M. (1995, September ). THE INFORMATION TECHNOLOGY INTERACTION MODEL:. Retrieved 01 05, 2010, from Management Information Systems Quarterly, volume 19, no. 3, pp. 361-390: http://www.bnet.fordham.edu/public/ics/msilver/itmodel.htm Sommerville, I. (2004). Software Engineering, 7th Edition,. New York: Pearson Education (Addison Wesley). Turban, E., Leidner, D., McLean, E., & Wetherbe, J. (2005). Information Technology for Management: Transforming Organizations in the Digital Economy (4th ed.). New York: Wiley. Whitten, J. L., Bentley, L. D., & Dittman, K. C. (2000). Systems Analysis and Design Methods 5th Edition. New York: Irwin/McGraw-Hill. Read More