Additives Business Process
Suppliers
A4U keeps an updated record of all the companies it works with. There are suppliers, that provide the raw materials and pre-synthesized substances and customers, for all we know their VAT number, name of company, web address, address, name, email and phone of contact person and the names of the board of directors. For each substance, it can be purchased from a supplier in batches and we know the batch number and the quantity purchased (always in kilograms), the price per kilogram, the discount made and the total sales price. One purchase from a supplier may include several products. Also for each purchase there is an invoice and a receipt issued by the supplier. For invoices and receipts we know the total amount payed, the issue date, and an internal number that is reset for each year (meaning that that there might be invoice 1 for 2016, and invoice 1 for 2017 for each supplier supplier)Sales, customers, and products
A4U sells only additives to other companies. For each sale we know for each additive sold, the batch number and the supplier of each substance that is present in each additive even if it may come from several suppliers. The company also maintains a list of products from each customer company in which the additives sold are used and the respective quantities per gram of product. This list can be updated periodically as the composition of each product may change with time. For each product of each customer we know its commercial name and its class (e.g. cookie, snack, frozen food, …). Also for each sale we may have receipts and invoices, noting that invoices and receipts are issued by A4U and the same constraints as above apply (invoices and receipts identified by number and year).Stocks and Accounting
This company maintains an accurate stock, and for each product (additive or substance) we know the available amount (in kg) at each point in time. For substances it is also known the respective batch and supplier. Each additive is stored in one of 4 storage facilities, but there is only one large storehouse for storing substances.With the provided SQL model with the solution for the first part, make the full model of the extended problem and write a report that should contain:
- The E/R model of the Universe of Discourse presented above and the integrity constraints identified
- The relational model
- The SQL/DDL instructions for creating the tables. Please note that SQL and other code should always be presented in a Fixed Size Type font like Courier New.
- Should add sample data to the constructed database to verify the schema
Comments
Post a Comment