Assignment brief
Polly Pipe is a water sports provider and installer based in Braintree, England. They need you to design and implement a database that meets the data requirements. These necessities are defined in this scenario and below are samples of the paper records that the Polly Pipe preserves.
Polly Pipe is focused in placing aquariums at business customers. Customers can request several installations, but each installation is tailor-made for a specific customer. Facilities are classified by type. One or more employees are assigned to each facility. Because these facilities are often very large, they can include carpenters and masons as well as water installers. The facilities use equipment such as aquariums, air pumps and thermostats. There can be multiple computers in a facility.
Below are examples of paper records that Polly Pipe currently maintains.
Staff Management Record
Staff Number
|
Name
|
Type
|
SHA1
|
Dave Clark
|
Plumber
|
SHA8
|
John Smith
|
Installation Manager
|
SHA2
|
Freddy Davies
|
Aquatics installer
|
SHA11
|
McCloud
|
Aquatics installer
|
SHA23
|
Satpal Singh
|
Plumber
|
SHA66
|
Winstn Kodogo
|
Aquatics installer
|
SHA55
|
Alison Smith
|
Brick Layer
|
Equipment Type Table
Type
|
Equipment
|
Tanks
|
20 gallon tank, 50 gallon tank, 100 gallon tank, 200 gallon tank
|
Thermostats
|
Standard, Super
|
Air Pumps
|
Standard, Super
|
Filters
|
Air driven, Undergravel
|
Installation ID
|
Installation Type
|
Installation Name and Address
|
Customer
|
Equipment
|
Types of Staff Required
|
Period of Staff assignment
|
234
|
Freshwater Tropical
|
Oak House, 17 Wroxton Road, Hertfordshire, H5 667
|
Lee A. sun
|
2 air pumps
200 gallons fish tank
1 x standard thermostat
|
1 x Carpenter
1 x Aquatics installer
1 x Electrician
|
From 1st September 2012
|
654
|
Freshwater Cold
|
Bayliss House, Orange Street, Kent, K7 988
|
Sally Dench
|
2 air pumps
200 gallons fish tank
Large Gravel Bag
2 x standard thermostats
|
5 x Carpenters
1 x Installation Manager
1 x Aquatics installer
1 x Plumber
3 x Labourers
|
1st June 2005 – 1st June 2011
|
767
|
Marine
|
Eaglestone Castle, Eaglestone, Kent
|
Perry Vanderrune
|
2 x 200 gallons fish tanks
500 Wood panels
|
10 x Carpenters
2 x Installation Manager
1 x Aquatics installer
1 x Plumber
3 x Labourers
|
From 30th June 2012
|
943
|
Marine
|
23 Sackville Street, Wilts. W55
|
Eric Mackintosh
|
2 air pumps
200 gallons fish tank
1 x standard thermostat
|
No staff required
|
157
|
Freshwater Tropical
|
Humbertson Castle, Kent, K8
|
Perry Vanderrune
|
2 air pumps
400 gallons fish tank
3 x standard thermostat
|
1 x Aquatics installer
|
1st September 2005 – 1st September 2012
|
Instillation Management Form
Activity 1
1.1. Identify the user and system requirements to design a database for the above scenario and 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. It should contain at least five interrelated tables. Check whether the provided logical design is normalised. If not, normalize the database by removing the anomalies.
(
Note:-It is allowed to have your own assumptions and related attributes within the scope of the case study given
)
1.2.
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 .
Activity 2
Activity 2.1
a. Develop a relational database system according to the ER diagram you have created (Use SQL DDL statements). Provide evidence of the use of a suitable IDE to create a simple interface to insert, update and delete data in the database. Implement proper security mechanisms in the developed database.
Evaluate the database solution developed and its effectiveness with relevant to the user and system requirements identified, system security mechanisms (EX: -User groups, access permissions) and the maintenance of the database.
Activity 2.2
a. Explain the usage of DML with below mentioned queries by giving at least one single example per each case from the developed database. Assess the usage of the below SQL statements with the examples from the developed database to prove that the data extracted through them are meaningful and relevant to the given scenario.
Select/ Where / Update / Between / In / Group by / Order by / Having
Activity 3
Activity 3.1
Provide a suitable test plan to test the system against user and system requirements. provide relevant test cases for the database you have implemented. Assess how the selected test data can be used to improve the effectiveness of testing.
Note:- Learner needs to give expected results in a tabular format and screenshots of the actual results with the conclusion
Activity 3.2
Get independent feedback on your database solution from the non-technical users and some developers (use surveys, questioners, interviews or any other feedback collecting method) and make recommendations and suggestions for improvements in a separate conclusion/recommendations section.
Activity 4
Produce a technical documentation and a user guide for the developed database system. Suitable diagrams diagrams (Use case diagram, class diagram, flow charts, DFD level 0 and 1) should be included in the technical documentation to show data movement in the system.
Assess the developed database by suggesting future enhancements to ensure the effectiveness of the system.
|