Database Design

1
Database Design

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

2
Conventions Review

• Crows feet
• Crows fly East and South
• Divide complex ERDs into functional areas
• Place Highest volume entities in upper left
  corner
• Improve readability
• avoid criss-crossing lines
• increase white spaces so relationships dont
  overlap
• be consistent with font type, size, and styles

3
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?

4
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?

5
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)

6
SQL to retrieve information

• Structured query language (SQL) used to access
  information
• English-like phrases
• Example SELECT lname, dept_noFROM
  employeesWHERE emp_no 210

7
Using HTML_DB SQL editor
HTML_DB SQL Editor
8
Table Definitions
Table Dfn.
9
Employee Table Structure
10
Selecting all records

• SELECT FROM employeesWHERE department_no
  10

11
Keys

• 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 cant be NULL

12
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?

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
11.4.10
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
JOBS Table
17
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

18
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

19
Review 12.2.3
20
Conceptual becomes Physical model

• Conceptional becomes Physical model

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

22
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

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

24
Cascade barred relationships

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

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

26
Relationship mapping

• 1M mapping
• Foreign key goes in table at crows 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?

27
Relationship mapping

• FK is mandatory from this diagram
• FK is optional from this diagram

28
12.3.4

• Optional or Mandatory determined by crows foot
  end of relationship

29
NonTransferable Relationship

• Transferablility is a procedural model
• Must be implemented by a program
• Need to document this constraint/business rule

30
Barred Relationship

• 12.3.6
• Barred relationship is mapped to a foreign-key
  column on the many side, just like any other M1
  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

31
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

32
MM relationship mapping

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

33
11 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.

34
Review

• FK 1M
• PK, FK in same key, rename
  one
• MM first resolve with an intersection entity

o
35
Review cont.

• Will be part of PK a composite key
• FK on mandatory side
• FK on either side

36
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

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

38
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

39
Subtype Review
40
Subtype mapping

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

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

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

43
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)

44
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

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

46
2-table cont.

• A separate table would be created for SHIRTS and
  SHOES.

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