I need a Dependency Diagrams for each entity type (show that there are no partial or transitive dependencies).

a complete E/R diagram that satisfies Third Normal Form,

and complete Data Dictionary

CIS4600: Homework 3 Modeling Databases using E/R Diagrams Due: 2/28/2019,11:30 PM You are required to complete following requirements for the Case *University Classes'. Identify relevant Entities. For each entity, identify Attributes, Primary Key (simple or composite), and Foreign Key s). Develop 1. Dependency Diagrams for each entity type (show that there are no partial or transitive dependencies). 2. a complete E/R diagram that satisfies Third Normal Form, and 3. a complete Data Dictionary You may use Microsoft Visio, Word, Access or any other suitable software. However, your final report should be a word document. To help you with this assignment, an EXAMPLE titled Dinner Program for Members of a Club is provided below: Dinner Program for Members of a Club [Entities and Altributes] Entity: MEMBER Attributes: MEM_NUM, MEM_NAME, MEM ADDRESS, MEM CITY, MEM_STATE, MEM ZIP Primary Key: MEM NUM Foreign Key: None Entity: INVITATION Attributes: INVITE_NUM, INVITE_DATE, DIN CODE, MEM NUM, INVITE_ACCEPT, INVITE ATTEND Primary Key: INVITE_NUM Foreign Keys: DIN CODE, MEM NUM Entity: DINNER Attributes: DIN_CODE, DIN_ DATE, DIN_DESCRIPTION, ENT_CODE, DES CODE Primary Key: DIN CODE Foreign Keys: ENT CODE, DES_CODE Entity: ENTREE Attributes: ENT CODE, ENT_DESCRIPTION Primary Key: ENT CODE Foreign Key: None Entity: DESSERT Attributes: DES CODE, DES_DESCRIPTION Primary Key: DES_CODE Foreign Key: None 1 ISpring 2019 Sample ERD ERD MEMBER INVITATION DINNER ENTREE DESSERT 2 ISpring 2019 Homework3 Sample Data Dictionary Entries for Two Entities Entity: MEMBER DATA FORMAT Autonumber 10010 Text Text Text Text ATTRIBUTE DESCRIPTION DATA TYPE Numeric Character Character Character Character Numeric SAMPLE VALUE PRIMARY | FOREIGN | DERIVED KEY? Yes No No No No No KEY? No ATTRIBUTE? Member Number Member Name MEM NUM MEM NAME MEM ADDRESS Member Address MEM CITY MEM STATE MEM ZIP ohn Doe 123 Nowhere Ave Kalamazoo Michigan 44999 No Member Ci Member State Member Zip Code No No Entity: INVITATION SAMPLE VALUE ATTRIBUTE DESCRIPTION DATA DATA FORMAT Autonumber20112390 PRIMARY FOREIGN DERIVED KEY? Yes KEY? ATTRIBUTE? INVITE NUM I INVITE DATE Invit DIN CODE Invitation Number Invitation Date Dinner Code Member Number Numeric Date Character Numeric 09-15-2011 D3-2011 No No Text Long Integer 10010 Y/N Integer Yes MEMBER No No MEM NUM Invitation Accepted Yes/No or Not How Many Attendees? No No INVITE ACCEPT Numeric No No No INVITE ATTEND 3 ISpring 2019 Sample Dependency Diagrams MEM NUM MEM NAME | MEM ADDRESS MEM CITY | MEM STATE! MEM ZIP | MEMBER Transitive Dependencies INVITE-NUMINVITE DATE DIN-CODE | MEMNUM NVITE ACCEP INVITE-ATTEND InvITATION DIN CODE DIN DATE DIN DESCRIPTION | ENT CODE! DES CODE | DINNER ENT-CODE ENT-DESCRPTON | ENTREE DES CODE DES DESCRIPTION I DESSERT 4 ISpring 2019 Homework3 Case: University Classes You are free to make assumptions if it is appropriate for the case. Please provide your assumptions in underlined texts. This scenario models the courses, students, professors, departments, and the like at a single university in a single semester. Each student enrolls in a certain number of courses in the semester. Each student receives a grade in each course he/she is enrolled in. In turn, each student evaluates the professor teaching the course. Each student has a name, a SID, and an address. A professor has a PID, a name and belongs to a department. The age of a professor can be one of young, going strong, old, very old, and still alive. Each course has a name, a number, an offering department, a classroom, a maximum enrollment, and an actual enrollment. The actual enrollment must be at most the maximum enrollment. (This university has not invented the concept of CRNs.) A student can TA a course but not a course he/she is enrolled in. Each department has a unique name. Each department has at most one chairperson who is its head (there are times when a department may not have a chairperson). Each chairperson can be the head of at most one department. Professors advise students on what courses to enroll in. Each student can have at most one advisor and must have at least one advisor. A course can have multiple pre-requisites. A course can be a pre-requisites for multiple courses. A course cannot be a pre-requisite for itself! A student enrolled in a course must have enrolled in all its pre-requisites At this university, more than one professor can teach a course. Students continue to get a single grade for each course. However, each student must provide an evaluation to each professor teaching a course the student is enrolled in. University Employees All employees have a unique ID. In addition to professors, universities also employ staff. The university pays all its employees a salary. Professors join as one of the three appointee types: 9-month appointees, calendar year appointees, or research professor appointees. Each 9-month appointee and research professor has a grant that pays part of the employee's salary. Calendar year and 9-month professors teach classes while research professors do not. 5 ISpring 2019 University Students Students enrolled in a university can be either undergraduates or graduates. Graduate students can be enrolled either in a Master's program or a Ph.D. program. Each graduate student must submit a thesis. The thesis can be uniquely identified by its Thesis ID, Each thesis must also have a title. Each student can be a TA for at most one course. Furthermore, a course can have at most one graduate student as a TA (it may have multiple undergraduate TAs) Sources: Turn in a copy of the word document on elearning on or before 2/28/2019 130 PM. The document must include the following A cover page with the Course ID, assignment number, student name and submission date. 6 ISpring 2019 Homework3 CIS4600: Homework 3 Modeling Databases using E/R Diagrams Due: 2/28/2019,11:30 PM You are required to complete following requirements for the Case *University Classes'. Identify relevant Entities. For each entity, identify Attributes, Primary Key (simple or composite), and Foreign Key s). Develop 1. Dependency Diagrams for each entity type (show that there are no partial or transitive dependencies). 2. a complete E/R diagram that satisfies Third Normal Form, and 3. a complete Data Dictionary You may use Microsoft Visio, Word, Access or any other suitable software. However, your final report should be a word document. To help you with this assignment, an EXAMPLE titled Dinner Program for Members of a Club is provided below: Dinner Program for Members of a Club [Entities and Altributes] Entity: MEMBER Attributes: MEM_NUM, MEM_NAME, MEM ADDRESS, MEM CITY, MEM_STATE, MEM ZIP Primary Key: MEM NUM Foreign Key: None Entity: INVITATION Attributes: INVITE_NUM, INVITE_DATE, DIN CODE, MEM NUM, INVITE_ACCEPT, INVITE ATTEND Primary Key: INVITE_NUM Foreign Keys: DIN CODE, MEM NUM Entity: DINNER Attributes: DIN_CODE, DIN_ DATE, DIN_DESCRIPTION, ENT_CODE, DES CODE Primary Key: DIN CODE Foreign Keys: ENT CODE, DES_CODE Entity: ENTREE Attributes: ENT CODE, ENT_DESCRIPTION Primary Key: ENT CODE Foreign Key: None Entity: DESSERT Attributes: DES CODE, DES_DESCRIPTION Primary Key: DES_CODE Foreign Key: None 1 ISpring 2019 Sample ERD ERD MEMBER INVITATION DINNER ENTREE DESSERT 2 ISpring 2019 Homework3 Sample Data Dictionary Entries for Two Entities Entity: MEMBER DATA FORMAT Autonumber 10010 Text Text Text Text ATTRIBUTE DESCRIPTION DATA TYPE Numeric Character Character Character Character Numeric SAMPLE VALUE PRIMARY | FOREIGN | DERIVED KEY? Yes No No No No No KEY? No ATTRIBUTE? Member Number Member Name MEM NUM MEM NAME MEM ADDRESS Member Address MEM CITY MEM STATE MEM ZIP ohn Doe 123 Nowhere Ave Kalamazoo Michigan 44999 No Member Ci Member State Member Zip Code No No Entity: INVITATION SAMPLE VALUE ATTRIBUTE DESCRIPTION DATA DATA FORMAT Autonumber20112390 PRIMARY FOREIGN DERIVED KEY? Yes KEY? ATTRIBUTE? INVITE NUM I INVITE DATE Invit DIN CODE Invitation Number Invitation Date Dinner Code Member Number Numeric Date Character Numeric 09-15-2011 D3-2011 No No Text Long Integer 10010 Y/N Integer Yes MEMBER No No MEM NUM Invitation Accepted Yes/No or Not How Many Attendees? No No INVITE ACCEPT Numeric No No No INVITE ATTEND 3 ISpring 2019 Sample Dependency Diagrams MEM NUM MEM NAME | MEM ADDRESS MEM CITY | MEM STATE! MEM ZIP | MEMBER Transitive Dependencies INVITE-NUMINVITE DATE DIN-CODE | MEMNUM NVITE ACCEP INVITE-ATTEND InvITATION DIN CODE DIN DATE DIN DESCRIPTION | ENT CODE! DES CODE | DINNER ENT-CODE ENT-DESCRPTON | ENTREE DES CODE DES DESCRIPTION I DESSERT 4 ISpring 2019 Homework3 Case: University Classes You are free to make assumptions if it is appropriate for the case. Please provide your assumptions in underlined texts. This scenario models the courses, students, professors, departments, and the like at a single university in a single semester. Each student enrolls in a certain number of courses in the semester. Each student receives a grade in each course he/she is enrolled in. In turn, each student evaluates the professor teaching the course. Each student has a name, a SID, and an address. A professor has a PID, a name and belongs to a department. The age of a professor can be one of young, going strong, old, very old, and still alive. Each course has a name, a number, an offering department, a classroom, a maximum enrollment, and an actual enrollment. The actual enrollment must be at most the maximum enrollment. (This university has not invented the concept of CRNs.) A student can TA a course but not a course he/she is enrolled in. Each department has a unique name. Each department has at most one chairperson who is its head (there are times when a department may not have a chairperson). Each chairperson can be the head of at most one department. Professors advise students on what courses to enroll in. Each student can have at most one advisor and must have at least one advisor. A course can have multiple pre-requisites. A course can be a pre-requisites for multiple courses. A course cannot be a pre-requisite for itself! A student enrolled in a course must have enrolled in all its pre-requisites At this university, more than one professor can teach a course. Students continue to get a single grade for each course. However, each student must provide an evaluation to each professor teaching a course the student is enrolled in. University Employees All employees have a unique ID. In addition to professors, universities also employ staff. The university pays all its employees a salary. Professors join as one of the three appointee types: 9-month appointees, calendar year appointees, or research professor appointees. Each 9-month appointee and research professor has a grant that pays part of the employee's salary. Calendar year and 9-month professors teach classes while research professors do not. 5 ISpring 2019 University Students Students enrolled in a university can be either undergraduates or graduates. Graduate students can be enrolled either in a Master's program or a Ph.D. program. Each graduate student must submit a thesis. The thesis can be uniquely identified by its Thesis ID, Each thesis must also have a title. Each student can be a TA for at most one course. Furthermore, a course can have at most one graduate student as a TA (it may have multiple undergraduate TAs) Sources: Turn in a copy of the word document on elearning on or before 2/28/2019 130 PM. The document must include the following A cover page with the Course ID, assignment number, student name and submission date. 6 ISpring 2019 Homework3