up

2020-01-21

Review - DB dev lifecycle

• Create a model
• Translate to relations
• Normalize relations
• Create the DB structure
• Define relationships between DB tables

Expanded DB dev lifecycle

• Create an ERM Model
• Transpose into Relations
• Normalize Relations
• Create the Schema within the DBMS
  • Create the DB
  • Create the Tables
• Populate the DB with Data

ERM review

• Entity Class
  • Represented by a Rectangle
  • A table in a DB
• Entity Instance
  • a row in a table

Instances have Identifiers

My favorite ERM resource (so far)

Doing practice exercise 1 together

I should really do the practice exercises :/

Attribute attributes:

• Composite / Simple
  • Made of many parts or one - has children or not
  • ‘Name’ can have firstName or lastName
• Single / Multivalued
  • How many values can it have? Two ovals if multivalued
  • Books
• Stored / Derived
  • Stored or calculated when needed - dashed outline if derived
• Identifier
  • Used to uniquely identify an entity instance

Transposing ERMs to Relations

A relation starts with a capital name, and then in parenthesis there is a list of attributes. Only and all simple, single-valued, and stored attributes are included.

For example:

EMPLOYEE(SSN, DOB, Name)

I could do the crazy underlining with MathJax, but that’s a lot of work. Pretend ‘SSN’ is underlined.

When this becomes a table:

• Attributes become ‘Fields’ or ‘Columns’
• Instances are ‘Rows’ or ‘Tuples’

Prof is using a lot of terms that require context. Anyone coming in here with no background knowledge is likely completely lost right now.

Normalizing Relations

Welcome to First Normal Form (1NF)

• Rows contain instances of the relation
• Cells hold a single value
• Columns are attributes of the relation
• All entries in a column are of the same ‘domain’
  • The Information belongs together, It’s all about the same thing
• Each column has a unique name
• The order of the attributes is unimportant
• the order of the rows is unimportant
• No two tuples may be identical (There must be a primary key)
  • Natural primary key (an attribute)
  • Surrogate primary key (an attribute added just to guarantee uniqueness)

If a relation meets all of the above criteria, then it is in First Normal Form.

Normal forms are def. on the test.

HW #2 will be assigned next week, deals with detecting NF, not with correcting it