The Relational Database Model

Question 1

What does the Relational Database Model do?

Answer 1

It enables logical representation of data and its relationshipsW

Question 2

What does logical simplicity yield?

Answer 2

It yields simple and effective database design methodologies

Question 3

What is the logical view facilitated by?

Answer 3

It is facilitated by the creation of data relationships based on a logical construct called a relation

Question 4

What are the 8 characteristics of a relational table?

Answer 4

Table is perceived as a two-dimensional structure composed of rows and columns
Each table row (tuple) represents a single entity occurence within the entity set
Each table column represents an attribute, and each column has a distinct name
Each intersection of a row and column represents a single data value
All values in a column must conform to the same data format
The order of the rows and columns is immaterial to the DBMS
Each table must have an attribute or combination of attributes that uniquely identifies each row

Question 5

What does a key consist of?

Answer 5

A key consists of one or more attributes that determine other attributes

Question 6

What does a key ensure?

Answer 6

It ensures that each row in a table is uniquely identifiable

Question 7

What does a key establish?

Answer 7

It establishes relationships among tables and to ensure the integrity of the data

Question 8

What is a primary key?

Answer 8

It is an attribute or combination of attributes that uniquely identifies a row

Question 9

What is a determination?

Answer 9

It is a state in which knowing the value of one attribute makes it possible to determine the value of another

Question 10

What does a determination establish?

Answer 10

It establishes the role of a key

Question 11

What is a determination based on?

Answer 11

It is based on the relationships among the attributes

Question 12

What is functional dependence?

Answer 12

It is a value of one or more attributes that determines the value of one or more other attributes

Question 13

What is a determinant?

Answer 13

It is an attribute whose value determines another

Question 14

What is a dependent?

Answer 14

It is an attribute whose value is determined by the other attribute

Question 15

What is a composite key?

Answer 15

It is a key that is composed of more than one attribute

Question 16

What is a key attribute?

Answer 16

It is an attribute that is a part of a key

Question 17

What is a superkey?

Answer 17

It is a key that can uniquely identify any row in the table

Question 18

What is a candidate key?

Answer 18

It is a minimal superkey

Question 19

What is a foreign key?

Answer 19

It is the primary key of one table that has been placed into another table to create a common attribute

Question 20

What is a secondary key?

Answer 20

It is a key that is used strictly for data retrieval purposes

Question 21

What is entity integrity?

Answer 21

It is when all primary key entries are unique, and no part of a primary key may be null

Question 22

What is the purpose of entity integrity?

Answer 22

Each row will have a unique identity, and foreign key values can properly reference primary key values

Question 23

What is referential integrity?

Answer 23

It is when a foreign key may have either a null entry, as long as it is not a part of its table’s primary key, or an entry that matches the primary key value in a table to which it is related (every non-null foreign key value must reference an existing primary key value)

Question 24

What is the purpose of referential integrity?

Answer 24

It is possible for an attribute not to have a corresponding value, but it will be impossible to have an invalid entry; the enforcement of the referential integrity rule makes it impossible to delete a row in one table whose primary key has mandatory matching foreign key values in another table

Question 25

What could a null represent?

Answer 25

An unknown attribute value
A known, but missing, attribute value
A “not applicable” condition

Question 26

What are some ways to handle nulls?

Answer 26

Flags
Constraints

Question 27

What is a flag?

Answer 27

It is a special code used to indicate the absence of some value

Question 28

What is a constraint? (2)

Answer 28

NOT NULL constraint: placed on a column to ensure that every row in the table has a value for that column
UNIQUE constraint: restriction placed on a column to ensure that no duplicate values exist for that column

Question 29

What property do relational operators have?

Answer 29

They have the property of closure

Question 30

What is closure?

Answer 30

It is a property of relational operators that permits the use of relational algebra operations on existing tables (relations) to produce new relations

Question 31

What is select?

Answer 31

It is a unary operator that yields a horizontal subset of a table (slide 17)

Question 32

What is project?

Answer 32

It is a unary operator that yields a vertical subset of a table (slide 18)

Question 33

What is union?

Answer 33

It combines all rows from two tables, excluding duplicate rows (slide 19)

Question 34

What is union compatible?

Answer 34

It is when tables share the same number of columns, and their corresponding columns share compatible domains

Question 35

What is intersect?

Answer 35

It is a relational set operator that yields only the rows that appear in both tables
Tables must be union-compatible to yield valid results (slide 20)

Question 36

What is difference?

Answer 36

It is a relational set operator that yields all rows in one table that are not found in the other table
Tables must be union compatible to yield valid results (table 21)

Question 37

What is product?

Answer 37

It is a relational operator that yields all possible pairs of rows from two tables

Question 38

What do joins do?

Answer 38

They allow information to be intelligently combined from two or more tables

Question 39

What is a natural join?

Answer 39

It links tables by selecting only the rows with common values in their common attribute

Question 40

What is an inner join?

Answer 40

It only returns matched records from the tables that are being joined

Question 41

What is an outer join?

Answer 41

It is when matched pairs are retained and unmatched values in the other table are left null

Question 42

What is a left outer join?

Answer 42

It is a join yields all of the rows in the first table, including those that do not have a matching value in the second table

Question 43

What is a right outer join?

Answer 43

It is a join that yields all of the rows in the second table, including those that do not have matching values in the first table

Question 44

What is a data dictionary?

Answer 44

It is a description of all tables in the database created by the user and designer

Question 45

What is a system catalogue?

Answer 45

It is a system data dictionary that describes all objects within the database

Question 46

What is one-to-many (1:M)?

Answer 46

It is the norm for relational databases

Question 47

What is a one-to-one (1:1)?

Answer 47

It is one entity that can be related to only one other entity and vice versa

Question 48

What is a many-to-many (M:N)?

Answer 48

It is implemented by creating a new entity in 1:M relationships with the original entities

Question 49

What is a composite entity?

Answer 49

It is an entity that helps avoid problems inherent to M:N relationships