Dear Tutor,

Attached please find my assignment. Could you help me?

Thanks in advance .

Database Design Problem You have been hired to review the accuracy of the books of Northeast Seasonal Jobs International (NSJI), a job broker. NSJI matches employers whose business is seasonal (like ski resorts) with people looking for part time positions at such places. Employers are located in the Northeast (New England), but recently expanded to include parts of Canada. NSJI maintains all of this information in a flat file spreadsheet (see attached layout of the spreadsheet structure). Required: 1. 1) From a reviewer's perspective, explain what is wrong with NSJI's approach to manage its data. Be specific, please! Do you think these problems are serious from an auditor's perspective? From the company perspective? 2. 2) To reduce the possible impact of these potential problems, what advice would you give to the company? 3. 3) Use the spreadsheet structure provided, to create a conceptual model (entity relationship diagram - ERD) for NSJI. 4. 4) Using the ERD and business rules described below, design a database model, transformed to 3NF (third normal form). As a guide, use the attached \"Database Normalization.PPT\" file, to guide you in creating your final model of NSJI database tables and relationships. Business Rules You have ascertained from an interview with your client (NSJI): Contact position is fixed - does not change (i.e. it is a field of Employer) \"comments\" is a field of Employer, not Position NAICS stands for \"North American Industry Classification System\" \"Openings\" is a field of Position, not Employer \"Phone\" is a field of Employer \"Position ID\" is unique for all employers Database Normalization Structure of tables must comply with several rules called normal forms to transform data tables that are not in normal form into tables that comply with the rules. Failure to normalize results in anomalies: errors that might occur when adding, changing, or deleting data stored in the database. The goal of normalization is to produce a database model that contains relations that are in third normal form. Functional Dependence An attribute (column in a table) is functionally dependent on a second attribute (or a collection of other attributes), if a value for the first attribute determines a single value for the second attribute at any time. When functional dependence exists, the first attribute determines the second attribute. Primary Keys Primary key: a value that uniquely identifies a specific row in a table. Typically stored in the tables first column. Composite primary key: two or more columns that provide a primary key for each row. A candidate attribute (a column or collection of columns) is that table's primary key if: - All attributes in the table are functionally dependent on the candidate attribute. - No collection of other columns in the table, taken together, has the first property. Unnormalized Relation First Normal Form Unnormalized table: contains repeating groups (repeating attributes [fields] within each row [record]). A table is in first normal form (1NF) if it doesn't contain repeating groups. Relation in First Normal Form Problems with First Normal Form Include functional dependencies called update anomalies. As a result: - Data may be inconsistent. - Updates may require changes to multiple rows. - Additions and deletions are problematic. Partial dependencies: attribute is dependent on a portion of the primary key. Second Normal Form (2NF) A table is in second normal form (2NF) if it is in 1NF and has no partial dependencies. It has no non-key attributes that are dependent on only a portion of the primary key. Non-key attributes: an attribute that is not part of the primary key. Two steps to get from 1NF to 2NF 1. Create a new table for each subset of the table that is partially dependent on a part of the composite primary key. 2. Place each of the non-key attributes that are dependent on a part of the composite primary key into the table that now has a primary key that is the field on which the non-key attribute is partially dependent. Relations in Second Normal Form Third Normal Form (3NF) A table is in third normal form (3NF) if it is in 2NF and has no transitive dependencies. A transitive dependency exists when a nonkey attribute is functionally dependent on another non-key attribute. Relations in Third Normal Form