Name: ________________ Terminal: ___
Homework 1:
COSC 3380
(100 points)
(Due date found in the COSC 3380 BB Calendar!)
Hardcopy (with your terminal #!): a handwritten PAPER AND PENCIL!,
WARNING: YOU WILL LOOSE 10 points IF YOU DO NOT MARK YOUR
TERMINAL NUMBER ON THE HARCOPY!
TURNING IN THE HOMEWORK INSTRUCTIONS will be
PENALTY OF -10 points.
I UNDERSTAND THAT TURNING ANOTHERs WORK IN is
CHEATING.
I UNDERSTAND THAT ANY KIND OF DISSEMINATION of this
WORK is CHEATING.
I CERTIFY THAT THE HOMEWORKs SOLUTIONs ARE MY OWN WORK!
SIGNATURE:
HOMEWORK CHECKLIST (YOU MUST GRADE YOURSELF!):
1.
2.
3.
4.
5.
DID TURN IN HOMEWORK INSTRUCTIONS? *
DID NOT DO TEXTUAL ANALYSIS *
DID NOT LABEL Es,Rs,As both on TEXT and ERD? *
DID NOT Sign *
Did not SUBMIT Homework1.doc to BB? *
points
6. Your Score out of 100 (25,50,25)
-10 points
-20 points
-20 points
-10 points
-90
100 points
* If NOT, do not enter anything in the box!
PLEASE ENTER YOUR GRADE IN THIS BOX:
I understand that if the .doc file is NOT in BB and I did not check the
BOX, I will get a ZERO for the Lab!
1. (25 points) Data Requirements Analysis for ERD
(you need to do the Textual Analysis on the Requirements below)
(1 E1: Customer Own M E2: Cars)
(you must use Microsoft Word)
E1: Customers
R1: Own
Construct an ERD Model for a car insurance company whose
E2: own
Cars one or more cars
E1: each.
Customers
A2: customer_name
customers
Customers
customer_id, name, and address need to be stored
in the A3: customer_address
E1: Customers
E1: Customers A1: customer_ID
R2: Has
(1 E2:to
Cars
Has
0:M E3: Accident)
database. Each car has associated with it zero
any
number
of recorded accidents. Both cars licence_no
and model data
E2: Cars A1: licence_no
E2: Cars A2: model
need to be stored in the database. Accidents report_id, date,
E3: Accidents A1: report_ID
and place need to be storedE3:
inAccidents
the database.
Each insurance
policy (has a policy_id) covers one or more cars, and
has one
E3: Accidents
A2:or
report_DATE
E4: Policy
more premium payments (amount) associated with it. Each
E3: Accidents A3: report_PLACE
E4: Policy A1: payment
policy_ID
(1 E4: Policy Cover M E2: Cars)
(has a payment_no) and is for a particular period of
R3: Covers
(1 E4: Policy Associate with M E5: Payment)
time, and has an associated due date, and the date when the
E5: Payment
R4: Associated
payment was received.
E5: Payment A1: payment_No
E5: Payment A2: payment_due_DATE
ANSWER:
E5: Payment A3: payment_received_DATE
_ID
2. (50 points) Data Requirements Analysis for ERD
(you need to do the Textual Analysis on the Requirements below)
(you must use Microsoft Word)
Consider a database used to record the marks that students
E1: Student
get in different exams of different course offerings (sections).
E1: Student A1: student_ID
E1: Student A2: name
E1: Student A3: department_name
(1
2 A4: total_credit
E1: Student
(1
R1: Takes
E1: Student Takes M E2: Sections)
E1: Student Takes M E4: Exams)
E2:Course
Section
A3:
year
E2:
Section
A1:
E3:
E3:
E4:
Course
E2:
Course
Exam
Section
E4:
E4:
A1:
A3:
Exam
A2:
Exam
course_ID
place
title
A2:
section_ID
A1:
semester
A2:
exam_ID
name
E3:
E4:
Exam
A4:
A3:
time
credits
E2:Course
Section
E3:
E4: Exam
Students student_id, name, dept_name, and tot_cred need to
be stored in the database. Sections sec_id, semester, and year
need to be stored in the database. Courses course_id, titlee,
and credits need to be stored in the database. Exams exam_id,
name, place, and time need to be stored in the database.
a. Construct an E- R diagram that models exams as entities,
and uses a ternary relationship between student, section, and
exam, for the database. marks
semester
section_ID
ANSWER:
student_ID
name
department_name
year
E2: Section
R1: Takes
E1: Student
total_credit
place
exam_ID
E4: Exam
time
name
b. Construct an alternative E- R diagram that uses only a
binary relationship between student and section. Make sure
that only one relationship exists between a particular student
and section pair, yet you can represent the marks that a
student gets in different exams.
section_ID
semester
ANSWER:
student_ID
name
department_name
year
Section
Takes
Student
total_credit
Exam of
Marks
place
exam_ID
E4: Exam
time
name
3. (25 points) Data Requirements Analysis from ERD
Figure below represents a situation of students who attend and work in schools and who
also belong to certain clubs that are located in different schools. Study this diagram carefully to try to discern what business rules are represented.
a. You will notice that cardinalities are not included on the Works For relationship. State
a business rule for this relationship and then represent this rule with the cardinalities that
match your rule.
ANSWER:
A STUDENT Works For 0:1 SCHOOL and a SCHOOL Employs 0:M STUDENT
b. State a business rule that would make the Located In relationship redundant ( i. e.,
where the school in which a club is located can be surmised or derived in some way from
other relationships).
ANSWER:
A STUDENT may Belongs To a CLUB that is Located In the SCHOOL s/he
Attends
c. Suppose a STUDENT could work for only a school that STUDENT attends but might
not work. Would the Works For relationship still be necessary, or could you represent
whether a student works for the school s/he attends in some other way ( if so, how)?
ANSWER:
Not necessary anymore, we can just have an attribute in STUDENT indicates whether
s/he works or not for the SCHOOL s/he Attends
c. Suppose a STUDENT could work for only a school that STUDENT attends but might not
work. Would the Works For relationship still be necessary, or could you represent whether a
5 other way ( if so, how)?
student works for the school s/he attends in some
ANSWER:
