Chapter 6 - Relational Database Design by ER and EERR-to-Relational Mapping presents about ER-to-Relational Mapping Algorithm, Mapping EER Model Constructs to Relations (Options for Mapping Specialization or Generalization, Mapping of Union Types (Categories). | CSC271 Database Systems Lecture # 6 Summary: Previous Lecture Relational model terminology Mathematical relations Database relations Properties of a relation Relational Keys Superkey Candidate key Primary key Alternate key Foreign key Superkey An attribute, or set of attributes, that uniquely identifies a tuple within a relation A super key may contain additional attributes that are not necessary for unique identification Consider Branch relation: {branchNo, street, city, postcode} {branchNo, street, city} {branchNo, city} or {branchNo, postcode} Candidate Key Superkey (K) such that no proper subset is a superkey within the relation In each tuple of R, values of K uniquely identify that tuple (uniqueness) No proper subset of K has the uniqueness property (irreducibility) In simple words, superkey with minimal attributes Simple key or composite key Instance of a relation cannot be used to prove uniqueness Consider Branch relation branchNo or postcode Primary Key Candidate key selected to identify tuples uniquely within relation Consider Branch relation Primary key: branchNo Alternate Keys Candidate keys that are not selected to be primary key Consider Branch relation Alternate key: postcode Foreign Key Attribute, or set of attributes, within one relation that matches candidate key of some (possibly same) relation Used to represent relationship In the Branch relation, branchNo is the primary key However, in the Staff relation the branchNo attribute exists to match staff to the branch office they work in In the Staff relation, branchNo is a foreign key branchNo in the Staff relation targets the primary key attribute branchNo in the home relation, Branch Representing Relational Database Schemas Conceptual schema or conceptual model Branch (branchNo, street, city, postcode) Staff (staffNo, Name, position, sex, DOB, salary, branchNo) Integrity Constraints We need to understand Nulls before starting discussion on integrity constraints Null Represents value for an attribute | CSC271 Database Systems Lecture # 6 Summary: Previous Lecture Relational model terminology Mathematical relations Database relations Properties of a relation Relational Keys Superkey Candidate key Primary key Alternate key Foreign key Superkey An attribute, or set of attributes, that uniquely identifies a tuple within a relation A super key may contain additional attributes that are not necessary for unique identification Consider Branch relation: {branchNo, street, city, postcode} {branchNo, street, city} {branchNo, city} or {branchNo, postcode} Candidate Key Superkey (K) such that no proper subset is a superkey within the relation In each tuple of R, values of K uniquely identify that tuple (uniqueness) No proper subset of K has the uniqueness property (irreducibility) In simple words, superkey with minimal attributes Simple key or composite key Instance of a relation cannot be used to prove uniqueness Consider Branch relation branchNo or postcode Primary Key Candidate key selected .
