Download
1 / 27
270 likes | 347 Views
Chapter Six. Transforming Data Models into Database Designs. Steps for Transforming a Data Model into a Database Design. Create a table for each entity Create relationship by appropriately placing foreign keys Specify logic for enforcing minimum cardinality.
E N D
Chapter Six Transforming Data Models into Database Designs
Steps for Transforming aData Model into a Database Design • Create a table for each entity • Create relationship by appropriately placing foreign keys • Specify logic for enforcing minimum cardinality.
Create a table for each entity • Specify candidate keys • Specify the primary key; consider surrogate keys as appropriate • Specify properties for each column • Null status • Data type • Default value if any • Data constraints if any • Verify normalization
2. Create relationshipby placing foreign keys • Strong (1:1, 1:N, N:M) • ID-dependent (association, multivalue, archetype/instnace) • Mixed • Subtypes • Recursive
3. Specify logic for enforcing minimum cardinality. • Emphasis on minimum cardinality and on which side it would be.
Create a Table for Each Entity EMPLOYEE (EmployeeNumber, EmployeeName, Phone, Email, HireDate, ReviewDate, EmpCode) Primary key is designated by key symbol
Select the Primary Key • The ideal primary key is short, numeric and fixed • Surrogate keys meet the ideal • Often a DBMS-supplied identifier of each row of a table. • Guaranteed to be unique • Numeric • Short • Fixed • Disadvantages • See next slide
Disadvantages of surrogate key as PK • The values are meaningless (pure sequential numbers) • Suppose you want to determine the department to which an employee belongs to. • If the DepartmentName is the foreign key in EMPLOYEE (and the primary key in DEPARTMENT), then you get an employee row, and you can get the department. • If the primary key in DEPARTMENT is a surrogate key, it is most likely a meaningless number. In the EMPLOYEE table, you get an employee row, and then you pick up the foreign key, which is the primary key of DEPARTMENT, which is a meaningless number. Therefore, you will have to “walk over” to the department using the key to find the name of the department.
Disadvantages of surrogate key as PK 2. Suppose data are shared among different databases. • Suppose a company maintains 3 different SALES databases, one for each product line. • Assume that each has a table called SALES_ORDER with surrogate key called ID. • The DBMS-assigned surrogate keys are unique across the three different databases. • It is possible for two different SALES_ORDER rows, in two different databases, to have the same ID value. • When you merge two databases, ID values will need to be changed to avoid duplicates. If so, the foreign key values may be changed in other tables.
Specify Candidate (Alternate) Keys • The terms candidate key and alternate key (AK) are synonymous • Candidate keys are alternate identifiers of unique rows in a table • ERwin uses AKn.m notation, where n is the ordinal number of the alternate key (i.e., 1 for 1st alternate key, 2 for 2nd alternate key, etc.), and m is the column number in that alternate key • e.g.), if the alternate key is a composite key of, say (course, prof), then course is AK1.1 and prof is AK1.2.
Specify Candidate (Alternate) Keys • In CUSTOMER, • 1st AK is (Name,City) • 2nd AK is Email. Note: AK for Alternate Key
Specify Column Properties: Null Status • Null status indicates whether or not the value of the column can be NULL
Specify Column Properties: Data Type • Generic Data Types: • CHAR(n) • VARCHAR(n) • DATE • TIME • MONEY • INTEGER • DECIMAL
Specify Column Properties:Default Value • A default value is the value supplied by the DBMS when a new row is created
Specify Column Properties: Data Constraints • Data constraints are limitations on data values: • Domain constraint - Column values must be in a given set of specific values • Range constraint - Column values must be within a given range of values – e.g.) Employee hire date could be limited to dates between 1/1/1990 & 12/31/2000. • Intrarelation constraint – Column values are limited by comparison to values in other columns in the same table. e.g.) If the review date is 6 mos. after hire, then EMPLOYEE.HireDate is an interrelation constraint. • Interrelation constraint - Column values are limited by comparison to values in other columns in other tables [Referential integrity constraints on foreign keys]
Verify Normalization • The tables should be normalized based on the data model • Verify that all tables are in: • BCNF • 4NF
Create Relationships:1:1 Strong Entity Relationships • Place the key of one entity in the other entity as a foreign key: • Either design will work – no parent, no child • Minimum cardinality considerations may be important: • 0-M will require a different design than M-0, and
1:N Strong Entity Relationships • Where would you put foreign key, the 1-side or N-side?
1:N Strong Entity Relationships • Place the primary key of the table on the one side of the relationship into the table on the many side of the relationship as the foreign key • The one side is the parent table and the many side is the child table, so “Place the key of the parent in the child”
N:M Strong Entity Relationships • In an N:M strong entity relationship there is no place for the foreign key in either table: • A COMPANY may supply many PARTs • A PART may be supplied by many COMPANYs
N:M Strong Entity Relationships • The solution is to create an intersection table that stores data about the corresponding rows from each entity • The intersection table consists only of the primary keys of each table which form a composite primary key • Each table’s primary key becomes a foreign key linking back to that table COMPANY_PART_INT (CompanyName, PartNumber)
N:M Strong Entity Relationships COMPANY_PART_INT (CompanyName, PartNumber)
