80 likes | 94 Views
Learn how to model a database using E/R diagrams, understand the translation of E/R diagrams to real databases, and the importance of good entity relationship design.
E N D
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. Day 2 - Basic Database Backbone
E/R Diagram Parts • Entities • Attributes & Keys • Relationships • Entity • 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 Customers Day 2 - Basic Database Backbone
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 FirstName CustID Age Address • Key • Attribute(s) that uniquely identify an entity. • Drawn by underlining the attribute name in the E/R diagram. • Example shown above. Day 2 - Basic Database Backbone
Rented By Rented By Rented By 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) • N to 1 (Many To One) • N to M (Many To Many) Day 2 - Basic Database Backbone
CustID Address LastName Customers FirstName Movie Rented Rented By CheckOut Example Simple Movie DB CatID CatName Age Category In Category MovieID Title Rentals CopyNum Duration Cost CheckOutDate Day 2 - Basic Database Backbone
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. Day 2 - Basic Database Backbone
Movie E/R Diagram Translated to a DB Customer CustID (integer) -- Key LastName (VarChar) FirstName (VarChar) Address (VarChar) Age (int) CheckOut CustID (integer) -- Key MovieID (integer) -- Key CheckOutDate (Date/Time) Duration (integer) Cost (Money) Category CatID (integer) -- Key CatName (VarChar) Rentals MovieID (integer) -- Key Title (VarChar) CopyNum (integer) CatID (integer) Day 2 - Basic Database Backbone