- to become an efective sql programmer,you will familiar whit the relation model.
- the model describes how to perform a common algebric operations on database tables in much the some ways that they're performed on matimatical sets.
- tables are collection of disting elements having common properties .
- model=table=file.
- attribute=column=field.
- tuple=row =record.
- a database is collection of one or more tables .
- tables is a two dimensional grid caracterized by rows and columns.
- table has a unique name within database
- each column represent a specific attribute
- each column has a domain that restricts the set of values allowed in that column.
- a domain is a set of constraints that includes restriction on a values data type length ,format ,range ...
- the order of columns is unimportant.
- each column has a name that identifies it within table (you can reuse the same column in other tables.
- no two rows in the table can be identical .
- each row in a table is identified uniquely by its primary key.
- a DBMS uses two type of tables :user tables and system tables
- user tables store user definied data.
- system tables contain metadata about the database such as structurel information ,physiqual details....
- every table has exactly one primary key (remember that the relation model sees unorded set of rows there's no concept of a next or preveriuos row you can't identify the rows by position with out key primary some of data would be inaccessible).
- a primary key is unique because it identify single row in a table.
- primary key is simple or composite (one column key is called a simply key,and a multiple columns key is called a composite key).
- names generally make poor keys because there are unstable.
- database designer create unique identifier when natural or obvious ones (such as a names) won't work.
- database designer forgo common unique identifier such as social securite numbers ,instead they use artificiel key that encode internal information that is meaningfull inside database user organization(for example employee ID might embed the years that the person was hired).
- we need a way to navigate between tables =>the relational model provide a mechanisme called a foriegn key to associate tables .
- the foriegn key has this caracteristics:
- it's a column reference value in some other table.
- it ensure that a row in one table have corresponding row in other table.
- a foreign key establishes a direct relationship to the parrent table primary kay.
- foreign key have the some domainas the parent key.
- foreign key values are not unique in their own table.
- allowing nullin a foreign key column complicates enforcement of referential integrity.
- a relationship can be:
- one-to-one
- one-to-many
- many-to many
- one-to-one:each row in table A can have at most one matching row in table B.
- one-to-many:each row in table A can have many matching rows in table B.
- many-to-many:each row in table A can have many matching rows in table B and each row in table b can matching rows in table A.
- redundancy is the enemy of databases user and administrators.
- normalization is the process a serie of stepof modyying tables to reduce redandancy.
- the relational model definies three normal forms.
- frist normal form:
- a table has columns that contain only atomic values(atomic value is a single value that can't ce subdevided).
- has no repeating groups
- to fix these problems store the data in two related tables.
- second normal form:
- it's primary key is a single column (the key isn't composite).
- all the columns in the tableare part of the primary key.
- has no partial function depending
- the DMBS provide operators and function that let you extract and manipulate the compenents of atomic values if necessary such as substring() function to extract the telephone number area code
- thrid normal form
- has no transitive dependencies
- a table contains a transitive dependency if a non key column value determines another non keycolumn values.
- for each non key ask"can i determine a non key column if i know any other non key column values".
- if no=>the column is no transitively dependent(good).
- else =>the column is transitively dependent (bad).
