Skip to main content

Additives 4Us - Universe of Discourse | database design & implementation

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

Get Project Solution by contacting us

via WhatsApp: +92-324-7042178
via email: codelogixstudio@gmail.com

Comments

Popular posts from this blog

The Zoo Management System - entity relationship diagram & MS Access Database

Zoo Management System - Project Details: You are the employee of a big, worldwide working Zoo Management Company. Your company is responsible for the Zoo management. Your boss thinks it would be a great idea to store all data for each Zoo in a brand new self-developed ZOO Management System. Up to now, the ZOO management company has maps of each ZOO available. Your boss knows that you took a course in introduction on an ERP system, so he asks you if you could help designing such a system. Each ZOO must have the same organizational structure, which should look like this: Each Zoo has a Zoo-Address. Each Zoo has many visitors (Visitor Ticket Process (VTP). Many Zoo-Attractions belong to a Zoo. Module 1: Entity Relationship Diagram Design a ER (entity-relationship) diagram for your ZOO Management System. Use the information provided below with the entities and its attributes. Put the entities in the correct relationship to each other (organizational structure). Module 2: DB Implem...

Human Physiology by Stuart Ira Fox [PDF] (12th edition) free download

Sudoku Game Project in Java with full source code including GUI task

Introduction The purpose of the project is to try in practice the use of recursion and object-oriented program design. Please make sure to read this entire note before starting your work. Pay close attention to the sections on deadlines, deliverables, and exam rules. The problem Your task in this part of the project is to write a solver for Sudoku puzzles. Sudoku puzzles are a kind of crossword puzzles with numbers where the following two conditions have to be met: In each row or column, the nine numbers have to be from the set [1,2,3,4,5,6,7,8,9] and they must all be different.  For each of the nine non-overlapping 3x3 blocks, the nine numbers have to be from the set [1,2,3,4,5,6,7,8,9] and they ust all be different. The following two figures show a Sudoku puzzle and its solution. The input The input of your program, the Sudoku puzzles, is given in text files. More specifically, they are represented as nine lines of nine characters separated by spaces. The characters can be of two...