Freddy Duarte

GO CELTICS! The last NBA title by the Celtics was back in 1986, so it was about time! Here are some pictures… Enjoy!

Random

Topics

• A Definition of Foreign Keys (FK);
• 5 Referential Actions in MySQL;
• Advantages and Disadvantages of FK;
• Example SQL scrips.

Note: In mySQL, tables have to be InnoDB to support Foreign key functionality.

Definition

A foreign key is a referential constraint. A foreign key is an attribute in a child table that refers to a primary key in a parent table.

What does ‘Referential Integrity’ mean

• Can’t update or insert a row into a child or referencing table (Product) that doesn’t match a row in the parent or referenced table (Manufacturer)
• What happens to the values on the child table upon change in the parent table is determined by the Referential Actions

CASCADE, SET NULL, RESTRICT, NO ACTION, SET DEFAULT

Referential Actions – What happens to child table as parent table changes (update/delete)

CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table.

SET NULL: Delete or update the row from the parent table and set to null matching rows in child table (Note: FK can’t have the NOT NULL qualifier specified)
These two are the same as having no FK constraint:

RESTRICT: Rejects the delete or update

NO ACTION: (Restrict and No Action are the same)

SET DEFAULT: (…) FK values are set to the column default. (Note: Not supported by InnoDB)

Advantages and Disadvantages

• Help avoid inconsistency in database
• Centralized checking of constraints by the database server
• Cascading updates and deletes can simplify the application code
• Properly designed foreign key rules aid in documenting relationships between tables
• With FK constraints it’s easier to recover from an error that happened in the middle of multiple-record inserts (versus having your code handle this)
• Additional overhead for the database server to perform the necessary checks (probably only relevant with bigger DB systems)

Note: If problems arise when restoring DB from mysqldump, temporarily disable FK checks

MySQL Notes

• Foreign keys can’t be used in Temp Tables
• Tables must be InnoDB
• Foreign key must be of same data type as Primary Key it’s referencing
• Indexes are created automatically for all FK fields

Example SQL Script

CREATE TABLE manufacturer (
mid INT NOT NULL,
mname tinytext,
mphone varchar(15),
PRIMARY KEY (mid)
) ENGINE=INNODB; 

CREATE TABLE product (
pid INT NOT NULL,
pname tinytext,
mid INT NOT NULL,
FOREIGN KEY (mid) REFERENCES manufacturer(mid)
ON UPDATE CASCADE ON DELETE RESTRICT) ENGINE=INNODB;

Presentation Powerpoint: Foreign Keys (PPT)
Presentation PDF: Foreign Keys (PDF)

Tech

What is FTTP?

Fiber to the premises (FTTP) is fiber optic cable that connects the last mile stretch of
wire from a telecommunications network to a premise, which is typically a home or
office. Fiber is based upon light, can travel much faster and longer than electrical signals, and is not affected by electromagnetic interference. FTTP is typically replacing the copper infrastructure or coaxial wires that exist today, because the last mile stretch of cable to a premise plays an important role in the type of service that a home or business receives. It is often the copper infrastructure that cannot meet the increasing consumer needs for high speed transactions, and has sparked the growth in use of Fiber-to-the-premises.

Introduction

Demand for more bandwidth continues to rise and, with more widespread use of network
intensive applications (file sharing, video conferencing, gaming, high-definition video,
etc), there appears to be no end in sight. Over the last 20 years, there has been a
significant and steady growth in bandwidth capabilities. Early dial-up speeds were very
slow relative to current day DSL and Cable Modem technologies, but even these faster
technologies are being pushed to their limits by today’s demanding applications.

These are the driving forces behind efforts to deploy FTTP (Fiber to the Premises). As
with jumps in prior technology changes, FTTP provides significant gains in bandwidth for both home users and businesses(…)

Table of Contents

Introduction...........................4
What is FTTP? .........................5
Evolution of Fiber Optics .............6
Types of Deployment ...................9
Network Architectures..................11
Benefits / Advantages..................14
Limitations / Disadvantages............16
The FTTP Market........................17
Impact on the Organization.............21
Costs .................................21
Alternatives ..........................22
Implications for Managers .............23
The Future of FTTP.....................25

For more info about FTTP and full reserach project presentation and paper:
FTTP Presentation 04/28/2008 – PPT
FTTP Full Research Paper 04/28/2008 – PDF
FTTP Links Page 04/28/2008

Tech