Create an end of project presentation to the client

Assignment Help Database Management System
Reference no: EM131165140

Assignment 1 Database Management System (DBMS)

Applicable course objective:

- demonstrate problem-solving skills by identifying and resolving issues relating to information systems and their components, and proficiently utilise different types of information systems software (especially gaining proficiency in utilising databases, spreadsheets, and presentation applications).

- demonstrate written communication skills by understanding basic information communication and technology (ICT) terminology for effective communication and applying it within a business environment.

Applicable graduate qualities and skills gained from this assessment instrument:

- Problem Solving (Skill U2)

- Written & Oral Communication (Skill U4)

This assignment is quite complex, and exposes you to many different components in Microsoft Access(any version from Access 2007 to current), some or all of which might be very unfamiliar to you. The assignment description provides some explanation of how to use these components, but you are also expected to use available resources such as Online Help, the Beskeen et al text, the Practical Skills sections at the end of each Module in the Study Materials, the SAM online tutorial activities and the course discussion forums, as well as exploring and experimenting on your own.

Preamble

Dr Nikola Teslaretired in 2012 after a long career in Logistics Management.To keep busy and to fulfil a lifelong dream, Dr Teslastarted a Steampunk Merchandise mail order business called the "Ye OldeSteampunk Emporium" and began selling various types of imported Steampunk merchandise such as ladies' and gentlemen's clothing, ladies' and gentlemen's boots, headwear,goggles and other assorted Steampunk related paraphernalia to customers via mail order around Australia. These types of Steampunk merchandise have become very popular, especially with the development ofsteampunk games (e.g. Bioshock II), steampunk graphic novels (e.g. League of Extraordinary Gentlemen), and even steampunk movies (e.g. Sherlock Holmes, Hugo, the Golden Compass, Sucker Punch and the Three Musketeers) and TV shows (e.g. Warehouse 13) have brought the phenomenon into the public's attention;and Dr Tesla'business has experienced a rapid growth in their mail orders. When Dr Teslafirst retired, the business bought a personal computer to help manage the books and finances.

Ye Olde Steampunk Emporiumis located at Shop 7, 1EdisonBoulevard, Toowoomba (about 100 km west of Brisbane).Dr Teslacan be contacted via phone on (07) 4632 1856; fax on (07) 4632 1943; or email on enquiries@steampunk_emporium.com.au.

Dr Tesla is extremely impressed with our computing firm "Einstein Technologies" and the business has contracted our firm to assist them in setting up their various computer-based information systems.

The first computer-based information system that Dr Teslais interested in is a Database Management System (DBMS) utilising Microsoft Access (any version from Access 2007 to current). The DBMS will be used to store all of the business's customer details, information about the stock items that the business sells, the suppliers that the business uses and all postal order transactions for the business. It will allow Dr Teslato run a number of queries on the data which the business has specified below and they wish to be able to print out an invoice for each customer at the end of each month.

All phases in this project must be developed with professionalism and user-friendliness in mind.

Your firm's Systems Analyst has developed a database structure that has been determined to be suitable to create a database to store Dr Tesla's customer, item, supplier and order data for the business, which has been provided as follows:

Dr Tesla has provided to us a partial list of the business's customers, stock items, and suppliers and a partial list of herpostal orders for the second quarter 2016(Apr - Jun) to use as test data in four comma delimited text files which can be found on your firm's Intranet (CIS5100 Study Desk).

MrsAda Lovelace, your manager, has set up the following tasks for you to complete for this phase of the project:

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. Create Data and Update Queries
6. Create Data Display Forms
7. Create an Invoice Report
8. Create an End of Project Presentation to the Client

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_CIS5100_assign1.accdb).

Import the four sets of data from your firm's Intranet (CIS5100 Study Desk).

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

1) On the External Data tab, in the Import & Link group, click the Text File data source icon.

2) In the Get External Data - Text Filewizard popup,browse to 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.

3) Select the Delimited - Characters such as commas or tab separate each fieldoption on the first page of the wizard - then click Next.

4) Select the Taboption on the second page of the wizard and turn on the First Row Contains Field Namescheck box - then click Next.

5) Set the following for each field on the third page of the wizard:
- Field Name- keep the field name as it appears when imported

Note: Changing Field Names rather than using Captions will result in loss of marks.

- DataType-giving each an appropriate data type (students will be marked on their logical choice of data types, based on the sample data provided).

Note: Numbers that will not be used for calculations should be set as Text. Numbers such as Freight Weight and Order Qtymust be carefully and logically set to avoid loss of data (numbers without decimal places should be set to be either binary, integer or long integer; and numbers with decimal places should be set to be single or double).

See the Beskeen et al textbook Access 2013 Unit E for more details on setting appropriate Data Types, particularly Table E-3: Common Number field properties for logical Data Types.

- When you are sure that each field has the appropriate data type, click Next.

6) Select No Primary Keyoption on the forth page of the wizard - these will be set in Task 2 - then click Next.

7) Name the table tblCustomers on the final page of the wizard and click Finish.

Repeat the above 7 steps for the rest of the text files naming each Database Table as follows:

Note:Table names must conform to theLeszynski Naming Convention (LNC) as per the Naming Convention for Access Database Objects document in the Practical Appendixesfound

Task 2: Modify Table Design

Modify the four imported tables' structures using the given Table Specifications,from the section above,in Microsoft Access using Table

Design View:

- Use the above table specifications, checking that you have the correct table names and field names.
- Check that all fields have the appropriate Data Typefrom the following:
o Text, Number, Date/Time, Currency, Yes/No, Hyperlink.
- Add an appropriate Field Descriptionsto ALL data field with a well-structured description, eg. for CustID an appropriate description would be:

Customer Identification | Data Type: Short Text: 3 Characters

- Ensure that you also set properties for each field where appropriate. You MUST set the following:
o Field Size- set appropriate logical fields sizes for all Text and Number data types (numbers without decimal places should be set to be either binary, integer or long integer; and numbers with decimal places should be set to be single or double)
o Caption- set an appropriate English caption for all fields where the field name is not in English (eg. Customer ID for CustID)
o Format- set an appropriate format for all: Yes/No and Date data fields;Text data fields that should be stored in Upper-Case (State);Hyperlinks data fields that should be stored in lower-case (EmailAddress); and numbers that contain currency or percentages
Note:LU is the country code for Luxembourg- should be formatted in Euros; KY is the country code for Tuvalu- should be formatted in Dollars.
o Decimals- set an appropriate number of decimal places for any single or double numbers
o Default Value- set an appropriate default value for all Yes / No data Fields
o Required- set any data Fields that must contain data to Required and leave any Fields where data is optional as unchecked.
o Validation Ruleand Validation Text- include appropriate Validation Rules for each of the following data Fields:Title; State; and Item

Type. This ensures that onlylimited value options can be entered for these data Fields.

Task 3: Set Primary Keys

Set the Primary Keysfor the four (4) Tables as per the following table specificationsin Microsoft Access using Table Design View:

Note: The primary key of the Orders table is a combined multiple-field key. It has three fields in one key.

Task 4: Create Relationships between the Tables

Create the following relationships between the four (4) tables:
- tblCustomers to tblOrders
- tblItems to tblOrders
- tblSuppliers to tblItems
Check that the correct table names and field names are listed in the ‘Edit Relationships' screen.
Ensure that you ENFORCE referential integrity, but do not set Cascading Updates or Cascading Deletes.
You should check that the computer has identified correctly the 1 to ∞ relationships between the four (4) tables.
Note:This must be done after you have established the primary keys
See the Beskeen et al textbook Access 2013 Unit E and the YouTube videos on the StudyDesk for more details on creating relationships

Task 5: Create Data and Update Queries

Create the following Five (5) queries using the Query Design View. Use the names given for each query to avoid confusion for the client (make sure you submit them in the order given below).

Note: Not all queries will require the inclusion of all four tables; you must decide which tables are appropriate for the generation of each query's output. Only tables needed should be included in the query design as extraneous tables could cause problems with the query results.

Note: Query names must conform to the Leszynski Naming Convention (LNC) as per the Naming Convention for Access Database Objects document in the Practical Appendixes found on the Study Schedule and Module Materials - including the Query Numberand Two Meaningful Words to describe the query eg. qry1XxxxxxXxxxxx.

See the Beskeen et al textbook Access 2013 Unit B, Access 2013 Unit F and Access 2013 Unit K for more details on creating queries

- Query 1: Unfulfilled Steampunk HatsMother's Day Orders:

Dr Teslawould like to see how many Steampunk style hatsorders were ordered for Mother's Day but were unfulfilled due to the items being out of stock. In the past the majority of Mother's Dayorders were placed in Apriland were for items less than5kilograms.

Create a Select Query that will display the full Customer'sName (including Title, Given Names and Family Name), Full Item Details (includingItem Type, Item Description, Item Colour, Item Size and Freight Weight), Order Date and Order Qty for all Headwearorders,placed in the month of April (using the BETWEENcriteria tool), weighing lessthan to5 Kg, and that are currently on order but not in stock.

Display the results sorted by Order Date, check that have you included all necessary Fieldsand ensure that you only display the required Fields.

Note: This Query will display 9rows of data if done correctly.

- Query 2: Eastern Australian Ladies relatedOrders from a Supplier:

Dr Teslawould like to have a query that the businesscan reuse with a manually entered Supplier Name, to see how many customers living in Eastern Australia (Queensland, New SouthWales or Victoria) have purchased Ladies related items obtained from the Supplier asspecified.

Create a Select Querythat will display the full Customer'sName (including Title, Given Names and Family Name), andFull Customer's Address for customers who live in Queensland, New South Wales or Victoria(using the IN criteria tools), and the Item Type, Item Description, Item Colour, Item Size, Dispatch Date and OrderQty for all Customerswho have purchased anItem Types containing the word "ladies" (using the LIKE criteria tool) obtained from a user specified Supplier.

Note: As no supplier name has been given, this query must use a Parameterin place of the supplier name.

Display the results sorted by Item Type and Item Description, check that have you included all necessary Fieldsand ensure that you only display the required Fields and test using Supplier'sName of"Charles Babbage Inc.".

- Query 3: Import Duty the Tuvalu (TV) Cost Price Increase:

A new Australian import duty of 7.25% has been added for all products being imported from the Tuvalu(TV) and Dr Tesla has asked you to develop a query that will update the Cost Price from the Tuvaluof all items that are currently on order. As it would be considered profiteering, it is important that the query does not update the Cost Price of items already in stock.

Create an Update Querythat will raise theTuvalu(TV) 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:
- In the List of Tables, copy the table called tblItems and paste it with the new name tblBackupItems.

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 Query that will increase the Cost Price (TV) in tblItemsby 7.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)

- Query 4: Late Dispatched Orders Form Query:

Dr Teslawould like to have a single query that the business can display within a form (Task 6: Form 1) to seethe customers who are:
i) Living in Victoria, have had items dispatched late (between5thJune and today) and where the item was ordered before the 31thMay and the item is in stock

Or

ii) Living in Tasmania, have had items dispatched late (between 31stMay and today) and where the item was ordered before the 25thMay and the item is in stock

Create a Select Querythat will display the CustID, Full Customer's Name (including Title, Given Names, and Family Name), Full Customer's Address, Date of Birth, eMail Address, MailingList, ItemID, Full Item Details (includingItem Type, Item Description, Freight Weight and Supplier Name), OrderDate, Dispatch Date and OrderQty for those customers

i) living in Victoria, who have placed Orders before the 31st May 2016and where these orders were onlydispatched between the 5thof June 2016and today (using the BETWEENcriteria tool and the DATE() function), and where the business currently has that item in stock

ii) OR living in Tasmania(using the OR criteria row) , who have placed Orders before the 25th May 2016 but where these orders were only dispatched between the 31stof May 2016and today (using the BETWEEN criteria tool and the DATE() function), and where the business currently has that item in stock

Display the results sorted by State and Order Date, check that have you included all necessary Fields and ensure that you only display the required Fields.

Note: This query willdisplay 15 rows of data if done correctly (11VIC and 4TAS).

- Query 5: Invoice Query with Calculated Fields:

Dr Teslawould like to have a query that the business can use to produce customer invoices (Task 7) to see how many customers living in Queenslandordered a Brass relatedproduct in the months of Apriland May where the item was in stock.This query should also calculate the selling price (based on goods purchased from the Luxembourg) and order total, both of which will be needed on the customer invoices.

Create a Select Querythat will display the CustID, Full Customer'sName (including Title, Given Names and Family Name), Full Customer Address, ItemID, Full Item Details (includingItem Type, Item Description, Item Colour, Item Size, Freight Weight and Supplier Name), OrderDate, and OrderQty, including two calculated Fields- Selling Price (AU) and Order Total (AU). Limit the query to only those customers living in Queenslandor New South Wales(using the IN criteria tools)who ordered aproduct where the Item Descriptioncontaining the word "Brass"(using the LIKE criteria tool), who have placed orders in the months of April and May (using the BETWEEN criteria tool), and where the business currently has that item in stock.
Note:

- SellingPrice(AU)- CostPrice (LU) x Exchange Ratex (1 + Recommended MarkUp)
- OrderTotal(AU)- SellingPrice(AU) x Ordered Qty

Task 6: Create Data Display Forms

Create the following two (2) data entry forms using the Form Wizard and Form Design.

Note: Form names must conform to the Leszynski Naming Convention (LNC) as per the Naming Convention for Access Database Objects document in the Practical Appendixes found on the Study Schedule and Module Materials - including the Form Number and Two Meaningful Words to describe the query eg. frm1XxxxxxXxxxxx.

- Form 1: Late Orders Form Display with Calculated Fields:

Using all Fields from Task 5 Query 4, create a professional user friendly Display Form(using either the Columnar or Justified Form Wizard Templates) that will allow users to view order details stored in the Database Management System (DBMS).

Include two calculated Textbox Fields that will calculate:

- Customer's Age in whole years (using the DATEDIFF function - see video Assignment 1 Extra Resources).
- Dispatch Delay in whole days (Dispatch Date - Order Date).

The form should demonstrate the following form controls:

- Image -an appropriate logo at the top of the form
- Label -an appropriate Form Title and appropriate Section Headings
- List Boxes or Combo Boxes - demonstrate for Title, State and Item TypeFields
- Check box - demonstrate for MailingListField
- Buttons - demonstrate the following four (4) Navigation Buttons (First Record, Previous Record, Next Record, Last Record).

- Form2: Supplier ItemsDisplay Form with Subform:

Create a professional user-friendly DisplayForm(using either the Columnar or Justified Form Wizard Templates) and a sub-formthat will allow users to vieweach supplier record's details stored in the Database Management System (DBMS).

The main form should contain AllSupplier details, the sub-form should contain a Datasheet view of the items sold by that particular Supplier(including at least Item Type, Item Description, and Freight Weight) - name the subform: frmItemsSub.

The form should demonstrate the following form controls:

- Image -an appropriate logo at the top of the form

- Label -an appropriate Form Title and SubForm Heading

- List Box or Combo Box - demonstrate for State Fields

- Buttons - demonstrate the following four (4) Navigation Buttons (First Record, Previous Record, Next Record, Last Record).
Note: Adjust the data Fieldwidth on the subform so that all columnsfit on the main form using the Property Sheet - Format width option.

Task 7: Create an Invoice Report

Dr Teslahas provided you with thefollowing sample Invoice. The client requires you to develop a similar invoice for their business.

Task 8: End of Project Presentation to the Client

Mrs Ada Lovelacehas asked you to provide a professionally developed business presentation to the client, Dr Tesla, outlining the process that was taken in the development of this Database Management System.

Create a PowerPoint2007/2010/2013Presentation and name it ‘[lastname] [initial] _ [student number] _ [course code] _ [assignment number' (eg. genrichr_0050051005_CIS5100_assign1.pptx).

Make sure that your professional developed business presentation is prepared so that it conforms to the course StudyDesk presentation resources. These resources include, but are not limited to the Practical Appendixes found on the Study Schedule and Module Materials and various videos.

The professional business presentations set of slides should be structured as follows:

- Title - outlining the full Business Name, Project Description, Student Name and Student ID.
- List of Topics
- Presentation Content - the following are the topics the presentation must answer.

Each slide must include detailed speaker notes provided in the notes section of the slide - the detailed speaker notes must provide a full answer to each of the following questions and be written so that another person could present this presentation on your behalf to the client.

DO NOT PROVIDE THIS AS AN AUDIO RECORDING
o Define what a Database Management System is.
o Define what a Relational Database is.
o Explain why a Database Management System is the appropriate tool for this project.
o Explain why this Database Management System was developed as a Relational Database.
o Describe what a Database Query is and discuss briefly the five queries that were developed for this project.
o Describe what a Database Form is and discuss briefly the two forms that were developed for this project.
o Describe what a Database Report is and discuss briefly the report that was developed for this project.

Need it as per the guidelines

Reference no: EM131165140

Questions Cloud

Estimate the volume of the sample : estimate the volume of the sample when the water content is 12.7%.
Calculate the shrinkage limit of the clay : The L.L of a medium sensitive Swedish post-glacial clay is 56 and the PI is 28. At its natural water content, the void ratio is 1.01 while after shrinkage the minimum void ratio is 0.72. Assuming the density of dm soil solids is 2.72, calculate th..
Create an end of project presentation to the client : Demonstrate written communication skills by understanding basic information communication and technology (ICT) terminology for effective communication and applying it within a business environment
Distinguish between economic profit and accounting profit : 1. Respond to the following question in at least three well composed paragraphs: In your own words, and using research sources other than the textbook, distinguish between economic profit and accounting profit.
Determine the pi and li for each soil : The soils in Problem 2-33 have the following Atterberg limits and natural water contents. Determine the PI and LI for each soil and comment on their general activity.
How does the creator make her point in the comic strip : Which techniques does the creator of the comic strip use? How does the creator make his/her point in the comic strip? What do we learn about the characters and/or ourselves from this comic strip or political cartoon? If colors are available, what ..
Consumer preferences are represented by the utility function : Suppose a consumer’s preferences are represented by the utility function U(X,Y) = X*Y2. Therefore, • MUx = Y2 • MUy = 2XY Also, suppose the consumer has $180 to spend (M = $180), PY = 1, and that they spend all of their money on goods X and Y. Also, ..

Reviews

Write a Review

 

Database Management System Questions & Answers

  Create library database schema structure

The library System stores 3 type of informations: Users ( USID, USNAME, USMob, USEmail, USDept,USStartDate, NofBooks). Create the library database schema Structure.

  Create a view named customeraddresses

Write a SELECT statement that returns these columns from the CustomerAddresses view that you created in exercise 1: CustomerID, LastName, FirstName, BillLine1.

  Structure of a relational database

Outline the main differences between the structure of a relational database optimized for online transactions versus a data warehouse optimized for processing and summarizing large amounts of data

  Display the final contents of each object''s field.

Write a program the instantiates a RealEstateSalesperson and a Girlscout object.

  Part ause the prime minister database primeminister2013sql

part ause the prime minister database primeminister2013.sql available from the interact site. answer the following

  Create mock-up report to make the monthly claim

He wishes you to group data by insurance company number, with subtotals by company and grand totals for each numeric field.

  Process of creating the entity-relationship diagrams

Create documentation that explains the process of creating the entity-relationship diagrams in the change request relative to this project.

  Craete the database to be fault tolerant

Explain the thought process of DBA as they craete the database to be fault tolerant. What policies would you suggest to get this goal?

  Question 1 for each of the following tasks youll use the

question 1 for each of the following tasks youll use the xxxxxxxx database. you need only provide the query or command

  Percentage of elapsed time

Let us assume that a program takes 400 seconds of elapsed time to execute. Out of these 400 seconds, 320 seconds is the CPU time and the rest is I/O time. What percentage of elapsed time is spent on I/O operations?

  How sql used to retrieve information from a spatial database

What does a spatial database store? How does it store it? How is SQL used to retrieve information from a spatial database? Explain what observer functions are and their role in a spatial database. Provide examples.

  List the rows in each of the joined tables.

List the rows in each of the joined tables. Use the small database shown in Figure Q3.5 to illustrate the difference between a natural JOIN, an equiJOlN, and an outer JOIN.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd