Database Solution for Polly Pipe

Course - BTEC HND in Computing

Unit 04 Database Design & Development in the BTEC HND in Computing program delves into the fundamental principles and practical aspects of creating and managing databases. Students explore the entire lifecycle of database development, from conceptualization and design to implementation and maintenance. Key topics include database modeling, normalization techniques, SQL programming, database security, and optimization strategies. Through a combination of theoretical study and hands-on exercises, learners gain proficiency in designing efficient and scalable databases that meet the needs of modern organizations. This unit equips students with essential skills to pursue careers in database administration, software development, and related fields within the computing industry.

Table of Contents

• Activity 1.1
• User Requirements
• System Requirements
• Entity Relationship Model
• Converting from ER model to logical database design
• Normalizing the entities
• Final Normalized Database
• Activity 1.2
• UI Design
• Effectiveness of the database design

Assignment - Database Solution for Polly Pipe

Database Mastery Made Easy: Ace Your Unit 04 Database Design & Development - BTEC HND in Computing Assignment with Professional Assistance!

Learning Outcome 1: Use an appropriate design tool to design a relational database system for a substantial problem.

Question 1. Identify the user and system requirements to design a database for the above scenarioand design a relational database system using conceptual design (ER Model) by including identifiers (primary Key) of entities and cardinalities, participations of relationships. Convert the ER Model into logical database design using relational database model including primary keys foreign keys and referential Integrities

Solution:

User Requirements
As the user of the database, Polly Pipe needs the following requirements that need to be implemented in the database:

1. The user of the database wants to add, modify and delete the staff details which contains Staff name, Staff type.

2. The user of the database wants to add, modify and delete the equipment details such as equipment type and equipment information.

3. The user of the database wants to add, modify and delete the facility installation details such as installation type, customer address where the installation needs to be done, customer name, set of equipment for installation, type of staffs work in the installation and period of time of installation.

4. The user needs secure, reliable, concurrent and efficient database access.

System Requirements

Entity Relationship Model

Set of rules given in the scenario are:
1. Customer may request as many as facility installations.
2. More than one employee can work in the facility installation.
3. Many equipmentis used for facility installation.
4. Many computers are installed in the facility.

Based on the details given in the scenario, entities found for the database design are:
1. Staff
2. Equipment
3. Customer
4. Computer
5. Facility

There is a one-to-many relationship between the facility and staff entities. There is a 1 to many relationships between the customer and facility entities. There is a one-to-many relationship between the facility and equipment entities. There is a one-to-many relationship between facility and computer entities(javatpoint, 2022).

Converting from ER model to logical database design

Staff (StaffNum, StaffName, StaffType)
Customer (CustomerNum, Name, Address)
Equipment (EquipmentNum, Type, Equipment)
Computer (ComputerNum, RamSize, OS)
Installation (InstallationID, Type, Period, CustomerNum, CustomerName, CustomerAddress, EquipmentNum, Equipment,StaffsRequired, CustomerNum References Customer (CustomerNum), EquipmentNum References Equipment (EquipmentNum))

Streamline Database Development: Reach New Heights in BTEC HND Computing's Unit 04 Database Design & Development!

Normalizing the entities

1. First Normal Form

An entity is said to be in first normal form when there are no multivalued attributes and should contain only single valued attributes.

In the given scenario, the equipment entity has multivalued attributes, it can be converted as follows:

Also Read: Crafting an impeccable research report assignment requires a blend of skill and strategy. Dive into these stellar tips for "How To Write A Perfect Research Report Assignment"!

2. Second Normal Form

An entity is said to be in second normal form when the entity is in first normal form and there is no partial dependency. Partial dependency occurs when a non-prime attribute functionally dependent on the part of the primary key. In the logical database design, partial dependency occurs in the installation entity based on the following:

CustomerName, CustomerAddress → CustomerNum
Equipment → EquipmentNum
This can be eliminated by dividing the entity into number of entities. The Installation entity can be divided into Installation, EquipmentInstallation and StaffInstallation.
WorkType(WorkID, WorkName)
Staff(StaffID, Staffname, WorkID)
Installation (InstallationID, Type, CustomerNum, Period)
EquipmentInstallation (InstallationID, EquipmentNum)
StaffInstallation(InstallationID, WorkID)

3. Third Normal Form

An entity is said to be in third normal form when it is in second normal form and there is a transitive dependency. The transitive dependency occurs when attribute A1 functionally dependent on attribute A2 and attribute A2 is functionally dependent on attribute A3, then attribute A1 is functionally dependent on attribute A3. There is no transitive dependency in any of the entities in the above database design(w3schools, 2022).

Final Normalized Database
Customer (CustomerNum, Name, Address)
Equipment (EquipmentNum, Type, Equipment)
Computer (ComputerNum, RamSize, OS)
WorkType (WorkID, WorkName)
Staff (StaffID, Staffname, WorkID)
Installation (InstallationID, Type, CustomerNum, Period)
EquipmentInstallation (InstallationID, EquipmentNum)
StaffInstallation (InstallationID, WorkID)

FAQ for Database Design

1. What is the scenario?
2. What are user and system requirements?
3. What is an ER Model?
4. How do you convert an ER Model to a logical design?
5. What are relational database models?
6. What is normalization?
7. How do you check for and remove anomalies?

Question: Design set of simple interfaces to input and output for the above scenario using Wireframe or any interface-designing tool.Evaluate the effectiveness of the given design (ERD and Logical design) in terms of the identified user and system requirements .

Solution: UI Design

Effectiveness of the database design

The purpose of the organization is very much satisfied using this database design.All the business rules given in the scenario is implemented in the database design. Based on the data records given in the scenario, entities of the database are designed. Based on the details in the data record, the attributes of the entities are designed. The primary keys are set for the entities so that each record can be uniquely identified. The relationship between the entities is also established in order have a consistent database. Anomalies, redundancies and inconsistencies are removed using the normalization process.

FAQ - Interface Design and Evaluation

1. What are the goals of designing interfaces for a database system?
2. What is wireframing, and how does it help in interface design?
3. What are some common tools for wireframing and interface design?
4. How do you effectively evaluate interface design in relation to user and system requirements?

Database Design Solution Delivered: Excel in Unit 04 Database Design & Development of BTEC HND Computing with Our Help!

Looking for HND Assignment Help Service in the UK? Our team of experienced writers and assignment experts is here to help!

Whether it's essays, case studies, reports, or proposals, we provide well-researched papers at affordable prices. With expertise in various types of writing, we ensure accurate content on topics related to marketing planning, implementation, and evaluation. Expect non-plagiarized samples in digital marketing assignment writing, giving you the opportunity to achieve top grades.