Database design | CPT 310 Database Systems & Management | Ashford University

Please read carefully and only bid if you know SQL and have MySQL screenshots will be needed. 

Please base your answer on the diagram attached.
 

Physical Design
In Week Four, 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.

  • List all available laptops assigned laptops to managers.
  • List all available named licenses.
  • List the workstations that are over three years old (from the date of purchase) and assigned to leads.
  • List the name of the leads, workstation IDs, and location.
  • List the names of all the leads in the operations department.
  • List all the workstations with an amount of disk space less that the average disk space available in all workstations.
  • 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 (Links to an external site.) 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.
Include the modified code by copying and pasting the code as plain text at the bottom of this section.

The Database Design Project