Tools for Information Literacy ⑭ Relational databases

Entities are the foundational units

Entity Classes

Healthy, well-constructed entity classes are the foundations of an effective relational database. 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.

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

Focus in the image below on the attibutes (descriptors) contained in the tables (a table is an entity class, like "Employees").

tables and relationships in the Northwind Relational Database

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

Entities

An entity is a collection of facts about a particular person, event, or other item of interest

Each row contains a record which is a single entry in a table and each record is composed of several descriptive fields called attributes.

For example in a library database, ...

an author and their details might be an entity in an Authors entity class
a book and its information might be a entity in a Books entity class

Once you have entered entities into a worksheet/table, the collection of entities is an entity set

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

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

relationships between primary and foreign keys in the Northwind Relational Database