Nitemice

Because I’ve been spending all my time studying lately, the only thing I’ve had time to write is study notes. So I thought that I’d make the most of them, share a bit of my new knowledge with you, and give you a quick crammer’s guide to some of the topics from each of my subjects this semester. The information probably won’t mean a whole lot to you if you’ve never learnt anything about the topic before, but if you have, hopefully, this’ll clear up a few things for you.

This one’s about Database Theory. This subjects covers all about designing databases, and modelling them, as well as the basics of SQL to get the information you want out of them.

Data Anomalies

Inconsistencies in your data that occur due to bad design. There are 3 types of data anomalies.
Update anomaly – Where updating a tuple(row) leaves other occurrences of the same information un-updated. E.g. An address change has to be updated in every tuple where that company appears, instead of just once

Deletion anomaly – Where valid attributes are lost when a tuple is deleted. E.g. A company no longer exists in the database if the only employee leaves

Insertion anomaly – Where certain attributes can’t be inserted without the presence of others. E.g. A company can’t be added without an employee

Self-Join

If you want to find tuples (rows) which match on a particular attribute, you can join the table to itself, and use a condition of `table1.attribute = table2.attribute`. This however will leave you with duplicates like below.

Adding a condition using `<` or `>` will remove one of them, leaving the one in alphabetical/reverse alphabetical order. It will also eliminate self-matches like the last row above.

Functional Dependencies & BCNF

It seems super strange, but if no functional dependencies hold on a relation (table), then no FDs exist to violate BCNF. Therefore, the relation is in BCNF!

Mapping Relations

There are two ways to map a relationship from an ER diagram: as a separate relation, or as part of an existing relation. If you’re making it a separate relation, then the keys of each entities connected to the relationship go in a new relation, and the key from the entity with the most connections provides the primary key.

The order of connection is: Many > One > ExactlyOne. If both are many, they both are the primary key; if they’re both one, either can be the primary key.

If you choose that latter method, the key of the entity with the least connections is added  as a foreign key to the relation of the entity with the most connections.

Until Next Time,

Nitemice

P.S. I was watching Chuck while I wrote this, so that’s why my examples have a particular flavour.

Related articles

• Crammer’s Guide – Y01S01: Computer System Fundamentals (nitemice.com)
• Crammer’s Edition – Maths for Computing (nitemice.com)
• Crammer’s Guide – Y01S01: Java Programming 1 (nitemice.com)