Transcript Day 2 - Basic Database Backbone

Day 2 - Basic Database Backbone
How to Model a Database
Using E/R Diagrams
 How an E/R Diagram
Translates to a Real Database

How do I model a database?

Why do I need to model a system?
– Gives a team a basis to design a system on.

Design Issues
– Entity Designs and Relationships
– Domain/Range Constraints of the System
– Good Entity Relationship Design

Entity/Relationship Model
– Used to model the relationships between different objects in
the database.
– Parallel structure to an object-oriented approach of thinking.
CSE 498
Day 2 - Basic Database Backbone
2/8
E/R Diagram Parts

Entities
Attributes & Keys
Relationships

Entity


Customers
– Major objects in a database.
– Strong Entity: Records are uniquely identified by its attribute(s).
– Weak Entity: Records are uniquely identified by its
relationship(s). Weak entities are shown with double borders
around the entity block.
Customers
CSE 498
Day 2 - Basic Database Backbone
3/8
Attributes & Keys

Attribute
– A piece of information that defines a part of the entity.
– Drawn as ovals that link off an entity.
– Example:
LastName
Customers
CustID

Key
Age
FirstName
Address
– Attribute(s) that uniquely identify an entity.
– Drawn by underlining the attribute name in the E/R diagram.
– Example shown above.
CSE 498
Day 2 - Basic Database Backbone
4/8
Relationships

Relationship
– Diagram definition of how entities relate to each other.
– Drawn as a line between two objects with a diamond defining
the relationship. An arrow defines the direction of the
relationship.
– Weak relationships are drawn as double lines.
– Three types of relationships…
• 1 to 1 (One To One)
Rented By
• N to 1 (Many To One)
Rented By
• N to M (Many To Many)
Rented By
CSE 498
Day 2 - Basic Database Backbone
5/8
Example Simple Movie DB
CustID
LastName
Address
CatID
Customers
Age
CatName
Category
FirstName
In Category
MovieID
Rented By
Title
Rentals
CopyNum
Movie Rented
CheckOut
Duration
CSE 498
Cost
Day 2 - Basic Database Backbone
CheckOutDate
6/8
How does an E/R translate to a DB?

All entities become tables.

All attributes become columns in the tables.

Relationships are tricky…
– One-To-One and Many-To-One relationships are handled by
adding in the keys from the higher level table to the lower level
table.
– Many-To-Many relationships are handled by creating a table
that has the keys from both entities to define the different
relations.
CSE 498
Day 2 - Basic Database Backbone
7/8
Movie E/R Diagram Translated to a DB
Customer
CheckOut
CustID (integer) -- Key
CustID (integer) -- Key
LastName (VarChar)
MovieID (integer) -- Key
FirstName (VarChar)
CheckOutDate (Date/Time)
Address (VarChar)
Duration (integer)
Age (int)
Cost (Money)
Category
Rentals
CatID (integer) -- Key
MovieID (integer) -- Key
CatName (VarChar)
Title (VarChar)
CopyNum (integer)
CatID (integer)
CSE 498
Day 2 - Basic Database Backbone
8/8