Data Modeling

Above is the solution of the data modeling of the case study. 8 different entities have been identified. For each entity its primary key is highlighted in red color and foreign key is highlighted in blue color.

• Employee table will have all the attributes or details of an employee where Employee_ID is the primary key. Now in case of employees there are different categories of employees such as technician, bookkeepers, and other normal in-house employees. So I have used the concept of subtyping to distinguish them as Technician and Non-Technicians.
• Technicians will handle all the field work and respond to the fix the issues raised by the clients, so I have shown the relationship between the Technician and Service_Request table. Technician may work on zero or many issues and each service request is assigned to a single technician.
• Non-Technician category of employees is involved in different activities. They have the task of managing the inventory and other paper works. So connection is shown between Inventory and Non-Technician table. An employee in Non-Technician can maintain zero or multiple entries of various items in inventory and an inventory entry will be made only by a single employee.
• Service_Request table maintain records of the service requests raised by the clients. This table also has 2 distinct attributes namely username and password to give access only to the clients and technicians who have the authorization to access the records of service request table. The Client table has details of the client. The client will raise zero or multiple service requests depending upon the issues faced by them and each request will be raised by a single client.
• Client table is also connected to Equipment table. Equipment table maintains the record of the equipment’s. Client will have multiple equipment’s which they own and each equipment belongs only to a single client.
• Equipment table is connected with Service_Request table. For each equipment single service request will be raised and service request will be raised for single or multiple equipment’s.
• Equipment Type table has details of various types of equipment’s. Different equipment types will form a single equipment and an equipment will have zero or 1 equipment type.
• Software_Configuration table keeps record of network, SQL server, and router and so on. Each equipment will have multiple software installed in it and a particular software will be installed in an equipment only once at a given point.
• Hardware_Component is table will has record of various components used in an equipment. Equipment consists of multiple hardware components and a particular type of hardware component is installed in an equipment only once at a given point.
• Inventory will have multiple hardware components and each hardware component is recorded only once in inventory.

...