preps | erm | entities | relationships | sql
Class Schedule
This work
is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.
home & schedule | syllabus | contact | grades
Healthy, well-constructed entity classes are the foundations of an effective relational database.
A database should have a separate entity class/worksheet/table for every major component of the database
Data should not be unnecessarily duplicated in multiple entity classes/worksheets/tables
Duplicating data is a common error,
but it's easy to avoid if you structure your entity classes well
Each entity class table contains
entities and attributes
which are analogous to
rows and columns in an Excel worksheet.
An entity set is a single table, analogous to an Excel worksheet.
type | ⇒⇒⇒⇒⇒⇒ | ⇓⇓⇓⇓⇓⇓ |
ERModel entity set | composed of entities | which have attributes |
Excel worksheets | composed of rows | which have column header cells |
Each row contains a record which is a single entry in a table and each record is composed of several descriptive fields.
When creating databases, sketch out the different entity classes/tables you will need and the data contained in the entity classes/tables before you begin
Automatically increments; used for primary key (unique identifier)
Numbers are integers that are negative or positive
not numbers that do not have numeric values like SSANs or PIDs
Used for words or non-value numbers; default setting; 255 characters max
Dollar or other currency amounts, with choice of decimal places
Dates & times. Like Excel, Access stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day. Formatting is necessary to have the numbers display as dates and times.
number of characters, max is 255 (except for Memo)
how the field's contents will be displayed
option appears when data type is numeric
default for new entries
a range of acceptable entries; checks values
appears in status bar when field is selected
a yes/no field that indicates if a value is mandatory
a yes/no type setting that indicates whether a text string with no length ("") is valid
creates an index for the field; improves searching and sorting
To distinguish one entity from another, entity sets contain a primary key attribute
the primary key is an identifier - such as a part number, a product code, or an employee ID - that is unique to each record
The primary key should be a piece of information that won't change frequently
05 November lecture | preps | erm | entities | relationships | sql