CS 327e Databases Midterm

What are some effects of poor database design?

1. Data inconsistency

2. Unnecessary duplicated data

3. Retrieving inaccurate data

4. Untimely queries

What are the three types of data relationships?

1. One-to-one

2. One-to-many

3. Many-to-many

What kind of identifiers should a primary key use?

Arbitrary identifiers or concatenation of arbitrary identifiers

Entity

An object represented by a database; it is represented in relations and tables once the database is constructed

Columns

Describes the properties of a specific entity; it represents fields or attributes

Row

An instance of an entity; represented as a tuple

Schema

The structure of a database such as the name of the table, the columns and their types, the domain of the columns, etc. It also includes entity relationships.

Keys

Identifiers for tables

Primary Keys

Every table needs one; they uniquely identify each row in a table; by convention, they are usually underlined

Foreign Keys

How parent tables are related to child tables

SQL

Structured Query Language; language used by every relational database

DDL

Data Definition Language; actions that cannot be rolled back in SQL such as create table and alter table

DML

Data Manipulation Language; commands that can be rolled back such as inserts, updates, and deletes

What are the three types of data anomalies?

1. Insertion Anomaly

2. Update Anomaly

3. Deletion Anomaly

Insertion Anomaly

When the design of the table prevents the user from inserting new elements when necessary. Instead, restrictions on insertion force the user to wait until a certain event occurs (i.e. a user can't create a new item until someone orders it).

Update Anomaly

Occurs when there is too much duplication within a database such that it causes an error when some but not all of the duplicates are updated after a change in state of an entity.

Deletion Anomaly

Occurs when removing one entity or variable causes the deletion of other information that the user does not want to remove.

Instance

A group of attributes that describes a single real-world occurrence of an entity.

Entity Identifier

An attribute that uniquely identifies an entity occurrence.

Natural Indentifier

Entities that are already assigned arbitrary identifiers such as invoices or customer numbers

Constraint

Allows the user to disallow various inputs such as null values or non-uniqueness; helps to maintain data consistency and accuracy

Single-Valued Attribute

For a given instance of an entity, each attribute can have only one value.

Multivalued Attribute

An entity cannot have one of these, so the user must handle these attributes by creating an entity to hold them in. These types of values slow down searching and place unnecessary restrictions on the amount of data that can be stored.

Entity-Relationship Diagrams (ERD)

Provide a way to document the entities in a database along with the attributes that describe them.

What are the three major ERDs?

1. The Chen Model

2. Information Engineering (IE)

3. Unified Modeling Language (UML)

Unified Modeling Language (UML)

Specifically intended for the object-oriented environment and is usually the choice when objects are included

Domain

An expression of the permissable values for an attribute.

Data Dictionary

Stores the domains and their constraints.

BLOB

Binary Large Object; can store anything binary such as graphic; supported by most DBMSs

What do the relationships in a database describe?

The relationships between instances of entities

Weak Entity

An entity that cannot exist in the database unless a related instance of another entity is present and related to it; it must use a foreign key in conjunction with its attributes to create a primary key. The foreign key is typically a primary key of an entity it is related to.

Mandatory Relationship

When one entity occurrence requires a corresponding entity occurrence.

|| (On an IE Style Diagram)

Means one and one only (mandatory relationship)

0| (On an IE Style Diagram)

Zero or one

>1 (On an IE Style Diagram)

One or more (mandatory relationship)

>0 (On an IE Style Diagram)

Zero, one, or more

Relationship Data

Data that apply to the relationship between two entities, rather than to the entities themselves.

Composite Entities

Exist to represent the relationship between two or more entities.

Data Flow

Shows how data are handled within an organization, including who handles that data, where the data is stored, and what is done to the data.

Data Model

Depicts the internal, logical relationships between the data without regard to who is handling the data or what is being done with them.

Relation

A table with columns (attributes) and rows (tuples).

Column Homogeneous

When the values in a column are drawn from one and only one domain.

Base Tables

Relationships that are actually stored in the database; they are described by a schema

What are the requirements for a column?

1. A name that is unique within the tables

2. A domain

3. Columns can be viewed in any order without affecting the meaning of the data

What are the requirements for a row?

1. Only one value at the intersection of a column and row (no multi-valued attributes)

2. Uniqueness

3. Primary key

4. The rows can be viewed in any order without affecting the meaning of the data

Virtual Tables

Copies of data in the base tables and are never stored in the database

Can primary keys have null values?

No (entity integrity)

Candidate Key

The possible primary keys in a table.

What are the requirements of a primary key?

1. Should have a value that is highly unlikely to ever be null

2. Should be immutable

3. Should avoid meaningful data - use arbitrary identifiers

Referential Integrity

Every non-null foreign key value must match an existing primary key value

Normalization

Creating relations that avoid most of the problems that arise from bad relational design.

Decompose

Taking projections of a relation to create more than one relation.

Normal Forms

Represent an increasingly stringent set of rules; the higher the level, the better the design of the relation.

What is the most common normal form?

Third (3NF)

First Normal Form Criterira

1. The data are stored in a two-dimensional table

2. There are no repeating groups

What is the order of the normal forms from least specific to most?

1. First NF

2. Second NF

3. Third NF

4. Boyce-Codd NF

5. Fourth NF

6. Fifth NF

Repeating Group

An attribute that has more than one value in each row of a table; analogous to a multivalued attribute in an ER diagram

How should you handle a repeating group?

Create another table to handle multiple instances of the repeating group.

Second Normal Form Criterira

1. The relation is in first normal form

2. All non-key attributes are functionally dependent on the entire primary key

Functionally Dependent

One-way relationship between two attributes such that at any given time, for each unique value of attribute A, only one value of attribute B is associated with it throughout the relation.

Determinant

An attribute that determines the value of other attributes.

Third Normal Form Criterira

1. The relation is in second normal form

2. There are no transitive dependencies

Transitive Dependencies

Exist when you have the functional dependency pattern:

A -> B and B-> C therefore A -> C

Boyce-Codd Normal Form Criterira

1. The relation is in third normal form

2. All determinants are candidate keys

Multivalued Dependency

Exists when for each value of attribute A, there exists a finite set of values of attribute B that are associated with it, and a finite set of values of attribute C that are also associated with it. Attributes B and C are independent of each other.

Join Dependency

Occurs when a table can be put together correctly by joining two or more tables, all of which contain only attributes from the original table

Can you delete a table with the CREATE TABLE SQL statement?

No

Conceptual Diagrams

Represent the entities, attributes, and relationship types of a database

Physical Diagrams

Show the keys, junction tables, datatypes of a database

What is the hierarchy of a database from lowest to highest?

Columns and rows -> tables and views -> schemas -> catalogs -> clusters

Permanent Base Tables

Tables whose contents are stored in the database and remain permanently in the database unless they are explicitly deleted.

Global Temporary Tables

Tables for working storage that are destroyed at the end of a SQL session; must be loaded with data each time they are going to be used

Local Temporary Tables

They are visible only to the specific program module in which they are created

CASCADE

Delete or update all foreign key rows

RESTRICT

Does not allow deletions of primary key rows

ALTER TABLE

How you can modify almost any characteristic of a table

MODIFY

SQL command that can replace a complete column definition and make new column characteristics

PostgresSQL

Open source object relational database management system

CREATE TABLE

Can clone a table, can be used to materialize the result of the SELECT statement to boost performance, or to create a new table in the database

\dt:

Looks at a list of tables in a database

\c:

Connects to a database

\i:

Runs the commands in a script

\?:

Help

\copy:

Loads the dataset into a table as long as a table already exists

TRUNCATE

Removes all of the rows of data but not the actual table in a database

DROP

Removes all the data in a table along with the table itself

How do you create a foreign key in SQL?

REFERENCES Table(column)

How do you select all of the data from a file in SQL?

SELECT * FROM column_name

How do you create aliases in SQL?

FROM column_name AS alias

DISTINCT

Only unique rows from the input dataset will be returned

ALL

Returns all rows and is the default in SQL

Cartesian Product

All possible combinations of records.

Inner Join

Includes rows in the query only when the joined field matches records in both tables

Full Outer Join

A join in which all rows from both tables will be included regardless of whether they match rows from the other table

Left Outer Join

Where the query includes all of the rows from the table on the left and only those records from the table on the right that match the join field in the left table.

Right Outer Join

Where the query includes all of the rows from the table on the right and only those rows from the table on the left that match the join field in the right table.

Natural Join

Creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables; can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join.

Which types of joins return null values?

Outer joins

How do you filter rows in SQL?

Using the WHERE clause

Grouping

Splitting the whole input set of records into several groups with a view to have only one result row for each group.