Create relationships between the tables

Assignment Help Database Management System
Reference no: EM131026088

Assignment

Database Management System (DBMS)

In completing this assignment, you are expected to use available resources such as the practical activities in the study modules, the Course Study Desk - especially the Discussion Forums (click the Study Desk link on UConnect - <https://uconnect.usq.edu.au>), as well as exploring and experimenting on your own.

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 2007/2010/2013, 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.

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

Table Specifications



Table:

tblCustomers

Fields:

Cust ID (Primary Key), Title, Family Name, Given Names, Date of Birth, Address, City, State, Postcode, Freight Distance (Km), Mobile Number, eMail Address, Mailing List.



Table:

tblItems

Fields:

Item ID (Primary Key), Type, Description, Colour, Size, Freight Weight (Kg), Supplier ID, In Stock, On Order, Cost Price (NL), Cost Price (KY).


Table:

tblSuppliers

Fields:

Supplier ID (Primary Key), Supplier Name, Address, City, State, Postcode, Phone Number, Recommended MarkUp

Table:

tblOrders

Fields:

Cust ID, Item ID, Order Date (Primary Key), Dispatch Date, Order Qty

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

Dr Dorothy Little has provided to us a partial list of her customers, stock items, and suppliers and a partial list of her postal orders for the fourth quarter 2015 (Oct - Dec) to use as test data in four comma delimited text files which can be found on your firm's Intranet (CIS5100 Study Desk).

Mrs Hyacinth Macaw, 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 2007/2010/2013 Database 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 2007/2010/2013 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 File wizard 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 field option on the first page of the wizard - then click Next.

4) Select the Tab option on the second page of the wizard and turn on the First Row Contains Field Names check 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.

-Data Type - 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 Qty must 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 2010 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 Key option 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.

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 Type from the following:
o Text, Number, Date/Time, Currency, Yes/No, Hyperlink.
-Add an appropriate Field Descriptions to 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: NL is the country code for Netherlands - should be formatted in Euros; KY is the country code for Cayman Islands - 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 Rule and Validation Text - include appropriate Validation Rules for each of the following data Fields: Title; State; and Item Type. This ensures that only limited value options can be entered for these data Fields.

Task 3: Set Primary Keys

Set the Primary Keys for the four (4) Tables as per the following table specifications in Microsoft Access using Table Design View:

Table Name

Primary Key(s)

tblCustomers

CustID

tblItems

ItemID

tblSuppliers

SupplierID

tblOrders

CustID, ItemID, OrderDate

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

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

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

-Query 1: Unfulfilled Light Christmas Dog Gift Orders:

Dr Little would like to see how many Dog Gift orders were ordered for Christmas but were unfulfilled due to the items being out of stock. In the past the majority of Dog Gift Christmas orders were placed in November and were for items less than 2.5 kilograms.
Create a Select Query that will display the full Customer's Name (including Title, Given Names and Family Name), Full Item Details (including Item Type, Item Description, Item Colour, Item Size and Freight Weight), Order Date and Order Qty for all Dog Gifts orders, placed in the month of November (using the BETWEEN criteria tool), weighing less than to 2.5 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 Fields and ensure that you only display the required Fields.

-Query 2: Eastern Australian Animal Supplies Orders from a Supplier:

Dr Little would like to have a query that the business can reuse with a manually entered Supplier Name, to see how many customers living in Eastern Australia (Queensland, New South or Victoria) have purchased animal supplies related items obtained from the Supplier as specified.
Create a Select Query that will display the full Customer's Name (including Title, Given Names and Family Name), and Full Customer's Address for customers who live in Queensland, New South or Victoria (using the IN criteria tools), and the Item Type, Item Description, Item Colour, Item Size, Dispatch Date and Order Qty for all Customers who have purchased an Item Types containing the word "supplies" (using the LIKE criteria tool) obtained from a user specified Supplier.

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

-Query 3: Import Duty The Cayman Islands (KY) Cost Price Increase:

A new Australian import duty of 8.125% has been added for all products being imported from the Cayman Islands (KY) and Dr Little has asked you to develop a query that will update the Cost Price from the Cayman Islands of 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 Query that will raise the the Cayman Islands (KY) Cost Price for all items on order that are not in stock by 8.125%. 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.

-Create an Update Query that will increase the Cost Price (KY) in tblItems by 8.125% for all items currently not in stock but 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 Little would like to have a single query that the business can display within a form (Task 6: Form 1) to see the customers who are:
i) Living in New South Wales, have had items dispatched late (between 10th December and today) and where the item was ordered before the 30st November and the item is in stock

Or

ii) Living in South Australia, have had items dispatched late (between 12th December and today) and where the item was ordered before the 1st December and the item is in stock

Create a Select Query that 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 (including Item Type, Item Description, Freight Weight and Supplier Name), OrderDate, Dispatch Date and OrderQty for those customers

i) living in New South Wales, who have placed Orders before the 30th November 2015 and where these orders were only dispatched between the 10th of December 2015 and today (using the BETWEEN criteria tool and the DATE() function), and where the business currently has that item in stock

ii) OR living in South Australia (using the OR criteria row) , who have placed Orders before the 1st December 2015 but where these orders were only dispatched between the 12th of December 2015 and 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.

-Query 5: Invoice Query with Calculated Fields:

Dr Little would like to have a query that the business can use to produce customer invoices (Task 7) to see how many customers living in Queensland ordered a Dog related product in the months of October and November where the item was in stock. This query should also calculate the selling price (based on goods purchased from the Netherlands) and order total, both of which will be needed on the customer invoices.

Create a Select Query that will display the CustID, Full Customer's Name (including Title, Given Names and Family Name), Full Customer Address, ItemID, Full Item Details (including Item 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 Queensland who ordered a product where the Item Description containing the word "Dog" (using the LIKE criteria tool), who have placed orders in the months of October and November (using the BETWEEN criteria tool), and where the business currently has that item in stock.

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 Appendix 2 - 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 Type Fields
-Check box - demonstrate for MailingList Field
-Buttons - demonstrate the following four (4) Navigation Buttons (First Record, Previous Record, Next Record, Last Record).

-Form 2: Supplier Items Display Form with Subform:
Create a professional user-friendly Display Form (using either the Columnar or Justified Form Wizard Templates) and a sub-form that will allow users to view each supplier record's details stored in the Database Management System (DBMS).
The main form should contain All Supplier 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 Field width on the subform so that all columns fit on the main form using the Property Sheet - Format width option.

Task 7: Create an Invoice Report

Dr Little has provided you with the following sample Invoice. The client requires you to develop a similar invoice for their business.

Using all Fields from Task 5 Query 5, create in the Report Wizard (using the Block Report Wizard Template) a single Report called rptCustomerInvoice that produces a professional looking invoice for the business.

The invoice must have the following attributes:
-The report must contain at least all the elements displayed in the sample invoice (above).
-The report must be grouped by CustID.
-Each Customer's invoice must be on a separate page.

-Each Customer's invoice must have the Customer's name merged into a single Field including Title, Given Names and Family Name by modifying one of the Fields to include:
[Title] & " " & [GivenNames] & " " & [FamilyName]

-Each Customer's invoice must have part of the Customer's address merged into a single Field including City, State and Postcode by modifying one of the Fields to include:
[City] & " " & [State] & " " & [Postcode]
-Each Customer's invoice must have Today's Date in the Page Header
-Each Customer's invoice must have an Invoice Total for each customer
-Each Customer's invoice must not have any extraneous data including Grand Totals, Page Numbering, nor Summary Information.
-Each Customer's order must have the Order Date in the Details Section
-Each Image on the invoice must be inserted using the Image Control and must have the following properties:

-Each Customer's invoice must have the Customer's name merged into a single Field including Title, Given Names and Family Name by modifying one of the Fields to include:
[Title] & " " & [GivenNames] & " " & [FamilyName]

-Each Customer's invoice must have part of the Customer's address merged into a single Field including City, State and Postcode by modifying one of the Fields to include:
[City] & " " & [State] & " " & [Postcode]
-Each Customer's invoice must have Today's Date in the Page Header
-Each Customer's invoice must have an Invoice Total for each customer
-Each Customer's invoice must not have any extraneous data including Grand Totals, Page Numbering, nor Summary Information.
-Each Customer's order must have the Order Date in the Details Section
-Each Image on the invoice must be inserted using the Image Control and must have the following properties:

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.

Properties

Options

Picture Type:

Embedded

Control Source:

Blank

Picture:

[File Name]

-Summary of Presentation

Task 8: End of Project Presentation to the Client

Mrs Hyacinth Macaw has asked you to provide a professionally developed business presentation to the client, Dr Dorothy Little, outlining the process that was taken in the development of this Database Management System. Create a PowerPoint 2007/2010/2013 Presentation 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 appendix 1 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.

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.

Reference no: EM131026088

Questions Cloud

How many of each type of seat are there on the plane : An airplane has a total of 152 seats. The number of coach-class seats is 5 more than six times the number of first-class seats. How many of each type of seat are there on the plane?
What is shadow cost of equity : Shadow corp. has no debt but can borrow at 8%. the firm's WACC is currently 11% and the tax rate is 35%. i. What is shadow's cost of equity? ii. If the firm converts to 25% debt, what will its cost of equity be?
What is the after-tax salvage value : Your firm needs a computerized machine tool lathe which costs $60,000 and requires $13,000 in maintenance for each year of its 3-year life. After three years, this machine will be replaced. The machine falls into the MACRS 3-year class life category...
Determine the speed and speed regulation : The armature resistance is 2Ω. Determine the speed and speed regulation if a load demanding a torque of 50 Nm is connected to the motor. Assume that the losses consist solely of heating of RA and frictional loss torque that is independent of speed..
Create relationships between the tables : Explain why a Database Management System is the appropriate tool for this project - explain why this Database Management System was developed as a Relational Database.
Ending inventory by the conventional : Markdowns 9,100, Beginning inventory (at cost) 43,720, Markdown cancellations 3,200, Beginning inventory (at sales price) 57,700, Freight on purchases 2,700. Compute the ending inventory by the conventional retail inventory method.
Bale electric company is a small rapidly growing : On January 1, a one-off payment of $175,000 is required to purchase new equipment. No depreciation is required to be calculated for this new equipment.
How does porter''s competitive forces model : How does Porter's competitive forces model help companies develop competitive strategies using information systems?Define Porter's competitive forces model and explain how it works.
Management usually adapts certain values : A company's culture evolves from its core values and its ethical standards. A company's commitment to certain values and belief in proper behavior is the basis upon which a company operates. Management usually adapts certain values and ethical standa..

Reviews

Write a Review

 

Database Management System Questions & Answers

  Create the class diagram and write the pseudocode

What is the screen output of the following code segment? Explain the reasoning behind your answer.

  Create the entity-relationship diagram for the doctors

Given the following business rules, create the Entity-relationship Diagram:

  Dimensions and attributes in a star schema model

Explain the use of facts, dimensions and attributes in a star schema model. Explain the use of facts, dimensions and attributes in a star schema model.

  Create database for easydrive school of motoring

Create mission statements and mission objectives for database systems explained in the following case study EasyDrive School of Motoring. Also in mission objectives include 10 reports that the database system must support

  Convert excel files into an access database

The Morris Arboretum tracks donors to their organization in Microsoft Excel. Access for future Arboretum records. In the following project, you convert Excel files into an Access database for Morris Arboretum

  Implement direct-address table keys of stored elements

Suggest how to implement direct-address table in which keys of stored elements don't require to be distinct and elements can have satellite data.

  Practice on topic of normalization

Practice on topic of Normalization

  Create a role for user to complete imports & exports of data

Use the DTS utility to Import the data from the text file into your SQL Server database. Show screen shots of the import steps and provide a screen shot of the table rows in the Enterprise Manager once the data is imported.

  Build a database

Build a database and produce a query report that shows 10 fields as in Table iamge using Microsoft Access

  Consider a relation stored as a randomly ordered

Consider a relation stored as a randomly ordered ?le for which the only index is an unclustered index on a ?eld called sal. If you want to retrieve all records with sal > 20, is using the index always the best alternative? Explain.

  Create a data flow diagram

Create a Data Flow Diagram (DFD) relating the tables of your database schema through the use of graphical tools in Microsoft Visio or an open source alternative such as Dia. Note: The graphically depicted solution is not included in the required page..

  1 design an relational model rm model of a database db that

1. design an relational model rm model of a database db that will be useful for each of the chosen enterprises. hint

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