beach activities

Practical Assessment Overview
In this practical assessment, you need to work in a group of 3 to 4 students.

Each group is required to choose a business scenario of a selected business, then design and develop a database (DB) to support it. The business scenario can be of a company, department, shop, or any other business activity that your teacher approves. Examples are car rental database, gym membership database, games rental database, online course database, and more others.

Each group needs to use Microsoft Access to present their work. The group must use Microsoft Access to develop a small database (DB) for the business scenario. In the form of an Access database and use Microsoft Word to write a short report to explain the chosen business scenario and developed database.

Each group is also expected to identify the different components of the database. This includes:

· Tables
· Table Fields
· Relations
· Business processes and BPMN diagram

The created Access database is intended to enable the system users to interact with the system, and apply different operations on the database, such as insert, delete, create, and update.

General information about the Database cation used:
Each group is required to use Microsoft Access 2010 or higher to build their simple DB. The group needs to create one database that contains a minimum number of four (4) tables.

1. What information does each group need to add to the database?
Each group is free to choose the type of database system he/she sees suitable. Students need to make sure that the developed database meets the business’s final goal.

2. What kind of information users are expected to store/retrieve in the system database?
This is related to the chosen (or assigned) database scenario. Different types of requirements and information could be involved.

3. What kind of relations the database tables apply, and why?
The developed database must provide a way to gather information from different tables.

4. What kind of queries and reports the suggested database help in answering?
The output expected from each developed database is the ability to answer queries and provide suitable reports when needed. What queries and reports your business database provide.

Phases of Building the Database
Phase 1. Creating a report “Planning”
Each group is requested to create an MS word report and name it for example (Group#2_BusinessScenarioName_Rpt.docx). The report should contain the following information:

1.1 Name of the Database (DB)
Each group should choose a name of the DB. The name must reflect the main functionality of the developed DB.

1.2 General Purpose of the DB
Each group needs to give a brief description of the purpose of the DB. This should help in focusing on your goals. It is advisable to use the following questions as your guide:

How you expect to use the database?

Who will use the database?

When and how each database user will use it.

1.3 Find the required information- information needs to be recorded:
· Search in existing information of the system, such as customer archives.
· Consider the final report types. Imagine the results, and ask yourself how data should be stored in the database to get this report—imagining final reports helps in identifying items in your database.
· Break information in smaller parts. For example, do you need to store the first name separated from the last name or not. This is identified by the form of the final report you are looking for. For example, maybe storing the title (“Mr” or “Ms”) as individual fields in the database table will be more useful and easier to fetch.

1.4 Define database tables
Each group should divide the information into tables. Think of the main entities that have enough information therefore it can be represented as tables.

1.5 Define table columns
From the previous step, turn each items information into columns/fields. Each row/record in the table should present complete information about the table item. For example, in a product table, we think of what details we need to know about a single product and represent every single detail in a separate column. In product, each row represents a meaningful record. It is advisable to use the following questions as your guide:

· What information to store in each table?
· In case of deleting one table record, will you lose another information from another table? If yes. Then maybe you should think of separating the two tables to maintain data integrity.
· Do you need calculated data in your results, such as age? Then do not include them as individual columns in the table because you can calculate it from birthday information.
· What data type each column should be?

1.6 Specify primary keys
Each group is asked to define the column that uniquely identifies each record for every table in the database.
1.7 Define Relationships
Each group is asked to define how all pieces of tables are interacting with each other and how to bring information together in a meaningful way. This is established by determining the relationships between tables: one-to-one, one-to-many, and many-to-many.

Add a screenshot of the relationships diagram from the Access project.
1.8 Build BPMN diagram
Define what questions your database information is answering, and what procedural steps and people involved in the business process.
The business diagram should contain:
§ Activity
§ Actor
§ Event
§ Flow
§ Gateway
§ Partners

Phase 2. Building the Database (DB) “Implementation”
Each group is required to do the following:

2.1 Create an Access Database
Create a Database using your Group# followed by the business scenario name you listed in your report (for example: Group#2_BusinessScenarioName_DB.accdb). This DB represents the correct design that will achieve your database purpose or goal.

2.2 Create the required database
Each group must use:

§ An appropriate name for each table. This name must be related to the stored information and table fields.

§ Relations between tables

§ Primary keys and foreign keys.

2.3 Test and Refine your Database
Each group must:

§ Enter at least 5 records in each table.

§ Test the relationships between tables and make sure data entry restrictions are applied.

§ Highlight problems and update your database design by inserting or removing columns as required.

§ Look for unexpected data duplication and alter the database table to avoid duplication.

§ Create a test query (Name it: Group#2_test_query). And test if your database design shows the answer you expect.

o For example, in a product database. If I need to know all the products sold today by (X) seller. And the existing relationships do not provide this information. Then you may need to update the database to add the selling date and the seller name.

2.4 Create Reports and Forms.
Each group must do the following:

§ Create one report that helps in decision making. (Name the report for example, Group#2_report).
§ Create Two Forms as following.
o One form to make it easy to enter records directly in one table. (Name the form for example:Group#2_direct_form)
o Choose a table in your database that is linked with other table using foreign key, and create a form to insert data in that table. (Name the form for example: Group#2_connected_form)
§ Data stored in the foreign key column should appear as a drop-down list in the form, where the user just must choose from existing values.

Note: After you finish both the MS word report and the MS Access project files, create a folder with a sample format (CIS1003_AT2_AliSalem_AbdullaSaif_OmarSultan). Then save both project files to folder and compress to a zipped file and submit to BB Learn as directed by your class teacher.

Phase 3. Testing the Information System (IS) “Testing”
3.1 Each group is required to test the DB by creating three queries to answer three different questions.
o Queries requirements:

1- One query that answer a question directly from one table (Name the query for example:Group#2_direct_query)

2- One query that displays calculated information (Name the query for example: Group#2_calc_query)

3- One query that fetch information gathered by more than one table. (Name the query for example:Group#2_joined_query) (e.x. select all products that have been sold today after 6 PM by seller X).

Before submitting your assignment, make sure that the following items are ready:

(1) The Planning Report:

(2) The Access Database:

Report and Access
(Assessment Rubric) Total Marks – 50 (50%)

Item

Information

Marks available

Marks Awarded

Phase 1
Planning Report
(20 Marks)

Planning – Microsoft Access Report

1.1
} Name of the Database
1

1.2
} General Purpose of the DB
2

1.3
} Find the required information
1
1.4
} Define database tables
2

1.4
} Define tables columns
2

1.5
} Specify primary keys
4

1.6
} Define Relationships
3
1.7
} Build BPMN diagram
5

TOTAL REPORT

20

Phase 2
Access Database
(20 Marks)

Implementing the Database

} Suitable Microsoft Access database file Name
} Suitable tables names
1
1

Creating and testing the database

Tables
} At least 4 tables
} At least 5 records inserted in each table
4
5

Relations
} Relationship diagram is logical
2

Forms
} Forms works
2

} Direct form created
} Connected form created
1
1
Reports

} Report works
2

} One report created
1

TOTAL Implementation
20