Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

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

An in-depth exploration of the design stages of a relational database system. It covers topics such as initiation, requirements analysis, conceptual design, physical design, implementation, and evaluation & review. The document also includes examples of entities, relationships, and data types, as well as discussions on aggregation, composition, and using generated keys.

Typology: Slides

2012/2013

Uploaded on 04/27/2013

prakash
prakash 🇮🇳

4.6

(10)

63 documents

1 / 57

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
Database Management Systems
1
Docsity.com
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15
pf16
pf17
pf18
pf19
pf1a
pf1b
pf1c
pf1d
pf1e
pf1f
pf20
pf21
pf22
pf23
pf24
pf25
pf26
pf27
pf28
pf29
pf2a
pf2b
pf2c
pf2d
pf2e
pf2f
pf30
pf31
pf32
pf33
pf34
pf35
pf36
pf37
pf38
pf39

Partial preview of the text

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