Unit 3 - Relational Model

Question 1

Data model

Answer 1

Collection of high-level data description constructs (e.g., tables and links) that hide many low-level storage details

Question 2

Relational model

Answer 2

Data is in table(s) (aka RELATIONS, not relationships)

Question 3

What do rows and columns of relation represent?

Answer 3

Rows are tuples or individual records and columns are attributes (or fields)

Question 4

Can two relations have the same name?

Answer 4

No, name of relation must be distinct

Question 5

Can two attributes have the same name if they’re in different relations?

Answer 5

Yes.
In the same relation, each attribute must have a distinct name (we cannot have two or more attributes with the same name)
However, two attributes in different relations can have same name.

Question 6

Can a cell in a relation have a composite value?

Answer 6

No - each cells contains exactly one atomic (single) value

Question 7

Can you have different types of values in a column?

Answer 7

No - values of an attribute are all from the same domain

Question 8

Can you have two duplicate tuples?

Answer 8

No - no duplicate tuples.

Question 9

What order must the attributes be in?

Answer 9

None. Order of attributes has no importance.

Question 10

What order must the tuples be in?

Answer 10

None. Tuples can appear in any order (but order may impact efficiency)

Question 11

What’s a schema?

Answer 11

Header of table: relation’s name, name of each field/attribute, domain of each field

e.g., Students (sid : string , name : string , age : integer , gpa : real)

Question 12

From ER to relations - Step 1: Translate Entity Sets to Relations Steps

Answer 12

Step 1: each atomic attribute of an entity set becomes an attribute (field) in the relation schema; entity set name = relation name; underline primary key

Question 13

How do you translate a composite attribute in an ER diagram to schema?

Answer 13

Concatenate the root attribute + composite attribute (order of concatenation doesn’t matter) and that becomes the attribute/field

e.g., let’s say Name is composite and its atomic attributes are First and Last. The employee relation is,
Employee (SSN : integer, First_Name : string, Last_Name : string)

Question 14

What do derived attributes look like in a relational schema?

Answer 14

They are NOT included

Question 15

What if there’s a multi-value attribute in the ER? (e.g., multiple email addresses associated with an employee)

Answer 15

You should break that table into two tables:
1. original table minus the email column, and
2. each email address + identifier

Question 16

When we create more tables from an existing table, what becomes the primary key in the new tables?

Answer 16

Usually it’s a concatenated key (e.g., Skillset + SSN), but remember one of the rows is primary key from the previous table (so it’s a FOREIGN key)

Don’t forget to add an arrow from foreign key to primary key!

Question 17

What are the steps to translate entity sets to relations (tables)?

Answer 17

Step 1: place all single-value attributes in the main relation
Step 2: for each multi-value attribute, create a new relation; determine the composite primary key in new relation (multi value attribute is partial key + primary key from prev table is foreign key)
Step 3: Add an arrow from foreign key (of the new relation) to primary key (of the main relation)

Question 18

How to translate relationship set to relation?

Answer 18

Create schemas, then add the primary key of one of the schemas as a foreign key to the other schema

Question 19

How to translate M:N relationship set to relation?

Answer 19

Create a third schema/table that represents the relationship set - its composite primary key will be the primary keys of the two schemas.

Question 20

Example: you have entity set S, relationship set ‘makes’ and weak entity set T.
What is the primary key for weak entity set T?

Answer 20

It’s a composite primary key:
Primary key S + primary key of T
^ this is foreign key of T (from S)

Question 21

What are the three main problems of data redundancy?

Answer 21

Insertion, Update, Deletion
^ Anomalies

Question 22

What is Insertion Anomaly with data redundancy?

Answer 22

For all 1000 records, the same data must be input correctly 1000 times.

Question 23

What is Update Anomaly with data redundancy?

Answer 23

If one field changes, and there are 1000 records, must update it 1000 times.
If we miss one, data inconsistency.

Question 24

What is Deletion Anomaly with data redundancy?

Answer 24

Let’s say each record has info on the student and the department.
If the last student is deleted, we lose all stored info about the department!

Question 25

What is a non-normalized table?

Answer 25

A table with data redunancy

Question 26

What is normalization?

Answer 26

Techniques/ rules that divide the table into sub-tables (to reduce redunancy)

Question 27

What is denormalization?

Answer 27

Redundant data is ADDED to improve performance
Tables are COMBINED

Question 28

First Normal Form (1NF) - what should a table have in order to be in the 1NF?

Answer 28

GET RID OF ARRAYS

Each cell should only hold single (atomic) attributes
Columns must have same domain
All columns should have unique names

Question 29

Second Normal Form (2NF) - what should a table have in order to be in 2NF?

Answer 29

Must be in 1NF
Should NOT have partial dependency

Question 30

What is partial dependency (2NF)?

Answer 30

Primary key MUST be a single attribute.
If composite primary key, then each non-key attribute must be FULLY dependent on the ENTIRE primary key and not a subset of the primary key.

Question 31

Third Normal Form (3NF) - what should a table have in order to be in 3NF?

Answer 31

Must be in 2NF
It doesn’t have transitive dependency

Question 32

What is transitive dependency (3NF)?

Answer 32

When a non-key attribute is functionally dependent on another non-key attribute

Question 33

How to solve transitive dependency?

Answer 33

Transitive dependency is when a non-key attribute is functionally dependent on another non-key attribute.
Put both those attributes in a separate table. (can keep one in previous table too)

Question 34

Do you want to reduce data redundancy (i.e., do you want to normalize a table)?

Answer 34

It depends.
Sometimes, having data redundancy (and denormalizing tables) is better
You gain query performance - if you normalize, you’d have to use ‘join’ operator which is costly