HeatingSupplies is a home services provider based in the United Kingdom. It is specialised in the on-going maintenance, regular servicing and repairing of boilers and central heating systems in houses all around the country. The initial services provided by HeatingSupplies include a preliminary inspection of the boiler and central heating system, the formulation of a quote for a maintenance contract and the suggestion of a maintenance package for this contract. If a maintenance contract is signed then HeatingSupplies guarantees the servicing of the boiler and central heating installation at regular intervals as well as unlimited cover for breakdowns.
Part B Questions
You have been hired by HeatingSupplies as a database architect to undertake a database project to support the data needs required by the business processes in the organisation. In this second part, you are given a conceptual data model for HeatingSupplies (figure 3) and your first goal is to map it onto a high-quality LOGICAL RELATIONAL DATA MODEL to logically represent how the key business data needs to be organised as a set of interrelated tables that can then be implemented. These relations need to be interconnected according to the strict rules of the relational model to be implementable. You also have to produce an executive report to justify your mapping. Subsequently, you have to create two of the tables in the MySQL RDBMS and create a simple PHP script to add data into it and retrieve data from it.
6) Produce a complete Logical Entity-Relationship Diagram for HeatingSupplies. This needs to include all the correct relations, relationships, multiplicity constraints, attributes, primary keys and foreign keys. This needs to fit on one page of the report.
7) Provide an executive summary of how you produced the HeatingSupplies logical ERD i.e. how you mapped the HeatingSupplies conceptual ERD into a full logical relational schema. In other words, you need to refer to the Logical Data Modelling Methodology and explain in a written narrative in the first person what you need to do to resolve each relationship and derive the relevant relations (i.e. tables) with all their attributes, primary keys and foreign keys. This needs to fit on no more than two pages of the report.
8) Write some SQL code (DDL) to create the Customer table (individual customer) and the Property table in the MySQL RDBMS. The table names should be preceded with your initial e.g. FR_Customer and FR_Property. No field should be left empty. Both tables should have primary keys and Property should have one foreign key. Both the code and screenshots of the structure of the tables should be included. The screenshots should show your student id number (right above the structure of the table). This needs to fit on no more than two pages of the report.
9) Create a simple PHP file called [login to view URL] to display a Web-based form to allow an administrator to enter the details of new customers onto the Customer table in the MySQL RDBMS. Both the code and a screenshot of the Web page should be included. The screenshot should show your student id number (in the URL bar). This needs to fit on one page of the report. Ideally, your form should look like the one on figure 1.
10) Create a simple PHP file called [login to view URL] to display a Web-based confirmation page displaying the added customer. Your page should ideally show the last added customer and also a customer added previously. Both the code and a screenshot of the Web page should be included. The screenshot should show your student id number (in the URL bar). This needs to fit on no more than two pages of the report. Ideally, your form should look like the one on figure 2.