Send the link below via email or IMCopy
Present to your audienceStart remote presentation
- Invited audience members will follow you as you navigate and present
- People invited to a presentation do not need a Prezi account
- This link expires 10 minutes after you close the presentation
- A maximum of 30 users can follow your presentation
- Learn more about this feature in our knowledge base article
Unit 16 - Database Systems
Transcript of Unit 16 - Database Systems
LO1 - Understand the principles of database systems
LO2 - Be able to create non-relational database systems
LO3 - Be able to use database software tools Unit 16 - Database Systems 16.1 Understanding the principles of database systems 16.1.2 Database Structure What have we learned today? 16.1.1 Database Basics Advantages and Disadvantages One of the most frustrating issues with computing and IT is the new terminology that you have to learn and understand. Activity
On the post it note on your desk, please write down 2 things that you have learned today & 1 thing that you did not understand that you would like explaining again (if anything)
Hand this post it note in at the door before you leave the lesson. Databases are everywhere: Schools and Colleges, Libraries, Doctor's and Dentist's surgeries, Supermarkets, Garages, Florists, Grocers, Music and DVD stores, Clothes shops, Gyms, Hotels, Kennels, Banks, Building Societies, Hospitals, Fast Food Outlets - and there are many more examples. Within each of these organisations the following information could be stored: Customers, Sales, Products, Suppliers, Purchases, Stock, Manufacturing, Accounts and VAT These databases are organised in such a way that data can be easily entered and stored, edited, deleted and manipulated to support the activities of the company. BTEC Level 2 ICT Sarah Walt Unit Objectives Advantages Disadvantages + Easy to use + Records are easy to access and retrieve + Data can easily be manipulated in many different ways + Data can be validated to ensure that it is accurate when input + Easy to store - Database structures must be carefully developed to ensure that they work as efficiently as possible - There are laws that control the use of databases - Many people feel that simply too much information is stored about us - The costs of technology can be prohibitive Database technology is no different in requiring you to learn new terminology. Some of the most common terms encountered are as follows: Object Tables Record Field Query Ascending Descending Form Reports Object An object is something that can be designed, selected and manipulated. Tables, queries, forms and reports are all examples of objects. Tables Database tables consist of rows and columns of information, where the row contains a record and the column becomes a record field. These are the foundation of the database, most databases contain a number of tables that store different information. Records A record is a single piece of information with all its relevant parts.
4, Helga Watzke, 06666 687987, 2, 24/11/2010, 29/11/2010
The above represents a single record, each record is stored in a row in the database table. Field The fields are the columns in the table. Each field has a field heading (basically a name, such as Customer_Name).
All the data in a field will have 2 things in common:
All the data in the field will be the same type
All the data will be related. For example, all the customer names in a table would be keyed into and stored in the Customer_Name field Query A query is used to manipulate the data stored in the tables. This data can be sorted.
The data can be filtered to show only records containing a specific word or phrase, or you can use the data to perform calculations.
Being able to query the data is the main advantage that computerised databases have over data that is stored manually. The results can be displayed on screen or printed. Form A form changes the way that the user accesses and uses the records within the database.
They enable users to use databases more easily, the data in tables is often (in fact usually) accessed through a form.
The form changes the way that the user accesses and uses records: Ascending Ascending means that you sort something in order of start to end e.g:
A - Z
1 - 9
January - December Descending This means sorting something in a backwards direction, from the end to the start e.g.
9 - 1
Z - A
December - January Reports Reports are used to display the information that is stored in the database or alternatively to show the results of a query in a more business-like way.
The main advantage of creating a report rather than just printing out the results of the query is that with a report, users can add headings, sub-headings, dates, page numbers and other types of formatting. We will look at reports in more detail later in the unit... Data Types Lesson Outcomes LO1: Discuss the basic principles of databases LO2: Identify advantages and disadvantages of databases LO3: Explain why different database structures are used Text Number Currency Date/Time Text
This data type will allow you to hold any alphanumeric data. This means that it can hold numbers, characters, combinations of numbers and characters. Currency
Setting a field as currency will automatically add a £ sign to the value that has been keyed in.
As with the number, you will need to choose how many numbers you wish to display after the decimal point. Number:
You can use number fields to store whole numbers or numbers with a decimal place.
If the number is a decimal, you will need to set how many numbers will be displayed after the decimal point. Date/Time:
This field allows you to key in a date in most recognised formats and store it in a specific way.
The main options within this data type are:
General - 08/04/2005 & 13:23:41
Long Date - 8 April 2005
Medium Date - 8-April-05
Short Date - 08/04/2005
Long Time - 13:23:41
Medium Time - 01:25 PM
Short Time - 13:23 AutoNumber LookupWizard AutoNumber
This is a number that the computer generates. If you use an AutoNumber in a field, each time a record is created the number will increase by 1. LookupWizard
This is a very useful data type because you will be able to set the values that will be stored in advance and the user will simply then choose from a list.
For example Mr, Mrs, Miss, Dr, Rev, Lady would all be possible titles for a customer. Primary Key Each record that is stored must have something completely unique about it. This is so that records that are similar can be told apart.
Usually, a field is selected which will contain a unique piece of data. This is then allocated as the PRIMARY KEY.
Selecting a suitable field for this can be difficult. If, for example, we were creating a database of students, we could potentially have a number of students with the same name. The name would therefore, not be unique, and could not be used as a primary key.
As such, an ID is created, often using the AutoNumber data type. This will never be repeated in any record, and so will be considered unique 16.1.3 Data Integrity The choices you make as a DB developer and user are easier if you have some very basic guidelines that you can always follow.
Given below are some suggestions which will help you make the right decisions. They will help you ensure that your DB is more likely to be correct. Data Accuracy Consistency Validation Rules When you create a database you will have the opportunity to include some on-screen instructions, to prompt the user about what needs to be keyed in. For example, you would like the user to key in their height. Your prompt is as follows: A more helpful instruction would be: Height Height in metres (e.g. 1.3) We have already talked about the currency data table and making sure that the computer stores all the numbers in the currency field with the same number of decimal places. These are used to limit the user in terms of what can be keyed into teh field. There are a number of ways that validation rules can be applied. The easiest way to apply validation is to use a lookup list. The user is then only able to select from a given list of options. There is no physical keying in required and as such there is less opportunity for the user to input something incorrectly. A second option is to give a field a specific default value. It is usually the value that you would expect someone to type or the most commonly typed value that is used - it can however be changed by the user. A 3rd option is to set the top and bottom expected values. This means that when a user keys in a value it will check to see whether the value entered is between the 2 values that you have set. Validation Rules are extremely important in ensuring data integrity.