An Introduction to Database Systems (8th Edition)

Author: C. J. Date
3.3
All Stack Overflow 11
This Month Hacker News 1

An Introduction to Database Systems (8th Edition)

3.3

Review Date:

Comments

by anonymous   2017-08-20

a database table is a mathematical relation, in other words a predicate and a set of tuples ("rows") for which that predicate is true. that means each "row" in a "table" is a (true) proposition.

this may all look scary but the basic principles are really simple and worth knowing and applying rigorously: you'll better know what you're doing.

relations are simple if you start small, with the binary relation. for example, there's a binary relation > (greater than) on the set of all integers which "contains" all ordered pairs of integers x, y for which the predicate x > y holds true. note: you would not want to materialize this specific relation as a database table. :)

you want Books, Authors, Publishers and Users with their bookshelfs (Read, Currently Reading, Want to Read). what are the predicates in that? "user U has read book B", "user U is reading book B", "user U wants to read book B" would be some of them; "book B has ISBN# I, title T, author A" would be another, but some books have multiple authors. in that case, you'll do well to split it out into a separate predicate: "book B was written by author A".

CREATE TABLE book (
  id INT NOT NULL PRIMARY KEY
);
CREATE TABLE author (
  id INT NOT NULL PRIMARY KEY
, name TEXT NOT NULL
);
CREATE TABLE written_by (
  book INT NOT NULL REFERENCES book (id)
, author INT NOT NULL REFERENCES author (id)
);
CREATE TABLE reader (
  id INT NOT NULL PRIMARY KEY
);
CREATE TABLE has_read (
  reader INT NOT NULL REFERENCES reader (id)
, book INT NOT NULL REFERENCES book (id)
);
CREATE TABLE is_reading (
  reader INT NOT NULL REFERENCES reader (id)
, book INT NOT NULL REFERENCES book (id)
);
CREATE TABLE plans_reading (
  reader INT NOT NULL REFERENCES reader (id)
, book INT NOT NULL REFERENCES book (id)
);

etc etc.

edit: C. J. Date's Introduction to Database Systems

by anonymous   2017-08-20

If you are new to the subject then Introduction to Database Systems by Chris Date is a good place to start before you get onto database design.

http://www.amazon.com/Introduction-Database-Systems-8th/dp/0321197844

For books specifically about design:

http://www.amazon.com/Information-Modeling-Relational-Databases-Management/dp/0123735688

http://www.amazon.com/Practical-Issues-Database-Management-Practitioner/dp/0201485559

by anonymous   2017-08-20

A functional dependency defines a functional relationship between attributes. For example: PersonId functionally determines BirthDate (normally written as PersonId -> BirthDate). Another way of saying this is: There is exactly one Birth Date for any given given any instance of a person. Note that the converse may or may not be true. Many people may have been born on the same day. Given a BirthDate we may find many PersonId sharing that date.

Sets of functional dependencies may be used to synthesize relations (tables). The definition of the first 3 normal forms, including Boyce Codd Normal Form (BCNF) is stated in terms of how a given set of relations represent functional dependencies. Fourth and fifth normal forms involve Multi-Valued dependencies (another kettle of fish).

Here are a few free resources about Functional Dependencies, Normalization and database design. Be prepared to exercise your brain and math skills when studying this material.

The following are "slide shows" from various academic sites...

The following are academic papers. Heavier reading but well worth the effort.

If you are seriously interested in this subject I suggest you put out the cash for a good book on the subject of Relational Database Design. For example: An Introduction to Database Systems by C.J. Date