Phase 2

1
Phase 2 Database Design

• MSTU 5031

2
Phase 2

• Defining requirements
• Analyzing requirements
• Exploring alternatives

3
Defining Requirements

• Inputs
• Outputs
• Processes
• Timing
• User Controls

4
Amazon.com

• Inputs
• Index page
• Search string
• Clicking on links
• Outputs (nouns)
• Search results
• Web page
• Processes (verbs)
• Search database for search string
• Retrieve web page
• Timing
• User controls

5
Online Book Publishing Store

• Publishing company with 3 subsidiary lines
• The database stores information that editors,
  administrators and executives might want about
  books, their authors, their editors, and the
  companys financial arrangements.
• The database can produce many kinds of reports
  summarizing current sales, comparing different
  book lines, discovering which editors work with
  which authors, etc

6
Steps

• Investigate
• List all entities and all possible attributes
• Entity-Relationship diagrams
• Ensure each entity has attribute(s) you can use
  to uniquely identify any row in a future table.
• Consider relationship between entities
• Analyze according to normalization guidelines
• SQL
• Reevaluate

7
What is database design?

• The process of deciding what the database will
  look like.
• Database design involves choosing
• The tables
• Columns for each table
• How the table and columns interact with each
  other
• Iterative process

8
Characteristics of Good Design

• Good design
• Makes your interactions with the database easier
  to understand
• Guarantees the consistency of the database
• Paves the way for the highest performance your
  system can deliver.

• Bad design
• Foster misunderstandings of query results
• Increase the risk of introducing inconsistencies
  in the data.
• Force redundant data entry
• Make life difficult if you need to change the
  structure of the tables youve built and filled
  with data

9
Logical Structure

• Database design is concerned with the logical
  structure of a database.
• Independent of what the user eventually sees.
• Independent of the physical structure and storage
  of the database.

10
Approach to Database Design

• Design aids
• Questions
• Business Rules
• Entity-relationship modeling
• Normalization
• Data dictionary/definitions

11
Questions Business Rules

• Which authors live in California?
• Which business book costs more than 9.95?
• How much do we owe the author of Life Without
  Fear?

• An author may have written more than one book
• A book may have been a collaborative project of
  more than one author

12
Data Entities Relationships

• Entity relationship modeling
• The things - entities about which information
  will be store in the database system
• The properties of these things
• The relationships among them

13
Normalization

• Protecting your data integrity by avoiding
  duplicate data.
• The normalization guidelines are most useful when
  youve identified which columns go in which table.

14
Normalization

• Set of design standards called normal forms
• Five normal forms are widely accepted
• Making your tables match these standards is
  normalization
• Form progresses in order from first through fifth
• Each form implies that the requirements of the
  previous form have been met

15
Why Normalize?

• Normalization guidelines involves splitting
  tables into tow or more tables with fewer
  columns, designating primary-foreign key
  relationships into new smaller tables that can be
  reconnected with the join operation.
• This help reduce data redundancy within tables
• Intentional duplication is not the same as
  redundancy
• The maintenance of intentional duplication
  (consistency) between primary and foreign keys is
  a major point of referential integrity.

16
Entities Attributes

• Entities
• Attributes

17
Entities

• Authors
• Books
• Editors
• Subsidiary publishing houses

18
Properties or Attributes of Each Entity

• Book
• Books name
• Books price
• Books publication date
• Author
• Authors name
• Authors address
• Authors telephone number
• Editor
• Editors name
• Editors address
• Editors telephone number
• Publisher
• Publisher's name
• Publishers address

19
Preliminary Sketch
20
Preliminary Sketch
21
Defining Primary Keys
22
Unique Identifiers

• Keys
• Pieces of data that help to identify a row of
  information in a table.
• Types of keys
• Primary
• Always have a value (it cannot be null)
• Have a value that remains the same (never
  changes)
• Have a unique value for each record in the table
• Foreign
• A column in one table whose values match those of
  the primary key in some other table.

23
Relationships Foreign Keys

• One to many
• Many to many
• One to one
• Foreign key - a column in one table whose values
  match those of the primary key in some other
  table.

24
More towards the physical model

• Requirements for Data dictionary
• Entity
• Attribute name
• Null? (required field, primary key, foreign key)
• Field size
• Data type (varchar, date, )
• Instance