"A designer can mull over..."

	A designer can mull over complicated designs for months.  Then suddenly the simple, elegant, beautiful solution occurs to him.  When it happens to you, it feels as if God is talking!  And maybe He is.
	- Leo Frankowski The Cross-Time Engineer,1986

Database Design

	Information Requirements
	Database Modeling
		Entity-Relationship models
		Relational models (sections 9.1 & 9.3)
	Database Normalization

Information Requirements

	To design a database, first determine the nature of the information to be stored.
	Elements of the information:
		Entity classes
		Attributes
		Relations

Entity Classes

Attributes

	Important features of the entity
	Each attribute has a:
		Name
		Data type
		Single value (possibly NULL)
	The Primary Key uniquely identifies a single entity within its class.

Relationships

Relationships (cont.)

	Cardinality:
		One-to-one
		One-to-many
		Many-to-many
	Recursive relationships

Designing a Database

	Understand the domain information requirements.
	Model the data using an Entity-Relationship Diagram (ERD) that includes:
		the required data entities, attributes and relationships
		the constraints on that data
	Translate that ERD into a relational database schema.
	Normalize the resulting schema as appropriate.

 Who cares about models?

	A Model is a formal representation of certain aspects of the world.

Modeling Database Structures

Slide 11

Peter Chen
Entity-Relationship Diagrams

	Introduced in the                                   CACM, 1976
	Included features for:
		Data entities
		Data attributes
		Data relationships

ERD Notation

ERD Notation

   ERD Notation (cont.)

   ERD Notation (cont.)

The Products ERD

Designing a Database

	Understand the domain information requirements.
	Model the data using an Entity-Relationship Diagram (ERD) that includes:
		the required data entities, attributes and relationships
		the constraints on that data
	Translate that ERD into a relational database schema.
	Normalize the resulting schema as appropriate.

Edgar F. Codd
Relational Data Model

	Developed in the early 1970s
	Included features for:
		Data definition
		Data queries
	Currently, it is the database model.

Relations

	2-dimensional tables of data comprising:
		A relation Schema
		Atomic data values
	A database schema comprises a set of relation schemata.

Example

Representing Relationships

	Relationships are implemented using foreign keys as attributes.

Representing Relationships

One-to-Many Relationships

Many-to-Many Relationships

Recursive Relationships

Integrity Constraints

	Integrity constraints allow database systems to maintain the consistency of the database:
		Entity integrity
		Domain integrity
		Referential integrity

Referential Integrity

	The use of foreign keys can lead to inconsistency in the database:
		A foreign key value without a matching primary key value
		Changing a primary key value that is referenced as a foreign key
		Deleting a record whose primary key value is referenced as a foreign key

Enforcing Referential Integrity

Designing a Database

	Understand the domain information requirements.
	Model the data using an Entity-Relationship Diagram (ERD) that includes:
		the required data entities, attributes and relationships
		the constraints on that data
	Translate that ERD into a relational database schema.
	Normalize the resulting schema as appropriate.

A 7th Grade Punishment

	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”

A 7th Grade Punishment

	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”

A 7th Grade Punishment

	“I promise in the future to be considerate of other people’s feelings and not talk while they are talking.”
	”
	”
	”
	”
	”
	”
	”
	”
	”
	”
	”
	”
	”
	”
	”
	”
	”
	…

Relational Schema Punishment

Functional Dependencies

	Strong connections between attributes
	Informal Definition: the value of one attribute “determines” the value of another

Normal Forms

	Relations can be characterized by the normal form conditions that they satisfy:
		1st Normal form (1NF)
			all attributes are atomic
		3rd Normal form (3NF)
			all FDs have a super-key on their left-hand side or a part of some key on their right
		Boyce-Codd normal Form (BCNF)
			all FDs have a super-key on their left-hand side

Normalization

	1. Start with a set of relations.
	2. Identify the FD's for them based on constraints in the real world.
	3. Modify the relations to achieve the desired normal form:
	- This frequently involves splitting apart, or decomposing, relations.

Designing a Database

	Understand the domain information requirements.
	Model the data using an Entity-Relationship Diagram (ERD) that includes:
		the required data entities, attributes and relationships
		the constraints on that data
	Translate that ERD into a relational database schema.
	Normalize the resulting schema as appropriate.