Unit 2 Flashcards
(34 cards)
Primary Key
attribute with information that uniquely identifies each instance in the table. There can only be one primary key.
Candidate Key
The primary key and alternative keys. All candidate keys that are not the primary key are alternative keys.
Alternative Key
All keys that could be primary keys but are not selected to be the primary key.
Foreign Key
Keys that are primary keys in other tables.
Composite Primary Key
Combines two attributes to uniquely identify instances in a table.
Surrogate Key
Auto generated values.
Should you use surrogate keys on every table?
No. Overusing surrogate keys confuses the design and relationships of the table.
What does it mean for data to be atomic?
Data values cannot be broken down any further.
Determinant
An attribute which uniquely determines other attributes. Ex: studentID uniquely identifies studentName and studentGPA.
Meaning of “A → B”
B is functionally dependent on A.
What makes a well-structured relation?
- minimal data redundancies.
- designed to avoid insert, delete, and update anomalies.
Types of Functional Dependencies
Full Dependency, Partial Dependency, Transitive Dependency
Full Dependency
The determinant is the primary key (s) and all attributes have a functional dependency on the determinant (s).
Partial Dependency
The determinant is part of the primary key and one or more of the attributes have a functional dependency on the determinant. Can only exist when there is a composite primary key.
Transitive Dependency
The determinant is not part of the primary key and one or more of the attributes has a functional dependency on the determinant.
Normal Forms and their definitions
3NF: 2NF, no transitive dependency.
2NF: 1NF, no partial dependency.
1NF: Unique PK, unique rows, and atomic values.
Does 4NF exist?
Yes.
What Normal Form is the industry standard?
3NF.
Procedural Language
how to process steps; step-by-step; Java.
Declarative Language
what needs to be done; SQL.
Intracluster Skills
if you have an advanced understanding of Python for data analysis, you can migrate that skillset to another programming language like Ruby, Java, C++, C#, or R fairly easily because they are all in the same app/language cluster.
Data Definition Language (DDL)
define a database. Ex: Create, Drop/Truncate, Alter.
Data Manipulation Language (DML)
maintain/retrieve records.
- Insert, Delete, Update.
- Select (Data Query Language (DQL))
Are Null values allocated memory? Are derived attributes allocated memory space?
Null values: yes.
Derived attributes: no.
