1 / 22

Database Design

Database Design. J.G. Zheng May 19 th 2008. Overview. Entity Relationship Modeling Data modeling using Entity Relationship Diagram (ERD) Transforming ERD into tables. Introduction. Design is a process Analysis Requirements (user view) Conceptual modeling (ERD) (Logical) design

michaelbacon
Download Presentation

Database Design

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Design J.G. Zheng May 19th 2008

  2. Overview • Entity Relationship Modeling • Data modeling using Entity Relationship Diagram (ERD) • Transforming ERD into tables

  3. Introduction • Design is a process • Analysis • Requirements (user view) • Conceptual modeling (ERD) • (Logical) design • Transforming ERD to tables and keys • Physical design and implementation • Using MS Access

  4. Entity-Relationship Model • A model to represent the real world • Elements • Entities • Attributes • Identifiers • Relationships

  5. Entity • Entity • Anything in the real world • Entity class (entity set) • A set of things of the same type • Entity instance • An exact instance of an entity class • ERD notation for entity • A rectangular box Books

  6. Title ISBN Books Price Attribute and Identifier • Attribute • Properties of entity • Notation: an oval • Identifier • An attribute with unique value to identify each instance

  7. Books Publishers publish Relationship • Notation: a diamond • Degree of relationship • Unary (recursive) – only 1 entity • Binary – 2 entities • Ternary – 3 entities • N-ary – more entities

  8. Governors States govern Types of Binary Relationship • One-to-One (1:1) • A single entity instance in one entity class is related to a single entity instance in another entity class • Examples: • Governors : States • Students : PantherCards • ER Notation (Crow’s foot)

  9. Types of Binary Relationship • One-to-Many (1:N) • A single entity instance in one entity class (parent) is related to multiple entity instances in another entity class (child) • Examples • Customers : Orders • Students : Degrees • ER Notation(Crow’s foot) Books Publishers publish

  10. Types of Binary Relationship • Many-to-Many (N:M) • Each entity instance in one entity class is related to multiple entity instances in another entity class; and vice versa • Examples • Books : Writers • Customers : Banking Accounts • ER Notation (Crow’s foot) Books Authors write

  11. A Complete ER Example

  12. ER Diagram Exercise • Draw a ERD about movie data • Let’s only consider the following entities and their attributes • Perfomers (Actors/Actresses): PerformerID, FirstName, LastName, Gender • Movies: MovieID, Title, Maker, Year • MovieMakers (companies): MakerID, Name • Assumptions • A movie one or more actors/actresses • An actor/actress can be in multiple movies • A movie is made by only one company

  13. Transforming ER to Tables • Guidelines to create tables • Each entity becomes a relation (table) • Attributes of the entity become fields of that table • Identifier becomes primary key • Rules to transform relationships • Based on the type of the relationship • One-to-One • One-to-Many • Many-to-Many

  14. Locker Employee LockerID EmpID Primary Key LockerDesc LockerID Foreign Key EmpName Location Transforming 1:1 Relationships • One-to-One • The key from one relation is placed in the other as a foreign key

  15. Locker Employee LockerID EmpID Primary Key EmpID EmpName Foreign Key LockerDesc Location Transforming 1:1 Relationships • Usually, it does not matter which table receives the foreign key

  16. Transforming 1:N Relationships • One-to-Many • The primary key from the “One” side is placed in the “Many” side as a foreign key • The foreign key is always on the “Many” side TaughtBy

  17. Department Employee DeptID EmpID Primary Key DeptName Dept Foreign Key EmpName Location Transforming 1:N Relationships • Another example Department Employee has

  18. Transforming N:M Relationships • Many-to-Many • There is no direct way to map many to many relationships • To represent an M:N relationship, a table is created (intersection table) for the relationship Orders Parts OrderItems

  19. Transforming N:M Example • Many-to-Many is designed as two One-to-Many relationships • Create an intersection table with the primary key from each original table as foreign keys • The intersection table usually has a composite primary key 1:N N:1

  20. M:N Relationship Exercise MovieId PerformerId Movies Performers Cast FirstName Title Character LastName Length Gender

  21. Review Question Given the entities SUPPLIER and PRODUCT are one-to-many relationship, which of the following would represent the correct table design? • PRODUCT (ProductID, Description, Cost)SUPPLIER (SupplierID, ContactName, PhoneNumber) FK ProductID  PRODUCT • PRODUCT (ProductID, Description, Cost, SupplierID) FK SupplierID  SUPPLIER SUPPLIER (SupplierID, ContactName, PhoneNumber, ProductID) FK ProductID  PRODUCT • PRODUCT (ProductID, Description, Cost, ContactName) FK ContactName  SUPPLIER SUPPLIER (SupplierID, ContactName, PhoneNumber) • PRODUCT (ProductID, Description, Cost, SupplierID) FK SupplierID  SUPPLIERSUPPLIER (SupplierID, ContactName, PhoneNumber)

  22. Summary • E-R diagram is used widely for database design • Remember the rules to transform ERD to actual tables and relationships • One to one • One to many • Many to many

More Related