240 likes | 333 Views
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.
E N D
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 ID-Dependent 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.