Global Unified Technology Sales (GUTS) is moving toward a “bring your own device” (BYOD) model for employee computing. Employees can use traditional desktop computers in their offices. They can also use a variety of personal mobile computing devices such as tablets, smartphones, and laptops. The new computing model introduces some security risks that GUTS is attempting to address. The company wants to ensure that any devices connecting to their servers are properly registered and approved by the Information Technology department. The business requirements are attached.

Given the attached business requirements, create the complete Crow’s Foot ERD containing all primary keys, foreign keys, and main attributes. Use specialization hierarchy if appropriate. This ERD is the initial database blueprint. Use the normalization procedures to remove any possible data redundancies. This process may produce additional entities. If additional entities are created, revise the ERD created in previous step. Use the normalization procedures to further audit the revised ERD.

Create database table structures for your proposed entities in revised ERD. Identify in which normal forms your tables are. If a table is not in 3NF, convert it to 3NF or BCNF or 4NF as appropriate

(Note: SQL statements do not have to be written to create tables or populate the data... Just need to show the table structures as a relation in a word document.

For example for table Vendor, you can show it as
Vendor(Vendor_Code, Vendor_Name, Vendor_Address, Vendor_Phone). Vendor_Code is the primary key. There is no foreign key for this table, but if there is one, identify it. Vendor is in 3NF and no modification is needed at this point. )
Transcribed Image Text:Business Requirements: Every employee works for a department that has a department code, name, mail box number, and phone number. The smallest department currently has 5 employees, and the largest department has 40 employees. This system will only track in which department an employee is currently employed. Very rarely, a new department can be created within the company. At such times, the department may exist temporarily without any employees. For every employee, their employee number and name (first, last, and middle initial) are recorded in the system. It is also necessary to keep each employee's title. An employee can have many devices registered in the system. Each device is assigned an identification number when it is registered. Most employees have at least one device, but newly hired employees might not have any devices registered initially. For each device, the brand and model need to be recorded. Only devices that are registered to an employee will be in the system. While unlikely, it is possible that a device could transfer from one employee to another. However, if that happens, only the employee who currently owns the device is tracked in the system. When a device is registered in the system, the date of that registration needs to be recorded. • Devices can be either desktop systems that reside in a company office or mobile devices. Desktop devices are typically provided by the company and are intended to be a permanent part of the company network. As such, 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 the system so that if the device becomes compromised, the IT department can dispatch someone to remediate the problem. For mobile devices, it is important to also 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 has encryption enabled for data. The system should support storing information on whether or not each mobile device has these capabilities enabled. • Once a device is registered in the system, and the appropriate capabilities are enabled if it is a mobile device, the device may be approved for connections to one or more servers. 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 any server. GUTS has a number of servers, and a device must be approved for each server individually. Therefore, it is possible for a single device to be approved for several servers but not for all servers. • Each server has a name, brand, and IP address. Within the IT department's facilities are a number of climate-controlled server rooms where the physical servers can be located. Which room each server is in should also be recorded. Further, it is necessary to track which operating system is being used on each server. Some servers are virtual servers and some are physical servers. If a
Transcribed Image Text:server is a virtual server, then the system should track which physical server it is running on. A single physical server can host many virtual servers, but each virtual server is hosted on only one physical server. Only physical servers can host a virtual server. In other words, one virtual server cannot host another virtual server. Not all physical servers host a virtual server. • A server will normally have many devices that are approved to access the server, but it is possible for new servers to be created that do not yet have any approved devices. When a device is approved for connection to a server, the date of that approval should be recorded. It is also possible for a device that was approved for a server to lose its approval. If that happens, the date that the approval was removed should be recorded. If a device loses its approval, it may regain that approval at a later date if whatever circumstance that lead to the removal is resolved. A server can provide many user services, such as email, chat, homework managers, and others. Each service on a server has a unique identification number and name. The date that GUTS began offering that service should be recorded. Each service runs on only one server although new servers might not offer any services initially. Client-side services are not tracked in this system so every service must be associated with a server. Employees must get permission to access a service before they can use it. Most employees have permissions to use a wide array of services, but new employees might not have permission on any service. 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. The first time an employee is approved to access a service, the employee must create a username and password. This will be the same username and password that the employee will use for every service for which the employee is eventually approved.

Question

Global Unified Technology Sales (GUTS) is moving toward a “bring your own device” (BYOD) model for employee computing. Employees can use traditional desktop computers in their offices. They can also use a variety of personal mobile computing devices such as tablets, smartphones, and laptops. The new computing model introduces some security risks that GUTS is attempting to address. The company wants to ensure that any devices connecting to their servers are properly registered and approved by the Information Technology department. The business requirements are attached.

Given the attached business requirements, create the complete Crow’s Foot ERD containing all primary keys, foreign keys, and main attributes. Use specialization hierarchy if appropriate. This ERD is the initial database blueprint. Use the normalization procedures to remove any possible data redundancies. This process may produce additional entities. If additional entities are created, revise the ERD created in previous step. Use the normalization procedures to further audit the revised ERD.
Create database table structures for your proposed entities in revised ERD. Identify in which normal forms your tables are. If a table is not in 3NF, convert it to 3NF or BCNF or 4NF as appropriate

(Note: SQL statements do not have to be written to create tables or populate the data... Just need to show the table structures as a relation in a word document.

For example for table Vendor, you can show it as
Vendor(Vendor_Code, Vendor_Name, Vendor_Address, Vendor_Phone). Vendor_Code is the primary key. There is no foreign key for this table, but if there is one, identify it. Vendor is in 3NF and no modification is needed at this point. )

Transcribed Image Text:Business Requirements: Every employee works for a department that has a department code, name, mail box number, and phone number. The smallest department currently has 5 employees, and the largest department has 40 employees. This system will only track in which department an employee is currently employed. Very rarely, a new department can be created within the company. At such times, the department may exist temporarily without any employees. For every employee, their employee number and name (first, last, and middle initial) are recorded in the system. It is also necessary to keep each employee's title. An employee can have many devices registered in the system. Each device is assigned an identification number when it is registered. Most employees have at least one device, but newly hired employees might not have any devices registered initially. For each device, the brand and model need to be recorded. Only devices that are registered to an employee will be in the system. While unlikely, it is possible that a device could transfer from one employee to another. However, if that happens, only the employee who currently owns the device is tracked in the system. When a device is registered in the system, the date of that registration needs to be recorded. • Devices can be either desktop systems that reside in a company office or mobile devices. Desktop devices are typically provided by the company and are intended to be a permanent part of the company network. As such, 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 the system so that if the device becomes compromised, the IT department can dispatch someone to remediate the problem. For mobile devices, it is important to also 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 has encryption enabled for data. The system should support storing information on whether or not each mobile device has these capabilities enabled. • Once a device is registered in the system, and the appropriate capabilities are enabled if it is a mobile device, the device may be approved for connections to one or more servers. 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 any server. GUTS has a number of servers, and a device must be approved for each server individually. Therefore, it is possible for a single device to be approved for several servers but not for all servers. • Each server has a name, brand, and IP address. Within the IT department's facilities are a number of climate-controlled server rooms where the physical servers can be located. Which room each server is in should also be recorded. Further, it is necessary to track which operating system is being used on each server. Some servers are virtual servers and some are physical servers. If a

server is a virtual server, then the system should track which physical server it
is running on. A single physical server can host many virtual servers, but each
virtual server is hosted on only one physical server. Only physical servers can
host a virtual server. In other words, one virtual server cannot host another
virtual server. Not all physical servers host a virtual server.
• A server will normally have many devices that are approved to access the
server, but it is possible for new servers to be created that do not yet have
any approved devices. When a device is approved for connection to a server,
the date of that approval should be recorded. It is also possible for a device
that was approved for a server to lose its approval. If that happens, the date
that the approval was removed should be recorded. If a device loses its
approval, it may regain that approval at a later date if whatever circumstance
that lead to the removal is resolved.
A server can provide many user services, such as email, chat, homework
managers, and others. Each service on a server has a unique identification
number and name. The date that GUTS began offering that service should be
recorded. Each service runs on only one server although new servers might
not offer any services initially. Client-side services are not tracked in this
system so every service must be associated with a server.
Employees must get permission to access a service before they can use it.
Most employees have permissions to use a wide array of services, but new
employees might not have permission on any service. 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. The first time an employee is
approved to access a service, the employee must create a username and
password. This will be the same username and password that the employee
will use for every service for which the employee is eventually approved.

Accepted Answer

An entity relationship diagram (ERD) also known as an entity relationship model, is a graphical…