1 / 15

Primary Keys

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. Uniqueness of Table Rows.

dakota
Download Presentation

Primary Keys

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. 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

  2. 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

  3. 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

  4. 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

  5. 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)

  6. 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 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

  7. Composite Identifiers • Some entities need more than one attribute to form a unique value • DepartmentCode & CourseNumber • 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

  8. 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? • RegisterNumber & StartTime (DateTime value) • VIN and SSN • Building & RoomNumber (UCF database) • AptNum & StartDate • ProductID & SaleID

  9. “Natural” and “Artificial” Identifiers • Most PKs are “artificial” or “generated” • SSN – StudentID • VIN – CourseNum • SerialNumber – LicensePlateNum & State • Some PKs are (more) “natural” • MagazineTitle & PublicationDate • Section Identifier PK • The distinction isn’t particularly important

  10. 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 • By definition these values are unique and so can be a PK

  11. Candidate Keys • Some entities may have more than one attribute or sets of attributes that can serve as a PK • VIN • ParkingDecalNum & Year • State & TagNum • 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”

  12. Some Problem PKs • People’s names are horrible choices for PKs • (Is there a “natural” PK for a person?) • Avoid PKs that change frequently • All things being equal, choose the simplest candidate key to be the PK • Always ensure that you PK defines the entity that you have in mind

  13. A Data Model Segment

  14. Documenting Identifier Attributes (cont.) • Identifier attributes are underlined in an ER diagram

  15. PKs and Other Attributes in SQL Server Diagram Composite PK Single-attribute PK

More Related