IMS 6217 Primary Key Reference Primary Keys

IMS 6217: Primary Key Reference Primary Keys • • Uniqueness of Table Rows Identifier Attributes and Primary Keys Composite Identifiers Natural and Artificial Identifiers and Autogenerated Primary Keys • Candidate Keys and Alternate Keys • Problem Keys • Diagraming Tables with Key Attributes Dr. Lawrence West, MIS Dept. , University of Central Florida lwest@bus. ucf. edu 1

IMS 6217: Primary Key Reference Uniqueness of Table Rows • Remember our twin goals of minimizing storage space and reducing data redundancy • A duplicate record (occurrence) in a table violates this rule • Because any record represents one real world occurrence of the thing the entity defines if there is a duplicate record there must be a duplicate occurrence • But each occurrence is unique so duplicate records are wasted space—one record is enough Dr. Lawrence West, MIS Dept. , University of Central Florida lwest@bus. ucf. edu 2

IMS 6217: Primary Key Reference Uniqueness of Table Rows and Update Anomalies • An “update anomaly” occurs when data is not updated correctly • Duplicate records affect data accuracy through update anomalies and processing efficiency – Occur when duplicate data exists and not all matching records are updated when a change is needed • May be two records for the same customer with different addresses – All records must be checked (since there could be duplicates) instead of stopping when the matching record is found Dr. Lawrence West, MIS Dept. , University of Central Florida lwest@bus. ucf. edu 3

IMS 6217: Primary Key Reference A Theory for the Importance of Primary Keys • If a subset of attributes (including a single attribute) can be guaranteed to have unique values in a single table… • …then each record in the table is guaranteed to be unique… • …even if there are duplicates for the values of other attributes – Student ID is unique to each student – Roommates will share address attribute values Dr. Lawrence West, MIS Dept. , University of Central Florida lwest@bus. ucf. edu 4

IMS 6217: Primary Key Reference Identifier Attributes • Identifier Attribute(s): An attribute or set of attribute(s) whose value(s) uniquely identify each occurrence of an entity • Identifier Attributes are forced to be unique within a table – SSN for student or faculty – VIN for an automobile – SKU for a retail product • We will refer to Identifier Attributes at design time and these will become Primary Keys upon implementation (after some analysis) Dr. Lawrence West, MIS Dept. , University of Central Florida lwest@bus. ucf. edu 5

IMS 6217: Primary Key Reference Identifier Attributes Define Entities • A Vehicle Identification Number (VIN) identifies one and only one instance of an automobile • Since only an automobile can be identified by a VIN, an entity with VIN as an identifier attribute can only contain occurrences of automobiles • The VIN then identifies a car but also defines the entity • What entities do these attributes define? – Social Security Number – Radio Call Letters (WMFE) – Universal Product Code (UPC) • Identifier attributes can never, ever be null Dr. Lawrence West, MIS Dept. , University of Central Florida lwest@bus. ucf. edu 6

IMS 6217: Primary Key Reference Composite Identifiers • Some entities need more than one attribute to form a unique value – Department. Code & Course. Number • Both attributes combine to be unique and identify each occurrence and define the entity • Duplicates are allowed within each attribute – Any MIS course has an “ISM” prefix – There can be an English course with “ 4212” as the course number • The combination of values (“ISM 4212”) must be unique Dr. Lawrence West, MIS Dept. , University of Central Florida lwest@bus. ucf. edu 7

IMS 6217: Primary Key Reference Composite Identifiers (cont) • What is the natural PK of our section of this course? • Of the fact that you are enrolled in this course? • What entities are identified by the following values? – Register. Number & Start. Time (Date. Time value) – VIN and SSN – Building & Room. Number (UCF database) – Apt. Num & Start. Date – Product. ID & Sale. ID Dr. Lawrence West, MIS Dept. , University of Central Florida lwest@bus. ucf. edu 8

IMS 6217: Primary Key Reference “Natural” and “Artificial” Identifiers • Most PKs are “artificial” or “generated” – SSN – Student. ID – VIN – Course. Num – Serial. Number – License. Plate. Num & State • Some PKs are (more) “natural” – Magazine. Title & Publication. Date – Section Identifier PK • The distinction isn’t particularly important Dr. Lawrence West, MIS Dept. , University of Central Florida lwest@bus. ucf. edu 9

IMS 6217: Primary Key Reference Autogenerated PKs • An important class of artificial PKs are the anuogenerated, autonumber, or “Identity” attributes • DBMS can be configured to automatically generate a unique sequential number for each new record added to a table – Identity attribute value unique within any table – Global Unique Identifier (GUID) unique anywhere in the world • By definition these values are unique and so can be a PK Dr. Lawrence West, MIS Dept. , University of Central Florida lwest@bus. ucf. edu 10

IMS 6217: Primary Key Reference Candidate Keys • Some entities may have more than one attribute or sets of attributes that can serve as a PK – VIN – Parking. Decal. Num & Year – State & Tag. Num • How is a house identified? • Often a “natural” PK is cumbersome and an artificial PK, especially an autonumber, is introduced • One candidate key is selected to be the PK • Nonselected candidate keys become “alternate keys” Dr. Lawrence West, MIS Dept. , University of Central Florida lwest@bus. ucf. edu 11

IMS 6217: Primary Key Reference Some Problem PKs • • • People’s names are horrible choices for PKs (Is there a “natural” PK for a person? ) Avoid PKs that change frequently (e-mail) Phone numbers (not unique, change) All things being equal, choose the simplest candidate key to be the PK • Always ensure that your PK defines the entity that you have in mind • (Can a SSN change? ) Dr. Lawrence West, MIS Dept. , University of Central Florida lwest@bus. ucf. edu 12

IMS 6217: Primary Key Reference A Data Model Segment Dr. Lawrence West, MIS Dept. , University of Central Florida lwest@bus. ucf. edu 13

IMS 6217: Primary Key Reference Documenting Identifier Attributes (cont. ) • Identifier attributes are underlined in an ER diagram Dr. Lawrence West, MIS Dept. , University of Central Florida lwest@bus. ucf. edu 14

IMS 6217: Primary Key Reference PKs and Other Attributes in SQL Server Diagram Composite PK Single-attribute PK Dr. Lawrence West, MIS Dept. , University of Central Florida lwest@bus. ucf. edu 15