1 / 28

Database Theory

Database Theory. What is a database?. A database can be simply defined as a collection of items of information organised for convenient access. Database. Usually, databases are thought of in relation to computers, but there are actually many different types of databases, e.g.

isolde
Download Presentation

Database Theory

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Theory

  2. What is a database? A database can be simply defined as a collection of items of information organised for convenient access.

  3. Database Usually, databases are thought of in relation to computers, but there are actually many different types of databases, e.g. • Library card catalogues • Dictionaries • Telephone books

  4. Database Packages • Businesses keep histories of clients on a database Accounting packages such as M.Y.O.B. or Quickbooks, are examples of customised databases for small business

  5. Data Data can be retrieved from a database. This illustration shows data retrieved from a database on Cheese used by a Gourmet Cheese Shop.

  6. Information Information is retrieved from a database when the stored data is processed or organised in some way. This is commonly done to answer a question or query about the data in the database, e.g. What is the price of the most expensive cheese? $26.99 What is the name of the most expensive cheese? Monterey-Jack How many types of Blue cheese are available? 3 What type of cheese is Camembert? Soft/Fresh

  7. Database Management Systems A Database Management System (DBMS), is a computer software program that manages the storage of data in a computer and the retrieval of information from a database.

  8. Database Management Includes the following processes: • Creating a database table to store the data. • Adding data to the table in the form of records. • Saving the database table so the added records are available at a later time. • Retrieval of the data for display on a screen or printing. • Modification of the data to correct input errors or to update data. • Deletion of data that is no longer required.

  9. Types of Database There are two types of database commonly in use today: • Flat File Databases e.g. Microsoft Excel • Relational Database Management Systems (RDBMS) e.g. Microsoft Access

  10. RDBMS Relational database. The data is broken down into separate files (tables) that can be related by using a common (Primary Key) field.

  11. Advantages of RDBMS • Easy to learn and use • Allow almost unlimited ad hoc querying of the data without any knowledge of the way in which the data is stored. • Powerful and simple to visualise and use

  12. Table Structure • Data is stored in related tables. • Tables consist of one or more recordswhich contain data about a specific entity or item. • Each record contains one or more fieldsthat contain different categories of data. • Each field is identified by a field name and is characterised by a data type and field size.

  13. Sample Microsoft Access Table • This database contains 23 records. • An example is record 1 that represents the following data: • The collection of cheeses stored at the shop include 1.2kg Semi-Hard Monterey-Jack cheeses, code AM001, which retail at $26.99 each.

  14. Sample Microsoft Access Table The records in this database table contain ten fields with the field names: CheeseCode Price Type Received CheeseName Qty Country Comments WeightKg AustCheese

  15. Data Types The records in this database table contain different data types: • Text data is stored in the CheeseCode, Type, CheeseName, Country, and Comments fields. • Currency data is stored in the Price field. • Numeric data is stored in the WeightKg and Qty fields. • Memodata (lengthy notes or descriptions) is stored in the Comments field. • Date/Timedata is stored in the Received field. • Yes/Nodata (Yes/No, True/False and On/Off answer) is stored in the AustCheese field.

  16. Table Structure The illustration shows the structure for the table that contains data on the cheese shop supplies.

  17. Table Views Tables have two views: • Design view - used to create and modify the structure of a table • Datasheet view - used to view, add, delete and edit data in a table

  18. Keys A key is any field used as the basis for ordering the data in a table in a meaningful way

  19. If your database isn't stored on a computer, or only parts of it are, you may be tracking information from a variety of sources that you're having to coordinate and organize yourself.

  20. Example Suppose the phone numbers of your suppliers are stored in various locations: • in a card file containing supplier phone numbers • in product information files in a file cabinet • in a spreadsheet containing order information.

  21. If a supplier's phone number changes, you might have to update that information in all three places. In a database, however, you only have to update that information in one place — the supplier's phone number is automatically updated wherever you use it in the database.

  22. Data Storage • Data is stored once in one table, but can be viewed from multiple locations. • When you update data, it's automatically updated everywhere it appears. • Data can be: • Displayed in a query • Displayed in a form • Displayed in a report • Displayed in a data access page

  23. Create one table for each type of information • Define relationships between tables to bring data from multiple tables together in a query, form, report, or data access page. • Supplier phone numbers that once existed in a card file now reside in the Suppliers table. • Product information that was stored in a filing cabinet now resides in the Products table • Order information that once existed in a spreadsheet now resides in the Orders table.

  24. A unique ID (identifier), such as a Supplier ID, distinguishes one record from another within a table. • By adding one table's unique ID field to another table and defining a relationship, related records from both tables can be matched so that you can bring them together in a form, report, or query.

  25. Queries • Find and retrieve data that meets specified conditions, including data from multiple tables. • Update or delete multiple records at the same time • Perform predefined or custom calculations on data.

  26. Forms • View, enter, and change data directly in a table. • When you open a form, data is retrieved from one or more tables, and displayed on the screen with the chosen layout • A form focuses on one record at a time, and can display fields from more than one table. It can also display pictures and other objects. • A form can contain a button that prints, opens other objects, or otherwise automates tasks.

  27. Reports • To analyse data or present it a certain way in print. For example, you might print one report that groups data and calculates totals, and another report with different data formatted for printing mailing labels. • You can use a report to: • create mailing labels. • show totals in a chart. • calculate totals.

  28. Data Access Pages • Make data available on the Internet or an intranet for interactive reporting, data entry, or data analysis. • Data is retrieved from one or more tables and displayed on the screen with the chosen layout

More Related