Database Test 1

First Normal Form (1NF)

Any relation that does not have multi-valued attributes is in First Normal Form • "Atomic" • In fact, any valid relation is in 1NF, by definition

Network database model

Created to improve on hierarchical model Allows records to have more than one parent Can access data from multiple points

Business processes need to be converted to?

entities and interactions

Types of Databases can be classified according to?

number of users, database locations, and type of use

Relational Database Model - Entity

object/concept about which data is collected and stored Entity instance (or occurrence) = row in a relational table

Problems with a "flat" database

Redundancy (i.e. data duplication), Update anomalies (Update a single publisher's phone number requires you to update multiple entries), Insertion anomalies (You cannot insert information about an author until we have procured a book by that author), and Deletion anomalies (If there is a single book from a particular publisher, and for some reason we decide to remove the record about the book, we lose information about the publisher too)

Relationships and Subtypes

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

Data Models

Relatively simple (logical or conceptual) representations of complex real-world data structures Often graphical abstraction of a real-world object or event Useful in understanding complexities of the real-world environment

Partial Specialization

Relaxes the above constraint: every entity in the supertype need not belong to any of the subtypes in the specialization

When we convert EER diagrams to relations?

Some of the richness of the context is lost (although foreign keys do indicate relationships) - All we know is now in the form of some special tables or relations, that satisfy some criteria

Hierarchical database model

Tree-based approach developed in 1960's Based on parent-child relationships (1:M) Each record is linked to all its child records Ex: customer → order(s) → item(s)

Total Specialization

When this constraint is specified, every instance of the supertype does have to belong to one of the subtypes

Steps to Start the normalization process

1. Eliminate repeating groups (multi-valued attributes) 2. Identify the Primary Key 3. Identify all dependencies

Converting to 3NF

1. Make new tables to eliminate transitive dependencies - The determinant of each transitive dependency becomes the primary key of its own new table 2. Reassign corresponding dependent attributes − Remove dependent attributes from the original table and move them to the corresponding new table

Attribute Rules when mapping regular entities to relations

1. Simple attributes: ER attributes map directly onto the relation 2. Composite attributes: Use only their simple, component attributes 3. Multi-valued attribute: Becomes a separate relation with a foreign key taken from the superior entity

Secondary Key

attribute (or attributes) used for data retrieval

Single-valued Attribute

attribute with a single value

Entities have some _____ which are of interest to the 'organization'

attributes

Primary Key

cell value that uniquely identifies a tuple

Foreign Key

cell value which is exactly equal to the primary key value in some other relation

Relational Database Model - Attributes

characteristics of an entity (columns)

DBMS (Database Management System)

collection of programs that manages the database structure and controls access to and retrieval of the data (security)

Relational Database Model - Relationships

connections among data Used to connect tables

In an EER Diagram we attempt to identify a supertype that contains?

the maximum number of common attributes -All subtypes inherit all of the attributes from the supertype -Each subtype has other unique attribute(s)

Database

"a collection of interrelated data organized in such a way that it corresponds to the needs and structure of an organization and can be used by more than one person for more than one application." - i.e.: interrelated files with shared data.

File-based database systems often imply?

"flat" data files

Relational Database Model - Table

"relation" = matrix of intersecting rows and columns

Data Management focuses on?

the proper generation, storage, and retrieval of data and is a core activity of any organization

Mapping Weak Entities

A Weak entity becomes a relation with a foreign key that is adopted from its associated Strong entity The primary key for the Weak entity relation is composed of: -Partial identifier of Weak entity -Primary key of identifying relation (Strong entity) = Composite primary key (multiple attributes)

Primary Key - Relational

A candidate key selected to be used as the primary row identifier Cannot contain NULL entries

Entity Type

A collection of entities that share common properties or characteristics

ER Model

A detailed, logical representation of the data for an organization or business area expressed in terms of Entity Types, Relationships and Attributes A CUSTOMER places an ORDER for a PRODUCT

ER Diagram

A graphical representation of an ER model

Candidate Key

A minimal (irreducible) superkey No subset of attributes in this key can be a superkey

Attribute

A named property or characteristic of an entity type that is of interest to the organization

Entity

A person, place, object, event, or concept in the user environment, about which the organization wishes to maintain data

Normalization

A process for evaluating and correcting table structures to minimize data redundancies, thereby reducing the likelihood of data anomalies. - i.e., the process of decomposing relations with anomalies to produce smaller, well-structured relations

Third Normal Form (3NF)

A relation is in third normal form, or 3NF, if it is in second normal form and it contains no transitive dependencies

Well-Structured Relations

A relation that contains minimal data redundancy and allows users to insert, delete, and update rows without causing data inconsistencies

Entity Instance

A single occurrence of an entity type. example: Susan and John are employees of a company, both of them are entity instances, they belong to entity type EMPLOYEE

Subtype

A subgrouping of the entities in an entity type that is meaningful to the organization and that shares common attributes or relationships distinct from other subgroupings

Enhanced ER Diagram

An ER diagram that also shows subtypes and supertypes of entities and identifies the relationships between them

Relationships

An association between instances of one or more entity types that is of interest to the organization (usually a VERB)

Foreign Key - Relational

An attribute (or combination of attributes) in one table whose values must either match the primary key in another table, or be NULL

Superkey

An attribute (or combination of attributes) that uniquely identifies each row in a table

Identifier/Primary Key

An attribute (or set or attributes) that uniquely identifies individual instances of an entity type

Subtype Discriminator

An attribute of the supertype whose values determine the target subtype or subtypes

Composite Attribute

An attribute that can be broken down into its component parts

Simple Attribute

An attribute that cannot be broken down into smaller components

Derived Attribute

An attribute whose value can be derived from related attribute values

Associative Entity

An entity that associates the instances of one or more entity types and contains attributes that are peculiar to the relationship between those entity instances

Supertype

An entity type that includes distinct subtypes that needs to be represented in the data model

What are the two ways of viewing a database?

As a set of relations, and the associated definitions of tuples, keys, etc. • Nothing to do with a real-life situation and.... • An implementation of a real-life model, namely the E-R model • A one-to-one mapping between the elements in the model, and the elements of the database

Relational Database Model

Basis for most approaches used today for storing data

Mapping Binary Relationships - Many-to-Many

Create a new relation with the primary keys of the two entities as its primary key

Mapping Supertype/Subtype Relationships

Define one relation for the supertype and one for each subtype Supertype attributes (including identifier and subtype discriminator) go into supertype relation Subtype-specific 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

Disjoint Constraints

Define whether it is possible for an instance of a superclass to be a member or one or more subclasses simultaneously. May be Disjoint Rule or Overlap Rule

Participation Constraints

Dictate whether every member of a superclass must participate as a member of a subclass. May be Total Participation or Partial Participation

What are the four things that we need to be able to build into a set of relations?

Entities (with simple, composite, or multi-valued attributes), Relationships (one to many (1:M), one to one (1:1), and many to many (M:N)), Associative Entities, and Supertypes/Subtypes

What are the two main types of database integrity constraints?

Entity Integrity and Referential (or Reference) Integrity

Referential Integrity implies that?

Every non-NULL foreign key references an existing primary key value Enforcing this rule makes it impossible to have an invalid foreign key value, or to delete a row whose primary key has matching foreign key values in another table

Entity Integrity implies that?

Every relation in a relational data model has a unique primary key The data values for all attributes in the primary key are not NULL Enforcing this rule ensures that every row has a unique identity and thus allows foreign key values to properly reference primary key values EX: Customers must be uniquely identified by their ID number

Properties of relations

Every relation must have a distinct name Each cell of the relation is atomic A particular tuple can have only one value for an attribute Note that two different tuples can have the same value for an attribute Each attribute in a table must have a distinct name The values of any attribute must be from the same domain There should not be any duplicate tuples Order of tuples has no significance Order of attributes has no significance

What are the 3 types of database models?

Hierarchical database model, Network database model, and Relational database model

Functional Dependency

If A and B are two sets of attributes of a relation R, then B is functionally dependent on A if each value of A in the relation is associated with exactly one value of B - Represented by A −> B - A is called the DETERMINANT - If we know the values of A, the values of B are uniquely determined

Non-Disjoint (Overlap) Constraint Rule

If subtypes of a specialization are not disjoint then an entity may be a member of more than one subtype of a specialization

Computerized Manual Filing Systems

Individual files for each purpose Each business unit had its own file system Organized to facilitate the expected use of the data Data processing specialist required to retrieve data and run reports

What are some examples of DBMS software?

Microsoft Access, Microsoft SQL Server, Oracle, MySQL

What is the solution to database problems like redundancy and anomalies that make it harder to create, maintain, or modify the database?

Normalization

Characteristics of Relations

Not all database tables qualify as relations • Requirements (to be a relation): - Every relation has a unique name - Every attribute value is atomic - Every row is unique - Attributes (columns) in a given table have unique names - The order of the columns is irrelevant - The order of the rows is irrelevant

Mapping Binary Relationships - One-to-One

Primary key on mandatory side becomes a foreign key on optional side

Mapping Binary Relationships - One-to-Many

Primary key on the "one" side becomes a foreign key on the "many" side

Converting to 2NF

Starting with a relation in 1NF, do the following: 1. Make new tables to eliminate partial dependencies - A new table is needed for each component of the primary key that is a determinant in a partial dependency - The original (composite) primary key should remain in the original table 2. Reassign the corresponding dependent attributes - Remove dependent attributes from the original table and move them to the corresponding new table • Note: any relation in 1NF that has a single-attribute primary key, is already automatically in 2NF

Attribute Inheritance

The concept that if an entity is a subtype it retains all the attributes of the supertype. All attributes of a supertype are 'inherited' by a subtype and hence need not be shown explicitly

Degree of a Relationship

The number of entity types that participate in a relationship Unary: (degree 1) - Also called "Bill of Materials" or "Recursive" Binary: (degree 2) - Most common Ternary: (degree 3)

Cardinality

The number of instances of one entity that can (or must) be associated with each instance of another entity

Specialization

The process of maximizing the differences between members of an entity by identifying their distinguishing characteristics...a top-down approach

Generalization

The process of minimizing the differences between entities by identifying their common features...a bottom-up approach

Information

The result of processing raw data to reveal its meaning in a given context

Boyce-Codd Normal Form (BCNF)

a table is in Boyce-Codd Normal Form or BCNF, if every determinant in the table is a candidate key

What are some issues with file-based systems?

data redundancy (different files contain the same information like id, address, etc), isolation of data in separate systems, data inconsistency (lack of data integrity), data anomalies (update/insert/delete anomalies), program-data dependence (definition of data is coded into application programs)

The number of entities involved in a relationship gives us the?

degree of the relationship

How do we reduce the number of entities in an ER Diagram?

generalize attributes entities have in common into "sets" of entities using an Enhance ER (EER) Diagram

What are the components of a database system?

hardware, software, people, procedures, and data

Multi-valued Attribute

may take on more than one value for a given entity instance

As a general rule of thumb: a table should not pertain to?

more than one entity type

Normalization is a "check" against?

potential mistakes/ inefficiencies that might be in the structure of the database tables

ER Diagrams enable us to understand not only the data, but also the?

processes

Data

raw facts that can be recorded and preserved

ER Diagrams can be complex (ex. multi-valued attributes, associative entities, etc) - however?

relations cannot -We have to suitably break down the complexity -Relations present one consistent method of representing the data - everything is in the form of tables

Interactions between entities represent?

relationships

Tuple

row of a relation, with each cell consisting of specific values for a particular variable or attribute

Relation

set of tuples (table)

Disjoint Constraint Rule

specifies that if the subtypes of a specialization are disjoint then an entity can be a member of only one of the subtypes of the specialization

What are some examples of people in a Database System?

system admins, database admins (DBAs), database designers, system analysts and programmers and end users

Multi-User Database

the database used the most often, especially by businesses. includes workgroup database and enterprise database

Second Normal Form (2NF)

• A relation is in second normal form or 2NF if it is in first normal form, and every nonkey attribute is fully functionally dependent on the primary key • "Fully" implies no partial functional dependencies, i.e. no non-key attributes are functionally dependent on only part of the primary key