Concepts of Database Management, Fifth Edition

1
Concepts of Database Management, Fifth Edition

• Chapter 6
• Database Design 2
• Design Methodology

2
Objectives

• Discuss the general process and goals of database
  design
• Define user views and explain their function
• Define database design language and use it to
  document database designs
• Create an entity-relationship diagram to visually
  represent a database design
• Explain the physical-level design process

3
Database Design

• Information-level Design building a database
  that satisfies the organizations requirements as
  cleanly as possible.
• Physical-level design designers transform the
  information-level design into a design for the
  specific DBMS used by the organization
• User Views requirements necessary to support a
  particular users operations.

4
User Views

• A user view is the set of requirements that is
  necessary to support the operations of a
  particular database user.
• For each user view, designers must design the
  database structure to support the view and then
  merge it into a cumulative design.

5
Information-Level Design Methodology

• Information-level Design Methodology
• Represent user view as collection of tables
• Normalize these tables
• Identify all keys
• Merge the result into design

6
Represent User View as Collection of Tables

• What procedure should you follow when the correct
  design is not so obvious?
• Step 1 Determine entities (tables) involved and
  create separate table for each type
• Department(
• Employee(
• Step 2 Determine primary key for each table
• Department(DepartmentNum,
• Employee(EmployeeNum,

7
Represent User View as Collection of Tables

• Step 3 Determine properties for each entities
• Department(DepartmentNum, Name, Location
• Employee(EmployeeNum, LastName, FirstName,
  SocSecure
• Step 4 Determine relationships among entities
• Department(DepartmentNum, Name, Location)
• Employee(EmployeeNum, LastName, FirstName,
  SocSecure, DepartmentNum)

8
Types of Relationships

• One-to-Many Relationships created by including
  the primary key of the one table as a foreign
  key in the many table
• Many-to-Many Relationships created with a new
  table whose primary key is the combination of the
  primary keys of the original tables
• One-to-One Relationship created with a single
  relationship from one record in a table to a
  single record in another table

9
Normalize the Tables

• Represent all keys
• Primary, alternate, secondary, foreign
• Database Design Language (DBDL)
• Mechanism for representing tables and keys
• DBDL Example
• Figure 6.1

10
DBDL Notation

• Table name followed by columns in parentheses
• Primary key column(s) underlined
• AK identifies alternate keys
• SK identifies secondary keys
• FK identifies foreign keys

11
Entity-Relationship Diagrams

• Diagram that visually represents database
  structure
• Rectangle represents each entity in the E-R
  diagram
• Primary key for each entity appears above the
  line in the rectangle for each entry

12
Entity-Relationship Diagrams

• Other columns that comprise each entity appear
  below the line within each rectangle
• The letters FK appear in parentheses following
  the foreign key respectively
• For each foreign key, there is a line leading
  from the rectangle that corresponds to the table
  being identified to the rectangle that
  corresponds to the table containing the foreign
  key.

13
Figure 6.2 Entity-Relationship Diagrams
14
Merge the result into the design

• As soon as you have completed Steps 1 through 3
  for a given user view, you can merge these
  results into the cumulative design.
• Next, you combine tables that have the same
  primary key to form a new table.

15
Merge the result into the design

• For example, if the cumulative design already
  contains the following table
• Employee(EmployeeNum, LastName, Firstname,
  SocSecNum, DepartmentNum)
• and the user view you just completed contains the
  following table
• Employee(EmployeeNum, LastName, Firstname,
  Street, City, State, Zip)
• Then you need combine then two tables because
  they have the same primary key.
• Employee(EmployeeNum, LastName, Firstname,
  SocSecNum, DepartmentNum, Street, City, State,
  Zip)

16
Figure 6.3 Information-leveldesign methodology
17
Database Design Examples

• Complete an information-level design for a
  database that satisfies the following constraints
  and user view requirements for a company that
  stores information about sales reps, customers,
  parts, and others. You need to create four user
  views.
• To transform each user view into DBDL, examine
  the requirements and create the necessary
  entities, keys, and relationships.

18
Database Design Example 1

• User View 1 Requirements For a sales rep, store
  the sales reps number, name, address, total
  commission, and commission rate.
• Youll need to create only one table to support
  this view
• Rep(RepNum, LastName, FirstName, Street, City,
  State, Zip, Commission, Rate)

19
Database Design Example 1

• User View 2 Requirements For a customer, store
  the customers number, name, address, balance,
  and credit limit. In addition, store the number
  and name of the sales rep who represents this
  customer.
• You would begin by listing the table as follows
• Customer(CustomerNum, CustomerName, Street,
  City, State, Zip, Balance, CreditLimit, RepNum,
  LastName, FirstName)
• The CustomerNum column determines all the other
  columns, as it should. However, the RepNum column
  determines the LastName and FirstName column.

20
Database Design Example 1

• Customer(CustomerNum, CustomerName, Street,
  City, State, Zip, Balance, CreditLimit, RepNum)
• Rep(RepNum, LastName, FirstName)

21
Database Design Example 1

• User View 3 Requirements For a part, store the
  parts number, description, units on hand, item
  class, the number of the warehouse in which the
  part is located, and the price, All units of a
  particular part are stored in the same warehouse.
• Only one table is required to support this user
  view
• Part(PartNum, Description, OnHand, Class,
  Warehouse, Price)

22
Database Design Example 1
23
Database Design Example 1

• User 4 For a order, store the order number the
  order date the number, name, and address of the
  customer that placed the order and the number of
  the sales rep who represents that customer.
• Orders(OrderNum, OrderDate, CustomerNum,
  CustomerName, Street, City, State, Zip, RepNum,

24
Database Design Example 1

• In addition, for each line item within the order
  (this means repeating group), store the part
  number and description, the number of the part
  that was the ordered, and the quoted price.
• Orders(OrderNum, OrderDate, CustomerNum,
  CustomerName, Street, City, State, Zip, RepNum,
  (PartNum, Description, NumOrdered, QuotedPrice))

25
Database Design Example 1

• 1NF Remove the repeating group
• Orders(OrderNum, OrderDate, CustomerNum,
  CustomerName, Street, City, State, Zip, RepNum,
  PartNum, Description, NumOrdered, QuotedPrice)
• 2NF Some columns depend on only a portion of the
  primary key.
• The combination of OrderNum and PartNum
  determines NumOrdered and QuotedPrice.
• Orders(OrderNum, OrderDate, CustomerNum,
  CustomerName, Street, City, State, Zip, RepNum)
• Part(PartNum, Description)
• OrderLine(OrderNum, PartNum, NumOrdered,
  QuotedPrice)

26
Database Design Example 1

• 3NF CustomerNum determines CustomerName, Street,
  City, State, Zip and RepNum.
• Orders(OrderNum, OrderDate, CustomerNum)
• Customer(CustomerNum, CustomerName, Street,
  City, State, Zip, RepNum)
• Part(PartNum, Description)
• OrderLine(OrderNum, PartNum, NumOrdered,
  QuotedPrice)

27
(No Transcript)
28
Exercise

• Ray Henry, the owner of a bookstore chain named
  Henry Books, gathers and organizes information
  about branches, publishers, authors, and books.
  Each branch has a number that uniquely identifies
  the branch. In addition, Ray tracks the branchs
  name, location, and number of employee. Each
  publisher has a code that uniquely identifies the
  publisher. In addition, Ray, but you dont want
  to treat the entire project as a single user
  view. Ray has provided you with all the reports
  the system must produce, and you will treat each
  report as a user view. Ray has given you the
  following requirements.

29
Exercise

• User View 1 Requirements For each publisher,
  list the publisher code, publisher name, and the
  city in which the publisher is located.
• User View 2 Requirements For each branch, list
  the number, name, location, and number of
  employees.
• User View 3 Requirements For each book, list its
  code, title, publisher code and name, price, and
  whether it is a paperback.
• User View 4 Requirements For each book, list its
  code, title, price, and type. In addition, list
  the books authors and their names. If a book has
  more than one author, all names must appear in
  the order in which they are listed on the books
  cover. The author order is not always
  alphabetical.

30
Exercise

• User View 5 Requirements For each branch, list
  its number and name. In addition, list the code
  and title of each book currently in the branch as
  well as the number of copies the branch has
  available
• User View 6 Requirements For each book, list its
  code and title. In addition, for each branch that
  currently has the book in stock, list the number
  and name of the branch along with the number of
  copies available.
• To transform each user view into DBDL, examine
  the requirements and create the necessary
  entities, keys, and relationships.

31
Figure 6.13 Cumulative designafter fifth user
view
32
Physical-Level Design

• Undertaken after information-level design
  completion
• Most DBMSs support primary, candidate, secondary,
  and foreign keys
• DB programmers must include logic to ensure the
  uniqueness of primary keys and enforce other
  conditions

33
Summary

• Database design is a two-part process of
  determining an appropriate database structure to
  satisfy a given set of requirements
• A user view is the set of necessary requirements
  to support a particular users operations
• A database design is represented in a language
  called Database Design Language (DBDL)
• Designs can be represented pictorially using
  entity-relationship (E-R) diagrams