The Design Process

1
The Design Process

1. Define Objectives
2. Analyze the Current Database
3. Define Table Structures
4. Establish Table Relationships

1. Define Business Rules
2. Determine and Establish Views
3. Review Data Integrity

2
1. Define Objectives

• Mission Statement
• A succinct statement in general terms describing
  the purpose of the database
• New Starz Talent Agency
• Mission Objectives
• A series of statements describing general tasks
  that the user can perform with the database.
• They support the mission statement and help the
  designer to determine the various aspects of the
  database
• New Starz Talent Agency

3
Mission StatementNew Starz Talent Agency

• The purpose of the New Starz Talent Agency
  database is to maintain the data in order to
  supply information that supports the engagement
  services we provide to our clients and the
  management services we provide to our
  entertainers.

Back
4
Mission ObjectivesNew Starz Talent Agency

• Maintain information for our clients that ask for
  entertainment engagements.
• Maintain complete entertainer information.
• Keep track of all the engagements we book.

Back
5
2. Analyze the Current Database

• Interview management and users
• Find out what kind of data is being used
• Find out how they are collected and handled on a
  daily basis
• Compile list of fields and calculated fields
• This list serves as a starting point for building
  tables, then the database

6
3. Define Tables Structures

• Determine entities
• Identify their attributes
• Define tables and fields
• Establish keys
• Define field specifications
• Make sure that a table represents one entity
  (subject) with no duplicate fields

7
4. Establish Table Relationships

• Determine which tables are directly related
• Determine type of relationship--one-to many,
  one-to-one, many-to-many
• In the case of many-to-many, use a link table

8
5. Define Business Rules

• E.g.
• Use 2-letter abbreviation for state name
• shipDate must be later than orderDate
• daytimePhone number is required
• An agent can handle a maximum of 20 clients
• An employee salary cannot be more than 100,000

9
6. Determine Views

• Some views users need to see
• Specific information from a single table
• Specific information obtained from several tables
• Summaries from several tables
• Create Queries
• What are the questions that the user wants
  answered?

10
Queries

• Which books are 25 or less?
• What is the phone number of publisher Big House?
• What is the phone number of Melville?
• Who publishes the book Macbeth?
• All books published by Big House over 20.00
• All books written by Shakespeare and their
  publisher

11
7. Review Data Integrity

• At table level, make sure each record is unique.
• At the field level, check all field are
  specifications
• At relationship level, check that relationships
  are valid, that there are no many-to-many, etc.

12
Keys

• Candidate Keys
• Primary Keys
• Foreign Keys

13
Employees
SSN firstN lastN Address City Phone
456919938 Kendra Rider 1204 Bryant Rd Seattle 3639948
386112231 Katherine Erlich 101 C St Bellevue 3226992
601480039 Tim Ennis 7402 King Dr Redmond 5274992
116931299 Shannon McLain 4141 Lake Way Seattle 3366992
478021129 Susan McLain 2100 Mineola Av Seattle 5729948
655925523 Estella Pundit 101 C St Bellevue 3226992
601221734 Tim Sherman 66 120th Bothell 5223232
14
Candidate Keys

• Field or set of fields that uniquely indentifies
  each record in the table
• Must contain unique values
• Cannot be null
• Primary key will be chosen from these

15
Artificial Candidate Key

• Created if none of the fields qualify as
  candidate key
• Or, if other candidate keys are complicated
• Or, for efficiency (e.g., 5-digit number takes
  less time to compare than a 5-digit text)

16
Primary Key

• Chosen from Candidate Keys
• Choose the simplest key.

17
Elements of a Primary Key

• Uniquely identifies each record in table
• Contains unique values
• Cannot be null
• Contains minimum number of fields necessary to
  define uniqueness
• Not optional
• Its value should not be modified, except in rare
  cases

18
Foreign Key

• A Primary Key from one table becomes a Foreign
  key another table when that field is used to
  established a relationship between two tables.
• While a Primary Key in a table has unique values,
  Foreign Key values are repeated.

19
Foreign Key
BuildingsbldNumber PK bldFloors bldElevator bldP
arking
RoomsrmNumber PK bldNumber FK rmType rmSize rmPo
ne
1
N
20
Elements of a Foreign Key

• Has the same name as the Primary Key from which
  it is copied (usually)
• Has the same type as the Primary Key from which
  it is copied