CSIS 254 Oracle Database Design Day 3

Today’s Agenda • Questions on homework • Review key concepts from last week • Continue with Analysis stage of SDLC – Entity Life History Diagrams – Data Flow Diagrams – Relational Databases

Seven System Development Stages (Review) The Software Development Life Cycle – Strategy / Scope – Analysis – Design – Programming – Testing – Go Live – Production & Maintenance

The Entity Relationship Model (ERM) Entities • Entities are people, places, things, or events of interest to our system that can be divided into distinct classes – An entity may describe physical things, or it may be conceptual • Entity types (or sets) are collections of entities having similar properties • Each occurrence of an entity type can also be called an instance or an object

The Entity Relationship Model (ERM) Attributes • Attributes are relevant properties or characteristics that describe the entity instances in an entity set • An attribute may be simple (atomic) or composite • An attribute may also be single-valued or multivalued • The values of an attribute in an entity instance may be restricted: – mandatory, uniqueness, formatting, restricted domain. . .

The Entity Relationship Model (ERM) Keys • An attribute (or combination of attributes) that uniquely identify an entity is called a key attribute • Key attributes are limited to the minimum set of attributes that can uniquely identify an entity • When an entity has more than one set of key attributes, they are called candidate keys • When a key does not contain any descriptive data about the entity, it is called a surrogate or natural key, or an identifier

The Entity Relationship Model (ERM) Derived Attributes • A derived attribute is one that can be determined based upon the values of other attributes in the entity object or related entity objects • These might it be beneficial for us to include derived attributes in our physical database design – Performance – Redundancy – Security – Consistency (? ? ) – Snapshots in time (as of)

Engineering Method Entity Diagrams Entity Name Attributes PALOMAR STUDENT Student ID Full Name First Name Middle Name Last Name Social Security Number Home Address Home Phone High Schools

Engineering Method Entity Diagrams PUBLISHER A familiar example Publisher Id Publisher Name Street Address City State Key Attribute

The Entity Relationship Model (ERM) Weak Entity Types • Entity types that do not have key attributes of their own are called weak entity types • Weak entities are always related to specific entities from another entity type -- the owner • A weak entity must have a partial key or an identifier, which is a set of attributes that can be used to uniquely identify each instance from other instances belonging to the same owner

The Entity Relationship Model (ERM) Subtypes and Supertypes • When different entity types share the same attributes, we can break out the common attributes into Supertypes, leaving the attributes that distinguish entities into Subtypes – There must be at least one attribute that distinguishes subtypes common to a supertype – An entity instance must belong to one and only one subtype

The Entity Relationship Model (ERM) Relationships • A relationship is an association between two or more entities • Each relationship has an inverse relationship • Participation or optionality refers to whether each entity instance must occur in the relationship (i. e. , is it a mandatory relationship) • The degree of a relationship is the number of participating entity types

Cardinality • Cardinality is the maximum number of occurrences of each entity type that can occur in a relationship • In binary relationships: – A 1 -to-1 relationship occurs when an entity of one type can occur in (at most) one relationship with an entity of the other type – A 1 -to-many relationship occurs when an entity of one type can occur in (at most) one relationship with the other type, but entities or the second type can be related to any number of entities of the first type. – A many-to-many relationship occurs when no restrictions exist on the number of entities that can participate in a relationship

Relationships ER Diagrams In ERDs, entities that are related are connected by lines. (A 1 -to-1 relationship is shown below. ) SOCIAL SECURITY NUMBER PALOMAR STUDENT ID

Relationships ER Diagrams Crow’s feet are used at the “many” end in 1 -to-many relationships. COUNTRY has had as Head of State RULER

Relationships ER Diagrams Both sides of the line will have crow’s feet in a manyto-many relationship. CAR MODEL is available using COLOR GROUP

Relationships ER Diagrams A small circle is placed at the end of an optional participation, and a small perpendicular line placed at the end of a mandatory participation. WOMAN is biological mother of CHILDREN

Relationships ER Diagrams Subtype entities reference their supertypes using arrows, rather than lines. LICENSED VEHICLE CAR TRUCK BIKE

Relationships ER Diagrams All entity occurrences must fall into one of the subtypes We add an “other” category to include RV’s, planes, motorcycles, etc. LICENSED VEHICLE CAR TRUCK OTHER BIKE

ER Diagrams Relationships are (optionally) shown in diamonds connected to the associated entities NATIONAL LEAGUE CHAMP meet in Super Bowl AMERICAN LEAGUE CHAMP

Reflexive (Recursive) Relationships • Occur when an entity has a relationship with another entity in the same entity set • We might need to add business rules prohibiting certain recursive conditions • If participation is mandatory, then the relationship is circular • If the relationship is hierarchical, then participation is optional

ER Diagrams Reflexive relationships are drawn with a single entity box is married to PERSON

Entity Life History Diagrams • Entity-Relationship Diagrams describe the data in our system, but have no sense of order or sequence • Functional hierarchies or specifications describe processes • Entity Life History Diagrams (ELH) pull the two together for each entity – They are also commonly known as State Transition Diagrams, as entities pass from creation to their final states (archived, deleted, distributed, etc. )

Generic Entity Life History Diagram Entity Creation Deletion or Archive Transactions Event 1 o Event A * Event 2 Event 3 o Event B

Entity Life History Notes • The single entity being examined appears at the top of the diagram • The rest of the diagram is comprised of events, which create, modify, or delete instances of the entity • A little ‘o’ in the upper-right corner of an event means “option” or selection • An asterisk ‘*’ in the upper-right corner means “iteration” or repetition • Basic flow of diagram is left-to-right

Entity Life History Example Payroll Check Job Cards Transferred Hours Summarized 2 years pass Processing Calculations Performed o Approved Archive Reviews* Made Check Printed o Rejected

Data Flow Diagrams • Are used to uncover as many data elements as possible in our data dictionary • Document the system from the data’s viewpoint (i. e. , are data-centric) • Contain several entities on each diagram (unlike Entity Life History Diagrams) • Differentiate manual from automated processes • Show both external and internal processes

Data Flow Diagram Example Purchase Requisition Fulfillment EMPLOYEE D 1 Inventory confirm place req. P 1 issue req. update stock create order match to invoice P 3 receive item review & approve BUYER D 2 Accts Payable ship item P 2 issue P. O. SUPPLIER send to

Data Flow Diagram Notes • Rectangles represent entities • Open-ended rectangles represent databases or data stores, and are labeled by a small box on their left • Rounded rectangles represent processes, with the top part of the rectangle having a process number • Arrows represent data flows (either electronic or physical) • Flow is generally from left-to-right – Entities may appear more than once in the diagram to preserve the flow and directional nature

Data Flow Diagram Rules • All processes must have at least one flow of data coming in and one going out • All processes on the diagram should modify the incoming data and create new forms of outgoing data • Each data store and each external entity must be involved with at least one data flow

Relational Databases (Review) • In relational databases, all data is stored in tables, which correspond roughly to entities • Each table is two-dimensional, consisting of rows and columns • Each row in a table, called a t-uple, corresponds to an occurrence of the entity • Columns in each table contain similar data across all rows in the table

Relational Database Example The following table is an example of a relational table describing classes that students have taken at a mythical college used in the rest of this lesson Student Id Student Name 0194327 1850243 8502432 7402943 Joe Adams Jane Smith Ida Know Eunice Eye Course Id CSIS-840 CSIS-824 CSIS-740 CSIS-941 CSIS-840 CSIS-184 CSIS-824 Course Name VB Concepts Intro to C++ Oracle Admin Systems Des. VB Concepts Networks Power. Point Grade Term Teacher C B A B B A W Spr-02 Fal-02 Spr-03 Fal-02 Spr-02 Sum-03 Spr-02 Wilkins Smythe Wallace Evans Wolkins Farmer Simpson

Relational Database Example • Each row (or t-uple) in the table describes a Class taken by a Student in a term at our college • The data in each column is consistent throughout the table • However, there are three inconsistencies in the table itself. Can you find them?

Primary Keys (Review) • Each row in a table has a primary key, which is the column or set of columns identified to our DBMS that uniquely identifies it from every other row in the table • No attribute value in a primary key can be NULL • A table can have only one primary key • If a primary key is not specified, Oracle supplies one • What would be the primary key for our sample database?

Foreign Keys (Review) • An attribute (or group of attributes) in a table can also be a foreign key, meaning that it references the primary key (or at least unique attribute) to another table • An example would be a Customer Id attribute on an invoice header, which would reference the customer account information for that invoice