Database Design for the Web

1
Database Design for the Web

• Sue Hove
• Director of Instructor Readiness
• Macromedia, Inc

2
Topics

• Introduction to Relational Databases
• Conceptual Modeling
• E/R Diagrams
• Physical Modeling
• QA

3
Introducing the Relational Model

• Conceived by E. F. Codd in 1969, then a
  researcher at IBM
• A relational model consists of a series of
  unordered tables that can be manipulated using
  non-procedural operations
• Advantages
• Reduction of redundant data
• Increased data integrity
• Decreased data storage requirements

4
Introducing an RDBMS

• A Relational Database Management System (RDBMS)
  is a collection of tools that can be used to
• Design and implement information storage
  solutions
• Modify stored information
• Retrieve stored information
• Popular RDBMS Packages
• MS Access (not really relational)
• MS SQL Server
• IBM DB2
• Oracle
• Sybase

5
Designing a Database

• Gather data requirements into a Data Store
• Find all input forms/screens
• Find all output reports, invoices
• Logical/Conceptual Modeling
• What data should be stored
• Physical Modeling
• How it should be stored

6
Logical/Conceptual Modeling

• Step 1 Identify Entities
• Step 2 Assign Identifiers
• Step 3 Determine Relationships
• Step 4 Assign Attributes

7
Step 1 Identify Entities
8
Step 2 Assign Identifiers

• Each entity must have value(s) that uniquely
  identify each entity instance
• User-Assigned Identifier naturally-occurring
• System-Assigned Identifier sequential number

9
Step 2 Assign Identifiers
10
Step 3 Identify Relationships

• An association between instance(s) in one entity
  and instance(s) in another table
• Three types of relationships
• One-to-Many Relationship
• One-to-One Relationship
• Many-to-Many Relationship

11
Determining Relationship

• Assume entity A and entity B
• First ask Is there a direct relationship between
  A and B?
• Two parts to every relationship
• Cardinality which describes how many rows in an
  entity may be related to another entity
• Existence which enforces if one row exists, the
  other row must also exist

12
Determining Relationship

• Ask 4 questions about every pair of entities
• How many As can be related to a B?
• Does an A have to be related to a B to exist?
• How many Bs can be related to an A?
• Does a B have to be related to an A to exist?

13
Determining Relationship

• Example Customer and Order
• How many Customers can be on 1 order? (one)
• Does a customer have to have an order to exist?
  (perhaps not collect information for marketing)
• How many Orders can a customer place? (many)
• Does an Order have to have a Customer to exist?
  (yes implications on transactions)
• Conclusion 1M relationship

14
One-to-Many Relationships

• Association between tables where a single row in
  one table corresponds to 0, 1 or multiple rows in
  another
• Most common type of relationship

15
Entity/Relationship Diagrams

• To denote cardinality, the relationship lines
  have either a crows foot for many, or a single
  line for one.
• To denote existence, the relationship line has
  either a crossbar for required, or a circle for
  optional.
•  

16
Reading E/R Diagrams

• One-to-Many Relationship

17
1M Recursive Relationship
18
One-to-One Relationships

• Where a single row in one table corresponds to 0
  or 1 rows in another
• Rarely used but can allow efficient storage of
  information

19
Inheritance Relationship
20
Many-to-Many Relationship

• Where 0, 1 or many rows in one table correspond
  to 0, 1 or many rows in another

21
Step 4 Assign Attributes

• Attribute a non-decomposable unit of information
  about an entity
• Only store 1 piece of (atomic) information in
  each attribute
• For instance, separate all address pieces
• Assign attributes about each entity
• Validate normalization rules
• Determine domains (data types)

22
Normalization

• First, Second, Third, Boyce/Codd, Fourth and
  Fifth normal forms
• Only recommend going to Third normal form

23
Normalization

• 1NF Remove repeating groups into a new entity
• The Key
• Create a child Items table

24
Normalization

• 2NF Remove attributes that are not dependent on
  the whole primary key (used for composite key
  tables)
• The whole key
• OrderDate is only associated with Order_ID not
  with Item_No therefore it needs to be moved
  into the Orders table

25
Normalization

• 3NF Remove attributes that are dependent on
  other, nonkey attributes
• Nothing but the key
• Create a master Product table

26
Physical Data Modeling

• Convert Conceptual Model to Physical Model
• Entities become tables
• Relationships become primary to foreign key
  relations
• Attributes become columns
• Address physical storage concerns here
• Resolve relationships
• MN relationships require associative entity
  creation
• Determine if inheritance relationship becomes 11
  or all in one table
• Create referential integrity to enforce data rules

27
Relational Components - Tables

• Basic data storage structure consisting of
• Rows (records)
• Columns (fields)

28
Columns and Data Types

• Must contain the same type of data in each row
  its domain
• Domain data types include
• Textual, or alphanumeric data
• Numeric data
• Binary data
• Date information

29
Primary Keys

• Unique identifier of each row in the table
• May be a single column or multiple columns
• Can not contain empty values
• Can be user- or system-assigned

30
System-Generated Primary Keys
 
31
Foreign Keys

• Column or combination of columns whose values
  match the primary key of another table within the
  database
• Used to enforce data integrity
• Defines table relationships

32
Naming Columns

• SA PKs ID (EmployeeID or Employee_ID)
• UA PKs Code (Employee_Code)
• Foreign keys name the same as the PK when
  possible

33
Many-to-Many Relationships

• Break many to many relationships up into 2 or
  more one to many relationships

34
Inheritance Relationship

• Choices
• Generate 1 table (Customer) with all attributes
• Generate 3 tables one for each type
• Generate 2 tables Customer and 1 sub-type

35
Reflexive Relationship

• SELECT E1.EmpIoyeeID,
• E1.EmpName,
• E1.EmpSalary,
• E1.ManagerID,
• E2.EmpName AS MgrName
• From Employee E1 LEFT OUTER JOIN Employee E2 ON
  E1.ManagerID E2.EmpIoyeeID

36
QA

• Any questions?

37
(No Transcript)