Fundamentals of Database

Overview

1. What is a Database?
   • a model of reality.
2. Why use models?
3. When to use dbms to create and use model?

Why user models?

A map is a model of reality?

map is model of reality(scale, color,north up)

A message to model maker

quiz

To use

1. data intensive vs process intensive communication between memory secondeary memory
2. power shutdown
3. redundancy --> consistency
4. without documentation can not run

Not To Use

Data Model

1. Data modeling processis to fix a perception of structure of reality
2. And then represent this perception.
3. Database people use tow different languages for this. One is extended entity relationship model which is good for fix... The relation modle is good for representing this perception in the database.

Process Modeling

Data manipulation langue we are going to use is sql

Data models-architecutre

Examples Of Data Models

ERM: Conceptual modle High level data models Relation model: Representational Data Models

Always remember these three aspect for data model

Three data model in this class:

1. ERM: fix perception of reality
2. Relation model: implement model in dms
3. Hierarchical model: the model implemented in the first data base, it is also the fundamental model on the XML database today.

Relational Models Data-Structures

Relational Model-Constraints

Data Model-Operations

Keys And Identifiers

quiz

Integrity And Consistency

user1 and user 2 inconsistency, because there are some reduntancy

quiz

Null Value

inapplicable is not good, unknow is ok. it is catch all table problem

Surrogates Things And Names

User1 change her name and it is hard for us to identify this user by using name-based.

so the system generate surrogates(id), it will never change.

ANSI-SPARC

1. The separation into a single schema and data however is not the end of it. If the database only consisted of schema and data, then the schema would have to describe aspects of what the meaning of data is how it's used and how it's internally organized.This would mean that queries against the database through that schema would be able to reference how the data is physically organized.
2. The implication of that is If we decide to change how data is physically organized, then the application of the database would be affected.

3. Therefore, the ANSI/SPARC 3-level
   architecture separates out aspects
   of how data is physically organized into
   what is known as an internal schema.
   And data now sits under the internal
   schema, such that database access, it will appear that it goes through
   the schema to access the data, but instead what happens is that it goes
   through the schema, and the request, or
   chains, is translated to one at
   the internal schema level and data's accessed and
   an answer is sent back.

4. The benefit of this is
   that now it's actuallyplausible to change
   the way data is stored without affecting the applications
   that run against the schema. However, a database is used by many
   different applications with different
   needs to display data.

5. Therefore for each need, for
   each application of the database,we could create an external schema. Therefore the ANSI/SPARC
   3-level architecture

6.So now you have applications
running against external schema that represents data in the preferred
format for those application requests through the external schema
are then translated to requests through
the conceptual schema, translated to
requests through the internal schema.
Data is accessed and
the response is translated up and presented to the application
in the format that it needs.

Conceptual schema

Extenal shcema

HightPayFemale is virtual table

Quiz

Internal Schema

index can be removed in the internal schema.

Physical Data Independence

It is like encapsulation, we can change the implementation of the class (internal schema)inside without changing the whole application.

Logical Data Independence

It is harder than physical data independence
ex are logically derivied from conceptual schema.

we can change conceptual schema without chaning ...

Quiz

ANSI-SPARC DBMS Framework

two peices: schema compier and query tranformer

Metadata Chart

Metadata-What Is It