Must make a single change in multiple places.
Introduces the possibility for inconsistency.
Wastes space.
Inefficient.
Missing data? Default data?
Their meaning is ambiguous.
List an automobile manufacturer might create to tracking vehicles it would like to produce:
|
Model |
Color |
ManufacturingPlant |
City |
Qty |
|
Camry |
red |
us1 |
detroit |
150000 |
|
Camry |
red |
us2 |
Detroit |
100000 |
A relation is just a table with a few constraints that we’ll explore later.
A table holds data for a particular kind of thing (called a theme in chapter 1 of the text), e.g., Student.
So for our purposes at this moment, a relation, table, theme, entity are all the same thing.
Relational model developed by E.F. Codd and rooted in Logic. Can think of a table as a set of propositions that are true, e.g., from the table above: there exists a type of car whose model is ‘Camry’ whose color is ‘red’ that is manufactured in the plant ‘us1’, which is located in the city ‘detroit’, and 150000 of these cars were manufactured there.
A table is a 2-dimensional array of values.
A row holds a set of values that describe a particular entity, e.g., a particular student.
A column holds a set of values for a particular attribute that all entities possess, e.g., all students have a name.
In java, a class defines the instance variables (attributes) for all objects instantiated (built) from this class?
A row in a table is analogous to a particular object built from a class in java.
Suppose we have a student table having one attribute – name.
We want to be able to support two students who have the same name.
In java, we create two separate objects, which produces two different references.
Create a ‘dummy’ attribute that causes all entities in the same table to be unique – studentID.
Data is manipulated in relational databases using Structured Query Language (SQL). There are only 4 actions possible:
Add a row to a table
Change a row that was already in a table
Remove a row from a table
Retrieve a row(s) from a table(s). We will concentrate on select for a while.
(MYSQL provides a graphical interface to insert, update, delete, and select from tables, but it can also be done with SQL statements.)
Select <fields> from <table_specifications> where <search conditions>
Table specifications will get a more complex, but for now, it is just the name of a table, e.g., student.
Simple <search conditions> compare a name with a value.
See the text pages mentioned in the course schedule for legal where-clause conditions.
See connectToWebpagesUsingMySQLWorkbench.htm
Initialize your webpages database with the tables needed for lab 1.
In MySQL Workbench, open another sql script window and type SQL to find:
1. All data for all animals
2. AnimalIDs of animals whose Name starts with ‘H’
Note on case sensitivity: the behavior of queries w.r.t. case sensitivity depends on how the table was defined. The pet store database we will see in lab was defined in such a way that searches are case insensitive. Case sensitive searches can be forced by preceding the column name with keyword ‘binary’, e.g,
select *
from Animal
where binary name = ‘hunter’
selects all animals whose name is lowercase ‘hunter’ (there are none).
select *
from Animal
where name = ‘hunter’
selects all animals whose name is case-insensitive ‘hunter’ (there are three).