Access's Query Design mode provides a graphical environment to create and edit queries.
Table schemes are added in the upper portion of the Query Design window.
The design grid is in the lower part of the window.
Access takes care of forming the appropriate join based on the relationship information shown in the upper portion of the window.
Access SQL is more powerful than the Access Query Design interface.
Wednesday, November 29, 2006
Query Languages and the Relational Algebra
Access SQL is a special form of standard SQL.
Query Languages
query - a request of the database, the response to which is a result table.
Queries permit me to extract the data from the database in meaningful forms.
Query languages can be based on algebraic expression or logical expressions.
base table - a table whose data are actually stored in the database.
view - a query expression that has been given a name, and is stored in the database. The expression is the view and not the result table.
virtual table - a view because it is an expression that results in a table.
Relational Algebra and Relational Calculus
relational algebra - the most common algebraic query language.
procedural - the expression describe an explicit procedure for returning the results. Relational algebra is a procedural language.
relational calculus - uses logic.
nonprocedural - expressions represent statements that describe conditions that must be met for a row to be in the result. Example is relational calculus.
Plan English:
Get the names and phone numbers for publishers who publish books costing under $20.00.
Relational Algebra:
projPubName,PubPhone(selPrice<20.00(BOOKS join PUBLISHERS))
Relational Calculus:
{(x,y) | PUBLISHERS(z,x,y) and BOOKS(a,b,z,c) and c < $20.00}
Query Languages
query - a request of the database, the response to which is a result table.
Queries permit me to extract the data from the database in meaningful forms.
Query languages can be based on algebraic expression or logical expressions.
base table - a table whose data are actually stored in the database.
view - a query expression that has been given a name, and is stored in the database. The expression is the view and not the result table.
virtual table - a view because it is an expression that results in a table.
Relational Algebra and Relational Calculus
relational algebra - the most common algebraic query language.
procedural - the expression describe an explicit procedure for returning the results. Relational algebra is a procedural language.
relational calculus - uses logic.
nonprocedural - expressions represent statements that describe conditions that must be met for a row to be in the result. Example is relational calculus.
Plan English:
Get the names and phone numbers for publishers who publish books costing under $20.00.
Relational Algebra:
projPubName,PubPhone(selPrice<20.00(BOOKS join PUBLISHERS))
Relational Calculus:
{(x,y) | PUBLISHERS(z,x,y) and BOOKS(a,b,z,c) and c < $20.00}
Relational Database Design Principles
Keys and Superkeys
superkey - a set of attributes that uniquely identifies any entity from among all possible entities in the entity class.
key - A superkey that has no proper subset of it is also a superkey.
Redundancy
Attributes can be used for identification and/or informational purposes.
Normal Forms
A normal form is a special set of forms, properties, or constraints a table scheme may possess, in order to achieve certian desired goals.
normalization - the process of changing a database design to produce table schemes in normal from.
First Normal Form
First Normal Form (1NF) occurs when all attributes in a table scheme are indivisible.
scalar or atomic attribute - an attribute that only allows indivisible values.
structured attribute - an attribute that allows a list of divisible items.
Good database design almost always requires that an attribute be atomic so that the table scheme is in first normal form.
Functional Dependencies
The attribute of left completely determines the attribute of the right.
{PubName, PubPhone} -> {PubID}
superkey - a set of attributes upon which all other attributes of the table scheme are functionally dependent.
trivial dependencies - If we know the values of A, B, C, then we know the value of A and B.
nontrivial - If is is not a trivial dependency.
Second Normal Form
Second Normal Form (2NF) occurs when all strictly informational attributes are attributes of the entities in the table scheme, and not of some other class of entities.
example: {City, Street, HouseNumber, HouseColor, CityPopulation}
CityPopulation is strictly informational and gives information about that city and not the house address.
{City} -> {CityPopulation}
Third Normal Form
Third Normal Form (3NF) occurs only if strictly informational attributes depend only upon superkeys.
Boyce-Codd Normal Form
Boyce-Codd Normal Form (BCNF) occurs if no attribute depends upon anything other than a superkey.
Table Scheme {City, StreetName, ZipCode}
{City, StreetName} -> {ZipCode}
and:
{Zipcode} -> {City}
Normalization
{ISBN, Title, Authors} becomes {ISBN, Title, AuID} and {AuID, AuName} which is 1NF.
{City, StreetName, ZipCode} becomes {ZipCode, City} and {ZipCode, StreetName} which is BCNF.
A design of a database may begin with E/R diagram. If some of the table schemes have redundanceis, it may be desirable to split them into smaller table schemes that satisfy a higher normal form.
Decomposition
lossless decomposition - decomposing a table scheme into small schemes does not cause any information to be lost.
dependency-preserving decomposition- decomposing that doesn't cause any dependencies to be lost.
superkey - a set of attributes that uniquely identifies any entity from among all possible entities in the entity class.
key - A superkey that has no proper subset of it is also a superkey.
Redundancy
Attributes can be used for identification and/or informational purposes.
Normal Forms
A normal form is a special set of forms, properties, or constraints a table scheme may possess, in order to achieve certian desired goals.
normalization - the process of changing a database design to produce table schemes in normal from.
First Normal Form
First Normal Form (1NF) occurs when all attributes in a table scheme are indivisible.
scalar or atomic attribute - an attribute that only allows indivisible values.
structured attribute - an attribute that allows a list of divisible items.
Good database design almost always requires that an attribute be atomic so that the table scheme is in first normal form.
Functional Dependencies
The attribute of left completely determines the attribute of the right.
{PubName, PubPhone} -> {PubID}
superkey - a set of attributes upon which all other attributes of the table scheme are functionally dependent.
trivial dependencies - If we know the values of A, B, C, then we know the value of A and B.
nontrivial - If is is not a trivial dependency.
Second Normal Form
Second Normal Form (2NF) occurs when all strictly informational attributes are attributes of the entities in the table scheme, and not of some other class of entities.
example: {City, Street, HouseNumber, HouseColor, CityPopulation}
CityPopulation is strictly informational and gives information about that city and not the house address.
{City} -> {CityPopulation}
Third Normal Form
Third Normal Form (3NF) occurs only if strictly informational attributes depend only upon superkeys.
Boyce-Codd Normal Form
Boyce-Codd Normal Form (BCNF) occurs if no attribute depends upon anything other than a superkey.
Table Scheme {City, StreetName, ZipCode}
{City, StreetName} -> {ZipCode}
and:
{Zipcode} -> {City}
Normalization
{ISBN, Title, Authors} becomes {ISBN, Title, AuID} and {AuID, AuName} which is 1NF.
{City, StreetName, ZipCode} becomes {ZipCode, City} and {ZipCode, StreetName} which is BCNF.
A design of a database may begin with E/R diagram. If some of the table schemes have redundanceis, it may be desirable to split them into smaller table schemes that satisfy a higher normal form.
Decomposition
lossless decomposition - decomposing a table scheme into small schemes does not cause any information to be lost.
dependency-preserving decomposition- decomposing that doesn't cause any dependencies to be lost.
Books I'm Using
I am using the following books to study from:
"Access Database Design & Programming," by Steven Roman, published by O'Reilly Press.
I like this because it is a typical O'Reilly book and keep it coming fast.
"Mastering Microsoft Access 2000 Development" by Alison Balter, published by SAMS.
The lady that wrote this book seems to know a lot about database and the development and business process involved.
"SQL Server 2000" by Robin Dewson, published by WROX.
"Mastering Access '97" by ?, published by ?.
I have used this book enough to have an opinion.
"Access Database Design & Programming," by Steven Roman, published by O'Reilly Press.
I like this because it is a typical O'Reilly book and keep it coming fast.
"Mastering Microsoft Access 2000 Development" by Alison Balter, published by SAMS.
The lady that wrote this book seems to know a lot about database and the development and business process involved.
"SQL Server 2000" by Robin Dewson, published by WROX.
"Mastering Access '97" by ?, published by ?.
I have used this book enough to have an opinion.
Learning About Database
Although I have some knowledge of databases, I have come to realize that I really know very little. Therefore, I am starting this blog to record my experiences as I learn.
Subscribe to:
Posts (Atom)
