1 / 73

IT 20303

IT 20303. Relational Database Theory. Relational Database Theory. The Relational Theory Ways of working with data Types of “Models” File database model Hierarchical database model Network database model Relational database model. Relational Database Theory. The Relational Theory

orea
Download Presentation

IT 20303

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. IT 20303 • Relational Database Theory

  2. Relational Database Theory • The Relational Theory • Ways of working with data • Types of “Models” • File database model • Hierarchical database model • Network database model • Relational database model

  3. Relational Database Theory • The Relational Theory • Meaning of database model • The way data is organized & stored • The way data is manipulated

  4. Relational Database Theory • Relational Model of Data • Published in 1970 by Dr. Edgar (Ted) Codd – IBM • “A Relational Model of Data for Large Shared Data Banks”

  5. Relational Database Theory • Relational Model of Data • Purpose • Achieve program/data structure independence • Treat data in a disciplined way • Apply rigor of mathematics • Uses Set Theory – sets of related data • Improve programmer productivity

  6. Relational Database Theory • The Relational Model • Relational uses familiar concepts • The data is perceived as organized in tables • Relational also incorporates the rigor of mathematics • Rows of the table are treated as elements in a set • Manipulation of rows is based on set operations – (Vinn Diagrams) • User works with a set of rows at a time

  7. Relational Database Theory • Relational also impacts Data Design • Files were often constructed to support an application • Tables are designed to describe one thing or Entity in the database

  8. Relational Database Theory • Example of a Relation: • ANIMAL – Entity (Relation)

  9. Relational Database Theory • Definition of a Relation • Data is organized & stored in structures called relations • A relation is a table that adheres to certain rules • A relation can be called a table

  10. Relational Database Theory • Definition of a Relation • A relation is a table containing all the data about some entity • An entity is a thing or object that is important in this application area • Data items in the table are related

  11. Relational Database Theory • Relational Data Structure Name Species Weight Domains Attributes Primary Key Tuples Relation

  12. Relational Database Theory • Relational Data Structure Definitions • Relation • The Table • Tuple • A Row • Attribute • A Column

  13. Relational Database Theory • Relational Data Structure Definitions • Primary Key • A unique identifier for the table • Domain • A pool of legal values from which an attribute value is selected • Related to meaning • Has a Data Type

  14. Relational Database Theory • Relational Data Structure Definitions • Degree • The number of attributes • Cardinality • The number of tuples

  15. Relational Database Theory • Relational Table Rules • A Relation is a table that adheres to the following rules: • There are No Duplicate Tuples in the table • The tuples in the table are treated as a mathematical set

  16. Relational Database Theory • Relational Table Rules • By definition, a set is a collection of unique elements • There must be a primary key (unique identifier) for each tuple

  17. Relational Database Theory • Relational Table Rules • There is no order to the tuples (top to bottom) • There is no order to the attributes (left to right) • By convention, the primary key attribute is usually the first one on the left side of the table

  18. Relational Database Theory • Attributes • Each attribute has a datatype • Examples: Integer, character, date, user-defined • The data value of an attribute can be null

  19. Relational Database Theory • Attributes • Each attribute value is atomic • There is One & Only One data value in each cell of the table • There are no Lists or Arrays • One fact per field, one field per fact • Can be called a Field (MS Access)

  20. Relational Database Theory • Relational Data Structure: Design • Each relation contains data about only one entity • Each row corresponds to one unique occurrence of the entity • A relation does not contain arrays, lists or repeating groups • No multi-valued attributes

  21. Relational Database Theory • Tables are designed according to Rules of Normalization • Each data item in the table is determined • By the Primary Key • By the Whole Primary Key • Only by the Primary Key

  22. Relational Database Theory • Normalization avoids well-known update problems • Optimizes design to minimize redundancy & storage requirements

  23. Relational Database Theory • Example: Table with repeating group • Animal

  24. Relational Database Theory • Example: Table with no repeating group Animal-Food Animal

  25. Relational Database Theory • A Database Models the Real World • A Database represents Reality • The database is a collection of relations • A relation represents an entity type • Each tuple represents one occurrence of that entity type • Each occurrence of an entity is unique

  26. Relational Database Theory • A Database Models the Real World • A database contains information about • Entities • Relationships between entities • Rules about the entities’ data & the relationships

  27. Relational Database Theory • Relational Databases Support Relationships • Relational databases support relationships between entities • Relationship is established by a Foreign Key • Repeat the Primary Key of one table in the related table(s)

  28. Relational Database Theory • Example: The Zoo has an “Adopt-an-Animal” program • A zoo member can adopt an animal Foreign Key Zoo-Member Animal

  29. Relational Database Theory • Example: Another Relationship Animal-Food Composite Primary Key Animal Foreign Key

  30. Relational Database Theory • Relational Integrity Rules • Entity Integrity • No part of the Primary Key (PK) may be Null • Referential Integrity • The value of a Foreign Key (FK) must either • Be Null or • Be one of the values of the PK in the related table

  31. Relational Database Theory • Keys, Keys, and More Keys • Characteristic of a Primary Key (PK) • Unique • Mandatory • Unchanging • Under the control of IT organization

  32. Relational Database Theory • Keys, Keys, and More Keys • Names or Types of Keys • Candidate Key • A minimal set of attributes that can be used as the unique identifier for a table

  33. Relational Database Theory • Keys, Keys, and More Keys • Names or Types of Keys • Primary Key • One of the candidate keys • Alternate Key • A candidate key that is not the primary key

  34. Relational Database Theory • Keys, Keys, and More Keys • Names or Types of Keys • Foreign Key • A primary key of a related table • Indicates relationships

  35. Relational Database Theory • Keys, Keys, and More Keys • Names or Types of Keys • Composite Key • A key composed of more than one attribute • Search Key • One or more attributes on which a retrieval is based • Indexes

  36. Relational Database Theory • Characteristics of Relationships • Referential integrity applies to the relationship between entities • Also known as an existence constraint or an enterprise rule • For every relationship, referential integrity must be defined

  37. Relational Database Theory • Relationships have Cardinality • One-To-One • One-To-Many • Many-To-Many • Relationships have Optionality • Each entity’s participation is either • Mandatory or • Optional

  38. Relational Database Theory • Cardinality reflects Business Rules • One-To-One Relationship • One animal is cared for by one zoo worker • One zoo worker cares for one animal

  39. Relational Database Theory • Cardinality reflects Business Rules • One-To-Many Relationship • One animal is cared for by many zoo workers • One zoo worker cares for only one animal

  40. Relational Database Theory • Cardinality reflects Business Rules • Many-To-Many Relationship • One animal is cared for by many zoo workers • One zoo worker cares for many animals

  41. Relational Database Theory • Mandatory Relationship • The Foreign Key Cannot be Null • Every purchase order must have a supplier • In the example below the FK, SNO, cannot be Null

  42. Relational Database Theory • Example: PORDER SUPPLIER

  43. Relational Database Theory • Example: FK can be Null Foreign Key ZOO-MEMBER ANIMAL

  44. Relational Database Theory • What happens when a Tuple is deleted? • For every relationship, there are three possible delete options • Cascades • Delete the target tuple and • Delete the related tuples

  45. Relational Database Theory • Restricted • Delete restricted to cases for which there are no related tuples • Nullifies • Delete the target tuple and • Set the FK to null in the related tuples

  46. Relational Database Theory • Relational Algebra Operations • Select • Project • Join • Union • Intersect • Difference

  47. Relational Database Theory • Our Zoo Database Tables ANIMAL-FOOD ANIMAL ZOO-MEMBER

  48. Relational Database Theory • Relational Algebra: SELECT • Extracts specified tuples from a relation (or get rows from a table)

  49. Relational Database Theory • Example: SELECT out from the ANIMAL-FOOD table (display) the rows where FOOD=PEOPLE ANIMAL-FOOD RESULTS

  50. Relational Database Theory • Relational Algebra: PROJECT • Extracts specified attributes(columns) from a relation (or get columns from a table)

More Related