College of Business 2021-2022 Fall Semester Management Information Systems Access Project Due

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:

To provide hands-on experiencing, designing and building a database.

To relate database applications to business functions.

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:

Submission is due to WED DEC 1ST by 4pm overdue projects will be rejected.

Submit the project to your instructor in her office/ blackboard before the date above. Make sure that all files are working in your disks.

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

Customer Telephone Number – Mask (111)555-1212

Csm_City

Text

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.

Query One “Query-Age”. Query the first name, last name, and age of people between the ages of 15 and 30.

Query Two “Query-city”. Query the first name, last name, Tele of people from Huddersfield.

Query Three “Query-Country”. Query the first name, last name, Tele of people from England and are at least 30 years old.

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.

Query Five “Query-product”. Query the product id, product name and product price for products which have a valid applied tax of 16%.

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.

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

Country

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.

Query Eight “Query-Salary”. Query the Employee ID, Employee Fname and Employee Lname of employees who are getting paid over than 2000 £ a month.

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.

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

Weight

Grade

Main Menu Form

(Company Logo, Graphic, colours)

5%

Marketing

(Table, Form and Queries)

4%

Sales

(Table, Form and Queries)

4%

Invoice

(Table and Form)

4%

Suppliers

(Table, Form and Queries)

4%

Human Resource

(Table, Form and Queries)

4%