CIS 8040 Fundamentals of Management Systems Assignment 2 Due date: 2/1/2017 at 11: from a B- to a C-), and a charge of academic dishonesty.

CIS 8040 Fundamentals of Management Systems 1 Assignment 2 Due date : 2/1/2017 at 11:50 PM This assignment is an individual assignment, not a team assignment. You must complete the assignment independently and submit your original work. “Plagiarism or duplicate lab assignments will be given a grade of ‘zero’, a point deduction equivalent to one final grade level (i.e. from a B - to a C -), and a charge of academic dishonesty. Both the person copying the assignment and the person supplying the copy will be penalized equally .” Study the given conceptual ER model diagram in each problem and perform the following two tasks .

1) Map the conceptual model into relations (a logical model) following the mapping rules. You should show the mapping of each entity type and each relationship type and provide a brief explanation of the mapping. Each relation should include the relation name, attribute names, primary key (underlined ), and foreign keys marked by FK subscript if any. After the completion of all mapping steps, list the final set of relations. 2) Perform normalization of each mapped relation. You should (a) identify all candidate keys, primar y key and non -prime attributes , (b) show the functional dependencies between the attributes including full dependencies and transitive dependencies , (c) determine the normal form of the relation with a brief explanation, (d) normalize the relation into 3NF relations if it is not in 3NF. It is possible in the task 2 (d) above that you decide not to normalize a mapped relation into 3NF relations . In this case, you are required to provide a clear explanation of and argument for your decision. Note that you should provide sufficient explanations t o your answers . Otherwise, the answers will be interpreted by the reviewers. When it happens, the interpretations are usually different from your s and may lead to a conclusion that your solutions contain errors. CIS 8040 Fundamentals of Management Systems 2 Problem 1 (30%) The follo wing conceptual model represented a grade report user view in a school. It assumed that each section is taught by only one instructor. During the scheduling of classes for a semester, a course section has neither any student registered for it, nor any instructor assigned to teach it . After the class schedule is published, the classes are assigned instructors and allowed students to register for them . Problem 2 (3 5%) The following conceptual model showed a solution to Proble m 2 in Assignment 1. Student Instructor Office Term (0, m)(0, m) Take Grade Day Time (0, m)(0, m) Phone City Street State Zip code GPA Address Section Section ID Room (0, m)(0, m) Course Course number Title # of Credits Teach (0, 1)(0, 1) Offer (1, 1)(1, 1) (0, m)(0, m) Person Email ID Name Employee Project Department Vendor Report (1, 1)(1, 1) (0,m)(0,m) supply Location (0,m)(0,m) (1,m)(1,m) (1, 1)(1, 1) Assign (0,m)(0,m) (0,m)(0,m) Emp Skill (0,m)(0,m) Emp Num Name DoB Title Marry (1,1)(1,1) (0,1)(0,1) Date of Marriage Dept Name Phone Data of Meeting Vendor ID Name Address Skill Num city State Population Proj Num Cost Desc Use (1,1)(1,1) (1,m)(1,m) Locate (1,m)(1,m) (1,m)(1,m) Possess (1,m)(1,m) (1, 1)(1, 1) CIS 8040 Fundamentals of Management Systems 3 Problem 3 (35%) The following ER diagram showed a solution to Problem 3 in Assignment 1. Assignment Deliverables and Assessment The assignment report should include the given ER model of each problem , the model mapping and the normalization of all mapped relations. The assignment answers are assessed based on the following rubric. Task Criteria Explanation Weight Logical design (50% ) Relation mapping Correctly mapped the model 70% Mapping explanation Sufficient explanation of each step 20% Relations Completeness of each relation & the final list of relations 10% Normalization (50% ) Analysis of each relation Correctly identified all candidate keys, primary keys and non -prime attributes 20% City Street State Zip code City Street State Zip code Name Title Phone Name Title Phone Name Tech Consultant Business Consultant Service Customer Location Estimate Perform (0,m)(0,m) (0,m)(0,m) (0,m)(0,m) (1,m)(1,m) Locate (1,m)(1,m) (1, 1)(1, 1) (0,m)(0,m) (0,m)(0,m) (0,m)(0,m) EmpID Phone DoB Age Bus. Type # of Years Degree Received Tech Skill CustID Num of Employees Company Name City Street State Zip code Address Consultant Address Business Type Contact Location ID City Street State Zip code Address Phone Building Size Service Desc Cost Coverage Clearance Date Amount Date Amount CIS 8040 Fundamentals of Management Systems 4 Functional dependencies Correctly identified all full dependencies and transitive dependencies 40% Determination of normal forms Correctly determined the normal forms of mapped relations , normalize non -3NF relations to 3NF relations if any 20% Normalization explanation Sufficient explanation of the normalization process 20% The assignment report should be submitted into the corresponding assignment drop box in iCollege. Email submissions are not accepted. Labe submission is accepted until 5:00 PM before the next class with 10% grade reduction penalty.