Week 4

Question 1

Normalization

Answer 1

• goal is to avoid unneccessary data duplication
• decomposing relations to well organized relations

Question 2

What does not normalized data look like?

What does normalized data look like?

Answer 2

• LOTS of duplication (Think tons of repeat supplier names for every product)

-make to 3 smaller tables and have no repeats! keep relationships to other tables through foreign keys

Question 3

What is a well-structured relation

Answer 3

• no data redundancy, allowing users to insert/delete/update rows w/o inconsistencies

GOAL: AVOID ANOMALIES

Question 4

What are the types of anomalies that can arise

Answer 4

Insertion: adding new rows forces user to create duplicated data

Deletion: deleting rows may cause a loss of data that may be needed for future rows

Modification: changing data forces changes to other rows because of duplication

Question 5

Rule of thumb about well structured relations

Answer 5

TABLE SHOULD NOT HAVE >1 ENTITY TYPE

(HAVE a lot of foreign keys but one entity type only)

Question 6

Steps in normalization

Answer 6

1. first normal form: no multi value attributes (a divided cell!) so all columns should only have one value (Remove partial dependencies)
2. second normal form: within a single table, no 2 entities should have many to many relatinshiom (remove transitive dependencies)
3. Third normal form: remove any other one to many relationships

Question 7

how do you know something is in a relationship database?

Answer 7

PKs and FKs

Can be searched with SQL queries

Question 8

what is a relation?

Answer 8

two dimensional table of data! not a relationship!!

Question 9

requirements for a relation

Answer 9

• unqniue name
• every att must be singular! no mv
• every row must be unique
• columns must be uniqe
• order of rows and columsn not relevant

Question 10

can keys be simle or composite

Answer 10

yes both!h

Question 11

what is the point of indexes?

Answer 11

indexing!! keys do this!

Question 12

Constraints: 3 types

Answer 12

domain- what are the allowable values for an attribute

entity integrity- PK must be not null and unique

referential integrity- MOST IMPORTANT! consistency!!! if you use FK it must exist

Question 13

3 approaches to ensure referential integrity

Answer 13

restrict: restricting from deleting by cnnecting to FK

cascade: when you delete a parent row litl the relevant dependant cell will also be deleted

set to null: sets dependant cells to null

Question 14

how to create referential integrity constriants??????? in sql

Answer 14

just link the FK’s!!!!

Question 15

how to map entities to relaitons/tables

Answer 15

simple attribute (no mv!): each becames its own column

composite attributes: use only their simple, ocmponent attributes; each mv becomes onw column

Question 16

How to handle foreign keys/primary key?

Answer 16

1. Create the foreign key on the many side!!!
2. ASSOCIATIVE ENTITIES: make everything have a primary key!! even associative entities (itll either be unique or combo of 2 fks)

Question 17

how to handle weak entities in creating an ERD in SQL?

Answer 17

2 WAYS FOR WEAK ENTITIES TO HAVE PK:

a) partial identifier + parent identifier

1. weak entity needs to have the ID of its identifying parent entity
2. set both the partial identifier and the ID of the identifying relationship as Primary keys!

b) surrogate identifier

Question 18

how to handle a ternary relationship?

Answer 18

Create 3+ FKS in the singular entity that has many pointing to it!

Question 19

WHERE DO YOU ADD A FOREIGN KEY?

Answer 19

ON THE MANY SIDE!!! OF THE CARIDNATILITY

Question 20

How to handle composite attributes?

Answer 20

break into separate rows in the entity table

Question 21

why do we call it a relation

Answer 21

mathematics!

Question 22

Relational schema:

How to read it?

Answer 22

1. Each row is an entity, and each cell is an attribute
2. solid underline is the PK
3. Dashed line is the FK
4. Relational arrow? this means a constraint! every time you delete an arrow it checks if you are violating a referential constraint

Question 23

How to handle composite attributes in entity relationship diagrams?

Company address (city, psotal code)

Answer 23

you make each composite attribute its own attribute (each has its own “column” which become a row in the entity)

Question 24

How to handle multivalue attributes
{Skills} in entity relationship diagram?

Question 25

Where do you add the foreign key?

Answer 25

on the many side or the optional side!!! BECAUSE otherwise youll have a column of just blanks on the other table :)

Question 26

How to model the attributes of a relationship in a relational database? (like the box coming off with a dashed line)

Answer 26

As an attribute of the entity with the foreign key on it

Question 27

How to model many to many relationship?

Answer 27

With a table in between! And then two foreign keys in it

If no indicaiton of unique primary key, then a combination of the two FK’s is a composite primary key

Question 27

How to deal with weak entities?

Answer 27

just as regular, but there are several potentially partial identiifier and also mark the strong entity ID as an attribute of the weak entity!!

DONT MARK THE STRONG ENTITY IDENTIFIER AS AN FK!!!