 |
|
 |
| |
Database Design
eXercise #4
Further Constraints
- Referring to the following predicates:
Indicate those DEFINITELY UNSPLITTABLE, PROBABLY UNSPLITTABLE and
DEFINITELY SPLITTABLE.
- Referring to the following predicates:
Re-draw those predicates that are incorrectly formed.
- The following is an exerpt from a University results system - perform
CSDP steps 1-5 on this data - be careful to identify derived information,
and supply rules for derivation if possible on your schema.
Subject Year Enrollment Rating NrStudents % Lecturer
_______________________________________________________________
CS121 1982 200 7 5 2.5 F.Bloggs
6 10 5
5 75 37.5
4 80 40
3 10 5
2 5 2.5
CS123 1982 150 7 4 2.67 O.Paarts
6 8 5.33
5 60 40
4 70 46.67
1 6 4
CS121 1983 250 7 .....
6 .....
5 .....
4 ..... <-- note that not all
3 ..... ratings are allocated
CS007 etc. each year!
|
- This question involves the design of an information system for evaluating
the cost of sending an air-mail parcel by Intemational Parcel Post.
The table shown below is taken from Australia Post's booklet Postal
Charges Easy Guide".
International Parcel POST Charge per article
| Weight |
Category |
Zone 1 |
Zone 2 |
Zone 3 |
Zone 4 |
Zone 5 |
| Up to 1kg |
A |
11.00 |
13.00 |
15.00 |
17.50 |
19.50 |
| 1kg-2kg |
B |
16.00 |
20.00 |
24.00 |
29.00 |
33.00 |
| 2kg-3kg |
C |
21.00 |
27.00 |
33.00 |
40.50 |
46.50 |
| 3kg-4kg |
D |
26.00 |
34.00 |
42.00 |
52.00 |
60.00 |
| 4kg-5kg |
E |
31.00 |
41.00 |
51.00 |
63.50 |
73.50 |
| 5kg-6kg |
F |
36.00 |
48.00 |
60.00 |
75.00 |
87.00 |
| 6kg-7kg |
G |
41.00 |
55.00 |
69.00 |
86.50 |
100.50 |
| 7kg-8kg |
H |
46.00 |
62.00 |
78.00 |
98.00 |
114.00 |
| 8kg-9kg |
I |
51.00 |
69.00 |
87.00 |
109.50 |
127.50
|
| 9kg-1Okg |
J |
56.00 |
76.00 |
96.00 |
121.00 |
141.00 |
| Each additional kg up to 20kg |
5.00 |
7.00 |
9.00 |
11.50 |
13.50 |
Maximum weight depends on country of destination
While the more observant of you will already have noticed a definite
pattem to the numbers in, this table, we will actually store these
data because Australia Post may not always follow this rule in future.
Note that, given a parcel's weight, we can determine the category
to which it belongs because each category has a definite maximum and
minimum weight. Further, given a parcel of a particular weight and
a country of destination, we must be able to derive the appropriate
postal charge.
Countries to which a parcel may be sent are grouped into zones, e.g.,
the USA is in Zone 4 while the Solomon Islands are in Zone 1. No country
can be in more than a single zone.
Perform CSDP Steps 1-5 for this application. State any additional
assumptions you have made in your solution. Where a fact type is derivable,
provide a rule or computation scheme next to the schema.
- A fictitous company is organized into departments. Each department
has a name, a number and an employee who manages that department.
We keep track of the start date when that employee begins managing
that department. A department may have several locations.
A department controls a number of projects each of which has a number,
name and single location.
For each employee, we store their name, tax file number, address,
salary, gender and date of birth. An employee is assigned to one department
but may work on several projects within that department and may also
work on projects not managed by their department. We keep track of
the number of hours per week that an employee works on each project.
We also keep track of the direct supervisor of each employee.
We also keep track of the dependants of each employee for insurance
purposes, with the name, gender, birth date and relation to the employee
recorded for each dependant.
Model this system, complete to STEP 5, being careful to annotate any
assumptions you needed to make.
- A University keeps track of each student's name, student number,
tax file number, current address, current phone number, permanent
address and phone number, birth date, gender, year (1st, 2nd, ...,
graduate), Major department, Minor Department (if any), and degree
program (B.A, B.I.T, ..., Ph.D.). Some people accessing the system
need to refer to the City, state, postcode and student's permanent
address and to the student's first and last names. Both student number
and tax file number uniquely identify the student.
Each department is described by name, department code, office number,
office phone number, and faculty. Both name and code have unique values
for each department.
Each course has a course name, short and verbose description, code
number, contact hour count per semester, level and offering department.
The code is unique for each course.
Each course may be divided up into sections, each section has an instructor,
semester, year and course and section number. The section number distinguishes
different sections of the same course that are taught during the same
semester/year; its values are 1, 2, 3... up to the number of sections
taught inthat course.
A grade reports has a student, section and grade.
Model this system, complete to STEP 5, being careful to annotate any
assumptions you needed to make.
- Referring to the following Conceptual Schema,
Place constraints on the schema to reflect your understanding of the
'real' world.
Solutions
|
|
|
 |
|
 |
|
