28 terms

Database: The Relational Model

Pages 59-72.
Things being tracked; something of importance to the user that is represented in the database.
DBMS products store data in the form of
relation form is used to store data for what product?
A special type of table; two dimensional table that stores data in the form of rows and columns.
What Characteristics does a relational table have?
1. rows contain data about an entity
2. columns contain data about attributes
3. Cells of the table hold a single value
4. All entries in a column are of the same kind
5. each column has a unique name
6. the order of the columns is unimportant
7. the order of the rows is unimportant
8. no two rows may hold identical sets of data values
the term used to describe each column.
SQL: What is written first and how is it written?
the relation name is written first. It is written in capital letters, and is always singular. If more then two words, join in a underscore.
SQL: How do you write column names?
Always in parenthesis. The first letter of each word is capitalized and the rest are lower case. If there is more then one word they are joined together and the first letter of each word is capitalized.
Write out the equivalent sets.
table Row Column
file Record Field
relation tuple attribute
DBMS and Duplicate Rows
When manipulating relations with DBMS, the table may have duplicate rows. The default behavior for DBMS products is not to check for duplicate rows.
A key is one or more columns of a relation that is used to identify a row. A key can be unique or non unique.
Unique Key
A unique key identifies a unique row.
Nonunique Key
A non unique key has the potential to identify more then one row.
Composite Key
A key that contains two or more attributes/columns. If one column is not unique then two columns in combination can make it unique. Composite key can have three attributes.
Candidate Keys
Can be single column keys or composite keys.
Uniquely identify each row in a relation.
There can be multiple candidate keys.
Primary Key
Ideal: Short, Numeric, Never changes.
the candidate key that is chosen as the key that the DBMS will use to uniquely identify each row in a relation.
Uniquely identify each row.
Represent rows in a relation by creating link between two tables.
DBMS and Primary Key Use
Organize storage, fast retrieval, build indexes.
dbo, what does it mean and how is it formatted?
Stands for database owner, dbo.CUSTOMER
Surrogate Primary Key
A unique DBMS designated identifier. This key has been added to the table to be a primary key when the primary key is too long, or non numeric. Each time a row is created a unique value is assigned. The values never change. Often hidden on forms, query, results and reports from users.
Identity or Identity Specification
A set of identity specifications that have been set to indicate to the SQL server that s surrogate key column exists.
Identity Seed
Starting value of the surrogate key.
Identity Increment
The amount that is added to each key value to create the next key value.
Used by Oracle Database as a function to define automatically increasing sequences of numbers that can be used as surrogate key numbers.
MySQL: Automatically assign surrogate key numbers. the starting value can be any value, but the increment is always one.
Foreign Key
The relationship between two tables. The attribute in the second relation is the foreign key. The parent table is the primary key, and the child table is the foreign key. Values of the foreign key must match values of the primary key but they do not need the same column names.
Referential Integrity Constraint
Makes sure that the values in the parent column, primary key, match the values in the foreign key, child column. This constraint enforces that what is in the foreign key is in the primary key.
Null Value
A null value is a missing value in a cell relation. We do not know how to interpret them as they have many different possible meanings. Certain programs can eliminate null values by requiring an attribute value. If nulls are not allowed then the text box can be filled with 'unknown' or some coding system if it is not a text box.
Functional Dependency
Occurs when one value is dependent on another for an answer.
Written as determinant arrow independent function.
The variable on the left. Determines the value on the right.