CIS 330 - MIDTERM

•Relationships

- Between entities ONLY THREE TYPES: - 1 to many - many to many - one to one can also have zero

•Mapping Weak Entities (slide 18, ch 4)

-Becomes a separate relation with a foreign key taken from the superior entity -Primary key composed of: ▪Partial identifier of weak entity ▪Primary key of identifying relation (strong entity)

Entities

-Entity instance - person, place, object, event, concept (often corresponds to a row in a table) -Entity Type - collection of entities (often corresponds to a table)

•Data models

-Graphical diagram capturing nature and relationship of data -Enterprise Data Model - high-level entities and relationships for the organization -Project Data Model - more detailed view, matching data structure in database or data warehouse

Mapping Associative Entities (slide 28, ch 4)

-Identifier Not Assigned ▪Default primary key for the association relation is composed of the primary keys of the two entities (as in M:N relationship) -Identifier Assigned ▪It is natural and familiar to end-users Default identifier may not be unique

Multivalued attribute

-May take on more than one value for a given entity (or relationship) instance -An employee can have more than one skill {Skill}

•Entities

-Noun form describing a person, place, object, event, or concept -Composed of attributes

•Mapping Ternary (and n-ary) Relationships (slide 39, ch 4)

-One relation for each entity and one for the associative entity -Associative entity has foreign keys to each entity in the relationship

•Mapping Supertype/Subtype Relationships (slide 43, ch 4)

-One relation for supertype and for each subtype -Supertype attributes (including identifier and subtype discriminator) go into supertype relation -Subtype attributes go into each subtype; primary key of supertype relation also becomes primary key of subtype relation 1:1 relationship established between supertype and each subtype, with supertype as primary table

•Mapping Binary Relationships (slide 21, ch 4)

-One-to-Many - Primary key on the one side becomes a foreign key on the many side -Many-to-Many - Create a new relation with the primary keys of the two entities as its primary key -One-to-One - Primary key on mandatory side becomes a foreign key on optional side

•Mapping Unary Relationships (slide 35, ch 4)

-One-to-Many - Recursive foreign key in the same relation -Many-to-Many - Two relations: ▪One for the entity type ▪One for an associative relation in which the primary key has two attributes, both taken from the primary key of the entity

Attributes

-Properties or characteristics of an entity or relationship type (often corresponds to a field in a table)

Prototyping

-Rapid application development (R A D) -Cursory attempt at conceptual data modeling -Define database during development of initial prototype -Repeat implementation and maintenance activities with new prototype versions

Relationships

-Relationship instance - link between entities (corresponds to primary key-foreign key equivalencies in related tables) -Relationship type - category of relationship; link between entity types

•Mapping Regular Entities to Relations (slide 13, ch 4)

-Simple attributes: E-R attributes map directly onto the relation -Composite attributes: Use only their simple, component attributes -Multivalued attributes: Become a separate relation with a foreign key taken from the superior entity

•Attribute Inheritance:

-Subtype entities inherit values of all attributes and relationships of the supertype -An instance of a subtype is also an instance of the supertype

1) if the first cardinality is a 1, what is it? 2) if the first cardinality is a zero, what is it?

1) mandatory 2) optional

1) entities become _____ in the database 2) fields become ____ 3) records are ____

1) tables 2) columns 3) rows (ONE RECORD = COMPLETE ROW, ALL FIELDS)

Supertype:

A generic entity type that has a relationship with one or more subtypes

multivalued vs compositve attributes

A multivalued attribute is not the same as a composite attribute, although novices may confuse these terms. A composite attribute is one that has many parts, such as an address composed of street, city, state, and zip. By contrast, a multivalued attribute is one that can have many different values, such as an employee being able to do many things.

data vs information vs metadata

Data by itself is not very useful. It is only after the data has been processed, summarized, and organized that it becomes useful for decision makers and knowledge workers. Processed data becomes information, which is often made available to users in the form of reports or graphical displays. Metadata really pertains to the underlying structure of the data. When you design a database, you are specifying its metadata. When you populate the database, you are putting data into it.

t/f: null + 1 = 1

FALSE null is the absence of a value, NOT zero. null + 1 = null

t/f: can use either "has" or "have" in your relationship names

FALSE use other words. either two words (submits -> submitted by) or one word in middle (Registers)

relationship degrees

unary binary ternary n-ary

attributes vs fields

virtually the same in the ERD diagram, but once the ERD becomes tables, attributes become fields

t/f: there can only be 1 primary key

TRUE in composite attributes, there is still only 1 PK (both are in the same box). There are 2 attributes, but they make up 1 PK

Criteria for Identifiers

•Choose Identifiers that -Will not change in value -Will not be null •Avoid intelligent identifiers (e.g., containing locations or people that might change) •Substitute new, simple keys for long, composite keys IDENTIFIERS WILL NOT CHANGE

Data Normalization

•Primarily a tool to validate and improve a logical design so that it satisfies certain constraints that avoid unnecessary duplication of data •The process of decomposing relations with anomalies to produce smaller, well-structured relations

Advantages of the Database Approach

•Program-data independence •Planned data redundancy •Improved data consistency •Improved data sharing •Increased application development productivity •Enforcement of standards •Improved data quality •Improved data accessibility and responsiveness •Reduced program maintenance Improved decision support

A Good Data Name is:

•Related to business, not technical, characteristics •Meaningful and self-documenting •Unique •Readable •Composed of words from an approved list •Repeatable •Written in standard syntax

Correspondence with E-R Model

•Relations (tables) correspond with entity types and with many-to-many relationship types. •Rows correspond with entity instances and with many-to-many relationship instances. •Columns correspond with attributes. •Note: The word relation (in relational database) is not the same as the word relationship (in E-R model).

Well-Structured Relations

•Relations that contain minimal data redundancy and allow users to insert, delete, and update rows without causing data inconsistencies •Goal is to avoid anomalies -Insertion Anomaly - adding new rows forces user to create duplicate data -Deletion Anomaly - deleting rows may cause a loss of data that would be needed for other future rows -Modification Anomaly - changing data in a row forces changes to other rows because of duplication

Relationships and Subtypes

•Relationships at the supertype level indicate that all subtypes will participate in the relationship •The instances of a subtype may participate in a relationship unique to that subtype. In this situation, the relationship is shown at the subtype level

The Database Development Process

•SDLC or Prototyping

An Entity should be...

•Should Be: -An object that will have many instances in the database -An object that will be composed of multiple attributes -An object that we are trying to model

an entity should NOT be...

•Should Not Be: -A user of the database system -An output of the database system (e.g., a report)

Guidelines for Naming Entities

•Singular noun •Specific to organization •Concise, or abbreviation •For event entities, the result not the process •Name consistent for all diagrams

Defining Attributes

•State what the attribute is and possibly why it is important •Make it clear what is and is not included in the attribute's value •Include aliases in documentation •State source of values •State whether attribute value can change once set •Specify whether required or optional •State min and max number of occurrences allowed •Indicate relationships with other attributes

Strong versus. Weak Entities, and Identifying Relationships

•Strong entity -*exists independently* of other types of entities -has its own unique identifier ▪identifier underlined with single line •Weak entity -*dependent on a strong entity*(identifying owner); cannot exist on its own -does not have a unique identifier (only a partial identifier) -entity box and partial identifier have double lines •relationship between a strong and weak relationship = *Identifying relationship*. Links strong entities to weak entities i.e., JMU has database of JMU parents, not ALL parents.

Alternative Information Systems Development Approaches

•Traditional S D L C: methodical, structured, and time consuming •Rapid Application Development (R A D): faster and more adaptive, especially when a database is already in place •Several flavors: -Prototyping -Agile methodologies -eXtreme programming -Scrum -D S D M (dynamic system development methodologies)

Merging Relations

•View Integration - Combining entities from multiple E-R models into common relations •Issues to watch out for when merging entities from different E-R models: -Synonyms - two or more attributes with different names but same meaning -Homonyms - attributes with same name but different meanings -Transitive dependencies - even if relations are in 3N F prior to merging, they may not be after merging -Supertype/subtype relationships - may be hidden prior to merging

Associative Entities

•When should a relationship with attributes instead be an associative entity? -All relationships for the associative entity should be many -The associative entity could have meaning independent of the other entities -The associative entity preferably has a unique identifier, and should also have other attributes -The associative entity may participate in other relationships other than the entities of the associated relationship -Convert ternary relationships to associative entities

Information:

•data processed to increase knowledge in the person using the data

Data:

•stored representations of meaningful objects and events -Structured: numbers, text, dates -Unstructured: images, video, documents

if referential integrity is enforced

every foreign key value has a matching primary key in another table ALL FIELDS have to match, can't have different fields or a different number of fields

Enhanced E-R (E E R) model:

extends original E-R model with new modeling constructs

Database:

organized collection of logically related data

unary degree

represents a relationship between entities of the same entity type types of unary relationships: 1:1 !:many Person Is Married To a minimum of 0, maximum of 1 Person (Relationship comes out of person and loops back into the same Person box)

ternary degree

represents a relationship between entities of three different entity types.

binary degree

represents a relationship between entities of two different entity types

how to build a database

same as house drawing blueprint = modeling database building the house = implementing data

SDLC

-System Development Life Cycle -Detailed, well-planned development process -Time-consuming, but comprehensive -Long development cycle - The SDLC is sometimes called a "waterfall" approach. The outputs from one phase flow down to the next. But as you can see it is also a cycle. At any stage in the process, it is possible and sometimes necessary to return to a prior stage. •The traditional methodology used to develop, maintain, and replace information systems •Five main steps: 1.Planning - preliminary understanding of business situation. Enterprise model and conceptual data modeling. 2.Analysis - thorough analysis of business situation, leading to functional requirements. Detailed conceptual data modeling. 3.Design - logical and physical database design, to develop technology and organization. 4.Implementation - writing programs, building databases, testing, installing, training, and documenting. Maintenance - monitoring, repairing, and enhancing.

Modeling Relationships

-The relationship type is modeled as lines between entity types. The relationship instance is between specific entity instances •Relationships can have attributes -These describe features pertaining to the association between the entities in the relationship •Two entities can have more than one type of relationship between them (multiple relationships) •Associative Entity - combination of relationship and entity

derived attribute

-Values can be calculated from related attribute values (not physically stored in the database) - Years employed calculated from date employed and current date [Years Employed] The length of time employed, or a person's age, are classic examples, as they are calculated based on a fixed starting point (date hired or birth date).

composite attribute

An attribute that has meaningful component parts (sub-attributes) EMPLOYEE Employee Address (Street, City, Zip Code)

T/F: if referential integrity IS enforced, you can have orphan records

FALSE if enforced, orphan records won't exist. only when integrity is not enforced do orphan records exist

n-ary

In principle, you can have relationships between any number of entity types

describe this ERD diagram: Student tree branch 0, Registers, 1 1 Car

Student Registers Minimum of 1, Maximum of 1 Car the name of the relationship is simply: Registers

Time stamp

a time value that is associated with a data value, often indicating when some event occurred that affected the data value. The Price History attribute is both multivalued and composite.

foreign key

allows two tables to relate to each other

how can you tell if something has an associative entity

an associative entity has ROUNDED EDGES

database approach

data models, entities, relationships

Metadata:

data that describes the properties and context of user data data that describes data

a weak identity has what

double lined relationship double line can also mean it has a identifying relationship (strong linked with weak entity)

always put what in ERD

min and max cardinality and attributes

ALWAYS need what when making ERD

minimum and maximum cardinalities (the line)

Components of the Database Environment

most important part: - end user = people who use the applications and databases other components: •application programs •Repository - centralized storehouse of metadata •Database Management System (D B M S) - software for managing the database •Database - storehouse of the data •Application Programs - software using the data •User Interface - text, graphical displays, menus, etc. for user •Data/Database Administrators - personnel responsible for maintaining the database •System Developers - personnel responsible for designing databases and software

referential integrity

no value as a foreign key exists unless there is a matching value with another table every foreign key (child) has to have a parent a foreign key without a parent is called an "orphan"

in a one to many relationship, which is the parent

the one side = parent the many side = child

primary key becomes foreign key

the primary key of the "one" side migrates to become the foreign key of the "many" side

Cardinality Constraints

•Cardinality Constraints — the number of instances of one entity that can or must be associated with each instance of another entity •Minimum Cardinality -If zero, then optional -If one or more, then mandatory •Maximum Cardinality The maximum number

Guidelines for Defining Entities

•"An X is..." •Describe unique characteristics of each instance •Explicit about what is and is not the entity •When an instance is created or destroyed •Changes to other entity types •History that should be kept

Referential Integrity

•- rule states that any foreign key value (on the relation of the many side) MUST match a primary key value in the relation of the one side.(Or the foreign key can be null.) -For example: Delete Rules ▪Restrict - don't allow delete of "parent" side if related rows exist in "dependent" side ▪Cascade - automatically delete "dependent" side rows that correspond with the "parent" side row to be deleted ▪Set-to-Null - set the foreign key in the dependent side to null if deleting from the parent side → not allowed for weak entities

Second Normal Form

•1N F plus every non-key attribute is fully functionally dependent on the ENTIRE primary key -Every non-key attribute must be defined by the entire key, not by only part of the key -No partial functional dependencies

Third Normal Form

•2N F PLUS no transitive dependencies (functional dependencies on non-primary-key attributes) •Note: This is called transitive, because the primary key is a determinant for another attribute, which in turn is a determinant for a third •Solution: Non-key determinant with transitive dependencies go into a new table; non-key determinant becomes primary key in the new table and stays as foreign key in the old table

Relation

•A relation is a named, two-dimensional table of data. •Consists of rows (records) and columns (attribute or field) •Requirements for a table to qualify as a relation: -It must have a unique name. -Every attribute value must be atomic (not multivalued, not composite). -Every row must be unique (can't have two rows with exactly the same values for all their fields). -Attributes (columns) in tables must have unique names. -The order of the columns must be irrelevant. The order of the rows must be irrelevant. Note: All relations are in 1st Normal form.

Subtype:

•A subgrouping of the entities in an entity type that has attributes distinct from those in other subgroupings employee is super. has name and address. salaried employeed is sub. has name and address (from employee), as well as salary and stocks.

Business Rules

•Are statements that define or constrain some aspect of the business •Are derived from policies, procedures, events, functions •Assert business structure •Control/influence business behavior •Are expressed in terms familiar to end users •Are automated through D B M S software

Attributes definition

•Attribute - property or characteristic of an entity or relationship type •Classifications of attributes: -Required versus Optional -Simple versus Composite -Single-Valued versus Multivalued -Stored versus Derived -Identifier

Database Project Team Members

•Business analysts - analyze business situation and establish requirements •Systems analysts - like business analysts, but also have technical expertise for overall information systems •Database analysts and data modelers - analysts who focus on database •Users - the "customers" communicate their needs to analysts •Programmers - coders of the programs that interact with the database •Database architects - establish standards for data in business units •Data administrators - responsible for existing databases, ensuring data integrity and consistency •Project managers - oversee the projects, manage the personnel •Other technical experts - network, operating system, documentation, etc.

Constraints in Supertype/Subtype Relationships

•Completeness Constraints: Whether an instance of a supertype must also be a member of at least one subtype -Total Specialization Rule: Yes (double line) Partial Specialization Rule: No (single line) •Disjointness Constraints: Whether an instance of a supertype may simultaneously be a member of two (or more) subtypes -Disjoint Rule: An instance of the supertype can be only ONE of the subtypes (d = can only be ONE) -Overlap Rule: An instance of the supertype could be more than one of the subtypes (o = can be BOTH) •Subtype Discriminator: An attribute of the supertype whose values determine the target subtype(s) -Disjoint - a simple attribute with alternative values to indicate the possible subtypes -Overlapping - a composite attribute whose subparts pertain to different subtypes. Each subpart contains a Boolean value to indicate whether or not the instance belongs to the associated subtype

Components of Relational Model

•Data structure -Tables (relations), rows, columns •Data manipulation -Powerful S Q L operations for retrieving and modifying data •Data integrity -Mechanisms for implementing business rules that maintain integrity of manipulated data

A Good Business Rule Is:

•Declarative - what, not how •Precise - clear, agreed-upon meaning •Atomic - one statement •Consistent - internally and externally •Expressible - structured, natural language •Distinct - non-redundant •Business-oriented - understood by business people

Integrity Constraints

•Domain Constraints -Allowable values for an attribute (includes data types and restrictions on values) •Entity Integrity -No primary key attribute may be null. All primary key fields MUST contain data values. •Referential Integrity -Rules that maintain consistency between the rows of two related tables.

Entity Clusters

•E E R diagrams are difficult to read when there are too many entities and relationships. •Solution: Group entities and relationships into entity clusters. •Entity cluster: Set of one or more entity types and associated relationships grouped into a single abstract entity type

Entity definition

•Entity -a person, a place, an object, an event, or a concept in the user environment about which the organization wishes to maintain data •Entity type -a collection of entities that share common properties or characteristics •Entity instance -a single occurrence of an entity type

Data Definitions

•Explanation of a term or fact -Term - word or phrase with specific meaning -Fact - association between two or more terms •Guidelines for good data definition -A concise description of essential data meaning -Gathered in conjunction with systems requirements -Accompanied by diagrams -Achieved by consensus and iteratively refined

Functional Dependencies and Keys

•Functional Dependency: The value of one attribute (the determinant) determines the value of another attribute •Candidate Key: -A unique identifier. One of the candidate keys will become the primary key ▪E.g., perhaps there is both credit card number and S S# in a table...in this case both are candidate keys. -Each non-key field is functionally dependent on every candidate key.

Generalization and Specialization

•Generalization: The process of defining a more general entity type from a set of more specialized entity types. BOTTOM-UP •Specialization: The process of defining one or more subtypes of the supertype and forming supertype/subtype relationships. TOP-DOWN

Identifiers (Keys)

•Identifier (Key) - an attribute (or combination of attributes) that uniquely identifies individual instances of an entity type •Simple versus Composite Identifier •Candidate Identifier - an attribute that could be an identifier; it satisfies the requirements for being an identifier

Anomalies in This Relation (slide 50, ch4)

•Insertion - can't enter a new employee without having the employee take a class (or at least empty fields of class information) •Deletion - if we remove employee 140, we lose information about the existence of a Tax Acc class •Modification - giving a salary increase to employee 100 forces us to update multiple records

Anomalies in This Relation (2 of 2) (slide 56, ch4)

•Insertion - if new product is ordered for order 1007 of existing customer, customer data must be re-entered, causing duplication •Deletion - if we delete the Dining Table from Order 1006, we lose information concerning this item's finish and price •Update - changing the price of product I D 4 requires update in multiple records

Key Fields

•Keys are special fields that serve two main purposes: -Primary keys are unique identifiers of the relation. Examples include employee numbers, social security numbers, etc. This guarantees that all rows are unique. -Foreign keys are identifiers that enable a dependent relation (on the many side of a relationship) to refer to its parent relation (on the one side of the relationship). •Keys can be simple (a single field) or composite (more than one field). •Keys are usually used as indexes to speed up the response to user queries.

Naming Attributes

•Name should be a singular noun or noun phrase •Name should be unique •Name should follow a standard format •Similar attributes of different entity types should use the same qualifiers and classes

Costs and Risks of the Database Approach

•New, specialized personnel •Installation and management cost and complexity •Conversion costs •Need for explicit backup and recovery Organizational conflict

First Normal Form

•No multivalued attributes •Every attribute value is atomic •Fig. 4-25 is not in 1st Normal Form (multivalued attributes) → it is not a relation. •Fig. 4-26 is in 1st Normal form. All relations are in 1st Normal Form.

Cardinality of Relationships

•One-to-One -Each entity in the relationship will have exactly one related entity •One-to-Many -An entity on one side of the relationship can have many related entities, but an entity on the other side will have a maximum of one related entity •Many-to-Many -Entities on both sides of the relationship can have many related entities on the other side