DBMS with SQL

profiledesg
lecture_07_s12014.zip
Home>Computer Science homework help>DBMS with SQL

Lecture_07_S12014.ppt

Welcome to

CIS 2002: DATABASE DESIGN AND IMPLEMENTATION
Semester 1, 2014 – LECTURE 7

*

*

*

USAGE OF SLIDES

Use of these lecture slides is restricted to teaching staff and students enrolled in this course. All students who use these slides should have acquired the prescribed texts.

These slides are for the personal use of students on the Study Desk only.

Students should not copy slides, allow third parties access to the slides or distribute the slides.

Copyright of these slides vests in the university and, where applicable, Cengage Education or Pearson (publishers of the textbooks)

*

*

READINGS – WEEK 9

  • Study Book, Module 7

*

*

TERNARY RELATIONSHIPS

  • We have concentrated on analysing the relationship between two entities so far
  • Sometimes, three (or more) entities can be involved in a relationship which require careful analysis
  • Ternary relationships involve three entities.

*

*

TERNARY RELATIONSHIPS

  • Consider the business rules:
  • An employee may have one or more skills and a skill may belong to many employees
  • An employee may work on one or more projects and a project may require many employees
  • A project requires one or many skills to complete and a skill may be required in several projects.
  • We need to store information about which skills each employee uses on each project

*

*

TERNARY RELATIONSHIPS

*

*

EMPLOYEE

SKILL

PROJECT

EMPLOYEE(empid#,name,date started)

SKILL(skillid#,skill name)

PROJECT(projectid#,name,loc,budget)

TERNARY RELATIONSHIPS

  • The ring structure is called a closed relationship which may indicate redundancy.

  • When this happens, we resolve the three M:M relationships with a single intersecting entity in the middle tying in all the other entities.

*

*

TERNARY RELATIONSHIPS

*

*

EMPLOYEE

SKILL

PROJECT

SKILL USAGE

EMPLOYEE(empid#,name, date started)

SKILL(skillid#,skill name)

PROJECT(projectid#,name, loc, budget)

SKILL USAGE(empid#, skillid#, projectid#)

SKILL USAGE stores information about each SKILL an EMPLOYEE uses in each PROJECT.

QUATERNARY RELATIONSHIPS?

  • Similar to ternary relationship but four entities are linked via a single intersection entity – rare indeed!

*

*

NORMALISATION

  • The process of organising relational database in order to minimise redundancy and dependencies.

  • Involves splitting larger relations (or tables) into smaller and less redundant tables and defining relationships between them.
  • First Normal Form (1NF)
  • Second Normal Form (2NF)
  • Third Normal Form (3NF)
  • Boyce-Codd Normal Form (BCNF)
  • Fourth Normal Form (4NF)
  • Fifth Normal Form (5NF), …..

  • A relational database is considered “normalised” when it is in 3NF!

*

*

REDUNDANCY

  • Refers to unnecessary data storage or duplication of storage

  • Best done before relations are derived

*

*

REDUNDANT ENTITIES

  • Sometimes system is poorly understood, especially during early phases of analysis
  • Cases of redundant entities:
  • Different users may have different names for the same entity
  • Two entities have so much in common that they are really one

*

*

REDUNDANT ATTRIBUTES

  • When unwanted attributes are present in a relation
  • Occurs most often when a sub-type is required but not implemented

*

*

DERIVED VALUES

  • Occurs when the value in a column can be calculated using values from other columns
  • Example:
  • “Age” column will have derived values if there is already a column for “date of birth”
  • “total” column will have derived values if it is multiplication of the values from “rate” column and “quantity” column.
  • We should avoid storing derived values
  • However, we store them in certain cases to solve performance problems or when we wish to query the column frequently

*

*

REDUNDANT RELATIONSHIPS

  • When unwanted relationships are created between relations
  • Most common in closed loops
  • Example: If we resolved a ternary relationship using three intersection entities rather than one

*

*

The DATABASE DESIGN PROCESS

AN EMPLOYEE WORKS FOR ONE COMPANY AND IS ASSIGNED TO ONE DEPARTMENT. A DEPARTMENT MAY HAVE MANY EMPLOYEES.

For EMPLOYEE, we store EMPID, NAME, SKILLTYPE,SKILLNAME and MULTIPLE ADRESSES, (e.g. home, mailing).

For DEPARTMENT, we store DEPTID and NAME.

For COMPANY, we store COMPANYNAME.

*

*

  • STEP 1: DRAW DATA MODEL (ER DIAGRAM)

( I will not do this in detail here – YOU ARE ALL EXPERTS)

*

*

The DATABASE DESIGN PROCESS

  • STEP 2: DERIVE RELATIONS / ENTITY LIST

COMPANY(companyname#)

DEPARTMENT(deptid#,name)

EMPLOYEE(empid#,name,skilltype,skillname, ((address)),companyname#,deptid#)

*

*

The DATABASE DESIGN PROCESS

  • Step 3: Normalise relations.

  • This is our task to learn this week!

  • After the top-down modelling process, we perform a bottom-up check on our work using Normalisation. Often, we find that there are potential problems.

  • The most dangerous problems are called redundancy and anomalies.

*

*

The DATABASE DESIGN PROCESS

Anomalies

Anomalies are irregularities in the database design.

Consider the following relation:

ACTIVITY(studno#, activity, fee)

100 Skiing 200

150 Swimming 50

175 Squash 50

200 Swimming 50

  • Consider:
  • delete stud# 100

*

*

*

3

*

*

Anomalies

ACTIVITY(studno#, activity, fee)

100 Skiing 200

150 Swimming 50

175 Squash 50

200 Swimming 50

  • Consider:
  • delete stud# 100 (loss of information about “skiing”)

*

*

*

Anomalies

ACTIVITY(studno#, activity, fee)

100 Skiing 200

150 Swimming 50

175 Squash 50

200 Swimming 50

Scuba Diving 150

  • Consider:
  • delete stud# 100 (loss of information about skiing)
  • insert “Scuba Diving” activity and its corresponding fee as $150

CAN THIS BE DONE?

*

*

*

Anomalies

ACTIVITY(studno#, activity, fee)

100 Skiing 200

150 Swimming 50

175 Squash 50

200 Swimming 50

  • Consider:
  • delete stud# 100 (loss of information about skiing)
  • insert “Scuba Diving” activity with $150 fee (can’t insert until at least a student takes up scuba diving activity)

*

*

*

Anomalies

ACTIVITY(studno#, activity, fee)

100 Skiing 200

150 Swimming 50

175 Squash 50

200 Swimming 60

  • Consider:
  • delete stud# 100 (loss of information about skiing)
  • insert “Scuba Diving” activity with $150 fee (can’t insert until at least a student takes up scuba diving activity)
  • update swimming cost from $50 to $60

*

*

*

Anomalies

ACTIVITY(studno#, activity, fee)

100 Skiing 200

150 Swimming 50

175 Squash 50

200 Swimming 60

  • Consider:
  • delete stud# 100 (loss of information about skiing)
  • insert “Scuba Diving” activity with $150 fee (can’t insert until at least a student takes up scuba diving activity)
  • update swimming cost from $50 to $60 – if it is only updated for studno# 200 (inconsistent results since its not updated for studno# 150)

*

Solution

  • The problem is that the ACTIVITY relation has information about the activity and student together
  • Solution: split ACTIVITY into 2 tables!

STUDENT_ACTIVITY (studno#, activity#)

100 Skiing

150 Swimming

175 Squash

200 Swimming

ACTIVITY (activity#, cost)

Skiing 200

Swimming 50

Squash 50

*

*

*

9

A BETTER Solution!

  • Split ACTIVITY into 3 tables!

STUDENT(studno#)

STUDENT_ACTIVITY (studno#, activity#)

ACTIVITY (activity#, cost)

*

*

*

9

REDUNDANCY

  • Consider the following relations:

  • INVOICE(invno#,custname,custaddress, custtelephone)
  • PAYMENT(payno#,custname,custaddress,custtelephone)

  • Typical of older (legacy) systems.

  • Can you spot any problems?

*

*

REDUNDANCY

  • When we create invoice records we store customer details.
  • When we create payment records we store customer details AGAIN.

  • Details for a customer may be stored many times – redundency
  • Details may be inconsistent – integrity problem

*

*

REDUNDANCY

  • SOLUTION: Again split the table!

CUSTOMER(custid#,custname, custtelephone)

INVOICE(invno#,custid#)

PAYMENT(payno#,custid#)

*

*

Purpose of Normalisation

  • Create a structure for storing data such that all rules and constraints can be enforced
  • remove uncontrolled redundancy
  • remove unwanted dependencies

*

*

*

10

Functional Dependencies

  • Think of all the attributes of a relation.
  • A Functional Dependency exists when: if we know the value of one attribute, we also know the value of another attribute.
  • Example:

If we know the course number then we should also obtain the course name. This means:

  • course name is functionally dependent on course number
  • for each course number there is one course name
  • All attributes in a relation are functionally dependent on the primary key!

*

*

*

11

FIRST NORMAL FORM (1NF)

  • Objective: Select the primary key, remove or mark derived attributes and remove repeating groups
  • A derived attribute is one whose value can be calculated using other attributes.
  • You can mark derived attributes using { } brackets
  • A repeating group is a single attribute (or groups of attributes) which occur more than once for each instance of the primary key
  • The notation for a repeating group is double brackets e.g. (( ))

*

*

1NF Example

STUDENT (studno, name, address, courseno, coursedescr, ((unitno, unitname, grade)) , no of units completed)

STEP 1: Select the primary key.

All other attributes must be functionally dependent upon primary key.

studno# - is the primary key.

STEP 2. Remove or mark derived attributes whose values are derived (if any).

{no of units completed} – is a derived attribute.

STEP 3: Identify and remove repeating groups.

*

*

1NF Example – STEP 3

  • A relation is in 1NF if it has no repeating groups

  • STUDENT (studno#, name, address courseno, coursedescr ((unitno, unitname,grade)), {no of units completed})

  • STEP 3:Identify and remove repeating groups.
  • Identify primary key within the repeating group: unitno
  • Split into two relations:

STUDENT(studno#, name, address, courseno, coursedescr)

RESULT(studno#, unitno#, unitname, grade)

  • Repeating group requires composite (or concatenated) key.

*

*

*

13

  • Consider relation in 1NF:

RESULT(studno#, unitno#, unitname, grade)

100 721 Intro A

100 704 Acc B

100 702 Econ HD

102 721 Intro IS

  • Still some redundancy:
  • Information about Unit is repeating.
  • Still some anomalies:
  • Inserting a new unit ?
  • Updating a unit ?
  • Deleting a unit ?

*

*

Problems with 1NF

*

14

RESULT(studno#, unitno#, unitname, grade)

Cause of the problem:

  • Some attributes are functionally dependent on only part of the primary key – this can happen only in case of composite key
  • unitname is only functionally dependent on unitno# and not on studno# - so unitname is only partially dependent on the primary key (studno#, unitno#)

Solution:

  • Second Normal Form (2NF): remove partial dependencies

*

*

Problems with 1NF - Cause

*

15

SECOND NORMAL FORM (2NF)

  • Objective: Remove partial dependencies

  • Partial dependency: Where an attribute is dependent upon only part of a composite key
  • To be in 2NF, each non-key attribute must be functionally dependent on the WHOLE key.

  • QUESTION: If a relation has a non-composite key (i.e. only ONE attribute as primary key); is it automatically in 2NF?

*

*

2NF: ANSWER

  • YES – if a relation does NOT have a composite key, you do not need to check for 2NF.
  • A relation with a unitary (non-composite) key is already in 2NF because there is no issue of partial dependencies.

  • HINT: The 2NF problem is most likely in the relations which have been removed (the repeating groups) during 1NF.

*

*

Second Normal Form (2NF)

RESULT(studno#, unitno#, unitname, grade)

UNIT(unitno#, unitname)

721 Intro

704 Acc

702 Econ

RESULT(studno#, unit#, grade)

100 721 A

100 704 B

100 702 F

102 721 IS

*

*

*

16

Second Normal Form (2NF): RULES

  • If relation is in first normal form (1NF) and has no partial dependencies then it is in second normal form (2NF)
  • Each attribute in relation must be functionally dependent on the whole key.

*

*

*

17

  • Consider:

STUDENT(studno#, studname,address, courseno, coursedesc)

This relation is in second normal form (2NF) but still has problems:

  • Insert new course ?
  • Update a course ?
  • Delete a course ?

*

*

Problems with 2NF

*

18

STUDENT(studno#, studname,address, courseno, coursedesc)

Cause of the problem:

  • Some attributes are functionally dependent on another non-key attribute and hence only indirectly dependent on the primary key, in other words, transitive dependency.
  • coursedesc is only functionally dependent on studno# THROUGH courseno and not directly - so coursedesc is only transitively dependent on the primary key (studno#)

Solution:

  • Third Normal Form (3NF): remove transitive dependencies

*

*

Problems with 2NF - Cause

THIRD NORMAL FORM (3NF)

  • Objective: Remove transitive dependencies.

  • How to look for 3NF problems?
  • This occurs when there is dependency between non-key attributes, hence examine the relationships between the attributes that are not the primary key.

*

*

Third Normal Form (3NF)

STUDENT(Studno#, studname, address, courseno, coursedesc)

  • Remove transitive dependencies
  • coursedesc is transitively dependent on studno# via courseno
  • Split into two relations:

STUDENT(studno#, studname, address, courseno#)

COURSE(courseno#, coursedesc)

  • Notice the foreign key in the STUDENT relation.

*

*

*

19

*

*

Third Normal Form (3NF): RULES

  • If relation is in second normal form (2NF) and has no transitive dependencies, then it is in third normal form (3NF)

  • Each attribute in a relation must NOT be functionally dependent on any other non-key attribute in the relation.

*

Normalisation Summary

  • First Normal Form (1NF)
  • Assign the Primary Key (PK)
  • Remove or mark derived attributes with { }
  • Remove repeating groups (PK of new relation is usually concatenation of PK of original relation + unique identifier of the repeating group)
  • Second Normal Form (2NF)
  • 1NF relations without composite PK are automatically 2NF
  • For composite PKs, Remove partial dependencies (PK of new relation will usually be a part of original composite PK)
  • Third Normal Form (3NF)
  • Remove transitive dependencies (PK of new relation will be a unique identifier for the transitively dependent group of attributes and will also appear in the original relation as a foreign key)

*

*

Normalisation Example Review

Unnormalised Relation (0NF)

STUDENT (studno, name, address, courseno, coursedescr, ((unitno, unitname, grade)) , no of units completed)

3NF

STUDENT(studno#, name, address, courseno#)

RESULT(studno#, unitno#, grade)

UNIT(unitno#, unitname)

COURSE(courseno#, coursedescr)

*

*

3NF: Example 2

HOUSING(studno#, building, fee)

100 Randolph 300

150 Ingersoll 250

200 Randolph 300

  • This relation is in second normal form (2NF) but not in third normal form (3NF)
  • There is transitive dependency between fee and studno# through building.

*

*

*

21

3NF Example 2: Solution

HOUSING(studno#, building, fee)

100 Randolph 300

150 Ingersoll 250

200 Randolph 300

3NF

HOUSING(studno#, building#)

100 Randolph

150 Ingersoll

200 Randolph

BUILDING (building#, fee)

Randolph 300

Ingersoll 250

*

*

*

22

Another Normalisation Exercise

  • Consider the unnormalised relation (0NF)

STUDENT(sid, name, ((dept id, dept name)), major id, major name, date of birth, age)

*

*

Normalisation Example

  • Consider the relation

STUDENT(sid,name,((dept id, dept name)),major id, major name,DOB,age)

1NF

Choose PK – sid#

Remove derived attributes – age

Resolve repeating groups

*

*

1NF

0NF:

STUDENT(sid,name,((dept id,dept name)), major id, major name, date of birth, age)

1NF:

STUDENT(sid#,name, major id, major name, date of birth,{age})

STUDENT_DEPT(sid#, dept id#, dept name)

*

*

2NF

1NF:

STUDENT(sid#,name, major id, major name, date of birth,{age})

STUDENT_DEPT(sid#, dept id#, dept name)

2NF:

STUDENT(sid#, name, major id, major name, date of birth,{age})

STUDENT_DEPT(sid#, dept id#)

DEPARTMENT(dept id#,dept name)

*

*

3NF

2NF:

STUDENT(sid#, name, major id, major name, date of birth,{age})

STUDENT_DEPT(sid#, dept id#)

DEPARTMENT(dept id#,dept name)

3NF:

STUDENT(sid#,name, date of birth, {age}, major id#)

STUDENT_DEPT(sid#, dept id#)

DEPARTMENT(dept id#, dept name)

MAJOR(major id#, major name)

*

*

EMBEDDED REPEATING GROUP

  • TICKET(ticket id, name, ((route id, name, route cost, price charged, ((sector id, sector name, sector distance )) )), status id, status description)

  • Here we have a repeating group within a repeating group

  • A ticket may have many different routes and a route may have many sectors. We must retain historical details for the price charged (by route) for each ticket.

*

*

FIRST NORMAL FORM (1NF)

  • 0NF:
  • TICKET(ticket id, name, ((route id, name, route cost, price charged, ((sector id, sector name, sector distance )) )), status id, status description)

  • 1NF:
  • TICKET (ticket id#, passenger name, status id, status description)
  • TICKET_ROUTE (ticket id#, route id#, route name, route cost, price charged)
  • TICKET_ROUTE_SECTOR (ticket id#, route id#, sector id#, sector name, sector distance)

*

*

SECOND NORMAL FORM (2NF)

  • 1NF:
  • TICKET (ticket id#, passenger name, status id, status description)
  • TICKET_ROUTE (ticket id#, route id#, route name, route cost, price charged)
  • TICKET_ROUTE_SECTOR (ticket id#, route id#, sector id#, sector name, sector distance)

  • 2NF:
  • TICKET(ticket id#, passenger name, status id, status description)
  • TICKET_ROUTE(ticket id#, route id#, price charged)
  • ROUTE(route id#, route name, route cost)
  • TICKET_ROUTE_SECTOR (ticket id#, route id#, sector id#)
  • SECTOR(sector id#, sector name, sector distance)

*

*

THIRD NORMAL FORM (3NF)

  • 2NF:
  • TICKET(ticket id#, passenger name, status id, status description)
  • TICKET_ROUTE(ticket id#, route id#, price charged)
  • ROUTE(route id#, route name, route cost)
  • TICKET_ROUTE_SECTOR (ticket id#, route id#, sector id#)
  • SECTOR(sector id#, sector name, sector distance)

  • 3NF:
  • TICKET(ticket id#, passenger name, status id#)
  • TICKET_ROUTE(ticket id#, route id#, price charged)
  • ROUTE(route id#, route name, route cost)
  • TICKET_ROUTE_SECTOR (ticket id#, route id#, sector id#)
  • SECTOR(sector id#, sector name, sector distance)
  • STATUS(status id#, status description)

What if there was no “price charged” in TICKET_ROUTE relation ?

*

*

3NF – if no “price charged” attribute

  • TICKET(ticket id#, passenger name, status id#)
  • ROUTE(route id#, route name, route cost)
  • TICKET_ROUTE_SECTOR (ticket id#, route id#, sector id#)
  • SECTOR(sector id#, sector name, sector distance)
  • STATUS(status id#, status description)

  • No need to implement TICKET_ROUTE relation now. The relation TICKET_ROUTE_SECTOR also tells us which routes exist for a ticket. The attribute “price charged” was the only reason for retaining TICKET_ROUTE relation.

*

*