1 / 26

Database Design

Hele-Mai Haav: CSC210 - Spring*01 CSC230-Spring*03. Database Design. The process of designing a database begins with an analysis of what information the database must hold and the relationships among components of that information.

websterj
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. Hele-Mai Haav: CSC210-Spring*01 CSC230-Spring*03 Database Design

  2. The process of designing a database begins with an analysis of what information the database must hold and the relationships among components of that information. • The structure of the database is called the database schema and it is specified in one of database design languages (e.g. E/R or ODL) • If the design is committed to a form in which it can be put to a DBS, then the database takes physical existence

  3. DATABASE DESIGN IDENTIFY ENTITIES IDENTIFY RELATIONSHIPS CHECK FOR DESIGN PROBLEMS SELECT PRIMARY KEYS CREATE INITIAL RELATIONS APPLY NORMALIZATION PRINCIPLES PRODUCE FINAL DESIGN

  4. UML Object-oriented DBS ODL Ideas Relations Relational DBS E-R Database modeling and implementation

  5. Entity-Relationship Diagrams Entity and entity sets • An entity set is a collection of distinguishable real-world objects with common properties. An entity is a member of an entity set Students, Instructors, Cars... Attributes • An attribute is a data item that describes a property of an entity or a relationship name, registration number, rank….

  6. Relationship • Relationships are connections among two or more entity sets Multiplicity of E-R Relationships many-to-one many-to-many one-to-one

  7. name addr title year movies Stars-in Stars length filmtype Owns Studios E-R schema of movie database name addr

  8. Attributes on Relationships

  9. KEY E-R DIAGRAM KEY PRICE CODE PURPOSE REGION SSN NAME ADDRESS RNO PHONE NAME NAME S/W COMPANY SALES PERSON 1 n REPRESENTS n ONE-TO-MANY DISTRIBUTES m MANY-TO-MANY S/W PACKAGES KEY

  10. ONE-TO-MANY RELATIONSHIP BCS S/W COMPANY IBM HELMES MS REPRESENTS SALES PERSON JUSTAS JAANUS LAURI MERIKE VERA

  11. MANY-TO-MANY RELATIONSHIP HELMES S/W COMPANY IBM BCS MS DISTRIBUTES S/W PACKAGES LOTUS NOTES WIN 95 OS/2 WIN NT OFFICE 97 OFFICE 95

  12. Design Principles Faithfulness • Entity sets and their attributes should reflect reality • Whatever connections are asserted should make sense given what we know about the part of the real world being modeled

  13. Avoiding Redundancy • We should be careful to say everything once only Simplicity Counts • Avoid introducing more elements into your design than are absolutely necessary

  14. Keys in E-R model We underline the attributes belonging to a key for an entity set (primary key is indicated only) title year movies

  15. Relational Database = set of RELATIONS (or TABLES) each with a distinct name Movies, Stars,…. Each relation has a set of ATTRIBUTES (or COLUMNS) with a distinct name within its relation title, year, length, filmtype,...

  16. Relational data model is used by most commercial DBS • Oracle, Informix, Sybase, MS Access • Relational model is very simple

  17. Keys: A key for a relation is a set of attributes such that no two tuples can have the same values for all of their key attributes. In real databases, if there isn't a natural, compact one-attribute key, then keys may be system-generated.

  18. Translating E-R design to relations • Assume each entity set has a specified key • Translation can be fully automated (except determining keys for generated relations in certain cases)

  19. 1. Entity set (strong) translates directly to relation SW Company R-No Name Address Phone R101 IBM Pirita tee 4 6 245 555 R102 Microsoft XYZ 5 5 666 666 R103 BCS Narva mnt 2 6 765 456 R104 Helmes Parnu mnt 123 6 507 567 R106 IE Soft Akadeemia tee 21 6578900

  20. E-R Relationships translate to Relations • Corresponding relation has attribute for key attributes of each entity set (E. S.) that participates in relationship • if the relationship has attributes, then these are also attributes of corresponding relations • renaming attributes is OK (needed if multiple roles in one E. S.

  21. RNOCode Price R101 S100 550 R101 S200 2000 R101 S300 5000 R101 s333 340 R101 S500 3500 R101 S600 8000 R102 S300 4000 R102 S301 3000 R102 s444 4589 R103 S302 3500 R104 S500 6000 R106 S100 600 R106 S401 700 Distribution 2. Binary relationship set between entity sets translates to relation containing keys for entity sets Relationship set with attributes: add attributes to relation for relationship set

  22. What is key for relation? In most general case, key for the relation= all key attributes from all the E.S. involved in relationship • however, the relation’s key excludes attributes from the “one” side if relationship is many-one • for one-to-one relationship, choose which side provides the key for the relation

  23. Combining relations Common case relation for an E.S. E plus the relation for some many-one relationship from E to another E.S. Foreign key SSN Name Region RNo K10100 Lauri Estonia R104 K10150 Vera Estonia R103 K10200 Jaanus Latvia R104 K10300 Justas Lithuania R102 K10500 Merike Estonia R101 K10600 Marion Latvia R101 K20000 Margus Estonia R102 K300400 Edgars Latvia R106 Salespersons

  24. name manuf addr name beers likes drinkers favorite married Example

  25. Drinker(name,addr,….favoritebeerName) beers(name, manuf) likes(DrinkerName,BeerName) married(hasbund,wife)

  26. Student Service Office REGISTRAR Registration Information Registration 4 REGISTER Receipts 1 MANAGE BILLS Special Forms Pay Data Application Financial Information STUDENTS Chosen Courses 3 MANAGE REGISTRATION FORMS 2 MANAGE SCHEDULE Data Money ACCOUNTANT BANK Account Information Example:Registration process at CIUE Course data

More Related