starter activity: databases setting up a database
TRANSCRIPT
Faculty of ICT: AGCSE in ICT Slide 2 LOs
April 19, 2023
Key QuestionsKey Questions
• How do I create a new database in Access?
• What is validation and how do I add it to a database
• What is an input form and how do I create an effective input form?
Faculty of ICT: AGCSE in ICT Slide 3 LOs
HomeworkHomework
• Update your project plan with everything we have done to date
• These will be checked in your next lesson, so MUST be up to date
April 19, 2023
Faculty of ICT: AGCSE in ICT Slide 4 LOs
Creating a New DatabaseCreating a New Database
• In Access, once you have a new document, there are 2 different Table views:– Design View – For changing the
layout/structure of the Database.– Datasheet View – For adding data to the
Database or browsing through data.
• Change between different views by using the following button:
19 April 2023
Faculty of ICT: AGCSE in ICT Slide 5 LOs
Task 1: Create DatabaseTask 1: Create Database
• Your teacher will show you how to create a new database and database table.
• Create your music database and table using your Data Dictionary from last lesson
• Create any look ups need (i.e. drop down boxes)
• Use the help sheet given to you at the start of the lesson
April 19, 2023
20mins
Faculty of ICT: AGCSE in ICT Slide 6 LOs
Task 2: Adding Task 2: Adding validationvalidation
April 19, 2023
20mins
Faculty of ICT: AGCSE in ICT Slide 7 LOs
Task 3: Add validation Task 3: Add validation to your music databaseto your music database
April 19, 2023
Faculty of ICT: AGCSE in ICT Slide 8 LOs
Importing data
• Your teacher will demonstrate how to import data
Reasons for errors:Too many fieldsCapital letters or spelling mistakesValidaiton rules are incorrect
Faculty of ICT: AGCSE in ICT Slide 9 LOs
April 19, 2023
Task Input FormsTask Input Forms
• Input forms are just electronic versions of traditional forms.
• There are many input forms on the Internet. They are used to make inputting information easier, and they can also display information as well.
Faculty of ICT: AGCSE in ICT Slide 10 LOs
Database Input FormsDatabase Input Forms
• When working with a Database, we need to create an input form to help the user type information into the database:
• eg
19 April 2023
Faculty of ICT: AGCSE in ICT Slide 11 LOs
19 April 2023
Making Input Forms (1)Making Input Forms (1)
• Click on ‘Forms’
• Then click on ‘Create form by using wizard’:
Faculty of ICT: AGCSE in ICT Slide 12 LOs
19 April 2023
Making Input Forms (2)Making Input Forms (2)
• Click on the >> button to add all of the fields in the database to your input form.
• Now click ‘Next’
Faculty of ICT: AGCSE in ICT Slide 13 LOs
19 April 2023
Making Input Forms (3)Making Input Forms (3)
• Choose ‘Columnar’ and then click on ‘Next’
Faculty of ICT: AGCSE in ICT Slide 14 LOs
19 April 2023
Making Input Forms (4)Making Input Forms (4)
• Choose ‘Standard’ and then click on ‘Finish’
Faculty of ICT: AGCSE in ICT Slide 15 LOs
19 April 2023
Making Input Forms (5)Making Input Forms (5)
• Double click the name of the form from the main window.
• The form will now open. Next, click on the ‘View’ button to go to design view (this will let you edit the appearance):
View Button
Faculty of ICT: AGCSE in ICT Slide 16 LOs
19 April 2023
Making Input Forms (6)Making Input Forms (6)
• Hover your mouse in this area until your cursor changes, then drag down to make more space.
• Right click on areas of the form to change the colours, fonts etc
Faculty of ICT: AGCSE in ICT Slide 17 LOs
Task 2: Input FormsTask 2: Input Forms
• Create an input form for your school trip database. Make sure that you include the following:a) A sensible titleb) Suitable buttons to help the userc) A related imaged) Sensible colour scheme
• Finished? Read these pages on Database reports.
19 April 2023
Faculty of ICT: AGCSE in ICT Slide 18 LOs
QueriesQueries
• When we need to search and/or sort the database, we can use a query.
• Queries work in design and datasheet view in the same way that tables do.
• When we make a new query we need to specify the search and/or sort criteria (ie what to search/sort).
19 April 2023
Faculty of ICT: AGCSE in ICT Slide 19 LOs
19 April 2023
Query Example Query Example
• In order to make a query, we need to know the fields in the database that will be used.
• Eg Search the ‘Country’ field for ‘Spain’
Search the ‘Price per person’ field for ‘£40’
Faculty of ICT: AGCSE in ICT Slide 20 LOs
19 April 2023
Making QueriesMaking Queries
1 2
• Click on ‘Queries’ then on ‘Create query using wizard’.
• Click on ‘>>’ to add all fields then click ‘Next’.
Faculty of ICT: AGCSE in ICT Slide 21 LOs
19 April 2023
Making Queries (2)Making Queries (2)
3 2
• Click on ‘Modify the query design’ and then click ‘Finish’.
Faculty of ICT: AGCSE in ICT Slide 22 LOs
19 April 2023
Making Queries (3)Making Queries (3)
4
• If you would like to find something, type into the ‘criteria’ box. To sort, choose a method of sorting from the ‘sort’ box.
Faculty of ICT: AGCSE in ICT Slide 23 LOs
19 April 2023
Making Queries (4)Making Queries (4)
5 • Click the Exclamation mark to run the query.
6 • The query results will now be shown. After closing the results window, save the query with a sensible file name.
Faculty of ICT: AGCSE in ICT Slide 24 LOs
Task 3: Creating QueriesTask 3: Creating Queries
• Create (and save) queries to do the following:1. Sort the destinations into descending alphabetical order.2. Show all educational destinations and sort this list into
ascending order according to destination name.3. Show a list of all Heritage and Sports trips only.4. Show all trips costing less than or equal to £5.5. Show all trips that are free and sort into ascending order
according to the ‘region’ field. Hide the web address, opening time and closing time fields.
• Need help? Click here. Finished? Click here & read – try the activity on this page also.
19 April 2023
Faculty of ICT: AGCSE in ICT Slide 25 LOs
19 April 2023
HomeworkHomework
• In PowerPoint, create an input form design for your Dance O’Clock database. Use your data dictionary for help with knowing which fields to include. Remember to include buttons, an image and sensible colours.
• Annotate to explain fitness for purpose and audience.
Faculty of ICT: AGCSE in ICT Slide 26 LOs
19 April 2023
PlenaryPlenary
• Here are some answers to questions regarding databases.
• Write a question for each answer:1. Field2. Record3. Query4. Validation5. Input Mask