Security and Precautions For the medium-sized business schema used in Assignments 4 and 5, write the SQL statements for the following: Create roles forthe Managers, HR Specialists, and Other Users....

Security and Precautions

• For the medium-sized business schema used in Assignments 4 and 5, write the SQL statements for the following:
  
  
  • Create roles forthe Managers, HR Specialists, and Other Users.
  
  
  • Grant privileges to the roles as follows:
    
    
    • All users have read access to all the tables except Employment.
    
    
    • Managers have read access to all the tables (including Employment), plus the ability to apply inserts, updates, and deletes to all tables
      except
      Division, Department, Work_Group, Pay_Grade, Employment, and Employee_Work_Group_Assignment
    
    
    • HR Specialists have read access to all tables (including Employment), plus the ability inserts, updates, and deletes to the Division, Department, Work_Group, Pay_Grade, Employment, and Employee_Work_Group_Assignment

      Hint: To simplify your task, assume you can grant roles to other roles

      
      
    
    
    
    
  
  
  
  


• 
  List the security precautions you would recommend for each of the following database applications:
  




• Company personnel database


• Hospital medical records database


• Bank checking account database


• Consumer credit database


• Automobile insurance claims database


• Medical insurance claims database


• Personal music library and playlist database

Assignment_4_Instructor_Solution_with_generalized_hierarchy Instructor's Solution to Assignment 5 Organization and Individual Collapsed into Party Notation: IEn,m: Non-unique Index AKn,m: Unique (Alternate Key) Index n = index sequence number m = column sequence number CONTACT_MECHANISM_USAGE PARTY_ID CONTACT_MECHANISM_ID INTEGER INTEGER NOT NULL NOT NULL (FK) (FK) (IE1.1) CONTACT_USAGE_TYPE_CODE VARCHAR(5) NULL (FK) CONTACT_USAGE_TYPE CONTACT_USAGE_TYPE_CODE VARCHAR(5) NOT NULL CONTACT_USAGE_TYPE_DESCRIPTION VARCHAR(100) NOT NULL CONTACT_MECHANISM CONTACT_MECHANISM_ID INTEGER NOT NULL CONTACT_MECHANISM_TYPE_CODE CHAR(2) NOT NULL DEPARTMENT DEPARTMENT_ID INTEGER NOT NULL DEPARTMENT_NAME DIVISION_ID DEPARTMENT_MANAGER_EMPLOYEE_ID VARCHAR(100) INTEGER INTEGER NOT NULL NOT NULL NULL (FK) (FK) (AK1.1) (IE1.1) DIVISION DIVISION_ID INTEGER NOT NULL DIVISION_NAME DIVISION_MANAGER_EMPLOYEE_ID VARCHAR(100) INTEGER NOT NULL NULL (FK) (AK1.1) E_MAIL CONTACT_MECHANISM_ID INTEGER NOT NULL (FK) E_MAIL_ADDRESS VARCHAR(255) NOT NULL EMPLOYEE_WORK_GROUP_ASSIGNMENT EMPLOYEE_ID WORK_GROUP_ID WORK_GROUP_ASSIGNMENT_EFFECTIVE_DATE INTEGER INTEGER DATE NOT NULL NOT NULL NOT NULL (FK) (FK) (IE1.1) WORK_GROUP_ASSIGNMENT_END_DATE DATE NULL EMPLOYMENT EMPLOYEE_ID INTEGER NOT NULL PAY_GRADE_CODE PARTY_ROLE_ASSIGNMENT_ID VARCHAR(20) INTEGER NOT NULL NOT NULL (FK) (FK) (AK1.1) ITEM ITEM_ID INTEGER NOT NULL ITEM_DESCRIPTION ITEM_LIST_PRICE VARCHAR(255) DECIMAL(7,2) NOT NULL NOT NULL PARTY PARTY_ID INTEGER NOT NULL PARTY_TYPE_CODE ORGANIZATION_NAME GIVEN_NAME MIDDLE_NAME FAMILY_NAME BIRTH_DATE CHAR(2) VARCHAR(100) VARCHAR(40) VARCHAR(40) VARCHAR(40) DATE NOT NULL NULL NULL NULL NULL NULL PARTY_ROLE_ASSIGNMENT PARTY_ROLE_ASSIGNMENT_ID INTEGER NOT NULL PARTY_ID PARTY_ROLE_TYPE_CODE ROLE_EFFECTIVE_DATE ROLE_TERMINATION_DATE INTEGER CHAR(3) CHAR(18) CHAR(18) NOT NULL NOT NULL NULL NULL (FK) (FK) (AK1.1) (AK1.3) (AK1.2) PARTY_ROLE_TYPE PARTY_ROLE_TYPE_CODE CHAR(3) NOT NULL PARTY_ROLE_TYPE_NAME VARCHAR(100) NOT NULL PAY_GRADE PAY_GRADE_CODE VARCHAR(20) NOT NULL PAY_GRADE_NAME VARCHAR(100) NOT NULL (AK1.1) POSTAL_ADDRESS CONTACT_MECHANISM_ID INTEGER NOT NULL (FK) ADDRESS_LINE_1_TEXT ADDRESS_LINE_2_TEXT CITY_NAME STATE_PROVINCE_CODE POSTAL_CODE COUNTRY_CODE VARCHAR(255) VARCHAR(255) VARCHAR(128) CHAR(2) VARCHAR(20) CHAR(2) NOT NULL NULL NOT NULL NOT NULL NOT NULL NULL PURCHASE_ORDER PURCHASE_ORDER_NUMBER INTEGER NOT NULL PARTY_ROLE_ASSIGNMENT_ID ORDER_DATE ORDER_STATUS_CODE INTEGER DATE CHAR(2) NULL NOT NULL NOT NULL (FK) (IE1.1) PURCHASE_ORDER_ITEM PURCHASE_ORDER_NUMBER ITEM_ID INTEGER INTEGER NOT NULL NOT NULL (FK) (FK) (IE1.1) ITEM_PURCHASE_UNIT_PRICE ITEM_PURCHASE_QUANTITY DECIMAL(7,2) INTEGER NOT NULL NOT NULL TELEPHONE CONTACT_MECHANISM_ID INTEGER NOT NULL (FK) AREA_CODE PHONE_NUMBER PHONE_EXTENSION_NUMBER DEVICE_TYPE_CODE CHAR(3) VARCHAR(20) VARCHAR(9) CHAR(2) NULL NOT NULL NULL NOT NULL (IE1.2) (IE1.1) WORK_GROUP WORK_GROUP_ID INTEGER NOT NULL WORK_GROUP_NAME EMPLOYEE_ID DEPARTMENT_ID VARCHAR(100) INTEGER INTEGER NOT NULL NULL NOT NULL (FK) (FK) (AK1.1) (IE2.1) (IE1.1)