up

2020-01-16

Practice Exercises will be on MyCourses - Use to Practice!

Re-cap from last week

File processing systems pre-date the DB. There are some legacy systems that still exist. They are expensive and hard to maintain.

Databases are collections of related information.

What can we store

• Numbers
• Text
• Date/time
• Graphics and images
• sound and video
• programs and objects

Parts of the DB

• App metadata
  • Info about forms and apps
• Metadata
  • what’s the structure of our db
• Index and Overhead
  • Indexing shenanigans
  • Indexing in DBs is dark magic, easily an entire course
• User data
  • The data you want

Metadata

Tells you about the DB. Table of tables, info about relations and rules and constraints and all that stuff.

DBMS components

• Design tools
  • Developers use design tools
  • Design tools use the engine to change the DB
• runtime
  • Applications use the runtime
  • The runtime uses the engine to query the DB
• dbms engine
  • Handles requests, changes the db

DB as a Model

A database is a model of the reality of the subject matter expert / user.

DB development life cycle

We’ll cover these things in this class:

• Create an entity-relationship model
• Transpose data model into relations
• Normalize relations
• Create the DB Tables
• Define relationships between tables

Prof. didn’t say it but we’re gonna query like crazy

Creating Data models

“Identify the Universe of Discourse of the problem”

The UoD describes the subset of reality that we are interested in modeling.

• Entities - Things
• Attributes - Properties of the things
• Relationships (between the things)
• Business Rules about the things

As the UoD changes, we will have to modify the data model.

Data Modeling

A method of communication between the DB expert and the client, such that each can understand what is being captured in the model.

ERP = Entity-relationship model

UML = Universal Modeling Language

Levels of Abstraction

• High Level
  • Abstract representation
  • UML / ERP
• Representational Level
  • Movement between high and low levels
  • SQL
• Low Level
  • Physical, filesystem layouts, etc.
  • Bits on Chips

Attributes

• Chen’s Model
  • Oval, connected to entity class (rectangle) by line.
• Crow’s Foot Model
  • Listed in box

Simple attributes are a single piece of data. Example, firstname.

Composite attributes are composed of other attributes. Fullname = firstname + lastname.

Composite Attributes are not listed in Crow’s foot notation

Single valued attributes store one thing. Firstname.

Multi-value attributes: Skills: carpentry, plumbing, etc.

In Chen’s notation, multi-value attributes are in a Double Oval.

Stored vs. Derived - is it in the db, or calculated when needed?

An identifier tells you something unique about that entity. For example, SSN, or a composite identifier of firstname+lastname.