Database Design

1
Database Design

• File Systems and Databases
• The Relational Data Model
• ER Diagrams
• Visio

2
Review

• Project
• Tasks
• Resources
• Baseline
• Costing

3
Objectives

• Introduce databases
• Describe database systems
• Describe database models
• Discuss RDBMS
• Explain the process of database design
• Explain ER Diagrams
• Introduce Visio

4
Databases

• Why is database design important?
• What tools exist to design databases?

5
Key Terms

• Data Raw facts
• Information Processed data
• Knowledge Gained from information
• Good, relevant, timely information is the key to
  good decision making

6
Databases

• DATABASE computer term for a collection of
  information concerning a certain topic or
  business application, can help you organize this
  related information in a logical fashion for easy
  access and retrieval.
• Data stored in tables
• Enter data into tables via data-entry forms
• Reports output the data to the screen or printer
• Queries retrieve desired information from the
  table(s)

7
Databases

• Over time, technological advances resulted in
• more affordable and powerful hardware
• increased tangible and intangible benefits to
  businesses who use technology to manage their
  operations
• increased costs to companies who allowed data
  redundancy and inconsistency across multiple
  departments

8
Database Management Systems

• Collection of programs that
• Manages the database structure
• Controls access to the data stored in the
  database
• Allows sharing of data among multiple
  applications or users

9
DBMS

• Database Management Systems
• Allows users to create, store, and manipulate the
  images of entities and relationships
• Entityperson, place, or thing for which data are
  to be collected and stored modeled as table
• Attributecharacteristics of entities modeled
  as columns within a table
• Relationshipseparate entities can have
  relationships with each other

10
Why do we need DBMS?

• Integrated data
• Reduced data duplication
• Program-data independence
• Ease of accessing data
• Simplifies implementation, maintenance, and
  documentation

11
More on data redundancy

• Redundant dataunnecessarily duplicated data
• Same data are kept in different locations for the
  same entity
• Can cause
• Data inconsistency
• Data anomalies
• Modification anomalies
• Insertion/deletion anomalies

12
Design Objectives

• Fulfill organizations need for information in a
  timely, consistent and economical manner
• Eliminate the duplication of database content
  across the organization
• Provide rapid access to the specific elements of
  information in the database required by each user
  category
• Accommodate database expansion to adapt to the
  needs of a growing organization (new products and
  processes), complying with governmental reporting
  requirements and incorporating new transaction
  processing and decision-support applications.

13
More Objectives

•  Maintain integrity of the database so that it
  contains only validated auditable information.
• Prevent access to the database by unauthorized
  persons (permit access only to those elements of
  the database information that individual users or
  categories of users need in the course of their
  work, allow only authorized persons to add or
  edit information in the database)
• Ease the creation of data entry, editing, display
  and reporting applications that efficiently serve
  the needs of the users of the database

14
RDBMS

• Relational Database Management Systems
• Advantages
• Create database structures containing fields,
  tables and table relationships
• Easily add new records, change field values in
  existing records and delete records
• Contains built-in query language
• Contains built-in report generator
• Provides protection of databases through
  security, control, and recovery facilities

15
Relational Database

• Uses multiple tables
• A one-to-many table example is when one publisher
  owns many books
• A many-to-many example is when an order can
  specify many books and one book may appear in
  many orders

16
Relationship Example
Customer Table Cust Last Name First
Name Address City State Zip
Order Table Order Cust Item Qty Price
17
Basic Modeling Concepts

• Database design is both art and science.
• Data model
• Relatively simple representation, of complex
  real-world data structures
• Usually graphic
• Represents data structures and their
  characteristics, relations, constraints, and
  transformations.
• Employed by database designer as communications
  tools to facilitate the interaction among the
  designer, the applications programmer, and the
  end user.
• A good database is the foundation for good
  applications.

18
E-R Model Terminology

• Entities
• In E-R models, refers to the entity set, not just
  a single entity occurrence.
• Refers to real world objects
• Uses nouns
• Represents things, places, people, and events
• Uniquely identified to allow tracking across
  business processes.
• Represented by a rectangle containing the
  entitys name.

19
E-R Model Terminology

• Attributes
• Particular properties or characteristics of an
  entity
• Attributes have a domain-- the attributes set of
  possible values.
• Attributes may share a domain.
• Some attributes uniquely identify an entity
• Primary key
• Represented by ovals and connected to the entity
  with a line.
• Each oval contains the name of the attribute it
  represents.

20
E-R Model Terminology

• Relationships
• An association between entities.
• A verb describes the relationship
• Cardinality
• defines the number of entity occurrences
  associated with one occurrence of a related
  entity
• Relationships are represented by diamond-shaped
  symbols.

21
Entity-Relationship Diagrams Chen Model

• Graphically shows a databases entities and the
  relationships among the entities.
• An entity and relationship are both required
• Relationship examples

22
E-R Diagram

• Entity - represented by rectangles (data units
  or data objects)
• Fields - represented by circles (attributes)
  sometimes not shown
• Relationships - represented by diamonds (exist
  between entities)
• Many-to-Many Relationships - represented by
  diamonds within rectangles (associative entity)

Student
SSN
HAS
23
Cardinality

• Cardinality expresses the specific number of
  entity occurrences associated with one occurrence
  of the related entity.
• Format (minimum, maximum)
• 0 is used to represent no lower limit
• N is used to represent no upper limit
• Example (0,N)
• Clearly defines the total relationship

24
Cardinality

• Example
• A professor can teach no more than four classes
  and no less than one class
• Each class can have one and only one professor

Professor
Class
M
1
teaches
(1,1)
(1,4)
25
Cardinality

• Example
• A classroom can have between 10 and 30 students
  in it. The size of the classroom requires a
  limit of 30 students, and if less than 10
  students register the class will be cancelled.
• A student may or may not be taking the class
• An associative entity is used for the
  many-to-many relationship

Classroom
Student
M
M
Assign
(0,1)
(10,30)
26
Student Major Example
SSN
MID
State
Student
Major
M
1
Description
HAS
City
Lname
MID
Fname
Street
Major(MID,Description)
Student(SSN,MID,Lname,Fname,Street,City,State)
27
Supplier Example
SuppID
ItemID
compName
Description
contactName
M
M
Supplier
Item
Sup_item
Price
Address
Zip
SuppID
State
City
Item(ItemID, Description, Price, SuppID)
Supplier(SuppID, compName, contactName,
Address,City,State, Zip)
28
Developing an E-R Diagram

• Identify organizations operations, procedures
• Identify business rules
• Create E-R diagram
• Review with end users for accuracy
• Repeat until agreement is reached on a fair
  representation of the organizations activities
  and functions.

29
Business Rules

• Business rules are
• precise statements,
• derived from a detailed description of the
  organization's operations
• define one or more of the following modeling
  components
• Entities
• Relationships
• Attributes
• Connectivities
• Cardinalities
• Constraints

30
Examples of Business Rules

• An invoice contains one or more invoice lines,
  but each invoice line is associated with a single
  invoice.
• A store employs many employees, but each employee
  is employed by only one store.
• A college has many departments, but each
  department belongs to a single college. (This
  business rule reflects a university that has
  multiple colleges such as Business, Liberal Arts,
  Education, Engineering, etc.)
• A driver may be assigned to drive many different
  vehicles, and each vehicle can be driven by many
  drivers. (Note Keep in mind that this business
  rule reflects the assignment of drivers during
  some period of time.)
• A client may sign many contracts, but each
  contract is signed by only one client.
• A sales representative may write many contracts,
  but each contract is written by one sales
  representative.

31
In Class Example

• Develop an ER Diagram for the following Business
  Rules
• An invoice is written by a sales rep. Each sales
  representative can write many invoices, but each
  invoice is written by a single sales
  representative.
• The invoice is written for a single customer.
  However, each customer can have many invoices.
• An invoice can include many detail lines, which
  describe the products bought by the customer.
• The product information is stored in a product
  entity
• The products vendor information is found in a
  vendor entity.

32
ERD
33
Crows Foot vs. Chen Model

• Both Chen and Crows Foot models use rectangles
  to represent entities.
• Entity names are nouns, relationship names are
  verbs
• Chen model places the relationship names within a
  diamond. The Crows foot model writes the
  relationship name next to the relationship line
  that connects the entity rectangles
• The number 1 represents the 1 side of the
  relationship in the Chen model and the M is used
  to represent the many side.
• The Crows foot model uses a bar that crosses the
  relationship line for a 1 side and a
  three-pronged crows foot to indicate the many
  side.

34
Creating ERDs in Visio

• Create business drawings
• Microsoft acquired Visio in 1999
• What can Visio create?
• building blueprints
• street maps
• project timelines
• program flowcharts
• network diagrams
• data flow
• process flow diagrams
• ERDs!!
• Etc.

35
Stencils

• A stencil (template) is simply a set of common
  shapes and connectors used in the type of drawing
  specified
• When start Visio, must identify new
  Drawing/Category type
• Database Category Database Model Diagram

36
Shapes and Connectors
Use to add a Table to your ERD
Use to define the foreign key between two tables
in your ERD
Note the arrow for the relationship must be
pointing to the one table
37
Entities

• To create entities, click and drag the entity
  shape to the blank graph paper worksheet on the
  right.
• To modify an entity, double click on it
• To delete an entity, click on it and press delete
• You may want to use page setup to rotate it as
  landscape orientation

38
Create Table

• Database properties
• Change table name (physical name)
• Add attributes (Columns)
• Specify primary key (PK)

39
Attributes

• The order you add columns is not important
• Visio underlines the primary key and moves it to
  the top
• Change data type
• Default data type is char(10)
• Required fields are show in bold

40
Relationships

• You must have a foreign key in the many table!!!!
• Click the relationships connector to any blank
  space in the drawing, it will appear as an arrow
• Click the arrow head and drag it to the one table
  until it turns red
• Drag the other end to the many table until it
  turns red
• Note you must connect the tables in this
  direction children point to their parents

41
Modifying Relationships

• Double click connector line to modify
  relationships
• Choose Name to give the relationship a physical
  name (leave verb phrase the same)
• Visio will specify the foreign key on the many
  table make sure this is correct
• To specify a different foreign key, click on the
  proper key and choose associate
• If you need to move your tables, Visio will
  automatically move the relationships as well

42
Modifying Relationships
43
Modifying Relationships

• Choose Database Options Document
• Can modify the order of fields
• Can change the way the document looks
• Choose the Relationship tab
• Choose Show Physical Name
• Can choose Crows Foot model if youd like

44
Adding Cardinality

• To modify cardinality, double click the
  relationship line
• Choose Miscellaneous, and specify the cardinality
  (range or exact numbers)

45
Visio Details

• Saved as .vsd file
• Can save as a picture (.jpg, .gif)

46
Transforming ER Diagrams into Relations (Tables)

•  Transform conceptual data model into a set of
  normalized relations
• Steps
• Represent entities
• Represent relationships
• Normalize the relations
• Merge the relations

47
Entities

• Each entity is transformed into a relation
• The primary key of the entity type becomes the
  primary key of the corresponding relation
• The primary key must satisfy the following two
  conditions
• The value of the key must uniquely identify every
  row in the relation
• The key should be nonredundant

48
Relationships

• 1 to many
• Add the primary key attribute of the entity on
  the one side of the relationship as a foreign key
  in the relation on the many side
• 1 to 1
• Add the primary key of A as the foreign key of B
• Add the primary key of B as the foreign key of A

49
More Relationships

• Many to many
• Create a separate relation
• Primary key of new relation is a composite of two
  attributes that both take their values from the
  same primary key
• OR Primary key of new relation is a unique
  autonumber

50
ERD1
51
ERD2
52
Example Create a Schema
Student(SID, Name, Phone, Address) Enrollment(SID,
CID, Grade) Course(CID, Desc)
53
Key concepts

• DBMS
• Data redundancy
• ERD
• Entity
• Relationship
• Attribute
• Visio