ISYS 464 MANAGING ENTERPRISE DATA Chapter 4 : The Relational Model
Agenda ➢ Relational Model Definition ➢ Integrity Constraints ➢ Transforming EER Diagrams into Relations ▪ Entities ▪ Binary Relationship ▪ Associative Entities ▪ Other Relationships ➢ Normalization ➢ Denormalization and Partitioning Data
E-R model example :
Relational model example
Relation ➢ A relation is a named, two - dimensional table of data. ➢ A table consists of rows (records) and columns (attribute or field). ➢ Requirements for a table to qualify as a relation : ▪ It must have a unique name . ▪ Every attribute value must be atomic (not multivalued, not composite). ▪ Every row must be unique (can’t have two rows with exactly the same values for all their fields). ▪ Attributes (columns) in tables must have unique names . ▪ The order of the columns must be irrelevant. ▪ The order of the rows must be irrelevant.
Correspondence with E - R Model ➢ Relations (tables) correspond to entity types and to many - to - many relationship types. ➢ Rows correspond to entity instances and to many - to - many relationship instances. ➢ Columns correspond to attributes. ➢ NOTE : The word relation (in relational database) is NOT the same as the word relationship (in E - R model).
Key Fields ➢ Keys are special fields that serve two main purposes: ▪ Primary keys are unique identifiers of the relation. Examples include employee numbers, social security numbers, etc. This guarantees that all rows are unique. ▪ Foreign keys are identifiers that enable a dependent relation (on the many side of a relationship) to refer to its parent relation (on the one side of the relationship). ➢ Keys can be simple (a single field) or composite (more than one field).