Designing Relational Databases: A Comprehensive Guide, Slides of Introduction to Database Management Systems

Download Designing Relational Databases: A Comprehensive Guide and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Database Management Systems

1

Objectives

• Why are models important in designing systems?
• How do you begin a database project?
• How do you know what data to put in a database?
• What is a class diagram (or entity-relationship diagram)?
• Are more complex diagrams different?
• What are the different data types?
• What are events, and how are they described in a database design?
• How are teams organized on large projects?
• How does UML split a big project into packages?
• What is an application?

2

The Need for Design

• Goal: To produce an information system that

adds value for the user

– Reduce costs

– Increase sales/revenue

– Provide competitive advantage

• Objective: To understand the system

– To improve it

– To communicate with users and IT staff

• Methodology: Build models of the system

4

Designing Systems

• Designs are a model of existing & proposed systems

• They provide a picture or representation of reality
• They are a simplification
• Someone should be able to read your design (model) and

describe the features of the actual system.

• You build models by talking with the users

• Identify processes
• Identify objects
• Determine current problems and future needs
• Collect user documents (views)

• Break complex systems into pieces and levels

5

Initial Steps of Design

7

1. Identify the exact goals of the system.

2. Talk with the users to identify the basic forms and reports.

3. Identify the data items to be stored.

4. Design the classes (tables) and relationships.

5. Identify any business constraints.

6. Verify the design matches the business rules.

Entities/Classes

8

Customer

CustomerID LastName FirstName Phone Address City State ZIP Code

Name

Properties

Add Customer

Delete Customer

Methods (optional for database)

Definitions

10

 Relational database: A collection of tables.

 Table: A collection of columns (attributes) describing an entity. Individual objects are stored as rows of data in the table.

 Property (attribute): a characteristic or descriptor of a class or entity.

 Every table has a primary key.  The smallest set of columns that uniquely identifies any row  Primary keys can span more than one column (concatenated keys)  We often create a primary key to insure uniqueness (e.g., CustomerID, Product#,.. .) called a surrogate key.

EmployeeID TaxpayerID LastName FirstName HomePhone Address 12512 888-22-5552 Cartom Abdul (603) 323-9893 252 South Street 15293 222-55-3737 Venetiaan Roland (804) 888-6667 937 Paramaribo Lane 22343 293-87-4343 Johnson John (703) 222-9384 234 Main Street 29387 837-36-2933 Stenheim Susan (410) 330-9837 8934 W. Maple

Employee

Properties

Rows/Objects

Primary key Class: Employee

Definitions

12

Entity: Something in the real world that we wish to describe or track. Class: Description of an entity, that includes its attributes (properties) and behavior (methods). Object: One instance of a class with specific data. Property: A characteristic or descriptor of a class or entity. Method: A function that is performed by the class. Association: A relationship between two or more classes.

Entity: Customer, Merchandise, Sales Class: Customer, Merchandise, Sale Object: Joe Jones, Premium Cat Food, Sale # Property: LastName, Description, SaleDate Method: AddCustomer, UpdateInventory, ComputeTotal Association: Each Sale can have only one Customer.

Pet Store Examples

Class Diagram

• Class/Entity (box)
• Association/Relationship
  • Lines
  • Minimum
    • 0: optional
    • 1: required
  • Maximum
    • Arrows
    • 1, M

14

Customer

Order

Item

.

.

Sample Association Rules

(Multiplicity)

• An order must have exactly 1

customer,

• 1 … 1 Minimum of 1
• 1 … 1 Maximum of 1
• And at least one item.
• 1 … * Minimum of 1
• 1 … * Maximum many
• An item can show up on no

orders or many orders.

• 0 … * Optional (0)
• 0 … * Maximum many

15

Customer

Sale

Item

N-ary Association Example

17

Employee Name ...

Component CompID Type Name

Product ProductID Type Name

EmployeeI D Name … 11 Joe Jones … 12 Maria Rio …

Product I D Type Name A3222 X32 Corvet t e A5411 B17 Camaro

EmployeeI D CompI d Product I D 11 563 A 11 872 A 11 563 A 11 872 A 12 563 A 12 882 A 12 888 A 12 883 A

CompI D Type Name 563 W32 Wheel 872 M15 Mirror 882 H 32 Door hinge 883 H 33 Trunk hinge 888 T54 Trunk handle

Assembly

Assembly EmployeeID CompID ProductID

Multiplicity is defined as the number of items that could appear if the other N-1 objects are fixed. Almost always “many.”

Association Details: Aggregation

18

Sale

SaleDate Employee

Item

Description Cost

• contains *

Aggregation: the Sale consists of a set of Items being sold.

Association Details: Generalization

20

Animal DateBorn Name Gender Color ListPrice

Mammal LitterSize TailLength Claws

Fish FreshWater ScaleCondition

Spider Venomous Habitat

{disjoint}

Inheritance

• Class Definition--encapsulation
  • Class Name
  • Properties
  • Methods
• Inheritance Relationships
  • Generic classes
  • Focus on differences
  • Polymorphism
  • Most existing DBMS do not handle inheritance

21

Accounts AccountID CustomerID DateOpened CurrentBalance OpenAccount CloseAccount

Class name

Properties

Methods

Savings Accounts InterestRate

PayInterest

Checking Accounts MinimumBalance Overdrafts

BillOverdraftFees CloseAccount

Inheritance

Polymorphism