Superkey
Definition:
A superkey of a relation is a set of attributes whose values can uniquely identify every potential tuple of the relation.
关系的超级键是一组属性，这些属性的值可以唯一地标识关系的每个元组。
Some claims:
Every relation has at least one superkey.
Any superset of a superkey is a superkey. //含有超级键的集合也是超级键
From a given state of a relation, we may determine whether a set of attributes of the relation does not form a superkey, but we can not determine if a set of attributes forms a superkey.
【Superkey & key】e.g.
For relation Students (ID, Name, Home_Address, Birthday, GPA)
ID is a key.
{ID, Name} is a superkey but not a key.
{Name, Home_Address, Birthdate} is also a key.
【Candidate Key】e.g.
Find all possible candidate keys for the following relation based on its current tuples.
| A | B | C | D |
|---|---|---|---|
| a1 | b1 | c1 | d1 |
| a1 | b2 | c2 | d1 |
| a2 | b2 | c1 | d1 |
| a2 | b1 | c2 | d1 |
Answer:
{A,B}, {A,C}, {B,C}
Primary key
A primary key of a relation is the candidate key chosen by the database designer for a particular application.
- The primary key of each relation is chosen and declared at the time when the relation is defined. Once chosen, it cannot be changed.
- The primary key is usually chosen to be the candidate key that has the smallest number of attributes to improve both storage efficiency and query processing efficiency.
- With the primary key defined, only the values under the attributes in the primary key need to be checked for identifying duplicate when new tuples are inserted (index is often used).
- The primary key of a relation is often used in references from other relations.
Foreign Key
A set of attributes FK of relation R1 is a foreign key in R1 if it satisfies the following two conditions:
- There is a relation R2 with the primary key PK such that FK and PK have the same number of attributes with compatible domains.
- For any tuple t1 in R1, either there exists a tuple t2 in R2 such that t1[FK] = t2[PK] or t1[FK] is null.
【Foreign Key】e.g.
Table Employees
| EID | Name | Age | Dept-Name |
|---|---|---|---|
| 123456789 | John | 45 | Sales |
| 234567891 | Mary | 42 | Service |
| 2345678912 | Bob | 39 | null |
Table Departments
| Name | Location | Manager |
|---|---|---|
| Sales | Shanghai | Bill |
| HR | Shenzhen | Charles |
| Service | Beijing | Maria |
Dept_Name of Employees is a foreign key referencing Name of Departments
- Foreign keys need to be explicitly defined.//需要显式定义外键
- R1 and R2 in the definition could be the same relation, for e.g.
Employees(ID, Age, Salary, Position, Manager_ID).
The primary key in the definition may be replaced by a candidate key.
约束
静态约束：必须始终保持在数据项之间。
e.g.
credits taken each semester of a student must be less than 24
employee.salary < manager.salary
IN employee, manager
WHERE employee.mgr# = manager.mgr#
学生每学期的学分必须少于24学分。
employee.salary<manager.salary
员工、经理
其中employee.mgr=manager.mgr
动态约束：必须在关系更改前后的数据库状态之间保留
每个学生的总学分不得在连续学期内减少。
完整性约束可能与几个表相关。
数据定义语言(DDL)
CREATE
used to create new objects (tables, views, etc.) in the database
ALTER
used to change the structure of an existing object
DROP
used to remove the object from the database, (all its data plus any reference to it in the data dictionary)
E.G.
(1) CREATE
CREATE TABLE emp
(
empno NUMBER NOT NULL,
ename CHAR(10) ,
job CHAR(9),
mgr NUMBER (4),
hiredate DATE ,
sal NUMBER(10,2),
comm NUMBER(9,0) ,
deptno NUMBER(4) NOT NULL
);
e.g. ADD Some constrains
CREATE TABLE emp
(
empno NUMBER NOT NULL primary key,
ename CHAR(10) NOT NULL CHECK (ename = UPPER (ename))
job CHAR(9),
mgr NUMBER REFERENCES emp(empno),
hiredate DATE CHECK (hiredate <=SYSDATE),
sal NUMBER(10,2)CHECK sal > 500,
comm NUMBER(9,0) DEFAULT NULL,
deptno NUMBER(2) NOT NULL REFERENCES dept(deptno)
);
(2) ALTER
- To add a new column
ALTER TABLE <tablename>ADD (column_name datatype);
ALTER TABLE emp ADD (gender CHAR(1));
- To change the definition of an existing column.
ALTER TABLE <tablename> MODIFY (column_name datatype);
ALTER TABLE emp MODIFY (deptno NUMBER(6));
- To drop a column
ALTER TABLE <tablename> DROP (column_name)
ALTER TABLE student DROP Address;
(3) DROP
If we want to permanently remove the student table that we created:
DROP TABLE student
DML modifying contents
INSERT
allows insertion of records into a table
UPDATE
updates existing rows in a table
DELETE
removes unwanted rows from a table
E.G.
（1）INSERT
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(7500, 'CAMPBELL', 'ANALYST', 7566, '30-OCT-1992', 24500,0, 40);
（2）UPDATE
1)
UPDATE emp SET comm = 0;
To give a 15% raise to all Analysts and Clerks in department 20 could use;
UPDATE emp
SET sal = sal* 1.15
WHERE (job = 'ANALYST' OR job = 'CLERK') AND deptno = 20;
（3）DELETE
- To remove from EMP all sales staff who made less than 100 commission last month enter:
DELETE FROM emp
WHERE job = 'SALESMAN'
AND comm < 100;
- To delete everything in a table :
DELETE FROM <tablename>;
数据库范式：
1NF（First Normal Form）：当且仅当所有域只包含原子值，即每个分量都是不可再分的数据项，则称实体E满足第一范式
2NF（Second Normal Form）：当且仅当实体E满足第一范式，且每一个非键属性完全依赖主键时，满足第二范式
3NF（Third Normal Form）：当且仅当实体E是第二范式（2NF），且E中没有非主属性传递依赖时，满足第三范式
