Download Database Design and Development for Polly Pipe: A Case Study and more Study Guides, Projects, Research Database Programming in PDF only on Docsity!
Designing and Developing a
Relational Database System for
Polly Pipe
Polly Pipe Database Design and Development
1.1 Introduction
Polly Pipe is a water sports provider and installer based in Braintree, England. The company currently maintains its data in a manual, paper-based system and wants to design and implement a computerized database system to meet its data requirements.
The key objectives of the new database system are to:
Control the company's data of maintainable assets through an asset register Easily manage customer requests for various installations, as each installation is tailored to a specific customer Manage the documentation and issues related to the various installations
1.1.1 Database Design Process
The database design process for the Polly Pipe system involves the following steps:
Requirement collection and analysis Conceptual design Logical design/data model mapping Physical design
1.1.2 User and System Requirements
The requirements for the Polly Pipe database system can be categorized as:
Functional Requirements:
Ability to enter customer data, including customer details, employee details, payments, installations, and equipment details Provide a creative user interface
Non-Functional Requirements:
Accessibility, audit, efficiency, security, accuracy, availability, backup and restore, capacity, certification, compliance, reliability, responsibility, virus guard, compatibility of software
System Requirements:
Hardware: 8GB RAM, HDD, SSD, 3-3.5 GHz processor, 100Mbps network interface, 200GB SSD for SQL Software: Windows 10, 250GB+ hard disk space, Intel i7 dual-core processor, printer, fax, scanner, restore and backup options, anti-virus software
1.1.3 Explanation of Polly Pipe's ER Diagram
The key entities and their attributes in the Polly Pipe ER diagram are:
Customer: cus_id, cus_name, cus_address, cus_contact_no Employee: emp_id, emp_name, emp_address, emp_contact_no, emp_type, salary Equipment: equi_id, equi_type, equi_name, equi_price, quantity Installation: inst_id, inst_type, start_job, end_job, address Payment: id, paym_type, paym_date
1.1.4 ER Diagram for Polly Pipe
The ER diagram for the Polly Pipe database system is shown in Figure 2.
1.1.5 Logical Schema
The logical schema for the Polly Pipe database system is shown in Figure 3.
1.1.6 Data Normalization
Data normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. The key steps in data normalization are:
First Normal Form (1NF):
Remove repeating groups by creating separate tables Identify data using a primary key
Second Normal Form (2NF):
Use separate tables for values that apply to multiple reports Connect tables using foreign keys
Data validation can be applied during:
Insert Update Delete
Interface of Polly Pipe
The Polly Pipe company system has the following interface forms:
Login Form
A simple interface design for the user to get the main idea of the system. It can be designed on a wireframe or any interface designing tool.
Main Form
The main interface of the Polly Pipe company system.
Employee Form
The interface for managing employee details.
Customer Form
The interface for managing customer details.
Installation Form
The interface for managing installation details.
Equipment Form
The interface for managing equipment details.
Payment Form
The interface for managing payment details.
SQL
SQL (Structured Query Language) is a computer language for storing, manipulating, and retrieving data stored on a relational database. A database is a table that includes rows and columns. SQL mainly targets the understanding and analysis of databases, including data tables.
DDL Statements
SQL has several sub-languages, including:
DDL (Data Definition Language) DML (Data Manipulation Language) DRL/DQL (Data Retrieval Language/Data Query Language) TCL (Transaction Query Language) DCL (Data Control Language) SCL (Session Control Language)
DDL statements can change commands automatically and are permanently saved in the database. DDL commands include:
CREATE: Used to create new tables DROP: Used to delete tables ALTER: Used to modify the structure of existing tables TRUNCATE: Used to delete all data from a table, while keeping the table structure intact
DML Statements
DML statements are used to modify databases, but they cannot execute automatic commands like DDL. Data can be reversed via DML. DML commands include:
INSERT: Used to add new data to a table UPDATE: Used to modify existing data in a table DELETE: Used to remove data from a table
Usage of DDL
The key here is the ability to create tables and manage created tables using data definition language. DDL can be used to create new tables, structure and record stored data in tables, delete structured data and stored data in tables, and add new attributes to the database.
Polly Pipe Company System
The Polly Pipe company system includes the following interfaces:
Login Form Employee Details Form Customer Details Form Equipment Details Form Installation Details Form Payment Details Form
Example: sql SELECT * FROM Customers WHERE CustomerID = 1; This query will retrieve all columns from the Customers table, but only for the row where the CustomerID is 1.
'Update' statement
The UPDATE statement is used to modify existing data in a table. It allows you to change the values of one or more columns in one or more rows.
Example: sql UPDATE Customers SET CustomerName = 'John Doe' WHERE CustomerID = 1; This query will update the CustomerName column to 'John Doe' for the row where the CustomerID is 1.
'Between' statement
The BETWEEN clause is used in a WHERE clause to select values within a given range. The values can be numbers, text, or dates.
Example: sql SELECT * FROM Orders WHERE OrderDate BETWEEN '2022-01-01' AND '2022-12-31'; This query will retrieve all rows from the Orders table where the OrderDate is between '2022-01-01' and '2022-12-31'.
'In' Statement
The IN clause is used in a WHERE clause to allow you to specify multiple values in a WHERE clause. It is a shorthand for multiple OR conditions.
Example: sql SELECT * FROM Customers WHERE CustomerCity IN ('London', 'Paris', 'New York'); This query will retrieve all rows from the Customers table where the CustomerCity is 'London', 'Paris', or 'New York'.
'Group by' statement
The GROUP BY clause is used in a SELECT statement to group rows that have the same values into summary rows, like when you want to calculate the total sales per customer.
Example: sql SELECT CustomerID, SUM(OrderAmount) AS TotalSales FROM Orders GROUP BY CustomerID; This query will retrieve the CustomerID and the total sales (sum of OrderAmount) for each customer, grouped by CustomerID.
'Order by' statement
The ORDER BY clause is used in a SELECT statement to sort the result set in ascending or descending order.
Example: sql SELECT * FROM Customers ORDER BY CustomerName ASC; This query will retrieve all rows from the Customers table, sorted in ascending order by CustomerName.
SQL Statements and Database Usage
SQL Statements
The following SQL statements are discussed in the given text:
Select : Used to extract data from a database. Where : Used to filter records based on a specified condition. Update : Used to modify existing data in a table. Between : Used to filter records based on a range of values. In : Used to filter records where the value is in a list of values. Group By : Used to group rows that have the same values into summary rows. Order By : Used to sort the result-set in ascending or descending order. Having : Used to filter groups based on a specified condition.
Database Usage
The text mentions that the usage of the above SQL statements should be assessed with examples from the developed database to prove that the data extracted through them are meaningful and relevant to the given scenario.
Examples and Evidence
The text does not provide any specific examples or evidence from the developed database. However, it is expected that the learner will provide relevant examples and evidence to demonstrate the meaningful and relevant data extracted using the SQL statements.
Test Plan and Test Cases
Test Plan
The text requires the learner to provide a suitable test plan to test the system against user and system requirements. The test plan should include relevant test cases for the database that has been implemented.
Test Data and Effectiveness
The text also mentions that the learner needs to assess how the selected test data can be used to improve the effectiveness of testing. The expected results should be provided in a tabular format, and screenshots of the actual results should be included with a conclusion.
