Unit 2 Isys317 Database Concepts The Relational Model

candidate keys

Are keys that uniquely identify each row in a relation.

The unique keys that are not chosen to be the primary key are called ______keys

Candidate

To be a well-formed relation, every ________ in the relation must be a candidate key

Determinant

Which of the following is true about a key

It may be non-unique

Which of the following is not true about null values

Null values cannot be avoided

identity increment

The amount that is added to each keyvalue to create the next key value is called this. Many times it is set to one.

DBO

stands for database owner and it occurs frequently and SQL server. The table names are often listed with DBO preceding the table name as in Dbo.CUSTOMER

sequence function

used by Oracle to define automatically increasing sequences of numbers that can be used as surrogate key numbers. The starting value can be any value such as one but the increment will always be one.

The relational model

was developed and published in 1970 by Edgar Frank cod commonly referred to as EF cod been an employee at IBM and was founded on the theory of relational algebra.

to make a table a relation

we should eliminate the duplicates

Which of the following functional dependency diagrams accurately represents the following situation? - a campus has many buildings - each building has a unique name - each building has many rooms - all rooms in any given building are numbered sequentially starting at 101 - each room has a certain capacity although many rooms in the same building or different buildings may have the same capacity -each room is assigned to a single department - a department may have many rooms in one or more buildings each with the same or different capacities?

(BuildingName, RoomNumber) -->(Capacity, Department)

Explain the concept of a functional dependency

A functional dependency is a relationship that exists among the attributes of a relation, such that if the value of the one attribute or group of attributes is known the value of another attribute(s) can be determined. In a functional dependency, the attributes whose value determines the value of the other attribute is called the determinant. The other attribute whose value is determined by the determinant is said to be functionally dependent on the determinant.

Explain the concept of a surrogate key

A surrogate key is an artificial key that is created to act as the primary key for a relation. The surrogate key is a unique, numeric value that is appended to the relation. Surrogate keys are used in situations where no suitable primary key exists within the user data or when all available primary keys within the data are too cumbersome for an efficient design. Surrogate key values have no meaning to the users and are normally hidden on all forms, reports, and displays. Most DBMS products have the ability to automatically generate values for surrogate keys as needed.

STUDENT (SID, StudentName, Major, AdvisorID) ADVISOR(AdvisorID, AdvisorName, Office, Phone) Given the relations above such that each student is assigned to one advisor, which of the following is true?

AdvisorID is a foreign key.

A Primary Key Is

All Of These

What are the basic steps of the normalization process?

Before starting the normalization process, the relation must first be in first normal form, which means that it meets the basic requirements of being a relation. The first step of the normalization process is to identify all the candidate keys in the relation. The second step is to identify all the functional dependencies in the relation. The third step is to check to see if all the identified determinants are candidate keys. If all determinants are candidate keys then the relation is well formed and nothing more needs to be done. On the other hand, if the relation is not well formed then it is 1. necessary to place the columns of that functional dependency into a new relation 2. Make the determinant of that functional dependency the primary key of the new relation 3. Leave a copy of the determinant in the original relation as a foreign key and 4. Create referential integrity constraints between the original relation and the new relation.

Distinguish between the primary key and a candidate key

Both the primary key and candidate key can uniquely identify the rows in a table. The primary key is the candidate key that is chosen by the database designer, working with the users, to uniquely identify rows and to represent relationships. Although any candidate key could by definition be selected to act as the primary key, the choice of the primary key is often based on design decisions such as the amount of foreign key data that would be generated.

One important relational design principle is that

Every determinant must be a candidate key

What requirements must a two-dimensional table satisfy in order to be a relation?

For a table to be considered a relation it must meet several requirements. First, every cell must contain a single value. Second there can be no duplicate rows. Third each column must have a unique name. Fourth, the order of the columns must have no significance. Fifth, all the values for a given column must be of the same type. Finally the order of the rows have no significance.

The first step of the Normalization process is to

Identify all of the candidate keys of a relation

In practise why would tables that have duplicate rows be allowed.

It is not uncommon for a table that is returned as the result of a data manipulation operation such as a query to contain duplicate rows. This is often tolerated because of the processing time necessary for the DBMS to search the table to find and eliminate duplicate rows. This is especially true if the table is very large. In these cases, it is often acceptable to allow the duplicate rows to exist.

Explain the possible interpretations of a null value

The problem with allowing null values in a table is that the null value is open to three different interpretations. First, a null value in a field may mean no value is appropriate for the field for the given record. Second a null value may mean that the value of that field is known to be blank for the given record. Third a null value may mean the value of that field is not known for the given record.

primary key

This key is the candidate key that is chosen as the key that the database management system will use to uniquely identify each row in a relation.

surrogate key

a column with a unique database management system assigned identifier that he's been added to a table to be the primary key. The unique values of the surrogate key are signed by the database management system each time a row is created and the values never change.

Which of the following is not true about a relation

a relation may have duplicate column names

column

also means attribute

Field

also means column

attribute

also means column

Column

also means field

Row

also means record

table

also means relation

Record

also means row

tuple

also means row

relation

also means table

Row

also means tuple

relation structures

are part of a database schema.

Surrogate Key

are short numeric and never change they are ideal primary keys. Because the values of the surrogate primary key will have no meaning to users they are often hidden on form square he results and reports.

candidate keys

can be single column keys or they can be composite keys.

A composite key

contains two or more attributes which can be unique or non-unique.

identity value

for customer number is set to yes to make a customer number of surrogate key.

When the primary key of one relation is placed in a second relation to represent a relationship, the attribute in the second relation is called a(n) _________key

foreign

primary keys

has his key symbol next to a column name

A relation

is a two-dimensional table consisting of rows and columns that has the specific characteristics.

table

is also known as a file in traditional data processing

file

is also known as a table in traditional data processing

primary key

is important not only because it can be used to identify unique rows but also because it can be used to represent Rows in relationships. It is the candidate key that is chosen as the key that the DBMS will use to uniquely identify each row in a relation.

The table that has duplicate Rows

is not a relation

Oracle

is now called Oracle database because Oracle corporation has grown far beyond its database product routes now owns and sells a large range of products.

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.

A database schema

is the design on which a database and its associated applications are built.

The relational model

is the single most important standard and database processing today

identity seed

is the starting value of the surrogate key

Auto_Increment

is used by my sequel to automatically assign surrogate key numbers. The starting value can be any value but the increment will always be one.

A(n) ________ is one or more columns of a relation that is used to identify a row

key

composite keys

like one column keys can be unique or non-unique

key values

maybe automatically generated by the database management system

Oracle

owns both Oracle database and the MySQL database management system

A rule that requires every value in a foreign key to match values in the corresponding primary key is called a(n) ___________constraint.

referential integrity