SILS iSchool

05 November 2020



Value Added | daily

Class Schedule

Basics | sessions 01-03
  1. 13 Aug intro and clients | lecture | labs
  2. 20 Aug servers and command line | lecture | labs
  3. 27 Aug networks and protocols | lecture | labs
Web Development | sessions 04-07

  1. 03 Sep structural layer | lecture | labs
  2. 10 Sep presentational layer | lecture | labs
  3. 17 Sep using a structure | lecture | labs
  4. 24 Sep behavioral layer | lecture | labs
Dealing with Markup | sessions 08-09
  1. 01 Oct control objects and display | lecture | labs
  2. 08 Oct tools that read markup | lecture | labs
Working with Data | sessions 10-13
  1. 15 Oct formulas, functions, vectors | lecture | labs
  2. 22 Oct data display | lecture | labs
  3. 29 Oct manipulate data sets | lecture | labs
  4. 05 Nov relational data bases | lecture | labs
Presentation | session 14
  1. 12 Nov designing and delivering a presentation | lecture | labs


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

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.

Terminology Comparison
type ⇒⇒⇒⇒⇒⇒ ⇓⇓⇓⇓⇓⇓
ERModel entity set composed of entities which have attributes
Excel worksheets composed of rows which have column header cells

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.

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

back to top

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

back to top

Attribute data types

AutoNumber:

Automatically increments; used for primary key (unique identifier)

Number:

Numbers are integers that are negative or positive
not numbers that do not have numeric values like SSANs or PIDs

Text:

Used for words or non-value numbers; default setting; 255 characters max

Currency:

Dollar or other currency amounts, with choice of decimal places

Date/Time:

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.

back to top

Attribute Properties

all attribute types have properties - Some properties are primarily for text fields

Field Size:

number of characters, max is 255 (except for Memo)

Format:

how the field's contents will be displayed

Decimal Places:

option appears when data type is numeric

Default Value:

default for new entries

Validation Rule:

a range of acceptable entries; checks values

Validation Text:

appears in status bar when field is selected

Required:

a yes/no field that indicates if a value is mandatory

Allow Zero Length:

a yes/no type setting that indicates whether a text string with no length ("") is valid

Indexed:

creates an index for the field; improves searching and sorting

  • primary keys are always indexed (No Duplicates)
  • index fields that are frequently used

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

back to top

05 November lecture | preps | erm | entities | relationships | sql