1 / 19

Database Design: Surrogate Keys, Candidate Keys, and Data Constraints

This chapter discusses the use of surrogate keys, candidate keys, and data constraints in database design. It explains the advantages and disadvantages of surrogate keys, the concept of candidate keys, and the different properties of columns in a database table.

verla
Download Presentation

Database Design: Surrogate Keys, Candidate Keys, and Data Constraints

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. Chapter Six Professor Adams’ Slides

  2. Note that entities are shadowed, tables are not. • Note that entities have no physical existence (blueprint) • Note the use of the key symbol to indicate the primary key

  3. Surrogate Keys • A surrogate key is a DBMS-supplied identifier of each row in a table • Surrogate keys are unique and they never change • Surrogate keys are assigned when row is created and destroyed when row is deleted. • Surrogate key values are “the best” primary keys because they are designed to be short, numeric, and fixed • They are necessary when there is not obvious primary key, or when the rows are not unique.

  4. Surrogate Key Disadvantages • Their values have no meaning to the user • Another disadvantage arises when data are shared among different databases. (see description of problems on page 173)

  5. Candidate (alternate) keys • There is no difference between the terms candidate key and alternate key • The image shows a notation for specifying alternate keys.

  6. Column Properties – Null Status • Null status • Can a column have a null value or not • Null means null values are allowed (not that values are null) • See (BTW) on p. 175 • Data type • Default value • Data Constraints

  7. Column Properties – data type • Null status • Data type • Each DBMS has different data types • money vs currency • date vs datetime • Some SQL Server data types • Generic specifications • CHAR(n), VARCHAR(n), DATE, TIME, MONEY, INTEGER,DECIMAL • Default value • Data Constraints

  8. Specify Column Properties:SQL Server Data Types

  9. Specify Column Properties:Oracle Data Types

  10. Column Properties – Default value • Null status • Data type • Default value • A default value is a value supplied by the DBMS when a new row is created. • constant, string, function result • May be supplied by a trigger • Data Constraints

  11. Column Properties – Data constraints • Null status • Data type • Default value • Data Constraints • Domain constraints • Limit column values to a particular set of values • Range constraints • Limit values to a particular interval of values • Interrelation constraints • Limit a column’s values in comparison with other columns in the same or other tables

  12. Verify Normalization • Are the tables in Boyce-Codd normal form? • Is every determinant in every relation a candidate key? • Does every determinant uniquely determine a row? • Have all multi-valued dependencies been removed? • Is there still a condition in any relation with three or more attributes in which independent attributes appear to have relationship they do not have?

  13. Relationships Strong Relationships 1:1 1:N N:M ID-Dependent Entities Mixed Entity Relationships Subtype Relationships Recursive Relationships 1:1 1:N N:M Ternary & Higher Order Relationships

  14. 1:1 Strong Entity Relationships • Place the key of one entity in the other entity as a foreign key: • Either design will work – no parent, no child • Minimum cardinality considerations may be important: • O-M will require a different design that M-O, and • One design will be very preferable

  15. Choice we made • ClubMember(MemberNumber, email, phone, LockerNumber) • Locker(LockerNumber, LockerRoom, LockerSize)

  16. 1:N Strong Entity Relationships • Place the primary key of the table on the one side of the relationship into the table on the many side of the relationship as the foreign key (foreign key doesn’t have to be unique) • The one side is the parent table and the many side is the child table, so “Place the key of the parent in the child”

  17. N:M Strong Entity Relationships • In an N:M strong entity relationship there is no place for the foreign key in either table: • A COMPANY may supply many PARTs • A PART may be supplied by many COMPANYs

  18. N:M Strong Entity Relationships • Create an intersection table

More Related