Hi, I need help ASAP! I have attached the Instructions, and the link to the midterm data code is: http://vizedhtmlcontent.next.ecollege.com/CurrentCourse/Midterm%20Data%20Code.sql

Thank you very much!Midterm Summer II 2017 The due date for the Midterm is 7/25/2017 (midnight). It must be submitted through the “Midterm” in Dropbox provided on the eCollege. It is opened-book, opened-note, opened-Internet, but not openedfriends or neighbor. It means that you have to do this exam by your own. You can use Internet to check your syntax, but cannot ask anyone to do the exam for you. If the midterm is not done by you, you will get 0 for the midterm. It is important that you specify your name in the assignment document/attachment. The attachment name should follow the following convention for Part I and Part II: BUSA326_MIDTERM_FIRSTNAME_LASTNAME PART1 and PART2.DOCX The attachment name should follow the following convention for Part III: BUSA326_MIDTERM_FIRSTNAME_LASTNAME PART3.SQL General Instruction: There are three parts of the exam, and please answers all of them. Even your questions are not correct, you might be able to get a partial credit. PART I: Draw ERD for each of the following situation and submit your drawings in Word format with your name on the document: 1. A company has a number of employees. The attributes of EMPLOYEE include Employee ID, Name, Address, and Birthdate. The company also has several projects. Attributes of PROJECT include Project ID, Project Name, and Start Date. Each employee may be assigned to one or more projects, or may not be assigned to a project. A project must have at least one employee assigned and may have any number of employees assigned. An employee’s billing rate may vary by project, and the company wishes to record the applicable billing rate for each employee when assigned to a particular project. (10 points) 2. A laboratory has several chemists who work on one or more projects. Chemists also may use certain kinds of equipment on each project. Attributes of CHEMIST include Employee ID, Name, and Phone No. Attributes of PROJECT include project ID, and start date. Attributes of EQUIPMENT include serial number and cost. The organization wishes to record assign date – the date when a given equipment item was assigned to a particular chemist working on a specific project. A chemist must be assigned to at least one project and one equipment item. A given equipment item need not be assigned, and a given project need not be assigned either a chemist or an equipment item. (10 points) PART II: Transform ERDs to 3-Normalization-Form (3NF) relations of the following situations and submit your drawings in Word format with your name on the document. Note: Make sure you include functional dependency notation: 1. 10 points 2. 10 points PART III (60 points total: 6 points each question): Import the midterm data code and use the following logical schema to answer all questions below. Note that you will not get credits if you use techniques haven’t taught in or before Topic 6 - Subqueries. Also, make sure to leave comments/notes for me to know which questions you want to address (-1 point if you don’t). 1. Show all faculty information. 2. Show student major, student first name and student last name. 3. Show department name and department chair. 4. Display students who withdrawn from class 5. Display the staff members who are teaching classes for which they are not accredited. 6. Find classes with no students. 7. List students not currently enrolled in any classes. 8. Show staff members who have a post office box as their address. 9. Show subject that no students want to take. 10. State your own question, and write SQL command to answer it The following criteria will be used to grade the midterm:  Diagrams are detailed, accurate, and SQL answers are correct. ***************************NOTE ************************************* Please feel free to consult your instructor by email or phone, if you have questions or need assistance!!

Attachments:

• 1510218306-Midterm Summer II 2017.pdf