Introduction In This Assignment You Will Apply Critical Thinking And Technical Skill 2859576

Introduction In this assignment you will apply critical thinking and technical skills to solve authentic business problems. It is important that you: • Conceptualise problems or situations; • Apply technical skills to authentic situations; • Solve the complex problems; Submission Submit all answers in a single MS-WORD file. Use headings to clearly separate each task. Part 1 – Sales, Purchase and Stock Database System (30 marks) You are required to create a stock control system with a small number of common sense attributes. The system should contain the following information • Inventory details • Customer details • Sales order details • Supplier Details • Purchase order details So consider what stock control is needed when a customer raises a sales order or an inventory manager within the organisation raises a purchase order. You need to identify the tables you require and the information that they are to contain. It is recommended that sale and purchase orders have header and line details tables. This is only a small system, do not over-complicate it. 1. Design – Marks 10 Create a entity-relationship model drawn with MS WORD or POWER POINT if you prefer. You are required to submit a print out of your database schema: • Set of normalised tables – with your sample data. • Indicate the table relationships using multiple keys; PK, FK, and SK using UML notation. 2. SQL – Marks 10 Use MYSQL to create the set of database tables of the relational database model and complete the associated queries given. Procedure: 1) Write all the SQL statements, necessary to create all tables and relationships, with Primary & Foreign keys. 2) Execute each statement in the correct order so as to create the relational database in MYSQL 3) Insert some data into each table 4) Use all your SQL create and Insert statements (from MS Word) to execute in the MYSQL WorkBench 5) Write in MS Word and execute in MYSQL WorkBench the statements necessary to; i. display all tables, ii. identify stock levels for items iii. identify customer total expenditure