INF10025 Data Management and Analytic
Task ****1 – ****Pass and Credit
Overview
• See ****Canvas ****→ ****Assignments ****for ****due ****date
• To complete your learning portfolio, every few weeks we ask you to complete a set of tasks, document them (usually by asking for screen grabs), and submit them online.
• In this first task we focus on the basics. This includes using filters, queries, adding data, creating tables, more advanced queries, making relationships, indexes and forms.
• To create your submission download the files T 01P. DOCX ****and T 01C. DOCX ****from Canvas. Paste the required screen captures from the tasks below into these files.
• When complete, save the files as T01P.PDF and T01C.PDF.
• Submit ****both ****files ****into ****Canvas ****via ****Assignment
• Task ****1P and Task ****1C are individual ****tasks. You ****must ****not ****collaborate with ****any ****o ther ****students ****when ****completing these tasks.
Pay attention to ALL requirements.
If a column in ****Design Grid is not ****showing the ****full ****criteria ****or ****shows ****### ****in ****the ****Table ****or ****Data view, ****make sure you widen the column ****( same way as in ****Excel).
Pass Tasks
Completion Criteria: For the ****Pass Task to be marked “Complete” ****nine ****(9) sub-tasks ****must ****be ****marked ****“Correct”.
Pass ****1a
In Microsoft Access, data can be queried using the following methods:
• View a table ****in Datasheet ****View ****mode and use Filters
• Create a query, specify a table(s) and use the Query Design screen to search for data
In this unit, we do not want to use the first method that uses Filters. We want you to create Queries.
• What advantages does the Query me thod have over using filters?
List at least three advantages.
Pass ****1b
• Download the file named moviedb.accdb from Canvas.Open the file using Microsoft Access.
Important: ****before you begin, you must rename the ALL tables so that the table names end with the final 4 digits of your student ID.
E.g. if your id is 100234567 then the name of the MOVIE table must be MOVIE 4567, ACTOR becomes ACTOR4567, CASTING becomes CASTING4567, etc.
• Create a single ****Query that displays the following:
• The MOVIENO, TITLE, RATINGCODE, RELYEAR, and RUNTIME of all movies released in the year
. The list must appear in Descending RUNTIME sequence
• Save the query. Call it T 1 PB _4567 ****(where 4567 is the final 4 digits of your student ID.)
The next activity 代 写INF10025 Data Management and Analytic requires you to screen capture part of the screen.
Use a screen capture tools (such as the Snipp ing Tool ****in Windows Accessories) to capture only the necessary part of the screen.
Please avoid using the PrintSc reen ****key on your keyboard to capture the entire screen. This method often captures such a large image that when pasted into a document, the results are often too miniscule to be read by your tutor.
• Capture the Quer y ****Design Grid including the Table area (this is the screen where you specify criteria etc).
Paste ****the screen capture in the appropriate position in T 01P. DOCX
Note: The table name in the screen capture must ****contain the final 4 digits of your student ID.
• Screen capture the ****first ****five ****(5) rows ****that are displayed once this query is executed. Paste ****the screen capture in the appropriate position in T 01P. DOCX .
Note: The query name in the screen capture must contain the final 4 digits of your student ID.
Pass ****1c
• Create a single ****Query called T 1 PC _4567
• It must display the MOVIENO, TITLE RATINGCODE, RELYEAR, RUNTIME, and TMDB_SCORE for all movies that were made in the period 2002-2010 (inclusive), have a runtime less than 120 minutes, are rated "G" or "PG", and have a TMDB score greater than or equal to 6.9. The list must be in ascending Movieno sequence.
• Note: This is ONE single query that does all of the above. It does NOT involve multiple queries.
• Screen Capture the Query Design Grid including the Table ****area.
• Screen Capture all the rows that are displayed.
• Paste ****both ****screen ****captures ****in ****the ****appropriate ****position ****in ****T 01P. DOCX
Pass ****1d
• Create a single ****Query called T 1 PD _4567 (and not ****multiple queries)
• The query must display all fields for movies that meet all ****of these criteria
. The movie must ****have one of the phrases man ****or boy ****or miss ****or girl ****or ms ****within the
TITLE (E.g. Titles such as 'Man from Mars', 'Batgirl', 'I.T. Manager vs Zombie' would all be expected to appear).
. The movie must have fewer than 2700 TMDB votes. . Results sorted in Descending ****TMDB Vote order
• Make sure the design grid shows ALL ****criteria. If necessary, make a column in the query design grid wider to show the criteria in full (It is done same way as in Excel). Screen Capture the Query Design Grid including the Table ****area.
• Screen Capture all ****the rows that are displayed.
• Paste ****the ****screen ****captures ****in ****the ****appropriate ****position ****in ****T 01P. DOCX
Pass ****1e
• Create a new ****table ****named Member_4567 (where 4567 is the last four digits of your student id).
The table must include 4 columns: Membid, Surname, Givenname and Birthdate. Membid must be Numeric ****(not Autonumber) type.
Surname and Givenname are short ****text
datatypes. Birthdate is a date/time datatype. Membid must be the primary ****key.
• Add FIVE rows of data to this table
• The first 3 rows match the data below. The 4th row contains details of your favourite author ****or musician. You can use any id you wish. The 5th row must contain data about you that Includes your ****name & ****birthdate. Use your student ID as the memberID. (If your ID contains an "X" character, then omit the "X" from the member id.
• Screen Capture the table design screen (the screen that shows the field names and datatypes)
• Screen Capture all rows in the Member4567 table
• Paste ****the ****screen ****captures ****in ****the ****appropriate ****position ****in ****T 01P. DOCX
You should be able to complete up to here by the end of Week01. Remaining tasks require you to study the Lecture 2 materials.
Pass ****1f
• Create Relationships for all of the tables in the database.
• At the conclusion of this task, you should have a diagram similar to this:
• Take a screen capture of the diagram.
• Note: All table names in your diagram must contain the last 4 digits of your student ID.
• Paste ****the ****screen ****capture ****in ****the ****appropriate ****position ****in ****T 01P. DOCX
Pass ****1g
• Create a Query that uses the MOVIE, RATING and COLOURTYPE tables
• The query must display the MOVIENO, TITLE, RATINGCODE, RATING DESCRIPTION, and
COLOUR_NAME of all movies beginning with the letter “G”. The list must appear in Ascending ****MOVIENO SEQUENCE
• Save the query. Call it T 1 PG _4567
• Screen capture the Query ****Design ****Grid .
• Screen capture all the ****rows ****that are ****displayed ****once this query is executed.
• Paste ****the ****screen ****captures ****in ****the ****appropriate ****position ****in ****T 01 P. DOCX
Pass ****1h
• Create a Query T 1 PH _4567
• This Query requires Totals to be included.
• The query must display the actor no, actor fullname, and the count ****of CastId. The list must be in Descending Count sequence.
• Screen Capture th e Query ****Grid ****Design and ****the ****first ****15 ****rows ****(if there ****are ****that ****many) ****that are displayed ****and ****paste ****into ****T 01P. DOCX
Pass ****1i
• Create a single ****Query called T 1 Pi _4567 ****(and not ****multiple queries)
• The query must display the total number rows for each gender value that appear in the Actor table. E.g. Male 454
Female 231
• Screen Capture the Query ****Grid ****Design and all ****of ****t he ****rows ****that are ****displayed and ****paste ****into
T01P. DOCX
•
Pass ****1j
• Modify the Access Actor table.
• Create an Index on the following field Surname ****(with duplicates ****allowed)
• Screen Capture the ****Properties fo r this ****field after the ****index ****has ****been ****created ****and ****paste ****into
T01P. DOCX
Pass ****1k
Add a ****new row to the Actor table. Use your own details such as name, gender, country of birth etc. Use your student ****ID ****as the ActorNo. (If your ID contains an "X" character, then omit the "X" from the actor no)
• Add ****a ****new ****row ****to ****the ****Movie ****table. Make up the details of about any movie (you may use a real movie or make up your own, e.g. ‘The Zombies of Glenferrie Station’ . Use a 7 ****digit ****movie ****no. so that you can be sure it does not clash with an existing Movie No.
• Add at least 3 ****new rows ****to ****the ****Casting ****table. The first new row must specify that the Actor you created (above) has been cast in the Movie that you created. (e.g. Allocate yourself to star in the movie).
Note: **The CastID ******will automatically be alloca ted to the new row **(because **the **data **type for **CastId **is **Autonumber ). Simply enter an **Actor No and the CastID field will be **populated by the next number in **sequence.
The second / third new rows ****(plus any others that you may want to create) must cast any other
existing ****actor ****from the Actor table into your new movie. E.g. You may want to cast Tom Hanks to be in your movie with you.
• Finally create a ****query ****that lists the CastID, Movie No, Title, Relyear, RatingCode, Actor No, Actor FullName, Date of Birth and Birth Country from the Movie, Actor and Casting tables of the Movie that you created above.
• Screen capture all of the ****rows that are ****displayed ****once this query is executed and query grid.
• Paste ****the ****screen ****captures ****in ****the ****appropriate ****section ****in ****T 01P. DOCX
You should be able to complete up to here by the end of Week 2.
Credit Tasks
Completion Criteria: For the Cr edit Task to be marked “Complete” ****Eight ****(8) ****sub-tasks ****must ****be ****marked ****“Correct”
Credit ****1a
• Download ****the ****Customer ****database ****named ****cust . accdb ****from ****Canvas .
• Open the file using Microsoft Access.
• Important: ****Before you begin, you must rename the CUSTOMER table so that it now contains the word CUSTOMER plus the final 4 digits of your student ID. E.g. If your id is 100234567 then the name must be CUSTOMER 4567.
• Create a single ****query named T 1 CA _4567. (and not ****multiple queries)
• The query must display the Customer ID, surname, gender, loyalty points, rating code and state for all records that meet all of these criteria:
• The State must be Tasmania
• The Loyalty Points must be greater than or equal to 7500
• The Gender must be M
• The Rating code must be 1 or 3
• Screen Capture the Query Design Grid.
• Screen Capture all rows displayed by this query.
• Paste ****the ****screen ****captures ****in ****the ****appropriate ****position ****in ****T 01C. DOCX
Credit ****1b
• Create a single ****query named T 1 CB _4567 ****(and not ****multiple queries)
• The query must display the Customer ID, surname, gender, loyalty pts, rating, state, Sales this Year and Sales Last Year for all records that meet these criteria:
• Sales this year must be greater than or equal to 560
• The Rating code must be 1
• The state must be Tasmania
• The loyalty points must be in the range 3000 to 5999
OR
• Sales last year must be less than 75
• The Rating code must be 2
• The state must be Victoria
• The loyalty points must be in the range 7500 to 8999
Note: This single query must handle ALL 8 dot ****points ****above
• Screen Capture the Query Design Grid.
• Screen Capture all rows displayed by this query.
• Paste ****the ****screen ****captures ****in ****the ****appropriate ****position ****in ****T 01C. DOCX
Credit ****1c
• Create a single query named T 1 CC _4567.
• The query must display the Customer ID, given name, surname, date of birth and postcode for all records that meet this criteria:
• The person must have date of birth within years 31st Dec 1999 to 31st Dec 2001
• How to use dates in Access is explained the second half of this article:
support.office.com/enus/articl… 46eb-43dd-8689-5fc961f21762
• Screen Capture the Query Design Grid.
• Screen Capture all rows displayed by this query.
• Paste ****the ****screen ****captures ****in ****the ****appropriate ****position ****in ****T 01C. DOCX
Credit ****1d
• Add yourself as a customer into this table.
• Use your student id as the customer id (remove the ‘X’ if your student ID end with an ‘X’).
• Generate any values you wish for the other fields.
• Screen Capture a few rows from this table that include your customer details.
• Paste ****the ****screen ****captures ****in ****the ****appropriate ****position ****in ****T 01C. DOCX
Credit ****1e
• Now download the database named product_sales.accdb and rename all tables by adding last 4 digits of your student ID at the end of each table name.
• This database contains sales of 80 products over a 5-year period. There are over 350,000 sales transactions.
• There are no details about individual customer who were involved in the sales. The database has only recorded the gender and birthdate of the people who purchased products.
• Each product belongs to a product type.
• Each sales transaction belongs to a location in Australia.
• Using the Database Tools menu, create Relationships ****between the tables.
• The foreign ****keys ****are:
• POSTCODE in the TRANS table
• PRODID in the TRANS table
• PRODTYPE in the PRODUCT table
• Take a screen capture of the diagram.
• Paste ****the ****screen ****capture ****in ****the ****appropriate ****position ****in ****the ****document ****named ****T 01C. DOCX
Credit ****1f
• Create a single query named T 1 CF _4567 ****that displays these columns:
• TranID, ProdID, Prodname, TypeDescription, Qty, TranDate in Ascending TransID sequence
• Only shows records that meet all of these criteria
• Qty greater than or equal to 10
• TranDate = 01/03/2011
• Screen Capture the Query ****Grid ****Design ****and ****all ****the ****rows ****that ****are ****displayed and ****paste ****into
document ****T 01C. DOCX .
Credit ****1g
• Create a single query named T 1 CG _4567 that:
• Displays these columns: TranDate, TranID, Prodname, SalePrice, Qty, Suburb, CustBirthYear
• Is in Ascending TransDate sequence
• Only show records that meet a ll these criteria
• Prodname is “Highway" or "Touchtrax "
• Suburb is Balwyn
• Screen Capture the Query Grid ****Design and ****all ****the ****rows ****that are ****displayed and ****paste ****into
document ****T 01C. DOCX .
Credit ****1h
• Part ****1 ****The product Touchtrax has been sold over a number of years. Answer these questions.
How many times has this product been sold? Show the ****design ****grid ****and the ****Result ****of ****the ****query you created to answer this question
Part 2 How many different prices has this product been sold for? Show the ****desig n ****grid ****and the ****Result of the query you created to answer this question.
Has the price of this product been increasing or decreasing over the years?
• Add ****Text ****in ****the ****appropriate ****position ****in ****the ****document ****T 01C. DOCX .
Credit ****1i
• Create a single ****query named T 1 Ci _4567 that:
• Displays the sum of the qty sold for each weekday ( include Sat , Sun as weekday)
• The list must be in Ascending ****qty sequence
Sunday 425
Friday 690…
• Screen Capture the Query ****Grid ****Design and all ****the ****rows ****that are ****displayed ****and ****paste ****into
document ****T 01C. DOCX .
Credit ****1j
• Select ‘Create Form’ from the Access ribbon menu.
• Access will automatically create a Columnar Form. for the Product table using the Form. Wizard. It will look similar to this:
• Modify ****the ****Form ****in the design view. Make these simple changes:
o Change the text in the Form Header so that it includes your ****name a nd student ID
o Use the Insert Image option from the Ribbon
Menu to add a small image to the Form Header
o Change the prompts on the left side of the
Detail Section to match the text in the example below (e.g. ProdId becomes Product Id).
o You may make any other minor alterations (such as colours, fonts, move text boxes, etc.) if you wish.
• Screen Capture the ****Form ****Design View ****(the screen with gridlines ) ****and ****paste ****into ****document
T01C. DOCX .
WX：codinghelp
