[Informatics] Apply Your Knowledge

Wed Feb 28 20:08:16 EST 2018

Hi Matt

I've attached my solution for A1 Software Supplies. Note that there may be data slightly changed because I think there was a duplicate or incorrect data in one of the fields. Can't remember which, but I do recall there was a slightly fishy record somewhere in the sample data.

There's an excel spreadsheet that contains the UNF (not normalised table) and my 3NF normalised tables (along with a DSD). Technically I've moved postcodes into their own table to show a difference between 2NF and 3NF, but I am fine with people who would keep them in the same table (though I think they are at risk of creating anomalous data - e.g. Horsham 3400 for one record and Horsham 3402 for another through a typographical error).

One can show their students the difference between normalised and unnormalised data in terms of the number of records required. 768 records (blank or not, they are still stored) for unnormalised and 384 for normalised. A significant saving that should lead to more efficient and effective query writing.

Then once I created the 3NF database sample data in Excel, I created a sample normalised DSD (see the tab in Excel) which can then be created in Microsoft Access using Database Tools-Relationships.  This should be completed BEFORE data is added to the tables.  Data to the tables needs to be added in the order of the tables that are the ONE in the one-to-many relationships entered first.  The order that I updated data was: Postcodes, Software, Customers, Orders, Order Details.

The MS Access solutions are then provided to the queries (See attachment).  Here is the results for those interested:
qryOrders_WkEnding10April2016
Order ID:	Order Date:	Customer ID:	Family Name:	Given Name:	Delivery Address:	Suburb:	Postcode:	Phone No:	Software ID:	Product Name:		Price ($):	Quantity:	Total Price ($)
JE0611		4/04/2016	2958		Burns		Mia		P.O. Box 251		Horsham	3400	98235872	485090		Adobe Creative Suite	$435.00	3	$1,305.00
JE0611		4/04/2016	2958		Burns		Mia		P.O. Box 251		Horsham	3400	98235872	581653		Pinnacle Studio		$79.00		3	$237.00
QA5745	5/04/2016	2028		Livingston	Clayton		64/152 Ute Avenue	Williamstown	3016	98470245	684924		Adobe Audition		$229.00	6	$1,374.00
QA5745	5/04/2016	2028		Livingston	Clayton		64/152 Ute Avenue	Williamstown	3016	98470245	953726		Acronis True Image	$59.00		2	$118.00
PY7538		5/04/2016	5839		Parrish		Briar		6083 Vulputate Avenue	Footscray	3011	98247044	684924		Adobe Audition		$229.00	56	$12,824.00
PY7538		5/04/2016	5839		Parrish		Briar		6083 Vulputate Avenue	Footscray	3011	98247044	953726		Acronis True Image	$59.00		12	$708.00
EB9801		6/04/2016	6803		Hayes		Oprah		30 Sed Street		Hawthorn	3122	98949901	448484		Corel Designer		$149.00	9	$1,341.00
EB9801		6/04/2016	6803		Hayes		Oprah		30 Sed Street		Hawthorn	3122	98949901	485090		Adobe Creative Suite	$435.00	8	$3,480.00
IM8529		6/04/2016	6803		Hayes		Oprah		30 Sed Street		Hawthorn	3122	98949901	518116		MS Windows		$215.00	22	$4,730.00
IM8529		6/04/2016	6803		Hayes		Oprah		30 Sed Street		Hawthorn	3122	98949901	953726		Acronis True Image	$59.00		65	$3,835.00
IU2820		7/04/2016	2958		Burns		Mia		P.O. Box 251		Horsham	3400	98235872	448484		Corel Designer		$149.00	5	$745.00
US5970		10/04/2016	6803		Hayes		Oprah		30 Sed Street		Hawthorn	3122	98949901	334806		MS Office		$249.00	45	$11,205.00

qryAdobeAuditionPaid
Order Date:	Order ID:	Customer ID:	Family Name:	Delivery Address:	Suburb:		Postcode:	Phone No:	Quantity:	Total Cost ($)
14/02/2016	PS2357		1496		Sweet		7476 Nisi Street		Traralgon	3844		98024988	10		$2,290.00
5/04/2016	EK6196		2028		Livingston	64/152 Ute Avenue	Williamstown	3016		98470245	5		$1,145.00
5/04/2016	PY7538		5839		Parrish		6083 Vulputate Avenue	Footscray	3011		98247044	56		$12,824.00
5/04/2016	QA5745	2028		Livingston	64/152 Ute Avenue	Williamstown	3016		98470245	6		$1,374.00
21/04/2016	ZH3405		2958		Burns		P.O. Box 251		Horsham	3400		98235872	6		$1,374.00

Obviously NO ONE should be using this as their SAC for Year 12, due to the solutions being freely available.  But I do believe this task is a very worthwhile one for students to complete as a practice SAC.

Happy to also provide Pizzas on the Go if people need it (though the solution is pretty much completed throughout the chapter).

Lucas

-----Original Message-----
From: informatics [mailto:informatics-bounces at edulists.com.au] On Behalf Of Matt Olesen
Sent: Thursday, 1 March 2018 10:58 AM
To: informatics at edulists.com.au
Subject: Re: [Informatics] informatics Digest, Vol 26, Issue 6

Hi Everyone,

Obviously by my request I am new to this game (1st year IT) but wondering if there is anyone willing to send through an example of Chapter 1 Apply your Knowledge -specifically 2D and 2E with the Access query design?

Thanks in Anticipation.

Kind Regards, 

Matt Olesen.

IT Teacher
F-12 E-Learning Coordinator

-----Original Message-----
From: informatics [mailto:informatics-bounces at edulists.com.au] On Behalf Of mailto:informatics-request at edulists.com.au
Sent: Wednesday, 14 February 2018 4:00 AM
To: mailto:informatics at edulists.com.au
Subject: informatics Digest, Vol 26, Issue 6

Send informatics mailing list submissions to
	mailto:informatics at edulists.com.au

To subscribe or unsubscribe via the World Wide Web, visit
	http://edulists.com.au/mailman/listinfo/informatics
or, via email, send a message with subject or body 'help' to
	mailto:informatics-request at edulists.com.au

You can reach the person managing the list at
	mailto:informatics-owner at edulists.com.au

When replying, please edit your Subject line so it is more specific than "Re: Contents of informatics digest..."

Today's Topics:

   1. Cengage Informatics Text- (Alexakos, Zach Z)
   2. Re: Cengage Informatics Text- (Tony Crewe)

----------------------------------------------------------------------

Message: 1
Date: Tue, 13 Feb 2018 04:17:39 +0000
From: "Alexakos, Zach Z" <mailto:alexakos.zach.z at edumail.vic.gov.au>
To: Year 12 VCE Informatics Teachers' Mailing List
	<mailto:informatics at edulists.com.au>
Subject: [Informatics] Cengage Informatics Text-
Message-ID:
	<mailto:F1B3F5346D45F9459B462D93FB067AC69C49B253 at EDUMBX10.education.vic.gov.au>

Content-Type: text/plain; charset="utf-8"

Hi Everyone,

We are seeking  answers to the text book.  Primarily the apply your knowledge sections. Does anyone know about this please.

regards,
Zach Alexakos

IMPORTANT - This email and any attachments may be confidential. If received in error, please contact us and delete all copies. Before opening or using attachments check them for viruses and defects. Regardless of any loss, damage or consequence, whether caused by the negligence of the sender or not, resulting directly or indirectly from the use of any attached files our liability is limited to resupplying any affected attachments. Any representations or opinions expressed are those of the individual sender, and not necessarily those of the Department of Education and Training.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://edulists.com.au/pipermail/informatics/attachments/20180213/8d7d4f69/attachment-0001.html>

------------------------------

Message: 2
Date: Tue, 13 Feb 2018 04:24:33 +0000
From: Tony Crewe <mailto:TonyCrewe at caulfieldgs.vic.edu.au>
To: Year 12 VCE Informatics Teachers' Mailing List
	<mailto:informatics at edulists.com.au>
Subject: Re: [Informatics] Cengage Informatics Text-
Message-ID:
	<mailto:5155E0F8-1F50-43B3-B4F8-301A342616BE at caulfieldgs.vic.edu.au>
Content-Type: text/plain; charset="utf-8"

Zach,

Contact your Cengage supplier/booklister ... , they will give access to teacher area which provides solutions to TYK and AYK plus other resources.

Tony

On 13 Feb 2018, at 3:17 pm, Alexakos, Zach Z <mailto:alexakos.zach.z at edumail.vic.gov.au<mailto:alexakos.zach.z at edumail.vic.gov.au>> wrote:

Hi Everyone,

We are seeking  answers to the text book.  Primarily the apply your knowledge sections. Does anyone know about this please.

regards,
Zach Alexakos

IMPORTANT - This email and any attachments may be confidential. If received in error, please contact us and delete all copies. Before opening or using attachments check them for viruses and defects. Regardless of any loss, damage or consequence, whether caused by the negligence of the sender or not, resulting directly or indirectly from the use of any attached files our liability is limited to resupplying any affected attachments. Any representations or opinions expressed are those of the individual sender, and not necessarily those of the Department of Education and Training.

_______________________________________________
http://www.edulists.com.au - FAQ, resources, subscribe, unsubscribe VCE Informatics Mailing List kindly supported by http://www.vcaa.vic.edu.au/vce/studies/infotech/itapplications3-4.html - Victorian Curriculum and Assessment Authority <br> http://www.vitta.org.au  - VITTA Victorian Information Technology Teachers Association Inc <br> http://www.swinburne.edu.au/ict/schools - Swinburne University
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://edulists.com.au/pipermail/informatics/attachments/20180213/c9b607bd/attachment-0001.html>

------------------------------

Subject: Digest Footer

_______________________________________________
informatics mailing list
mailto:informatics at edulists.com.au
http://edulists.com.au/mailman/listinfo/informatics

------------------------------

End of informatics Digest, Vol 26, Issue 6
******************************************
_______________________________________________
http://www.edulists.com.au - FAQ, resources, subscribe, unsubscribe VCE Informatics Mailing List kindly supported by http://www.vcaa.vic.edu.au/vce/studies/infotech/itapplications3-4.html - Victorian Curriculum and Assessment Authority <br> http://www.vitta.org.au  - VITTA Victorian Information Technology Teachers Association Inc <br> http://www.swinburne.edu.au/ict/schools - Swinburne University

IMPORTANT - This email and any attachments may be confidential. If received in error, please contact us and delete all copies. Before opening or using attachments check them for viruses and defects. Regardless of any loss, damage or consequence, whether caused by the negligence of the sender or not, resulting directly or indirectly from the use of any attached files our liability is limited to resupplying any affected attachments. Any representations or opinions expressed are those of the individual sender, and not necessarily those of the Department of Education and Training.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: A1Software.accdb
Type: application/octet-stream
Size: 2359296 bytes
Desc: A1Software.accdb
URL: <http://edulists.com.au/pipermail/informatics/attachments/20180301/74293754/attachment-0001.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: U3O1 - A1 Software Supplies full spreadsheet.xlsx
Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Size: 23198 bytes
Desc: U3O1 - A1 Software Supplies full spreadsheet.xlsx
URL: <http://edulists.com.au/pipermail/informatics/attachments/20180301/74293754/attachment-0001.xlsx>