BTT10+-+Databases

According to Wikipedia, a database is: "a collection of records stored in a computer in a systematic way, so that a computer program can consult it to answer questions. The items retrieved in answer to queries become information that can be used to make decisions." Your personal information is contained in records in databases held by governments, hospitals, schools and more. Ever subscribe to a magazine? If so you can be sure your personal information has been used to target you as a possible customer for similar products. Let's get started building and manipulating a database.

= Activity #1: Tutorial =


 * [|Microsoft Access Tutorial]
 * If you're using Open Office Base as your software program, read this [|Database Tutorial] and proceed to the next activity. If using Microsoft Access or another program, see the available tutorials off the homepage.

t = Activity #2: Movie Database =

Go check out [|IMDb] which stands for "**I**nternet **M**ovie **D**ata**b**ase". Notice how you can search up content stored on their site using fields such as the actor's name, title of the movie, ratings, etc.. Now it's your turn: Create a simple database to store your movies. Save as Create a simple database to store your movies. Save as Movie DB. Follow these instructions (Using Microsoft Access)
 * Create text fields for Title, Male Star, Female Star and Year; create the text field Type (e.g. action, drama, etc.). You will eventually be creating a combo box to make a Value List. Also create a container field for the graphics representing the movie's dvd cover.
 * You must also //add at least one field of your own// that is appropriate.
 * Use the form wizard to create a form with all these fields (do not include the ID field as this will self-populate. Replace the type field with a combo box. Be sure to add in the types of movies (Horror, Comedy, etc.)
 * Next, Create entries for at least 20 records usin the form. Be sure to refresh after you are done addign these.
 * Use colours and add graphics to make the database form pleasing to users.
 * Create a report of all your records.
 * Once you have created your report, sorted it and given it a title. Save as moviedbreport and submit to Edmodo

= Activity #3: Used Something Store =

Here you can assume you are running your store selling a previously owned (used) product in a tiny spot in a local mall. The example is a CD store. This file would be saved as cd database. You must choose another product for your store such as computer games, sporting goods, sunglasses, purses, etc. The first step will be to select an item you are interested in to buy used and then sell in your store for a profit.

__Step 2:__ Create at least 11 fields similar to the following, allowing for differences based on your choice of product (the green fields will need to be changed!): Create fields for:
 * Title ... text
 * Artist ... text
 * Year ... number or text
 * Rating/Quality ... can be a number (1, 2, 3, etc.) or text (Poor, Average, Good, etc.)
 * Cost ... calculation field depending on the Rating field above; use the IF function (see below)
 * Retail Price ... calculation field, e.g. double the cost, Cost*2
 * Harmonized Sales Tax ... another calculation field, 13% percent of the Retail Price
 * Total Price ... calculation, adding the Retail Price and HST
 * CD Cover ... container field showing the picture of the cd
 * Payment method... use a Value List to create radio buttons (credit card, debit card, cash - you create these the same way as pop-up lists, just select radio buttons instead)
 * Include a button for "Next Record" so it's easy to enter information (this should be done in layout mode - look for the button tool on the left!)
 * Make sure that at least one of your fields has a pop-up list (but not the field referenced in your cost formula - the formula won't work!)

Calculation fields can use other fields and make the database very useful. When creating a calculation field, double-click on other fields and single click on operators. For example, if creating the HST field, select calculation as your field type. This will lead to a window where you create your formula: Retail Price*0.13.

Using the familiar IF function: We can automate the database. Let's assume I have three types of CD's in the Rating field. Create a a pop-up list for the Rating field with three types of cd's, say Quality1, Quality2, and Quality3. All will have different prices, similar to what you would see in the store. The Cost field (price you pay to buy the cds) uses a calculation with the IF function. I would create the following formula for the Cost field:

If(Rating="Quality1",2,If(Rating="Quality2",4,6))

The Cost Price field will check the Rating field in each record. If the Rating had Quality1 selected, the Cost field would show a price of $2 (format to currency in layout mode by right-clicking the field, selecting number format, decimal, currency). If Quality2, the Cost field would show $4. Otherwise it must be a Quality3 and therefore a cost of $6. Remember to have as many close brackets as you do open brackets! The Retail Price field will then be based on the Cost field. For example, a Quality1 cd would cost $2 and be sold for $4 if your formula for Retail Price is Cost*2.

__Step 3:__

Enter at least 25 items into your database.

__Step 4:__

Use colours and graphics to make your database more appealing.

__Step 5:__

Make a report showing your item inventory with summary fields in the footer, showing total items, total cost and total revenue if all were sold. Make sure your report fits onto one page.

__Step 6:__

You will have to create at least one query or find. For example, how many cd's sold over $8? (**DO NOT** use this example! Come up with one of your own!)

= Activity #4: Create a Form to Collect Data - Skipped = Often in Business we collect information on clients to know their interests. You will have to create a form in Google with at least one question of each type. How many cups of coffee do you drink each day?

In your form do the following:
 * 1) Create a form with a good design.
 * 2) Have one question of each type in your form.
 * 3) Share the link to your form on your website and in the following form detail your web address for your classmates to find it. Type the name of your website into the following form. Do this only once please: BTT Form (Do not do this again if you already did.)
 * 4) Obtain 10 responses from classmates. - Respond to the 10 people below your name here
 * 5) Use the data in Open office spreadsheets to create a chart that offers a comparison of responses. The chart can be any type but must include proper labels and be colourful.

In Edmodo submit the page link to your form on your website on or before Thursday, December 1st. Also, submit the spreadsheet which contains your chart comparing responses.
 * Note: Be sure the questionnaire is unbiased, be sure that the questionnaire is anonymous.**