Tools for Information Literacy
Entities are the foundational units
Healthy, well-constructed entity classes are the foundations of an effective relational database.
Entity Classes
An entity class is the definition of the worksheets/tables that will store data, so they're essential building blocks of any 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
An entity class is essentially an object wrapper for a database table.
The attributes of an entity are transformed to columns on the database table.
Each entity set is a single table, analogous to an Excel worksheet,
which contains entities and attributes which are analogous to
rows and columns in an Excel worksheet.
Terminology Comparison
| type | ⇒⇒⇒⇒⇒⇒ | ⇓⇓⇓⇓⇓⇓ |
| ERModel entity set | composed of entities | which have attributes |
| Excel worksheets | composed of rows | which have column header cells |
entity classes and sets entities attributes primary/foreign keys
back to top
Entities
An entity is a collection of facts about a particular person, event, or other item of interest
- For example, in a library database, authors and their details might be an entity in an Authors entity class
- Books and their information might be a entity in a Books entity class
Each row contains a record which is a single entry in a table and each record is composed of several descriptive fields called attributes.
Once you have entered entities into a worksheet/table, the collection of entities is an entity set
Creating Entity Classes
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
entity classes and sets entities attributes primary/foreign keys
back to top
Attributes are descriptors of entities
Some attribute data types
| AutoNumber | Automatically increments | used for primary key (unique identifier) |
| Number | integers that are negative or positive | not numerals that do not have numeric values, like SSANs, PIDs, ZIP Codes, or telephone numbers |
| Text | used for words or non-numeric-value numerals | |
| Currency | currency amounts | with choice of decimal places |
| Date/Time | Microsoft tools, like Excel and Access, store dates and times as a number representing the number of days since 01 January 1900, plus a fractional portion of a 24 hour day | formatting is necessary to have the numbers display as dates and times |
all attribute types have properties - Some properties are primarily for text fields
Some attribute properties
| Field Size | number of characters | |
| Format | how the field's contents will be displayed | |
| Decimal Places | when data type is numeric | |
| Default Value | for new entries | |
| Validation Rule | range of acceptable entries | checks values |
| Required | a yes/no field | indicates if a value is mandatory |
| Indexed | creates an index for the field | improves searching and sorting |
entity classes and sets entities attributes primary/foreign keys
back to top
Setting Primary Keys
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
these keys link together the related entities/rows in a relational database/entity set
a foreign key is a connector - it is a copy of a primary key for one entity in one entity set - that is placed as an attribute in a different entity in a different entity set to relate the two entities, in two different entity sets, together
entity classes and sets entities attributes primary/foreign keys