Database Management Systems: An Overview

Database Management Systems: An Overview

Entity Relationship Modeling 1 Outline Data Modeling: Big picture E-R Model Attributes types Relationships connectivity, cardinality strength, participation, degree Entities composite entity supertype/subtype Table Normalization

normal forms 1NF, 2NF, 3NF 2 S511 RDB Project Lifecycle Study Database Environment Define Database Objectives Planning & Analysis Implementation Realize data model in DBMS (tables, forms, queries, reports) Populate database Design Data Analysis & Requirements

Data Modeling & Verification Test, Debug, & Evaluate 3 Basic Modeling Concepts Model Description or analogy used to visualize something that cannot be directly observed -Websters Dictionary - Data Models Relatively simple representation of complex real-world data structures Facilitate communication & enhance understanding Degrees of data abstraction Conceptual Model

global view of data Internal Model DBMS view of data External Model end-user view of data Physical Model machine view of data 4 Degrees of Data Abstraction Conceptual Global view of data identify and describe main data items e.g. E-R diagram

Hardware and software independent Internal Representation of database as seen by DBMS adapt conceptual model to specific DBMS e.g. Access tables Software dependent External Users views of data environment group requirements & constraints subsets into functional modules e.g. student registration module, class scheduling module

Facilitates development & revalidates the conceptual model Physical Lowest level of abstraction determine of physical storage devices and access methods software and hardware dependent 5 Data Abstraction Models Database Systems: Design, Implementation, & Management: Rob & Coronel 6 Entity Relationship Model Main components of the ER Model Entities entity set (table) entity name (noun) is usually written in capital letters

Attributes characteristics of entities attribute domain = set of possible values Relationships association between entities Entity Relationship Diagram (ERD) ER model forms the basis of an ER diagram ERD represents the conceptual view of the database 7 E-R Model: Attributes Simple Cannot be subdivided e.g. age, sex, marital status

Composite Can be subdivided into additional attributes e.g. address street, city, zip Replace with multiple simple attributes Single-valued Can have only a single value e.g. ssn person has one social security number Multi-valued Can have many values e.g. college degree person may have several college degrees

Avoid if possible Derived Can be derived with algorithm e.g. age = (current date - date of birth)/365 Stored vs. Computed store to save CPU cycles & keep track of historical data compute to save storage & use current data 8 E-R Model: Attributes Multi-valued attributes 1. Replace with multiple single-valued attributes. Car_Color Car_TopColor, Car_TrimColor, Car_BodyColor, Car_InteriorColor could be problematic

2. Create a new entity composed of original multi-valued attributes components Car_Color CAR_COLOR (Car_Vin, Col_Section, Col_Color) Database Systems: Design, Implementation, & Management: Rob & Coronel 9 E-R Model: Relationships Relationship = Association between entities Connectivity Connectivity & Cardinality are established by business rules. Type/Classification of Relationships 1:1, 1:M, M:N Cardinality (min, max) = minimum/maximum number of occurrences of the related entity

Database Systems: Design, Implementation, & Management: Rob & Coronel 10 Relationship Strengths Existence Dependence Entitys existence depends on the existence of related entities. Existence-independent entities can exist apart from related entities. e.g. EMPLOYEE claims DEPENDENT A dependent cannot exist without an employee. DEPENDENT is existence-dependent on EMPLOYEE. Weak (non-identifying) Relationship PK of related entity does not contain PK component of parent entity One entity is existence-independent on another.

e.g. COURSE (CRS_CODE, DEPT_CODE, CRS_DESCRIPTION, CRS_CREDIT) CLASS (CLASS_CODE, CRS_CODE, CLASS_SECT, CLASS_TIME, ) Strong (identifying) Relationship PK of related entity contains PK component of parent entity One entity is existence-dependent on another e.g. COURSE(CRS_CODE, DEPT_CODE, CRS_DESCRIPTION, CRS_CREDIT) CLASS(CRS_CODE, CLASS_SECT, CLASS_TIME, ) 11 Relationship Strengths weak relationship strong relationship Database Systems: Design, Implementation, & Management: Rob & Coronel Crows Foot model

Dashed relationship line to indicate weak relationship. Solid relationship line & clipped corners to indicate strong relationship. Double-walled entity in Chens model Database designer often determine the nature of relationship. Best suited for database transaction, efficiency, and information requirements Based on business rules 12 Relationship Participation Optional Participation Entity occurrence does not require a corresponding occurrence in related entity. e.g. COURSE generates CLASS (some course may not generate a class) Minimum cardinality of the optional entity is 0.

Mandatory Participation Entity occurrence requires corresponding occurrence in related entity. e.g. COURSE generates CLASS (each course generates one or more classes) Minimum cardinality of the mandatory entity is 1. CLASS is optional to COURSE CLASS is mandatory to COURSE Database Systems: Design, Implementation, & Management: Rob & Coronel 13 Relationship: Strength vs. Participation Relationship Strength Relationship Participation

Depends on the formulation of primary key. Depends on the business rule. Examples EMPLOYEE has DEPENDENT Strong & Optional A dependent cannot exist without an employee DEPENDENT is existence-dependent on EMPLOYEE An employee may not have a dependent DEPENDENT is optional to EMPLOYEE PHD_STUDENT teaches CLASS Weak & Mandatory A class can exist without a doctoral student CLASS is existence-independent on PHD_STUDENT A doctoral student must teach at least one class

CLASS is mandatory to PHD_STUDENT 14 Relationship: Weak Entities Database Systems: Design, Implementation, & Management: Rob & Coronel Strong vs. Weak entities Strong Entity = existence-independent entity Weak Entity existence-dependent entity in a strong relationship inherits all or part of its primary key from parent entity entity w/ clipped corners in CF model, double-walled in Chen model 15 Relationship Degree Relationship Degree indicates the number of associated entities. Unary Relationship

Relationship exists between occurrences of same entity set e.g., Recursive relationship Binary Relationship Two entities associated Most common higher-order relationships are often decomposed into binary relationships Ternary Three entities associated e.g., CONTRIBUTOR, RECIPIENT, FUND need ternary relationship for a recipient to identify the source of fund Database Systems: Design, Implementation, & Management: Rob & Coronel 16 Composite Entities

Composite Entity (i.e., Bridge Entity) Transforms a M:N relationship into two 1:M relationships Contains primary keys of the bridged entities May also contain additional attributes that play no role in connective process Typically has strong relationships with the bridged entities Database Systems: Design, Implementation, & Management: Rob & Coronel 17 M:N to 1:M Conversion CLASS STUDENT STU_ID STU_NAME CLS_ID 1234 John Doe

10012 1234 John Doe 10014 2341 Jane Doe 10013 2341 Jane Doe 10014 2341 Jane Doe 10023 STU_ID STU_NAM E

1234 John Doe 2341 Jane Doe STUDEN T 1. 2. CLS_ID CRS_NAM E CLS_SEC T STU_ID 10012 L546 1

1234 10013 L546 2 2341 10014 L548 1 1234 10014 L548 1 2341 10023 L571 1

2341 B CLS_I D CRS_NAM E CLS_SE C 2341 A 10012 L546 1 10014 1234 C 10013

L546 2 10014 2341 A 10014 L548 1 10023 2341 A 10023 L571 1 CLS_ID

STU_ID ENR_GRD 10012 1234 10013 CLASS ENROL L Move the foreign key columns to create a bridge table & add attributes if needed. Collapse the duplicate records in remaining tables. 18 Entity Supertypes & Subtypes Problem: Unshared characteristics of certain entity subtypes e.g. PILOT vs. EMPLOYEE Solution:

Generalization hierarchy higher-level Supertype (parent) and lower-level Subtype (child) entities Supertype and Subtype maintain 1:1 relationship Supertype has shared attributes Subtypes have unique attributes inherit attributes and relationships of the supertype often comprise of unique and disjoint entities (G symbol) e.g. EMPLOYEE PILOT, MECHANIC, ACCOUNTANT sometimes comprise of overlapping entities (Gs symbol) e.g. EMPLOYEE PROFESSOR, ADMINISTRATOR 19 Subtypes: Overlapping vs. Non-overlapping Non-overlapping (Disjoint)

Overlapping Database Systems: Design, Implementation, & Management: Rob & Coronel 20 Developing ERD Iterative Process 1. Create detailed narrative of organizations description of operations 2. Identify business rules based on description of operations 3. Identify main entities and relationships from business rules 4. Develop initial ERD 5. Identify attributes and primary keys that adequately describe entities

6. Revise and review ERD 21 ERD Example: Narrative Narrative of operational environment Tiny College is divided into several schools Each school is composed of several departments Each school is administered by a dean Each dean is a member of administrators group A dean is also a professor and may teach classes

Administrators and professors are employees Each department offers several courses Each course may have several sections (classes) Each department has many professors and students One of the professors chairs the department Each professor may teach up to 4 classes A student may enroll in several classes Each student has an advisor in his/her department Each student belongs to only one department 22 ERD Example: Supertype/Subtype - Each school is administered by a dean - Each dean is a member of administrators group - A dean is also a professor and may teach classes - Administrators and professors are employees Database Systems: Design, Implementation, & Management: Rob & Coronel Professors and administrators have unique characteristics not present in other employees EMPLOYEE supertype, PROFESSOR & ADMINISTRATOR (overlapping) subtypes Professors and administrators have same set of characteristics

collapse PROFESSOR and ADMINISTRATOR entities 23 ERD Example: ERD segment 1 Database Systems: Design, Implementation, & Management: Rob & Coronel Professors are employees A professor may be a dean Each school is administered by a dean Each school is composed of several departments 24 ERD Example: ERD segment 2 & 3 Database Systems: Design, Implementation, & Management: Rob & Coronel Each department offers several courses Each course may have several sections (classes)

25 ERD Example: ERD segment 4 & 5 Database Systems: Design, Implementation, & Management: Rob & Coronel Each department has many professors One of the professors chairs the department Each professor may teach up to 4 classes 26 ERD Example: ERD segment 6 & 7 Database Systems: Design, Implementation, & Management: Rob & Coronel A student may enroll in several classes Each department has many students Each student belong to only one department 27

ERD Example: ERD segment 8 & 9 Database Systems: Design, Implementation, & Management: Rob & Coronel Each student has an advisor Class is held in class rooms 28 ERD Example: ERD components Database Systems: Design, Implementation, & Management: Rob & Coronel 29 ERD Example: Merging ERD segments 30 ERD Example: Completed ERD Database Systems: Design, Implementation, & Management: Rob & Coronel 31

Recently Viewed Presentations

  • 2.5 Linear Equations and Formulas: - Miami Arts Charter

    2.5 Linear Equations and Formulas: - Miami Arts Charter

    Words: Look at the figure, multiply the number of rectangles by 2 to get the total lengths of the top an bottom sides of the combined figure. Then add 2 times the length of the left and right sides of...
  • Diapositiva 1 - QNET

    Diapositiva 1 - QNET

    Se funda la Central Unitaria de Trabajadores (CUT). Su primer presidente es el dirigente sindical Clotario Blest, esta es la organización gremial más importante proyectada por dirigentes obreros hasta el día de hoy. Se anuncia la disolución del MEMCH, movimiento...
  • Agile Adoption Survey 2008 - AmbySoft

    Agile Adoption Survey 2008 - AmbySoft

    Effectiveness of Agile and CMMI We found the following success rates: 57.6% for CMMI-compliant traditional projects (107 responses) 56.8% for non-CMMI Agile projects (114 responses) 54.8% for non-CMMI traditional projects (172 responses) 53.4% for CMMI-compliant Agile projects (70 responses) Statistically...
  • DR. I. SELVARAJ SOCIOLOGY STUDY OF SOCIAL CAUSES

    DR. I. SELVARAJ SOCIOLOGY STUDY OF SOCIAL CAUSES

    DR. I. SELVARAJ SOCIOLOGY STUDY OF SOCIAL CAUSES AND CONSEQUENCES OF HUMAN BEHAVIOUR MEDICAL SOCIOLOGY PROFESSIONAL ENDEAVOUR DEVOTED TO SOCIAL EPIDEMIOLOGY, STUDY OF CULTURAL FACTORS AND SOCIAL RELATIONS IN CONNECTION WITH ILLNESS, AND THE SOCIAL PRINCIPLES IN MEDICAL ORGANISATION AND...
  • Introduction to Weather 5 Clouds, Rain and Moisture

    Introduction to Weather 5 Clouds, Rain and Moisture

    Image: NEODAAS, University of Dundee. Geostationary satellite picture from 12:00 on 21 May 2013. Clouds are found all around the world. We have seen images like this before. The tropics are dominated by deep, convective cloud; the mid-latitudes by shallower,...
  • Grid, Globus Toolkit, and OGSA

    Grid, Globus Toolkit, and OGSA

    Open Grid Services as an Enabler of Future Networked Applications Ian Foster Argonne National Laboratory University of Chicago http://www.mcs.anl.gov/~foster
  • Learning Theory Group Presentation

    Learning Theory Group Presentation

    Benner clearly acknowledges that her thinking in nursing has been influenced greatly by Virginia Henderson (Brykczynski, 2010, p. 137) and that she adapted the Dreyfus model of Skill Acquisition to clinical nursing practice (Brykcyznski, 2010, p. 140). ... Learning Theory...
  • Convergent Margin Volcanism Three topics 1. MORs versus

    Convergent Margin Volcanism Three topics 1. MORs versus

    Ba is lowered by the diluting effect of higher extent of melting, but raised by the concentration oif flux. These two effects offset and ba id "normal" or just the same as in Guatemala and El Salvador. Nicaragua may also...