1 / 20

Databasics

Databasics. A primer on data structures. What is data?. Everything. Everything is a datum. Seriously. Pretty much by definition * . But some data are better than other data. And, crucially, how you choose to record your data dramatically affects its usability. * Irrelevant aside:

sarila
Download Presentation

Databasics

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. Databasics A primer on data structures

  2. What is data? • Everything. • Everything is a datum. • Seriously. Pretty much by definition*. • But some data are better than other data. And, crucially, how you choose to record your data dramatically affects its usability. *Irrelevant aside: Physicist John Wheeler argues that the boolean bit, the basic quantum of information, is also the smallest possible unit of the physical universe.

  3. Also Data

  4. Ways to store data • Sticky notes • Pictures • Static web pages • PDFs • Documents • Spreadsheets • Databases Simplicity Power

  5. ALTERNATIVE DATA STORAGE

  6. Advantages of a database • Strongly-typed data • Automated validation and constraints • Simultaneous shared access • Centralization of data • Security • Performance • Reliability

  7. Disadvantages of a Database • Requires architectural consideration • Requires user management • Requires user interfaces for data input and retrieval (which may or may not be provided by the DBMS selected; prosumer databases like MS Access and FileMaker Pro generally have very usable templates.)

  8. Databases in use at Edgewood • Jenzabar EX • PowerFAIDS (SQL and MS Access) • WWW & My.Edgewood websites • Education Department (FileMaker) • ITSO Inventory • Printer/Copier Management … and many, many more.

  9. SCHEMATICS A BASIC OVERVIEW OF DATABASE OBJECTS

  10. Tables: Lists of Lists • Each row represents one data transaction • Columns store data points relevant to that transaction • Can be infinitely large (depending on platform), but performance suffers eventually

  11. After that slide, some cute puppies….

  12. Keys • The most important fields in a table; usually included by default in any index • A “primary key” is a key which holds only the data fields that must be used to uniquely identify a single record: • Sequence numbers (incrementing integers) • Globally Unique IDentifierS • Unique combinations of data across several fields

  13. Relational Data • How data from one table is associated with data in another table. • Joined via “relationships” recorded as “foreign keys” in the child table. • The hardest and most important part of database design.

  14. Queries • The mechanism by which everything happens – Create, Read, Update, and Delete. • Structured Query Language allows direct communication with a database engine over an established database connection (generally via an Open DataBase Connectivity object). INSERT INTO STAGE_HISTORY_TRAN (ID_NUM, HIST_STAGE, TRANSACTION_SEQ, YR_CDE, TRM_CDE, PROG_CDE, DIV_CDE, LOCA_CDE, DEPT_CDE, HIST_STAGE_DTE, ADD_TO_COUNT_DUP, USER_NAME, JOB_NAME, JOB_TIME) SELECT TOP 1 ID_NUM, dbo.[CUS_ReturnAcceptedStageCode](HIST_STAGE), TRANSACTION_SEQ + 1, YR_CDE, TRM_CDE, PROG_CDE, DIV_CDE, LOCA_CDE, DEPT_CDE, @DTE, 'Y', 'sa', 'AcceptCand', GETDATE() FROM STAGE_HISTORY_TRAN WHERE ID_NUM = @ID_NUM ORDER BY TRANSACTION_SEQ desc, HIST_STAGE_DTE desc;

  15. We might need to hug a puppy now.

  16. Stored Procedures/Functions • Scripts written in Transactional SQL that can perform updates, execute programs (like automatic emails), and/or calculate data. • Often used to process data in large batches. • Functions like GetCurrentTerms() or CalcNextPayDate() can simplify business logic. • Can be assigned to run on a regular schedule or triggered by data changes.

  17. ANALYTICS getting your data back out

  18. Structured Query Language • SQL is the most widely accepted scripting language used to communicate with modern databases, although there are several variants and exceptions. • Almost all tools that allow you to drag and drop tables and select fields using a visual editor are actually writing SQL code for you behind the scenes. • SQL is easier than it looks; there are only a handful of objects and operators (nouns and verbs), meaning that it is relatively simple to read and write. Want to learn SQL for free? It’s more fun than a fair number of things which are not fun. Start here: http://www.w3schools.com/SQL/

  19. Still Interested? • Ask us about our fabulous Infomaker and Reporting Tools Overview Presentation! • Disclaimer: No puppies included

  20. QUESTIONS? Want more? Nostalgic for this presentation already? Get these slides – and special bonus material – here: http://tinyurl.com/73qq59q

More Related