Room (room#)
Attendance Attended Attended by At Offering
Employee (Empl#)
Course (CourseNa
me
Date (DD/MM/YY
0
Lecturer (Name)
On the in
Offered Of start delivering
offered Leader
Correct Schema
Course Management Management Marketing Prod Plan Exporting Management Management
Date 1/9/93 2/10/93 5/1/94 6/1/94 7/1/94 7/1/94 7/9/94
Venue Tr Rm 1 Tr Rm 1 Tr Rm 1 Tr Rm 2 Tr Rm 2 Tr Rm 1 Tr Rm 2
Lecturers A Smith A Smith
A Smith, P Brown J Jones
J Jones P Brown P Brown
Attendees E1, E2, E3 E4, E5, E6 E1, E2, E7, E8 E5, E9, E10 E2, E7, E8, E9 E10, E12, E13 E7, E8, E14
1. Training Courses
Date (DDMMYY) Trn_course
Employee (EMP#)
Attendance
attends attendedby
Leader
teaches taughtby
Lecturer (L_NAME)
held on is dayheld
Room (ROOM#) Venue (CRS_NAME) held in hascourse
An Employee can only attend a particular
Training Course once and can attend only one Training Course per day.
A Lecturer can only give one course per day. There can be only one Training Course per Venue per day.
Other facts can be
inferred from the example output report.
Room (room#)
Attendance Attended Attended by At Offering
Employee (Empl#)
Course (CourseNa
me
Date (DD/MM/YY
0
Lecturer (Name)
On the in
Offered Of start delivering
offered Leader
Correct Schema
Unit Code ITB210 ITB101 ITB410 ITB310 ITB412 ITB102 ITB411 BSB103 ...
Unit Name
Formal Representation Laboratory 1
Software Development 1 Information Management 1 Technology of Information Systems
Laboratory 2
Software Development 2 Business Communications
Prereq nonenone nonenone
ITB101 ITB410 ITB101 none Sem
1
2
3
2. Degree Subjects.
Semester (SEM#) Subject
(SJT_CODE, SJT_NAME)
Offerings
offers offered in
Prerequisites isprereq
to
has as prereq
P#
p1p2 p3 p4p5 p6p7
P_name
nutnut bolt screw camcog cog
Size
1020 5 126 1525
Qty on hand 500800 400 200800 120350
Qty on order 3000 200 1500 20050
Reorder level 400300 300 800100 100100
3. Stock Details.
Part (PART#)
PART_NAME
Size (SIZE_UNIT) Description
Measurement
called is name of
is size has size of
Number (INTEGER)
instock
is no instock
order at
no to order at
has on order
no on
order U
B o r n G r a d u a t e d J o i n e d P e r s o n A c s D a y M o n t h Y e a r M o n t h Y e a r
p 1 1 7 6 5 1 2 8 9 8 7 p 2 3 7 6 4 1 2 9 2 9 0 p 3 5 9 5 7 6 9 2 9 3 p 4 1 0 8 6 5 6 9 1 9 3 p 5 3 7 6 5 6 9 1 8 7
4. Different Days.
Person (PER_ID)
Year (YEAR#)
joined in
whenwas member
Month MONTH#
Month_number
has mth#
is mth#of
Month_year
is in hasmonth
U
Graduation
gotdegree when grad
Day DAY#
hasday is in
hasday#
isday#
of
U
isbirth date
Dob
born on
Join of the three projections produces original population,
P S b c b m g c g p o m
Publisher (PUB_NAME)
Subject category (CAT_NAME)
Library (LIB_NAME)
markets marketed by holds
Big Books Big Books Big Books Good Books Good Books Old Books
Computing Mathematics Mathematics Computing Philosophy Mathematics
Eng Lib Eng Lib Maths Lib Maths Lib Maths Lib Eng Lib
2. Book Supply.
S L c e m e m m p m
P L b e b m g e g m o m
=
P S L b c e b m e b m m g c e g p m o m e o m m
Conclusions?
Problem 6
Book Supply
No join of any two projections on two columns produces original population of the fact type. Therefore, Split of this fact type into two fact types is impossible.
HOWEVER:
E1 E2 E3 E4 E5
D2 D2 D1 D1 D2
CS01 Employee
(EMP#) Department
(DEPT_CODE) Position (POS_CODE)
in location is location of allocated to
CS01 CS02 CS03 CS03
1. Personnel.
Is the following conceptual schema diagram splittable?
Yes. Employee determines Dept and Pos, so split on Employee is possible
Problem 5
Is the following Fact Type splitable?
Final schema
Publisher (PUB_NAME)
Subject_
category (CAT_NAME)
Library (LIB_NME) Marketing
Holdings
Sales
marketed
by markets
heldin holds
sells
to buys
from
Problem 7
Academic results
Enrolment Student
(STU#) Unit (UNIT#)
Year (YEAR#)
studies/
in offered/
when offers/
to
gotresult of isresult given
Result (GRADE)
Final_
exam (%)
scored in
wereawarded to
Student
(STU#) Year
(YEAR#) Unit (UNIT#)
Final_exam (%) Result
(GRADE)
3. Academic Results.
Construct a significant population and check the following conceptual schema diagram (correct if it is wrong) given the constraints shown:
c1: a unit is offered once in each year c2: a student may repeat a unit
c3: a student may study many units in each year c4: a unit is studied by many students in each year c5: a final examination percentage is to be recorded
for each student in each unit offering
c6: the final grade is to be recorded for each student in each unit.
Proj P1
P2
P3
ProjName Accounts
HRMS
Loans
Budget 200,000
350,000
270,000
Leader 789012
567890
890123
Worker 123456 234567 456789 234567 345678 456789 678901 Empl
123456 234567 345678 456789 567890 678901 789012 890123
Tax_No 000-111- 222 000-222-333 000-333-444 000-444-555 000-555-666 000-666-777 000-777-888 000-888-999
Sex MM
F FF M MF
ProjInv P1P1 P2P2 P1P2 P2P3 P1P3
4. Projects and employees.
Employee (EMP#)
Project (PRJ_CODE)
TAX_FILE#
hasTFN
isTFN of
SEX
is of sex
hasempl
PRJ_NAME
hasname
isname of
Money ($)
alloc p.a.
isalloc of
Worker Manager
works
on has
working manages managedby
Problem 9
Employees and projects
Employee Department Salary Address 13416
24576 43658 98452 88774
Sales Accounts Sales Production Production
21,950 26,000 23,400 26,000 21,950
1 High St, Toowong 1 George St, City 6 Queen St, City 1 High St, Toowong 4 Rode Rd,
Chermside
Employeee Earns Is earned by Salary
Salarye
Works for Employs Work Location
Department
Lives at Is home of Domicile
Home
Of amountIs amount of Pay_Id
Dollars
Earns Is earned by Dept_Id
Dept_name
Located at Is of Home_Location
Address
Is identi-
fied by Identifies Empl_Id
Empl#
ZMA-s3-S 7
Vehicle
Vehicle repairs
Date
Money Is the cost Of Repaired
on/for
Is identi-
fied by Identifies Vehicle_Id
Regl#
Ident. by Is id of Pay_Id
DD/MM/YY
Of amountIs amount of Pay_Id
Dollars Vehiclee Earns Is earned
by Date
Is cost of Money
For the cost Vehicle repairs
Repair cost
Alternative Approach
(with nested FT)
Problem 1.
Employee Details
Problem 2.
Vehicle repairs
Vehicle Date Cost 100ABC
100ABC 100ABC 200DEF 200DEF 300GHI 400JKL 900XYZ
18/11/02 25/03/01 14/07/01 10/05/02 18/11/02 01/06/02 14/07/01 25/03/01
350.77 765.00 350.77 357.77 150.00 53.90 980.00 765.00
Insect
Herb Ants Aphids Cabbage Flies Mosqui- Potato Tomato Moths Toes bug worm Basil
Borage Dead Nettle Flax Horse radish Hyssop Spearmint
X X
X X
X X X
X X Employeee
Born at Is DoB of Birth
Date
Started work
Of empl start
Empl_Commencement
Is Id of
Empl_ID Is iden- tified by Empl
Id Is cost of
DD/MM/YY For the Date cost
Id
Insect Is control- Herb
led by Controls
Pest Control
Is Id of
Pest_name Is iden-
tified by Pest Id
Is cost of
Herb_name For the
Herb cost
Id
Problem 3.
Employee dates
Employe e
Birth_dat e
Empl_start_date 123456
234567 345678 456789 567890 678901
21/06/60 18/07/55 29/03/55 20/08/65 21/06/60 16/12/72
05/09/83 20/04/86 04/07/90 04/07/90 16/12/72 02/01/92
Problem 4 Pest Control
Employeee Earns Is earned by Salary
Salarye
Works for Employs Work Location
Department
Lives at Is home of Domicile
Home
Of amountIs amount of Pay_Id
Dollars
Earns Is earned by Dept_Id
Dept_name
Located at Is of Home_Location
Address
Is identi-
fied by Identifies Empl_Id
Empl#
99 Vehicle
Vehicle repairs
Date
Money Is the cost Of Repaired
on/for
Is identi-
fied by Identifies Vehicle_Id
Regl#
Ident. by Is id of Pay_Id
DD/MM/YY
Of amountIs amount of Pay_Id
Dollars Vehiclee Earns Is earned
by Date
Is cost of Money
For the cost Vehicle repairs
Repair cost Alternative
Approach (with nested FT)
Problem 5
Employee Department Salary Address 13416
24576 43658 98452 88774
Sales Accounts Sales Production Production
21,950 26,000 23,400 26,000 21,950
1 High St, Toowong 1 George St, City 6 Queen St, City 1 High St, Toowong 4 Rode Rd,
Chermside
1. Employee Details
2. Vehicle repairs
Vehicle Date Cost 100ABC
100ABC 100ABC 200DEF 200DEF 300GHI 400JKL 900XYZ
18/11/02 25/03/01 14/07/01 10/05/02 18/11/02 01/06/02 14/07/01 25/03/01
350.77 765.00 350.77 357.77 150.00 53.90 980.00 765.00
10 Employeee
Born at Is DoB of
Date
Started
work Of empl start
Empl_Commencement
Is Id of
Empl_ID Is iden- tified by Empl
Id Is format
of
DD/MM/YY Of format Dat e Id Birth
Employee (Empl_Id)
Born at Is DoB of Birth
Date (DD/MM/YY) Started
work Of empl start
Empl_Commencement
ZMA-s3-S
Insect
Herb Ants Aphids Cabbage Flies Mosqui- Potato Tomato Moths Toes bug worm Basil
Borage Dead Nettle Flax Horse radish Hyssop Spearmint
X X
X X X X X
X X
Insect Is control- Herb
led by Controls
Is Id of
Pest_name Is iden-
tified by Pest Id
Herb_name
Herb
Id Is Id of Is iden- tified by
3. Employee dates
Employee Birth_date Empl_start_date 123456
234567 345678 456789 567890 678901
21/06/60 18/07/55 29/03/55 20/08/65 21/06/60 16/12/72
05/09/83 20/04/86 04/07/90 04/07/90 16/12/72 02/01/92
4. Insect Control
Employee Manager Jones
Adams Wong Fegan Crane Bell G Spencer Bell A
Giles Jones Jones Giles Bell G Jones Bell G Jones
Name DAddress CompanyC
ar Job Title
Brown Smith Santos Wong Ng Nguyen
1 George St 1 Queen St 1 Queen St 1 George St 78 Elizabeth St 15 Mary St
400NRU 500NRU 100ABC 100BCD 500PQR 400NRU
Sales Rep Sales Support Manager Clerk Manager Sales Rep
Employeee
Emp_Id Empl as Is job title of
Occupation
Position (J_T_name)
uses Is used by Car Use
Car (NReg#
Works at Is location of
Dept Loc
DeptAddr (H_Address)
Problem 6
Employees and Managers.
Employeee Emp_Id
Is Mana- Ged by
Manages
Management
Problem 7
Employee information.
Problem 8
Staff Offices
Floor Room# Staff Member 2
2 3 2
12 13 12 13
Sally Wong Tom Smith Manual Barriga Johann Schmidt
Employeee
Emp_Id Has office
In Is office of Office Location
Room
of
Floor Is on
of
Room # has
U
Employee Project Total Allocation (hours) HoursSpent
E1 E1 E2 E2 E3 E3
P1 P2 P1 P2 P1 P2
50 50 65 35 50 35
24 26 13 15 25 26
Owner Address RegNum
ber RegLet
ters SpecPlate Bill Brown
Sally Brown Mary Jones Helen Santos Thom Santos Peter Perfect
1 George St 1 George St 15 Park Rd 22 Mary St 22 Mary St 77 Mary St
100 100 100 202
ABC ABC PQR ABC
Wowser Perfect
Problem 9
Employees and projects
Problem 10 Vehicle registration
Province
Medals
Gold Silver Bronze Total
Province W 7 5 3 15
Province X 2 2 9 13
Province Y 3 1 3 7
Province Y 4 0 2 6
…..
Employee
(Empl#) Allocated
to
Ihas allocation
Projedt ((proj#)
Is cost of
Time (Hours) For the
Total cost Alloc
Is cost of For the Time cost
spent
Owner
(name) owns Is owned
by Ownership
Care
Home (address)
Is identified
Is part odf rego RegoNumbers
RegoNunb
Is identified
Is part of rego RegoLetters
RegoLetters
Is
identified identifies SpecialRegoNumbers
SpecialPlate Lives at Identifies
Domicile
U
Province (ProvId)
Achieved
Achieved
Silver
Medals (No)
Achieved
Bronze Gold
Achieved
* TOTAL
ORM Schema using binary fact types
Problem 11 Medals on a Championship
Province
RoviId)e Earns Is earned
by
Medal Type (colour)
Is number of
Number (Int) Has won
Achievement
Achievement
is number Size
of
Number (Int) Has won
In total
Total Medals *
Product
(prod_Id) Under sale in Quarter
(Q_No)
of
Value ($US) Has reached
Sales
Sales Value Product
(prod_Id)
Quarter (Q_No)
Value ($US)
Under sale in Achieved for Product
Sales Value
Two schema options are presented: Flat – ternary fact type and nested.
ORM Schema using a ternary fact type
Problem 13 Typical fact is here:
Product A in Quarter 1 has reached 2.4 mln $US in sales.
If the year is required to the sale description, the verbalisation will be as follows
Product A in Quarter 1 of 2007 has reached 2.4 mln $US in sales.
Product
(prod_Id) Under sale in Quarter
(Q_No)
of
Value ($US) Has reached
Sales
Sales Value If the year is desired, then the
schema must be modified as follows .
Once again two schema options are presented
Year (YYYY)
Product
(prod_Id) Quarter
(Q_No) Value
($US)
Under sale in Achieved
for
Product Sales Value
in
in Quarter (Q_No)
Problem 13 (cont)
15 Typical facts are here:
The Labor party in 2008 year election has won 31 seats (in the Parliament) The Democrats have 10% of seats in the Parliament after 2008 election
Note: the assumption is that there are no more than one election in a single year. If this assumtion is not valid, then other item of election identification could be added : eg 2008/E1 for the first election in 2008
Party (party_nam
e)
Participa-
ting in
Election (Election_ID
)
Elected
MPs (No) Has achieved
Participation Election Result in %%
of *
Participation (%%)
Has achieved Election
Result
Problem 15
!["De l'antisymbolon à l'antapocha?", Henri Henne, "Studi in onore di V. Arangio-Ruiz", IV : [recenzja]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACH5BAEAAAAALAAAAAABAAEAAAICRAEAOw==)