20 April 2021
preps | erm | entities | relationships | sql
Class Schedule
- 19 Jan intro and clients | lecture | labs
- 26 Jan servers and command line | lecture | labs
- 02 Feb networks and protocols | lecture | labs
- 09 Feb structural layer | lecture | labs
- 16 Feb presentational layer | lecture | labs
- 23 Feb using a structure | lecture | labs
- 02 Mar behavioral layer | lecture | labs
- 09 Mar design thoughts | lecture | labs
- 30 Mar formulas, functions, vectors | lecture | labs
- 06 Apr data display | lecture | labs
- 13 Apr manipulate data sets | lecture | labs
- 20 Apr relational data bases | lecture | labs
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.
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 | 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 |
back to top
Attribute Properties
all attribute types have properties - Some properties are primarily for text fields
| 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 |
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
20 April lecture | preps | erm | entities | relationships | sql