Cmpt 275 phase design
This presentation is the property of its rightful owner.
Sponsored Links
1 / 44

CMPT 275 Phase: Design PowerPoint PPT Presentation


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

CMPT 275 Phase: Design. Map of design phase. DESIGN. LOW LEVEL DESIGN. HIGH LEVEL DESIGN. Data Persistance Subsystem. Classes Class Interfaces Interaction Diagrams. Module Interfaces. Modularization. architecture. User Interface. User Manual. Implementation.

Download Presentation

CMPT 275 Phase: Design

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


Cmpt 275 phase design

CMPT 275 Phase: Design


Map of design phase

Map of design phase

DESIGN

LOW LEVEL

DESIGN

HIGH LEVEL

DESIGN

Data

Persistance

Subsystem

Classes

Class Interfaces

Interaction Diagrams

Module Interfaces

Modularization

architecture

User Interface

User Manual

Implementation


Implementation issues related to data persistence

Implementation issues related to Data Persistence


Data persistence solution 2

Data Persistence: Solution 2

  • Advantage: can change file format and organization e.g. could use sorted records or B+ Tree rest of software system unaffected by this change

  • How to transform objects into relational records of fixed length

    • Normalization

      • 1st normal form: fixed length records

      • 2nd normal form: remove partial dependencies

      • 3rd normal form: remove transitive dependencies


Why fixed length records

Why fixed length records?

  • Minimizes fragmentation of the file.

    • If record 2 is removed, a unused space in the file will be created

    • Any new record may be put in that unused space, because all records have the same length the new record completely fills the space created by removal of the previous record

    • Fragmentation is not a problem.

Record 1

Record 2

Record 3

Record 4

Record 5

Record 6


Why fixed length records1

Why fixed length records?

  • No need to have special support to find the beginning of the next record.

    • If you know the record length you know where each record begins. Can easily find any record (record n starts a n-1*length)

Record 1

Record 2

Record 3

Record 4

Record 5

Record 6


Without fixed length records

Without fixed length records

  • Fragmentation becomes a problem.

    • If record 2 is removed, a unused space in the file will be created

    • The next new record may be put in that unused space if it is small enough, the new record may not fill the gap. Gaps are created between records → fragmentation

Record 1

Record 2

Record 3

Record 4

Record 5

Record 6


Without fixed length records1

Without fixed length records

  • Special support to find the beginning of the next record.

    • You must keep track of where each record begins and ends. Need a table of contents, or must step through all records

Record 1

Record 2

Record 3

Record 4

Record 5

Record 6


Relational db design

Relational DB Design

  • We will structure our relational database table(s) using Normalization

    • process of assigning attributes to tables

    • series of stages called Normal Forms

      • 1st normal form: fixed length records

      • 2nd normal form: remove partial dependencies

      • 3rd normal form: remove transitive dependencies


Relational db design1

Relational DB Design

  • We will structure our relational database table(s) using Normalization

    • Advantage:

      • assures equal length records

      • reduces data redundancies hence helps eliminate problems that result from redundancies

    • Disadvantage:

      • decrease performance as we normalize to higher forms, higher forms require more tables


An example

An example…

  • To illustrate how to normalize, we shall use the example of a Student Registration System. Here are some requirements:

    • For each student, we need to remember: student-id, name, address, phone, courses

    • For each of the course taken, must remember: credit, semester, grade, room, instructor’s office, instructor.


An example1

An example…

  • To illustrate how to normalize, we shall use the example of a Student Registration System. Here are some requirements:

    • Students can repeat same course in a later semester

    • There is only one offering a a given course in a semester

    • For each of the course attempted, must remember: semester, grade, room, instructor, instructor's office


Oo classes class diagram

OO Classes: Class diagram

Student

Course offering

Course

Student ID

Course name

name

Receives a grade for takes

semester

Course

Name

room

0..*

0..*

0..*

address

1

Instructor

phone

Instructor’s office

credit

List of courses

List of students

List of grades


Our first table

Our First Table

  • From our requirements, we could create the following database table: (horizontal lines separate records, representing single student objects)

Instructor

Std-name

Std-address

Room

Std-phone

Std-id

Coursename

Sem

Grade

Credit

Instructor’s office

Paul K.

Will B.

Kim L.

Xiao T.

Dr. Klaus

M. Nole

V. Karu

W. Loti

Dr. Quel

Dr. Klaus

Dr. Yu

15438

25636

47352

21544

Brook St., Bby

Elf Ave., Van.

Mer Cr., Poco

Alpha St., Bby

AQ2

AQ1

ASB

WM

EDC

AQ1

AQ2

Cmpt 101

Cmpt 150

Bus 152

Engl 102

Biol 234

Cmpt 354

03-2

03-1

03-2

03-1

03-2

03-1

03-2

4

3

3

2

3

3

3

C-

B

A-

A+

B+

D

A-

ASB985

ASB352

WM543

AQ834

EDC243

ASB985

ASB111

294.2563

256.2453

939.2766

295.9976

Each record is uniquely identified by the student number (Std-id). The primary key for the table is therefore Std-id. This table is unnormalized (contains records of varying length)


Our first table is there a problem

student-idnameaddressphonecoursecreditsemestergraderoominstructorinstructor’s office

Group of attributes repeated for each course takenby 1 student

Group of attributes repeated each time a particular course is attempted by 1 student

Our First Table: Is there a problem?

  • So far, attributes are in an unnormalized form.

    • Objects, transformed into DB records, will not all be of same length. Each record contains all information about one student.

NOT ALL RECORDS ARE OF THE SAME LENGTH !!


What is the problem

What is the problem?

  • Problem: attributes that are lists (multiple courses per student, multiple attempts per course) do not produce fixed length records

  • Solution: remove lists by adding additional rows one to hold each attribute in the list

  • Consider the example: for a student, have 1 complete row per course attempted/taken

  • This results in a table in First Normal Form


First normal form

First Normal Form

  • Definition of First Normal Form (1NF):

    • Tables do not have repeating groups, i.e., each row/column intersection can contain one and only one value, not a set of values.

    • All the key attributes are defined, no blank (null) values of keys are permitted


Our example

Our example

Defining primary key attributes

  • Which attributes are needed to assure each record is uniquely identified

    • Std-Id is not enough

      • a student can take multiple courses

    • Std-id and course name is not enough

      • a student can take the same course more than once if they wish

    • Std-id, course name and semester is enough

      • Each time the student takes a course it is uniquely identified as a single record in the table


First normal form of table 1nf

First Normal Form of Table (1NF)

Instructor’s office

Instructor

Std-address

Room

Std-phone

Std-id

Course-name

Semester

Grade

Credit

Std-name

  • Result: Single table with compound (multi-attribute) primary key.

  • Primary Key: each row uniquely identified by one single attribute

  • Compound Primary Key: each row uniquely identify by a or group of attributes

  • The compound primary key for the above table is: Std-id, Course-name, Semester

Paul K.

Paul K.

Paul K.

Will B.

Kim L.

Xiao T.Xiao T.

Dr. Klaus

M. Nole

V. Karu

W. Loti

Dr. Quel

Dr. Klaus

Dr. Yu

15438

15438

15438

25636

47352

21544

21544

Brook St., Bby

Brook St., Bby

Brook St., Bby

Elf Ave., Van.

Merry Cr., Poco

Alpha St., BBY

Alpha St., Bby

AQ2

AQ1

ASB

WM

EDC

AQ1

AQ2

Cmpt 101

Cmpt 150

Bus 152

Engl 102

Biol 234

Cmpt 354

Cmpt 354

03-2

03-1

03-2

03-1

03-2

03-1

03-2

4

3

3

2

3

3

3

C-

B

A-

A+

B+

D

A-

ASB985

ASB352

WM543

AQ834

EDC243

ASB 985

ASB111

294.2563

294.2563

294.2563

256.2453

939.2766

295.9976

295-9976


Is there still a problem

Is there still a problem?

Yes!

  • Our table in First Normal Form could still contain data redundancies due to partial dependencies.

  • Partial dependencies are based only on a part of the compound primary key.

    • Consider an attribute A, that is dependent on the compound primary key K

      • If A is dependent on all components of the compound primary key the A is fully dependent on K

      • If A is dependent on some but not all of the components of the primary key then A is partially dependent on K


Redundancy examples problems

Redundancy: Examples + problems

  • Examples of redundancy and partial dependence:

    • For each course a student takes the student’s name, address and phone number are repeated. A student’s name and address are dependent on the student’s id but not on the course name or semester

    • For each course a student repeats the course credit is repeated. The course credit is dependent on the course name but not the student’s id or the semester


Problems related to redundancy

Problems related to Redundancy

  • Redundancy

    • Insert anomalies: e.g. Each time a student takes a course the student information must be entered, this adds to the potential for error

    • Delete anomalies: if delete the row where info about Std-id 47352 is stored will also delete info that cannot be found anywhere else in DB table namely that Dr. Quel’s office is EDC243

    • Update problems: because of redundant data, if a student moves, need to change student's address in all rows corresponding to every instance of every course the student had ever taken. Problems occur if one occurrence is missed or an error is made in one occurrence


Partial dependencies

Partial Dependencies

  • Definition: non-key attribute(s) dependent on only some of primary key(s)

  • Examples:

    • Phone #, Std-name, and address depend only on Std-id (not course name or semester)

    • Credit depends only on course name (not on Std-id or semester)

    • Instructor, Instructor’s office, room, and grade depend on course and semester (not Std-id)


Partial dependencies1

Partial Dependencies

  • Definition: non-key attribute(s) dependent on only some of primary key(s)

  • When an attribute is only partially dependent on the primary keys of the table there may be redundant occurrences of that attribute in the table

  • Therefore, To remove redundancies we should remove partial dependencies


Problem with 1nf

Problem with 1NF

  • non-key attribute(s) may depend on some but not all of the primary key(s)

  • e.g.: primary keys are Std_id, Course-name and semester

    address depends only on Std-id


From 1nf to 2nf

From 1NF to 2NF

  • Solution: Remove partial dependencies

    • Determine if there are any partial dependencies.

    • If so, divide 1NF table into several tables such that in each table each non-primary key attribute is dependent on only the primary key (or compound primary key) of that table.

    • Note that if primary key of 1NF table is not a compound primary key, there cannot be partial dependencies and hence the 1NF table is already in 2NF.


Second normal form example

Second Normal Form - Example

Transform our 1NF table into a 2NF table

  • STEP 1: We determine dependencies on single primary key:

    • Std-id

      Phone #, Std-name, Std-address

    • Course-name

      credit

    • Semester

      none dependent only on semester


2nf example step 1

2NF Example - Step 1

  • DB tables look like:

Course Table

Student Table

Course-name credit

Std-id Std-name Std-address Std-phone

Cmpt 101 4

15438 Paul K. Brook St. Bby 294.2563

25636 Will B. Elf Ave., Van. 256.2453

Cmpt 150 3

47352 Kim L. Merry Cr., Poco 939.2766

Bus 152 3

21544 Xiao T. Alpha St., Bby 295.9976

Engl 102 2

Biol 234 3

Cmpt 354 3


Second normal form example1

Second Normal Form - Example

  • STEP 2: We determine dependencies on pairs of primary keys:

    • Course-name + Semester

      room, instructor, instructor’s office

    • Course-name + Std-id

      none

    • Semester + Std-id

      none


2nf example step 2

2NF Example - Step 2

Course Offering Table

Course-nameSemester Room Instructor Instructor’s office

Cmpt 10103-2 AQ2 Dr. Klaus ASB985

Cmpt 150 03-1 AQ1 M. Nole ASB352

Bus 152 03-2 ASB V. Karu WM543

Engl 102 03-1 WM W. Loti AQ834

Biol 234 03-2 EDC Dr. Quel EDC243

Cmpt 354 03-1 AQ1 Dr. Klaus ASB985

Cmpt 354 03-2 AQ2 Dr. Yu ASB111


Second normal form example2

Second Normal Form - Example

Step 3

  • We determine dependencies on whole compound primary key:

    • Course-name + Semester + Std-id

      grade


2nf example step 3

2NF Example - Step 3

Student Registration Table

Std-id

Course-name

Grade

Semester

03-2

03-1

03-2

03-1

03-2

03-1

03-2

C-

B

A-

A+

B+

D

A-

15438

15438

15438

25636

47352

21544

21544

Cmpt 101

Cmpt 150

Bus 152

Engl 102

Biol 234

Cmpt 354

Cmpt 354


2nf example alternate step 3 1

2NF Example, alternate Step 3-1

Introducing Association

  • Looking at the data model (class diagram), we can recognize the “many-to-many” multiplicity relationship between Student, grade and CourseOffering

Student

Course offering

Course

Student ID

Course name

Course

Name

Receives a grade for takes

name

semester

0..*

1

0..*

0..*

List of grades

address

credit

room

phone

Instructor

List of courses

Instructor’s office

List of students

  • These 3 attributes are used to implement “many-to-many” multiplicity relationships


Association class

Association Class

  • However, this data model does not lead to DB tables with records of fixed length because these 3 attributes are of varying size for each object of Student and Course Offering class types, so…

  • … we introduce yet another “class” that associates 1 student to many attempts at (registrations to) one course and 1 course to many attempts (registrations) per 1 student. For each of these attempts there is one grade


Association class1

Association Class

  • An association class takes a many-many relation and breaks it into two 1-many relationships

    • Student and Student Registration have a “1-to-many” multiplicity relationship

    • Course Offering and Student Registration have a “1-to-many” multiplicity relationship

  • The association class will contain the attributes that are lists (that cause the many to may relationship)

  • The association class wil contain the attributes that depend upon all the variables (lists) in the association class.


2nf example alternate step 3 2

2NF Example, alternate Step 3 - 2

  • This relationship can be broken down into 2 “1-to-many” multiplicity relationships by creating an association class Student-Registration

Student

Course offering

Course

1

Course name

Student ID

Course

Name

name

semester

0..*

address

room

credit

Student Registration

phone

Instructor

Instructor’s office

Student ID

1

Course name

1

0..*

semester

0..*

grade


2nf example alternate step 3 3

2NF Example, alternate Step 3 - 3

Course Offering Table

Course-name Semester Room Instructor Instructor’s office

  • We can therefore store the attributes that depend on this association into a Student Registration table. The compound primary key of this table is the union of the primary keys of the Student and the Course Offering tables:

Cmpt 10103-2 AQ2 Dr. Klaus ASB985

Cmpt 150 03-1 AQ1 M. Nole ASB352

Bus 152 03-2 ASB V. Karu WM543

Engl 102 03-1 WM W. Loti AQ834

Biol 234 03-2 EDC Dr. Quel EDC243

Cmpt 354 03-1 AQ1 Dr. Klaus ASB985

Cmpt 354 03-2 AQ2 Dr. Yu ASB111

Student Registration Table

Std-id

Course-name

Grade

Semester

Course Table

Student Table

Course-name credit

Std-id Std-name Std-address Std-phone

03-2

03-1

03-2

03-1

03-2

03-1

03-2

Cmpt 101

Cmpt 150

Bus 152

Engl 102

Biol 234

Cmpt 354

Cmpt 354

15438

15438

15438

25636

47352

21544

21544

C-

B

A-

A+

B+

D

A-

Cmpt 101 4

25636 Will B. Elf Ave., Van. 256.2453

Cmpt 150 3

47352 Kim L. Merry Cr., Poco 939.2766

Bus 152 3

21544 Xiao T. Alpha St., Bby 295.9976

Engl 102 2

Biol 234 3

Cmpt 354 3


Second normal form

Second Normal Form

  • To get Student Registration System in 2NF we need 4 tables (files)  

    • Multiplicities come from our Requirement Analysis phase

    • With this 2NF DB, students do not have to register to a course to be admitted to an institution

    • Room and instructor for a course offering can be entered even if there are no students registered yet

    • Less redundancy:Most update problems have been eliminated, but we can still have multiple occurrences of instructor and instructor’s office


Second normal form1

Second Normal Form

  • Definition of 2NF:

    • The table is in 1NF

    • The table includes no partial dependencies


Is there still a problem1

Is there still a problem?

Yes!

  • Our tables in 2NF could still contains data redundancies due to transitive dependencies.

  • When one non-primary key attribute is dependent on another non-primary key attribute, the second non-primary key attribute is transitively dependent on the first non-primary key attribute.


Transitive dependencies example

Transitive Dependencies: example

  • instructor’s office (non-primary key attribute) is transitively dependent on instructor (another non-primary key attribute) but not on any of the primary key attributes for that particular table (course and/or semester)

  • Solution: Conversion from 2NF to 3NF

    • Determine the transitive dependencies.

    • Split 2NF table containing the transitive dependency such that the dependency is represented by its own table.


3nf example

3NF Example

Course Offering Table

Instructor Table

Course Table

Course-name Semester Room Instructor

Instructor Instructor’s office

Course-name credit

Cmpt 10103-2 AQ2 Dr. Klaus

Cmpt 101 4

Dr. Klaus ASB985

Cmpt 150 03-1 AQ1 M. Nole

Cmpt 150 3

M. Nole ASB352

V. Karu WM543

Bus 152 3

Bus 152 03-2 ASB V. Karu

W. Loti AQ834

Engl 102 2

Engl 102 03-1 WM W. Loti

Dr. Quel EDC243

Biol 234 3

Dr. Yu ASB111

Biol 234 03-2 EDC Dr. Quel

Cmpt 354 3

Cmpt 354 03-1 AQ1 Dr. Klaus

Cmpt 354 03-2 AQ2 Dr. Yu

Student Registration Table

Std-id

Course-name

Grade

Semester

Student Table

Std-id Std-name Std-address Std-phone

03-2

03-1

03-2

03-1

03-2

03-1

03-2

Cmpt 101

Cmpt 150

Bus 152

Engl 102

Biol 234

Cmpt 354

Cmpt 354

15438

15438

15438

25636

47352

21544

21544

C-

B

A-

A+

B+

D

A-

25636 Will B. Elf Ave., Van. 256.2453

47352 Kim L. Merry Cr., Poco 939.2766

21544 Xiao T. Alpha St., Bby 295.9976


Third normal form

Third Normal Form

  • Definition:

    • Every table is in 2NF.

    • There are no transitive dependencies.


Normalization summary

Normalization Summary

  • When normalizing, we seek to make sure that attributes depend

    • on the key (1NF)

    • on the whole key (2NF)

    • on nothing but the key (3NF)

  • When normalized:

    • records have fixed length

    • no insert/delete/update anomalies

    • minimize redundancy


  • Login