1 / 17

DATABASE DESIGN

DATABASE DESIGN. DATABASE DESIGN AS A MODELING PROCESS Models as vehicles for understanding reality DATABASE DESIGN AS AN INTUITIVE PROCESS Satisficing vs. Rationality DATABASE DESIGN AS AN ITERATIVE PROCESS Prototyping. THREE SCHEMA DATA MODEL. Data Model

salali
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 • DATABASE DESIGN AS A MODELING PROCESS Models as vehicles for understanding reality • DATABASE DESIGN AS AN INTUITIVE PROCESS Satisficing vs. Rationality • DATABASE DESIGN AS AN ITERATIVE PROCESS Prototyping

  2. THREE SCHEMA DATA MODEL Data Model Away of representing data that models some part or parts of an organization. Schema Simply, a representation of something, perhaps a blueprint or a flowchart. 3-Schema Model Internal Schema (Physical) External Schema Conceptual Schema (Logical) External Schema Database External Schema

  3. THREE SCHEMA DATA MODEL (Cont’d) • External Schema: User views; Axiom: Different users have different needs and different views of the database • Conceptual Schema: Logical view of the DB containing all data and relationships • Internal Schema: Physical representation of the Conceptual Schema in a target DBMS

  4. CONCEPTUAL SCHEMA / DATA MODEL METHODOLOGIES • Entity-Relationship (E-R):developed by Chen (1976); still the most widely used technique • IDEF1X:National standard E-R model • Unified Modeling Language (UML):object-oriented system development methodology; object friendly but not DB friendly • Semantic Object Modeling (SOM):developed by Kroenke; uses objects to model the semantics of the data, i.e. what the data means; nice crossover between E-R and UML • Relational Model:data expressed as tables; not semantically expressive; not as accessible to end users; other methodologies are generally used for modeling, then transformed into relations at implementation time

  5. EXTENDED E-R MODEL • Basic E-R Model

  6. CUSTOMER entity class: CustNumber CustName Address City State Zip ContactName PhoneNumber Two instances of CUSTOMER entity class: 1234567890 Ajax Manufacturing Jefferson Dance Club 123 Elm St 345-10th Avenue Memphis Boston TN MA 32455 01234 P. Schwartz Frita Bellingsley 223-5567 210-8896 ENTITY CLASS and ENTITY INSTANCE Entities are simply things which can be identified in the environment being modeled.

  7. ATTRIBUTES • Attributes describe an Entity’s characteristics • Attributes may be simple (e.g., Zip) or composite (e.g., Address) • Attributes may be single-valued (e.g., Zip) or multi-valued (e.g., PhoneNumber) • Each Attribute has a domain of possible values which it can assume for any Entity instance (e.g., State may be any of 50 two-character abbreviations; PhoneNumber may be (nnn) nnn-nnnn where n is an integer between 0 and 9)

  8. RELATIONSHIPS • Relationships involve associations between two or more entity classes • Relationships may involve more than 2 entities, however we focus upon binary relationships which involve just 2 entities. • There are 3 types of binary relationships: • One-to-One (1:1) • One-to-Many (1:N) • Many-to-Many (M:N)

  9. BINARY RELATIONSHIPS • One-to-One (1:1) • One-to-Many (1:N) • Many-to-Many (M:N) IS_ASSIGNED_TO EMPLOYEE AUTO 1:1 IS_ASSIGNED RESIDES_IN DORMITORY STUDENT 1:N HOUSES HAS_MEMBERS STUDENT CLUB M:N IS_MEMBER_OF

  10. RELATIONSHIP CARDINALITIES • Relationships have maximum cardinality and minimum cardinality associated with them which are the maximum and minimum number of entity instances involved in the relationship respectively. • The maximum cardinalitynumbers are those contained within the relationship diagram. • The minimum cardinalitynumbers are either 0 or 1 and are shown as ovals or hash marks on the relationship lines respectively. • “A DORMITORY HOUSES at least 1 STUDENT and can HOUSE many STUDENTs; simultaneously, a STUDENT may or may not reside in a DORMITORY, but can RESIDE in, at most, one DORMITORY.” RESIDES_IN DORMITORY STUDENT 1:N HOUSES

  11. WEAK ENTITIES • WEAK ENTITY cannot exist unless another type of entity already exists. EMPLOYEE FAMILY DEPENDENT 1:N EMPLOYEE may exist without any FAMILY DEPENDENTs, but not vice versa.

  12. ID-DEPENDENT ENTITY • A special case of WEAK ENTITY where the identifier of the dependent entity includes the identifier of the entity upon which it depends. BUILDING 1:N APARTMENT Identifier: BldgName Identifier: {BldgName, AptNum}

  13. SUBTYPE ENTITIES • Previous binary relationships are “HAS_A” relationships • Subtype entities are used to represent “IS_A” relationships (also referred to as “specialization hierarchies” or “generalization hierarchies”) • E.g., “a zebra is an ungulate is a mammal is an animal” is a specialization hierarchy going down the tree and a generalization hierarchy going up the tree • A Subtype entity is a special case of an already existing entity • Each Subtype “inherits” all the attributes of its parent type.

  14. CLIENT 1 Î Î Î CLIENT entity class: ClientNumber ClientName AmountDue Address SocialSecurityNumber ManagingPartnerName TaxIdentificationNumber ContactPerson Phone INDIVIDUAL CLIENT PARTNERSHIP CLIENT CORPORATE CLIENT CLIENT ClientNumber ClientName AmountDue INDIVIDUAL CLIENT Address SocialSecurityANumber PARTNERSHIP CLIENT ManagingPartnerName Address TaxIdentificationNumber CORPORATE CLIENT ContactPerson Phone TaxIdentificationNumber SUBTYPE ENTITY (EXCLUSIVE) “An INDIVIDUAL CLIENT IS_A CLIENT.” “Each CLIENT must be one (minimum cardinality = 1), and only one (maximum cardinality = 1), of INDIVIDUAL, PARTNERSHIP, or CORPORATE.”

  15. CLIENT m Î Î Î CLIENT USING PCs CLIENT USING MINI CLIENT USING MAINFRAME SUBTYPE ENTITY (NON-EXCLUSIVE) “A CLIENT USING PCs IS_A CLIENT.” “A CLIENT need not be using PCs, MINIs, or MAINFRAMEs (minimum cardinality = 0), but can use any combination of those computers (the “m” on the arc).”

  16. EMPLOYEE Î TRUCK-ASSGN TRUCK ENGINEER 1:1 ENG-SKILL 1:N SERVICE-PROVIDER . . 1 N SERVICE ENGINEER- CERTIFICATION FEE CLIENT-SERVICE . . N M QUAL-ENGINEER .. N 1 CLIENT CERTIFICATION 1:N REFERRED-BY EXTENDED E-R EXAMPLE

More Related