1 / 16

IFS180.81 Intro to Data Management

IFS180.81 Intro to Data Management. Chapter 1 What is Relational. Operational Database TPS Transaction Processing System Dynamic Usage = conceptual running of the business. Analytical Database Data Warehouse / Mart Static

yuval
Download Presentation

IFS180.81 Intro to Data Management

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. IFS180.81 Intro to Data Management Chapter 1 What is Relational

  2. Operational Database TPS Transaction Processing System Dynamic Usage = conceptual running of the business Analytical Database Data Warehouse / Mart Static Usage = historical. Typically used for analysis (trending / forecasting) What is Relational

  3. What is Relational • Functions of DBMS: • Store the structure of the database • Stores data • Stores relationships between data • Stores forms and reports pertaining to the database

  4. What is Relational • Character—smallest logical representation—e.g.; gender code “M” for Male or “F” for Female • Data Field—a character or group of related characters that conveys meaningful data about an object—smallest meaningful unit of data—e.g.; employee name – Relational = Column • Record—a collection or group of data fields that relay data/information about an object—e.g.; employee record – Relational = Row • File—a collection or group of related records usually grouped by some logical association—e.g.; payroll file – Relational = Table • Database—a collection or group of files usually grouped by some logical association—e.g.; corporate database

  5. What is Relational • Row represents a unique instance of the subject matter of table (defines characteristics about the object) • For example: Part Master Table: W50517648 Washer, Locking EA 150 40 W50518643 Bolt, Lag EA 62 25 L404100101 Cap, End EA 23 50 Part Number Part Description U/M On-Hand ROP

  6. What is Relational • Key Fields • Primary Key Fields • Composite Primary Key Fields • Foreign Key Fields W50517648 Washer, Locking EA 150 40 B01C W50518643 Bolt, Lag EA 62 25 H34S L404100101 Cap, End EA 23 50 CY76 Part Number Part Description U/M On-Hand ROP CC

  7. What is Relational Part Master Table W50517648 Washer, Locking EA 150 40 B01C W50518643 Bolt, Lag EA 62 25 H34S L404100101 Cap, End SQ EA 23 50 CY76 Commodity Code Table B01C Purchased Fastener D765 Purchased Castings CY76 Manufactured End Caps

  8. What is Relational • Primary Keys (PK) • PK value identifies a specific record throughout the entire database (What?) • PK Field identifies a given table throughout the entire database (What?) • PK enforces table level integrity (What?) • PK should be identified for each table in the database

  9. What is Relational • Foreign Key (FK) • FK is a PK in another table • FK ensures relationship-level integrity (What?) • FK are important to eliminate orphaned records

  10. What is Relational • Table Relationships • **Not** where the name RDBMS is derived from • *Is* association of rows between tables • One – to – One Relationship • One – to – Many Relationship • Many – to – Many Relationship • Is it possible to have a none – to – none Relationship with RDBMS

  11. What is Relational • One – to – One Table Relationship • Not very common • Done for security / privacy reasons • Can be accomplished by column level security within the DBMS 04984 James Jones 1010 Mockingbird Lane York 04984 17.57 PK

  12. What is Relational • One – to – Many Relationship • Single row in one table is related to multiple rows in another table • PK on the ‘One’ side of the relationship becomes the FK on the many side of the relationship • Not every row must exist in the “Many” table, but every row in the many table must have a related row in the “one” table ** Important Concept **

  13. What is Relational Part Master Table W50517648 Washer, Locking EA 150 40 B01C L404100101 Cap, End SQ EA 23 50 CY76 W50518643 Bolt, Lag EA 62 25 H34S PK becomes a FK in “Many” Table Physical Inventory Table 251452A W50517648 149 251453A W50518643 62

  14. What is Relational • Many – to –Many Relationship • Many rows in one table relate to many rows in the second table • Normally described as a LINKING table • A methodology for associating tables without violating Normalized Form (Next week) • PK’s of both tables are combined to form the linking table

  15. What is Relational Customer Master 2270 R.R. Donnelly Chicago 9985 World Color Boston Shipto 34583 Greenfield Road Lancaster PA 64736 SteelWay Lancaster PA 98765 Harrisburg Pike Lancaster PA 2270 34583 2270 64736 2270 98765

  16. IFS180.81 Introduction to Data Management

More Related