CIS5100 Data Structures and Programming

1. Create a Database and import the four (4) Text files into four (4) Tables
2. Modify Table Design
3. Set Primary Keys
4. Create Relationships between the Tables
5. Edit Customers Table
6. Create Data and Update Queries
7. Create an Invoice Report

Task 1: Create and Import

Create a new blank Access Database (using any version from Access 2007 to current) and name it [lastname] [initial] _ [student number] _ [course code] _ assign1.accdb(eg. genrichr_0050051005_cis1000_assign1.accdb).

Import the four sets of data from your firm’s Intranet.

The easiest and quickest way to import data into Access is by using the Import Text Wizard.  The following steps must be followed carefully to ensure the data is imported correctly:

Importing Tab Delimited Text files into Access (any version from Access 2007 to current) using the Wizard  

• On the External Datatab, in the Import & Link group, click the Text File data source icon.
• In the Get External Data - Text Filewizard popup, browseto find the ‘Assignment01 Text File Customers.txt’ text file and select the Import the source data into a new table in the current database option – then click OK.
• Select the Delimited – Characters such as commas or tab separate each fieldoption on the first page of the wizard - then click Next.
• Select the Taboption on the second page of the wizard and turn on the First Row Contains Field Namescheck box - then click Next.
• Set the following for each fieldon the third page of the wizard:
  • Field Name– keep the field name as it appears when imported.
• • In the List of Tables, copy the table called tblItems and paste it with the new name tblBackupItems.

    Create an Update Querythat will raise the Belgium (BE) Cost Price for all items on orderthat are not in stockby 7.25%. It is always good practice to backup any data before running an update or a delete query. The following steps should be followed to achieve this Query:

  Use tblItems for this Update Query, not your back up table tblBackupItems. If you have difficulties with this query and need to restore your data to the original values, you can copy columns as necessary from tblBackupItems to tblItems and start again.

  • Create an Update Querythat will increase the Cost Price (BE)  in tblItemsby 25%for all items currently not in stockbut on order.
  • Include rounding to 2 decimal places with the update calculation by placing the following function around the update formula:  ROUND( update_formula ,2).

In daily life, every one use different type of data in the form of cell phone address book, email account, search engine and etc. All the data are organized in the form of database. Every one use the database directly or indirectly. Here we discuss or analyze two different data handling tools such as Access and Excel from the two different assignment

 Define Database Management System 

A DBMS is a collection of programs that allows users to add, change, and filter information from a database. It defines rules to validate and manipulate the data. It is a transitional layer between programs and the data. In Software market, these are in the form of open source and commercial. Open Source DB tools are MySQL, Base (Star Office) and etc.

 DBMS is the appropriate tool 

In our project, we chosen Microsoft Access. It is a simple GUI tool. It is easy to use/learn for basic learner. No need to learn the SQL query for basic operation.

Uses: 

• Easy to find the duplicate record and remove it
• Data can be integrated among more tables
• Easy to share the data between two or more systems
• Easy to set the security to open or access the database
• It has control over concurrency. If more than one user access the data, it automatically lock the control to one user and after execution, it releases the control to another one
• Few DBMS has auto backup features. It automatically backup the data during the particular interval such as one day or one week or else.

Define Decision Support System 

It is an interactive software-based system proposed to support decision makers gather suitable info from raw data, records, and industry models to identify and solve problems and make assessments

 DSS is the appropriate tool 

In our project, we chosen Microsoft Excel as DSS. It is also an easy tool to handle the data. It has few DBMS features, it is not easy to handle with programming tools.  It acquires data from the vast informations. It analyzes the data using advanced statistic functions to filter the necessary information.

 Uses: 

• It can compare two or more set of data in various format such as Graphs, Tables and etc.
• It reduces the time to solve the problems
• It is easy to access the data which is used for building the data effectively
• It reduces the labour cost
• It is improved assessment creating usefulness and better conclusions. Decision quality and decision making effectiveness are however hard to record and measure

 I analyzed the Report using Scenario Summary tool. In this business, there are two different Markup type such as Sales and Supplier.  Both markup type of business profit are same with the Freight Type as well as Exchange rate type I recommended the Victor Von Doom Freight Type. Because it has the lowest expenses which is around $28,000

I recommended the Canadian Country to import the product. Because it has the lowest exchange rate. So that purchase expenses from the Country CA is less than the Country BE

 Conclusion 

From these two project, I have learnt how to import, modify and filter the data from data base. I have used several of functions (mathematical and statistical) from worksheet.

References

• Gaining Competitive Advantage with Decision Support Systems. (2017). Managementstudyguide.com. viewed on 19 August 2017, from https://www.managementstudyguide.com/gaining-competitive-advantage-with-decision-support-systems.htm
• Google+., T. (2017). Decision Support Systems – DSS (definition). Informationbuilders.com. viewed on 19 August 2017, from http://www.informationbuilders.com/decision-support-systems-dss
• Advantages of DBMS. (2017). Computer-dbms.blogspot.in. viewed on 19 August 2017, from http://computer-dbms.blogspot.in/2011/09/advantages-of-dbms.html

What is a Database Management System (DBMS)? - Definition from Techopedia. (2017). Techopedia.com. viewed on 19 August 2017, from https://www.techopedia.com/definition/24361/database-management-systems-dbms.