Compiled Contents of:Week 5 Logical Design and Normalisation

 

Content File: Topic 5 Objectives


Topic 5 Objectives

Please note this week we will be covering two topics (Topic 5 & 6) to help with preparation for assignment 1. In last weeks study we have already introduced some of the work related to this topic 'logical design' in our use of the Physical ERD.

Topic 5 Objectives

After completing this weeks study students should be able to:

  1. Understand the purpose of logical database design.
  2. Understand the expected inputs and outputs of the logical database design process.
  3. Convert a conceptual design model (Conceptual ER diagram) to a logical design model (Logical ERD or Data Structure Diagram (DSD)).
  4. Convert a Logical ERD (a DSD) into a schema file
  5. Understand the drawing conventions of DSD.
  6. Interpret a DSD.
  7. Understand how business rules are reflected in DSDs.
  8. Understand the purpose of a Data Dictionary.
  9. Create a data dictionary.

 

 

Content File: FIT9999SG5.pdf


FIT9999SG5.pdf
 

Content File: FIT9999t5.pdf


FIT9999t5.pdf
 

Content File: Topic 6 Objectives


Topic 6 Objectives

Topic 6 Objectives

After completing this weeks study students should be able to:

  1. Understand the purpose of normalisation
  2. Understand the problems associated with redundant data
  3. Identify various types of update anomalies such as insertion, deletion, and modification anomalies
  4. Recognise the appropriateness or quality of the design of relations
  5. Identify various types of functional dependencies between attributes
  6. Understand how functional dependencies can be used to group attributes into relations that are in a known normal form
  7. Identify the most commonly used normal forms, namely 1NF, 2NF and 3NF
  8. Perform normalisation
  9. Understand various ways to refine 3NF relations to achieve better database design
  10. Produce an ER diagram from the derived set of 3NF relations

 

 

Content File: FIT9999SG6.pdf


FIT9999SG6.pdf
 

Content File: FIT9999t6.pdf


FIT9999t6.pdf
 

Content File: Week 5 Tutorial - Logical Design & Normalisation


Week 5 Tutorial - Logical Design & Normalisation

Tutorial 5 – Logical Design & Normalisation

Topic 5: Logical Database Design

For each of the following diagrams:

  • transform the Conceptual ERD into a physical ERD (DSD)
  • define the referential integrity rules (update/delete) on the DSD

1.

2. Note in this ERD although a M:N is depicted the 'Completes' relationship has not as yet been depicted as a composite entity.

Convert your physical ERD from this problem into an Oracle Schema file using the SQL Developer GUI

Be sure to turn on 'Advanced Options':

After you have created your tables record the DDL for each table - select the table and then select the SQL tab - create a single schema file containing all the required SQL in the correct order.

3. Note carefully the attributes on the entity CERTIFICATE (which has been flagged as a composite - is it really?)

4.

5.

6.

 

Topic 6: Normalisation

Theory

Reference:

Rob, P. & Coronel, C. Database Systems: Design, Implementation & Management, 6th Edition, 2004. Chapter 5. Review Questions 6

Rob, P. & Coronel, C. Database Systems: Design, Implementation & Management, 7th Edition, 2006. Chapter 5. Review Questions 6

Review Question:

  1. Given the dependency diagram shown in Figure Q5.6, answer Questions 6a through 6c:

    FIGURE Q5.6 The Dependency Diagram for Question 1

  2. a. Identify and discuss each of the indicated dependencies.
  3. b. Create a database whose tables are at least in 2NF, showing the dependency diagrams for each table.
  4. c. Create a database whose tables are at least in 3NF, showing the dependency diagrams for each table

Database Design Exercise

UNITS CURRENTLY APPROVED                                        Date: 29/07/2006
Unit Number Unit Name Unit Description Unit Value
FIT1001 Computer Systems Computer Hardware 6
FIT1002 Computer Programming Introductory Programming 6
FIT1012 Website Authoring Website creation 6
FIT9999 Database Database Concepts 6

* Unit values may be either 3, 6 or 12 points

LECTURER DETAILS                                                                      DATE: 29/07/2006

LECTURER'S NUMBER           10234

LECTURER'S NAME                GUISEPPE BLOGGS

LECTURER'S OFFICE No.      169

LECTURER'S PHONE No.       99047111

UNIT ADVISER FOR:

UNIT NUMBER UNIT NAME
FIT1012 Computer Programming
FIT9999 Database

* A given unit may have several advisers

* Some lecturers share offices, although each have their own phone

STUDENT DETAILS                                                                        DATE: 29/07/2006

STUDENT No.                 12345678

STUDENT NAME          Poindexter Jones

STUDENT ADDRESS    23 Wide Road, Berwick, 3806

COURSE ENROLLED   BITS (MMApps)

MODE OF STUDY         On-Campus

MENTOR NUMBER    10234

MENTOR NAME          Guiseppe Bloggs

ACADEMIC RECORD:

UNIT NUMBER UNIT NAME YEAR / SEMESTER GRADE
FIT1001 Computer Systems 2006/1 D
FIT1002 Computer Programming 2006/1 D

* Grade may have the value N, P, C, D or HD

* Mode of Study must be On-campus (O) or Distance Education (D)

In order to add a student, the lecturer who advises that student must already exist in the database. No lecturer may be deleted who advises any students which are currently in the database. If the lecturer number of a lecturer is changed, then the number would be changed for each student advised by that lecturer.

Prepare:

  1. a database model for this situation as a set of third normal form relations. Normalise each form one at a time and then group together those relations which have the same PK ie. represent the same entity
  2. express this model using a Conceptual ERD
  3. translate the conceptual ERD into a physical ERD (DSD) be sure to include full update/delete documentation
  4. create an Oracle schema file from your ERD in step c. and test the file against the Oracle database. This schema should be hand created do not generate it from the SQL Developer GUI.

Week 5 tutorial as a PDF file

 

Content File: wk5tut.pdf


wk5tut.pdf
 

Content File: Week 5 Tutorial - Logical Design Sample Solution


Week 5 Tutorial - Logical Design Sample Solution

Tutorial 5 – Logical Design Sample Solution

Topic 5: Logical Database Design

For each of the following diagrams:

  • transform the Conceptual ERD into a physical ERD (DSD)
  • define the referential integrity rules (update/delete) on the DSD

1.

2. Note in this ERD although a M:N is depicted the 'Completes' relationship has not as yet been depicted as a composite entity.

Convert your physical ERD from this problem into an Oracle Schema file using the SQL Developer GUI

After you have created your tables record the DDL for each table - select the table and then select the SQL tab - create a single schema file containing all the required SQL in the correct order.

--Completed under LSMI1 username
--REM LSMI1 COURSE
--
--  CREATE TABLE "LSMI1"."COURSE" 
--   (	"COURSE_ID" VARCHAR2(7 BYTE) NOT NULL ENABLE, 
--	"COURSE_TITLE" VARCHAR2(40 BYTE) NOT NULL ENABLE, 
--	 CONSTRAINT "COURSE_PK" PRIMARY KEY ("COURSE_ID") ENABLE
--   ) ;
-- 
--REM LSMI1 EMPLOYEE
--
--  CREATE TABLE "LSMI1"."EMPLOYEE" 
--   (	"EMPLOYEE_ID" NUMBER(5,0) NOT NULL ENABLE, 
--	"EMPLOYEE_NAME" VARCHAR2(40 BYTE) NOT NULL ENABLE, 
--	 CONSTRAINT "EMPLOYEE_PK" PRIMARY KEY ("EMPLOYEE_ID") ENABLE
--   ) ; 
--
--REM LSMI1 COMPLETE
--
--  CREATE TABLE "LSMI1"."COMPLETE" 
--   (	"EMPLOYEE_ID" NUMBER(5,0) NOT NULL ENABLE, 
--	"COURSE_ID" VARCHAR2(7 BYTE) NOT NULL ENABLE, 
--	"DATE_COMPLETED" DATE NOT NULL ENABLE, 
--	 CONSTRAINT "COMPLETE_PK" PRIMARY KEY ("EMPLOYEE_ID", "COURSE_ID", "DATE_COMPLETED") ENABLE, 
--	 CONSTRAINT "COMPLETE_COURSE_FK1" FOREIGN KEY ("COURSE_ID")
--	  REFERENCES "LSMI1"."COURSE" ("COURSE_ID") ENABLE, 
--	 CONSTRAINT "COMPLETE_EMPLOYEE_FK1" FOREIGN KEY ("EMPLOYEE_ID")
--	  REFERENCES "LSMI1"."EMPLOYEE" ("EMPLOYEE_ID") ENABLE
--   ) ;

-- Hand edited version with formatting and no reference to LSMI1 schema
REM COURSE

  CREATE TABLE COURSE 
   (COURSE_ID         VARCHAR2(7 BYTE)  NOT NULL ENABLE, 
	  COURSE_TITLE      VARCHAR2(40 BYTE) NOT NULL ENABLE, 
	  CONSTRAINT COURSE_ PK PRIMARY KEY (COURSE_ID) ENABLE
   );
 
REM EMPLOYEE

  CREATE TABLE EMPLOYEE 
   (EMPLOYEE_ID      NUMBER(5,0)        NOT NULL ENABLE, 
	  EMPLOYEE_NAME    VARCHAR2(40 BYTE)  NOT NULL ENABLE, 
	  CONSTRAINT EMPLOYEE_PK PRIMARY KEY (EMPLOYEE_ID) ENABLE
   ); 

REM LSMI1 COMPLETE

  CREATE TABLE COMPLETE 
   (EMPLOYEE_ID      NUMBER(5,0)        NOT NULL ENABLE, 
	  COURSE_ID        VARCHAR2(7 BYTE)   NOT NULL ENABLE, 
	  DATE_COMPLETED   DATE               NOT NULL ENABLE, 
	  CONSTRAINT COMPLETE_PK PRIMARY KEY (EMPLOYEE_ID, COURSE_ID, DATE_COMPLETED) ENABLE, 
	  CONSTRAINT COMPLETE_COURSE_FK1 FOREIGN KEY (COURSE_ID)
	      REFERENCES COURSE (COURSE_ID) ENABLE, 
	  CONSTRAINT COMPLETE_EMPLOYEE_FK1 FOREIGN KEY (EMPLOYEE_ID)
	      REFERENCES EMPLOYEE (EMPLOYEE_ID) ENABLE
   );

3. Note carefully the attributes on the entity CERTIFICATE (which has been flagged as a composite - is it really?)

No it is not as the entity has its own PK

4.

5.

6.

 

 

Content File: Wk5-normalisation-soln.pdf


Wk5-normalisation-soln.pdf