Implementing Attributes

---

Implementing Plural Attributes

• in the implement entities step, entities become tables and attributes become columns
  • singular attributes remain in the initial table
  • plural attributes move to a new weak table
    • contains the plural attribute and a FK referencing the initial table
    • PK of the new table is the composite of the plural attribute and the FK
    • new table is identified by the initial table
      • PK cascade and FK restrict rules are specified
    • name consists of the initial table name followed by the attribute name
• if a plural attribute has a small, fixed maximum,
  • it can be implemented as multiple columns in the initial table
  • but implementing plural attributes in a new table simplifies queries and is usually better

Implementing Attribute Types

• first a list of attribute types is established
  • during conceptual design
• then a SQL data type is defined for each attribute type
  • during logical design
  • attribute types and corresponding data types are documented in the glossary
• each attribute name includes a standard attribute type as a suffix
  • attribute typed determines the data type of the corresponding column

A derived attribute is one that is calculated from other attributes in the database and is not physically stored.

Implementing Attribute Cardinality

• required or unique attributes become required or unique columns
• columns are presumed optional and not unique unless followed by R or U in the table diagram
• relationship cardinality determines constraints on FK columns:
  • if the table referenced by the FK implements a required entity,
    • the column is required
  • if the table containing the foreign key implements a singular entity,
    • the column is unique
• table diagrams are implemented as CREATE TABLE statements
  • NOT NULL is specified for required columns
  • UNIQUE is specified for unique columns
  • PRIMARY KEY is specified for primary key columns
• composite unique columns and composite PKs cannot be specified in a column definition clause
  • composite constraints require an additional clause in the CREATE TABLE statement

Database Design

• implementing attributes step specifies columns, column constraints, and data types
• plural attributes become new weak tables
• database is completely specified as CREATE TABLE statements
• final step, ‘review tables for third normal form’,
  • ensures that tables do not contain redundant data
  • fine-tunes the design if necessary

Implement Attributes

Step	Activities
7A	Implement plural attributes as new weak tables.
7B	Specify cascade and restrict rules on new foreign keys in weak tables.
7C	Specify column data types corresponding to attribute types.
7D	Enforce relationship and attribute cardinality with UNIQUE and NOT NULL keywords.