Tasks: Your group will be tasked with completing the following scenario: The client organization is continuing to expand their digital infrastructure and your team have been brought in as system consultants to help develop an employee datastore. An on-site Systems Analyst has generated a list of the organizations assessed needs based on elicitation [interviews] with key stakeholders and documentation analysis of existing/planned Information Systems. For the client organization to complete their work they require you to; design a data model, the database shell, insert testing data, verify the databases functionality and then remove the test data. 1) Develop a data model of the proposed employee database utilizing crow's foot notation. You are to utilize the entity and attribute names exactly as written, do not make any modifications. (After submission, a selection of three tables will be made by your professor as a sample of your work for grading purposes) - EMPLOYEe (EmployeelD RoleID, ManagerID, DepartmentID, EmpAddressID, FName, LName, Salary, HireDate, Probation, PersonaleMail, PersonalPhoneNumber) - ROLES (RoleID, PositionTitle, PositionDescription, ProbationLength, MinSalary, MaxSalary) - DEPENDENTS (DependentID, EmployeeID, Relationships, FName, LName, DateOfBirth) - EMPLOYEEPERSONAL (PersonalID, EmployeeID, DateOfBirth, SocialSecurityNumber, AllergyNc AllergyDesc, EpiPen, DisabilityName, DisabilityDesc, Gender) - DEPARTMENTS (DepartmentID, FacilityID, DepartmentName, DepartmentDesc) - FACILITYADDRESS (FacilitylD, CountryID, FacilityName) - EMPLOYEECONTACT (EmpContactID, EmployeeID, FacilityID, WorkExtension, OfficeNumber) - EMERGENCYCONTACTS (ContactID, EmployeeID, FName, LName, ContactNumber, ContactAltNumber) - ADDRESS (AddressID, Facility, StreetNumber, StreetName, StreetType, PostalCode, City, Province) - COUNTRIES (CountryID, RegionID, CountryName) - EMPLOYEEADDRESS (EmpAddressID, CountryID, HomeType, UnitNumber) - REGIONS (RegionID RegionName) 2) Create the CREATE TABLE scripts which will build the tables as identified above. Your database name must contain your group name and adhere to the following referential Integrity constraints which must be populated within the create table script, they are as follows; - EmployeelD in DEPENDENTS must exist in EmploveelD in EMPLOYEE - EmployeeID in EMPLOYEEPERSONAL must exist in EmployeeID in EMPLOYEE - EmployeeID in EMPLOYEECONTACT must exist in EmployeelD in EMPLOYEE - EmployeeID in EMERGENCYCONTACT must exist in EmployeelD in EMPLOYEE - RoleID in EMPLOYEE must exist in RolelD in ROLES - DepartmentID in EMPLOYEE must exist in DepartmentID in DEPARTMENT - ManagerID in EMPLOYEE must exist In EmployeeID in EMPLOYEE but will allow NULL - EmpAddressID in EMPLOYEE must exist in EmpAddressID in EMPLOYEEADDRESS - EmployeeID in EMERGENCYCONTACTS must exist in EmployeeID in EMPLOYEE - FacilityID in DEPARTMENTS must exist in FacilityID in FACILITYADDRESS - FacilityID in ADDRESS must exist in FacilityID in FACIIITYADDRESS - CountryID in FACILITYADDRESS must exist in CountryID in COUNTRIES - CountryID in EMPLOYEEADDRESS must exist in CountryID in COUNTRIES - RegionID in COUNTRIES must exist in RegionID in REGIONS Autoincrement value; - EmployeelD starts at 1117 and increments by 17 - DepartmentID starts at 10 and increments by 10 - EmpAddressiD starts at 10000 and increments by 2 - FacilityID starts at 1 and increments by 1 Requirements; - Set the default value for ContactNumber and ContactAltNumber in EMERGENCYCONTACTS to 911 - Write a constraint to ensure that CountryName is limited to 6 countries (Canada, Columbia, Brazil, Uganda, Vietnam, Indonesia) 3) Create a SQL INSERT script that will insert at least 5 records for each table in the database. Ensure that referential integrity has been upheld and that all of your PK-FK fields properly share data. 4) Create a SQL UPDATE script to modify one RolelD in the Roles Table to x000, where x is your group number. - le,. Group 5 would put in 5000 as the updated role ID 5) Create a view that will display each manager with all of their subordinates. You will need to use aliasecfoctable names to create a view based on a recursive relationship. Your output must also include any em not currently have a supervisor. 5) Create a view that will display each manager with all of their subordinates. You will need to use aliases for table names to create a view based on a recursive relationship. Your output must also include any employees who do not currently have a supervisor. 6) Create a view to display all employees who have allergies, but do not have an emergency contact. 7) Write a SQL script that will delete all test data from the database. Submission parameters: 1) .PDF file: Question 1, Data model using Crow's foot notation 2) .SQL file: Question 26 3) SQL file: Question 7 Format Requirements: The following requirements are enforced for this assessment. - Length 2 of paper: N/A - Font Style: - Number of files: 3, 1 PDF, 2 SQL Scripts - Body Size: 11pt - File Name: See "O3 - Student Submissions - Font Style: Calibri Naming Convention.pdf" - Line Spacing: - Audience and Tone: - 1.08pt (Microsoft Default) - N/A - Mareins: - Citation Style: - Narrow setting (0.5) - N/A - Document Format: - Title Page: (10\% penalty if you do not include) - Header for each page including; Date, - College Name, Program Code, Course Code, Course code, Student name Course Section, Assignment Title, all - Dropbox submission through eConestoga participating group member Names, Date - PDF SQL specific formatting requirements: - All SQL reserved words must be capitalized, bolded, and have a distinct colour - Entities (Tables) and Attributes (Fieldnames) shall be black in text - Text words, and numeric values used for titles, filters, and comparators shall have a distinct colour - You will not be required to list the schema in front of the tables for this assessment as we have not setup schemas in the database yet