1 / 24

IT354 Database Design

IT354 Database Design. Seminar 5: May 19, 2011 Physical Database Design Entity-Relationship Diagrams. Activities for this Seminar. Preliminary Questions Examine Unit Topics—Seminar Questions Look at details for Unit 5 Assignment. Preliminary Questions. Weather check

erica-whitaker
Télécharger la présentation

IT354 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. IT354 Database Design Seminar 5: May 19, 2011 Physical Database Design Entity-Relationship Diagrams

  2. Activities for this Seminar • Preliminary Questions • Examine Unit Topics—Seminar Questions • Look at details for Unit 5 Assignment

  3. Preliminary Questions • Weather check • Do you have any questions about the web reading articles?

  4. Seminar Questions •   What data types can you assign to a column in Microsoft Access?   • How would you go about designing a survey form that you would use to obtain the information from users to design the required database?  • What information can you display in a Crow's Foot E-R diagram?  

  5. Example data types

  6. Microsoft Access data types • Found at the following website • http://msdn.microsoft.com/en-us/library/bb208866%28v=office.12%29.aspx

  7. Unit 5 Assignment • This is the next step of your final assignment for IT354 • One part (5 points) is incorporating feedback from your instructor. Please do not wait for me to return Unit 4 assignment to start the other parts.

  8. Unit 5 Assignment—three parts • 1.           Update the three parts of your Unit 4 project, incorporating the feedback from your instructor (5 points) • 2.            Merge all user views.   Determine all attributes for each table.   Represent all tables using Database Design Language (DBDL) • Part 2 is worth (25 points)

  9. Unit 5 Assignment 3.  From your DBDL representation, create an Entity-Relationship (E-R) Diagram for your database.   Use the IDEF1X notation used in the textbook. (See Fig. 6.2 and Fig. 6.8.)   Create your E- R Diagram in PowerPoint; change the Page Setup to portrait before you start.   When you are satisfied with your diagram, copy and paste it into your project Word document. (30 points)

  10. Associative Weak Entity (Shapes to use in ERD)

  11. Questions

  12. Have a great week! • Lhouse@kaplan.edu (816-213-2917) or KaplanLhouse@aol.com

  13. IT354 Database Design Enrichment Seminar ERD Dr. Laura House

  14. Relationships How do you determine the type of relationship between two entity types? To determine the connectivity of a relationship, you make two statements about the relationship, each starting with a singular entity, and examine the (one-word) multiplicity of each. The two statements look at the relationship from each "side." For example, to examine the relationship between employee and order, the two statements would be: 1. _An_ order is taken by only _one_ employee. (Multiplicity = one.) 2. _An_ employee may take _many_ orders. (Multiplicity = many.)

  15. Relationships, cont Determining the type of relationship between two entity types, cont. 1. _An_ order is taken by only _one_ employee. (Multiplicity = one.) 2. _An_ employee may take _many_ orders. (Multiplicity = many.) You combine the two one-word multiplicities to get the connectivity of the relationship. That makes employee to order a one-to-many relationship. The statement with the multiplicity of "many" is used to determine which is the "one" side and which is the "many" side of the relationship. In this case, Employee is the "one" table and Orders is the "many" table. The primary key (PK) of the "one" table, Employee (e.g. EmployeeID), will be a foreign key (FK) in the "many" table, Orders. If you carefully make one statement about the relationship from each side starting with a singular, you will get the correct connectivity with no guesswork.

  16. Relationships, cont Another example: To determine the relationship between order and product, the two statements would be: 1. _An_ order may contain _many_ products. (Multiplicity = many.) • _A_ product may be may be contained in by _many_ orders. (Multiplicity = many.) Product to Orders would be a many-to-many relationship. These statements are part of the “business rules.”

  17. Relationships, cont Another example, cont.: In a relational database, a many-to-many relationship must have an intermediate table called an “associative entity” to link the two entity types. Product …. 1 : M …. OrderProduct …. M : 1 …. Orders The OrderProduct associative entity will have the PK of Orders (OrderID) and the PK of Product (ProductID) columns. Each will be a foreign key pointing to their respective “outer” table. In addition, the two columns will be a composite PK in the OrderProduct table. This table is usually called LineItem or OrderDetail. Associative entities are usually not depicted in an Enterprise Data Model.

  18. Linking the Tables • Customer Number is a Primary Key in CUSTOMERS • And a Foreign Key in ORDERS

  19. Question-how do you link these three tables? (before) • Customer • Name    Address               Zip • Eric         8299 Elk                78250 • Order • Make    Model   Year • Chevy   S10         2001 • Payment • Form     Card Number     Balance • Credit    002220022992    2500

  20. Question-how do you link these three tables? (after) • Customer • CustomerID(PK) Name    Address               Zip • 123456 Eric         8299 Elk              78250 • Order • OrderNumber(PK Make    Model   Year CustomerID • 876654 Chevy   S10         2001 123456 • Payment • PaymentID Form  Card Number   Balance OrderNum • 000009 Credit    002220022992    2500 876654

  21. Situation and Business Rules Vintage Music Rocks sells music to customers. The music is on albums. The albums contain several tracks of music. An album has a catalog number. The catalog number is unique and is determined by the album name and release date. An album is in one item. An album may be ordered by many customers. A customer may place multiple orders. A customer may order many albums. What are the entity types? What is the identifier (PK) of each?

  22. Customer Orders ZipCode Music Album Are these relations in 3NF ?

  23. Customer CustomerID Name <more> Orders OrderNo CustomerName Album Name Album Price Order Date Total Price Qty ZipCode ZipCode City State Music Album CatalogNo AlbumName Album Description Artist Name Release Date In Stock Value Tracks Are these relations in 3NF ?

  24. Customer CustomerID Name <more> Orders OrderNo CustomerName Order Date ZipCode ZipCode City State Music TrackAlbum CatalogNo songname track length Track Total Price Album CatalogNo AlbumName Album Description Artist Name Release Date In Stock Album Price AlbumOrders OrderNoPK CatalogNoPK Discount Qty Are these relations in 3NF ?

More Related