Mountain Music Shop

After a 30min trivia quiz on Access and VBA (and a break!) we're going to have a go at our next Database Design problem:

You and a few friends have just been employed by a local music shop that sells DVDs, CD’s, Videos and games. They’ve heard that you’ve got some experience with designing databases and are offering you all a pay rise if you can together come up with a design. The boss of the place has worked out that she wants the following information to be collected for each sale:

· LastName

· FirstName

· PostCode

· SalesDate

· Title

· MediaType

· Artist

· Price

· Quantity

Knowing that the first commandment of Access is:

“Thou shalt design normalized tables and understand thy fields and relationships before thou dost begin,”

you sit down to design your database schema together.

Hints:

· What primary tables will you need – tables that contain information that isn’t dependant on any other info in the database?

· When you’ve got a design, does your design allows a customer to purchase a number of different items in one sale? How can you further split thy database to allow this?