Part B Questions
You have been hired by HomeSpace 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 HomeSpace (figure 1) and your 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 relations that can then be implemented. These relations need to be interconnected according to the strict rules of the relational model so that they can then be translated into a set of operational database tables. You also have to produce an executive report to justify your decisions and write a number of SQL statements to create some of the relations and retrieve information from them.
Produce a complete Logical Entity-Relationship Diagram for HomeSpace. This needs to include all the correct relations, relationships, multiplicity constraints, attributes, primary keys and foreign keys. This needs to fit on one page.
Provide an executive summary of how you produced the HomeSpace logical ERD i.e. how you mapped the HomeSpace 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.
Produce the SQL code (DDL and DML statements) to complete the following tasks. Your DDL statements need to include all the required constraints. Your DML statements need to retrieve the required information. This needs to fit on no more than one page.
Create a table to store the details of all the suppliers.
Create a table to store the details of all the purchase orders placed with suppliers.
Retrieve the reference numbers and total values of all the purchase orders that were placed in 2014 for a total value of over £1200.00 with the supplier called “Lovely Home Supplies Ltd”.
1 side featuring improved conceptual ERD
4 sides featuring improved explanations for conceptual ERD
1 side featuring logical ERD
2 sides featuring explanations for components of logical ERD
1 side featuring SQL code