You Will Analyze User Needs To Facilitate Development ✓ Solved

You Will Analyze User Needs To Facilitate The Development Of A Databas

You will analyze user needs to facilitate the development of a database using SQL Server to track the software and hardware assigned to the employees of a given organization. The purpose of this database project is to facilitate the development of data models that can be translated into relational databases. Organizations are legally required to track software and ensure that only legally procured software licenses are deployed. As a database architect, your task is to develop a database to track software and hardware for the ConstructCo Company. ConstructCo is a small construction company that employs 50 employees and owns over 80 desktop computers, laptops, and servers.

You will be using the SQL Server environment provided during this course. This database will ultimately be used as a basis for a software license management tool to assist the company in tracking how the licenses and workstations/PCs are deployed throughout the organization. You must use the ConstructCo Database Design Project template provided in the assignment. For each section of the template, include the content required as listed below.

Document History: The information in this section will be pre-populated in the template. The only required content will be your name in the “Revised By” column.

Executive Summary: Summarize the project parameters including a short description of each of the following sections: Conceptual Design, normalization phase, physical design, Data retrieval, and connecting to online databases.

Conceptual Design (ERD creation): Ensure that the following requirements are met: Identify all entities and attributes; identify all primary keys; define all relationships between entities.

Normalization: Once the ERD is completed for your ConstructCo database project, create the Logical Data Model and check the model against 3rd Normal Form (3NF). Review your ERD feedback from the Week Three assignment where you converted your ERD into a Logical Data Model in 3NF. Revise your Logical Data Model accordingly. Describe the three normal forms and justify how each of your tables meets 1st, 2nd, and 3rd Normal Form, utilizing a minimum of three professional sources for support.

Physical Design: In Week Four, create your database in SQL Server, translating your design into tables with appropriate fields. Select suitable data types for each field based on size and value range. Specify all primary keys as “identity” fields (not auto-increment). Populate the tables with sample data using INSERT statements.

Data Retrieval: Provide SELECT statements to fulfill the following data requests: list all available laptops assigned to managers; list all available named licenses; list workstations over three years old and assigned to leads, including lead names, workstation IDs, and locations; list names of all leads in the operations department; list all workstations with disk space less than the average; list managers with employees not assigned workstations. Include screenshots for each SQL statement and results, and describe your process.

Connecting Online Databases with Front-End Interfaces: Modify the provided Java code to retrieve a list of all workstations assigned to managers, including their first name, last name, and department, along with workstation ID and purchase date. Also, modify the code to insert new workstation records into the database, given the workstation’s details. Include the modified Java code as plain text below your description.

Sample Paper For Above instruction

Introduction

The development of a comprehensive database to track hardware and software assets within an organization is crucial for ensuring compliance with licensing regulations and optimizing resource management. For ConstructCo, a construction company with 50 employees and over 80 computing devices, creating an efficient database system facilitates license management, asset tracking, and operational oversight. This paper details the process of analyzing user needs, designing the database, normalizing data, implementing physical structures, executing data retrieval, and developing front-end connections using Java.

Conceptual Design

The initial step involved identifying key entities: Employee, Workstation, Software License, Hardware Asset, Department, and Role. Each entity was assigned unique attributes; for example, Employee included EmployeeID, FirstName, LastName, DepartmentID, and RoleID. Primary keys were defined for each entity (e.g., EmployeeID), and relationships were established using foreign keys—such as linking Employee to Department via DepartmentID. The Entity-Relationship Diagram (ERD) visualized these relationships, displaying one-to-many connections between departments and employees and between employees and assets.

Normalization

Following ERD creation, the Logical Data Model was refined to satisfy the three normal forms. First Normal Form (1NF) required that each table contain atomic values with no repeating groups; for instance, each hardware asset record stores individual specifications separately. Second Normal Form (2NF) mandates that all non-key attributes depend on the entire primary key; for example, software licenses are linked fully to their specific product codes. Third Normal Form (3NF) eliminates transitive dependencies, such as separating department details into a dedicated table. Each table was reviewed and justified against the normal form criteria, supported by scholarly sources such as Elmasri & Navathe (2015) and Ramakrishnan & Gehrke (2003).

Physical Design

The transition to physical design involved translating the logical schema into SQL Server tables, selecting appropriate data types like VARCHAR for names, INT for IDs, DATE for purchase dates, and DECIMAL for disk space and CPU speed. Primary keys were set as identity fields to automatically generate unique identifiers. Sample data was populated using INSERT statements, demonstrating typical asset and employee records, which facilitate testing and validation.

Data Retrieval

Multiple SELECT statements were constructed to meet specific organizational data requests. For example, to list all laptops assigned to managers, a query joining Employee, Workstation, and Role tables was used, filtering for device type and managerial roles. To find workstations over three years old, the query calculated the difference between current date and purchase date. Screenshots of SQL scripts and their execution results illustrate each step, with explanations highlighting the logic and SQL syntax applied.

Connecting Front-End Interfaces

Finally, the Java code was modified to connect these database queries with a user interface. The code was adapted to retrieve and display managers' workstations, showing their names and department details alongside device IDs and purchase dates. Additionally, methods were added to insert new workstation records, accepting user input for various attributes and executing SQL INSERT commands through JDBC. The modified code was included as plain text, demonstrating integration efforts and ensuring real-time data interaction.

Conclusion

Creating the database for ConstructCo required thoughtful analysis, sound relational design, normalization adherence, precise implementation, and effective front-end integration. This comprehensive approach ensures the organization remains compliant with licensing laws, improves asset management, and enhances operational efficiency. Future work includes expanding functionality for reporting and developing additional interface features for administrative use.

References

• Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
• Ramakrishnan, R., & Gehrke, J. (2003). Database Management Systems (3rd ed.). McGraw-Hill.