1 / 34

Database Design

Database Design. Relational Database A collection of normalized relations (tables). The Three Level ANSI-SPARC Architecture DBTG (Data Base Task Group) Appointed by the Conference on Data Systems and Languages in 1971 Two level approach

hachi
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 Relational Database A collection of normalized relations (tables).

  2. The Three Level ANSI-SPARC Architecture DBTG (Data Base Task Group) Appointed by the Conference on Data Systems and Languages in 1971 Two level approach SPARC: Standards Planning And Requirement Committee ANSI/X3/SPARC, 1975 Based on IBM's view For any database system Data Independence

  3. The Three Level ANSI-SPARC Architecture … External Level View 1 View 2 View 3 Conceptual schema Conceptual Level Internal schema Internal Level Database

  4. Phases of Database Design • Conceptual Database Design • Logical Database Design The two phases above are for External and Conceptual schemas • Physical Database Design

  5. Conceptual Database Design • Users' views • Understanding of the meaning of the data Common Sense Business rules Do not assume anything Students without any classes at UWP Classes without any students • E-R Model Formal document to specify the requirements Entity and Relationship • Independent of the target data model (relational, OO)

  6. Logical Database Design • Designer's view • Based on the target data model Relational • Normalization

  7. Physical Database Design • Base relations and constraints • File Organization and Storage Structure (Appendix C) • Others

  8. Conceptual Database Design E-R Model (Ch 11) High level conceptual data model Top-Down approach Widely used but not the only model UML (Unified Modeling Language) Draw ER diagrams

  9. Entity Types A group of objects with the same properties, which are identified by the enterprise as having an independent existence. Similar to Class in OOP Class is an implementation of Entity

  10. Physical Existence • Branch • Staff • PropertyForRent • Client • PrivateOwner • Customer • Product • Supplier

  11. Conceptual Existence • Viewing • Inspection • Sale • Work Experience

  12. Entity Occurrence A uniquely identifiable object of an entity type. Class and Object

  13. Diagrammatic Representation of Entity Types • Rectangle • The first letter of the entity name is upper case Branch Staff

  14. Relationship Types(Not Relation) A set of meaningful associations among entity types. Branch Has Staff Staff Manages Branch PrivateOwner Owns PropertyForRent

  15. Relationship Occurrence A uniquely identifiable association, which includes one occurrence of each participating entity type. Example Branch B003 has staff SG5. Owner O321 owns property P1045.

  16. Semantic Net for Relationship Occurrence r1 B003 SG5 r2 SG37 r3 B007 SA9 Branch Has Staff

  17. Diagrammatic Representation of Relationship Types Directed line with the relationship name Has Branch Staff

  18. Degree of Relationship Type The number of participating entity types in a relationship Binary Ternary Quaternary

  19. A Ternary Relationship Registers Branch Staff Client

  20. A Quaternary Relationship A solicitor arranges a bid on behalf of a buyer supported by a financial institute. Solicitor Arranges Buyer Bank Bid

  21. Recursive Relationship A relationship type where the same entity type participates more than once in different roles. Staff (Supervisor) supervises Staff (Supervisee) Roles Supervisor Supervisee

  22. Recursive Relationship Supervisor Supervises Staff Supervisee

  23. Multiple Relationships between the Same Entity Types Branch Has Staff (Staff Members) Staff (Manager) Manages Branch

  24. Multiple Relationships between Entity Types Manages Branch Staff Has

  25. Relationship or Entity? • Viewing As Entity in DreamHome example • Could be a Relationship Client Views PropertyForRent • It can be treated either way The final design will be the same

  26. Attributes A property of an entity type or a relationship type. Similar to the fields of a class PropertyForRent propertyNo address (street, city, state, zipcode) type rooms rent . . .

  27. Attributes of a Relationship Advertises Date Cost Advertises NewsPaper PropertyForRent

  28. Domain of Attribute The set of allowable values for one or more attributes PropertyForRent propertyNo: String (size) type: ‘Flat’, ‘House’ rooms: integer, 1 - 15 rent: integer, 150 – 1000 . . .

  29. Simple and Composite Attribute Simple attribute An attribute composed of a single component position salary GPA Composite attribute An attribute composed of multiple components name : firstName, lastName, middle address: street, city, state, zipcode DOB : year, month, day workExperience: organization, position, start, end

  30. Single-valued and Multi-valued Attribute Single-valued: An attribute that holds a single value for each occurrence of an entity type branchNo name DOB GPA The value could change from time to time, but one value at any given time. Multi-valued: An attribute that can hold multiple values for each occurrence of an entity type phoneNo studentMajor workExperience

  31. Composite and Multi-valued Attribute Composite attribute could be single-valued or multi-valued name: single-valued workExperience: multi-valued Multi-valued attribute could be simple or composite phoneNo: simple workExperience: composite

  32. Composite and Multi-valued Attribute Simple attribute could be single-valued or multi-valued salary: single-valued major: multi-valued Single-valued attribute could be simple or composite salary: simple DOB: composite

  33. Derived Attribute An attribute that represents a value that is derivable from the values of a set of related attributes, not necessarily in the same entity type. Age Total Number of staff members Deposit of Lease is twice the rent

  34. Required or Optional Attribute Required Attributes studentID Name DOB . . . Optional Attributes phoneNo Major . . .

More Related