From fdb6e167c10ea83e4d46e2faa4f12424c4ba9b0b Mon Sep 17 00:00:00 2001 From: mo khan Date: Fri, 10 Jan 2020 21:13:31 -0700 Subject: Add notes --- doc/01-introduction.md | 117 +++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 117 insertions(+) diff --git a/doc/01-introduction.md b/doc/01-introduction.md index e6c6c68..ce9db0d 100644 --- a/doc/01-introduction.md +++ b/doc/01-introduction.md @@ -33,3 +33,120 @@ line_items * order_id * product_id * quantity + +* Data structure: data are organized in the form of tables, with rows and columns. +* Data manipulation: SQL language used to manipulate data +* Data integrity: mechanism used to specify business rules that maintain data integrity. + +# Relation: two dimensional table of data. + +Employee1(id, name) # shorthand notation for describing a relation. + +## Relational Keys + +* primary key: attribute or combination of attributes that uniquely identifies each row in a relation. +* composite key: primary key that consists of more than one attribute. +* foreign key: attribute in a relation that serves as the primary key of another relation in the same db. + +Not all tables are relations. + +1. each relation has a unique name in the db. +2. each cell (intersection of row and column) is atomic (single valued). multi-valued attribute not allowed. +3. each row is unique. no two rows in a relation can be identical. +4. each attribute (column) within the table has a unique name. +5. sequence of columns is insignificant (left to right). order of columns can change without affecting meaning. +6. sequence of rows is insignificant. (top to bottom) + +Suggestions: + +It's good to create an instance of your relational schema with sample data because... + +1. the sample data allow you to test your assumptions regarding the design. +2. the sample data provide a convenient way to check the accuracy of your design. +3. the sample data help improve communications with users in discussing your design. +4. you can use the sample data to develop prototype applications and to test queries. + +## Integrity Constraints + +Rules limiting acceptable values and actions. Purpose is to facilitate maintaining the accuracy +and integrity of data in the db. + +The major types of integrity constraints are domain constraints, entity integrity and referential integrity. + +### Domain Constraints + +All the values that appear in a column of a relation must be from the same domain. huh? + +> Domain: the set of values that may be assigned to an attribute. + +Domain definition consists of the following components: + +* domain name +* meaning +* data type +* size (length) +* allowable values or allowable range + +#### Entity Integrity + +Ensures that every relation has a primary key and that the data values for the primary key are all valid. + +* Null: a value that may be assigned to an attribute when no other value applies or when the applicable value is unknown. + +* Entity integrity rule: No primary key attribute (or component of primary key attribute) may be null. + +#### Referential Integrity + +Associations between tables are defined through foreign keys. + +* Referential integrity constraint: each foreign key value must match a primary key value in another relation or the foreign key value must be null. + + +## Well structured relations + +What makes up a well structured relation? + +* minimal redundancy +* crud operations can occur without errors or inconsistencies. + +Anomaly: an error or inconsistency that may result when a user attempts to update a table that +contains redundant data. The three types of anomalies are insertion, deletion and modification anomalies. + +* insertion anomaly + * insert into employees also requires course data. + * Should be able to insert employee data without supplying course data. +* deletion anomaly: +* modification anomaly: a change to a single employee requires updating multiple records in the same relation. + +1. Regular entity: has independent existence. +2. Weak entity: cannot exist without relationship to owner. +3. Associative Entity: many-to-many relationships. join tables. + +> Surrogate primary key: serial # or other system assigned primary key for a relation. + +# Normalization + +Is a formal process for deciding which attributes should be grouped together in a relation +so that all anomalies are removed. + +> Normalization: the process of decomposing relations with anomalies to produce smaller, well-structured relations. + +1. minimize redundancy. +2. simplify enforcement of referential integrity constraints. +3. Make CUD operations easy. +4. Future growth. + +## Normal form + +What is normal form? + +A normal form is a state of a relation that requires that certain rules regarding relationships between attributes are satisfied. + +> Normal form: A state of a relation that requires that certain rules regarding relationships between attributes are satisfied. + +* 1st normal form: multivalued attributes have been removed. each cell has a single value. +* 2nd normal form: partial functional dependencies have been removed. (?) +* 3rd normal form: transitive dependencies have been removed. (?) +* Boyce-Codd normal form: remaining anomalies from functional dependencies have been removed. +* 4th normal form: multivalued dependencies have been removed. +* 5th normal form: remaining anomalies have been removed. -- cgit v1.2.3