Database Management Systems Database Design by Normalization and ER Model 1
Topics n n n Requirements and Assumptions Data Items Functional Dependencies (FD) Normalization ER Model 2
Guide 1. 2. 3. 4. 5. Find the data items Write the assumptions Draw the dependencies diagram Connect all data items Determine direction of the arrows Using Functional Dependencies Obtain the tables Underline the independent data items as PK’s 3
Interview Manager Listen to me, we want you to set up the most modern system in place, so go ahead and tell me what machine to buy and the advantages we get. Something like a database, you know. Listen I am not an expert; this is why I called you. Yet I can tell you that this company is dedicated to manufacture elegant fittings that are used in good furniture, rather than the awful nails and screws you see in cheap furniture. With them you can make modular designs. By the way, have you gone to IKEA or Beautiful Kitchens in Dallas or Houston, you should. Anyway, a piece of furniture has different types of fittings, an each piece requires a certain amount. The same fittings are also used in other type of furniture, such as a TV stand, a bookshelf, a table or a chair, but the amount is different for each piece. Also we have an order system, and for each order we keep the information on the delivery address, the name of the customer, the quantity ordered, the type of fitting, the name of the customer. You know, mostly we deal with manufacturers. For each order we keep a number and a detailed line for each fitting or item ordered. In any case, we know the price of each item and how many are needed for a given type of furniture, so we can plan our production. Be my guest. Since we get the fittings from various manufacturing plants For each Be my guest. fitting we also need to know the plant where it is manufactured and quantity in stock. Certainly each plant provides us with various fittings of the same kind as other plants. Finally for each fitting we know its type, its quality and a description. For each of our customers we keep his/her addresses. We provide discounts based on quantity only. You -- Easy does it Sir. First I need to know your Information Reality, this is to say, what reports you use, what are your input formats in your order and so on … (knowing that this is what you need) – Very interesting, tell me more (just keep him talking, it is important to record everything) – I will have some coffee. Do you mind? (knowing that there is nothing more coming from him for the time being) – Very well let me work a bit on this and I will propose you a database design before we go any further. 4
Data Items 1. Furniture ID: 2. Piece Description: 3. Address: 4. Customer ID: 5. Fitting ID: 6. Fitting Description: 7. Quality: 8. Order Number: 9. Date: 10. Detail Line: 11. Quantity Ordered: 12. MPlant ID: 13. Stock: 14. MPlant Name: 15. Volume: 16. Discount: 17. Price: 18. Quantity Required 1 Integer Example : 32 String ( 40 ) Example : “TV Stand” String (40) Example : “ 3457 Bellaire , Houston" Integer Example : 45 Integer Example : 12 String ( 40 ) Example : ”Medium hinge” String ( 10 ) Example : ”Brass" Integer Example : 1234 Date long Example : 12/12/2004 Integer Example : 13 Integer Example : 80 Integer Example : 12 Integer Example : 439 String (30) Example : ”Denton" Integer Example : 500 Integer Example : 25 Float Example : $56. 78 Integer Example : 34 5
Assumptions • • In each plant various fittings are manufactured The same fitting is manufactured in different plants The discount is based on volume only The customer has various shipping addresses The same fitting is used in different pieces of furniture A piece of furniture uses various fittings An order is comprised of more than one detail lines 6
Details : ( Order, Detail Line, Quantity Ordered, Fitting ID) Ensembles: ( Fitting ID, Furniture ID, Quantity Required ) FD Diagram Stocks : (MPlant, Fitting ID, Stock ) Piece Description Quantity Required Price Furniture: ( Furniture ID, Piece Description ) Fitting Description Address Customer ID Fittings : ( Fitting ID, Fitting Description, Quality, Price ) Furniture ID Addresses : ( Address, Customer ID ) Order Number Date Quality Fitting ID Detail Line MPlant ID Stock Plants : ( MPlant, MPlant Description ) Quantity Ordered Discount : ( Quantity Ordered, Discount ) Orders : ( Order Number, Address, Date ) MPlant Name 7
It is all in the relations Plants : ( MPlant, MPlant Description ) Furniture: ( Furniture ID, Piece Description ) Stocks : (MPlant, Fitting ID, Stock ) Ensembles: ( Fitting ID, Furniture ID, Quantity Required ) Fittings : ( Fitting ID, Fitting Description, Quality, Price ) Addresses : ( Address, Customer ID ) Orders : ( Order Number, Address, Date ) Details : ( Order Number, Detail Line, Quantity Ordered, Fitting ID) Discount : ( Quantity Ordered, Discount ) 8
Tables 1. Furniture: ( Furniture ID, Piece Description ) 2. Addresses : ( Address, Customer ID ) 3. Ensembles: ( Fitting ID, Furniture ID, Quantity Required ) 4. Fittings : ( Fitting ID, Fitting Description, Quality, Price ) 5. Orders : ( Order Number, Address, Date ) 6. Details : ( Order, Detail Line, Quantity Ordered, Fitting ID) 7. Stocks : ( MPlant, Fitting ID, Stock ) 8. Plants : ( MPlant, MPlant Description ) 9. Discount : ( Quantity Ordered, Discount ) 9
ER Model (Entities) 10
ER Model 11
Questions? 12
Скачать презентацию Database Management Systems Database Design by Normalization and
da8e35c1dfa4b6227b9db3e4cd21deed.ppt