Database Design and Implementation Assignments | Online Homework Help

Database Design and Implementation

Don't use plagiarized sources. Get Your Assignment on
Database Design and Implementation Assignments | Online Homework Help
Just from $13/Page
Order Now

Products on Sale Database

 

CIS 3100 – Database Design and Implementation

Products on Sale Database for Kahdea Inc.

 

Table of Contents

 

1. Project Overview

The activities in this project are designed to provide a better understanding of how data is organized into a relational database.  A relational database enables management information systems to support inventory, transactions and business intelligence capabilities.

Using Microsoft Access 2016, you will create and manage tables, perform data imports, link tables via relationships, create queries and finally create reports for executive summary.

A general overview of relational database fundamentals and Microsoft Access training via Lynda.com is provided in section 11 to enable your success in this project.  Please allow enough time to expose yourself to this material before starting the project.

 

2. Company Background

Kahdea Inc. is a small startup company that sells sports merchandise online. The mission of the company is to successfully sell and deliver sports products for all. The company sells products for a wide variety of sports including football, basketball, baseball, soccer, hockey, and volleyball.

Kahdea is composed of twenty-eight (28) employees who work in different departments (production, operations, automation, accounting, human resources, finance, marketing, etc.). During their probation period, all employees are trained before being assigned into their positions.

Kahdea is planning to have their annual sale next month. This sale is an important event for the company because it can typically generate 31% of Kahdea’s annual revenue. The company has been planning this sale for some time now, and wants to make sure all transactions are supported without issue.

 

Your specific role in the project

You are a new hire at Kahdea. During your training, you are expected to learn about building and maintaining relational databases using Microsoft Access. The database you will support is being used to keep track of Kahdea’s sales during this period. Although the database has been constructed, the team needs your assistance inserting data, using forms, creating queries and reports.

Your first training task requires you to watch the Microsoft Access training videos on Lynda.com.  A series of hyperlinks for reference information are provided for you at the end of this project document.

After you become familiar with Microsoft Access, open the Kahdea.accdb file.

 

3. Create a Table

Before you begin, the team wants to ensure you understand table structure. They would like you to create a table for Employees and populate a few records. The table should include the field names: EmployeeID(PK), Last Name, First Name, Phone and Attachments.

  • Step 1: Click the “Create” tab on the top ribbon and select “Table Design”.
  • Step 2: Enter the Field Names and Data Type. Include a Primary Key (EmployeeID) for

             the table.  Select attachment as the data type for the Attachments field.

  • Step 3: Save the table as “Employee”.
  • Step 4: Enter the following records into the table. Create a personal record by substituting the red text with your information (enter a fictitious phone number).  This record will be used later in section 9 of this assignment.
EmployeeID Last Name First Name Phone Attachments
1 Kathleen Salazar (909) 869-5438  
2 Kim Stella (909) 869-2360  
3 Leen Hlahza (909) 869-5079  
4 Mike Jacob (909) 648-1010  
5 <your first name> <your last name> <a phone number> see section 9
  • Step 5: After you enter the records, close the table. The entries should be saved

professional writing services near me

4. Importing Data

In preparation for the sale, the team has gathered information regarding products’ supplier, and customers that need to be entered into the system. Since there is a lot of information to upload, using the forms will not be effective. Microsoft Access allows for a bulk insert of data.

Populate Customer table with data

For this task you will need the Customer.xlsx file.

  • Step 1: Right click on the Customer table (Under All Access Objects panel) and select

             Import”, choose “Excel”.

  • Step 2: In the pop-up window click “Browse” and navigate to the xlsx file.

             Select the file.

  • Step 3: Click “Append copy of the records to the table” and in the drop-down menu

choose “Customer”, click “OK”.

  • Step 4: Click “Next” until the final window. Click “Finish”.

 

4. Importing Data (continued)

Populate ProductSupplier table with Data

For this task you will need the ProductSupplier.txt file.

  • Step 1: Right click on the ProductSupplier table (under All Access Objects panel) and

             select “Import”. Choose “Text File.

  • Step 2: In the pop-up window click “Browse” and navigate to the txt

file, choose the file.

  • Step 3: Click “Append copy of the records to the table” and in the drop-down menu
  • choose “ProductSupplier”, click “OK”.
  • Step 4: Click “Next” until the final window. Click “Finish”.

Note: For best results, right-mouse click on the ProductSupplier.txt file then save the file to your desktop to enable this data import.

 

  1. Create Forms

As a new hire at Kahdea Inc. you are tasked to create forms. Database administrators can enter the data directly into the tables. However, your boss feels it would be more efficient to create a form that coaches users to enter information in the best order. Create a user entry form to capture suppliers and product category data.

 

Create the Supplier Form

Apply the following steps using the Form Wizard to create a data entry form with tabular layout. Include all fields except Webpage and Notes.

 

  • Step 1: Click the “Create” tab on the top ribbon and select “Form Wizard
  • Step 2: In the popup window select the Supplier Table from the drop-down menu

             (Tables/Queries). Select the fields you wish to use and move them to the

Selected Fields box by clicking the “>” button. Click “Next”.

  • Step 3: Click and select “Tabular”, and click “Next”.
  • Step 4: Name the form Populate Supplier, and click “Finish”.
  • Step 5: Click the New Record
  • Step 6: Populate the form with new entries using the table given below, once all entries

             are entered, save the form. Once finished, close the form.

Field Input               
CompanyName Iamz Co
Address1 153 9th Street
City Brea
State CA
Zip 92821
Country USA
Phone 7148884565
FaxNumber 6523937595

 

Create the ProductCategory Form

Apply the following steps using the Form Wizard to create a data entry form with a columnar layout, to add data into the ProductCategory table.  Include all fields except ProductCategoryID and Active.

  • Step 1: Click the “Create” tab on the top ribbon and select “Form Wizard”.
  • Step 2: In the popup window select the ProductCategory table from the drop-down

 menu (Tables/Queries). Select the fields you wish to use and move them to the

Selected Fields box by clicking the “>” button. Click “Next

  • Step 3: Click and select “Columnar”, and click “Next”.
  • Step 4: Name the form Populate ProductCategory, and click “Finish”.
  • Step 5: Click the New Record
  • Step 6: Populate the form with new entries using the table given below, once all entries

             are entered save the form. Once finished close the form.

Field Input
ProductCategory Bodybuilding
  1. Creating Relationships

Although the tables have been created, some of them are missing relationships. Without table relationships, inserts, updates, or deletions in one table, data will not propagate to the other tables. You will need to create the relationships for all tables in the database.

Note: All tables have at least one relationship while some have two.

  • Step 1: Click the “Database Tools” tab on the top ribbon and select “Relationships”.
  • Step 2: If a table is not displaying on the screen, click the “Database Tools” tab on the

top ribbon and select “Show Table”, on the popup window and click “Add”. After

selecting any missing Tables, click “Close”.

  • Step 3: Drag the Primary Key from the first table to the Foreign Key on the second table.

             A new window will appear displaying the joint keys. Check “Enforce Referential

Integrity”. Click “Create” to create the relationship.

  • Step 4: Continue creating relationships for tables that do not have relationships.
  • Step 5: Save all changes and the close the workspace.

 

  1. Create Queries

Kahdea Inc. wants to gather information from the database. Create the following queries to enable decision making for inventory and logistics.

 

MostProductsSold Query

Create a query that displays the Product Code, Product Description, and number of times the Product was sold. Limit to the results to products that were sold at least 10 times. Save the query as MostProductsSold.

 

CustomerOrders Query

Create a query that displays CustomerID, ShipName, Order ID, Order Date, Product Code, Product Description, Quantity Sold, Price, and Total. Limit the results to Orders between 07/06/2017 and 09/07/2017. Save the query as CustomerOrders.

Note: In order to accomplish the Customer Orders query, you will need to create the “Total” field in your query results by using the “’Builder” tab. The following reference provides the method used to create a calculation query in Microsoft Access.

 

  1. Create Queries (continued)

CustomerOrders Query

 

ProductPrices Query

Create a query that displays the Company Name, Products Code, Product Description, Purchase Price, and Sale Price. Limit the query to products where purchase price is greater than sale price. Save the query as ProductPrices.

  • Step 1: Click the “Create” tab on the top ribbon and select “Query Design”.
  • Step 2: In the popup window, select the table you need for the query and click “Add”.

 Once all tables are selected, click the “Close” button.

  • Step 3: Select the fields required for the query.
  • Step 4: Enter criteria for the query.
  • Step 5: Click the “Run” button. Verify the query displays the correct data.

 

 

  1. Reports

Your manager is impressed with your database skills and would like you to prepare the following information for reporting to the executive team. Your manager is a has high expectations on the reporting format, so you will need to display this information in a presentable format.

 

Create a report to display CustomerOrders. Include CustomerID, ShipName, Order ID, Order Date, Product Code, Product Description, Quantity Sold, Price, and Total. Include the title “Customer Report” in the page header. Include the run date, page number and total pages in the page footer. Save the report.

  • Step 1: Click the “Create” tab on the top ribbon and select “Report Wizard” on the far

             right.

  • Step 2: In the popup window, select the query from the drop-down menu

             (Tables/Queries).  Select the fields you wish to use and move them to the

Selected Fields box by clicking the “>” button. Click “Next”.

  • Step 3: Select the fields to group by. This is optional and is not always required. Click

 Next”.

  • Step 4: Select the “sort order” of the report. Click “Next”.
  • Step 5: Select the Format of the report and landscape orientation. Click “Next”.
  • Step 6: Enter the name for the report. Click “Finish” and the report will display as a print

             preview.

 

Note: In order to accomplish the Customer Report, you will need to use custom configuration via Design View. The following reference provides the method used to create this report.

https://youtu.be/T-HgfywQ2Y4  Runtime: 28:14

 

 

  1. Project Assessment

Your manager is impressed with your performance supporting this information system and wants to measure your competency in the scope of your assignment.  She has asked for your assessment of the following.

  • What are the major advantages of DBMS software applications such as Microsoft Access?
  • What are the components of a relational database table (entity)?
  • What are the benefits of using queries (views) in management information systems?
  • Describe one lesson learned in your efforts to complete this project assignment

 

  • Step 1: Provide a comprehensive summary statement in paragraph form using Microsoft Word and name the file CIS 3100 Response <First Name Last Name> (Enter your first name and last name).
  • Step 2: Upload your Microsoft Word file to the Employee table, attachment field of your personal record created in section 3 of this assignment.

 

  1. Project Deliverables

Upload your Microsoft Access file to Blackboard, including your project assessment uploaded to your personal record (created in section 3 of this assignment) according to the specific instructions provided by your instructor.

 

 

 

 

 

  1. Microsoft Access Training Videos

Use the following references to brief yourself on Microsoft Access and supporting activities to succeed in this project and any future work with relational databases.  It is not necessary to navigate completely through each course for this assignment.

 

The additional three learning series are provided to further enable your success in this project.  You should be able to find a section within this reference to support any questions you may have.

 

 

Learning Relational Databases

 

https://www.lynda.com/Access-tutorials/Learning-Relational-Databases/604214-2.html?srchtrk=index%3a33%0alinktypeid%3a2%0aq%3aMicrosoft+Access+training%0apage%3a1%0as%3arelevance%0asa%3atrue%0aproducttypeid%3a2

 

·         Relational Structures

·         Breaking Data Down Into Its Components

·         Understanding Entities and Table

·         Develop Relationships

·         Develop Subtypes and Supertypes

·         Following a Naming Convention

·         Creating Tables in Access

·         Establish Relationships in Access

·         Write Queries in Access

 

 

Access 2016 Essential Training

 

https://www.lynda.com/Access-tutorials/Access-2016-Essential-Training/367064-2.html?srchtrk=index%3a8%0alinktypeid%3a2%0aq%3aMicrosoft+Access+training%0apage%3a1%0as%3arelevance%0asa%3atrue%0aproducttypeid%3a2

 

 

Access 2016 Queries

 

https://www.lynda.com/Access-tutorials/Access-2016-Queries/455726-2.html?srchtrk=index%3a18%0alinktypeid%3a2%0aq%3aMicrosoft+Access+training%0apage%3a1%0as%3arelevance%0asa%3atrue%0aproducttypeid%3a2

 

 

Access 2016 Forms and Reports

 

https://www.lynda.com/Access-tutorials/Access-2016-Forms-Reports/455727-2.html?srchtrk=index%3a17%0alinktypeid%3a2%0aq%3aMicrosoft+Access+training%0apage%3a1%0as%3arelevance%0asa%3atrue%0aproducttypeid%3a2

 

 

Calculate your paper price
Pages (550 words)
Approximate price: -

Why Choose Us

Quality Papers

At Myhomeworkwriters.com, we always aim at 100% customer satisfaction. As such, we never compromise o the quality of our homework services. Our homework helpers ensure that they craft each paper carefully to match the requirements of the instruction form.

Professional Academic Writers

With Myhomeworkwriters.com, every student is guaranteed high-quality, professionally written papers. We ensure that we hire individuals with high academic qualifications who can maintain our quality policy. These writers undergo further training to sharpen their writing skills, making them more competent in writing academic papers.

Affordable Prices

Our company maintains a fair pricing system for all academic writing services to ensure affordability. Our pricing system generates quotations based on the properties of individual papers.

On-Time delivery

My Homework Writers guarantees all students of swift delivery of papers. We understand that time is an essential factor in the academic world. Therefore, we ensure that we deliver the paper on or before the agreed date to give students ample time for reviewing.

100% Originality

Myhomeworkwriters.com maintains a zero-plagiarism policy in all papers. As such, My Homework Writers professional academic writers ensure that they use the students’ instructions to deliver plagiarism-free papers. We are very keen on avoiding any chance of similarities with previous papers.

Customer Support 24/7

Our customer support works around the clock to provide students with assistance or guidance at any time of the day. Students can always communicate with us through our live chat system or our email and receive instant responses. Feel free to contact us via the Chat window or support email: support@myhomeworkwriters.com.

Try it now!

Calculate the price of your order

You will get a personal manager and a discount.
We'll send you the first draft for approval by at
Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Homework Writing Services

My Homework Writers holds a reputation for being a platform that provides high-quality homework writing services. All you need to do is provide us with all the necessary requirements of the paper and wait for quality results.

Essays

Essay Writing Services

At My Homework Writers, we have highly qualified academic gurus who will offer great assistance towards completing your essays. Our homework writing service providers are well-versed with all the aspects of developing high-quality and relevant essays.

Admissions

Admission and Business Papers

With Myhomeworkwriters.com, we will help you secure a position at your desired institution. Our essay writing services include the crafting of admissions papers. We will still help you climb your career ladder by helping you write the official papers that will help you secure a job. We will guide you on how to write an outstanding portfolio or resume.

Editing

Editing and Proofreading

Myhomeworkwriters.com has a professional editorial team that will help you organize your paper, paraphrase it, and eliminate any possible mistakes. Also, we will help you check on plagiarism to ensure that your final paper posses quality and originality.

Coursework

Technical papers

My Homework Writers harbors professional academic writers from diverse academic disciplines. As such, we can develop homework writing services in all academic areas. The simplicity or complexity of the paper does not affect the quality of homework writing services.