Database Management FAQ - Test 1

Can the two foreign key fields in a bridge table for a M:N relationship be defined as optional (null) or should they be defined as required (non-blank)?

- A bridge table represents the actual combinations of records from the two tables --We would never put a "half record" in the bridge - The foreign key fields in a bridge table for a M:N relationship should be required

Should I use a composite primary key or simple primary key in a bridge table?

- A bridge will always have (at least) two FKs --More than two if it is relating records from more than two tables - Bridge also needs primary key 1. Create new PK 2. Make both FKs a composite PK

What is a composite key?

- A simple key consists of one field - A composite key is made up of two or more fields

How are indices stored?

- A variety of elegant data structures are available for implementing secondary indices - Conceptually, these data structures can be thought of as a set of pointers that link the physical records in the desired secondary order

How are M:N relationships stored?

- Create a bridge table --Has at least two foreign keys for each entity

What is normalization?

- Database design process - Objective is to remove interdependent and redundant data in order to improve the overall quality of the information held in the database - Minimizes storage space - Reduce chance of simple typos - Improved data quality' - Does not lead to database simplification --More tables and relationships --Doesn't run as fast (about quality, not speed)

Should the bridge table have a simple PK, a composite PK, or no PK at all?

- Every table needs a primary key - Up to you to decide if you want a simple one or composite one

Can a child's foreign key have a different name than the parent's primary key?

- Primary and foreign keys frequently have the same names --But it is possible to use entirely different names for the fields

What is an index or secondary index?

- Primary key (primary index) serves as the main identifier for the rows/records of a table - If the table is very large, then accessing the records in a non-key order is likely to be very slow --That's when it is handy to have a pre-built secondary indices --Include important secondary indices and discard the unimportant ones - Secondary index makes searching things up faster

Is a unary relationship the same as a one-to-one relationship?

- Unary --The parent and child records are in the same table - 1:1 --A parent record can only have one child record

Can a table have more than one primary key?

- You can have composite primary keys - You can have a primary key and a secondary index - But you can't have two primary keys

How are 1:1 relationships stored?

1. Decide which table will be designated as the parent and which as the child 2. Store the primary key of the parent as a foreign key in the child 3. Prevent two records becoming associated with the parent - Create an index on the foreign key field and restrict it not allowing any duplicates --The foreign key would be a unique index - OR Store the primary key of the child as a foreign key in the parent

What is 1NF?

1. Every table should have a primary key 2. All compound fields and delimited fields should be split into separate fields 3. All lists and repeating fields should be moved into a new table (with one record for each of the original non-null repeating fields) - The new table should be related back to the original table by a foreign key

Should every foreign key have a primary key?

99.9% of the time, the foreign key matches the primary key of the parent - Technically, however, the relational database model allows us to link the parent and child records with a field (or fields) other than the parent's primary key

What is a unary relationship?

A relationship between the rows of a single table

Can a table have more than one foreign key?

A table can have any number of foreign keys

What is 3NF?

All data fields must be independent of the other data in the table - They don't need to be independent of the primary key. They just need to be independent of the non-PK fields. - The implications of the third rule are: 1. All mathematically computable fields should be eliminated 2. All 'look up' values should be moved to a new (parent) table --The original table should be related back to the new table with a FK

What is 2NF?

All data must depend on the table's entire primary key - Each table should be about a single subject - You should not have fields in a table that only depend on part of the primary key

Which side of the X-Z relationship is the child?

If there is a 1:1 relationship between two tables, then you are free to decide which table acts as the parent and which acts as the child - FK is placed on the child side --Not both sides

What is de-normalization?

Intentionally violating some of the rules of normalization in order to improve the speed of our queries

Should every table have a foreign key?

It is not necessary - Foreign keys are pointers to parent tables that are "higher" up in the database - Tables at the "top" of a database will not have foreign keys --Most corporate databases have a natural top/bottom hierarchical structure to the table organization. Not an absolute requirement.

What do the records actually look like in a bridge table?

LOOK AT FAQ

Are dates and times compound fields?

No

Does a unary relationship have to be many-to-many?

No

Should street address be split into a street number and a street name?

No

Can a database be in 2NF and 3NF, but not in 1NF?

No Must satisfy all previous rules

Should every primary key have a foreign key?

No, it is not necessary

What is the difference between a primary and foreign key?

PRIMARY KEY - Uniquely identifies the records in a table FOREIGN KEY - Links a (child) record to the (parent) record that "owns" it

How are 1:N relationships stored?

Put FK in child to point to parent

What is referential integrity?

The term that means we want to prevent a child record from pointing to an unknown parent record - A child record may not have a parent, but if it does have a parent, then it must be a "real" parent

Can I create extra fields like DriverID or should I only use the ones in the initial table?

Yes

Can a child's FK field have a different name than the parent's PK field?

Yes - But PK and FK frequently have the same names - There are times when you absolutely must use different names

Can the N foreign key fields in a bridge table for an N-ary relationship be defined as optional (null) or should they be defined as required (non-blank)?

Yes But at least two of the FK values must be non-null

Can non-key data fields in different tables have the same name? For instance, can Name and Address be in the Buyer table and in the Seller table?

Yes But you cannot have two fields with the same name in ONE table

Can a unary relationship be many-to-many?

Yes But you will need to create a bridge table in order to actually implement it

Can a table have more than one unary relationship?

Yes LOOK AT FAQ

Can a bridge table be related to only one table?

Yes (LOOK AT FAQ) - M:N unary relationship --Bridge is related to a single table, but it is related in two different ways

Can a bridge table be related to more than two tables?

Yes (N-ary) - Most common between two tables though

Can a bridge table be used to represent a 1:N relationship or is it only used for M:N relationships?

Yes but it is very unnecessary

Can a field be a primary and a foreign key at the same time?

Yes, a field can simultaneously be a primary key and a foreign key - It is fairly common for a field to be a FK and part of a composite PK - It is less common, but perfectly legal, for a field to be a FK and an entire PK

What is an optional relationship?

You can have relationships that are mandatory each way or optional each way regardless of the number of related records in each entity

Should every table have a primary key?

You should REALLY define a primary key for each table PROBLEM - Can't uniquely identify record