Current Database Structure

My starting point was a single table containing equipment rental information. The table

tracked equipment details, manufacturer information, project assignments, and rental

transactions. The main issues I identified were:

o Multiple entries for the same equipment (like the RED Epic Camera appearing

three times)

o Repeated manufacturer information

o Redundant project and staff details These redundancies made the database

inefficient and prone to update anomalies.

Task 1 - Dependency Diagram

Let’s start by analysing the dependencies in our EQUIPMENT table.

1. Functional Dependencies

o EquipmentID uniquely determines other equipment attributes like

EquipmentName, EquipmentType, PurchaseDate, ManufacturerID,

WarrantyPeriod, and DailyRentalRate. This means that each EquipmentID is a

unique identifier for a specific set of equipment details.

o Similarly, ManufacturerID determines the ManufacturerName, as each

manufacturer has a unique name associated with its ID.

2. Transitive Dependencies

o We also have a transitive dependency here:

ManufacturerID → ManufacturerName. Since ManufacturerID does not depend

directly on EquipmentID, I consider it a transitive dependency.

Identifying these dependencies is crucial, as it informs us how to normalize the table to

remove redundancy and achieve database efficiency.”

Our EQUIPMENT table is already in First Normal Form (1NF) because:

 Each field contains atomic values, meaning every attribute holds a single value.

 There are no repeating groups or arrays within the table.

To ensure First Normal Form, each cell should have only one value, and every record should

be unique. The table meets these requirements, so we don’t need to make any changes to

achieve 1NF.”

Task 3 - Second Normal Form (2NF)

Moving to 2NF, I identified partial dependencies where several attributes depended only on

parts of our composite key. I split the table into three main entities:

 EQUIPMENT table for equipment-specific information

 PROJECT table for project details

 RENTAL_TRANSACTION table for rental events

This separation eliminated redundancy in equipment and project information while

maintaining all necessary relationships through proper keys. As a result, our tables are now in

Second Normal Form, with no partial dependencies remaining.

Task 4 - Third Normal Form (3NF)

To achieve Third Normal Form (3NF), we must remove transitive dependencies. A table is

in 3NF if it’s in 2NF and has no transitive dependencies, meaning that non-key attributes

should not depend on other non-key attributes.

Here’s what we need to address:

identified transitive dependencies:

 Manufacturer information depended on ManufacturerID, not directly on

EquipmentID.

 Similarly, Staff details depended on StaffID, not directly on ProjectID.

Solution

 To remove these transitive dependencies, I created two additional tables:

o MANUFACTURER table to store manufacturer information, and

o STAFF table for staff details.

 With these tables added, each attribute now depends solely on the primary key of its

respective table.

By resolving these transitive dependencies, the structure is fully normalized to 3NF, creating

a cleaner, more efficient design.

Task 5 - ERD of Third Normal Form Tables

The final Entity-Relationship Diagram (ERD) illustrates the relationships between five

main entities, each designed to maintain data integrity and eliminate redundancy:

 MANUFACTURER supplies EQUIPMENT in a one-to-many relationship.

 STAFF manages PROJECTS in a one-to-many relationship.

 EQUIPMENT is involved in RENTAL_TRANSACTIONS, also a one-to-many

relationship.

 PROJECTS have RENTAL_TRANSACTIONS in a one-to-many relationship as well.

and logical separation of information. With this design, we maintain a clean, normalized

database that supports accurate and organized data management.

Task 6 – Data Types and Sample Data

The tables I chose are the Equipment Table and the Rental Transactions Table, as both

play critical roles in our equipment rental system.

In the EQUIPMENT table, we have the following key attributes:

 EquipmentID (CHAR(4)): This is a fixed-length identifier, beginning with ‘E’

followed by three digits, like ‘E001’. We use CHAR for efficiency since the length is

always the same.

 EquipmentName (VARCHAR(50)): This field holds equipment names, with

sufficient space for various equipment descriptions, but a limited length to optimize

storage.

 EquipmentType (VARCHAR(20)): This field categorizes equipment types, such as

‘Camera’ or ‘Lighting.’ A length of 20 characters allows flexibility but prevents

excessive storage use.

 PurchaseDate (DATE): Stores only the date of purchase, without the time

component, which is not needed here.

 ManufacturerID (CHAR(4)): This foreign key links to the MANUFACTURER

table, using a fixed length for efficiency.

 WarrantyPeriod (VARCHAR(10)): This field allows warranty details, like ‘2 years’

or ‘1 year,’ giving flexibility for various warranty terms.

precise to two decimal places, suitable for monetary values.

This design ensures that we capture all necessary details of each equipment piece in a

compact and efficient format.

In our RENTAL_TRANSACTION table, we have these essential attributes:

 EquipmentID (CHAR(4)): A foreign key matching the EquipmentID in the

EQUIPMENT table, ensuring consistency.

 ProjectID (CHAR(4)): A fixed-length identifier beginning with ‘P’ for projects,

ensuring project details remain distinct.

 StartDate, EndDate, ReturnDate, ReturnedDate (DATE): These four date fields

track the rental start, end, actual return, and final processing dates, respectively.

 TotalRentalCost (DECIMAL(10,2)): This field stores the total rental cost, allowing

for larger monetary values up to 99 million, with two decimal places for accuracy.

This structure allows us to track rental transactions in detail, capturing essential dates and

costs for each equipment rental.

Conclusion

To conclude, my normalization process has:

 Eliminated data redundancy

 Improved data integrity

 Created a maintainable and scalable structure

 Established clear relationships between entities

preventing update and deletion anomalies.

Are there any questions about our normalization process or final design?"