1 / 32

Database Management: Storage, Retrieval & Organization of Information

A database is a software tool for storing and retrieving related information in an organized form. It consists of tables, records, and fields, similar to a filing cabinet analogy. This database terminology includes relational database, one-to-one and one-to-many relationships, flat vs. relational databases, primary keys, forms, queries, reports, and more. Learn the basics of database management using MS Access.

bgalvez
Download Presentation

Database Management: Storage, Retrieval & Organization of Information

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 collection of related information stored in an organized form Database program software tool for storage & retrieval of that information.

  2. Data is held in tables Record: one row in a a table Field: columns that each record is divided into Filing cabinet analogy Database Table Record Field (ABC Inc.) (Customers) (cust#78) (last name) Database Terminology

  3. Relational Database one-to-one relationship table EMPLOYEE TABLE Last First Work Id Name Name Station 001 Smith Joan 6-1942-A 002 Jones Paul 9-0865-A 003 Doe John 8-3649-B 004 Black Jason 8-3775-A ... .... .... ....... WORK STATION TABLE Work Make and Operating Station Model System 6-1942-A IBM-153 MS 8-3649-B DELL-A14 Linux 8-3775-A IBM-153 MS 9-0865-A HP-A919 MS ........ ........ ...... key record EMPLOYEE 1 1 WORK STATION each employee has one workstation

  4. Relational Database one-to-many relationship EMPLOYEE TABLE Last First Id Name Name Department 001 Smith Joan IT 002 Jones Paul MF 003 Doe John IT 004 Black Jason HR ... .... .... ....... DEPARTMENT TABLE Id Name AR Accounts Receivable IT Information Technology HR Human Resources MF Manufacturing ... .... .... ....... key EMPLOYEE * 1 DEPARTMENT a department has many employees an employee works in one department

  5. Relational Database many-to-many relationship STUDENT TABLE Last First Id Name Name 001 Green Jack 002 Black Millie 003 Taylor Peter 004 Trane Oscar ... .... .... COURSE TABLE Id Name ASTB03 Astronomy CSCA02 The Why and How... CSCA48 Programming MTGA04 Management ...... ........ key ENROLMENT TABLE Student Course 001 CSCA02 001 MTGA04 003CSCA02 004 CSCA48 ...... ........ STUDENT COURSE 1 1 a student takes many courses ENROLMENT a course has many students * *

  6. Flat vs Relational Flat database system that performs operations on a single file Relational database system that has a number of different tables, which may be linked together by common fields

  7. Flat Suppose you want to store information about books and their authors 1st Solution:

  8. both solutions: replication & data accuracy problems, wastes space, data retrieval problems Flat 2nd Solution:

  9. Relational Correct solution uses 2 linked tables author’s name common to both tables one-to-many relationship ∞ 1

  10. Primary Key Every table should have a primary key must be unique prevents duplicates maintains sort order (table index) used to define relationships may be a combination of several fields

  11. Database Terminology this is an Access Table: Record Field value

  12. primary key • primary key • (every table needs one) • is used for • sorting the table • accessing individual • records • linking tables

  13. Form for entering records into a Table

  14. Form for entering records into a Table

  15. example of a Query on one Table

  16. similar Query but showing only failing marks

  17. similar Query, now modified to include name and address from another Table

  18. example of a Report based on a Table

  19. example of a Report based on a Query

  20. MS Access Environment Menu Bar Toolbar Database Window wizards Object Buttons tables Status Bar

  21. Creating a Table • Using Table Wizard • Designing a table yourself (using Design view) • field names • data types • primary key • properties • Views: Datasheet or Design • enter data using the Datasheet view

  22. Design View Data Type Description Field Properties Primary Key Field Names

  23. Data Types Data Types Text Memo Number Date/Time Currency AutoNumber Yes/No OLE Object Hyperlink

  24. Properties Properties Field size Format Input Mask Caption Default Value Validation Rule Validation Text Required Allow Zero Length Indexed

  25. Datasheet View Use to add, edit, or delete records Currently selected record: editing  saved End Number of current record Total records

  26. Relationships • ToolsRelationships… • Create a 1-to-many relationship between the CustomerID fields

  27. Relationships • One-To-Many relationship (Join) • a CustomerID can appear once in Customers and many times in Invoices • a CustomerID in Invoices must be in Customers

  28. Relationships • Enforcing Referential Integrity causes an error message if a CustomerID not in the Customers table is entered

  29. Indexes • Examine indexes set up for database • make it faster to search a table • should be set up if searching on a field is anticipated Note: indexing refers to how data is stored, whereas sorting changes how the table is displayed.

  30. Create a Form: wizard Form view

  31. Form: Design view sizing handles drag Toolbox

  32. Form: complex example Each page shows one record Label Text Box(bound to field) Command Button

More Related