Principles of Databases

Document Sample
Principles of Databases Powered By Docstoc
					  Unit 9, Database Basic Principles and Structure
  BTEC First for IT Practitioners


                                                                                    advancing learning, changing lives

OnCourse - Tutor Notes


Databases are very widely used in business, but are one of the least well understood ‘office’ applications. This is
probably because they can become highly complex and a certain amount of planning and designing is required.
The unit has a emphasis on practical skills but there is some basic theory required in order to make sense of the
practical tasks. This topic should probably be the first lesson in this unit and as such concentrates on the theory.
Many learners may already have some practical knowledge of using database products such as Microsoft Access
but it is important that you ensure all your learners have a good understanding of these basic principles.


PowerPoint Presentation
Slide 1 – Title slide
Slide 2 – The slides begin with the question ‘What is a database’, which might sound a silly question but it can
          be quite difficult to describe what a database is and does without getting too technical. The first point
          to make is that although we might not have spent much time creating or using databases ourselves,
          they certainly affect many aspects of our lives. Lots of information about us is stored on databases,
          student and employee records, our bank and credit card transactions, car details, phone billing records
          and so one. Whenever we go to a supermarket checkout the EPOS (Electronic Point of Sale) system is
          interacting with a database of stock records, and whenever we visit a Internet on-line shop and search
          for the products we want we are also interacting with a database. You might like to ask you students
          how many examples of computer databases they can think of that they are aware of. It shouldn’t be
          too difficult to come up with a pretty long list.
          Having hopefully agreed that databases are widely used, the next question to ask, is why use a
          database. A comparison is drawn here with other ‘Office’ software. Word processors as the name
          suggests process or manipulate text (and images), spreadsheets are primarily designed for processing
          numbers (with formulae and function etc.), so, logically databases are designed for processing data.
          However data is a very broad term, so what does it mean. Your student’s will probably come up with
          answers like ‘information’, ‘facts’, ‘details’, all of which are correct. Strictly data is ‘that which is
          given or known’, think of any well know object either real such as a person, a car, a house, a computer
          etc. or conceptual like a bank account and it is not difficult to think of data items which would apply
          to that object. Data and information are related but slightly different. Data is raw or unprocessed,
          while information is data that has been processed in some way to make it meaningful or useful. So a
          list of all the students in a school or college is data, but that data sorted into class or course lists and
          printed on a register is information.
Slide 3 – Our simple definition of a database is ‘a collection of data’. The term database has come to be used
          for both the data itself and the software that manipulates that data. This can be a little confusing, so
          make sure your learners understand this. DBMS (database management system) is a term that is not
          used so much these days, but it is the proper name for the type of software that creates and
          manipulates databases.
          Microsoft dominate the low end database software market with their Access product which many of
          your learners will have heard of. However industrial scale databases often use highly sophisticated and
          expensive software designed for multiple users from companies such as Oracle. There is lots of
          information about this companies products and customers on their web site (www.oracle.com) but
          much of it is written in quite technical language. MySQL is an open source database (i.e. it is available
          free of charge) also designed for large multi-user database systems. You can find out more about
          MySQL at their web site (www.mysql.com) where there is also a lot of information about both products
          and customers (Web search engine Google for example uses mySQL).
          There isn’t any requirement for your learners to know very much about these products, it is just worth
          making them aware that Microsoft Access is not the only database software.
          The key point about a database is that it stores data in a structured way. You could argue that Word
          Processors and spreadsheets also store data, but they don’t apply a structure to the data in the same
          way that a database does. The next slide explains more about the way databases are structured.


816d362f-f32b-4a34-92d4-23c36418cecb.doc                                                            Page 1 of 4
../2

Slide 4 – The structure of a database if defined by the tables and the fields within those tables. Every database
          has one or more tables. A table is a singe store of related information. Typically a table will store data
          about a real world thing or concept, such as a car, student, hospital patient, or bank account. Many
          databases have multiple tables, so the database itself holds data about an application area, while the
          tables within that database hold data about things or objects within the application area. So a college
          or school database might have tables for students, teacher and courses, while a database for a doctors
          surgery might have tables for patients, doctors and appointments. The design of multi-table databases
          and the relationship between tables is a complex subject, beyond the scope of this topic. You need to
          make your learners aware that databases can have relationships between tables (so students can be
          related to courses and patients can be related to appointments) but there is no need to go into the
          technical detail at this level.
          Within the tables themselves data is structured by being split into fields. These are typically shown as
          the columns within a table and set the name and the type of data for the individual data items held in
          a table.
          Records on the other hand are the rows within the table and contain all the data (fields) for a single
          object or thing within the table.
          The structure is not that difficult to understand, but an important question to ask is ‘why have the
          structure in the first place?’. This goes back to primary purpose of a database, which stores data so
          that we can find the records we are looking for and answer questions about our data. By structuring
          the data into fields we can easily search for a record or records which match what we are looking for.
          This might be all the students over 21, everyone’s bank account that is overdrawn, all the phone calls
          made last month and so on. Each of these depend on a field based criteria (students age, account
          balance, date of call).
Slide 5 – Since finding records is a important part of the purpose of a database, it is very important that each
          record can be uniquely identified. It might be nice for you if your bank account got muddled up with
          Tony Blair’s, but he might not be so happy! While uniquely identifying records in a small database
          might not seem to be a problem, real world commercial databases can be vast, with millions of
          records. For this reason it is standard practice to define one of the fields in each table as the primary
          key. This is usually a made up value, such as a numeric code (such as student number) or a
          combination of letters and numbers (such as a National Insurance number or car registration number).
          The database software will not allow you to create a new record with the same primary key as another
          records so its uniqueness is guaranteed. Another advantage of the primary key is that the database
          software maintains an index of primary keys which means that it can search for and find a record using
          the primary key much more quickly that it can using a non-key field. It is also possible to set other
          fields to be key fields (secondary keys) which can either be unique or allow duplicates. This is useful if
          you often need to search for records using this field as the search criteria, because the software can
          search more quickly using the key’s index. However you should avoid making too many fields key fields
          as this will slow the overall performance of the database down and make the database files very large.
Slide 6 – As well as just tables, databases usually have an number of different parts (object) which allow the
          user to manipulate and present the data in different ways.
          Queries are an important part of most databases since they are the tools which help to find records
          and answer questions about the data. Queries are basically saved search criteria, so for example if you
          needed a list of all students were over 21, you could create a query which search the student table for
          records where the age field contained a value of 21 or greater. If this was a list you needed regularly
          you could save the query and run it whenever you needed the list. A later topic looks at queries in
          detail.
          Reports are primarily designed for formatting data for output to the printer. Additional text, lines and
          boxes and calculated totals can be added to the data from a table or query to make a meaningful and
          easy to read printed report.
          Forms are primarily designed for inputting or outputting data on screen. Like reports, additional text
          and layout features can be added to make the data clear and easy to read.
          Most database software also provides application building tools (such as macros and code modules)
          which combined with queries, forms and report allow technical users to build a complete application
          based on the database. This is subject is beyond this level, but it is worth mentioning to you learner
          that this is possible and they may learn about it at higher levels.




816d362f-f32b-4a34-92d4-23c36418cecb.doc                                                           Page 2 of 4
../3

Slide 7 – In terms of advantages of using a database we can look at these in comparison to using a non-IT system
           and in comparison to using other IT software.
          Compared to a paper based filing system, with a database creating and inserting records is quicker
          because you don’t have to search through the files to find the correct place to put a new one
          (assuming the paper files are in alphabetical order). Paper based records cannot easily be sorted in
          different orders (by surname, by date of birth etc.), but this can be easily done with a database.
          Searching for records is quicker with a database, and with a paper filing system you can only easily
          search by the order they are stored in (perhaps numerical order by code number or alphabetical by
          name), but with a database you can search by any field. Amendments to individual paper based records
          might be quite simple (although you have to find the record first) , but if you needed to insert another
          field, this is a much lengthier process with a paper filing system. Storage space is another
          consideration with large amounts of data. Storing millions of records in filing cabinets would take a
          very large amount of space. With a paper based system when files are out of the filing cabinet being
          worked on, they are not available to other users, this is not the case with a database. Finally putting
          together a report which draws data from many different records is very difficult with a paper based
          system, since you would need to extract the relevant data from every paper file, which if there were a
          lot of them would make the task impossible, however databases excel at this kind of task and what
          would take hours or days in a paper based system can be done in minutes.
          There are many other advantages of a database over a paper system, see if your learners can come up
          with some, but try to avoid simple answers like ‘its easier and quicker’ and get them to explain why
          this might be so.
Slide 8 – The benefits of a database over storing data in a Word Processing file should be fairly obvious. The lack
          of structure makes this solution only marginally better that a paper based system. However you can
          create simple databases in spreadsheet software, since the rows and columns provide a field and
          record structure, and Microsoft Excel for example has some simple database facilities. Where only a
          small number of records are required (in the hundreds) Excel provides a reasonable solution without
          the more complex set up and skills required with Access. However using a database rather than a
          spreadsheet for a has some major advantages:
                     Suitable for multiple table databases, can create relationships between tables
                     Databases can deal with very large amounts of data and allow multi-user access
                     Complex searches can be created and saved using queries, spreadsheets only allow simple
                      searches which cannot be saved.
                     Database software allows the creation of sophisticated forms and reports, which many data
                      input and output easier and more user friendly.
                     Database software allows creation of complete end-user applications.


Slide 9 – The first two disadvantages are to do with cost of ownership. The software itself may be quite
          expensive but the expertise required to create and maintain a database is much more expensive.
          Microsoft Access is probably the most difficult Office application to learn how to use, and large scale
          commercial database software (such as Oracle) is much more complex. To support sophisticated multi-
          user database system both technical experts (who demand high salaries) and powerful computer
          hardware are required. Related to this is the fact that unless the database is designed carefully in the
          first place it may not have the required functionality and/or it may perform slowly.
          One problem that databases can suffer from is inaccurate data. Quite a lot of data input to databases
          is done in large volumes and great care needs to be taken to ensure it is entered correctly, since
          inaccurate data may mean that the analysis and interpretation is incorrect. It can also lead to
          problems such as customers being sent incorrect bills, orders being sent to the wrong place and other
          such mistakes. Where ever possible data should be validated before it is accepted by the system, to
          reduce possible errors.
          Another issue, no so much a disadvantage, but a requirement of using databases that store personal
          data, is the Data Protection Act. This act was passed in response to concerns that the large volume of
          data held about individuals could be misused, or if inaccurate could lead to problems for individuals,
          such as being refused credit due to inaccurate details about their credit history being held on
          databases. Organisations that hold personal data on computer databases are required by the act to
          register with the Data Protection registrar and they must conform to the requirements of the act.


816d362f-f32b-4a34-92d4-23c36418cecb.doc                                                          Page 3 of 4
../4

          These include ensuring the data held is accurate and secure and that it should only be used for the
          purpose for which it was gathered.


MCQ Quiz
There is a simple multiple choice quiz that can be used to check your learners have remembered some of the
details from the PowerPoint presentation


Activity
This simple activity is designed to get your learners to think about the advantages of a database and relate them
to a case study application. Try to encourage them to give real examples in their reply to the e-mail and avoid
bland ‘quicker, faster, better’ type answers. You might want to discuss what sort of uses Tim could put his data
to before asking them to write the reply. You could also give them the answers to the questions that they want
to ask Tim (i.e. make them up!) before they write their reply.




816d362f-f32b-4a34-92d4-23c36418cecb.doc                                                        Page 4 of 4

				
DOCUMENT INFO
Description: Principles of Databases document sample