1 / 45

Database Design

theta
Download Presentation

Database Design

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    1. Database Design Sections 12 Database relationship, Integrity, keys, mapping conceptual model to logical/physical model

    2. Relational database concepts Discuss Primary keys Foreign keys Data integrity Physical mapping & transition to SQL Entity = table Attribute = column Marge Hohly 2 Lesson Preparation This lesson talks about relational databases, primary keys, foreign keys, and data integrity. This will help with the conceptual to physical mapping later in this section, and with the transition to SQL in the upcoming sections. Encourage the students to realize that they are learning a new set of terminology. An entity is not a table, and an attribute is not a column. We will be transforming (exchanging) one set of objects (ER conceptual modeling constructs) into another (data design physical model constructs). What to Watch For If anyone asks, the word “relation” is an abstract mathematical term used in set theory. A mathematical relation has the same logical properties as a table in a database. This is the origin of the term “relational database”, ie a database consisting of a set of relations (ie tables). Connections Remind students that they have seen integrity rules before. These are the business rules that sometimes cannot be represented in the conceptual model and therefore must be documented. This is the reason for the documentation -- to make sure that the programmers write extra code to enforce these rules in the database! Lesson Preparation This lesson talks about relational databases, primary keys, foreign keys, and data integrity. This will help with the conceptual to physical mapping later in this section, and with the transition to SQL in the upcoming sections. Encourage the students to realize that they are learning a new set of terminology. An entity is not a table, and an attribute is not a column. We will be transforming (exchanging) one set of objects (ER conceptual modeling constructs) into another (data design physical model constructs). What to Watch For If anyone asks, the word “relation” is an abstract mathematical term used in set theory. A mathematical relation has the same logical properties as a table in a database. This is the origin of the term “relational database”, ie a database consisting of a set of relations (ie tables). Connections Remind students that they have seen integrity rules before. These are the business rules that sometimes cannot be represented in the conceptual model and therefore must be documented. This is the reason for the documentation -- to make sure that the programmers write extra code to enforce these rules in the database!

    3. Relational database table Marge Hohly 3 Slide 4: Tell Me /Show Me – A relational database is a database… Relational database- Collections of objects or relations, set of operators to act on those relations, and data integrity for accuracy and consistency . Explain that each row of data describes an employee. Each column is an attribute of that employee. If we wanted to find out the last name and department number of employee number 210, we would need to access the third row in the table, and then find the values for fname and dept_no for that row. But how do we find the correct row in the first place? Would we have to go through the whole table and look at every row? Answer: No. (Move on to next page.) Slide 4: Tell Me /Show Me – A relational database is a database… Relational database- Collections of objects or relations, set of operators to act on those relations, and data integrity for accuracy and consistency . Explain that each row of data describes an employee. Each column is an attribute of that employee. If we wanted to find out the last name and department number of employee number 210, we would need to access the third row in the table, and then find the values for fname and dept_no for that row. But how do we find the correct row in the first place? Would we have to go through the whole table and look at every row? Answer: No. (Move on to next page.)

    4. Marge Hohly 4 SQL to retrieve information Structured query language (SQL) used to access information English-like phrases Example: SELECT lname, dept_no FROM employees WHERE emp_no = 210; Point out format and structure: Reserved words all caps Fields (attributes) lower case End with ;Point out format and structure: Reserved words all caps Fields (attributes) lower case End with ;

    5. Results of SQL statement Marge Hohly 5 Note result of SQL selection statement.Note result of SQL selection statement.

    6. Marge Hohly 6 Primary Key Primary Key (PK) Column or set of columns that uniquely identifies each row in a table Employee ID in Employee table (single unique) Bank ID & Account ID in Accounts table (composite) Every table has a Primary key not null no part of PK can be null (entity integrity) unique can be composite Candidate key (column that can be considered for a Primary key) Example of keys: Employee table: PK is Emp_no Accounts Table: PK is Bank_no & Acct_no (composite key) Enrollment: student_no and ticket_no Slide 9: Tell Me /Show Me – Primary Key Primary key- A constraint which ensures that the column contains no null values and uniquely identifies each row of the table. Point out that in ACCOUNTS, bank_no is not unique and acct_no is not unique. However, the combination of bank_no and acct_no is unique. Slide 10: Tell Me / Show Me – Primary Key (Table) Ask students to point out the rows in each table that violate the rule that no part of the primary key can be null. Answer: Second row in EMPLOYEES, second row in ACCOUNTS. These rows violate entity integrity. Slide 11: Tell Me / Show Me – A table can have more than one column Candidate key- More than one column or combination of columns that could serve as the table’s primary key. Ask students what makes emp_no and payroll_id good candidates for the primary key? Answer: They are both unique and not null. Slide 12: Tell Me /Show Me – Select one candidate key to be … Unique key- An integrity constraint that requires every value in a column or set of columns be unique. Ask students why having alternate or unique keys can be useful? Answer: It’s another way to locate a record. If you forget your employee ID, but know your payroll ID (or have a payroll stub with the ID on it), then you can still access your employee record. Example of keys: Employee table: PK is Emp_no Accounts Table: PK is Bank_no & Acct_no (composite key) Enrollment: student_no and ticket_no Slide 9: Tell Me /Show Me – Primary Key Primary key- A constraint which ensures that the column contains no null values and uniquely identifies each row of the table. Point out that in ACCOUNTS, bank_no is not unique and acct_no is not unique. However, the combination of bank_no and acct_no is unique. Slide 10: Tell Me / Show Me – Primary Key (Table) Ask students to point out the rows in each table that violate the rule that no part of the primary key can be null. Answer: Second row in EMPLOYEES, second row in ACCOUNTS. These rows violate entity integrity. Slide 11: Tell Me / Show Me – A table can have more than one column Candidate key- More than one column or combination of columns that could serve as the table’s primary key. Ask students what makes emp_no and payroll_id good candidates for the primary key? Answer: They are both unique and not null. Slide 12: Tell Me /Show Me – Select one candidate key to be … Unique key- An integrity constraint that requires every value in a column or set of columns be unique. Ask students why having alternate or unique keys can be useful? Answer: It’s another way to locate a record. If you forget your employee ID, but know your payroll ID (or have a payroll stub with the ID on it), then you can still access your employee record.

    7. Foreign Key Foreign Key (FK) depends on business rule comes from relationship primary key from another table If FK is part of a PK, then the FK can’t be NULL Marge Hohly 7 Slide 13: Tell Me / Show Me – Foreign Key Foreign key- A column or set of columns that refers to a primary key in the same table or another table. Point out that if the foreign key (dept_no) in EMPLOYEES has a value of 10, then there needs to be a row in DEPARTMENTS with a dept_no of 10. Otherwise, it is a violation of referential integrity. You can also explain this in business terms, by saying that an employee can’t belong to a department that doesn’t exist. Slide 14: Tell Me / Show Me If a foreign key is a part of a primary … Point out that in the example, shelf_no is part of the primary key of BOOKS. It is also a foreign key to SHELVES. Since it is part of the PK of BOOKS, it cannot be null. Slide 13: Tell Me / Show Me – Foreign Key Foreign key- A column or set of columns that refers to a primary key in the same table or another table. Point out that if the foreign key (dept_no) in EMPLOYEES has a value of 10, then there needs to be a row in DEPARTMENTS with a dept_no of 10. Otherwise, it is a violation of referential integrity. You can also explain this in business terms, by saying that an employee can’t belong to a department that doesn’t exist. Slide 14: Tell Me / Show Me If a foreign key is a part of a primary … Point out that in the example, shelf_no is part of the primary key of BOOKS. It is also a foreign key to SHELVES. Since it is part of the PK of BOOKS, it cannot be null.

    8. Marge Hohly 8 Key questions 11.4.8 what makes emp_no and payroll_id good candidates for the primary key? 11.4.9 why is having alternate or unique keys useful? Answer 1: They are both unique and not null. Answer 2: It’s another way to locate a record. If you forget your employee ID, but know your payroll ID (or have a payroll stub with the ID on it), then you can still access your employee record. Answer 1: They are both unique and not null. Answer 2: It’s another way to locate a record. If you forget your employee ID, but know your payroll ID (or have a payroll stub with the ID on it), then you can still access your employee record.

    9. Column integrity Contain values consistent with data format of column Marge Hohly 9

    10. Summary Data-Integrity Rules Marge Hohly 10

    11. Data-Integrity Summary Marge Hohly 11 Slide 17: Tell Me / Show Me – (Table) The first three shown here are a summary of the data-integrity rules we’ve covered. The last one (an example of user-defined integrity) has not been covered yet, but students should understand its importance by now. Slide 17: Tell Me / Show Me – (Table) The first three shown here are a summary of the data-integrity rules we’ve covered. The last one (an example of user-defined integrity) has not been covered yet, but students should understand its importance by now.

    12. Marge Hohly 12 Data-Integrity Summary Entity integrity- no part of PK can be NULL Referential integrity – FK must match an existing PK value (or else be NULL) Column integrity – column must contain only values consistent with defined data format User-defined integrity – data stored in database must comply with the rules of the business

    13. Marge Hohly 13 Referential Integrity Use Foreign Key to map relationships A foreign key (FK) is a column or combination of columns in one table that refers to a primary key in the same table or another table. 11.4.10 (next slide)

    14. Marge Hohly 14 11.4.10 Point out that if the foreign key (dept_no) in EMPLOYEES has a value of 10, then there needs to be a row in DEPARTMENTS with a dept_no of 10. Otherwise, it is a violation of referential integrity. You can also explain this in business terms, by saying that an employee can’t belong to a department that doesn’t exist. Point out that if the foreign key (dept_no) in EMPLOYEES has a value of 10, then there needs to be a row in DEPARTMENTS with a dept_no of 10. Otherwise, it is a violation of referential integrity. You can also explain this in business terms, by saying that an employee can’t belong to a department that doesn’t exist.

    15. Marge Hohly 15 Composite key Made up of two or more values Together unique ENROLL Table/Entity student_no & ticket_no ACCOUNTS bank_no & acct_no

    16. Marge Hohly 16 JOBS Table Nullable means that Null is allowed Primary Key can not be null “Not null” (blank under Nullable) in the Optionality column means that the column is mandatory.Nullable means that Null is allowed Primary Key can not be null “Not null” (blank under Nullable) in the Optionality column means that the column is mandatory.

    17. Marge Hohly 17 Transformation Conceptual model, focus on the business and its rules. Data modeling pays attention to the business requirements, regardless of implementation. Conceptual model Logical model

    18. Marge Hohly 18 Review 12.2.3

    19. Marge Hohly 19 Conceptual becomes Physical model Conceptional becomes Physical model

    20. Marge Hohly 20 Terminology Mapping - An entity leads to a table. - An attribute becomes a column. - A primary unique identifier produces a primary key. - A secondary unique identifier produces a unique Key. - A relationship is transformed into a foreign key and foreign-key columns. - Constraints are the rules that the database must follow to be consistent. Some of the business rules are translated into check constraints; other more complex ones require additional programming in the database or the application.

    21. Marge Hohly 21 12.2.8 For entity names of more than one word, take the: - First character of the first word - First character of the second word - Last character of the last word Example: JOB ASSIGNMENT gets a short name of JAT For entity names of one word but more than one syllable, take the: - First characer of the first syllable - First character of the second syllable - Last character of the last syllable Example: EMPLOYEE gets a short name of EPE For entity names of one syllable but more than one character: - First character - Second character - Last character Example: FLIGHT gets a short name of FLT

    22. Marge Hohly 22 Naming restrictions with Oracle Table and column names: must start with a letter can contain up to 30 alphanumeric characters cannot contain space or special characters such as “!,” but “$,” “#,” and “-“ are permitted Table names must be unique. Column names must be unique within a table. Avoid “reserved” words in tables and columns.

    23. Marge Hohly 23 Cascade barred relationships UID from parent entity becomes part of the UID of the child entity

    24. Marge Hohly 24 Relationship mapping Relationships are mapped to foreign keys Foreign keys enable users to access related information from other tables. Mapping relationships to relational database structures is part of creating the “first-cut” database design.

    25. Marge Hohly 25 Relationship mapping 1:M mapping Foreign key goes in table at crow’s foot from parent FK1 Dept_id mandatory is required FK2 might be better mgn_id and is optional Does the president of the company have a manager? Answer: must be optional Since epe_id implements the recursive relationship in EMPLOYEE, it is probably better renamed to mgr_id. Decisions like this can be made at this stage of mapping. Answer: must be optional Since epe_id implements the recursive relationship in EMPLOYEE, it is probably better renamed to mgr_id. Decisions like this can be made at this stage of mapping.

    26. Marge Hohly 26 Relationship mapping FK is mandatory from this diagram FK is optional from this diagram

    27. Marge Hohly 27 12.3.4 Optional or Mandatory determined by crow’s foot end of relationship The program will have to check for two things: - You cannot create a master record without at least one detail record. (In the example, BAND is the master and MUSICIAN is the detail.) - When deleting details, you must be sure that you do not delete the last detail for a master record, or alternatively, you must delete the master record together with the last detail. The program will have to check for two things: - You cannot create a master record without at least one detail record. (In the example, BAND is the master and MUSICIAN is the detail.) - When deleting details, you must be sure that you do not delete the last detail for a master record, or alternatively, you must delete the master record together with the last detail.

    28. Marge Hohly 28 NonTransferable Relationship Transferablility is a procedural model Must be implemented by a program Need to document this constraint/business rule In the example, a paycheck may not be transferred to another employee. This means that the epe_id, which is the foreign-key column in the table PAYCHECKS, cannot be updated. In the example, a paycheck may not be transferred to another employee. This means that the epe_id, which is the foreign-key column in the table PAYCHECKS, cannot be updated.

    29. Marge Hohly 29 Barred Relationship 12.3.6 Barred relationship is mapped to a foreign-key column on the many side, just like any other M:1 relationship. Bar means it becomes part of the composite primary key of the child ACCOUNT table has both acct_id and bank_id as the composite primary key Help students visualize the data in ACCOUNTS. The act_number alone would not be unique within the table, but the combination of act_nbr and bak_nbr would be.Help students visualize the data in ACCOUNTS. The act_number alone would not be unique within the table, but the combination of act_nbr and bak_nbr would be.

    30. Marge Hohly 30 Cascading barred relationships Pick up one more component to the composite key with each level Company – company_id Division company_id & div_id Department company_id, div_id & dept_no Team team_id, company_id, div_id & dept_no

    31. Marge Hohly 31 M:M relationship mapping M:M resolved with intersection entity Intersection entity has a composite key with the PK from each parent as FK in child Note the subscript notation on each foreign key (fk1 and fk2). This is meant to show that each foreign-key column references the primary key of a different table. This differs from previous examples where the foreign-key column (or columns) references the primary key of a single table.Note the subscript notation on each foreign key (fk1 and fk2). This is meant to show that each foreign-key column references the primary key of a different table. This differs from previous examples where the foreign-key column (or columns) references the primary key of a single table.

    32. Marge Hohly 32 1:1 relationship mapping Create a foreign key and a unique key If relationship mandatory on one side, Foreign key created on the mandatory side as a unique key If optional on both sides, you can choose which table gets the foreign key. Clarify the necessity for a unique key on bcp_code if necessary. Remind them of the relationship in the conceptual model : “Each bottle cap may be the sealer for one and only one soda bottle.” If you had a bottle cap with code AB55, that would be unique in the SODA_BOTTLES table, because it can occur only in one instance or one row of the table. Mandatory 1:1 relationships are rare. In most cases, this will be modeled as a single table, without a need for a 1:1 relationship. However, if you need to clarify the limitation of 1:1 mandatory at both ends, you can revisit the earlier slide on m:1 mandatory on the “one” end in this lesson.Clarify the necessity for a unique key on bcp_code if necessary. Remind them of the relationship in the conceptual model : “Each bottle cap may be the sealer for one and only one soda bottle.” If you had a bottle cap with code AB55, that would be unique in the SODA_BOTTLES table, because it can occur only in one instance or one row of the table. Mandatory 1:1 relationships are rare. In most cases, this will be modeled as a single table, without a need for a 1:1 relationship. However, if you need to clarify the limitation of 1:1 mandatory at both ends, you can revisit the earlier slide on m:1 mandatory on the “one” end in this lesson.

    33. Marge Hohly 33 Review FK 1:M PK, FK in same key, rename one M:M first resolve with an intersection entity

    34. Marge Hohly 34 Review cont. Will be part of PK a composite key FK on mandatory side FK on either side

    35. Marge Hohly 35 Arc mapping Foreign key from the parent (single) side are placed in the child (many) side The Foreign key is ALWAYS Optional in the child Only of the Arc can be valid and all others must be NULL Mandatory relationship is enforced with a check constraint

    36. Marge Hohly 36 Arc constraint You need a constraint to make sure only one is NOT NULL at a time Example: FK1, FK2, FK3, .... ALTER EVENT constraint (FK1 is not null and FK2 is null and FK3 is null ....) OR (FK1 is null and FK2 is not null and FK3 is null ....) OR (FK1 is null and FK2 is null and FK3 is not null ....)

    37. Marge Hohly 37 ARC mapping If mandatory then one MUST be NOT NULL If optional then all may be NOT NULL You will always need a check constraint defined You may need to explain the concept of a check constraint, as opposed to additional programming that is needed to enforce mandatory relationships on the “one” end (as in the previously discussed M:1 or 1:1). A check constraint is programming code that can be stored in the database. It can enforce simple rules that apply to a single row in the table (such as comparing values or ensuring that they are null or not null). This is the case with the arc. In the case of a mandatory one end of a M:1 or 1:1 relationship, we have to check that if a row is entered in one table (the master), a row must also be entered in another table (the child, or detail). A check constraint cannot span two tables or different rows in the same table. It cannot prevent insert, update, or delete operations. This is why additional programming (instead of a check constraint) is necessary. You may need to explain the concept of a check constraint, as opposed to additional programming that is needed to enforce mandatory relationships on the “one” end (as in the previously discussed M:1 or 1:1). A check constraint is programming code that can be stored in the database. It can enforce simple rules that apply to a single row in the table (such as comparing values or ensuring that they are null or not null). This is the case with the arc. In the case of a mandatory one end of a M:1 or 1:1 relationship, we have to check that if a row is entered in one table (the master), a row must also be entered in another table (the child, or detail). A check constraint cannot span two tables or different rows in the same table. It cannot prevent insert, update, or delete operations. This is why additional programming (instead of a check constraint) is necessary.

    38. Marge Hohly 38 Subtype Review Students may get bogged down in the arc implementation of a supertype. The best way to understand it is to redraw the data model using an arc instead of a supertype. Then transform the model as an arc. The tricky part is that the relationships in the arc are 1:1 instead of M:1, which is usually seen in an arc. 1:1 relationships are not modeled that often; usually the data modeler will use one entity instead of two. For this reason, modeling supertypes as arcs is not that common either. Arcs are best used to express exclusive relationships, not a “type of” condition. Let students know that most of the time, the single-table implementation is used. Students may get bogged down in the arc implementation of a supertype. The best way to understand it is to redraw the data model using an arc instead of a supertype. Then transform the model as an arc. The tricky part is that the relationships in the arc are 1:1 instead of M:1, which is usually seen in an arc. 1:1 relationships are not modeled that often; usually the data modeler will use one entity instead of two. For this reason, modeling supertypes as arcs is not that common either. Arcs are best used to express exclusive relationships, not a “type of” condition. Let students know that most of the time, the single-table implementation is used.

    39. Marge Hohly 39 Subtype mapping Mapping supertypes and subtypes makes sure that the right information gets stored with each type.

    40. Marge Hohly 40 Subtype modeling Mapping as a single table Rules Tables: Only one table is created, independent of the number of subtypes. Columns: The single table gets a column for all the attributes of the supertype, with the original optionality. Table gets a column for each attribute of the subtype, but column are. Mandatory column to distinguish between each different subtypes of entity. Point out that salary and hourly_rate are both optional, even if the ERD has them as mandatory. Dpt_id can remain mandatory, because it comes from the relationship to the supertype EMPLOYEE. Agy_id comes from a relationship to the subtype and has to become optional. Epe_type is the discriminator column. It can have values of FTE or PTE. Notice that mgr_id represents the recursive relationship for EMPLOYEE. It is a foreign key column and would normally be named epe_id, after the parent table. However, it makes sense to rename it at this stage, to make it easier to understand.Point out that salary and hourly_rate are both optional, even if the ERD has them as mandatory. Dpt_id can remain mandatory, because it comes from the relationship to the supertype EMPLOYEE. Agy_id comes from a relationship to the subtype and has to become optional. Epe_type is the discriminator column. It can have values of FTE or PTE. Notice that mgr_id represents the recursive relationship for EMPLOYEE. It is a foreign key column and would normally be named epe_id, after the parent table. However, it makes sense to rename it at this stage, to make it easier to understand.

    41. Marge Hohly 41 Subtype modeling – Single table cont. Rules Identifiers: Unique identifiers transform into primary and unique keys. Relationships: Relationships at the supertype level transform as usual. Relationships at subtype level are implemented as optional foreign-key columns. Integrity constraints: A check constraint is needed to ensure that for each particular subtype, all columns that come from mandatory attributes are not null. A check constraint is necessary to ensure that (epe_type = ‘FTE’ and salary is not null and hourly_rate is null and agy_id is null) OR (epe_type ‘PTE’ and salary is null and hourly_rate is not null and agy_id is not null). Epe_id is the employee identifier of each employee’s manager. A note about the OTHER subtype: An OTHER subtype is recommended in the conceptual model to ensure that the subtypes are exhaustive. However, by the time we start the design phase, we should have done extensive analysis to determine if another subtype is truly needed. If so, then this subtype must be named, and its attributes specified. If not, then the OTHER subtype is not part of the transformation process.A check constraint is necessary to ensure that (epe_type = ‘FTE’ and salary is not null and hourly_rate is null and agy_id is null) OR (epe_type ‘PTE’ and salary is null and hourly_rate is not null and agy_id is not null). Epe_id is the employee identifier of each employee’s manager. A note about the OTHER subtype: An OTHER subtype is recommended in the conceptual model to ensure that the subtypes are exhaustive. However, by the time we start the design phase, we should have done extensive analysis to determine if another subtype is truly needed. If so, then this subtype must be named, and its attributes specified. If not, then the OTHER subtype is not part of the transformation process.

    42. Marge Hohly 42 Subtype model – Single table Note mandatory attributes salary/hourly rate became optional Need check constraint to enforce mandatory requirement CHECK (epe_type = ‘FTE’ and salary is not null and hourly_rate is null and agy_id is null) OR (epe_type ‘PTE’ and salary is null and hourly_rate is not null and agy_id is not null) Explain that a check constraint is activated when you insert or update a row in the table. So, if you tried to update the row for Marcus Rivera by putting a value in the salary column, the check constraint would prevent you from succeeding. Similarly, if you wanted to enter a new row for a full-time employee (epe_type = FTE), but tried to enter a value for agy_id, the check constraint would prevent the insert.Explain that a check constraint is activated when you insert or update a row in the table. So, if you tried to update the row for Marcus Rivera by putting a value in the salary column, the check constraint would prevent you from succeeding. Similarly, if you wanted to enter a new row for a full-time employee (epe_type = FTE), but tried to enter a value for agy_id, the check constraint would prevent the insert.

    43. Marge Hohly 43 When Supertype/Single table The single-table implementation is common and flexible implementation. Appropriate where: Most attributes are at supertype level Most relationships are at supertype level Business rules are globally the same for the subtypes This is the implementation used most often. It is elegant and appropriate for most situations and should be considered as the first choice. Usually you would create a view for every subtype, showing only the columns that belong to that particular subtype. The correct rows are selected using a condition based on the discriminator column. Example: CREATE OR REPLACE FULL_TIME_VW AS Select id, first_name, last_name, salary, dpt_id, epe_id from EMPLOYEES where epe_type = ‘FTE’;This is the implementation used most often. It is elegant and appropriate for most situations and should be considered as the first choice. Usually you would create a view for every subtype, showing only the columns that belong to that particular subtype. The correct rows are selected using a condition based on the discriminator column. Example: CREATE OR REPLACE FULL_TIME_VW AS Select id, first_name, last_name, salary, dpt_id, epe_id from EMPLOYEES where epe_type = ‘FTE’;

    44. Marge Hohly 44 Two-Table implementation Create a table for each subtype Rules Tables: One table per first-level subtype. Columns: Each table gets a column for all attributes of the supertype with the original optionality. Each table also gets a column for each attribute belonging to the subtype, also with the original optionality. Identifiers: The primary UID at the supertype level creates a primary key for each table. Secondary UIDs of the supertype become unique keys in each table. Relationships: All tables get a foreign key for a relationship at the supertype level, with the original optionality. For relationships at the subtype levels, the foreign key is implemented in the table it is mapped to. Original optionality is retained. Point out that the original optionality of the attributes and relationships is carried over to the columns and foreign keys, so there is no need for a check constraint as there is in the one-table implementation. If there were a need to have unique values of the primary key (ID) across both tables, then additional programming would be necessary.Point out that the original optionality of the attributes and relationships is carried over to the columns and foreign keys, so there is no need for a check constraint as there is in the one-table implementation. If there were a need to have unique values of the primary key (ID) across both tables, then additional programming would be necessary.

    45. Marge Hohly 45 2-table cont. A separate table would be created for SHIRTS and SHOES. Point out that id, material, and mnr_id come from attributes in OR relationships to the supertype. Therefore, they appear in both tables.Point out that id, material, and mnr_id come from attributes in OR relationships to the supertype. Therefore, they appear in both tables.

    46. Marge Hohly 46 Subtype Considerations Subtype implementation may be appropriate when: Subtypes have very little in common. There are few attributes at the supertype level and several at the subtype level. Most of the relationships are at the subtype level. Business rules and functionality are quite different between subtypes. How tables are used is different -- for example, one table is being queried while the other is being updated. Usually you would create an additional view that represents the supertype, showing all columns of the supertype and the various subtypes. The view SELECT statement must use the UNION operator.Usually you would create an additional view that represents the supertype, showing all columns of the supertype and the various subtypes. The view SELECT statement must use the UNION operator.

More Related