In this assessment, you will construct database tables and relationships, and you will populate that database with data. You will also write database queries and develop database reports. You are required to complete this assignment using either Microsoft Access or LibreOffice Base.
“Simply Credit Card” Company Scenario
You have recently been hired as an IT manager at Simply Credit Card Company. This company issues credit cards for customers, and customers use the credit cards at various stores. Simply Credit Card Company already has a database system; however, the database was designed about 20 years ago and has multiple issues. One of the issues is that the data is not normalized, and parts of the data have been replicated in multiple tables. One of your initial assignments, a high-priority one, is to redesign the database and eliminate the issue of replication. The company’s CIO, Jim Miller, has personally entrusted you with this project. In order to establish a new database, you came up with a preliminary database design. You want to construct a prototype of your database design using either Microsoft Access or LibreOffice Base before you present the design to your team members.
Your database needs to keep track of customers, credit card details, sales that were made using the credit cards, and the merchants with whom the sales were placed. You arrived at the tables listed below for this scenario. You have also indicated the primary key and foreign key (where applicable) in each table. The sample data with all five tables is presented in the attached word document.
Important Note:
As you progress through Module Assignment, please stick to the data types I indicated in the Word document (see above). For example, please use Text data type for credit card numbers, and integers for merchant id, customer id etc. Some of you may like to use big integer (Bigint or large number) or similar data types in newer versions of MS Access for credit card numbers. But please do not use this and instead use text field for credit card numbers. Bigint data type may cause issues as you convert from one version of MS Access to another version (e.g. 2016 to 2013).
PART A: Building database tables
Step 1: If you have not already done so, you will need to acquire the database management software (Microsoft Access) that is required for this project. Check the Technical Resources module to learn more about acquiring the required software.
Step 2: Open your database management software. Save a new database, and give it a title (e.g., Mod2-DB).
Step 3: Using your database management software, create the following tables: CUSTOMER table, CREDIT_CARD_ACCOUNT table, ITEM_TYPE table, MERCHANT table, and SALE table in that order. Use the data types from in the word document (above) to define each field in each table.
Step 4: Create the Primary Key – Foreign Key (PK-FK) relationships among tables.
Step 5: Enter data into the five tables. Add sample data of 10 rows for the CUSTOMER table, 15 rows for the CREDIT_CARD_ACCOUNT table, 10 rows for the MERCHANT table, 5 rows in the ITEM_TYPE table, and 30 rows for the SALE table. Add fictitious data.
PART B: Constructing database queries
In this part, you will create queries and retrieve data in Microsoft Access. The details of what queries to construct and directions are included here: Module 2: Database Queries Assignment MS Access
PART C: Constructing database reports
Define a report for each of the queries in Part (B) of the assignment. Give each report a meaningful title to describe in business terms what is shown in the report. Modify the column headers as needed so that there are spaces between words (or abbreviations) used in the column names. By default, the report uses the table’s column names as column headers. These aren’t always stated in business terms, and renaming the columns in this way makes the report more readable. The data should be sorted the same way in the results of the reports and should include all the columns included in the query.
Instructions:
You are expected to complete this assignment in Microsoft Access. Complete and submit the assignment files by 23:59 CST on the due date indicated.