1 / 43

Access Primer

Access Primer. UoN workshop Naivasha, 30 July – 4 August 2006. MS Access. Relational Database Management System Data/information resides in series of related tables Principle of normalisation Double system (Microsoft!) .mdb: stand-alone files .adp: front-end to MS SQL Server database.

angelica-figueroa
Download Presentation

Access Primer

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. Access Primer UoN workshop Naivasha, 30 July – 4 August 2006

  2. MS Access • Relational Database Management System • Data/information resides in series of related tables • Principle of normalisation • Double system (Microsoft!) • .mdb: stand-alone files • .adp: front-end to MS SQL Server database

  3. Access elements • Tables: to store data • Queries: to ask questions from database • Forms: to create a user interface • Macros: to automate repetitive tasks • Modules: create non-standard functionality, using Visual Basic

  4. Tables • Must be defined before they can be used • Vaguely resemble Excel sheets but: • Unit of information is a ‘record’ (line) • Saved automatically as soon as one moves to another record • Consists of ‘fields’ • Type must be chosen • All fields of a record remain together • No restriction on the number of lines • Excel: 64,000 lines • Order of records is not fixed!

  5. Types of fields • Text (length; max = 255) • Memo • Integer (normal or long) • Autonumber • Real (single or double) • Date • Boolean (yes/no)

  6. Indices • Facilitate sorting and selective extraction of records • Special index: primary key • unique identifier for a record • often ‘autonumber’ • Other indices: • Unique or not • Slow data entry down

  7. Normalisation • All data should be in the database once and only once • Repeated data go to separate table • Relationships between the tables are part of the ‘model’ of the database • Link between tables defined on pair of fields, one in each table, that are of same type • Records of the two tables are ‘related’ if value in the link fields is the same

  8. Non-normalised Table Species # legs # eyes place Country date Asterias rubens 5 0 Oostende Belgium 12/3/2004 Asterias rubens 5 0 Zeebrugge Belgium 13/3/2005 Asterias rubens 5 0 Zeebrugge Belgium 14/3/2005 Cancer pagurus 10 2 De Panne Belgium 12/3/2004 Cancer pagurus 10 2 Oostende Belgium 12/3/2004 Cancer pagurus 10 2 Zeebrugge Belgium 14/3/2004 Asterias rubens 5 0 Wimereux France 13/3/2005 Asterias rubens 5 0 Wimereux France 14/3/2005 Cancer pagurus 10 2 Wimereux France 12/3/2004

  9. Normalisation • Table with morphological characters • fields: species, number of legs, number of eyes • Table with distribution records • fields: species, place, date • The field ‘Species’ is common to the two tables, and makes it possible to link records between two tables

  10. Creating relations • ‘one to many’ relations • Defined on fields of the same type • In case of text fields: same length • On the many side: primary key • Helps in maintaining the integrity of the data • Enforce presence of a ‘related record’ in the ‘related table’ • Avoid spelling variations (Oostende, Ostend, Ostende…)

  11. Relations • Best on fields that do not carry any real-world information • Autonumber • In the example: add field autonumber in the species table, and use this as a code in the distributions table; make autonumber field the primary key • Also best to add Autonumber as primary key in distributions table

  12. And more Relations… • Also the locality of the species distribution record should be in a separate table • With fields for position, country…

  13. Queries • Select and sort records of a table • Pull together information from related tables • Simple statistics • In principle: SQL • But access interface hides SQL from the user • In pratice: query grid • Define which tables will be used, fields that have to be shown, criteria for selection and sorting…

  14. SQL statement SELECT Places.PlaceName, Species.SpeciesName FROM Species INNER JOIN (Places INNER JOIN distributions ON Places.PlaceID = distributions.placeID) ON Species.speciesID = distributions.speciesID ORDER BY Places.PlaceName;

  15. Forms • Create an interface for the users • Hide codes/autonumbers (combobox) • Elements of the form are linked to fields in the tables • Can show single records at a time, or ‘repeated forms’ • Entries/changes to the form are written automatically to the table

  16. ComboBox • Limit possible entries into a field • Hide codes/autonumbers from users • Two different representations of the same information are combined • Code, to be entered into the table • Full text equivalent, for the benefit of the user

  17. Subforms • To display the content of a many-to-many relationship • We don’t know how many related records there will be, so impossible to solve with static, predefined structure • A form can be repeated, showing multiple lines • Number of lines is not limited • First create the subform

  18. Subforms • And then the main form

  19. And now… • How to decide on the structure of a database? • Which information do we want to store? • Which tables do we need? Relationships between them? • Which indices (primary and others) do we need? • Start from an analysis of user needs • In principle also, decide on a platform

  20. Task • Create ‘model’ for biodiversity/biogeography database • Which standard vocabularies to use? • What are important elements of information? • Sources • Biology • Classification, synonymy • Geography

More Related