Management Information Systems Questions-PMU .

College of Business 2021-2022 Fall Semester Management Information Systems Access Project Due Date 01/12/2021 Ms. Darin El-Nakla Introduction A database is a collection of information that is related to a particular subject or purpose. Database software, such as Microsoft Access, facilitates these tasks without the necessity of writing special programs each time the nature of the data changes. Data additions, deletions, and modifications can be handled automatically and database fields can be modified or adjusted at any time, all of which add a high degree of flexibility to the application. As a member of the Microsoft Office suite, Access easily interacts with the other Office programs. Digital Firm Project is a mandatory project in order to complete the course of Database Design. It strongly links the theoretical and the practical parts of the course as well as to primarily put the course topics which have been covered throughout the lectures into realistic implementation level. The project concludes the core of information system utilization in the enterprise, as business function areas will be seen through a technological prospective and through the database usage. This project has been designed in order to improve your ability to evaluate real life business cases in terms of software solutions. This assessment is your guideline for the project, read it carefully and don’t hesitate to contact me in case of any query by the email provided above. Objectives This is a group project – no more than 3 students per group! This is not negotiable – no groups of 3 or 4! The group project is by far the most important single piece of work in this course. It provides you with the opportunity to analyse, examine and evaluate real business project, and to put into practice some of the techniques you have been taught throughout the course. Whatever is your level in understand-ability of this course so far, you can show your inspiration in this project. This project aims to: 1. To provide hands-on experiencing, designing and building a database. 2. To relate database applications to business functions. 3. To illustrate how far students are able to represent their skills in system development process. Project Submission Procedure policy The final date of submission would be on the Thursday December 1st, 2021. Students are strongly encouraged to submit their projects in electronic softcopy saved on CD included all databases. Strongly, please consider the following: 1. Submission is due to WED DEC 1ST by 4pm overdue projects will be rejected. 2. Submit the project to your instructor in her office/ blackboard before the date above. Make sure that all files are working in your disks. 3. Each disk must be clearly labelled with Names of the students and their University ID number. Academic Rules and Regulations Cheating and plagiarism are disallowed and prohibited. Never copy your colleagues work! Evaluation and grading policy AIS access project is an essential component of the course. The project is mark out of 25%. Since the project is dividing into 5 subsystems that will make the grade distribution as the following: Subsystem Evaluation Description Main Menu 5% Graphical Interface, Appealing, Colours Marketing 4% Fields consistency, Relationships, Graphics Sales 4% Fields consistency, Relationships, Graphics Invoice 4% Fields consistency, Used Queries, Graphics Suppliers 4% Fields consistency, Used Queries, Graphics Human Resource 4% Fields consistency, Used Queries, Graphics. 25% Project Specifications As mentioned earlier, this project is basically representing an internal information system for an enterprise. It is signifying five major business departments which are Marketing, Sales, Invoice, Suppliers and Human Resources. For any business type you are interested in you have to develop your project, for each particular department, you should build a database to describe the internal processes included in each department. 1.Main menu (FORM and Queries) This section would be preferably delayed until you get the five forms done. In the main menu section you will be using Microsoft Access Form to design the main form; this form would be the graphical user interface (GUI) of your system, it has to include the following objects: • • • The company logo. ( Right or lift aligned, but it must be at the top part of the menu) Company Brand Name (Don’t forget to mention your company’s address and the contact details below the company name in smaller font). 6 buttons to link the main form to the rest of the forms in the following order: – A command button to link the main form with the Marketing form. – A command button to link the main form with the Sales form. – A command button to link the main form with the HR form. – A command button to link the main form with the Suppliers form. – A command button to link the main form with the Invoice form. – A command button to exit the form. Keep in mind that command buttons in Microsoft access can only be linked to forms, which means you firstly have to build the tables and then have them designed in form view, and finally, you link the main form buttons to these forms. 2.Marketing (Table, Form and Queries) Marketing is the first table you need to build; it represents the CRM system in your company, as it holds all the information about existed and potential customers. Marketing table is a key component of your company information system; it should include the following attributes: Field Data Type Description Csm_number Number Customer number (Primary Key) Csm_Fname Text Customer First Name – Field size = 10 Csm_Lname Text Customer Last Name – Field size =20 Csm_Age Number Customer Age Csm_Tele Number Csm_City Text Customer Telephone Number – Mask (111)5551212 Customer City – Field size = 20 Csm_Country Text Customer Country – Field size = 20 As soon as you have finished working on the table, you have to design a form to hold this table, and then finally you are expected to enter these records. Number Fname Lname Age Tele City Country 11122 Mark Brown 33 4445623320 Manchester England 11123 George Andrew 45 4446756675 Leeds England 11124 Louise Chris 19 4476553210 Huddersfield England 11125 Catherin Shirmen 27 4429743222 Halifax England 11126 John Alfonso 54 4455663211 Edinburgh Scotland 11127 John Green 22 4414842232 Huddersfield England For the previous table you are required to apply the following queries. 1. Query One “Query-Age”. Query the first name, last name, and age of people between the ages of 15 and 30. 2. Query Two “Query-city”. Query the first name, last name, Tele of people from Huddersfield. 3. Query Three “Query-Country”. Query the first name, last name, Tele of people from England and are at least 30 years old. 4. Query Four “Query-Invoice”. Query the Invoice date, Invoice number and Invoice value for customers who hold ID of 11122 This table has a single relationship with the Invoice table. It shall be in this form: Table Field Relationship Table Field Csm_number Number One to Many Invoice Customer 3.Sales (Table, Form and Queries) Sales table is the core business of your company; it has all the products and items provided by your company, so the contents of this table are subject to your business type. It should minimally include seven different attribute with a primary key for sure, and as the first table, you should enter at least six records in your table, below you would find a sample of sales table. You can use this table as a template for your table and you insert data based on the following attributes: Field Data Type Description Product_ID Number Primary Key Product_Name Text Name of the product Product_Price Currency Price of the product in USD $. Product_Location Text Location in the shelf. Ex. 302/Shelf 1 Tax Number It’s 15%, 16% or 17%. Employee_ID Number In charge employee’s number (From HR table) Supplier_ID Number Supplier ID number (From Supplier Table) As soon as you have finished working on the table, you have to design a form to hold this table, and then finally you are expected to enter seven records. For the
previous table you are required to apply the following queries. NOTE: If you must change your table design in order to do the queries, you should note that on your grade sheet. You might want to add records to your database so that you can test whether these queries work. Be sure to test that they do. 5. Query Five “Query-product”. Query the product id, product name and product price for products which have a valid applied tax of 16%. 6. Query Six “Query-Price”. Query the product id, product name and in charge employee’s Fname, employee’s Lname and telephone number of products priced between 16 to 30 dollars. Hence you should have to set up a relationship previously. 7. Query Seven “Query-Supplier”. Query the Supplier name, Supplier Tele for products range priced between 12 to 20 dollars. Hence you should have to set up a relationship previously. This table has two different relationships with two tables which are Supplier and HR; these relationships shall be in this form: Table Field Relationship Table Field Sales Product_ID One to Many Supplier Supplier ID Sales Product_ID One to Many HR Employee ID 4.HR (Table ,Form and Queries) Human Resources table is where you organise and keep records of your employees; it is an essential part of your system. HR table should include these attribute at least: Field Data Type Description Employee _ID Number Primary Key Employee_Fname Text Employee First Name – Field size = 10 Employee_Lname Text Employee Last Name – Field size = 10 Employee_Tele Number Telephone Number – Mask (111)555-1212 Employee_Salary Number Salary of Employee Employee _City Text Employee City – Field size = 20 Employee_Country Text Employee Country – Field size = 20 As soon as you have finished working on the table, you have to design a form to hold this table, and then finally you are expected to enter these records. Number Fname Lname Tele Salary/Month City 33220 Charles Andrew 4489356220 2100 Sheffield England 33221 Vincent Gloundin 4489544635 2400 Essex England 33222 Rose Flown 4489633778 1800 York England 33223 Susan Kroen 4489233455 1800 Dewsbury England 33224 Kareem Salami 4490367376 2500 Sheffield England 33225 Michel Sinatror 4478389933 2300 Sheffield England For the previous table you are required to apply the following queries. Country 8. Query Eight “Query-Salary”. Query the Employee ID, Employee Fname and Employee Lname of employees who are getting paid over than 2000 £ a month. 9. Query Nine “Query-City”. Query the Employee ID, Employee Fname, Employee Lname and salary for those employees who live in Sheffield. 5.Suppliers( Table, Form and Queries) Suppliers are those reliable partners who provide you with the products you need, they are playing a vital role in your business processes. The suppliers’ type depends on the business you do, but the table’ attributes should be as the following: Field Data Type Description Supplier_ID Number Primary Key Supplier_Name Text Name of the supplier – Field size = 10 Supplier_Tele Number Telephone Number – Mask (111)555-1212 Supplier_Country Text Country of the supplier – Field size = 10 As soon as you have finished working on the table, you have to design a form to hold this table, and then finally you are expected to enter at least 6 records. For the previous table you are required to apply the following queries. NOTE: If you must change your table design in order to do the queries, you should note that on your grade sheet. You might want to add records to your database so that you can test whether these queries work. Be sure to test that they do. 10. Query Nine “Query-Name”. Query the Supplier ID, Supplier name and Supplier Tele of Suppliers from England. 6.Invoice (Table and Form) Invoicing subsystem is a significant section of this system, as it is used to archive the sales’ transactions. The invoice table should include the following attributes: Field Data Type Description Invoice_ID Number Primary Key Invoice_Number Number Number of the Invoice Invoice_Value Currency The Value of the Invoice in dollars $ Invoice_Date Date/Time Short date ex. 01/08/2014 Customer Number ID number of customer As soon as you have finished working on the table, you have to design a form to hold this table, and then finally you are expected to enter these records. Invoice_ID Invoice_Number Invoice_Date Invoice_Value Customer 4460 107 01/08/2014 $500 11122 4461 207 02/08/2014 $455 11123 4462 307 13/08/2014 $600 11122 4463 407 14/08/2014 $233 11125 4464 507 16/08/2014 $105 11122 Project Assessment Form Student’s Names: ID Numbers: Section: Criteria Main Menu Form (Company Logo, Graphic, colours) Marketing (Table, Form and Queries) Sales (Table, Form and Queries) Invoice (Table and Form) Suppliers (Table, Form and Queries) Human Resource (Table, Form and Queries) Weight Grade 5% 4% 4% 4% 4% 4%