Week 4 Flashcards
Normalization
- goal is to avoid unneccessary data duplication
- decomposing relations to well organized relations
What does not normalized data look like?
What does normalized data look like?
- 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
What is a well-structured relation
- no data redundancy, allowing users to insert/delete/update rows w/o inconsistencies
GOAL: AVOID ANOMALIES
What are the types of anomalies that can arise
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
Rule of thumb about well structured relations
TABLE SHOULD NOT HAVE >1 ENTITY TYPE
(HAVE a lot of foreign keys but one entity type only)
Steps in normalization
- first normal form: no multi value attributes (a divided cell!) so all columns should only have one value (Remove partial dependencies)
- second normal form: within a single table, no 2 entities should have many to many relatinshiom (remove transitive dependencies)
- Third normal form: remove any other one to many relationships
how do you know something is in a relationship database?
PKs and FKs
Can be searched with SQL queries
what is a relation?
two dimensional table of data! not a relationship!!
requirements for a relation
- 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
can keys be simle or composite
yes both!h
what is the point of indexes?
indexing!! keys do this!
Constraints: 3 types
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
3 approaches to ensure referential integrity
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
how to create referential integrity constriants??????? in sql
just link the FK’s!!!!
how to map entities to relaitons/tables
simple attribute (no mv!): each becames its own column
composite attributes: use only their simple, ocmponent attributes; each mv becomes onw column
How to handle foreign keys/primary key?
- Create the foreign key on the many side!!!
- ASSOCIATIVE ENTITIES: make everything have a primary key!! even associative entities (itll either be unique or combo of 2 fks)
how to handle weak entities in creating an ERD in SQL?
2 WAYS FOR WEAK ENTITIES TO HAVE PK:
a) partial identifier + parent identifier
- weak entity needs to have the ID of its identifying parent entity
- set both the partial identifier and the ID of the identifying relationship as Primary keys!
b) surrogate identifier
how to handle a ternary relationship?
Create 3+ FKS in the singular entity that has many pointing to it!
WHERE DO YOU ADD A FOREIGN KEY?
ON THE MANY SIDE!!! OF THE CARIDNATILITY
How to handle composite attributes?
break into separate rows in the entity table
why do we call it a relation
mathematics!
Relational schema:
How to read it?
- Each row is an entity, and each cell is an attribute
- solid underline is the PK
- Dashed line is the FK
- Relational arrow? this means a constraint! every time you delete an arrow it checks if you are violating a referential constraint
How to handle composite attributes in entity relationship diagrams?
Company address (city, psotal code)
you make each composite attribute its own attribute (each has its own “column” which become a row in the entity)
How to handle multivalue attributes
{Skills} in entity relationship diagram?
