Design methodology
This presentation is the property of its rightful owner.
Sponsored Links
1 / 23

Design Methodology PowerPoint PPT Presentation


  • 83 Views
  • Uploaded on
  • Presentation posted in: General

Design Methodology. A structured approach that uses procedures, techniques, tools, and documentation aids to support and facilitate the process of design. Three main phases Conceptual database design Understanding client data E-R (EER) Model

Download Presentation

Design Methodology

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Design methodology

Design Methodology

A structured approach that uses procedures, techniques, tools, and documentation aids to support and facilitate the process of design.

Three main phases

  • Conceptual database design

    Understanding client data

    E-R (EER) Model

    Contract between clients and designers

    E-R Model could be used for any database system

  • Logical database design

    Mapping E-R Model to (relational) database schema

    Normalization

  • Physical database design


How to represent relationship

How to Represent Relationship?

Primary Key and Foreign Key!

1:1

1:* (1..m)

*:* (m..n)

Complex relationship

Recursive relationship

Superclass / Subclass Relationship


One to many 1

One-to-Many (1:*)

Owner (1) Owns (0..*) PropertyForRent

Parent entity: one side

Owner

Child entity: many side

PropertyForRent

Do we need a table for the relationship?

NO!

Copy PK of parent entity to child entity as FK

Copy Ono to PropertyForRent

Does it work to copy Pno to Owner?


One to many 11

One-to-Many (1:*)

E-R Model

Owner (Ono, firstName, lastName, Phone)

PropertyForRent (Pno, Street, City, State, Zipcode, Rent)

Owner (1) Owns (0..*) PropertyForRent

Table Schemas (DBDL)

Owner (Ono, firstName, lastName, Phone)

PK: Ono

AK:

FK: None

PropertyForRent (Pno, Street, City, State, Zipcode, Rent, Ono)

PK: Pno

AK: Street, City, Zipcode

FK: Ono references Owner


One to one 1 1

One-to-One (1:1)

Do we need a new table for the relationship?

NO!

Copy one PK as FK.

Which Direction: Three Cases

  • Total on both sides

  • Partial both sides

  • Total on one side and Partial on the other side


One to one 1 11

One-to-One (1:1)

Total on One Side and Partial on the Other Side

Entity1 (1..1) RelatedTo (0..1) Entity2

Partial Total

Copy PK of Entity2 to Entity1

Some records of Entity1 will have null value for FK

Partial

Copy PK of Entity1 to Entity2

Each record of Entity2 will have a non-null value for FK

Total

Do it this way!


One to one 1 12

One-to-One (1:1)

Total on Both Sides

Entity1 (1..1) RelatedTo (1..1) Entity2

What does it mean?

Duplicate Entity?

Combined into one entity.

If not Duplicate Entity

Copy PK of one entity to another entity as FK.


One to one 1 13

One-to-One (1:1)

Partial on Both Sides

Entity1 (0..1) RelatedTo (0..1) Entity2

Either way

Which side is more partial?

Performance issue


Many to many

Many-to-Many (*:*)

Entity1 (0..*) RelatedTo (1..*) Entity2

Will Coping PK as FK work!

NO!

No multi-value attributes!

What to do?

Adding a new table!


Many to many1

Many-to-Many (*:*)

Client (0..*) Views (0..*) Property

(Date, Comment)

New Table

Viewing

Attributes: Date, Comment

New relationships

Client (1..1) Has (0..*) Viewing (0..*) BeViewed (1..1) Property

One (*:*) becomes two (1:*)

Copy PK of both Client and Property to Viewing as FK

Figure out PK


Many to many2

Many-to-Many (*:*)

Original Entities (E-R Model)

Property (Pno, Street, City, State, Zipcode, Ono)

Client (Cno, firstName, lastName, Phone, MaxRent, PrefType)

Client (0..*) Views (0..*) Property

(Date, Comment)

Table Schemas (DBDL)

Property (Pno, Street, City, State, Zipcode, Ono)

PK: Pno

AK: Street, City, Zipcode

FK: Ono references Owner (not from the *:* relationship)

Client (Cno, firstName, lastName, Phone, MaxRent, PrefType)

PK: Cno

AK: None

FK: None

Viewing (Cno, Pno, ViewDate, Comment)

PK: Cno, ViewDate

AK: None

FK: Cno references Client

Pno references Property


Complex relationship involving 3 or more entities

Complex Relationship(involving 3 or more entities)

Client REGESTERS with Staff at Branch

Add a new table Registration

Registers

Branch

Staff

Client


Complex relationship

Complex Relationship

Client REGESTERS with Staff at Branch

Add a new table Registration

Has

CarryOut

Branch

Registration

Staff

Does

Client


Complex relationship involving 3 or more entities1

Complex Relationship(involving 3 or more entities)

Client REGESTERS with Staff at Branch

Add a new table Registration

New relationships

Client (1..1) Does (1..1) Registration

Staff (1..1) Carryout (0..*) Registration

Branch (1..1) Has (0..*) Registration

Is Client the same as Registration?


Recursive relationship

Recursive Relationship

One-to-many (1:*)

Staff (0..1) Supervises (0..*) Staff

(Supervisor) (Supervisee)

Add a new column (SupervisorNo)

Staff (staffNo, fName,…, SupervisorNo)

Column SupervisorNo stores staffNo of the supervisor

Could have null values

Could have a new entity


Recursive relationship1

Recursive Relationship

Many-to-Many (*:*)

Add a new entity


Recursive relationship2

Recursive Relationship

One-to-One (1:1)

Add a new column

Add a new entity?

Could, but not required.


Superclass subclass relationship

Superclass/subclass Relationship


Multiple relationships between two entities staff and branch

Multiple Relationships between two EntitiesStaff and Branch

Branch (Bno, Street, City, State, Zipcode, Phone)

Staff (Sno, firstName, lastName, Address)

Branch (1..1) Has (0..*) Staff

Staff (1..1) Manages (0..1) Branch

Cannot have circular references.

Branch (Bno, Street, City, State, Zipcode, Phone)

Staff (Sno, firstName, lastName, Address, Bno, Position)


Mapping weak entities to relation schema

Mapping Weak Entities toRelation Schema

Primary Key may not exist

Adding PK based on the relationship

Combining weak entity into parent entity


Strong and weak entities staff and next of kin

Strong and Weak EntitiesStaff and Next_Of_Kin

Staff (Sno, firstName, lastName, Address, Phone, Salary)

Next_Of_Kin(firstName, lastName, Address, Phone)

Staff (1..*) RelatedTo (0..1) Next_Of_Kin

attribute: relationship

This is a one-to-many relationship.

Create PK for Next_Of_Kin and copy that with attribute relationship to Staff as FK

Combine into one table Staff

Same person may be Next_Of_Kin of multiple staff members

Next_Of_Kin of different staff members are not related


Dbdl database design language

DBDL (Database Design Language)

Branch (Bno, Street, City, State, Zipcode, Phone)

PK: Bno

AK: Street, City, Zipcode

Phone

FK: None

Staff (Sno, firstName, lastName, Address, Bno )

PK: Sno

AK: None

FK: Bno references Branch

Client (Cno, firstName, lastName, Phone, MaxRent, PrefType)

PK: Cno

AK: None

FK: None

Viewing (Rno, Pno, ViewDate, Comment)

PK: Rno, ViewDate

AK: None

FK: Rno references Client (Cno)

Pno references Property

No multi-value attributes, no composite attributes.

All relationships are maintained through FKs.


Assignment 4

Assignment 4

Group with two from the same section

Due Wednesday


  • Login