Question 1
Given a sample Magazine Subscription form.
POP MAZ MAGAZINE
Customer Account No: S00256-01 Date: 10/09/2006
Customer Name: Charles Tham
Customer Address: 153, Jensen Street, KL
Email Address: Charles@yahoo.com
Magazine Code Magazine Start Date End Date
description
C001 Home Design 1/12/2006 31/12/2006
C002 Fashion Week 1/2/2006 7/2/2006
D235 Mum & Child 18/6/2006 31/7/2006
E123 Health Today 1/9/2006 31/10/2006
C132 Contemporary 1/12/2006 28/2/2007
Cakes
Staff Id: J004
Name : Ms.Julie
Contact Number : 03-25145879
(a) Design an appropriate set of normalized relation to represent the Magazine Subscription
Information .Decompose the following relation to First Normal Form (1NF), Second
Normal Form (2NF) and Third Normal Form (3NF).
(16 marks)
1NF
CUSTOMER(Customer Account No,dateCustomer Name: ,ustomer Address,
Email Address, Staff Id,Name ,Contact Number )
MAG_SUBSCRIBED(Customer Account No ,Magazine Code, Magazine description ,
,Start Date End Date)
2NF
(b) Based from the Magazine Subscription Information above ,describe
THREE (3) different types of anomalies that could occur in an
unnormalized table? (9 marks)
UNF – PG_INFO TABLE
Player# Player Contact Address Game Game venue Time Day trainer# Trainer name
Name Id Description
P001 Rina 068877 55
INF(FIRST NORMAL FORM) – REMOVE REPEATING GROUP
PLAYER(PLAYERID,PNAME,CONTACT,ADDRESS,TRAINER_ID, TRAINER_NAME) – ORI TABLE
GAME-PLAYED(PLAYER ID,GAMEid,Gamedesc,venue,time,day) – NEW TABLE
P001. G22
P001,G23
2NF (REMOVE FUNCTIONAL DEPENDENCY/REM PARTIAL DEPENDENCY) -
PLAYER(PLAYERID,PNAME,CONTACT,ADDRESS,TRAINER_ID, TRAINER_NAME)
GAME-PLAYED(PLAYER ID,GAMEid,Time,day)
GAME(GAMEid,Gamedesc,VANUE)
ALL THE ABOVE IN 2ND. NF IS 100% DEPENDENT ON PK, NO PARTIAL DEP
3NF (REMOVE TRANSITIVE DEPENDENCY) – REMOVE NON KEY DEPENDENT ON ANOTHER NON
KEY
PG_INFO (PLAYERID, PNAME, CONTACT,ADDRESS,TRAINERID*)
TRAINER (TRAINER_ID, TRAINER_NAME)
GAME-PLAYED (PLAYER ID*, GAMEid*,Time,day)
GAME(GAMEid,Gamedesc,VANUE)
The above table in unnormalized form
Player_Game_info(playerid,name,cont,addr,gameid,desc,value………………….)
What is a referential integrity constraint? (3 marks)
d) With respect to referential integrity rules, what is an insertion rule? Use an
example table to explain and illustrate your answer ( 5 marks)
Question 2
Given a sample Player details form for a Sports Recreation Club. This form use to record down the
details of players and the games played ,together with their payment details.
Player Details Form
Player #: P0001 Contact number: 0167676543
Address: 25 Mount Rd.Charles Street UAE
Player Name: Rina
Game Id Game Venue Time Day Trainer Name
Description #
G001 Tennis Court A 9:00 am Mon T044 Mr.Henry
G002 Badminton Court C 5:00 pm Wed T121 Mr.Tan
G235 Chess East 2:30 pm Sat T343 Ms.Jenn
Room
G123 Swimming Pool 1 6:00 pm Sat T234 Mr.Ray
***********************PAYMENT DETAILS*****************************
Payment Ref No : R044 Payment ty pe :Cash/Credit Card
Payment Date :01st.February 2010 Amount Paid : RM 250.00
Figure 1 : Player Detail Form
INF
PLAYER(PLAYERID,PNAME,NUM,ADDR,PAYMENT REFNO.PAYMENT
DATE,TYPE,AMOUNT)
GAMEPLAYED(PLAYERID,GAMEID,DESC,VANUE,TIME,DAY,TRAINERID,NAME)
2NF
PLAYER(PLAYERID,PNAME,NUM,ADDR,PAYMENT REFNO.PAYMENT
DATE,TYPE,AMOUNT)
GAMEPLAYED(PLAYERID,GAMEID,TIME,DAY,TRAINERID,TRAINERNAME)
GAME(GAMEID,DESC,VANUE)
3NF
PLAYER(PLAYERID,PNAME,NUM,ADDR,PAYMENT REFNO*)
PAYMENT(PAYMENT REFNO.PAYMENT DATE,TYPE,AMOUNT)
GAMEPLAYED(PLAYERID,GAMEID,TIME,DAY,TRAINERID*)
TRAINER(TRAINERID,TRAINERNAME)
GAME(GAMEID,DESC,VANUE)
TIME, DAY CAN BE IN GAME TABLE IF YOU WANTTO FIX THE TIME IN EACH GAME
I HAVE STATED THE TIME AND DAY IN GAME PLAYED ENTITY BECAUSE I ASSUME
TIME IS CHOSEN BY PLAYER CONVINIENCE
QUESTION
UNNORMALISED(UNF)
Student(Sid,name,tel,addr,email,
{subcode,desc,credit hr,mark,grade,remark,}
ExamOfficerid,name,ext)
Student Details Form
Studentr #: P0001 Contact number: 0167676543
Address: 25 Mount Rd.Charles Street UAE
Student Name: Rina
Subject code Subject Credit hr mark grade remark#
Description
G001 SYSTEM 3 60 C
DESIGN
G002 ORG 5 76 B+
BEHAVOIUR
G235 MATHS 4 54 C- SEE
MS.LIM
G123 ENGLISH 3 89 A
***********************EXAM OFFCIE DETAILS*****************************
Exam officerId : R044 Ext:2335
Name :Anna
INF(REMOVE REPEATING GROUP)
STUDENT(SID,NAME,TEL,ADDR,EMAIL,
ExamOfficerid,name,ext)
Subject taken
(SID,SUBCODE,DESC,CREDITHR,MARK,GRADE,
REMARK)
S001 ICT 1104 DBM 3 89 A
S002 ITM 3222 CS 4 60 C
S001 ICT 3201 CM 3 50 D
S003 ICT 1104
S004 ICT 1104
2NF(REMOVE FUNCTIONAL DEPENDENCY/PARTIAL
DEP)
STUDENT(SID,NAME,TEL,ADDR,EMAIL,
ExamOfficerid,name,ext)
Sub taken (SID*,SUBCODE*, ,MARK,GRADE,REMARK)
SUBJECT(SUBCODE,DESC,CREDITHR)
Itm3201 database 3
Itm 3202 network
3NF(REMOVE TRANSITIVE DEPENDENCY)
STUDENT(SID,NAME,TEL,ADDR,EMAIL,
ExamOfficerid*)
OFFICER(ExamOfficerid,name,ext)
Subject taken(SID*,SUBCODE*, ,MARK,GRADE,REMARK)
S001 CA11*
S001 DB22
S003 CA11*
SUBJECT(SUBCODE,DESC,CREDITHR)
CA11 COMP ARC 4
CA12 COMP SYS 5
