1 / 15

DATA MODELING AND DATABASE DESIGN Part 1

DATA MODELING AND DATABASE DESIGN Part 1. Objectives. Learn the stages of system development. Define basic types of data relationships. Recognize the optionality and degree of a relationship Read an entity relationship diagram.

jduncan
Download Presentation

DATA MODELING AND DATABASE DESIGN Part 1

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. DATA MODELING AND DATABASE DESIGN Part 1

  2. Objectives • Learn the stages of system development. • Define basic types of data relationships. • Recognize the optionality and degree of a relationship • Read an entity relationship diagram. • Translate an entity relationship diagram into set of table instance charts. • Complete translation from logical to physical database design.

  3. Development Stages • Creating CONCEPTUAL database model • Making LOGICAL database design • Performing PHYSICAL database design

  4. Interview notes Existing documentation Current System Specs Business Narrative Course # Code * Name o Start Date o Duration Instructor # id * last name o first name * phone taught by leads a

  5. Benefits of Entity Relationship Diagrams • Quickly present concepts in people's minds • Provide an easily understood graphical map of the system • May be easily refined and upgraded • Separate the information required by a business from the activities performed by the business

  6. Entity Relationship Modeling Terms • Entity • A thing of significance about which information needs to be known • Examples: department, employee, order • Attribute • Something that describes or qualifies an entity • Examples: dept_id, address, customer_id • Relationship • An association between two entities • Examples: region and department, customer and order

  7. Entity Relationship Model • Create an entity relationship diagram from business specifications or narratives. • Scenario (from Department’s side) • ". . . Assign one or more employees to a certain department . . ." • ". . . Some departments do not yet have assigned employees . . ." EMPLOYEE #* id * last name o username DEPARTMENT #* id * name o region_id Belongs to Contains

  8. Entity Relationship Modeling Conventions ENTITY Soft box Singular, unique name Uppercase attribute Singular name Lowercase Mandatory marked with "*" Optional marked with "o" CUSTOMER #* id * name o phone EMPLOYEE #* id * last name o first name (#)* username assigned to the sales rep to Unique Identifier (UID) Primary marked with "#" Secondary marked with "(#)"

  9. Entity Relationship Syntax • Syntax • Each source entity {may be | must be} relationship name {one and only | one or more} destination entity. • Example • Each ORDER must be for one and only one CUSTOMER. • Each CUSTOMER may be the client for one or more ORDERs. Mandatory - Must be Optionality - May be ORDER #* id * ord_date o ship_date CUSTOMER #* id * name o phone for the client for Degree - One or more Degree - One and only one

  10. OPTIONALITY Solid line represents a mandatory relationship often called a MUST BE relation. Relationships Dashed Line represents an optional relationship often called a MAY BE relation. DEGREE One and only One --- ONE-TO-ONE One or More (Many) --- ONE-TO-MANY

  11. Degree Types • One-to-one • Have a degree of one and only one in both directions. • Are rare. • Example: Computer and Motherboard • One-to-many • Have a degree of one or more in one direction and a degree of one and only one in the other direction. • Are very common. • Example: Customer and Order. • Many-to-many • Have a degree of one or more in both directions. • Are resolved with an intersection entity. • Example: Reader and Magazine

  12. UID Bar: Example ITEM #* id * price o quantity ORDER #* id * ord_date o ship_date in made up of taken by UID bar - relationship is part of the entity’s unique identifier. the sales rep for EMPLOYEE #* id * last name o first name

  13. A Unique Identifier bar indicates that the relationship participates in an entities UID. In other words the UID of one entity becomes part of the composite UID of the other entity as well as a foreign key. UID Bars

  14. UID Bars Rules: 1) The UID Bar is always at the many end of a one to many relationship. 2) The entity at the many end always receives the UID of the other entity.

  15. Reading ERD’s COURSE # code * duration o fee INSTRUCTOR # id # last name # first name o hire date is taught by is the teacher of included in includes PROGRAM # code # start date

More Related