Database Systems EXAM 2

Relationships depicted with specialization hierarchies are described in terms of ______

"is a" relationships. Example: a pilot IS AN employee. a mechanic IS AN employee. an accountant IS AN employee.

1NF

-all key attributes are defined -there are no repeating groups in the table. Each row/column intersection contains one and only one value, not a set of values. -all attributes are dependent on the primary key.

Two categories of SQL functions:

1. Data definition language (DDL)- includes commands to create db objects like tables, indexes, and views. 2. Data manipulation language (DML)- includes commands to insert, update, delete, and retrieve data within the db tables.

Steps for the normalization procedure:

1. Eliminate the repeating groups (present the data in a tabular format where each cell has a single value and there are no repeating groups. To eliminate repeating groups you eliminate the nulls). 2. Identify the Primary Key (make sure it is a PK that will uniquely identify ANY attribute value) 3. Identify all dependencies (know what is related and what determines what)

Weak Entity

1. Entity is existence-dependent; it cannot exist without the entity with which it has a relationship. 2. Entity has a PK that is partially or totally derived from the parent entity in the relationship.

What are the first three stages of Normalization?

1. First normal form (1NF) 2. Second normal form (2NF) 3. Third normal form (3NF) 2NF is better than 1NF, and 3NF is better than 2NF. 3NF is as high as you need to go for normalization but there is 4NF.

A table is in 3NF when:

1. It is in 2NF 2. It contains no transitive dependencies

1NF to 2NF steps:

1. Make new tables to eliminate partial dependencies (for each component of the PK that acts as a determinant in a partial dependency, create a new table with a copy of that component as the PK. Keep the determinants in the original table too bc they will be FKs for the relationship to relate the new tables to the original table). 2. Reassign corresponding dependent attributes

2NF to 3NF:

1. Make new tables to eliminate transitive dependencies (for every transitive dependency write a copy of its determinant as a PK for a new table. If you have 3 different transitive dependencies, you will have 3 different determinants. Keep the determinant in the original table to serve as FK) 2. Reassign corresponding dependent attributes READ PG 230 of TEXTBOOK PDF

Disadvantages of stored derived attributes

1. Requires constant maintenance to make sure derived value is current

Advantages of stored derived attributes

1. Saves CPU processing cycles 2. Saves data access time 3. Data value is readily available 4. Can be used to keep track of historical data

Advantages of not stored derived attributes

1. Saves storage space 2. Computation always yields current value

2 criteria to help determine when to use subtypes and supertypes:

1. There must be different, identifiable kinds or types of the entity in the user's environment 2. The different kinds or types of instances should each have one more attributes that are unique to that kind or type of instance.

Disadvantages of not stored derived attributes

1. Uses CPU processing cycles 2. Increases data access time 3. Adds coding complexity to queries

A specialization hierarchy provides the means to:

1. support attribute inheritance 2. define a special supertype attribute (subtype discriminator) 3. define disjoin/overlapping constraints and complete/partial constraints.

Second normal form (2NF)

1NF and no partial dependencies

If the 1NF has a single-attribute PK, then the table is automatically in _____.

2NF

Third normal form (3NF)

2NF and no transitive dependencies

Entity Cluster

A "virtual" entity type used to represent multiple entities and relationships in the ERD. Formed by combining multiple interrelated entities into a single, abstract entity object. It's not actually an entity in the final ERD. Just a temporary entity used to represent multiple enttiies/relationships with the purpose of simplifying the ERD and enhancing its readability.

Surrogate Key

A PK created by the DB designer to simplify the identification of entity instances. Has no meaning in the user's environment- it exists only to distinguish one entity from another (like PKs). It has no intrinsic meaning, values for it can be generated by the DBMS to ensure that unique values are always provided. Example: auto-incrementing ID numbers

Dependency Diagram

A diagram that depicts all dependencies found within a given table structure. Its use make it less likely that you will overlook an important dependency.

Entity Supertype

A generic entity type that is related to one or more entity subtypes. Contains common characteristics/attributes for all of its subtypes. Example: EMPLOYEE (it's a super type that has common characteristics for its entities but it has subtypes for specific kinds of employees that branch out)

Normalization

A process for evaluating and correcting table structures to minimize data redundancies, thereby reducing the likelihood of data anomalies. Works through a series of stages called normal forms.

Inheritance

A property that enables an entity subtype to inherit the attributes and relationships of the supertype. All entity subtypes inherit their PK attribute from their supertype.

Weak Relationship

AKA Non-identifying relationship Exists if the primary key of the related entity does not contain a primary key component of the parent entity.

Extended Entity Relationship Model (EERM)

AKA enhanced entity relationship model. The result of adding more semantic constructs to the original ER model. Uses a EERD (EER Diagram). Has entity supertypes, subtypes, and clustering.

Strong Relationship

AKA identifying relationship. Exists when the primary key of the related entity contains a PK component of the parent entity.

Natural Key

AKA natural identifier A real-world, generally accepted identifier used to distinguish/uniquely identify real-world objects. It is familiar to end users and forms part of their day-to-day business vocabulary. Most of these make acceptable PK identifiers. Example: SSN.

Disjoint Subtypes

AKA nonoverlapping subtypes; Subtypes that contain a unique subset of the supertype entity set.; each entity instance of the supertype can appear in only one of the subtypes. Example: Employee (supertype) who is a pilot (subtype) can appear only in the PILOT subtype not in any other subtypes. Indicated by the letter d.

Relationship definition

An association between entities

Single-valued attribute

An attribute that can only have a single value. Example: a person can only have one social security number. Example: a manufactured part can only have one serial number. This is not necessarily a simple attribute!! Example: a serial number is single-valued but it is a composite attribute bc it can be subdivided into the region it was produced, plant within the region, blah blah blah. like how SSN numbers mean certain things.

Atomic Attribute

An attribute that cannot be further subdivided. It displays ATOMICITY.

Simple Attribute

An attribute that cannot be subdivided. Example: age, sex, marital status.

Optional Attribute

An attribute that does not require a value; it can be left empty. Example: Not all students have a middle name or phone number (so these would not be required).

Required Attribute

An attribute that must have a value; it cannot be left empty. In the Crow's Foot Model, these are in bold to indicate that there must be data entry. Example: students are assumed to all have a last and first name (so these would be required).

Derived Attribute

An attribute whose value is calculated (derived) from other attributes. Does not need to be physically stored within the database; but can be derived instead by using an algorithm. Sometimes called computed attributes. Example: Age can be derived by using the difference between the current date and the person's DOB. Example: Total cost of an order can be derived by multiplying the quantity ordered by the unit price.

Existence-independent

An entity that can exist apart from all of its related entities. AKA Strong Entity or Regular Entity. Example: It is possible for a PART of a produce to exist independently from a VENDOR in the relationship "PART is supplied by VENDOR"

Existence-dependent

An entity that can exist in the database only when it is associated with another related entity occurrence. It has a mandatory FK attribute that cannot be null. Example: PARENT has Child (so the child entity is existence-dependent on the PARENT entity bc it is impossible for a child to exist without the PARENT.

Determinant

Any attribute whose value determines other values within a row.

Multivalued Attributes

Attributes that can have many values. Example: a person may have several college degrees. Or a car's color can be divided into many colors for the roof, body, and trim. So CAR_VIN would be the PK but CAR_COLOR would be a multivalued attribute of the CAR entity.

Iterative Process

Based on repetition of processes and procedures. This is when you make an ER diagram. Have to identify business rules, main entities and relationships, develop an initial ERD, identify attributes and PKs, and then revise/review the ERD.

Generalization

Bottom-up process or identifying a higher-level, more generic entity supertype from lower-level subtypes. Based on grouping the common characteristics and relationships of the subtypes. Example: might identify piano, violin and guitar. You could identify a "string instrument" entity supertype as a common characteristic for the subtypes.

Attribute definition

Characteristics of entities.

Entity Subtype

Contain their own unique characteristics but are directly related to a supertype and inherit its attributes too. Example: a PILOT is a subtype of EMPLOYEE. also MECHANIC and ACCOUNTANT (these have unique attributes). CLERK is not acceptable bc it only satisfies that it is an identifiable kind of employee but none of the attributes are unique to just clerks.

CREATE SCHEMA AUTHORIZATION

Creates a db schema

CREATE TABLE

Creates a new table in the user's db schema

Atomic Data

Data stored at their lowest level of granularity

The ERM forms the basis of an ____, which represents _____.

ERD; the conceptual database as viewed by the end user.

NOT NULL

Ensures that a column will not have null values

Database main components

Entities, attributes, and relationships

Specialization Hierarchy

Entity supertypes and subtypes are organized into this; it depicts the arrangement of higher-level entity supertypes (parent entities) and lower-level entity subtypes (child entities). A subtype can only exist with the context of a supertype and every subtype can only have one supertype to which it is directly related. But it can have many levels of supertype/subtype relationships so a supertype can have many subtypes.

Unary Relationship

Exists when an association is maintained with a single entity. AKA a recursive relationship because the entity has a relationship with itself. Example: EMPLOYEE manages EMPLOYEE (like if it is a manager then it is technically an employee that manages employees). (1:1 relationship with itself)

Transitive Dependency

Exists when there are functional dependencies such that X-->Y, Y-->Z, and X is the PK. So the dependency X -->Z is a transitive dependency bc X determines the value of Z via Y. *more difficult to identify among a set of data. AKA the signaling dependency.

Partial Dependency

Exists when there is a functional dependence in which the determinant is only part of the PK. Example: (A,B) is the PK, then the functional dependence B --> C is a partial dependency because only part of the PK (B) is needed to determine the value of C. *these are straightforward and easy to identify

Ternary Relationship

Exists when three entities are associated. (higher degrees exist like four-degree relationship, but they are rare). Example: DOCTOR, PATIENT, AND DRUG and the verb for the relationship is prescribes. DOCTOR writes many PRESCRIPTIONs PATIENT receives many PRESCRIPTIONs DRUG appears in many PRESCRIPTIONs

Binary Relationship

Exists when two entities are associated in a relationship. The most common type of relationship. Higher-order relationships, like ternary, are decomposed into equivalent binary relationships whenever possible to simplify the conceptual design. Example: PROFESSOR teaches one or more CLASSes.q

Cardinality

Expresses the minimum and maximum number of entity occurrences associated with one occurrence of the related entity. Indicated by putting numbers by the entities like (x,y). X is the minimum number of associated entities, and y is max. Example: A PROFESSOR teaches a CLASS. Cardinality: PROFESSOR(1,1) and CLASS (1,4) This means that each professor teaches up to four classes, so the PK occurs at least once and no more than 4 times as FKs in the CLASS table.

T/F? You should implement M:N relationships and multivalued attributes in the RDBMS.

FALSE. Each column and row intersection represent a single data value. So if multivalued attributes exist, the designer has to decide on splitting the multivalued attribute into several new attributes (one for each component of the multivalued attribute) OR create a new entity composed of the multivalued attribute's components.

T/F? The highest level of normalization is always the most desirable.

False; the higher the normal form, the more relational join operations needed to produce a specified output. And more resources are needed by the DB system to respond to end user queries. So sometimes you need to denormalize.

Entity naming conventions

It's a noun; usually written in all CAPS.

Optional Entity

Minimum cardinality is 0.

Optional Participation

One entity occurrence does not REQUIRE a corresponding entity occurrence in a particular relationship. Example: "COURSE generates CLASS" --> some courses do not generate a class. You put a small circle (O) on the side of the optional entity.

Mandatory Participation

One entity occurrence requires a corresponding entity occurrence in a particular relationship. If no optionality symbol is depicted with the entity in crow's foot notation, then the entity is assumed to exist in a mandatory relationship with the related entity.

Denormalization

Produces a lower normal form so 3NF will be converted to 2NF. BUT: The price you pay for increased performance through denormalization is greater data redundancy.

LOOK AT PAGE 249 FOR DATA MODELING CHECKLIST

READ IT FROM TEXTBOOK PDF

Normalizing the table structure will do what?

Reduce the data redundancies. If repeating groups exist, they must be eliminated by making sure that each row defines a single entity. Dependencies must be identified to diagnose the normal form.

Time-variant data

Refer to data whose values change over time and for which you must keep a history of the data changes. For example: GPA or bank account balance change over time. Whereas, DOB or SSN are not time variant.

SQL

Structured Query Language

Overlapping Subtypes

Subtypes that contain nonunique subsets of the supertype entity set; each entity instance of the supertype may appear in more than one subtype. Example: a person may be an employee, a student, or both. STUDENT AND EMPLOYEE OVERLAP.

T/F? The relationship classification is difficult to establish if you only know one side of the relationship.

TRUE.

First normal form (1NF)

Table format, no repeating groups, and PK identified

Conversion to 2NF occurs only when ____

The 1NF has a composite PK.

Identifiers

The ERM uses these; they are one or more attributes that uniquely identify each entity instance. Can be the PK of a table. These are UNDERLINED in the ERD.

Functional dependence

The attribute B is fully functionally dependent on the attribute A if each value of A determines one and only one value of B. Example: PROJ_NUM --> PROJ_NAME proj num is the determinant attribute and the proj name is the dependent attribute. Review definition from previous exam.

Subtype Discriminator

The attribute in the supertype entity that determines to which subtype the supertype occurrence is related. Example: EMPLOYEE_TYPE so if the EMPLOYEE_TYPE has a value of "P" then the supertype is related to the PILOT subtype.

Participants

The entities that participate in a relationship. And each relationship is identified by a name that describes the relationship. The relationship is an active or passive verb for example: a student TAKES a class, a professor TEACHES a class. etc.

Granularity

The level of detail represented by the values stored in a table's row.

Relationship Degree

The number of entities or participants associated with a relationship.

Relational Schema

This is a shorthand notation for the table structure; Key attributes are underlined in the ERD.

What is the function of the PK?

To guarantee entity integrity, not "describe" the entity.

Specialization

Top-down process of identifying lower-level, more specific entity subtypes from a higher-level entity supertype. Based on grouping the unique characteristics and relationships of the subtypes. Example: you identify many entity subtypes from the original employee supertype.

T/F? Entity and object are interchangeable and represent real-world objects.

True

T/F? Relationships between entities always operate in both directions.

True Example: a CUSTOMER may generate many INVOICEs. & each INVOICE is generated by one CUSTOMER. (1:M relationship)

T/F? Normalization is usually used in conjunction with entity relationship modeling.

True. A DB designer will make a data model using Crow's Foot notation ERDs and then after the initial design is complete, the designer can use normalization to analyze the relationships among the attributes within each entity and determine if the structure can be improved through normalization.

Connectivity

Used to describe the relationship classification. Example: 1:M

Can attributes share a domain?

YES. Example: a student address and professor address have the same domain of all possible addresses.

How to put many to many relationships in tables?

You make 3 tables and the third one is a junction table that has the PKs of the other two ENTITY tables. Then you link that PK of ENTITY tables to the junction table.

Composite Identifier

a primary key composed of more than one attribute.

Composite Attribute

an attribute that can be further subdivided to yield additional attributes. Example: Address can be subdivided into street, city, state, zip code. DON'T CONFUSE WITH COMPOSITE KEY. It's wise to change these into a series of simple attributes.

The ERM refers to a table row as ____

an entity instance or entity occurrence At the ER modeling level, an entity refers to the entity set; not the single entity instance

Relationship Strength

based on how the PK of a related entity is defined.

Repeating Group

derives its name from the fact that a group of multiple entries of the same type can exist for any single key attribute occurrence. Example: each time a new record is entered for a project, the number of entries in the group grows by one. This is a repeated group! A relational table must not contain any of these.

Attributes have a ___

domain

Total completeness

every supertype occurrence must be a member of at least one subtype.

Normalization helps ____

improve the existing data structure and create an appropriate database design. Whether designing a new database structure or modifying an existing one, the normalization process is the same.

Partial completeness

not every supertype occurrence is a member of a subtype; some supertype occurences may not be members of any subtype.

Completeness Constraint

specifies whether each entity supertype occurrence must also be a member of at least one subtype. Can be partial or total.

1NF to 2NF means to remove _____

the partial dependencies

Domain

the set of possible values for a given attribute. Example: GPA has a domain (0,4) because the lowest possible GPA is 0 and the highest is 4. Example: Gender only has 2 possibilities (M or F)

2NF to 3NF means to remove ____

the transitive dependencies