Slide
- Lesson:R004 Databases
- Please login to keep your results
- Welcome,
R004 Databases
http://www.ocr.org.uk/Images/129643-unit-r004-exemplar-materials.pdf
- I know which organisations use databases
- I know how different people use a database
Key Vocab
- Table
- field
- record
- datatype
- primary key
Tables
All data is stored in tables. We have different tables for different things such as: People, Products, Sales.
- Create Fields
- Select Datatypes
- Choose a Primary Key. The unique identifier of
Video Help From Teach-ICT.com
Validation Rules
We can’t make people enter the correct data into a database but we can help them not to make silly errors using data validation
- Data Types (non-text). You can’t enter a word into a number or date field
- Presence Check – if a field is required the user can’t leave it blank
- List Check – eg. The validation rule “M” or “F” would force the user
- Lookup would specify the list so the user can choose
- Range Check keeps numbers and currency fields above or below certain levels eg. >1000
- Format Check
- Input Mask
Video Help From Teach-ICT.com
Relationships
- A primary key that appears again in a foreign table is called a foreign key.
- We link tables by linking Primary Key to foreign key.
Query / Search
We use a query to get data out of the database.
- Select columns (fields)
- Filter the records using criteria
Query Task
For help check the previous stone on Queries
Objective 1: Simple query on a single table using single criteria
- Show only DVD’s that fall in the Action genre.
- Any overdue rentals
- Customers who live in Leeds, in alphabetical order*
Objective 2: Complex queries on linked tables using single and multiple criteria
- We want to know customers who haven’t returned DVD’s, so we can write to them. It should include key information about the DVD/rental.
- A customer wants to find DVD’s released between 2002 and 2005 that are in stock. They would like them sorted by category
Objective 3: Re-usable queries (parameter queries)
- We want a query to search for any category of DVD which is in stock. The results will need to be sorted to inform the user of the various titles.
Reports
- Reports are pretty queries.
- They are designed for printing on paper.
What makes a good report?
- Good Title
- No data cut off
- All columns fit across the page (landscape orientation can help)
- House Style
- Doesn’t waste paper or ink (ie mainly a white background)
How to make a report
- Use the wizard !