PMU Management Information Systems Questions

College of Business2021-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%