DATABASE DESIGN v DATABASE DESIGN AS A MODELING

DATABASE DESIGN v DATABASE DESIGN AS A MODELING PROCESS Models as vehicles for understanding reality v DATABASE DESIGN AS AN INTUITIVE PROCESS Satisficing vs. Rationality v DATABASE DESIGN AS AN ITERATIVE PROCESS Prototyping

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

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

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

EXTENDED E-R MODEL v Basic E-R Model COMPONENT ENTITY PART OF SPEECH GRAPHICAL FIGURE EXAMPLE Noun OFFICER, SHIP, WEAPON ATTRIBUTE Adjective or Noun Weapon. Id, Weapon. Type, Range IDENTIFIER Adjective or Noun Weapon. Id Verb SHIP_HAS_WEAPONS RELATIONSHIP

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

ATTRIBUTES v v 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. , Phone. Number) 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; Phone. Number may be (nnn) nnn -nnnn where n is an integer between 0 and 9)

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

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

RELATIONSHIP CARDINALITIES v v v 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 cardinality numbers are those contained within the relationship diagram. The minimum cardinality numbers are either 0 or 1 and are shown as ovals or hash marks on the relationship lines respectively. RESIDES_IN DORMITORY 1: N STUDENT HOUSES v “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. ”

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

ID-DEPENDENT ENTITY v A special case of WEAK ENTITY where the identifier of the dependent entity includes the identifier of the entity upon which it depends. BUILDING Identifier: Bldg. Name 1: N APARTMENT Identifier: {Bldg. Name, Apt. Num}

SUBTYPE ENTITIES v v v 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.

SUBTYPE ENTITY (EXCLUSIVE) CLIENT entity class: Client. Number Client. Name Amount. Due Address Social. Security. Number Managing. Partner. Name Tax. Identification. Number Contact. Person Phone CLIENT Î INDIVIDUAL CLIENT 1 Î PARTNERSHIP CLIENT Î CORPORATE CLIENT Client. Number Client. Name Amount. Due INDIVIDUAL CLIENT Address Social. Security. ANumber PARTNERSHIP CLIENT Managing. Partner. Name Address Tax. Identification. Number CORPORATE CLIENT Contact. Person Phone Tax. Identification. Number “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. ”

SUBTYPE ENTITY (NON-EXCLUSIVE) CLIENT Î CLIENT USING PCs m Î CLIENT USING MINI Î CLIENT USING MAINFRAME “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). ”

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