A First Course in Database Systems - notes ch2

Notes:

• Relational Model = 2-dimension table. 
• Data model: describe the data - 1.data structure(not as the struct in C but in higher level)    2. operation like query and modification   3. constraints (e.g.  month goes from 1 to 12).
• [ Schema ] is sth like relation title(attribute1, attribute2...), e.g. Movies(name, year, length, genre).
• [ domain ] is similar to data type in C/Java, e.g. Student(ID:integer, name:string)
• [ instance of the relation] : a set of tuples for a given relation.
• [ key ] : formed by a set of attributes which not allowed two instances with the same key. e.g. key = ID, (001, Sean), (001, Lee) not allowed.
• Basic SQL data type:

CHAR(n), VARCHAR(n);/* the difference is implementation=dependent, typically CHAR implies that short strings are padded to make n characters while VARCHAR implies that an endmarker or string-length is used*/

BIT(n), BIT VARYING(n);

BOOLEAN  /* TRUE, FALSE, UNKNOW */;

INT/INTEGER, SHORTINT;

FLOAT/REAL, DOUBLE PRECISION, DECIMAL(n, d)   /* n decimal digits , d digits after point */;

DATE, TIME  /*  DATA='1912-05-10'    TIME='21:00:20'  / "14:12:30.5" */;

• Basic SQL operation

delete a relation:                                        DROP TABLE R;

modify schema must start with:               ALTER TABLE

add attribute:                                               ALTER TABLE MovieStar ADD phone CHAR(16);

delete attribute:                                           ALTER TABLE MovieStar DROP birthdate;

default value:                                               gender CHAR(1)  DEFAULT  '?'

key declaration:                                           PRIMARY KEY, UNIQUE;   only PRIMARY KEY(title, year)

• relational algebra:

Union: R∪S

Intersection: R∪S

Difference: R-S

Projection some columns:pL(R) 

Selectionof some rows:sC(R)

Combiningtables:R´S 

Theta Join, Natrual Join, Cartesian product;