Reference no: EM133552293

Assignment: Database Design

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 in the creation of your 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.

1) Identify all entities and attributes
2) Identify all primary keys
3) Define all relationships between entities

Normalization

Once the ERD is completed for your ConstructCO database project, you will create the Logical Data Model and check the model against 3rd Normal Form (3NF). For this phase of the project review your existing ERD feedback from your Week Three assignment where you converted your ERD into a Logical Data Model in 3NF. Using information from your Week Three assignment feedback revise your Logical Data Model. 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 to support your statements.

Physical Design

You created your database in the SQL server you were provided within your lab environment and translated this design into SQL server tables with appropriate fields. Select the appropriate data type for every field. The specific data type you select will depend on the size and range of the values that will be stored in the field.

Specify all primary keys as "identity" fields, and not as auto-increment.

After creating the tables, use the "insert" command to populate the tables with sample data for all the tables. (This will be affirmed in your SQL server in the lab environment.)

Data Retrieval

Provide select statements to satisfy the following data requests.

1) List all available laptops assigned laptops to managers.

2) List all available named licenses.

3) List the workstations that are over three years old (from the date of purchase) and assigned to leads.

4) List the name of the leads, workstation IDs, and location.

5) List the names of all the leads in the operations department.

6) List all the workstations with an amount of disk space less that the average disk space available in all workstations.

7) List the names of all managers with employees who have not been assigned a workstation.

Include screen shots of the SQL statements used to perform each step as well as the result sets resulting from the execution of the SQL statements. Describe what you did to complete each step.

Connect Online Databases with Front-End Interfaces

In order to connect the database with the front-end interface you will modify the Java code provided on the Connection URL Sample website to retrieve the list of all workstations assigned to managers. The output must include the first name, last name, and department of the managers as well as the workstation ID and date of purchase.

Modify the Java Code to insert new workstation record in the workstations table if provided with the WorkstationID, type, location, purchase date, memory, disk space and CPU speed.