21 Oct 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 purp
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 Download ConstructCo Database Design Project templatein 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.
- 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 SampleLinks 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.
[removed],
1
The Need for Normalization
Christina Peacock
ISM 641 Database Design and Management
Professor Xu Ashton
10/09/23
2
The Need for Normalization
It is fundamental to guarantee that the conceptual model adheres to the principles of
normalization before translating the Entity-Relationship Diagram (ERD) into a physical database
using Structured Query Language (SQL). The normalization process is an essential step in database
design pointed toward arranging information to limit overt repetitiveness and keep up with data
integrity. In this context, I will explore the purpose of normalization and give portrayals of each of
the five normal forms (1NF, 2NF, 3NF, 4NF, and 5NF) while offering models for better
comprehension.
Purpose of the Normalization Process
Minimizing Data Redundancy
Normalization assumes a critical part in limiting data redundancy by organizing data
efficiently (Servant et al., 2018). Redundant data alludes to the pointless duplication of data within a
database. At the point when information is excess, it consumes extra room and builds the gamble of
irregularities. For example, in a denormalized database, where redundant data is common, on the off
chance that one occurrence of an information point is refreshed while others are not, it prompts data
inconsistencies.
Enhancing Data Integrity
Update Anomalies: In a denormalized database, where information is copied across numerous
records, refreshing one event of an information point might leave others conflicting. For instance, in
the event that the telephone number of a client is changed in one spot but not in another, it results in
an update anomaly.
Insertion Anomalies: In some cases, adding new data to a denormalized database can be
challenging, as it might require embedding information into different spots (Ouafiq, Saadane &
Chehri, 2022). This intricacy can prompt inclusion abnormalities, where data insertion becomes
error-prone.
3
Deletion Anomalies: Deletion anomalies occur when deleting data from a denormalized database
inadvertently removes related data that ought to be held. For example, if erasing a client's
organization likewise eliminates their contact data, it leads to a deletion anomaly.
Improving Query Performance
Reduced Data Volume: Normalization eliminates data redundancy and stores data in a smaller
structure. This decrease in information volume implies that less data must be scanned or transferred
during query execution, leading to faster query performance.
Optimized Indexing: Normalized databases often have distinct essential keys and records. These
lists are more modest and more proficient, making it easier for the database management system to
locate and retrieve specific data quickly.
Structured Joins: Normalization often involves breaking data into multiple related tables, which
empowers organized joins between tables (Kasica, Berret & Munzner, 2020) Effective joins are
fundamental for complex questions that include recovering information from different related
elements. Very much planned joins result in quicker query execution.
Simplified Query Logic: In normalized databases, the inquiry rationale will be more clear and
natural. This straightforwardness makes it simpler for engineers to write efficient queries, reducing
the chances of performance bottlenecks.
1NF – First Normal Form
Scenario Violation of 1NF Resolution (1NF)
Table:
EmployeePhoneNumbers
Attributes:
EmployeeID,
PhoneNumbers
Attributes: EmployeeID, PhoneNumber
Description: In this table, there
is a single attribute
To bring this table into 1NF, create a new row for
each phone number and associate them with a
unique identifier, in this case, EmployeeID.
Before 1NF (Example Row): EmployeeID PhoneNumbers
4
101 123-456-7890, 987-654-3210
101
101
2NF – Second Normal Form
Scenario Violation of 2NF Resolution (2NF)
Table:
OrderDetails
Attributes: OrderID, ProductID,
ProductName
Table: Products
Attributes: ProductID (Primary Key),
ProductName
Description: In this table, we have attributes To achieve 2NF, we create a new table
"OrderID," "ProductID," and called "Products" with "ProductID" as
"ProductName." A violation of 2NF the primary key. We then include
occurs because "ProductName"
depends
"ProductName" in the "Products" table
on "ProductID" but not "OrderID." with a reference to "ProductID" as a
foreign key. This separation of data
ensures that non-key attributes are fully
functionally dependent on the primary key,
adhering to the principles of the Second
Normal Form (2NF).
Before 2NF (Example
Rows): OrderID ProductID ProductName
101 501 Laptop
102 502 Smartphone
103 501 Laptop
104 503 Tablet
5
3NF – Third Normal Form
Scenario Violation of 3NF Resolution (3NF)
Table:
EmployeeDetails
Attributes: EmployeeID, Department,
Manager
Table: Departments
Attributes: Department (Primary Key),
Manager
Description: In this table, we have attributes To achieve 3NF, we create a new table
called
"EmployeeID," "Department," and "Departments" with "Department" as the
primary
"Manager." A violation of 3NF key. We then include "Manager" in the
occurs because "Manager" depends "Departments" table with a reference to
on "Department" but not "Department" as a foreign key. This
separation
"EmployeeID." of data resolves the transitive
dependency.
Before 3NF (Example Rows): EmployeeID Department
101 HR
102 IT
103 HR
104 IT
Normalization of the ERD
Table
Meets
1NF
Meets
2NF
Meets
3NF Issues & Resolution
Students Yes Yes Yes No issues found.
Courses Yes Yes Yes No issues found.
Grade_Report Yes Yes No (3NF
Issue)
"DepartmentName" transitive dependency.
Create a separate "Courses" table with
"DepartmentName."
6
Departments Yes Yes Yes No issues found.
In conclusion, while most of the tables in the ERD already meet 1NF, 2NF, and 3NF
criteria, the "Grade_Report" table requires a slight change to accomplish 3NF by separating
"DepartmentName" into a new "Courses" table. This adjustment guarantees that the ERD lines up
with the principles of database normalization, optimizing data organization and integrity.
7
References
Ouafiq, E. M., Saadane, R., & Chehri, A. (2022). Data management and integration of low power
consumption embedded devices IoT for transforming smart agriculture into actionable
knowledge. Agriculture, 12(3), 329.
Kasica, S., Berret, C., & Munzner, T. (2020). Table scraps: an actionable framework for multi-table
data wrangling from an artifact study of computational journalism. IEEE Transactions on
visualization and computer graphics, 27(2), 957-966.
Servant, N., Varoquaux, N., Heard, E., Barillot, E., & Vert, J. P. (2018). Effective normalization for
copy number variation in Hi-C data. Bmc Bioinformatics, 19(1), 1-16.
