´ú×öIS61x6 Individual CA´ú×öSQLÓïÑÔ

ÈÕÆÚ£º2024-04-11 05:48

IS61x6 Individual CA

Due 19th April 2024

Life Insurance Co. Li-BOYD System: Individual Project

Business and IT managers at Life Insurance Co. decided to introduce a ¡°bring your own device¡± (BYOD) model as part of their post-COVID ¡°Work from Home¡± policy. All employees will be expected to be in the office two days per week, however. Existing senior employees may use traditional desktop computers in their dedicated office space: However, general employees will hot-desk and can use the desktop workstations provided or personal laptops configured and secured. They can  also use personal tablets and smartphones in a limited way.

Business and IT management are aware of the cyber risks involved in this new IT policy. Thus, the IT department wants to ensure that any devices connecting to their network comply with IT cybersecurity policies and controls, hence all devices used for company business need to be registered and appropriate end-point protection such as identification, access control, and authorisation configured as well as anti-virus and anti-malware software installed and updated. A Mobile Device Management (MDM) application will installed for that purpose. All employees will use Microsoft 365 for office software applications and OneDrive to store work-related data. Corporate systems can only be accessed through a secure virtual private network application from Citrix called VirtualApp. No work-related data can be saved/stored locally on any BOYD unless it is encrypted.

The following are the full requirements and business rules.

1.    Each employee works for a department that has a department code, name, mailbox number, and phone number. The smallest department currently has 15 employees, and the largest department has 80 employees. Departments include Sales and Marketing, Compliance and Legal, Human Resources, Underwriting, Claims, Customer Services, Policy, Risk Management, Finance and Accounting, Product Development, Actuarial and IT.  This system will only track in which department an employee is currently employed. For every employee, an employee number and name (first, last, and middle initial), email etc. is recorded. It is also necessary to keep each employee¡¯s title e.g. Mr, Ms., Mrs. Dr. etc.

2.    New projects are created in the company to address specific business issues such as designing and implementing new products and services. Project teams will consist of members from one or more departments (e.g. Sales and Marketing, Compliance and Legal, Product Development, Actuarial and IT). Project name, description, start and end dates are recorded.

3.    Staff in the IT Department register all devices submitted for inclusion in the system, so the date of that registration needs to be recorded. IT devices can be either desktop systems that reside in a company office or mobile devices, such as laptops, smartphones and tablets. Desktop devices are typically provided by the company and are intended to be a permanent part of the company network. All new mobile devices are BOYDs.

4.    Most employees may have at least one device registered, but newly hired employees might not have any devices registered initially.

5.    For each device, the brand, model, value in €, and operating system and version will need to be recorded (E.g. Microsoft Windows, Apple OS, Chrome OS, Android, Linux etc.) Only devices that are registered to an employee will be allowed to log on to the network.

6.    An employee can have several devices registered in the system. Each device is assigned an identification number when it is registered and its Media Access Control (MAC) address, Windows operating system SID recorded or equivalent ID for Apple OS and Chrome OS.

7.    Once registered a BOYD will be scheduled for approval by an IT information security supervisor, who also records the appropriate department group policy by department name. The device will activated on Active Directory by a systems administrator, using the data provided in the system. Not all devices meet the requirements to be approved at first, so the device might be in the system for a period of time before it is approved to connect to the business domain. The approval date is registered by IT Information Security supervisor and the activation date is by the IT systems administrator.

8.    Once approved the user will For laptops,a corporate user account and login will be set up on the BOYD in addition to the employees¡¯ personal account logonon the device. Users will use the business account on their devices to loginto the Active Directory Domain, remotely from home or on-premises. Microsoft 365, OneDrive, VirtualApp, Business-specific applications, Citrix VPN software, and company anti-virus/anti-malware software will also be installed only on laptops at registration, as well as Mobile Device Management (MDM) software.

9.    Activation involves enabling appropriate logon and security capabilities if it is a mobile device. Laptops areactivated on the business Active Directory Domain and the Organisation Unit that represents their department, and any related groups, such as project groups.

10.  Departmental group policy objects are installed on user accounts on BYOD laptop devices to allocate access permissions to department-level system resources by role, group, seniority level or department. The system will record a policy update if a user becomes a member of a project group with special access permissions.

11.  Users in all departments have access to shared software services from HR, Payroll, Personal Development and Training and soon. These services are accessible through two-factor authentication via Active Directory, first using email and password and second number codes texted to users'corporate and personal mobilephones or sent through email. Hence user email and phone number data needs to be recorded. New employees might not have permission on any service. The system will record which services users can access.

12.  Employees must get permission to access special services before they can use them. Each service can support multiple approved employees as users, but new services might not have any approved users at first. The date on which the employee is approved to use a service is   tracked by the system.

13. Smartphones and Tablets will not have direct access to the corporate Active Directory domain on premises. However, off-premises, they will have access to the DMZ Domain on the other side of the corporate Firewall to web services such as Microsoft 365 including Outlook, Teams and OneDrive. Two-factor authentication per device will be provided to Office 365, OneDrive, and Teams only on these devices.

14. Access to the Wifi access points on the business premise will first be authenticated using the MAC address of each device. To ensure that lost or stolen devices cannot gain access two- factor authentication will be required. Note that access to all services requires separate two- factor authentication unless those services are accessed via Active Directory.

15.  Each desktop device is assigned a static IP address, and the MAC address for the computer hardware is kept in the system. A desktop device is kept in a static location (building name  and office number). This location should also be kept in an asset register in the system sothat, if the device becomes compromised, the IT department can dispatch someone to remediate the problem. Users loginto devices used for hot-desking using their domain user credentials.

16. All mobile devices will receive a temporary IP address each time they access the corporate network on premises.

17.  For mobile devices, it is important to capture the device¡¯s serial number, which operating system (OS) it is using, and the version of the OS. The IT department is also verifying that each mobile device has a screen lock enabled and SDD Bitlocker encryption enabled for data protection. The system should support storing information on whether or not each mobile device has these capabilities enabled.

You are to make and record all assumptions in creating your ERD model.

The following are the core deliverables to be uploaded to Canvas (40 marks or %).

1.    An entity-relationship model (ERM) in the Third Normal Form. The model should use the Crows Foot notation in the style. employed, with all primary and foreign keys, relationship labels, and cardinality clearly indicated. Data definitions for all attributes are required. These are also to be listed in a separate table by Entity Type and include standard data type definitions for each entity attribute. The ERM diagram will be in PNG or similar with a clear background. Data definitions, if not contained in the entities, will also be uploaded in a separate Word document. (15 marks)

2.    Explain in detail using examples from your model, why your model is in 1NF, 2NF and 3NF.   (5 marks)

3.    Build the Life Insurance Co. LI-BOYD Database. You will populate tables to provide examples of the data and how it might be queried.

The deliverables here will be:

a.    LI-BOYD script. file in .sql .

b.   The LI-BOYD EER. This is performed by Reverse Engineering the database once created.

c.    You need to populate your database with mock data. (Use ChatGPT to provide you with typical examples.) Provide MySQL statements answers that provide the following information to Life Insurance Co.

d.    Write SQL statements to answer the following questions for IT management:

i.    Provide a breakdown of the number of BYOD devices by type, make, model, and operating system.

ii.   What is the total cost of all BYODs in use and a cost breakdown of types and models.

iii.   What us the average cost each type of BOYD device?.

iv.   What are the average times between registration and approval, approval to activation and average time from registration to device?

v.   What type of device and operating system took longest in days to be activated?

vi.   What are the minimum and maximum times for service delivery to activation? (15 Marks)

4.    A short 5 minute video of your deliverables i.e. the MySQL script, the database schema, the EER model, and in particular the SQL statements illustrating a command of SQL. (5 Marks)