General information about the Database cation used:

Each group isrequired 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 systemhe/she sees suitable. Students need to make sure that the developed databasemeetsthe 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 anMS 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 focusingon 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 itemsinformation 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 representsa 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 AccessDatabase

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.
  • 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      CreateReports and Forms.

Each groupmust do the following:

• Create onereport that helps in decision making.(Name the report for example,Group#2_report).
• Create Two Forms as following.
  • One form to make it easy to enter records directly in one table. (Name the form for example: Group#2_direct_form)
  • 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.

• Queries requirements:

• One query that answer a question directly from one table (Name the query for example: Group#2_direct_query)
• One query that displays calculated information (Name the query for example: Group#2_calc_query)
• One query that fetch information gathered by more than one table.(Name the query for example: Group#2_joined_query) (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:

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