Transcript Creating tables

Designing tables from a data model
(Chapter 6)
One base table for each entity.
Define primary key for each table.
Usually choose attributes than are
unambiguous and dataless
However, a key might contain data
(information) if it does not change.
For example, a room number
Some systems use a surrogate key (one
generated by the dbms).
For your projects I recommend simple
keys such as integers or short strings.
It will make your life easier when you have
to develop the application to access the data.
With a surrogate key, uniqueness may be
a problem when combining data from
different databases (prose on p. 194).
Each attribute has a type
Types may differ across different
systems.
Use primitive types (int, float, string,
date, etc.).
Can assign default values or NULL
values (no data available).
NULL value results in a false result with
ANY comparison with data.
Makes it a better choice than a default value
such as 0 for numeric fields.
Example: a supplier may have a numeric status
assigned to it.
Good (bad) suppliers have a status above
(below) a specified value.
You might want to list all good suppliers and all
bad suppliers.
Maybe a supplier is new and hasn’t been
assigned a status yet. A default value would
cause it to appear in one of these lists.
Another problem resulting from using a
specific (sentinel) value to take a specific
action.
Some years ago a database was being
created containing cities and nations
throughout the world.
For some reason, the software stopped
working part way through the process.
After much frustration and debugging it
was discovered the software quit each time
it read the Ecuadorian city of Quito.
Integrity Constraints
entity integrity
Primary key must never be null.
Define appropriate constraints
Gpa (must lie between 0 and 4)
letter grade (only certain letter combinations)
$$ value (usually non-negative), etc have
constraints.
Use them.
A database with such constraints will
disallow any attempt to insert or modify a
record unless constraints are met.
May be called domain or range
constraints.
Interrelation constraint
Value must be consistent with that in
other tables.
Student GPA, for example, must be
consistent with grades received
Often need programming logic to enforce
these (i.e. Triggers)
Implementing relationships.
Foreign key
an attribute in one table whose value must
match a primary key in another table, or be
wholly NULL.
Student table may have an advisor attribute.
It must match the ID of an existing advisor
or be NULL
A table consisting of orders must have
foreign keys matching a customer number
and a product code.
What if a primary key changes?
What about matching foreign keys?
Another reason to keep primary keys
dataless.
What if a record is deleted?
What about other entries that reference it?
Some choices:
disallow delete
provide cascading delete (delete all records
with a matching foreign key)
May affect numerous tables
change foreign keys to NULL (if allowed).
Can be specified when designing tables.
Examples
If a faculty member is deleted, might change
the student advisor field to NULL.
If an employee is deleted, will probably delete
all dependent entities also.
An employee may be given a list of company
supplies when hired. Do not delete employee if
he/she still possesses some of those things.
One-to-one relationship:
Use a foreign key in one entity to match
the primary key in the other
but where?
Ex: employee  companyCar
should employee have a CarId as a foreign
key or should companyCar have an
employeeId as a foreign key?
What do you think?
See also Fig. 6-8 on page 198
One-to-many relationship and IDDependent relationships.
Need a foreign key on the many side
(child side).
Ex. Department and Employee
Employee has a Dept ID that is a foreign key
Student and Major Advisor: Student has
an advisor ID that is a foreign key
See also Fig. 6-9 on page 199
Many-to-many relationship
Must convert into two 1-to-many
relationships with a separate intersection
table.
The intersection table has two foreign
keys each matching one of the primary
keys in one of the connecting tables – it
may contain nothing else
If there is other data, it’s called an
association table instead
Examples
A many-to-many relationship between
Suppliers and Parts requires a shipment
table having Supplier ID and Part ID as
foreign keys and perhaps nothing else.
A many-to-many relationship between
Students and Courses may require a
“courses taken” table having a Student ID
and Course ID as foreign keys and
perhaps a grade.
See also examples on pages 200-204.
Implementing multivalued attributes or
Archetype/Instance patterns
Can treat as ID dependent relationships
Ex: a salesperson may have many
regions.
There’s a one-to-many relationship between
the salesperson entity and a region entity.
Ex: Classes and sections
There’s a one-to-many relationship between
a class and its sections
Examples on page 205 and beyond
Implementing subtypes
Subtype entity contains a primary key
that also matches the primary key in the
supertype.
The key is both a primary key and a
foreign key.
It’s a type of ID dependent relationship.
Diagram on page 208
Create a patient table in a medical
database. Use patient ID as the primary
key.
Create a female patient table in the
database. Patient ID is both a primary key
and a foreign key to the above table.
Proceed similarly for a male patient table.
Recursive relationships
An entity could have a foreign key
matching a primary key of some instance
of the same entity.
Ex: Employees may be supervisors or
mentors for other employees
May have an intersection table with two
course numbers, where one represents a
prerequisite of the other.
Examples on page 209-210
Designing for cardinality
More difficult since it is not a structural
issue.
Usually have to code procedures or
programming logic to implement these.
CAN design for minimum cardinality
(mandatory or optional requirements)
in some cases.
If a parent entity is mandatory, can put a
NOT NULL constraint on the child’s
foreign key.
See Figure 6-28 on page 215 and 6-30 on
page 218.
Triggers are something we will discuss
later, but will skip for now.
They can be useful but can also cause all
kinds of unexpected problems if not done
correctly.
Book has lots of examples; go through
them.