mode 5 SQL III

Question 1

What is multiplicity?

Answer 1

Multiplicity describes the relationship between tables/entities.

Question 2

Types of multiplicity?

Answer 2

> MANY-TO-MANY
>owners to cars
>customers to stores
>fridges to healthinspectors

> MANY-TO-ONE
>workers to managers
>carModels to carMake
>fridges to restaurant

> ONE-TO-MANY
>managers to workers
>carMake to carModels
>restaurant to fridges

> ONE-TO-ONE
>currentGovener to state
>person to drivers license
>person to ssn

Question 3

What is referential integrity?

Answer 3

The enforcing of foreign keys referencing valid and existing primary keys

Essentially, referential integrity protects us from having orphan records

Question 4

What is an orphan record?

Answer 4

Because a record w/ a FK references a PK record, the record w/ a FK is DEPENDENT upon the PK record…
meaning the PK record is a parent record to the FK record (aka the child record)

So…if you delete the Parent record then the child record (FK) will no longer have a parent, it will be referred to as an orphan record. REFERENTIAL INTEGRITY protects us from accidentally creating any orphan records.

To delete the parent record, you must first delete the children records (also, you can look into “cascade on delete” OR you could set the child’s FK field to null temporarily)

Question 5

What is a join?

Answer 5

It’s a way to combine two or more tables into a single result set based on some attributes (like a FK)

Question 6

Types of Joins?

Answer 6

> Inner Join
Outer Join
>Left
>Right
>Full
Cartesean Join / Cross Join
(example: shirt size table cartesean join shirt graphics table)
Self Join: (when a table joins with itself)
(example: employee and managers that are also employees. so a reports_to attribute)
(example: person join person ON PPK=spouse_id)
Equijoin: (equijoins are when we use equalities “=”)
(example employee joins otherTable ON EPK = reports_to)
Thetajoin: (theta joins are when we use inequalities like >, Natural Join
N-way join
etc

Question 7

What are set operators

Answer 7

set operators combine result sets into a single NEW result set

Question 8

Types of Set Operators

Answer 8

Union: takes all the data entries in both result sets and puts them into a NEW result set, no duplicates

Union All: takes all the data entries in both result sets and puts them into a NEW result set, yes duplicates

Intersect: takes all the data entries that happen to appear in BOTH result sets and puts them into a NEW result set

Minus: takes all the data entries from the left result set and subtracts any data entries that happen to appear in the right table

Question 9

Sample syntax for set operators

Answer 9

EXAMPLE SYNTAX
SELECT * FROM tableA WHRE attr1=’stuff’ AND attr3=10;
UNION
SELECT * FROM tableB WHRE attr2=5;

Question 10

What are the requirements for set operators?

Answer 10

Requirements for the set operators:

• each result set must have the same number of columns AND
• each column must have the same datatypes [and order of the datatypes].
• The names of the columns don’t matter, they don’t have to match

Question 11

What is normalization?

Answer 11

Normalization is the process of organizing your DB schema to reduce data redundancy and DB inconsistencies

Question 12

Types of Normalization

Answer 12

1st Normal Form (1st NF)

• Each record must have a PK, relevant to the table
• Attributes should be atomic/granular
  
  • –break down attributes into their smallest possible units
  • –no “name” column, you’d do “firstname” and “lastname” columns
• Attributes must be singular
  
  • –no “cust1”, “cust2”, “cust3”…they should be in their own table, then have a multiplicity relationship

> > > > > > > 2nd Normal Form (2nd NF)

• Must be in 1st NF
• All attributes MUST be dependant on the PK
  
  • –to put it another way, attributes in the table should be properties of THIS record
• No parital dependencies
  
  • –this deals with composite keys
  • –example (Author, BookTitle) “Author Age”

> > > > > > > > > 3rd Normal Form (3rd NF)

• Must be in 2nd NF
• No transitive dependencies

Question 13

What is a lookup table?

Answer 13

A lookup table is normally a table that acts as a “master list” for something and you use it to look up a business key value (like “Make”) in exachange for it’s identifier (like the id column) for use in some other table’s foreign key column.

Question 14

What is enum?

Answer 14

Enumeration

Question 15

What is a View?

Answer 15

A view is essentially a projection of 1 or more ACTUAL tables.

The view itself is just a virtual table, it doesn’t store data, it just references data from other tables.
In other words, a view stores a query so you can access massive queries easier

Question 16

What is a materialized view?

Answer 16

It’s a view that DOES store data

Question 17

What is an index?

Answer 17

You can create indexes on a table to speed up searching on a table. It becomes useful when you’re looking in a small range of a table that has a LOT of records.

It’s similiar to a table of contents.