Database Modelling: Relationships

Question 1

What are the the relationship (cardinality) options between entities?

Answer 1

1. One-to-many
2. Many-to-many
3. One-to-one
   OPTIONAL: 4. None-at-all

Question 2

What is the natural order of database design?

Answer 2

1. Sketch out entities -> tables
2. Define data type of entities -> columns
3. Determine which columns are primary keys or if need to generate primary keys

NOTE: This is not always a linear process!

Question 3

What is the natural order of database design?

Answer 3

1. Sketch out entities -> tables
2. Define data type of entities -> columns
3. Determine which columns are primary keys or if need to generate primary keys

NOTE: This is not always a linear process!

Question 4

What is the benefit of formaly specifying the relationships between entities in a DBMS?

Answer 4

This allows the DBMS itself to ensure that the relationship between entities is VALID AND MEANINGFUL

Question 5

Do the data types between the primary key and foreign key need to be the same?

Answer 5

YES

Question 6

Do the names of the columns of the primary and foreign key need to be the same?

Answer 6

NO

Foreign key column an have different name as long as the name has a meaning behind it which relates it to the primary key

Question 7

Can the one in a one-to-many relationship have multiple one-to-many relationships?

Answer 7

YES

Question 7

Can the one in a one-to-many relationship have multiple one-to-many relationships?

Answer 7

YES

Question 8

When designing the schema of a database, how do we denote the primary key and foreign key in the boxes which represent tables?

Answer 8

Primary is denoted as PK

Foreign is denotes as FK

Question 9

Why are one-to-one relationships unusual to build?

Answer 9

One-to-one relationships can often share the same key to denote that they share a one-to-one relationship HOWEVER this can then be combined into a single table which makes the one-to-one relationship pointless in most cases.

Question 10

Can you link a many-to-many relationship directly?

Answer 10

No

Question 11

How do you link a many-to-many relationship?

Answer 11

Using a junction/linking table which links the various many-to-many relationships between multiple primary keys between tables. These junction tables have non-unique foreign keys from both tables to allow for multiple links with the same keys.

Question 12

How do you name a linking table?

Answer 12

Combine the names of the two tables in the many-to-many relationship

Question 13

What is a referential constrant?

Answer 13

This is a rule that applies between tables where each table applies the rule as well as cross-referencing its data with other tables to ensure that other tables are also following this rule.

Question 14

What is referential integrity?

Answer 14

This refers to when the data is valid and meaningful between ALL tables and columns within a database

Question 15

What is a cascading delete? What does it maintain?

Answer 15

This is when the removal of a certain row in a table results in all linked rows in other tables to that specific row to also be deleted. This ensures the referential integrity is maintained between tables.

Question 16

What is cascading nullify?

Answer 16

This is where the deletion of data in a row will cause linked rows to return a null value in the column that is directly linked to the deleted data.

E.g. Deleting a customer ID resulting in the foreign key of this ID to be NULL in other tables.

Question 17

What is No Action?

Answer 17

This refers to when attempting to delete data in a row is refused as the data is required to maintain data integrity. This data cannot be deleted until the foreign key is assigned to a new ID.