Primary and Foreign Keys

Table:

entity, relation

Column:

field, attribute

Row:

record

DBMS:

Database engine, back end

Foreign Keys

Def: another table's primary key. Used to relate one database table to another.

Schema:

ER Diagram, logical model, structure

Primary Keys

-Must be unique -Cannot be NULL -Must be given a value when the record is created -Values cannot be changed

Composite Primary Key

-Two or more fields together provide unique values -Ex. Latitude/Longitude, Address/Zip, Model/Color/Capacity

Natural Primary Key

-Values exist outside of the context of the database -Ex: Social Security Number, VIN Number, Passport Number

Surrogate Primary Key

-Values only exist in the context of the database -Almost always has "ID" as part of the identifier -Ex. EmployeeID, StudentID, MedicationID

ISM 310 Naming conventions

Tables: -Table identifiers should be PascalCase -Table identifiers should be singular -Ex: Employee, Customer, Order, StoreTransaction, UserPersonalization Attributes (fields/columns): -Attribute identifiers should be PascalCase -Attribute identifiers should be singular -Ex: SaleDate, FirstName, Price Don't use spaces, underscores, prefixes, postfixes, plurals, ALLCAPS, alllower