Introduction to data management and relational databases l.jpg
1 / 77

Introduction to Data Management and Relational Databases.

Uploaded on:
Category: General / Misc
Introduction to Data Management and Relational Databases Outline of presentation Data Management Compare database Versus spreadsheets, word processor docs, … Relational Databases Parts & Terms tables, forms, queries, reports (we’ll skip reports) fields, records, keys Relationships
Slide 1

Prologue to Data Management and Relational Databases

Slide 2

Outline of presentation Data Management Compare database Versus spreadsheets, word processor docs, … Relational Databases Parts & Terms tables, structures, inquiries, reports (we’ll skip reports) fields, records, keys Relationships Linking Tables

Slide 3

1 2 3 Mature Forest A B 1 2 1 2 3 An Old Growth C 1 2 3 B 1 2 3 B C A 1 2 3 Clear Cut C

Slide 6

Data Management Issues Organization! Information Entry (mistake inclined procedure) Quality Control – Quality Assurance Metadata (conceivable information values, how gathered, and so on.) Tracking examples, tests Data recovery

Slide 7

Spreadsheet versus Social Databases Relational Database Data section Data stockpiling Data recovery Spreadsheet Manipulating Data (eg. Turn tables) Summarizing & Presenting Data (eg. diagramming) (Formatting information for measurements programs)

Slide 8


Slide 9

Embedded Information Spreadsheets = “flat files” Databases = “multi-dimensional”

Slide 12

A glance at spreadsheets

Slide 13

It is conceivable to sort and channel records in the spreadsheet (look under DATA in the menu bar). Sifting incidentally expels every single undesirable record from perspective. This is additionally conceivable in a database, with some more complex choices accessible.

Slide 14

It is anything but difficult to duplicate cells or whole columns of information in spreadsheets, however more troublesome in databases (one of only a handful few points of interest of spreadsheets over databases). On the other hand, if one needs to duplicate down a considerable measure of information, then the database is not well “normalized” (examined later).

Slide 15

It is anything but difficult to look for and supplant words in spreadsheets. This is likewise conceivable in databases, however with more complex pursuit and supplant choices.

Slide 16

The “auto-fill” choice in a spreadsheet finishes a word it perceives from sections instantly over the present one. In databases one can utilize a “lookup table” (examined later) for a full rundown of qualities (eg. names), which may not yet show up in the information set.

Slide 17

Word processor documents are the slightest equipped for sifting, discovering & supplanting, and helping information section contrasted and spread sheets and databases.

Slide 18

Although with fitting organizing a word processor record can resemble a spreadsheet or database table, one can't control the lines and segments in the same way.

Slide 19

Archiving Data As an aside, the best frame to file information in is tab delimited (.txt) or comma isolated qualities (.csv) content records Although projects and configurations travel every which way, all database, spreadsheet, and word processor projects know how to handle .txt and .csv records

Slide 20

Comparing databases, spreadsheets, and reports 1 Auto complete is done in altogether different ways 2 Not connecting in a genuine social sense, with the exception of through a database 3 Properly set up (standardized) information can be inlayed.:

Slide 21

Tabulations Matrix-style summary of information “crosstab query” in MS Access “pivot table” in MS Excel

Slide 22


Slide 23

Relational Databases Four noteworthy segments Tables – these are the place ALL information live Queries – select subsets of information (recover information) Forms – “windows” into information tables (perspectives of information) Reports – outlines of information (designed rundowns)

Slide 24

Tables All information in social databases dwell in tables. Questions, structures, and reports are simply helpful methods for taking a gander at the information in the tables. As we might soon see, the sizes and sorts of information that can be gone into a table can be controlled for better productivity and mistake sealing. What's more, two or more tables that have a field in like manner can be connected to draw data from every single related table.

Slide 25

Some wording: Each square is a “cell” of information

Slide 26

Columns = “Fields”

Slide 27

Rows = “Records”

Slide 28

To change between datasheet view and configuration view

Slide 29

Another approach to enter outline perspective is to tap on the table name once (with the goal that it is highlighted), then tap on the configuration view symbol. On the other hand right-tap the table name and pick outline view.

Slide 30

The DESIGN VIEW of a table is the place one directs the sort and scope of information that can be gone into every field. This can incorporate organizing, (for example, capitalization), default values, and legitimate/non-substantial sections.

Slide 32

“Lookup” is utilized to make a rundown of conceivable qualities that a field can take. This illustration utilizes a rundown of qualities as a part of the field’s properties settings (in DESIGN VIEW). In DATA VIEW the field will have a drop down rundown of qualities (“Combo Box”). The full esteem will be filled in when the first letter is written.

Slide 34

In this illustration the “lookup” is set to the rundown of species codes in the table “Species”

Slide 36

Miscellaneous New records are constantly included toward the table's end (numerous individuals locate this irritating) “Esc” once to fix current writing “Esc” twice to fix the entire record Changes are spared when you get off the cell No compelling reason to spare the information in a database after any progressions (organizing changes must be spared)

Slide 37


Slide 38

Linking Tables Fields regular b/w two or more tables can connection Keyed fields avert copy passages Keyed fields focus connections b/w tables Linked tables can lessen information section and capacity needs (utilizing a thought called information standardization)

Slide 44

Keys and connections A keyed field is one that does not permit rehashed qualities. For instance, if the field “Code Name” is entered in a table, then the client would not be permitted to enter the same Code Name more than once (a blunder, “key violation” would show up). Along these lines, one develops a rundown of novel qualities (eg. Code Names).

Slide 46


Slide 47

One-to-numerous relationship Because every Species Code is extraordinary in the “keyed” Species table, and can be rehashed ordinarily in the Collections table, a “one-to-many” relationship is made between the two (demonstrated by the “1” and limitlessness). Referential respectability implies that a Species Code can't be gone into the Collections table in the event that it is not in the Species table. Course Update permits one to change the animal categories code once and engender that change through Collections table. Course Delete erases that species code in all tables that are joined. Utilize this element carefully. This is the “relationships view” of the database that permits the client to characterize which tables are connected and how. Entered fields are in striking.

Slide 49

One-to-one relationship?

Slide 51

Normalization Identical information ought not be rehashed Fields with records that are rehashed ought to be maneuvered out into a different table and referenced when required

Slide 56


Slide 57

Forms “Windows into the tables” (which truly contain the information). Structures permit the client to modify/organize fields on the screen for simplicity of information passage and recovery. Structures additionally permit the client to see a few connected tables in one showcase. Structures show every one of the records in the basic tables rather than subsets (see inquiries for this). Information can be entered and overhauled in structures.

Slide 58

Forms can (ought to) look like field gathering sheets

Slide 59

Forms can consolidate a few tables in one view (this one has five)

Slide 60

A structure with a subform (ex: Loans Masterform) Here two tables are joined in one presentation subform

Slide 61

Using Forms: Create information section frames that look like field sheets Viewing different related tables in one view The most straightforward approach to make shapes in MS Access is to utilize the “form wizard” Select first table to be in structure Select fields to be in structure Select next table to be in structure Select fields to be in structure … Select style of inquiry to make Once the structure is made, go to Design View to alter the size and position of fields in the showcase

Slide 62


Slide 63

Queries are utilized to make inquiries of the information. That is, to choose subsets of records taking into account specific pursuit criteria. The genuine force of social database questions is that information can be abridged crosswise over tables that are connected by regular fields.

Slide 68

Queries Used to choose subsets of information or mixes of information from connected tables Two or more tables may be consolidated in one inquiry utilizing connected fields Data can be entered and changed (changes will engender through the basic tables) Queries can likewise be utilized to attach one table to another, redesign records given certain criteria, or make new tables for sending out as spreadsheets or archives

Slide 69

Creating Queries are effortlessly made in Design View Click “Create question in configuration view” Select tables to be in the inquiry Select the fields to be in the inquiry Type in any criteria to scan for in every field Set any sorting inclinations Press run catch (!)

Slide 70

Review Steps for making a social database

Slide 71

Step 1: Nested Data Structure 1 2 3 Mature A B 1 2 1 2 Old Growth 3 A C 1 2 3 B 1 2 3 B C A 1 2 Clear Cut 3 C

Slide 72

Step 2: Sketch-out tables Table 1 Trap Plot Site Date Species Genus Family Order Quantity Collector Table 2 Dissolved Oxygen Dissolved Nitrogen pH Tempe