1 / 18

Databases

Databases. A technical example of a two table relational database. A quick review :. The simplest databases are the electronic equivalent to a shoebox the shoebox itself is a TABLE each card is a RECORD on each card : specific data item containers are FIELDS (filled in with data).

yvonne
Download Presentation

Databases

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. Databases A technical example of a two table relational database AC 2002 - 2003

  2. A quick review : • The simplest databases are the electronic equivalent to a shoebox • the shoebox itself is a TABLE • each card is a RECORD • on each card : specific data item containers are FIELDS (filled in with data) AC 2002 - 2003

  3. Let’s construct an example : • A database of Water Treatment Stations • plants outside cities where used domestic waters are cleaned up • What is the set of Water Treatment Stations that we shall consider ? • It will concern France. • What information do we want to gather on each station ? AC 2002 - 2003

  4. What information do we want to gather on each station ? • The name • The location (which city it serves) • Its capacity • Its production • The technology used • The operator • The number of stations run by the operator • Financial figures about the operator • The type of franchise AC 2002 - 2003

  5. Money • Time used to be money • Now : information is money AC 2002 - 2003

  6. An example : • Gather information on 200 domestic waste treatment plants in France • It takes 15 days of a free trainee on the phone • Then sell the study to many clients • cities that intend to build a plant • operators • suppliers, etc. • eg : 1000 €/ study x 50 clients = 50 000 € AC 2002 - 2003

  7. Another example • Innovative buildings • select 500 innovative buildings in France • gather information on the architects, the techniques, the materials, the builder, the suppliers, etc. • there are thousands of potential clients (all architects, all suppliers, etc.) • we can sell the study 1000 € to say 1000 clients • NOTE : for each of them, to reproduce the work would cost more than 1000 €… An architect is in the business of drawing bldgs, not creating and selling information • 1000 x 1000 € = ? AC 2002 - 2003

  8. Back to technicalities : • Access is THE king information mgt software of the MS Office Suite • It offers, like any other DB software, several VIEWS for the data : • the simplest : the TABLE presentation ; we see all the information at a glance • the most intuitive : the FORM presentation ; each record appears as ONE CARD AC 2002 - 2003

  9. Where IS the information ? • In the TABLE • DON’T delete tables (unless you’re sure of what you’re doing) • Not in the FORM displays • We may delete a form display without much consequences AC 2002 - 2003

  10. One problem : • Access, like any DB, is designed to manage structured information • It puts it into a table (with records, fields, etc.) • It runs into the problem of repetitive information AC 2002 - 2003

  11. Repetitive information : • In our example every time we enter Lyonnaise in the « operator » field, we then have to enter 2500 in the field « number of stations run by the operator » • Time consuming useless repetition • Potential source of data entering mistakes AC 2002 - 2003

  12. Solution : • Create a separate table for « operators » • Enter information specific to operators into this second database • and LINK the operators table to the stations table. • Our database will now be a multiple table database, also called a relational database AC 2002 - 2003

  13. A frequent mistake : • A frequent mistake is to create the second table into a new database • No, no • We must create the new table within the SAME database, • just like we created new sheets within the same excel document (if we wanted to link them…) AC 2002 - 2003

  14. The technique : • We split the one table database into TWO tables • Why ? Because there was repeated information, and we want to avoid that • (There exists a tool that analyses how to split the information, we did not use it) • We are still into ONE database • There remains to link the two tables AC 2002 - 2003

  15. Let’s step back, what do we do ? • We split our initial UNIQUE table into several tables of information • In our example the way to do it was natural and evident : have a main table for Stations and a « subsidiary » table for Operators • In more complex situations there is no unique choice ; it is a matter of database architecture (we have the choice) AC 2002 - 2003

  16. Database architecture • With the same end use, some databases are beautiful, some databases are ugly • It all depends upon the art of the architect • The main database architecture tool is called UML (Uniformed Modelling Language) AC 2002 - 2003

  17. Creating the link • Determine which field in the subsidiary table will be the « handle » of the link with the main table • Make it the primary key of the subsidiary table • Go to the Relations view and « pick and slide »... AC 2002 - 2003

  18. Difficulties FAQ • Cannot create a « 1 to many » link : another person in the room uses the same name, with the same login • Cannot « save as » : normal ACCESS is more complex than Word • Why ? • Because we ACCESS we work on the heart of information not just on displaying it AC 2002 - 2003

More Related