Flashcards
Duplicate Rows, Tables, Keys. Ect.
Flashcards do conjunto (22)
- 1Why you can't there be duplicate rows?I. Data has no sense of uniqueness. The operations on values would fail.
II. SQL becomes non-orthogonal. Duplicate rows do not produce the same results from different ways of attempting to pull up the same query. For example, an INNER JOIN and Nested Query will bring up different values if the rows are duplicated. - 2Relational TableSet of column definitions, together with a time varying collection of zero or more rows.
- 3Properties of TableI. All values are explicit - no references, formulas, or pointers
II. All values are atomic or non-composite
III. No duplicate rows
IV. Rows are stored inherently, un-ordered.
V. Columns are stored un-ordered, referenced by name. - 4Can you store partial data?No.
- 5Can you extract partial data? If so, what is an example?Yes, you can extract substrings.
- 6True or False: SQL does not remember how you store columns.False
- 7Candidate KeyUnique identifier for rows in a 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 table.
- 8Example of candidate keys:sn in Students, tn in Teachers, sn+cn in Registrations (since they form a unique row in a table together. sn and cn individually have duplicates of their own).
- 9Every relational table has a candidate key because...lack of duplicate rows.
- 10True or False: You can't have multiple candidate keys?False
- 11THE primary candidate keyTHE most useful candidate key chosen by DBA, since it can link to other tables.
- 12Why would sn be a better primary key than, say, Social Security Number?sn can link to other tables, SSN can not.
- 13Alternate KeyNon-primary candidate key.
- 14Can NULLS be unique? Why or why not?Yes and No. Yes, they are valueless, and therefore can't be determined equal by the = operator. No, NULLS can make multiple rows contain NULLS, and therefore that is not unique.
- 15Entity IntegrityPrimary Key is not NULL, but you can use the primary key to find any row.
- 16Foreign KeyColumn(s) in a table that derive their values from primary key(s) of other tables.
- 17Example of Foreign Keysmajor (Students) derives its value from dept (Teachers. tn (Courses) derives its value from tn (Teachers).
- 18Referential IntegrityAll values in a foreign key must be contained in the associated primary key.
- 19Actions that can disrupt Referential IntegrityUPDATE, DELETE, INSERT
- 203 things to do on Foreign Key which will preserve Referential IntegrityI. Cascade - delete/update the values, and the other values in the other tables.
II. Restrict - keep DBAs from updating/deleting
III. Replace deleted data with nulls. Not always legal, since you might put it in an attribute of the primary key. - 21Proper SQL code to create Registrations table?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
); - 22When should you use RESTRICT?If deleting causes a CASCADE to delete multiple sets of data across tables.
Informações
- Descrição
- Database Systems (10/16/17) FlashCards sobre Duplicate Rows, Tables, Keys. Ect., criado por SERGIO GUTIERREZ em 16-10-2017.
- Criado por
- SERGIO GUTIERREZ
- Visualizações
- 2
- Avaliação