Duplicate Rows, Tables, Keys Ect.

Página 1

Duplicate Rows

Examples: Registrations Tables

S R

S1 Smith S1 C1

S1 Smth S1 C2

S2 Jones S2 C1

S3 Blake

Find courses(cn) Smith is in

Do an INNER JOIN, result:

S1 Smith S1 C1

S1 Smith S1 C1

S1 Smith S1 C2

S1 Smith S1 C2

S2 Jones S2 C1

SELECT cn

FROM S INNER JOIN R

ON S.sn=R.sn

WHERE sname = "Smith"

OUTPUT:

C1

C1

C2

C2

NESTED

SELECT cn

FROM Registrations

Where sn IN (

SELECT sn

FROM Students

WHERE sname = 'Smith'

)

Why you should not have duplicate rows:

If SQL allows duplicate rows, SQL becomes non-orthogonal

If you have duplicates, you can not have unique values.

Definitions

Relational table - set of column definitions, together with a time varying collection of zero or more rows

Properties of table:

All values are explicit. No pointers, no references, no formulas

All values are atomic, or indivisible on insertion, and updating. You can't store an array, or another table.

You CAN extract part of the data, like a substring, but you can't store partial data.

No duplicate rows.

Rows are stored inherently, un-ordered

Columns are un-ordered, referenced by name

It does remember, HOW you store the columns.

Candidate key - a unique identifier for the rows of the table.

An attribute or set of attributes that are used : unique (no duplicates) and minimal (smallest set of attributes to get uniqueness), used to identify a row in a sable

Ex: sn in Students, tn in Teachers, sn + cn in Registrations

Every relational table has a candidate key, due to a lack of duplicate rows.

You can have multiple candidate keys

THE primary candidate key - The candidate key chosen by the DBA, that links to other tables.

Ex: Say you added column such as SSN in Students, it would be unique, however sn is more useful since it links to other tables.

Alternate key - A non-primary candidate key

NULLS can and can't be unique. The valueless allows them to not be compared with the "=", but they can create multiple rows with NULLS.

Entity Integrity

Primary key is not null, you can use the primary key to find any row.

Foreign key - A column, or columns, in one table that draws its values from the primary key of another table.

Ex: major (Students) derives is values from dept (Teachers).

Ex: tn (Courses) derives its value from tn in Teachers.

Referential integrity - All values in a foreign key must be contained in the associated primary key.

Ex: having a 'C7' in cn column of Registrations. C7 does not exist.

UPDATE, DELETE, INSERT can disrupt referential integrity

3 things you can do to foreign key, on UPDATE/DELETE/INSERT to keep referential integrity:

Cascade - Change/Delete all C7 from all tables. Can be dangerous.

Restrict - Keep DBA from updating/deleting the data

Set NULL - make foreign key value NULL on changing/deleting primary key value. Not always legal, since you might put it in an attribute of the primary key.

CREATE TABLE Registrations (

sn int NOT NULL,

cn int NOT NULL,

PRIMARY KEY Reg (sn,cn),

INDEX(sn,cn)

FOREIGN KEY (sn) References

Students (sn),

FOREIGN KEY (cn) References

Courses(cn)

ON UPDATE CASCADE

ON DELETE RESTRICT

);

If deleting causes a CASCADE to delete multiple sets of data across tables, use RESTRICT