Using the simplified partial given ERD $($appendix B$),$ analyse the requirements $($appendix A$)$ and then:

a$)$ Produce a script suitable for SQL Developer to create the tables in the ERD $($appendix B$).$ Remember to define a primary and foreign key$($s$)$ where necessary using constraints. Entity definitions are found in appendix C$.$You will require some numeric data types for certain attributes so that you can comply with the requirement for numeric functions in queries. Examine the data records supplied carefully to identify the numeric attributes. There is an example in appendix C$.$Populate the tables with the supplied $$SQL Scripts$.$

b$)$ Write SQL statements necessary to satisfy the following queries. Use sensible headings where appropriate.The queries will retrieve data from single and multiple tables, they will use textual and numeric functions as well as creating views.

Queries:

$1.$ Produce a $$telephone list$$ for all suppliers. Define a $$View$$ and include the supplier name and telephone number. Sort by supplier ascending. The final output should only be one column.

$2.$ Create a query to establish which invoices are over $200.$ Sort on TOTAL$\_$VALUE descending.

$3.$ Create a query to select a particular invoice of your choice. The user should be prompted for the criteria $$ i$.$e$.$ INVOICE$\_$NUMBER should be entered at run$-$time.

Select...From...Where INVOICE$\_$NUMBER $=100001$ is INCORRECT

$4.$ Produce a list of parts for both warehouses. Include BIN$\_$NUMBER and QUANTITY as well as other suitable attributes. This should be one query. Sort by WAREHOUSE$\_$ID$.$ Use correlation $($alias$)$ names.

$5.$ Produce a list of parts to find the value of stock by line $($Qty x Unit Price$)$ in the Haverfordwest warehouse. Order by PART$\_$NUMBER$\_$WITH$\_$PREFIX.

$6.$ Create a View from your query $5$ and then use it to calculate the total value of all stock currently held in the Haverfordwest warehouse. Sort on PART$\_$NUMBER then create another view to calulate the total stock value.

$7.$ Produce a list of parts that are out of stock in both warehouses.

$8.$ Produce a list of parts that are available from both warehouses. You will need two queries and a view.

$9.$ Create a query to find out which parts are cheaper to buy from Sweden, when converted from Swedish Krona to Sterling and rounded to $2$ decimal places. Use PART$\_$NUMBER$\_$WITH$\_$PREFIX, STERLING$\_$NET and your third calculated column. To convert krona to sterling use $13/100.$

$10.$ Produce a query to increase the cost of all parts by $5\%.$ Use PART$\_$NUMBER$\_$WITH$\_$PREFIX, STERLING$\_$NET and your new price including the $5\%.$ Order by the new price.

Appendix A

EuroParts.com is an established company in Wales. It deals with the import and export of car parts. The company$$s suppliers are based in the UK and Sweden.

EuroParts.com has a warehouse in Haverfordwest and another in Stansted and both hold a stock of parts. Steve is manager of the Haverfordwest branch and Peter manages the Stansted warehouse. Each warehouse has many storage bins comprised of shelves and each shelf will have many plastic part storage containers which are numbered. For simplicity only use the shelf numbers. Part prices are in two currencies, UK sterling, and Swedish krona. All part prices are net of Vat.

a$)$ Each warehouse would have a unique set of bins numbers i$.$e$.,$ bin number A$1$M located in Warehouse ID $$Steve Haverfordwest$$ would not be found in Warehouse ID $$Peter Essex$.$

b$)$ Some bin shelves $($location$)$ may be empty.

c$)$ There will be no requirement for additional warehouses.

d$)$ There may be new suppliers.

e$)$ There may be new parts.

f$)$ The same part may be kept in both warehouses.

g$)$ The entity $$Location$$ is the shelf location on a bin. A bin will have several shelves.

h$)$ The part number $$prefix$,$ the first two digits, identifies whether the part is for bodywork, engine, accessory etc.

i$)$ The discount code and discount percentage are the markup on the part $($e$.$g$.,$ Discount$\_$UK$\_$Code of $1$ has a Discount$\_$UK$\_$Percent of $25$ which means $25\%$ profit$).$ The net cost of parts is in both UK sterling and Swedish Krona.

j$)$ For simplicity the Invoice contains a minimum number of attributes.

There is a M:N relationship between supplier and part but this will not be investigated for the purposes of this system. It can be ignored. There would also be a relationship between supplier and warehouse but this is also outside the remit of this system.Appendix C

Entity Definitions$/$Attributes:

Part $($part number with prefix, part number, description, discount sw$.$ code,

discount sw percent, krona net, discount uk code, discount uk percent, sterling

net$)$

Supplier $($supplier number, supplier name, address, town, county, postcode,

contact number, email address, country$)$

Warehouse $($warehouse id$,$ address, town, county, postcode, contact number$)$

Bin $($bin number, warehouse id$)$

Location $($part number with prefix, bin number