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

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. There is an example in appendix C$.$

Appendix A

EuroParts.com is an established company in UK$.$ 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 task. 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, quantity$)$

Invoice $($invoice$\_$number, idate, vat$\_$total, total$\_$value, supplier$\_$number$)$

InvoiceDetail $($invoice$\_$number, part$\_$number$\_$with$\_$prefix, quantity, net$\_$price, net$\_$vat$)$

Use part$\_$number$\_$with$\_$prefix as the PK for the part table.

Appendix B: $[$image$]$