Database design
1 / 47

Database Design - PowerPoint PPT Presentation

  • Uploaded on

Database Design. Sections 11 & 12 drawing conventions, generic model, integrity, keys, mapping conceptual model to logical/physical model. Conventions Review. Crows feet Crows fly East and South Divide complex ERD’s into functional areas Place Highest volume entities in upper left corner

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Database Design' - mason

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Database design

Database Design

Sections 11 & 12drawing conventions, generic model, integrity, keys, mapping conceptual model to logical/physical model

Conventions review
Conventions Review

  • Crows feet

    • Crows fly East and South

    • Divide complex ERD’s into functional areas

    • Place Highest volume entities in upper left corner

    • Improve readability

      • avoid criss-crossing lines

      • increase white spaces so relationships don’t overlap

      • be consistent with font type, size, and styles

Marge Hohly

Generic modeling
Generic Modeling

  • Can reduce number of entities in diagram

  • Can provide more flexibility in unstable situations (where business requirements change often)

  • Use a more distant perspective

  • Review 11.3.3

  • What would happen to the generic model if we had to add 10 new ARTICLE types, each with their own attributes?

Marge Hohly

Generic modeling1
Generic Modeling

  • Have more attributes in fewer entities

  • Many mandatory requirements/attributes become optional

  • Structural rules become procedural rules

  • Example: PANTS waist size was mandatory, with ARTICLE waist size becomes optional

  • What other businesses would be good candidates for generic modeling?

Marge Hohly

Relational database concepts
Relational Database Concepts

  • Conceptual model transforms into a relational database

  • A relational database is a database that is perceived by the user as a collection of relations or two-dimensional tables.

  • Table, each employee (instances), and each column (attribute)

Marge Hohly

Sql to retrieve information
SQL to retrieve information

  • Structured query language (SQL) used to access information

  • English-like phrases

  • Example: SELECT lname, dept_noFROM employeesWHERE emp_no = 210;

Marge Hohly

Using html db sql editor
Using HTML_DB SQL editor


Marge Hohly

Table definitions
Table Definitions

Table – Dfn.

Marge Hohly

Selecting all records
Selecting all records

  • SELECT * FROM employeesWHERE department_no = 10;

Marge Hohly


  • Primary Key (PK)

    • not null

    • no part of PK can be null (entity integrity)

    • unique

    • can be composite

  • 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

Key questions
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?

Marge Hohly

Referential integrity
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)

Marge Hohly

11 4 10

Marge Hohly

Composite key
Composite key

  • Made up of two or more values

  • Together unique

  • ENROLL Table/Entity

    • student_no & ticket_no


    • bank_no & acct_no

Marge Hohly

Jobs table
JOBS Table

Marge Hohly

Data integrity summary
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

Marge Hohly


  • Conceptual model, focus on the business and its rules.

  • Data modeling pays attention to the business requirements, regardless of implementation.

  • Conceptual model Logical model

Marge Hohly

Review 12 2 3
Review 12.2.3

Marge Hohly

Conceptual becomes physical model

Conceptional becomes Physical model

Conceptual becomes Physical model

Marge Hohly

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.

Terminology Mapping

Marge Hohly

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


Marge Hohly

Naming restrictions with oracle
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.

Marge Hohly

Cascade barred relationships
Cascade barred relationships

  • UID from parent entity becomes part of the UID of the child entity

Marge Hohly

Relationship mapping
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.

Marge Hohly

Relationship mapping1

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?

Relationship mapping

Marge Hohly

Relationship mapping2
Relationship mapping

  • FK is mandatory from this diagram

  • FK is optional from this diagram

Marge Hohly

Nontransferable relationship
NonTransferable Relationship relationship

  • Transferablility is a procedural model

  • Must be implemented by a program

  • Need to document this constraint/business rule

Marge Hohly

Barred relationship
Barred Relationship 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

Marge Hohly

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



made up of



made up of



made up of


Cascading barred relationships

Marge Hohly

M m relationship mapping

M:M resolved with intersection entity level

Intersection entity has a composite key with the PK from each parent as FK in child

M:M relationship mapping

Marge Hohly

1 1 relationship mapping
1:1 relationship mapping level

  • 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.

Marge Hohly

Review level

  • FK 1:M

  • PK, FK in same key, rename one

  • M:M first resolve with an intersection entity



Marge Hohly

Review cont
Review cont. level

  • Will be part of PK a composite key

  • FK on mandatory side

  • FK on either side

Marge Hohly

Arc mapping
Arc mapping level

  • 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

Marge Hohly

Arc constraint
Arc constraint level

  • 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 ....)

Marge Hohly

Arc mapping1
ARC mapping level

  • If mandatory then one MUST be NOT NULL

  • If optional then all may be NOT NULL

  • You will always need a check constraint defined

Marge Hohly

Subtype review
Subtype Review level

Marge Hohly

Subtype mapping
Subtype mapping level

  • Mapping supertypes and subtypes makes sure that the right information gets stored with each type.

Marge Hohly

Subtype modeling

Mapping as a single table level


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.

Subtype modeling

Marge Hohly

Subtype modeling single table cont
Subtype modeling – Single table cont. level

  • 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.

Marge Hohly

Subtype model single table

Note mandatory attributes salary/hourly rate became optional level

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)

Subtype model – Single table

Marge Hohly

When supertype single table
When Supertype/Single table level

  • 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

Marge Hohly

Two table implementation

Create a table for each subtype level


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.

Two-Table implementation

Marge Hohly

Subtype considerations
Subtype Considerations level

  • 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.

Marge Hohly