Database Design Project For the final assignment of this course, you will analyz
Database Design Project
For the final assignment of this course, 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 downloadin 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. There is a Sample of an Executive Summary (Links
to an external site.)on the Writing Center website for your
reference.
Conceptual design (ERD creation)
Review the feedback provided for your Week Two ERD Creation interactive
assignment. Carefully review the ISM641 Database Design Project: Business Rules downloadand revise your Visio conceptual model (or
entity-relationship diagram) for your database. 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, 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
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 (https://docs.microsoft.com/en-us/sql/connect/jdbc/connection-url-sample?redirectedfrom=MSDN&view=sql-server-ver15 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
·
Must be 15 to 20
double-spaced pages in length (not including title and references pages) and
formatted according to APA style as outlined in the Writing Center (Links to an external
site.).
·
Must include a
separate title page with the following:
o Title of paper
o Student’s name
o Course name and number
o Instructor’s name
o Date submitted
·
Must document any
outside resources used in APA style
Connection URL sample https://docs.microsoft.com/en-us/sql/connect/jdbc/connection-url-sample?redirectedfrom=MSDN&view=sql-server-ver15)
